-- PostgreSQL 17 schema. 语句以分号分隔，由 database._apply_schema 逐条执行，注释里不要出现分号
-- 多校隔离：knowledge / tasks / system_accounts / learned_entries / skills 都带 school_id，settings 是实例级配置不带
-- updated_at 由应用在 UPDATE 时显式写 NOW()

CREATE TABLE IF NOT EXISTS knowledge (
    school_id VARCHAR(64) NOT NULL DEFAULT 'default',
    id VARCHAR(64) NOT NULL,
    name VARCHAR(255) NOT NULL,
    content JSONB NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (school_id, id)
);

CREATE TABLE IF NOT EXISTS skills (
    school_id VARCHAR(64) NOT NULL DEFAULT 'default',
    id VARCHAR(64) NOT NULL,
    version VARCHAR(32) NOT NULL,
    knowledge_id VARCHAR(64) NOT NULL,
    status VARCHAR(32) DEFAULT 'draft',
    content JSONB NOT NULL,
    created_from_task_id VARCHAR(64),
    verified_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, version)
);

CREATE INDEX IF NOT EXISTS idx_skills_knowledge ON skills (knowledge_id);

CREATE TABLE IF NOT EXISTS tasks (
    id VARCHAR(64) PRIMARY KEY,
    school_id VARCHAR(64) NOT NULL DEFAULT 'default',
    student_id VARCHAR(64) DEFAULT 'default',
    knowledge_id VARCHAR(64) NOT NULL,
    status VARCHAR(32) DEFAULT 'collecting',
    current_step VARCHAR(128),
    inputs JSONB,
    artifacts JSONB,
    skill_version VARCHAR(32),
    browser_profile_path VARCHAR(512),
    pause_reason TEXT,
    next_check_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_tasks_school ON tasks (school_id, updated_at);

CREATE INDEX IF NOT EXISTS idx_tasks_student ON tasks (student_id);

CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks (status);

CREATE TABLE IF NOT EXISTS run_logs (
    id BIGSERIAL PRIMARY KEY,
    task_id VARCHAR(64) NOT NULL,
    step_id VARCHAR(128),
    agent_history JSONB,
    screenshots JSONB,
    verdict VARCHAR(32),
    started_at TIMESTAMP NOT NULL DEFAULT NOW(),
    finished_at TIMESTAMP NULL
);

CREATE INDEX IF NOT EXISTS idx_run_logs_task ON run_logs (task_id);

CREATE TABLE IF NOT EXISTS settings (
    "key" VARCHAR(128) PRIMARY KEY,
    "value" TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS system_accounts (
    id BIGSERIAL PRIMARY KEY,
    school_id VARCHAR(64) NOT NULL DEFAULT 'default',
    student_id VARCHAR(64) NOT NULL,
    system_id VARCHAR(64) NOT NULL,
    role VARCHAR(32) NOT NULL DEFAULT '',
    username VARCHAR(128) NOT NULL,
    password_enc TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    UNIQUE (school_id, student_id, system_id)
);

CREATE TABLE IF NOT EXISTS learned_entries (
    id BIGSERIAL PRIMARY KEY,
    school_id VARCHAR(64) NOT NULL DEFAULT 'default',
    knowledge_id VARCHAR(64) NOT NULL,
    business_id VARCHAR(64) NOT NULL DEFAULT '',
    "key" VARCHAR(128) NOT NULL,
    "value" TEXT NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'draft',
    category VARCHAR(16) NOT NULL DEFAULT 'note',
    observations INT NOT NULL DEFAULT 1,
    source_task_id VARCHAR(64),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    verified_at TIMESTAMP NULL,
    verified_by VARCHAR(16) NULL,
    UNIQUE (school_id, knowledge_id, business_id, "key", status)
);

CREATE INDEX IF NOT EXISTS idx_learned_task ON learned_entries (source_task_id);

-- 试用制（D19）：草稿不再压着等人审，直接作为「待验证线索」注入提示词，用运行结果当裁判。
-- 每次任务结束时 Agent 逐条回答哪条与页面相符、哪条不符，证据记在这里。
ALTER TABLE learned_entries ADD COLUMN IF NOT EXISTS trials INT NOT NULL DEFAULT 0;
ALTER TABLE learned_entries ADD COLUMN IF NOT EXISTS confirmed INT NOT NULL DEFAULT 0;
ALTER TABLE learned_entries ADD COLUMN IF NOT EXISTS contradicted INT NOT NULL DEFAULT 0;
ALTER TABLE learned_entries ADD COLUMN IF NOT EXISTS last_trial_at TIMESTAMP NULL;
ALTER TABLE learned_entries ADD COLUMN IF NOT EXISTS last_outcome TEXT NOT NULL DEFAULT '';

-- 每次注入与回执的流水，用于追溯某条知识是被哪次任务证实或证伪的
CREATE TABLE IF NOT EXISTS learned_trials (
    id BIGSERIAL PRIMARY KEY,
    school_id VARCHAR(64) NOT NULL DEFAULT 'default',
    entry_id BIGINT NOT NULL REFERENCES learned_entries(id) ON DELETE CASCADE,
    task_id VARCHAR(64) NOT NULL,
    outcome VARCHAR(16) NOT NULL DEFAULT 'unused',
    note TEXT NOT NULL DEFAULT '',
    at TIMESTAMP NOT NULL DEFAULT NOW(),
    UNIQUE (entry_id, task_id)
);

CREATE INDEX IF NOT EXISTS idx_learned_trials_entry ON learned_trials (entry_id);