"""SQLite schema for graphlemon's own structured storage.

U-001 scope: the 15 platform entities from docs/sdd/01-M-模型设计.md §1.0-1.14
(PH0-PH4). `deployment`/`deploy_health_signal` (§1.15/§1.16, added here in U-034,
2026-08-02) are PH5-only -- 17 tables total as of PH5.

Interpretive conventions used below, where 01-M leaves the physical type implicit:
- Booleans are stored as INTEGER CHECK(x IN (0, 1)) -- sqlite3 has no native bool type.
- A `*_at` column that records "when this row was created" (or, for skill_iteration,
  when the row's one-shot event happened) is NOT NULL DEFAULT CURRENT_TIMESTAMP.
  A `*_at` column that records a later lifecycle event which may not have happened
  yet (started_at, completed_at, purged_at, ...) is nullable with no default -- the
  application sets it only when that event actually occurs.
- JSON-typed columns (graph_manifest, nodes, edges, payload, ...) are stored as TEXT;
  (de)serialization is an application-layer concern, not enforced by this schema.
- task.assigned_org_node is documented as "not null, fk" but there is no standalone
  org-node lookup table to reference (planner/coder/validator only exist as entries
  inside org_graph_definition.nodes, a JSON column) -- implemented as a CHECK enum
  instead, matching work_graph_run.org_node / skill.owner_org_node / agent_profile.org_node.
- bug_record.root_cause_category gained 'core_engine' in U-034 (2026-08-02, PH5) --
  05 §5.8: self-target scenarios (`.claude/agents/*.md` identity file changes, Graph
  Engine scheduler/executor core logic changes, this schema's own changes), routed by
  `c_org_loop_route` to `p_deploy_supervisor_blue_green` instead of fast_lane/full_replan.
- bug_record and skill have no created_at in 01-M (skill is Git-versioned, so git log
  covers "when"; bug_record has no such backing and this looks like a possible spec
  gap, but adding an undocumented column is out of scope for U-001 -- flagged, not fixed).
- deployment.previous_commit_hash/previous_instance_dir are "not null" per 01-M 1.15,
  but the very first deployment ever run has no prior `finalized` deployment to point
  at -- that bootstrap case is c_deploy_supervisor_stage's problem (U-037), not this
  schema's: it must supply the currently-running commit/checkout dir as the "previous"
  values on that first call, not leave the schema-level NOT NULL as aspirational.
"""

import sqlite3

TABLE_NAMES = (
    "project",
    "org_graph_definition",
    "skill",
    "task",
    "work_graph_run",
    "work_node_execution",
    "artifact",
    "bug_record",
    "skill_iteration",
    "agent_profile",
    "budget_guardrail_config",
    "org_concurrency_config",
    "execution_event",
    "execution_log_file",
    "log_retention_config",
    "deployment",
    "deploy_health_signal",
)

SCHEMA_SQL = """
-- 1.0 project
CREATE TABLE project (
    project_id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    target_folder_path TEXT NOT NULL UNIQUE,
    has_git_repo INTEGER NOT NULL CHECK (has_git_repo IN (0, 1)),
    graphlemon_subdir_initialized INTEGER NOT NULL DEFAULT 0
        CHECK (graphlemon_subdir_initialized IN (0, 1)),
    status TEXT NOT NULL CHECK (status IN ('active', 'archived')),
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 1.1 org_graph_definition
CREATE TABLE org_graph_definition (
    graph_id TEXT PRIMARY KEY,
    version TEXT NOT NULL,
    nodes TEXT NOT NULL,
    edges TEXT NOT NULL,
    file_path TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('draft', 'active', 'deprecated'))
);

-- 1.7 skill (created before task/work_node_execution: work_node_execution.skill_ref
-- references it)
CREATE TABLE skill (
    skill_id TEXT PRIMARY KEY,
    owner_org_node TEXT NOT NULL CHECK (owner_org_node IN ('planner', 'coder', 'validator')),
    file_path TEXT NOT NULL,
    current_git_commit_sha TEXT NOT NULL,
    source TEXT NOT NULL CHECK (source IN ('migrated_from_hermes', 'new', 'auto_iterated')),
    migrated_from_profile TEXT CHECK (migrated_from_profile IN ('code', 'planning', 'qa')),
    status TEXT NOT NULL CHECK (status IN ('active', 'archived'))
);

-- 1.2 task
CREATE TABLE task (
    task_id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES project(project_id),
    goal_text TEXT NOT NULL,
    task_type TEXT NOT NULL CHECK (task_type IN ('goal', 'bug_fix')),
    origin_ref TEXT,
    created_via TEXT NOT NULL DEFAULT 'api' CHECK (created_via IN ('api', 'human_console')),
    status TEXT NOT NULL CHECK (status IN (
        'queued', 'planning_brd_prd_sdd', 'planning_failed',
        'coding', 'coding_failed', 'validating',
        'routed_fast_lane', 'routed_full_replan',
        'blocked_pending_human', 'done'
    )),
    route TEXT CHECK (route IN ('fast_lane', 'full_replan')),
    loop_round INTEGER NOT NULL DEFAULT 0,
    blocked_reason TEXT,
    assigned_org_node TEXT NOT NULL CHECK (assigned_org_node IN ('planner', 'coder', 'validator')),
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_task_status ON task(status);
CREATE INDEX idx_task_assigned_org_node ON task(assigned_org_node);
CREATE INDEX idx_task_project_id ON task(project_id);

-- 1.3 work_graph_run
CREATE TABLE work_graph_run (
    run_id TEXT PRIMARY KEY,
    task_id TEXT NOT NULL REFERENCES task(task_id),
    org_node TEXT NOT NULL CHECK (org_node IN ('planner', 'coder', 'validator')),
    graph_manifest TEXT NOT NULL,
    orchestration_script_ref TEXT,
    status TEXT NOT NULL CHECK (status IN (
        'planning', 'running', 'merging', 'verifying',
        'reflecting', 'blocked', 'done', 'failed'
    )),
    started_at TEXT,
    completed_at TEXT,
    result_artifact_refs TEXT
);

-- only one active (non-terminal) run per (task_id, org_node) at a time
CREATE UNIQUE INDEX ux_work_graph_run_active ON work_graph_run(task_id, org_node)
    WHERE status IN ('planning', 'running', 'merging', 'verifying');

-- 1.4 work_node_execution
CREATE TABLE work_node_execution (
    node_exec_id TEXT PRIMARY KEY,
    run_id TEXT NOT NULL REFERENCES work_graph_run(run_id),
    node_id TEXT NOT NULL,
    subagent_type TEXT NOT NULL,
    skill_ref TEXT NOT NULL REFERENCES skill(skill_id),
    input_schema_ref TEXT NOT NULL,
    output_schema_ref TEXT NOT NULL,
    input_payload TEXT NOT NULL,
    output_payload TEXT,
    model_tier TEXT NOT NULL DEFAULT 'standard' CHECK (model_tier IN ('cheap', 'standard', 'premium')),
    status TEXT NOT NULL CHECK (status IN (
        'pending', 'running', 'reflecting', 'retrying',
        'waiting_for_quota', 'blocked', 'done', 'failed'
    )),
    retry_count INTEGER NOT NULL DEFAULT 0,
    reflection_notes TEXT,
    git_worktree_ref TEXT,
    quota_wait_started_at TEXT,
    quota_limit_type TEXT CHECK (quota_limit_type IN ('session', 'weekly', 'opus'))
);

-- 1.5 artifact
CREATE TABLE artifact (
    artifact_id TEXT PRIMARY KEY,
    artifact_type TEXT NOT NULL CHECK (artifact_type IN (
        'brd', 'prd', 'sdd', 'benchmark', 'bug_record', 'code_diff', 'test_report'
    )),
    task_id TEXT NOT NULL REFERENCES task(task_id),
    produced_by_run_id TEXT NOT NULL REFERENCES work_graph_run(run_id),
    file_path TEXT NOT NULL,
    git_commit_sha TEXT NOT NULL,
    depends_on_artifact_ids TEXT,
    validation_status TEXT NOT NULL DEFAULT 'pending' CHECK (validation_status IN ('pending', 'passed', 'failed')),
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX ux_artifact_task_type_commit ON artifact(task_id, artifact_type, git_commit_sha);

-- 1.6 bug_record (skill_iteration_suggestion_id forward-references skill_iteration,
-- created further below -- SQLite does not validate FK target existence at CREATE
-- TABLE time, only at DML time, so table creation order here is fine)
CREATE TABLE bug_record (
    bug_id TEXT PRIMARY KEY,
    task_id TEXT REFERENCES task(task_id),
    source TEXT NOT NULL CHECK (source IN ('validator', 'human')),
    reported_by TEXT,
    description TEXT NOT NULL,
    repro_steps TEXT,
    root_cause_category TEXT CHECK (root_cause_category IN ('planning', 'coder', 'skill_defect', 'core_engine')),
    root_cause_detail TEXT,
    related_artifact_ids TEXT,
    suggested_route TEXT CHECK (suggested_route IN ('fast_lane', 'full_replan')),
    skill_iteration_suggestion_id TEXT REFERENCES skill_iteration(iteration_id),
    status TEXT NOT NULL CHECK (status IN ('open', 'triaging', 'routed', 'resolved'))
);

-- 1.8 skill_iteration
CREATE TABLE skill_iteration (
    iteration_id TEXT PRIMARY KEY,
    skill_id TEXT NOT NULL REFERENCES skill(skill_id),
    triggered_by_bug_id TEXT NOT NULL REFERENCES bug_record(bug_id),
    before_git_commit_sha TEXT NOT NULL,
    after_git_commit_sha TEXT NOT NULL,
    change_summary TEXT NOT NULL,
    applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    rollback_of_iteration_id TEXT REFERENCES skill_iteration(iteration_id)
);

-- 1.9 agent_profile
CREATE TABLE agent_profile (
    org_node TEXT PRIMARY KEY CHECK (org_node IN ('planner', 'coder', 'validator')),
    soul_file_path TEXT NOT NULL,
    agents_file_path TEXT,
    migrated_from_profile TEXT NOT NULL CHECK (migrated_from_profile IN ('planning', 'code', 'qa')),
    current_git_commit_sha TEXT NOT NULL
);

-- 1.10 budget_guardrail_config
CREATE TABLE budget_guardrail_config (
    config_id TEXT PRIMARY KEY DEFAULT 'default',
    scope TEXT NOT NULL CHECK (scope IN ('global', 'per_task', 'per_day')),
    limit_type TEXT NOT NULL CHECK (limit_type IN ('token_count', 'call_count')),
    limit_value INTEGER NOT NULL,
    current_usage INTEGER NOT NULL DEFAULT 0,
    on_exceed_action TEXT NOT NULL DEFAULT 'pause_and_notify',
    updated_by TEXT NOT NULL,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 1.11 org_concurrency_config
CREATE TABLE org_concurrency_config (
    config_id TEXT PRIMARY KEY DEFAULT 'default',
    max_concurrent_goals INTEGER NOT NULL DEFAULT 1,
    updated_by TEXT NOT NULL,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 1.12 execution_event
CREATE TABLE execution_event (
    event_id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES project(project_id),
    task_id TEXT REFERENCES task(task_id),
    run_id TEXT REFERENCES work_graph_run(run_id),
    node_exec_id TEXT REFERENCES work_node_execution(node_exec_id),
    event_type TEXT NOT NULL CHECK (event_type IN (
        'task_status_changed', 'node_status_changed', 'artifact_created',
        'bug_recorded', 'loop_routed', 'budget_updated', 'skill_iterated',
        'log_appended', 'quota_wait_started'
    )),
    payload TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_execution_event_project_created ON execution_event(project_id, created_at);
CREATE INDEX idx_execution_event_task_created ON execution_event(task_id, created_at);

-- 1.13 execution_log_file
CREATE TABLE execution_log_file (
    log_file_id TEXT PRIMARY KEY,
    node_exec_id TEXT NOT NULL UNIQUE REFERENCES work_node_execution(node_exec_id),
    project_id TEXT NOT NULL REFERENCES project(project_id),
    file_path TEXT NOT NULL,
    format TEXT NOT NULL CHECK (format IN ('jsonl', 'raw_text')),
    size_bytes INTEGER NOT NULL DEFAULT 0,
    status TEXT NOT NULL CHECK (status IN ('active', 'closed', 'purged')),
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    purged_at TEXT
);

CREATE INDEX idx_execution_log_file_project ON execution_log_file(project_id);

-- 1.14 log_retention_config
CREATE TABLE log_retention_config (
    config_id TEXT PRIMARY KEY DEFAULT 'default',
    retention_days INTEGER NOT NULL DEFAULT 14,
    max_total_size_mb INTEGER,
    updated_by TEXT NOT NULL,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 1.15 deployment (PH5, U-034, 2026-08-02 -- see docs/supervisor-auto-rollback-design.md)
CREATE TABLE deployment (
    deployment_id TEXT PRIMARY KEY,
    git_commit_hash TEXT NOT NULL,
    previous_commit_hash TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN (
        'staged', 'candidate_starting', 'health_check_running', 'health_check_passed',
        'health_check_failed', 'cutover_in_progress', 'live', 'monitoring',
        'anomaly_detected', 'rollback_in_progress', 'rolled_back', 'rollback_failed',
        'blocked_pending_human', 'monitoring_window_clean', 'finalized'
    )),
    candidate_port INTEGER,
    candidate_db_path TEXT,
    -- Not in supervisor-auto-rollback-design.md §3's field table (found
    -- missing while building U-037's c_deploy_supervisor_stage): the
    -- checkout directory a candidate is actually running from has to be
    -- persisted somewhere, or a Deploy Supervisor restart between staging
    -- and cutover would lose track of it entirely -- the same class of gap
    -- REQ-06 exists to prevent everywhere else. Once a deployment reaches
    -- 'live'/'finalized', this same directory IS what the next deployment's
    -- `previous_instance_dir` bootstraps from (see deploy_stage.py).
    candidate_instance_dir TEXT,
    started_at TEXT,
    cutover_at TEXT,
    monitoring_closed_at TEXT,
    rollback_at TEXT,
    rollback_reason TEXT,
    previous_instance_dir TEXT NOT NULL
);

-- only one non-terminal deployment at a time (01-M 1.15's own constraint) --
-- expression index on a constant, same "global singleton while active" idiom
-- as a partial unique index scoped by a real column, just with no column to
-- scope by here (there's exactly one graphlemon self-target, not one per key)
CREATE UNIQUE INDEX ux_deployment_active ON deployment((1))
    WHERE status NOT IN ('finalized', 'rolled_back', 'blocked_pending_human');

-- 1.16 deploy_health_signal (PH5, U-034, 2026-08-02)
CREATE TABLE deploy_health_signal (
    signal_id TEXT PRIMARY KEY,
    deployment_id TEXT NOT NULL REFERENCES deployment(deployment_id),
    sampled_at TEXT NOT NULL,
    signal_type TEXT NOT NULL CHECK (signal_type IN (
        'process_alive', 'http_health', 'canary_task', 'validator_approval_rate'
    )),
    status TEXT NOT NULL CHECK (status IN ('ok', 'degraded', 'fail')),
    detail TEXT
);

CREATE INDEX idx_deploy_health_signal_deployment_sampled ON deploy_health_signal(deployment_id, sampled_at);
"""


def connect(db_path=":memory:", check_same_thread=True):
    """Open a sqlite3 connection with foreign-key enforcement turned on.

    SQLite disables FK enforcement by default per-connection; every caller needs
    this pragma, so it lives here rather than being repeated at every call site.

    check_same_thread=False is for callers like backend_server.py that share
    one connection across a ThreadingHTTPServer's per-request threads -- the
    caller is then responsible for serializing access itself (a lock), since
    turning this off only disables sqlite3's own thread-identity check, it
    doesn't make concurrent use of one connection object safe by itself.
    """
    conn = sqlite3.connect(db_path, check_same_thread=check_same_thread)
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def create_schema(conn):
    """Create all 17 tables (15 from U-001 + deployment/deploy_health_signal
    from U-034, PH5) + indexes on an open connection."""
    conn.executescript(SCHEMA_SQL)
    conn.commit()