-- Bowler Chart schema. Applied idempotently on startup.
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS color_schemes (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  name          TEXT    NOT NULL UNIQUE,
  seed_color    TEXT    NOT NULL,                 -- #rrggbb
  mode          TEXT    NOT NULL DEFAULT 'light', -- 'light' | 'dark'
  is_default    INTEGER NOT NULL DEFAULT 0,       -- 0/1; exactly one row should be 1
  created_at    TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS users (
  id                  INTEGER PRIMARY KEY AUTOINCREMENT,
  username            TEXT    NOT NULL UNIQUE,
  email               TEXT,
  password_hash       TEXT    NOT NULL,
  role                TEXT    NOT NULL DEFAULT 'user',  -- 'admin' | 'user'
  preferred_scheme_id INTEGER REFERENCES color_schemes(id) ON DELETE SET NULL,
  active              INTEGER NOT NULL DEFAULT 1,
  created_at          TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS projects (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  name        TEXT    NOT NULL,
  description TEXT,
  owner_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  sort_order  INTEGER NOT NULL DEFAULT 0,
  created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS kpis (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  project_id  INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name        TEXT    NOT NULL,
  description TEXT,
  unit        TEXT,
  goal_text   TEXT,
  sort_order  INTEGER NOT NULL DEFAULT 0,
  created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS entries (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  kpi_id     INTEGER NOT NULL REFERENCES kpis(id) ON DELETE CASCADE,
  year       INTEGER NOT NULL,
  month      INTEGER NOT NULL CHECK (month BETWEEN 1 AND 12),
  value      TEXT,
  status     TEXT    NOT NULL DEFAULT 'none',  -- 'green' | 'amber' | 'red' | 'none'
  note       TEXT,
  updated_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
  updated_at TEXT    NOT NULL DEFAULT (datetime('now')),
  UNIQUE (kpi_id, year, month)
);

CREATE INDEX IF NOT EXISTS idx_projects_owner ON projects(owner_id);
CREATE INDEX IF NOT EXISTS idx_kpis_project   ON kpis(project_id);
CREATE INDEX IF NOT EXISTS idx_entries_kpi    ON entries(kpi_id, year);