-- Cairn database schema — v2 (no accounts, no passwords).
-- Import this once into an empty database. There is nothing to migrate
-- from a prior version: no user of the old schema has any data yet.

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS users (
  id                        CHAR(36)      NOT NULL PRIMARY KEY,
  install_id                VARCHAR(64)   NOT NULL,
  device_token_hash         CHAR(64)      NOT NULL,
  platform                  VARCHAR(16)   NULL,
  full_name                 VARCHAR(80)   NULL,
  timezone                  VARCHAR(64)   NULL,
  subscription_active       TINYINT(1)    NOT NULL DEFAULT 0,
  subscription_product_id   VARCHAR(64)   NULL,
  subscription_expires_at   DATETIME      NULL,
  subscription_store        VARCHAR(16)   NULL,
  granted_premium           TINYINT(1)    NOT NULL DEFAULT 0,
  granted_reason            VARCHAR(255)  NULL,
  granted_at                DATETIME      NULL,
  created_at                DATETIME      NOT NULL,
  last_seen_at              DATETIME      NOT NULL,
  UNIQUE KEY uq_users_install_id (install_id),
  UNIQUE KEY uq_users_device_token_hash (device_token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Stones are append-only and never decrement. `sequence_no` is a per-user
-- monotonic counter (assigned in a transaction, see services/stones.js) —
-- it is what lets the cairn's visual layout stay stable forever even though
-- individual stones may be dated only approximately in an old cache.
-- `dedupe_key` is the real idempotency story: a unique (user_id, dedupe_key)
-- means a retried request can never place the same stone twice, independent
-- of the Idempotency-Key header's 24h window.
CREATE TABLE IF NOT EXISTS stones (
  id                  CHAR(36)     NOT NULL PRIMARY KEY,
  user_id             CHAR(36)     NOT NULL,
  sequence_no         INT          NOT NULL,
  type                VARCHAR(16)  NOT NULL, -- day | check_in | surf | reflection | mission | keystone | weathered
  source_type         VARCHAR(24)  NOT NULL, -- system_day | daily_checkin | emergency_session | journal_entry | user_mission | milestone | relapse
  dedupe_key          VARCHAR(160) NOT NULL,
  placed_at           DATETIME     NOT NULL,
  local_date          DATE         NOT NULL,
  tz_offset_minutes   SMALLINT     NOT NULL DEFAULT 0,
  revoked_at          DATETIME     NULL,
  created_at          DATETIME     NOT NULL,
  UNIQUE KEY uq_stones_user_dedupe (user_id, dedupe_key),
  UNIQUE KEY uq_stones_user_seq (user_id, sequence_no),
  KEY idx_stones_user_placed (user_id, placed_at),
  CONSTRAINT fk_stones_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per user per local calendar day — `PUT /v1/checkins/{localDate}`
-- upserts on this key, which is what makes a retried check-in from the
-- offline queue safe with no separate idempotency bookkeeping.
CREATE TABLE IF NOT EXISTS checkins (
  id                  CHAR(36)      NOT NULL PRIMARY KEY,
  user_id             CHAR(36)      NOT NULL,
  local_date          DATE          NOT NULL,
  checked_in_at       DATETIME      NOT NULL,
  tz_offset_minutes   SMALLINT      NOT NULL DEFAULT 0,
  mood                TINYINT       NOT NULL, -- -2..2
  urge_level          TINYINT       NOT NULL, -- 0..10
  energy              TINYINT       NOT NULL, -- 1..5
  stress              TINYINT       NOT NULL, -- 1..5
  sleep_hours         DECIMAL(4,1)  NOT NULL DEFAULT 0,
  source              VARCHAR(16)   NOT NULL DEFAULT 'app',
  created_at          DATETIME      NOT NULL,
  updated_at          DATETIME      NOT NULL,
  UNIQUE KEY uq_checkins_user_date (user_id, local_date),
  CONSTRAINT fk_checkins_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- A relapse earns nothing by itself — only the debrief (below) does. `id` is
-- client-supplied (a UUID minted on-device when the event happened), which
-- is what lets the debrief be addressed by it even if it is written offline
-- and replayed hours later.
CREATE TABLE IF NOT EXISTS relapses (
  id                          CHAR(36)     NOT NULL PRIMARY KEY,
  user_id                     CHAR(36)     NOT NULL,
  occurred_at                 DATETIME     NOT NULL,
  local_date                  DATE         NOT NULL,
  tz_offset_minutes           SMALLINT     NOT NULL DEFAULT 0,
  triggers                    JSON         NULL,
  debrief_completed           TINYINT(1)   NOT NULL DEFAULT 0,
  debrief_location_context    VARCHAR(24)  NULL,
  debrief_device_context      VARCHAR(24)  NULL,
  debrief_completed_at        DATETIME     NULL,
  created_at                  DATETIME     NOT NULL,
  KEY idx_relapses_user_occurred (user_id, occurred_at),
  CONSTRAINT fk_relapses_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- `id` is client-supplied, same reasoning as relapses. The body is encrypted
-- server-side on the way in (AES-256-GCM, services/crypto.js); the client
-- sends plaintext over TLS and never holds the key.
CREATE TABLE IF NOT EXISTS journal_entries (
  id                  CHAR(36)     NOT NULL PRIMARY KEY,
  user_id             CHAR(36)     NOT NULL,
  kind                VARCHAR(16)  NOT NULL DEFAULT 'free', -- free | prompted
  prompt_key          VARCHAR(64)  NULL,
  written_at          DATETIME     NOT NULL,
  local_date          DATE         NOT NULL,
  tz_offset_minutes   SMALLINT     NOT NULL DEFAULT 0,
  word_count          INT          NOT NULL DEFAULT 0,
  char_count          INT          NOT NULL DEFAULT 0,
  body_ciphertext     TEXT         NOT NULL,
  body_iv             VARCHAR(32)  NOT NULL,
  body_tag            VARCHAR(32)  NOT NULL,
  emotions            JSON         NULL,
  created_at          DATETIME     NOT NULL,
  KEY idx_journal_user_created (user_id, created_at),
  CONSTRAINT fk_journal_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS missions (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  code          VARCHAR(64)  NOT NULL,
  title         VARCHAR(120) NOT NULL,
  description   VARCHAR(500) NULL,
  rationale     VARCHAR(500) NULL,
  category      VARCHAR(48)  NOT NULL,
  est_minutes   INT          NOT NULL DEFAULT 5,
  points        INT          NOT NULL DEFAULT 0,
  UNIQUE KEY uq_missions_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- The mission assigned to one user on one day — `GET /v1/missions/today`
-- creates this row the first time it is asked for a given date, then always
-- returns the same one. `id` here (not `mission_id`) is what
-- `POST /v1/missions/{id}/complete` is addressed by.
CREATE TABLE IF NOT EXISTS user_missions (
  id               CHAR(36)  NOT NULL PRIMARY KEY,
  user_id          CHAR(36)  NOT NULL,
  mission_id       CHAR(36)  NOT NULL,
  assigned_date    DATE      NOT NULL,
  completed_at     DATETIME  NULL,
  created_at       DATETIME  NOT NULL,
  UNIQUE KEY uq_user_missions_user_date (user_id, assigned_date),
  CONSTRAINT fk_user_missions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_missions_mission FOREIGN KEY (mission_id) REFERENCES missions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per user. Regenerable, so there is no history to preserve —
-- `POST /v1/plan` simply replaces it.
CREATE TABLE IF NOT EXISTS plans (
  id                   CHAR(36)     NOT NULL PRIMARY KEY,
  user_id              CHAR(36)     NOT NULL,
  phase_code           VARCHAR(16)  NOT NULL DEFAULT 'ground', -- ground | clear | replace | steady
  focus_areas          JSON         NULL,
  dominant_triggers    JSON         NULL,
  high_risk_hours      JSON         NULL,
  headline             VARCHAR(280) NOT NULL,
  starts_on            DATE         NOT NULL,
  created_at           DATETIME     NOT NULL,
  updated_at           DATETIME     NOT NULL,
  UNIQUE KEY uq_plans_user (user_id),
  CONSTRAINT fk_plans_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Opened the screen = earned the surf stone, whatever happens next. `id` is
-- client-supplied so the tool-use rows below (and the closing PATCH) can be
-- queued and replayed alongside it without waiting for a server-minted id.
CREATE TABLE IF NOT EXISTS emergency_sessions (
  id                  CHAR(36)     NOT NULL PRIMARY KEY,
  user_id             CHAR(36)     NOT NULL,
  started_at          DATETIME     NOT NULL,
  local_date          DATE         NOT NULL,
  tz_offset_minutes   SMALLINT     NOT NULL DEFAULT 0,
  entry_point         VARCHAR(24)  NOT NULL DEFAULT 'home',
  urge_before         TINYINT      NOT NULL,
  urge_after          TINYINT      NULL,
  outcome             VARCHAR(16)  NULL, -- passed | acted | left_early | unknown
  ended_at            DATETIME     NULL,
  completed           TINYINT(1)   NOT NULL DEFAULT 0,
  offline_created      TINYINT(1)   NOT NULL DEFAULT 0,
  created_at          DATETIME     NOT NULL,
  KEY idx_emergency_sessions_user (user_id, started_at),
  CONSTRAINT fk_emergency_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS emergency_session_tools (
  id                CHAR(36)     NOT NULL PRIMARY KEY,
  session_id        CHAR(36)     NOT NULL,
  user_id           CHAR(36)     NOT NULL,
  tool_code         VARCHAR(32)  NOT NULL,
  sequence_index    SMALLINT     NOT NULL DEFAULT 0,
  started_at        DATETIME     NOT NULL,
  ended_at          DATETIME     NULL,
  completed         TINYINT(1)   NOT NULL DEFAULT 0,
  created_at        DATETIME     NOT NULL,
  CONSTRAINT fk_session_tools_session FOREIGN KEY (session_id) REFERENCES emergency_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS coach_messages (
  id                CHAR(36)              NOT NULL PRIMARY KEY,
  user_id           CHAR(36)              NOT NULL,
  role              ENUM('user','assistant') NOT NULL,
  body_ciphertext   TEXT                  NOT NULL,
  body_iv           VARCHAR(32)           NOT NULL,
  body_tag          VARCHAR(32)           NOT NULL,
  created_at        DATETIME              NOT NULL,
  KEY idx_coach_user_created (user_id, created_at),
  CONSTRAINT fk_coach_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS emotions (
  code    VARCHAR(48)   NOT NULL PRIMARY KEY,
  label   VARCHAR(80)   NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS triggers (
  code    VARCHAR(48)   NOT NULL PRIMARY KEY,
  label   VARCHAR(80)   NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS idempotency_keys (
  user_id         CHAR(36)      NOT NULL,
  idem_key        VARCHAR(128)  NOT NULL,
  endpoint        VARCHAR(255)  NOT NULL,
  status_code     SMALLINT      NOT NULL,
  response_body   MEDIUMTEXT    NOT NULL,
  created_at      DATETIME      NOT NULL,
  PRIMARY KEY (user_id, idem_key),
  CONSTRAINT fk_idempotency_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;