PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS cards (
  card_id          TEXT    PRIMARY KEY,
  slack_user_id    TEXT    NOT NULL,
  slack_channel_id TEXT    NOT NULL,
  titulo           TEXT    NOT NULL,
  texto_original   TEXT    NOT NULL,
  status           TEXT    NOT NULL DEFAULT 'em_progresso'
                           CHECK (status IN ('em_progresso', 'aprovado')),
  criado_em        TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
  atualizado_em    TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
  deleted_at       TEXT    DEFAULT NULL,
  UNIQUE (slack_channel_id)
);

CREATE TRIGGER IF NOT EXISTS cards_set_atualizado_em
AFTER UPDATE ON cards FOR EACH ROW
BEGIN
  UPDATE cards SET atualizado_em = strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
  WHERE card_id = NEW.card_id;
END;

CREATE INDEX IF NOT EXISTS idx_cards_user_status ON cards (slack_user_id, status);
CREATE INDEX IF NOT EXISTS idx_cards_deleted_at  ON cards (deleted_at);

CREATE TABLE IF NOT EXISTS card_stages (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  card_id   TEXT    NOT NULL,
  stage     TEXT    NOT NULL,
  conteudo  TEXT    NOT NULL,
  gerado_em TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
  UNIQUE (card_id, stage),
  FOREIGN KEY (card_id) REFERENCES cards(card_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_stages_card ON card_stages (card_id);

PRAGMA user_version = 1;
