-- Comprehensive fix for missing tables and columns in FTT ecosystem.
-- This migration fixes issues where various APIs and dashboard sections fail.

-- 1. System/API Errors
CREATE TABLE IF NOT EXISTS app_errors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(32) DEFAULT 'error',
    message TEXT NOT NULL,
    file VARCHAR(255) DEFAULT NULL,
    line INT DEFAULT NULL,
    trace LONGTEXT DEFAULT NULL,
    url VARCHAR(512) DEFAULT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_app_errors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(32) DEFAULT 'error',
    message TEXT NOT NULL,
    file VARCHAR(255) DEFAULT NULL,
    line INT DEFAULT NULL,
    trace LONGTEXT DEFAULT NULL,
    url VARCHAR(512) DEFAULT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Screen Sharing
CREATE TABLE IF NOT EXISTS screen_share_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(16) NOT NULL,
    device_id VARCHAR(255) DEFAULT NULL,
    stream_url VARCHAR(512) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    UNIQUE KEY uk_code (code),
    KEY idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_screen_share_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(16) NOT NULL,
    device_id VARCHAR(255) DEFAULT NULL,
    stream_url VARCHAR(512) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    UNIQUE KEY uk_code (code),
    KEY idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Telemetry/Analytics Fixes
ALTER TABLE crash_logs ADD COLUMN IF NOT EXISTS device_id VARCHAR(64) NULL DEFAULT NULL AFTER install_uuid;
ALTER TABLE app_logs ADD COLUMN IF NOT EXISTS device_id VARCHAR(64) NULL DEFAULT NULL AFTER install_uuid;
ALTER TABLE reports ADD COLUMN IF NOT EXISTS device_id VARCHAR(64) NULL DEFAULT NULL AFTER user_token_hash;
ALTER TABLE ftt_crash_logs ADD COLUMN IF NOT EXISTS device_id VARCHAR(64) NULL DEFAULT NULL AFTER install_uuid;
ALTER TABLE ftt_app_logs ADD COLUMN IF NOT EXISTS device_id VARCHAR(64) NULL DEFAULT NULL AFTER install_uuid;
ALTER TABLE ftt_reports ADD COLUMN IF NOT EXISTS device_id VARCHAR(64) NULL DEFAULT NULL AFTER user_token_hash;

-- 4. Cable Test Results
CREATE TABLE IF NOT EXISTS cable_test_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(64) NOT NULL,
    test_type VARCHAR(32) NOT NULL,
    status VARCHAR(16) NOT NULL,
    result_data JSON DEFAULT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_device (device_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_cable_test_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(64) NOT NULL,
    test_type VARCHAR(32) NOT NULL,
    status VARCHAR(16) NOT NULL,
    result_data JSON DEFAULT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_device (device_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Missing Columns in Devices
ALTER TABLE ftt_devices ADD COLUMN IF NOT EXISTS terminate_requested TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE devices ADD COLUMN IF NOT EXISTS terminate_requested TINYINT(1) NOT NULL DEFAULT 0;

-- 6. Missing App Tabs (seed if missing)
INSERT IGNORE INTO app_tabs (tab_id, label, icon, sort_order, content_type, requires_token, created_at, updated_at) VALUES 
('driveClonerTab', 'Drive Cloner', '💾', 22, 'native', 0, NOW(), NOW()),
('workerControlTab', 'Worker Control', '👷', 23, 'native', 0, NOW(), NOW());

INSERT IGNORE INTO ftt_app_tabs (tab_id, label, icon, sort_order, content_type, requires_token, created_at, updated_at) VALUES 
('driveClonerTab', 'Drive Cloner', '💾', 22, 'native', 0, NOW(), NOW()),
('workerControlTab', 'Worker Control', '👷', 23, 'native', 0, NOW(), NOW());

-- 7. E2E Results
CREATE TABLE IF NOT EXISTS e2e_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    test_name VARCHAR(128) NOT NULL,
    status VARCHAR(16) NOT NULL,
    duration_ms INT DEFAULT 0,
    error_message TEXT,
    created_at DATETIME NOT NULL,
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_e2e_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    test_name VARCHAR(128) NOT NULL,
    status VARCHAR(16) NOT NULL,
    duration_ms INT DEFAULT 0,
    error_message TEXT,
    created_at DATETIME NOT NULL,
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;