-- skills/context/schema/0001_init.sql
-- shepherd v5.0.0 baseline schema.
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

BEGIN;

CREATE TABLE schema_versions (
  version    INTEGER PRIMARY KEY,
  applied_at INTEGER NOT NULL,
  checksum   TEXT NOT NULL
);

CREATE TABLE projects (
  id          TEXT PRIMARY KEY,
  name        TEXT NOT NULL DEFAULT '',
  scope       TEXT NOT NULL DEFAULT '[]' CHECK(json_valid(scope)),
  metadata    TEXT CHECK(metadata IS NULL OR json_valid(metadata)),
  tags        TEXT NOT NULL DEFAULT '[]' CHECK(json_valid(tags)),
  created_at  INTEGER NOT NULL,
  updated_at  INTEGER NOT NULL
);

CREATE TABLE sessions (
  id            TEXT PRIMARY KEY,
  project_id    TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE ON UPDATE CASCADE,
  started_at    INTEGER NOT NULL,
  ended_at      INTEGER,
  agent_role    TEXT,
  sprint_branch TEXT,
  metadata      TEXT CHECK(metadata IS NULL OR json_valid(metadata))
);
CREATE INDEX idx_sessions_project_branch ON sessions(project_id, sprint_branch);

CREATE TABLE profiles_defs (
  id          TEXT PRIMARY KEY,
  project_id  TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE ON UPDATE CASCADE,
  name        TEXT NOT NULL,
  kind        TEXT NOT NULL CHECK(kind IN ('modifier','extension','override')),
  config      TEXT NOT NULL CHECK(json_valid(config)),
  source_path TEXT,
  active      INTEGER NOT NULL DEFAULT 1,
  created_at  INTEGER NOT NULL,
  updated_at  INTEGER NOT NULL,
  UNIQUE(project_id, name)
);

CREATE TABLE mem_entries (
  id          TEXT PRIMARY KEY,
  project_id  TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE ON UPDATE CASCADE,
  kind        TEXT NOT NULL CHECK(kind IN ('doctrine','note','decision','incident','session')),
  title       TEXT NOT NULL,
  body        TEXT NOT NULL,
  tags        TEXT NOT NULL DEFAULT '[]' CHECK(json_valid(tags)),
  pinned      INTEGER NOT NULL DEFAULT 0,
  source_path TEXT,
  created_at  INTEGER NOT NULL,
  updated_at  INTEGER NOT NULL
);
CREATE INDEX idx_mem_project_kind   ON mem_entries(project_id, kind);
CREATE INDEX idx_mem_project_pinned ON mem_entries(project_id, pinned) WHERE pinned = 1;

CREATE TABLE index_concepts (
  id                  TEXT PRIMARY KEY,
  project_id          TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE ON UPDATE CASCADE,
  concept             TEXT NOT NULL,
  canonical_symbol_id TEXT NOT NULL REFERENCES index_symbols(id) ON DELETE CASCADE,
  aliases_to_avoid    TEXT NOT NULL DEFAULT '[]' CHECK(json_valid(aliases_to_avoid)),
  notes               TEXT,
  UNIQUE(project_id, concept)
);

CREATE TABLE index_issues (
  id           TEXT PRIMARY KEY,
  project_id   TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE ON UPDATE CASCADE,
  source       TEXT NOT NULL,
  number       INTEGER NOT NULL,
  title        TEXT NOT NULL,
  state        TEXT NOT NULL,
  labels       TEXT NOT NULL DEFAULT '[]' CHECK(json_valid(labels)),
  milestone    TEXT,
  assignees    TEXT NOT NULL DEFAULT '[]' CHECK(json_valid(assignees)),
  body         TEXT,
  url          TEXT NOT NULL,
  created_at   INTEGER NOT NULL,
  updated_at   INTEGER NOT NULL,
  refreshed_at INTEGER NOT NULL
);
CREATE INDEX idx_issues_project_state     ON index_issues(project_id, state);
CREATE INDEX idx_issues_project_milestone ON index_issues(project_id, milestone);

CREATE TABLE index_prs (
  id           TEXT PRIMARY KEY,
  project_id   TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE ON UPDATE CASCADE,
  source       TEXT NOT NULL,
  number       INTEGER NOT NULL,
  title        TEXT NOT NULL,
  state        TEXT NOT NULL,
  base_branch  TEXT NOT NULL,
  head_branch  TEXT NOT NULL,
  labels       TEXT NOT NULL DEFAULT '[]' CHECK(json_valid(labels)),
  url          TEXT NOT NULL,
  created_at   INTEGER NOT NULL,
  updated_at   INTEGER NOT NULL,
  merged_at    INTEGER,
  refreshed_at INTEGER NOT NULL
);
CREATE INDEX idx_prs_project_state ON index_prs(project_id, state);

CREATE TABLE index_releases (
  id           TEXT PRIMARY KEY,
  project_id   TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE ON UPDATE CASCADE,
  source       TEXT NOT NULL,
  tag          TEXT NOT NULL,
  name         TEXT,
  prerelease   INTEGER NOT NULL DEFAULT 0,
  draft        INTEGER NOT NULL DEFAULT 0,
  body         TEXT,
  url          TEXT NOT NULL,
  published_at INTEGER,
  refreshed_at INTEGER NOT NULL,
  UNIQUE(project_id, source, tag)
);

CREATE TABLE index_milestones (
  id           TEXT PRIMARY KEY,
  project_id   TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE ON UPDATE CASCADE,
  source       TEXT NOT NULL,
  number       INTEGER NOT NULL,
  title        TEXT NOT NULL,
  state        TEXT NOT NULL,
  due_on       INTEGER,
  description  TEXT,
  url          TEXT NOT NULL,
  refreshed_at INTEGER NOT NULL,
  UNIQUE(project_id, source, number)
);

-- Views
CREATE VIEW v_open_issues AS
  SELECT project_id, number, title, state, labels, milestone, assignees, url, updated_at
  FROM index_issues WHERE state = 'open' ORDER BY updated_at DESC;

CREATE VIEW v_drift_risk AS
  SELECT project_id, number, title, milestone, labels
  FROM index_issues
  WHERE state = 'open'
    AND (labels LIKE '%"critical"%' OR labels LIKE '%"high"%');

CREATE VIEW v_mem_recent_7d AS
  SELECT * FROM mem_entries
  WHERE created_at >= unixepoch() - 7 * 86400 OR pinned = 1
  ORDER BY pinned DESC, created_at DESC;

INSERT INTO schema_versions (version, applied_at, checksum)
VALUES (1, unixepoch(), 'baseline-v5.0.0');

COMMIT;
