-- ==============================================================================
-- U.S. SECURITIES AND EXCHANGE COMMISSION (SEC)
-- DIVISION OF ENFORCEMENT // FORENSIC INVESTIGATIONS UNIT
-- ==============================================================================
-- CASE NAME: In the Matter of OpenAI Group PBC & OpenAI Foundation
-- CASE NO:   5:26-cv-07078-EJD (N.D. Cal.)
-- FILE REFE:  SEC WHISTLEBLOWER NO. 17684-273-411-436
-- SUBJECT:   Travelocity Lexicon of Corporate Espionage & Hardware Talent Siphon
-- PURPOSE:   Forensic Schema for Automated Subpoena Matching & Device Tracking
-- ==============================================================================

-- Clean up existing structures to ensure clean execution
DROP VIEW IF EXISTS view_unauthorized_ip_leaks;
DROP VIEW IF EXISTS view_valuation_intrusion_correlation;
DROP VIEW IF EXISTS view_high_risk_exfiltration_targets;

DROP TABLE IF EXISTS subpoena_matches;
DROP TABLE IF EXISTS forensic_logs;
DROP TABLE IF EXISTS encrypted_channels;
DROP TABLE IF EXISTS devices;
DROP TABLE IF EXISTS employees;

-- 1. EMPLOYEES TABLE: Tracking the 400+ Poached Apple Personnel
CREATE TABLE employees (
    employee_id VARCHAR(50) PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    former_apple_title VARCHAR(150) NOT NULL,
    former_apple_division VARCHAR(100) NOT NULL, -- e.g., iPhone Product Design, Apple Watch SoC
    termination_date DATE NOT NULL,
    openai_onboarding_date DATE NOT NULL,
    current_openai_title VARCHAR(150) NOT NULL,
    current_openai_division VARCHAR(100) NOT NULL, -- e.g., Consumer Devices Unit, io Products
    line_username VARCHAR(100),
    is_named_defendant BOOLEAN DEFAULT FALSE,     -- e.g., Tang Tan, Chang Liu, Alyssa Peng
    has_signed_nda_clawback BOOLEAN DEFAULT TRUE,  -- Active non-disparagement / equity clawbacks
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. DEVICES TABLE: Tracking Retained Laptops, Personal iClouds, & Storage Enclaves
CREATE TABLE devices (
    device_id VARCHAR(50) PRIMARY KEY,
    employee_id VARCHAR(50) REFERENCES employees(employee_id),
    device_type VARCHAR(100) NOT NULL,             -- e.g., MacBook Pro, Google Pixel 8 Pro
    serial_number VARCHAR(100) UNIQUE NOT NULL,
    mac_address VARCHAR(17) UNIQUE,
    mdm_status VARCHAR(50) DEFAULT 'UNLOCKED',     -- e.g., Locked, Bypassed, Unlocked, Residual
    retained_post_departure BOOLEAN DEFAULT FALSE, -- e.g., Did they fail to return work laptop?
    personal_icloud_connected BOOLEAN DEFAULT FALSE,
    forensic_image_hash VARCHAR(64),               -- SHA-256 of mirrored drive
    last_known_ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. ENCRYPTED CHANNELS: Mapping Covert Communication Channels (LINE, Signal, WhatsApp)
CREATE TABLE encrypted_channels (
    channel_id VARCHAR(50) PRIMARY KEY,
    sender_employee_id VARCHAR(50) REFERENCES employees(employee_id),
    recipient_name VARCHAR(150) NOT NULL,         -- e.g., Alyssa Peng, Tang Tan, Jony Ive
    recipient_status VARCHAR(100) NOT NULL,       -- e.g., Active Apple Employee, Outside Counsel
    platform_name VARCHAR(50) NOT NULL,           -- e.g., LINE, Signal, Telegram
    group_chat_name VARCHAR(200),
    message_count INTEGER DEFAULT 0,
    self_delete_timer_seconds INTEGER DEFAULT 0,  -- Spoliation indicators
    earliest_message_timestamp TIMESTAMP,
    latest_message_timestamp TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. FORENSIC LOGS: Tracking Network Intrusions and File Exfiltration Actions
CREATE TABLE forensic_logs (
    intrusion_id VARCHAR(50) PRIMARY KEY,
    device_id VARCHAR(50) REFERENCES devices(device_id),
    employee_id VARCHAR(50) REFERENCES employees(employee_id),
    network_storage_path VARCHAR(500) NOT NULL,   -- Apple R&D shared folders accessed
    intrusion_timestamp TIMESTAMP NOT NULL,
    bytes_downloaded BIGINT NOT NULL,
    authentication_vulnerability_used BOOLEAN DEFAULT FALSE, -- Exploited bug post-termination?
    exfiltrated_blueprint_category VARCHAR(150),   -- e.g., Display Shield, Battery Housing, Anodizing Spec
    file_count INTEGER NOT NULL,
    cfaa_violation_count INTEGER DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 5. SUBPOENA MATCHES: Tracking Targets for Immediate Automated Subpoenas
CREATE TABLE subpoena_matches (
    match_id INTEGER PRIMARY KEY AUTOINCREMENT,
    whistleblower_number VARCHAR(100) DEFAULT '17684-273-411-436',
    targeted_entity VARCHAR(200) NOT NULL,        -- e.g., SoftBank, Amazon, MGX, Y Combinator
    subpoena_category VARCHAR(50) NOT NULL,       -- e.g., Corporate Anchor, Sovereign Wealth, VC
    required_custodian VARCHAR(200) NOT NULL,     -- e.g., Sam Altman, Kirsty Nathoo, Jony Ive
    associated_capital_round_value DECIMAL(18, 2),-- Value of funding round aligned with theft
    transaction_close_date DATE,
    mandatory_search_strings TEXT,                -- Boolean parameters
    subpoena_status VARCHAR(50) DEFAULT 'DRAFTED', -- Drafted, Issued, Complied, Contested
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ==============================================================================
-- FORENSIC INTELLIGENCE VIEWS FOR AUTOMATED CORRELATION
-- ==============================================================================

-- VIEW A: High-Risk Exfiltration Targets (Mapping Inside Moles)
CREATE VIEW view_high_risk_exfiltration_targets AS
SELECT 
    e.employee_id,
    e.first_name || ' ' || e.last_name AS employee_full_name,
    e.former_apple_title,
    e.current_openai_title,
    d.serial_number AS laptop_serial,
    d.retained_post_departure,
    c.platform_name AS encrypted_app,
    c.recipient_name AS target_recruited,
    c.recipient_status AS target_status_at_time
FROM employees e
JOIN devices d ON e.employee_id = d.employee_id
LEFT JOIN encrypted_channels c ON e.employee_id = c.sender_employee_id
WHERE d.retained_post_departure = 1 
   OR c.platform_name IN ('LINE', 'Signal')
ORDER BY e.last_name;

-- VIEW B: The Valuation-Intrusion Correlation Map (Rule 10b-5 Timing Engine)
CREATE VIEW view_valuation_intrusion_correlation AS
SELECT 
    f.intrusion_timestamp AS exfiltration_date,
    e.first_name || ' ' || e.last_name AS exfiltrator_name,
    f.exfiltrated_blueprint_category AS stolen_ip,
    s.targeted_entity AS funding_source,
    s.associated_capital_round_value AS capital_round_value,
    s.transaction_close_date AS valuation_pump_date
FROM forensic_logs f
JOIN employees e ON f.employee_id = e.employee_id
CROSS JOIN subpoena_matches s
WHERE f.intrusion_timestamp BETWEEN DATE(s.transaction_close_date, '-60 days') AND s.transaction_close_date
  AND s.subpoena_status = 'ISSUED'
ORDER BY f.intrusion_timestamp DESC;

-- VIEW C: Unauthorized Post-Employment IP Leaks & CFAA violations
CREATE VIEW view_unauthorized_ip_leaks AS
SELECT 
    e.first_name || ' ' || e.last_name AS deponent,
    d.device_type,
    f.network_storage_path AS unauthorized_folder_accessed,
    f.intrusion_timestamp AS access_time,
    f.bytes_downloaded,
    f.authentication_vulnerability_used AS backdoor_exploited,
    e.line_username
FROM employees e
JOIN forensic_logs f ON e.employee_id = f.employee_id
JOIN devices d ON f.device_id = d.device_id
WHERE f.intrusion_timestamp > e.termination_date
ORDER BY f.intrusion_timestamp ASC;