-- The Small Business MBA. Initial schema.
-- Single brand, single author. Times are unix epoch seconds. IDs are ULIDs.

-- Members and the admin (JT).
CREATE TABLE IF NOT EXISTS users (
  id            TEXT PRIMARY KEY,
  email         TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  display_name  TEXT NOT NULL,
  role          TEXT NOT NULL DEFAULT 'member',  -- 'member' | 'admin'
  avatar_url    TEXT,
  bio           TEXT,
  created_at    INTEGER NOT NULL,
  updated_at    INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- Rotating refresh tokens, stored hashed.
CREATE TABLE IF NOT EXISTS refresh_tokens (
  id          TEXT PRIMARY KEY,
  user_id     TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash  TEXT NOT NULL,
  expires_at  INTEGER NOT NULL,
  created_at  INTEGER NOT NULL,
  revoked_at  INTEGER
);
CREATE INDEX IF NOT EXISTS idx_refresh_user ON refresh_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_refresh_hash ON refresh_tokens(token_hash);

-- One membership per user. Stripe is the source of truth; this mirrors it.
CREATE TABLE IF NOT EXISTS subscriptions (
  id                     TEXT PRIMARY KEY,
  user_id                TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  stripe_customer_id     TEXT,
  stripe_subscription_id TEXT,
  status                 TEXT NOT NULL DEFAULT 'inactive', -- active|trialing|past_due|canceled|inactive
  current_period_end     INTEGER,
  created_at             INTEGER NOT NULL,
  updated_at             INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_subs_user ON subscriptions(user_id);
CREATE INDEX IF NOT EXISTS idx_subs_customer ON subscriptions(stripe_customer_id);

-- Content groups. The four core pillars plus bonus "masterclass" courses.
CREATE TABLE IF NOT EXISTS pillars (
  id         TEXT PRIMARY KEY,
  slug       TEXT NOT NULL UNIQUE,
  title      TEXT NOT NULL,
  subtitle   TEXT,
  kind       TEXT NOT NULL DEFAULT 'core',  -- 'core' (the four pillars) | 'bonus' (masterclasses / electives)
  intro_video_url TEXT,                     -- optional course intro (e.g. Hedra animated intro)
  sort       INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL
);

-- Lessons / modules within a pillar.
CREATE TABLE IF NOT EXISTS lessons (
  id              TEXT PRIMARY KEY,
  pillar_id       TEXT NOT NULL REFERENCES pillars(id) ON DELETE CASCADE,
  slug            TEXT NOT NULL UNIQUE,
  title           TEXT NOT NULL,
  summary         TEXT,
  body_html       TEXT,
  video_url       TEXT,                       -- optional per-lesson video embed URL
  est_minutes     INTEGER,
  sort            INTEGER NOT NULL DEFAULT 0,
  is_free_preview INTEGER NOT NULL DEFAULT 0,  -- 0/1, shown without an active sub
  published       INTEGER NOT NULL DEFAULT 1,
  created_at      INTEGER NOT NULL,
  updated_at      INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_lessons_pillar ON lessons(pillar_id);

-- Per-member completion tracking.
CREATE TABLE IF NOT EXISTS lesson_progress (
  user_id      TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  lesson_id    TEXT NOT NULL REFERENCES lessons(id) ON DELETE CASCADE,
  completed_at INTEGER,
  PRIMARY KEY (user_id, lesson_id)
);

-- Community feed posts.
CREATE TABLE IF NOT EXISTS posts (
  id         TEXT PRIMARY KEY,
  user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  body       TEXT NOT NULL,
  pinned     INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  deleted_at INTEGER
);
CREATE INDEX IF NOT EXISTS idx_posts_created ON posts(created_at);

-- Comments on posts.
CREATE TABLE IF NOT EXISTS comments (
  id         TEXT PRIMARY KEY,
  post_id    TEXT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  body       TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  deleted_at INTEGER
);
CREATE INDEX IF NOT EXISTS idx_comments_post ON comments(post_id);

-- Reactions (likes) on posts. One per user per kind.
CREATE TABLE IF NOT EXISTS reactions (
  id         TEXT PRIMARY KEY,
  post_id    TEXT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  kind       TEXT NOT NULL DEFAULT 'like',
  created_at INTEGER NOT NULL,
  UNIQUE (post_id, user_id, kind)
);

-- Stripe webhook idempotency.
CREATE TABLE IF NOT EXISTS webhook_events (
  id          TEXT PRIMARY KEY,  -- stripe event id
  type        TEXT NOT NULL,
  received_at INTEGER NOT NULL
);
