-- TaskPilot 基线结构：任务、运行记录、Python 镜像与 uv 设置。
-- 这是全新起点（不再兼容旧库）：任务不再持久化并发/重试策略，运行触发只有定时与手动。

CREATE TABLE IF NOT EXISTS tasks (
    id TEXT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    cron TEXT NOT NULL,
    timezone TEXT NOT NULL,
    enabled INTEGER NOT NULL CHECK (enabled IN (0, 1)),
    timeout_secs INTEGER NOT NULL CHECK (timeout_secs > 0),
    action_type TEXT NOT NULL
        CHECK (action_type IN ('script', 'http', 'process')),
    action_json TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_tasks_updated_at
    ON tasks(updated_at DESC);

CREATE INDEX IF NOT EXISTS idx_tasks_enabled
    ON tasks(enabled);

CREATE INDEX IF NOT EXISTS idx_tasks_action_type
    ON tasks(action_type);

CREATE TABLE IF NOT EXISTS task_runs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    trigger_type TEXT NOT NULL
        CHECK (trigger_type IN ('schedule', 'manual')),
    status TEXT NOT NULL
        CHECK (status IN (
            'queued', 'running', 'succeeded', 'failed',
            'timed_out', 'cancelled', 'skipped'
        )),
    started_at TEXT,
    finished_at TEXT,
    duration_ms INTEGER,
    exit_code INTEGER,
    error_message TEXT,
    created_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_task_runs_task_created
    ON task_runs(task_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_task_runs_status
    ON task_runs(status);

CREATE TABLE IF NOT EXISTS python_mirrors (
    id TEXT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    index_url TEXT NOT NULL,
    trusted_host TEXT,
    enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0, 1)),
    is_default INTEGER NOT NULL DEFAULT 0 CHECK (is_default IN (0, 1)),
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

-- 同一时间只允许一个默认镜像
CREATE UNIQUE INDEX IF NOT EXISTS idx_python_mirrors_default
    ON python_mirrors(is_default)
    WHERE is_default = 1;

CREATE UNIQUE INDEX IF NOT EXISTS idx_python_mirrors_index_url
    ON python_mirrors(index_url);

INSERT OR IGNORE INTO python_mirrors
    (id, name, index_url, trusted_host, enabled, is_default, created_at, updated_at)
VALUES
    (
        'tsinghua',
        '清华 TUNA',
        'https://pypi.tuna.tsinghua.edu.cn/simple',
        NULL,
        1,
        1,
        strftime('%Y-%m-%dT%H:%M:%fZ', 'now'),
        strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
    ),
    (
        'aliyun',
        '阿里云',
        'https://mirrors.aliyun.com/pypi/simple/',
        NULL,
        1,
        0,
        strftime('%Y-%m-%dT%H:%M:%fZ', 'now'),
        strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
    ),
    (
        'ustc',
        '中科大',
        'https://pypi.mirrors.ustc.edu.cn/simple/',
        NULL,
        1,
        0,
        strftime('%Y-%m-%dT%H:%M:%fZ', 'now'),
        strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
    ),
    (
        'pypi',
        'PyPI 官方',
        'https://pypi.org/simple',
        NULL,
        1,
        0,
        strftime('%Y-%m-%dT%H:%M:%fZ', 'now'),
        strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
    );

-- uv 只认平台自带的副本（程序目录 uv）或用户指定的路径，不再读系统 PATH。
-- extra_env / mirror_url 分别存额外的 UV_* 环境变量和下载镜像。
CREATE TABLE IF NOT EXISTS uv_settings (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    source TEXT NOT NULL CHECK (source IN ('managed', 'custom')),
    custom_path TEXT,
    extra_env TEXT NOT NULL DEFAULT '{}',
    mirror_url TEXT,
    updated_at TEXT NOT NULL
);

INSERT OR IGNORE INTO uv_settings (id, source, custom_path, extra_env, updated_at)
VALUES (1, 'managed', NULL, '{}', strftime('%Y-%m-%dT%H:%M:%fZ', 'now'));