-- Migration 049: App Remote Control API tables
-- Creates command queue and result tables for the App Control feature.
-- The Android app polls for pending commands and posts results back.

CREATE TABLE IF NOT EXISTS ftt_app_control_commands (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id   VARCHAR(128)  NOT NULL,
    action      VARCHAR(64)   NOT NULL,
    params      JSON,
    status      ENUM('pending','running','done','error') NOT NULL DEFAULT 'pending',
    created_by  VARCHAR(128)  DEFAULT NULL COMMENT 'dashboard user or API key',
    created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_device_status (device_id, status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_app_control_results (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    command_id  BIGINT        NOT NULL,
    device_id   VARCHAR(128)  NOT NULL,
    result_json JSON          DEFAULT NULL,
    error       TEXT          DEFAULT NULL,
    completed_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_command    (command_id),
    INDEX idx_device     (device_id),
    INDEX idx_completed  (completed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed: ensure the app_control tab exists in ftt_app_tabs
INSERT IGNORE INTO ftt_app_tabs
    (tab_id, label, icon, sort_order, content_type, content_slug, requires_token, created_at, updated_at)
VALUES
    ('appControlTab', 'App Control', '🎮', 95, 'native', NULL, 1, NOW(), NOW());

-- Mirror to app_tabs
INSERT IGNORE INTO app_tabs
    (tab_id, label, icon, sort_order, content_type, content_slug, requires_token, created_at, updated_at)
VALUES
    ('appControlTab', 'App Control', '🎮', 95, 'native', NULL, 1, NOW(), NOW());

-- Seed feature policy for app control tab (requires admin token tier)
INSERT IGNORE INTO ftt_app_feature_policy
    (tab_id, visibility, message_type, min_tier, allow_override_for_developer_admin, updated_at)
VALUES
    ('appControlTab', 'show', 'none', 'admin', 1, NOW());

INSERT IGNORE INTO app_feature_policy
    (tab_id, visibility, message_type, min_tier, allow_override_for_developer_admin, updated_at)
VALUES
    ('appControlTab', 'show', 'none', 'admin', 1, NOW());