-- Accounts, sign-in sessions, guests and saved conversations.
-- Works on MySQL 8 and MariaDB 10.5+. Never edit a migration that has been applied: add a new one.
--
-- Ownership: a conversation belongs to EITHER a user (signed in) OR a guest (not signed in), never both.
-- When a guest signs in, their conversations are re-pointed to the user (guest_id -> user_id).
-- MySQL does not allow a CHECK constraint on columns that have ON DELETE CASCADE, so "exactly one owner" is
-- enforced by the code (app/server/conversations/store.ts takes an Owner, which is one or the other).

CREATE TABLE users (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email         VARCHAR(254)    NOT NULL,                       -- stored lower-case; unique
  password_hash VARCHAR(255)    NOT NULL,                       -- scrypt, self-describing: scrypt$N$r$p$salt$hash
  display_name  VARCHAR(100)    NULL,
  created_at    DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at    DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Server-side sessions: the browser holds a random token, we hold only its SHA-256, so a leaked table
-- cannot be used to sign in, and a session can be revoked by deleting its row.
CREATE TABLE sessions (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id      BIGINT UNSIGNED NOT NULL,
  token_hash   BINARY(32)      NOT NULL,
  created_at   DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  expires_at   DATETIME(3)     NOT NULL,
  user_agent   VARCHAR(255)    NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sessions_token (token_hash),
  KEY idx_sessions_user (user_id),
  KEY idx_sessions_expires (expires_at),
  CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A visitor who is not signed in. Same token-hash scheme as sessions. Inactive guests are purged
-- (their conversations go with them) so anonymous traffic cannot grow the database forever.
CREATE TABLE guests (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  token_hash   BINARY(32)      NOT NULL,
  created_at   DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  last_seen_at DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uq_guests_token (token_hash),
  KEY idx_guests_last_seen (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- `id` is the compact internal key; `public_id` (a UUID made by the browser) is the only id the API exposes,
-- so row counts and neighbouring ids are not guessable.
CREATE TABLE conversations (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id  CHAR(36)        NOT NULL,
  user_id    BIGINT UNSIGNED NULL,
  guest_id   BIGINT UNSIGNED NULL,
  title      VARCHAR(255)    NOT NULL,
  created_at DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uq_conversations_public (public_id),
  -- The sidebar query: one owner's conversations, newest first.
  KEY idx_conversations_user  (user_id,  updated_at),
  KEY idx_conversations_guest (guest_id, updated_at),
  CONSTRAINT fk_conversations_user  FOREIGN KEY (user_id)  REFERENCES users  (id) ON DELETE CASCADE,
  CONSTRAINT fk_conversations_guest FOREIGN KEY (guest_id) REFERENCES guests (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE messages (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,   -- insertion order = conversation order
  conversation_id BIGINT UNSIGNED NOT NULL,
  client_id       VARCHAR(64)     NOT NULL,                   -- the browser's id for the message; makes saving idempotent
  role            ENUM('user', 'assistant') NOT NULL,
  content         MEDIUMTEXT      NOT NULL,                   -- replies can carry large SQL / chart JSON
  error           TEXT            NULL,                       -- set when generating the reply failed
  interpretation  TEXT            NULL,                       -- JSON: what the server understood the question to be
  sent_at         DATETIME(3)     NOT NULL,                   -- when the message was sent (browser clock)
  PRIMARY KEY (id),
  UNIQUE KEY uq_messages_client (conversation_id, client_id),
  CONSTRAINT fk_messages_conversation FOREIGN KEY (conversation_id) REFERENCES conversations (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;