-- App tabs registry and per-tab feature policy.
-- Tabs: single source of truth for tab bar; policy: visibility, disabled, message, min_tier.
-- Create both unprefixed and ftt_ prefixed for schema compatibility.

-- Tab registry (unprefixed)
CREATE TABLE IF NOT EXISTS app_tabs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tab_id VARCHAR(64) NOT NULL,
  label VARCHAR(128) NOT NULL,
  icon VARCHAR(32) NOT NULL DEFAULT '',
  sort_order INT NOT NULL DEFAULT 0,
  content_type VARCHAR(32) NOT NULL DEFAULT 'native',
  content_slug VARCHAR(128) NULL,
  requires_token TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uk_app_tabs_tab_id (tab_id),
  INDEX idx_app_tabs_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tab registry (prefixed)
CREATE TABLE IF NOT EXISTS ftt_app_tabs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tab_id VARCHAR(64) NOT NULL,
  label VARCHAR(128) NOT NULL,
  icon VARCHAR(32) NOT NULL DEFAULT '',
  sort_order INT NOT NULL DEFAULT 0,
  content_type VARCHAR(32) NOT NULL DEFAULT 'native',
  content_slug VARCHAR(128) NULL,
  requires_token TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uk_ftt_app_tabs_tab_id (tab_id),
  INDEX idx_ftt_app_tabs_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Feature policy: one row per tab + global config key
-- visibility: show | hide | disabled
-- message_type: none | custom
-- min_tier: trial | user | member | developer | admin
CREATE TABLE IF NOT EXISTS app_feature_policy (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tab_id VARCHAR(64) NOT NULL,
  visibility VARCHAR(16) NOT NULL DEFAULT 'show',
  message_type VARCHAR(16) NOT NULL DEFAULT 'none',
  message_content TEXT NULL,
  min_tier VARCHAR(16) NOT NULL DEFAULT 'trial',
  allow_override_for_developer_admin TINYINT(1) NOT NULL DEFAULT 1,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uk_app_feature_policy_tab_id (tab_id),
  INDEX idx_app_feature_policy_visibility (visibility)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_app_feature_policy (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tab_id VARCHAR(64) NOT NULL,
  visibility VARCHAR(16) NOT NULL DEFAULT 'show',
  message_type VARCHAR(16) NOT NULL DEFAULT 'none',
  message_content TEXT NULL,
  min_tier VARCHAR(16) NOT NULL DEFAULT 'trial',
  allow_override_for_developer_admin TINYINT(1) NOT NULL DEFAULT 1,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uk_ftt_app_feature_policy_tab_id (tab_id),
  INDEX idx_ftt_app_feature_policy_visibility (visibility)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Global flag: when 1, tabs with visibility=disabled are still enabled for developer/admin
INSERT IGNORE INTO app_admin_config (config_key, config_value, updated_at) VALUES
('feature_policy_override_developer_admin', '1', NOW());

INSERT IGNORE INTO ftt_app_admin_config (config_key, config_value, updated_at) VALUES
('feature_policy_override_developer_admin', '1', NOW());

-- Seed app_tabs from default app list (same as MainActivity getDefaultTabConfigList)
INSERT IGNORE INTO app_tabs (tab_id, label, icon, sort_order, content_type, content_slug, requires_token, created_at, updated_at) VALUES
('homeTab', 'Home', '🏠', 0, 'native', NULL, 0, NOW(), NOW()),
('devicesTab', 'Devices', '📱', 1, 'native', NULL, 0, NOW(), NOW()),
('networkTab', 'Network', '🌐', 2, 'native', NULL, 0, NOW(), NOW()),
('wirelessTab', 'Wireless', '📶', 3, 'native', NULL, 0, NOW(), NOW()),
('testingTab', 'Testing', '🧪', 4, 'native', NULL, 0, NOW(), NOW()),
('cameraTab', 'Camera', '📹', 5, 'native', NULL, 0, NOW(), NOW()),
('notesTab', 'Notes', '📝', 6, 'native', NULL, 0, NOW(), NOW()),
('featureXTab', 'Theme', '🎨', 7, 'native', NULL, 0, NOW(), NOW()),
('featureYTab', 'Voice Note', '🎤', 8, 'native', NULL, 0, NOW(), NOW()),
('barcodeTab', 'Barcode', '📷', 9, 'native', NULL, 0, NOW(), NOW()),
('siteTimerTab', 'Site Timer', '⏱', 10, 'native', NULL, 0, NOW(), NOW()),
('visitChecklistTab', 'Visit', '☑', 11, 'native', NULL, 0, NOW(), NOW()),
('locationTab', 'Location', '📍', 12, 'native', NULL, 0, NOW(), NOW()),
('documentsTab', 'Documents', '📄', 13, 'native', NULL, 0, NOW(), NOW()),
('settingsTab', 'Settings', '⚙', 14, 'native', NULL, 0, NOW(), NOW()),
('adminTab', 'Admin', '🔐', 15, 'native', NULL, 0, NOW(), NOW()),
('orchestratorTab', 'Orchestrator', '📋', 16, 'native', NULL, 0, NOW(), NOW()),
('blank1Tab', 'Upcoming', '⋯', 17, 'native', NULL, 0, NOW(), NOW()),
('aboutTab', 'About', 'ℹ', 18, 'native', NULL, 0, NOW(), NOW()),
('donateTab', 'Donate', '❤', 19, 'native', NULL, 0, NOW(), NOW()),
('consoleTab', 'Console', '📟', 20, 'native', NULL, 0, NOW(), NOW()),
('agentTab', 'Agent', '💬', 21, 'native', NULL, 0, NOW(), NOW());

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;

-- Seed default policy: show all, no message, min_tier trial
INSERT IGNORE INTO app_feature_policy (tab_id, visibility, message_type, message_content, min_tier, allow_override_for_developer_admin, updated_at) VALUES
('homeTab','show','none',NULL,'trial',1,NOW()),('devicesTab','show','none',NULL,'trial',1,NOW()),('networkTab','show','none',NULL,'trial',1,NOW()),
('wirelessTab','show','none',NULL,'trial',1,NOW()),('testingTab','show','none',NULL,'trial',1,NOW()),('cameraTab','show','none',NULL,'trial',1,NOW()),
('notesTab','show','none',NULL,'trial',1,NOW()),('featureXTab','show','none',NULL,'trial',1,NOW()),('featureYTab','show','none',NULL,'trial',1,NOW()),
('barcodeTab','show','none',NULL,'trial',1,NOW()),('siteTimerTab','show','none',NULL,'trial',1,NOW()),('visitChecklistTab','show','none',NULL,'trial',1,NOW()),
('locationTab','show','none',NULL,'trial',1,NOW()),('documentsTab','show','none',NULL,'trial',1,NOW()),('settingsTab','show','none',NULL,'trial',1,NOW()),('adminTab','show','none',NULL,'trial',1,NOW()),
('orchestratorTab','show','none',NULL,'trial',1,NOW()),('blank1Tab','show','none',NULL,'trial',1,NOW()),('aboutTab','show','none',NULL,'trial',1,NOW()),
('donateTab','show','none',NULL,'trial',1,NOW()),('consoleTab','show','none',NULL,'trial',1,NOW()),('agentTab','show','none',NULL,'trial',1,NOW());

INSERT IGNORE INTO ftt_app_feature_policy (tab_id, visibility, message_type, message_content, min_tier, allow_override_for_developer_admin, updated_at) VALUES
('homeTab','show','none',NULL,'trial',1,NOW()),('devicesTab','show','none',NULL,'trial',1,NOW()),('networkTab','show','none',NULL,'trial',1,NOW()),
('wirelessTab','show','none',NULL,'trial',1,NOW()),('testingTab','show','none',NULL,'trial',1,NOW()),('cameraTab','show','none',NULL,'trial',1,NOW()),
('notesTab','show','none',NULL,'trial',1,NOW()),('featureXTab','show','none',NULL,'trial',1,NOW()),('featureYTab','show','none',NULL,'trial',1,NOW()),
('barcodeTab','show','none',NULL,'trial',1,NOW()),('siteTimerTab','show','none',NULL,'trial',1,NOW()),('visitChecklistTab','show','none',NULL,'trial',1,NOW()),
('locationTab','show','none',NULL,'trial',1,NOW()),('documentsTab','show','none',NULL,'trial',1,NOW()),('settingsTab','show','none',NULL,'trial',1,NOW()),('adminTab','show','none',NULL,'trial',1,NOW()),
('orchestratorTab','show','none',NULL,'trial',1,NOW()),('blank1Tab','show','none',NULL,'trial',1,NOW()),('aboutTab','show','none',NULL,'trial',1,NOW()),
('donateTab','show','none',NULL,'trial',1,NOW()),('consoleTab','show','none',NULL,'trial',1,NOW()),('agentTab','show','none',NULL,'trial',1,NOW());