-- KEMSWA eCPD & Certification System — SQLite schema
-- Uses Node's built-in node:sqlite module (no native build, no external DB needed).

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  full_name     TEXT NOT NULL,
  email         TEXT NOT NULL UNIQUE,
  password      TEXT NOT NULL,
  role          TEXT NOT NULL DEFAULT 'MEMBER' CHECK (role IN ('MEMBER','REVIEWER','ADMIN')),
  membership_no TEXT UNIQUE,
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS cpd_activities (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  title       TEXT NOT NULL,
  category    TEXT NOT NULL,
  points      REAL NOT NULL,
  description TEXT,
  is_active   INTEGER NOT NULL DEFAULT 1,
  created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS submissions (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  member_id      INTEGER NOT NULL REFERENCES users(id),
  activity_id    INTEGER NOT NULL REFERENCES cpd_activities(id),
  year           INTEGER NOT NULL,
  evidence_url   TEXT,
  notes          TEXT,
  points_awarded REAL,
  status         TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','APPROVED','REJECTED')),
  reviewed_by_id INTEGER REFERENCES users(id),
  review_notes   TEXT,
  created_at     TEXT NOT NULL DEFAULT (datetime('now')),
  reviewed_at    TEXT
);

CREATE TABLE IF NOT EXISTS certifications (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  member_id    INTEGER NOT NULL REFERENCES users(id),
  year         INTEGER NOT NULL,
  total_points REAL NOT NULL DEFAULT 0,
  status       TEXT NOT NULL DEFAULT 'NOT_ELIGIBLE' CHECK (status IN ('NOT_ELIGIBLE','ELIGIBLE','CERTIFIED')),
  certified_at TEXT,
  UNIQUE (member_id, year)
);

CREATE TABLE IF NOT EXISTS events (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  title       TEXT NOT NULL,
  event_code  TEXT NOT NULL UNIQUE,
  activity_id INTEGER REFERENCES cpd_activities(id),
  event_date  TEXT NOT NULL,
  is_active   INTEGER NOT NULL DEFAULT 1,
  created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS event_attendances (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  event_id      INTEGER NOT NULL REFERENCES events(id),
  member_id     INTEGER NOT NULL REFERENCES users(id),
  checked_in_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE (event_id, member_id)
);

CREATE INDEX IF NOT EXISTS idx_submissions_member ON submissions(member_id);
CREATE INDEX IF NOT EXISTS idx_submissions_status ON submissions(status);
CREATE INDEX IF NOT EXISTS idx_certifications_member ON certifications(member_id);