PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS meetings (
  meeting_uuid TEXT PRIMARY KEY,
  meeting_id INTEGER,
  topic TEXT NOT NULL,
  host_email TEXT,
  started_at TEXT,
  ended_at TEXT,
  source TEXT NOT NULL DEFAULT 'zoom',
  synced_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_meetings_started_at ON meetings(started_at);
CREATE INDEX IF NOT EXISTS idx_meetings_id ON meetings(meeting_id);

CREATE TABLE IF NOT EXISTS summaries (
  meeting_uuid TEXT PRIMARY KEY REFERENCES meetings(meeting_uuid) ON DELETE CASCADE,
  title TEXT,
  content_markdown TEXT,
  next_steps_json TEXT,
  source_url TEXT,
  updated_at TEXT,
  synced_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS transcripts (
  meeting_uuid TEXT PRIMARY KEY REFERENCES meetings(meeting_uuid) ON DELETE CASCADE,
  content TEXT NOT NULL,
  format TEXT NOT NULL DEFAULT 'vtt',
  created_at TEXT,
  synced_at TEXT NOT NULL
);

CREATE VIRTUAL TABLE IF NOT EXISTS meeting_search USING fts5(
  meeting_uuid UNINDEXED,
  topic,
  summary,
  transcript
);

CREATE TABLE IF NOT EXISTS sync_state (
  source TEXT PRIMARY KEY,
  cursor TEXT,
  last_success_at TEXT,
  last_error TEXT
);
