-- Nodes: store versioned facts as rows. One logical entity = many versions.
CREATE TABLE IF NOT EXISTS nodes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  logical_id TEXT NOT NULL,          -- e.g. REQ-BUILD-001
  type TEXT NOT NULL,                -- Requirement | Decision | SLO | ...
  group_id TEXT NOT NULL,            -- phoenix/canonical or phoenix/experiment/branch
  version INTEGER NOT NULL,          -- 1..N
  status TEXT NOT NULL,              -- draft|accepted|active|deprecated
  valid_from TEXT NOT NULL,          -- ISO datetime
  valid_to TEXT,                     -- ISO or NULL (if current)
  props TEXT NOT NULL,               -- JSON blob of your fields
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_nodes_identity
ON nodes (logical_id, group_id, version);

CREATE INDEX IF NOT EXISTS idx_nodes_current
ON nodes (logical_id, group_id, type, status);

-- Edges: optionally versioned too; most teams link current->current only.
CREATE TABLE IF NOT EXISTS edges (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  src_logical_id TEXT NOT NULL,
  dst_logical_id TEXT NOT NULL,
  type TEXT NOT NULL,                -- e.g. FOR, SUPERSEDES, PART_OF
  group_id TEXT NOT NULL,
  version INTEGER NOT NULL,          -- edge version (usually matches src/dst current versions)
  status TEXT NOT NULL,              -- accepted|active
  valid_from TEXT NOT NULL,
  valid_to TEXT,
  props TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_edges_lookup
ON edges (src_logical_id, dst_logical_id, group_id, type);

-- "Current" view: latest accepted/active version per (logical_id, group_id).
DROP VIEW IF EXISTS nodes_current;
CREATE VIEW nodes_current AS
WITH picks AS (
  SELECT
    logical_id, group_id, MAX(version) AS v
  FROM nodes
  WHERE valid_to IS NULL
    AND status IN ('accepted','active')
  GROUP BY logical_id, group_id
)
SELECT n.*
FROM nodes n
JOIN picks p
  ON n.logical_id = p.logical_id
 AND n.group_id  = p.group_id
 AND n.version   = p.v;
