-- ============================================================================
-- 新数据库 Schema 定义 (v4 重构)
-- ============================================================================
-- 设计理念：
--   1. 保留完整的原始数据（worker返回的所有字段）
--   2. collection_task 既是任务列表也是执行记录，用 attempt 追踪重试
--   3. 不做预计算，traffic 文件路径存为 JSON 数组，按需重算流量
--   4. 运行时代码直接读取新表并在查询层按需计算分析结果
--   5. 不存经过判断的受限状态，只存 worker 原始 success/failed
--
-- 运行库只创建基础表和索引，不创建兼容视图/投影
-- ============================================================================

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = OFF;

-- ============================================================================
-- 表 1: collection_task — 采集任务表（核心）
-- 既是任务列表也是执行记录。复合主键 (package_name, batch_tag, run_kind, attempt)
-- ============================================================================
CREATE TABLE IF NOT EXISTS collection_task (
    -- 复合主键
    package_name              TEXT    NOT NULL,
    batch_tag                 TEXT    NOT NULL,          -- 批次标签，如 '2026-6-4'
    run_kind                  TEXT    NOT NULL DEFAULT 'ranking'
                                      CHECK(run_kind IN ('ranking', 'block', 'model', 'manual')),
    attempt                   INTEGER NOT NULL DEFAULT 1,-- 采集轮次（重试自增）

    -- 任务基本信息
    task_key                  TEXT,                      -- 任务唯一 key
    app_name                  TEXT,                      -- 应用显示名称
    app_magic_label           TEXT,                      -- 应用魔法标签（关联同一应用的不同变体）

    -- 任务类型标识
    is_new_app                INTEGER DEFAULT 0,         -- ranking任务: 新增应用(1) / 更新应用(0)

    -- 任务状态
    task_status               TEXT    DEFAULT 'pending', -- 'pending' / 'running' / 'completed'
    execution_status          TEXT,                      -- worker原始状态: 'success'/'failed'/'stop'

    -- Worker信息
    worker_id                 TEXT,

    -- 时间信息
    created_at                TEXT    DEFAULT (datetime('now', 'localtime')),
    started_at                TEXT,                      -- 任务开始时间
    completed_at              TEXT,                      -- 任务完成时间

    -- 时长统计（worker返回）
    duration_seconds          REAL,
    download_duration_seconds REAL,
    execution_duration_seconds REAL,
    analysis_duration_seconds REAL,

    -- 错误信息（worker返回的原始错误）
    error_category            TEXT,                      -- 'INFRA_ERROR'/'DOWNLOAD_ERROR'/'APP_ERROR'/'BUSINESS_ERROR'
    error_code                INTEGER,                   -- 错误代码
    error_reason              TEXT,                      -- 错误原因
    error_details             TEXT,                      -- 详细错误信息
    crashed_source            TEXT,                      -- 导致崩溃的下载源

    -- 执行统计（worker返回的原始字段）
    droidbot_steps            INTEGER DEFAULT 0,
    gui_agent_steps           INTEGER DEFAULT 0,
    total_steps               INTEGER DEFAULT 0,
    num_nodes                 INTEGER DEFAULT 0,         -- UI节点数
    num_reached_activities    INTEGER DEFAULT 0,         -- 到达的activity数量
    app_num_total_activities  INTEGER DEFAULT 0,         -- 应用总activity数量

    -- 流量统计（worker返回的原始数据）
    total_traffic_bytes       INTEGER DEFAULT 0,
    self_traffic_bytes        INTEGER DEFAULT 0,
    server_traffic_bytes      INTEGER DEFAULT 0,
    unrecognized_traffic_bytes INTEGER DEFAULT 0,
    model_flow_count          INTEGER DEFAULT 0,
    model_traffic_bytes       INTEGER DEFAULT 0,

    -- Metrics（worker返回的额外指标）
    login_count               INTEGER DEFAULT 0,
    register_count            INTEGER DEFAULT 0,
    stuck_reason_code         INTEGER,
    guiagent_message          TEXT,
    scenario_triggered        INTEGER DEFAULT 0,

    -- 下载信息
    download_source           TEXT,                      -- 'google_play' / 'local'
    is_retry                  INTEGER DEFAULT 0,

    -- 执行追踪
    exit_code                 INTEGER,
    trace_json                TEXT,                      -- 执行轨迹 JSON

    -- 流量文件路径列表（JSON数组）
    traffic_file_paths        TEXT,                      -- ['\\\\lfs.../pkg/...', ...]

    PRIMARY KEY (package_name, batch_tag, run_kind, attempt)
);

CREATE INDEX IF NOT EXISTS idx_collection_task_status
    ON collection_task(task_status, run_kind, batch_tag);
CREATE INDEX IF NOT EXISTS idx_collection_task_execution
    ON collection_task(execution_status, run_kind);
CREATE INDEX IF NOT EXISTS idx_collection_task_pkg
    ON collection_task(package_name, run_kind);
CREATE INDEX IF NOT EXISTS idx_collection_task_time
    ON collection_task(created_at);
CREATE INDEX IF NOT EXISTS idx_collection_task_worker
    ON collection_task(worker_id, completed_at);
CREATE INDEX IF NOT EXISTS idx_collection_task_magic
    ON collection_task(app_magic_label);
CREATE INDEX IF NOT EXISTS idx_collection_task_batch
    ON collection_task(batch_tag, run_kind);

-- ============================================================================
-- 表 2: app_catalog — 应用目录元数据
-- 应用的分类、标签、优先级等管理信息（与采集结果分离）
-- ============================================================================
CREATE TABLE IF NOT EXISTS app_catalog (
    package_name    TEXT PRIMARY KEY,
    app_name        TEXT,

    -- 分类管理
    batch_tags      TEXT,                        -- JSON数组：完整的批次标签列表
    app_magic_label TEXT,                        -- 应用魔法标签
    last_updated    TEXT,                        -- 应用商店最后更新时间
    country_code    TEXT,                        -- 采集国家/地区
    device_type     TEXT,                        -- emulator / physical / any
    task_payload_json TEXT,                      -- 下发给 worker 的完整任务 payload
    last_update_interval_days INTEGER DEFAULT 0, -- 版本更新时间间隔

    -- 优先级管理
    task_queue      TEXT    DEFAULT 'default',   -- 'default' / 'priority'
    task_priority   INTEGER DEFAULT 50,
    source_order    INTEGER,                     -- 在源列表中的顺序

    -- 状态标记
    is_active       INTEGER DEFAULT 1,           -- 是否激活
    is_blocked      INTEGER DEFAULT 0,           -- 是否被屏蔽

    -- 应用信息
    category        TEXT,                        -- 应用分类
    downloads       INTEGER,                     -- 下载量

    created_at      TEXT    DEFAULT (datetime('now', 'localtime')),
    updated_at      TEXT    DEFAULT (datetime('now', 'localtime'))
);

CREATE INDEX IF NOT EXISTS idx_app_catalog_priority
    ON app_catalog(task_priority DESC);
CREATE INDEX IF NOT EXISTS idx_app_catalog_magic
    ON app_catalog(app_magic_label);
CREATE INDEX IF NOT EXISTS idx_app_catalog_active
    ON app_catalog(is_active, source_order);

-- ============================================================================
-- 表 3: apk_registry — APK文件注册表
-- 结构与原系统完全一致（由 apk_cloud/registry.py 维护），此处仅用于迁移数据落地
-- ============================================================================
CREATE TABLE IF NOT EXISTS apk_registry (
    package_name   TEXT PRIMARY KEY,
    download_date  TEXT NOT NULL,
    download_time  REAL NOT NULL,
    local_dir      TEXT,
    smb_dir        TEXT,
    version_name   TEXT,
    source         TEXT,
    apk_files_json TEXT,
    updated_at     REAL NOT NULL
);

-- ============================================================================
-- 表 4: worker_activity_summary — Worker状态时间分布（每小时汇总）
-- ============================================================================
CREATE TABLE IF NOT EXISTS worker_activity_summary (
    worker_id                TEXT    NOT NULL,
    stat_date                TEXT    NOT NULL,   -- YYYY-MM-DD
    stat_hour                INTEGER NOT NULL,   -- 0-23（-1 表示全天汇总）

    -- 状态时长统计（秒）
    idle_duration_seconds    INTEGER DEFAULT 0,
    busy_duration_seconds    INTEGER DEFAULT 0,
    offline_duration_seconds INTEGER DEFAULT 0,

    -- 任务统计
    task_count               INTEGER DEFAULT 0,
    success_count            INTEGER DEFAULT 0,
    failed_count             INTEGER DEFAULT 0,

    PRIMARY KEY (worker_id, stat_date, stat_hour)
);

CREATE INDEX IF NOT EXISTS idx_worker_activity_date
    ON worker_activity_summary(stat_date DESC);

-- 运行时代码直接查询真实表，不创建投影或视图。