-- Task #242 (B1) — Capability 通用框架的 source-controlled DDL。
--
-- 这个文件是本任务唯一的"权威 schema 来源",运行时由 applyCapabilityMigrations()
-- 在 boot 时 idempotent 地执行。原因:
--   1. 4 张新表 + partial unique index + view aliases 都用 IF NOT EXISTS / OR
--      REPLACE,可以重复跑;
--   2. drizzle-kit push 在 interactive prompt 上阻塞,且 partial WHERE 索引、
--      VIEW alias 都不在 drizzle DSL 表达力之内,所以 raw SQL 是唯一 reproducible
--      的 source-of-truth;
--   3. 任何环境(dev/prod/CI)只要这个文件 commit 进 repo,跑 boot 就能拿到一致
--      的 schema。

-- ============================================================ Tables

CREATE TABLE IF NOT EXISTS "capabilities" (
  "id" text PRIMARY KEY NOT NULL,
  "name" text NOT NULL UNIQUE,
  "domain" text NOT NULL,
  "description" text NOT NULL DEFAULT '',
  "current_lifecycle_state" text NOT NULL DEFAULT 'created',
  "bound_network_name" text,
  "meta" jsonb NOT NULL DEFAULT '{}'::jsonb,
  "created_at" timestamptz NOT NULL DEFAULT now(),
  "updated_at" timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS "capabilities_domain_idx" ON "capabilities" ("domain");
CREATE INDEX IF NOT EXISTS "capabilities_state_idx" ON "capabilities" ("current_lifecycle_state");

CREATE TABLE IF NOT EXISTS "capability_lifecycle_events" (
  "id" text PRIMARY KEY NOT NULL,
  "capability_id" text NOT NULL,
  "from_state" text,
  "to_state" text NOT NULL,
  "reason" text NOT NULL,
  "payload" jsonb NOT NULL DEFAULT '{}'::jsonb,
  "created_at" timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS "capability_lifecycle_events_cap_idx" ON "capability_lifecycle_events" ("capability_id");
CREATE INDEX IF NOT EXISTS "capability_lifecycle_events_created_at_idx" ON "capability_lifecycle_events" ("created_at");

CREATE TABLE IF NOT EXISTS "capability_inner_loop_runs" (
  "id" text PRIMARY KEY NOT NULL,
  "capability_id" text NOT NULL,
  "steps_json" jsonb NOT NULL,
  "source_run_id" text,
  "duration_ms" integer,
  "status" text NOT NULL DEFAULT 'ok',
  "created_at" timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS "capability_inner_loop_runs_cap_idx" ON "capability_inner_loop_runs" ("capability_id");
CREATE INDEX IF NOT EXISTS "capability_inner_loop_runs_created_at_idx" ON "capability_inner_loop_runs" ("created_at");

CREATE TABLE IF NOT EXISTS "network_dispatch_violations" (
  "id" text PRIMARY KEY NOT NULL,
  "network_name" text NOT NULL,
  "tool_name" text NOT NULL,
  "args_hash" text NOT NULL,
  "details_json" jsonb NOT NULL,
  "mode" text NOT NULL DEFAULT 'enforced',
  "conversation_id" text,
  "owner_user_id" text,
  "created_at" timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS "network_dispatch_violations_net_idx" ON "network_dispatch_violations" ("network_name");
CREATE INDEX IF NOT EXISTS "network_dispatch_violations_created_at_idx" ON "network_dispatch_violations" ("created_at");
CREATE UNIQUE INDEX IF NOT EXISTS "network_dispatch_violations_uniq_recent" ON "network_dispatch_violations" ("network_name","args_hash","created_at");

-- ============================================================ Partial unique index
-- 漏洞 4 真 idempotent 的关键:并发请求竞争 user-private active variant 时,
-- 第二个 INSERT 必须被 DB 拒绝(SQLSTATE 23505),getOrCreateUserPrivateVariant
-- 的外层 retry 会重新 select winner 行。
CREATE UNIQUE INDEX IF NOT EXISTS "network_versions_uniq_active_private"
  ON "network_versions" ("network_id","private_namespace")
  WHERE "status" = 'active' AND "private_namespace" IS NOT NULL;

-- ============================================================ Capability_* alias views
-- task plan 第 81 行:为 B2-B11 提供新名字读旧表的 view alias。
-- 实现策略:capability 一旦 boundNetworkName 指向 tool_networks.name,就可以
-- 通过这 2 个 view 用 capability 视角读到该 network 的 versions / promotions。
-- 真 capability 自有数据在 capability_data_foundation / capability_inner_loop_runs。

CREATE OR REPLACE VIEW "capability_versions" AS
SELECT
  c."id"                AS "capability_id",
  c."name"              AS "capability_name",
  nv."id"               AS "version_id",
  nv."network_id"       AS "network_id",
  nv."version_label"    AS "version_label",
  nv."status"           AS "status",
  nv."private_namespace" AS "private_namespace",
  nv."built_by"         AS "built_by",
  nv."builder_model_tier" AS "builder_model_tier",
  nv."internal_graph"   AS "internal_graph",
  nv."config"           AS "config",
  nv."created_at"       AS "created_at"
FROM "capabilities" c
JOIN "tool_networks" tn ON tn."name" = c."bound_network_name"
JOIN "network_versions" nv ON nv."network_id" = tn."id";

CREATE OR REPLACE VIEW "capability_evolution_events" AS
SELECT
  c."id"                AS "capability_id",
  c."name"              AS "capability_name",
  np."id"               AS "promotion_id",
  np."network_id"       AS "network_id",
  np."from_variant_id"  AS "from_variant_id",
  np."to_variant_id"    AS "to_variant_id",
  np."reason"           AS "reason",
  np."decided_by"       AS "decided_by",
  np."created_at"       AS "created_at"
FROM "capabilities" c
JOIN "tool_networks" tn ON tn."name" = c."bound_network_name"
JOIN "network_promotions" np ON np."network_id" = tn."id";