-- ==============================================================================
-- U.S. SECURITIES AND EXCHANGE COMMISSION (SEC)
-- DIVISION OF ENFORCEMENT // FORENSIC LITIGATION UNIT
-- ==============================================================================
-- CASE NAME: In the Matter of OpenAI Group PBC & OpenAI Foundation
-- CASE NO:   5:26-cv-07078-EJD (N.D. Cal.)
-- EXHIBIT ID: SEC WHISTLEBLOWER NO. 17684-273-411-436 // SUPPLEMENTAL EXHIBIT D
-- TITLE:     Forensic SQL Query Sheet for Communication Timelines & Exfiltration
-- PURPOSE:   Pre-compiled Relational JOINs for Automated Trial Exhibits
-- DATE:      August 26, 2026
-- ==============================================================================

-- ==============================================================================
-- EXHIBIT A: THE COVERT COMMUNICATION TIMELINE (LINE / SIGNAL PROTOCOL)
-- LEGAL THEORY: Admissible under FRE 801(d)(2)(E) (Co-conspirator Statements)
--               and FRE 1006 (Summaries of Voluminous Digital Records).
-- DESCRIPTION: Restructures communication metadata between Chang Liu and Alyssa 
--              Peng to show the parallel flow of recruitment and coordination.
-- ==============================================================================

.headers on
.mode column
.width 35 25 25 15 15 15 15

SELECT 
    (e_sender.first_name || ' ' || e_sender.last_name) AS Sender,
    e_sender.current_openai_title AS Sender_Corporate_Role,
    ec.recipient_name AS Recipient,
    ec.platform_name AS Platform,
    ec.message_count AS Total_Messages,
    DATETIME(ec.earliest_message_timestamp) AS Earliest_Contact,
    DATETIME(ec.latest_message_timestamp) AS Latest_Contact,
    -- Calculate active communication span in days
    ROUND(JULIANDAY(ec.latest_message_timestamp) - JULIANDAY(ec.earliest_message_timestamp), 2) AS Active_Span_Days,
    -- Highlight spoliation indicators (self-delete flags)
    CASE 
        WHEN ec.self_delete_timer_seconds > 0 THEN '⚠️ HIGH RISK (Spoliation Enabled)'
        ELSE 'STANDARD'
    END AS Evidence_Spoliation_Risk
FROM encrypted_channels ec
JOIN employees e_sender ON ec.sender_employee_id = e_sender.employee_id
-- Filter specifically for communication loops containing Liu and Peng
WHERE (e_sender.employee_id = 'EMP_LIU_01' AND ec.recipient_name LIKE '%Peng%')
   OR (e_sender.employee_id = 'EMP_PENG_01' AND ec.recipient_name LIKE '%Liu%')
ORDER BY ec.earliest_message_timestamp ASC;


-- ==============================================================================
-- EXHIBIT B: EXFILTRATION & NETWORK STORAGE INTRUSION LOG (THE FORENSIC TRAIL)
-- LEGAL THEORY: Admissible under FRE 803(6) (Records of Regularly Conducted Activity)
--               and authenticated under FRE 901(b)(9) (System Process / Output).
-- DESCRIPTION: Combines Chang Liu''s unauthorized network folder accesses with 
--              unreturned device MAC addresses to map the stolen blueprint categories.
-- ==============================================================================

.width 20 20 25 35 25 18 12

SELECT 
    (e.first_name || ' ' || e.last_name) AS Exfiltrator,
    e.former_apple_title AS Former_Apple_Role,
    d.device_type AS Access_Device,
    f.network_storage_path AS Unauthorized_Path_Accessed,
    DATETIME(f.intrusion_timestamp) AS Hack_Timestamp,
    f.exfiltrated_blueprint_category AS Blueprint_Category,
    -- Convert bytes to Megabytes for clean presentation
    PRINTF('%.2f MB', CAST(f.bytes_downloaded AS REAL) / (1024 * 1024)) AS Payload_Size,
    CASE 
        WHEN f.authentication_vulnerability_used = 1 THEN '❌ Backdoor Used'
        ELSE 'Direct Credential'
    END AS Access_Method
FROM forensic_logs f
JOIN employees e ON f.employee_id = e.employee_id
JOIN devices d ON f.device_id = d.device_id
WHERE e.employee_id IN ('EMP_LIU_01', 'EMP_PENG_01')
ORDER BY f.intrusion_timestamp ASC;


-- ==============================================================================
-- EXHIBIT C: RULE 10b-5 VALUE-PUMP TIMING MATRIX (THE HACK-TO-VALUATION LOOP)
-- LEGAL THEORY: Establishes materiality of omissions under SEC Rule 10b-5 by 
--               proving that funding commitments occurred during active theft.
-- DESCRIPTION: Correlates the exact timestamps of database intrusions with the 
--              closing schedules of OpenAI''s multi-billion dollar private capital rounds.
-- ==============================================================================

.width 20 25 22 25 20 18

SELECT 
    DATETIME(f.intrusion_timestamp) AS Exfiltration_Date,
    (e.first_name || ' ' || e.last_name) AS Actor,
    f.exfiltrated_blueprint_category AS Misappropriated_IP,
    sm.targeted_entity AS Funding_Participant,
    -- Format capital round valuation in Billions
    PRINTF('$%.1f Billion', CAST(sm.associated_capital_round_value AS REAL) / 1000) AS Round_Valuation,
    sm.subpoena_status AS Subpoena_Posture,
    -- Days between exfiltration and funding round close
    CAST(JULIANDAY(sm.transaction_close_date) - JULIANDAY(f.intrusion_timestamp) AS INTEGER) AS Days_Before_Close
FROM forensic_logs f
JOIN employees e ON f.employee_id = e.employee_id
CROSS JOIN subpoena_matches sm
-- Temporal constraint: Link exfiltrations that occurred up to 60 days prior to capital round closure
WHERE f.intrusion_timestamp BETWEEN DATE(sm.transaction_close_date, '-60 days') AND sm.transaction_close_date
  AND e.employee_id IN ('EMP_LIU_01', 'EMP_PENG_01')
ORDER BY sm.transaction_close_date ASC, f.intrusion_timestamp ASC;


-- ==============================================================================
-- EXHIBIT D: COMPREHENSIVE RECRUIT REVENUE PIPELINE (THE TALENT SIPHON IMPACT)
-- LEGAL THEORY: Quantifies damages under the Defend Trade Secrets Act (DTSA) 
--               by illustrating the scale of head-count exfiltration.
-- DESCRIPTION: Pulls a dynamic roster of poached employees, mapping their transition 
--              from specific Apple divisions directly into Jony Ive''s io Products PBC.
-- ==============================================================================

.width 25 30 25 30 15 12

SELECT 
    (e.first_name || ' ' || e.last_name) AS Employee_Name,
    e.former_apple_title AS Former_Apple_Role,
    e.former_apple_division AS Exited_Apple_Division,
    e.current_openai_title AS Assigned_OpenAI_Role,
    e.openai_onboarding_date AS Onboarding_Date,
    -- Flag if they are named in the primary trade secret complaints
    CASE 
        WHEN e.is_named_defendant = 1 THEN 'YES (Target)'
        ELSE 'NO'
    END AS Named_Defendant,
    -- Track associated device secure hash
    COALESCE(d.forensic_image_hash, 'No Image (Spoliation Risk)') AS Forensic_Image_Hash
FROM employees e
LEFT JOIN devices d ON e.employee_id = d.employee_id
WHERE e.current_openai_division LIKE '%io Products%' 
   OR e.current_openai_division LIKE '%Consumer Devices%'
ORDER BY e.openai_onboarding_date ASC;