-- Migration 045: Tab Config V2 — schema consolidation, color/title columns, sub-feature toggles.
-- Safe to re-run: uses ADD COLUMN IF NOT EXISTS, CREATE TABLE IF NOT EXISTS, INSERT IGNORE.

-- ============================================================
-- 1. Add new display/style columns to app_tabs and ftt_app_tabs
-- ============================================================
ALTER TABLE app_tabs
    ADD COLUMN IF NOT EXISTS tab_title VARCHAR(256) NULL AFTER label,
    ADD COLUMN IF NOT EXISTS button_color_hex VARCHAR(32) NULL AFTER tab_title,
    ADD COLUMN IF NOT EXISTS active_color_hex VARCHAR(32) NULL AFTER button_color_hex,
    ADD COLUMN IF NOT EXISTS disabled_color_hex VARCHAR(32) NULL AFTER active_color_hex,
    ADD COLUMN IF NOT EXISTS override_theme_color TINYINT(1) NOT NULL DEFAULT 0 AFTER disabled_color_hex;

ALTER TABLE ftt_app_tabs
    ADD COLUMN IF NOT EXISTS tab_title VARCHAR(256) NULL AFTER label,
    ADD COLUMN IF NOT EXISTS button_color_hex VARCHAR(32) NULL AFTER tab_title,
    ADD COLUMN IF NOT EXISTS active_color_hex VARCHAR(32) NULL AFTER button_color_hex,
    ADD COLUMN IF NOT EXISTS disabled_color_hex VARCHAR(32) NULL AFTER active_color_hex,
    ADD COLUMN IF NOT EXISTS override_theme_color TINYINT(1) NOT NULL DEFAULT 0 AFTER disabled_color_hex;

-- ============================================================
-- 2. Create ftt_tab_feature_toggles
-- ============================================================
CREATE TABLE IF NOT EXISTS ftt_tab_feature_toggles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tab_id VARCHAR(64) NOT NULL,
  feature_key VARCHAR(128) NOT NULL,
  feature_label VARCHAR(256) NOT NULL DEFAULT '',
  feature_description TEXT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uq_tab_feature (tab_id, feature_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 3. Seed sub-feature toggles for Phase-2 tabs
-- ============================================================

-- SSH
INSERT IGNORE INTO ftt_tab_feature_toggles (tab_id, feature_key, feature_label, feature_description, enabled, updated_at) VALUES
('sshTab', 'allow_tunnel', 'SSH Tunneling', 'Allow creating SSH port tunnels', 1, NOW()),
('sshTab', 'allow_sftp', 'SFTP File Access', 'Allow SFTP file transfers over SSH', 1, NOW()),
('sshTab', 'allow_port_forward', 'Port Forwarding', 'Allow SSH port forwarding', 1, NOW()),
('sshTab', 'allow_saved_connections', 'Saved Connections', 'Allow saving and loading SSH connections', 1, NOW());

-- HTTP Client
INSERT IGNORE INTO ftt_tab_feature_toggles (tab_id, feature_key, feature_label, feature_description, enabled, updated_at) VALUES
('httpClientTab', 'allow_custom_headers', 'Custom Headers', 'Allow adding custom HTTP request headers', 1, NOW()),
('httpClientTab', 'allow_request_history', 'Request History', 'Allow saving and viewing request history', 1, NOW()),
('httpClientTab', 'allow_body_edit', 'Request Body Editing', 'Allow editing the HTTP request body', 1, NOW()),
('httpClientTab', 'allow_auth_header', 'Auth Header Support', 'Allow setting Authorization headers', 1, NOW());

-- Network Probe
INSERT IGNORE INTO ftt_tab_feature_toggles (tab_id, feature_key, feature_label, feature_description, enabled, updated_at) VALUES
('networkProbeTab', 'allow_ping', 'Ping', 'Allow ICMP ping tests', 1, NOW()),
('networkProbeTab', 'allow_traceroute', 'Traceroute', 'Allow traceroute network path analysis', 1, NOW()),
('networkProbeTab', 'allow_port_scan', 'Port Scanner', 'Allow TCP port scanning', 1, NOW()),
('networkProbeTab', 'allow_dns_lookup', 'DNS Lookup', 'Allow DNS resolution lookups', 1, NOW());

-- QR Generator
INSERT IGNORE INTO ftt_tab_feature_toggles (tab_id, feature_key, feature_label, feature_description, enabled, updated_at) VALUES
('qrGeneratorTab', 'allow_qr_history', 'QR History', 'Allow saving QR generation history', 1, NOW()),
('qrGeneratorTab', 'allow_qr_export', 'QR Export', 'Allow exporting QR codes as images', 1, NOW()),
('qrGeneratorTab', 'allow_qr_share', 'QR Sharing', 'Allow sharing QR codes', 1, NOW());

-- FTP Client
INSERT IGNORE INTO ftt_tab_feature_toggles (tab_id, feature_key, feature_label, feature_description, enabled, updated_at) VALUES
('ftpTab', 'allow_upload', 'File Upload', 'Allow uploading files to FTP server', 1, NOW()),
('ftpTab', 'allow_download', 'File Download', 'Allow downloading files from FTP server', 1, NOW()),
('ftpTab', 'allow_delete', 'File Deletion', 'Allow deleting files on FTP server', 1, NOW()),
('ftpTab', 'allow_mkdir', 'Create Folders', 'Allow creating directories on FTP server', 1, NOW()),
('ftpTab', 'allow_rename', 'Rename Files', 'Allow renaming files/folders on FTP server', 1, NOW());

-- Vault
INSERT IGNORE INTO ftt_tab_feature_toggles (tab_id, feature_key, feature_label, feature_description, enabled, updated_at) VALUES
('vaultTab', 'allow_export', 'Vault Export', 'Allow exporting vault entries', 1, NOW()),
('vaultTab', 'allow_import', 'Vault Import', 'Allow importing vault entries from file', 1, NOW()),
('vaultTab', 'allow_clipboard_copy', 'Clipboard Copy', 'Allow copying vault values to clipboard', 1, NOW()),
('vaultTab', 'allow_password_generate', 'Password Generator', 'Allow generating secure passwords', 1, NOW());

-- Voice Memo
INSERT IGNORE INTO ftt_tab_feature_toggles (tab_id, feature_key, feature_label, feature_description, enabled, updated_at) VALUES
('voiceMemoTab', 'allow_recording', 'Recording', 'Allow recording new voice memos', 1, NOW()),
('voiceMemoTab', 'allow_playback', 'Playback', 'Allow playback of recorded memos', 1, NOW()),
('voiceMemoTab', 'allow_export', 'Export', 'Allow exporting recordings', 1, NOW()),
('voiceMemoTab', 'allow_transcription', 'Transcription', 'Allow voice-to-text transcription', 1, NOW());

-- Code Lab
INSERT IGNORE INTO ftt_tab_feature_toggles (tab_id, feature_key, feature_label, feature_description, enabled, updated_at) VALUES
('codeLabTab', 'allow_run_python', 'Run Python', 'Allow executing Python code snippets', 1, NOW()),
('codeLabTab', 'allow_run_bash', 'Run Bash', 'Allow executing Bash/shell scripts', 1, NOW()),
('codeLabTab', 'allow_remote_sync', 'Remote Sync', 'Allow syncing code with remote server', 1, NOW()),
('codeLabTab', 'allow_snippet_save', 'Save Snippets', 'Allow saving code snippets', 1, NOW());

-- DNS Explorer
INSERT IGNORE INTO ftt_tab_feature_toggles (tab_id, feature_key, feature_label, feature_description, enabled, updated_at) VALUES
('dnsExplorerTab', 'allow_whois', 'WHOIS Lookup', 'Allow WHOIS domain queries', 1, NOW()),
('dnsExplorerTab', 'allow_reverse_dns', 'Reverse DNS', 'Allow reverse DNS lookups', 1, NOW()),
('dnsExplorerTab', 'allow_record_types', 'All Record Types', 'Allow querying A, MX, TXT, NS, and other record types', 1, NOW());

-- Syslog
INSERT IGNORE INTO ftt_tab_feature_toggles (tab_id, feature_key, feature_label, feature_description, enabled, updated_at) VALUES
('syslogTab', 'allow_syslog_server', 'Syslog Server', 'Allow running embedded UDP syslog receiver', 1, NOW()),
('syslogTab', 'allow_logcat', 'Logcat Reader', 'Allow reading Android logcat output', 1, NOW()),
('syslogTab', 'allow_log_export', 'Log Export', 'Allow exporting captured log entries', 1, NOW()),
('syslogTab', 'allow_log_filter', 'Log Filtering', 'Allow filtering log entries by level/source', 1, NOW());

-- Asset Mapper
INSERT IGNORE INTO ftt_tab_feature_toggles (tab_id, feature_key, feature_label, feature_description, enabled, updated_at) VALUES
('assetMapperTab', 'allow_photo_capture', 'Photo Capture', 'Allow capturing photos for assets', 1, NOW()),
('assetMapperTab', 'allow_export', 'Export Assets', 'Allow exporting asset data as JSON/CSV', 1, NOW()),
('assetMapperTab', 'allow_barcode_scan', 'Barcode Scan', 'Allow scanning barcodes for asset identification', 1, NOW()),
('assetMapperTab', 'allow_location_tag', 'Location Tagging', 'Allow attaching GPS location to assets', 1, NOW());

-- Automation Hub
INSERT IGNORE INTO ftt_tab_feature_toggles (tab_id, feature_key, feature_label, feature_description, enabled, updated_at) VALUES
('automationHubTab', 'allow_create_scripts', 'Create Scripts', 'Allow creating automation scripts', 1, NOW()),
('automationHubTab', 'allow_run_scripts', 'Run Scripts', 'Allow executing automation scripts', 1, NOW()),
('automationHubTab', 'allow_schedule', 'Scheduling', 'Allow scheduling automation runs', 1, NOW()),
('automationHubTab', 'allow_remote_trigger', 'Remote Trigger', 'Allow triggering scripts from remote API', 1, NOW());

-- Webhook
INSERT IGNORE INTO ftt_tab_feature_toggles (tab_id, feature_key, feature_label, feature_description, enabled, updated_at) VALUES
('webhookTab', 'allow_server_start', 'Start Server', 'Allow starting the webhook capture server', 1, NOW()),
('webhookTab', 'allow_export', 'Export Captures', 'Allow exporting captured webhook payloads', 1, NOW()),
('webhookTab', 'allow_replay', 'Replay Requests', 'Allow replaying captured webhook requests', 1, NOW()),
('webhookTab', 'allow_filter', 'Filter Captures', 'Allow filtering webhook captures by headers/body', 1, NOW());

-- ============================================================
-- 4. Add missing feature toggle keys to ftt_app_admin_config and app_admin_config
-- ============================================================

-- Legacy toggles (from migration 025) — ensure in ftt_app_admin_config
INSERT IGNORE INTO ftt_app_admin_config (config_key, config_value, updated_at) VALUES
('camera_enabled', '1', NOW()),
('notes_enabled', '1', NOW()),
('barcode_enabled', '1', NOW()),
('site_timer_enabled', '1', NOW()),
('visit_checklist_enabled', '1', NOW()),
('location_enabled', '1', NOW()),
('documents_enabled', '1', NOW()),
('orchestrator_enabled', '1', NOW()),
('console_enabled', '1', NOW()),
('theme_enabled', '1', NOW()),
('voice_note_enabled', '1', NOW()),
('network_enabled', '1', NOW()),
('wireless_enabled', '1', NOW()),
('testing_enabled', '1', NOW()),
('webserver_enabled', '1', NOW());

-- Phase-2 tab enable toggles
INSERT IGNORE INTO ftt_app_admin_config (config_key, config_value, updated_at) VALUES
('ssh_enabled', '1', NOW()),
('http_client_enabled', '1', NOW()),
('network_probe_enabled', '1', NOW()),
('qr_generator_enabled', '1', NOW()),
('ftp_client_enabled', '1', NOW()),
('vault_enabled', '1', NOW()),
('voice_memo_enabled', '1', NOW()),
('code_lab_enabled', '1', NOW()),
('dns_explorer_enabled', '1', NOW()),
('syslog_enabled', '1', NOW()),
('asset_mapper_enabled', '1', NOW()),
('automation_hub_enabled', '1', NOW()),
('webhook_capture_enabled', '1', NOW());

-- Mirror to app_admin_config (unprefixed) for parity
INSERT IGNORE INTO app_admin_config (config_key, config_value, updated_at) VALUES
('ssh_enabled', '1', NOW()),
('http_client_enabled', '1', NOW()),
('network_probe_enabled', '1', NOW()),
('qr_generator_enabled', '1', NOW()),
('ftp_client_enabled', '1', NOW()),
('vault_enabled', '1', NOW()),
('voice_memo_enabled', '1', NOW()),
('code_lab_enabled', '1', NOW()),
('dns_explorer_enabled', '1', NOW()),
('syslog_enabled', '1', NOW()),
('asset_mapper_enabled', '1', NOW()),
('automation_hub_enabled', '1', NOW()),
('webhook_capture_enabled', '1', NOW());

-- ============================================================
-- 5. Seed Phase-2 tabs into ftt_app_tabs / app_tabs if missing
-- ============================================================
INSERT IGNORE INTO ftt_app_tabs (tab_id, label, icon, sort_order, content_type, content_slug, requires_token, created_at, updated_at) VALUES
('sshTab', 'SSH', 'terminal', 25, 'native', NULL, 0, NOW(), NOW()),
('httpClientTab', 'HTTP Client', 'http', 26, 'native', NULL, 0, NOW(), NOW()),
('networkProbeTab', 'Net Probe', 'network', 27, 'native', NULL, 0, NOW(), NOW()),
('qrGeneratorTab', 'QR Gen', 'qr', 28, 'native', NULL, 0, NOW(), NOW()),
('ftpTab', 'FTP', 'ftp', 29, 'native', NULL, 0, NOW(), NOW()),
('vaultTab', 'Vault', 'lock', 30, 'native', NULL, 0, NOW(), NOW()),
('voiceMemoTab', 'Voice Memo', 'mic', 31, 'native', NULL, 0, NOW(), NOW()),
('codeLabTab', 'Code Lab', 'code', 32, 'native', NULL, 0, NOW(), NOW()),
('dnsExplorerTab', 'DNS', 'dns', 33, 'native', NULL, 0, NOW(), NOW()),
('syslogTab', 'Syslog', 'log', 34, 'native', NULL, 0, NOW(), NOW()),
('assetMapperTab', 'Assets', 'map', 35, 'native', NULL, 0, NOW(), NOW()),
('automationHubTab', 'Automation', 'auto', 36, 'native', NULL, 0, NOW(), NOW()),
('webhookTab', 'Webhook', 'webhook', 37, 'native', NULL, 0, NOW(), NOW());

INSERT IGNORE INTO app_tabs (tab_id, label, icon, sort_order, content_type, content_slug, requires_token, created_at, updated_at) VALUES
('sshTab', 'SSH', 'terminal', 25, 'native', NULL, 0, NOW(), NOW()),
('httpClientTab', 'HTTP Client', 'http', 26, 'native', NULL, 0, NOW(), NOW()),
('networkProbeTab', 'Net Probe', 'network', 27, 'native', NULL, 0, NOW(), NOW()),
('qrGeneratorTab', 'QR Gen', 'qr', 28, 'native', NULL, 0, NOW(), NOW()),
('ftpTab', 'FTP', 'ftp', 29, 'native', NULL, 0, NOW(), NOW()),
('vaultTab', 'Vault', 'lock', 30, 'native', NULL, 0, NOW(), NOW()),
('voiceMemoTab', 'Voice Memo', 'mic', 31, 'native', NULL, 0, NOW(), NOW()),
('codeLabTab', 'Code Lab', 'code', 32, 'native', NULL, 0, NOW(), NOW()),
('dnsExplorerTab', 'DNS', 'dns', 33, 'native', NULL, 0, NOW(), NOW()),
('syslogTab', 'Syslog', 'log', 34, 'native', NULL, 0, NOW(), NOW()),
('assetMapperTab', 'Assets', 'map', 35, 'native', NULL, 0, NOW(), NOW()),
('automationHubTab', 'Automation', 'auto', 36, 'native', NULL, 0, NOW(), NOW()),
('webhookTab', 'Webhook', 'webhook', 37, 'native', NULL, 0, NOW(), NOW());

-- ============================================================
-- 6. Sync any app_tabs rows not in ftt_app_tabs (basic columns only)
-- ============================================================
INSERT IGNORE INTO ftt_app_tabs (tab_id, label, icon, sort_order, content_type, content_slug, requires_token, created_at, updated_at)
SELECT tab_id, label, icon, sort_order, content_type, content_slug, requires_token, created_at, updated_at
FROM app_tabs
WHERE tab_id NOT IN (SELECT tab_id FROM ftt_app_tabs);

-- Sync policy rows
INSERT IGNORE INTO ftt_app_feature_policy (tab_id, visibility, message_type, message_content, min_tier, allow_override_for_developer_admin, updated_at)
SELECT tab_id, visibility, message_type, message_content, min_tier, allow_override_for_developer_admin, updated_at
FROM app_feature_policy
WHERE tab_id NOT IN (SELECT tab_id FROM ftt_app_feature_policy);