-- =====================================================================
-- Travel Docket · Migration 003 · Phase P3 — Documents, extraction, job queue
-- Covers: DOC-01..07, DOC-09, BKG-07, BKG-08, TML-04, TML-05, OPS-01, OPS-02, OPS-04, NFR-14
-- =====================================================================

SET NAMES utf8mb4;

-- Uploaded / emailed files. trip_id NULL = user's inbox until assigned.
-- Physical file lives outside web root at storage_path, named by sha256 (DOC-02).
CREATE TABLE documents (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid             CHAR(36) CHARACTER SET ascii NOT NULL,
  user_id          BIGINT UNSIGNED NOT NULL,            -- uploader / mailbox owner
  trip_id          BIGINT UNSIGNED NULL,
  booking_id       BIGINT UNSIGNED NULL,                -- set on confirm or manual attach (DOC-07)
  source           ENUM('upload','camera','email') NOT NULL DEFAULT 'upload',
  doc_kind         ENUM('ticket','hotel_voucher','payment_screenshot','receipt','other') NULL,
  original_name    VARCHAR(255)  NOT NULL,
  mime             VARCHAR(80)   NOT NULL,
  size_bytes       INT UNSIGNED  NOT NULL,
  sha256           CHAR(64) CHARACTER SET ascii NOT NULL,
  storage_path     VARCHAR(255)  NOT NULL,
  page_count       SMALLINT UNSIGNED NULL,
  status           ENUM('queued','processing','ready','failed','confirmed','discarded') NOT NULL DEFAULT 'queued',
  extracted_text   MEDIUMTEXT    NULL,                  -- DOC-03 local PDF text
  extraction       JSON          NULL,                  -- DOC-04 LLM output (schema per type)
  field_confidence JSON          NULL,                  -- DOC-05 {"pnr":0.98,"arr_time":0.41}
  llm_model        VARCHAR(60)   NULL,
  error            VARCHAR(500)  NULL,
  processed_at     DATETIME      NULL,
  confirmed_by     BIGINT UNSIGNED NULL,
  confirmed_at     DATETIME      NULL,
  created_at       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at       DATETIME      NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_documents_uuid (uuid),
  UNIQUE KEY uq_documents_user_sha (user_id, sha256),  -- same file twice by same user → one row
  KEY ix_documents_sha (sha256),
  KEY ix_documents_trip (trip_id),
  KEY ix_documents_booking (booking_id),
  KEY ix_documents_status (status, created_at),
  CONSTRAINT fk_doc_user    FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_doc_trip    FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE,
  CONSTRAINT fk_doc_booking FOREIGN KEY (booking_id) REFERENCES bookings (id) ON DELETE SET NULL,
  CONSTRAINT fk_doc_conf_by FOREIGN KEY (confirmed_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE bookings
  ADD CONSTRAINT fk_bkg_source_doc FOREIGN KEY (source_document_id) REFERENCES documents (id) ON DELETE SET NULL;

-- Job queue (OPS-01). Worker claims with:
--   SELECT id FROM jobs WHERE status='pending' AND available_at<=UTC_TIMESTAMP()
--   ORDER BY priority, available_at LIMIT 1 FOR UPDATE SKIP LOCKED;
CREATE TABLE jobs (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  queue         VARCHAR(40)   NOT NULL DEFAULT 'default', -- extract / status / notify / weather …
  job_type      VARCHAR(60)   NOT NULL,                   -- e.g. ExtractDocument, PollSegmentStatus
  payload       JSON          NOT NULL,
  unique_key    VARCHAR(150)  NULL,                       -- prevents duplicate pending jobs
  priority      TINYINT UNSIGNED NOT NULL DEFAULT 5,      -- 1 = highest
  status        ENUM('pending','running','done','failed') NOT NULL DEFAULT 'pending',
  attempts      TINYINT UNSIGNED NOT NULL DEFAULT 0,
  max_attempts  TINYINT UNSIGNED NOT NULL DEFAULT 3,
  available_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  locked_at     DATETIME      NULL,
  locked_by     VARCHAR(64)   NULL,                       -- hostname:pid
  last_error    TEXT          NULL,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at   DATETIME      NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_jobs_unique_key (unique_key),
  KEY ix_jobs_claim (status, queue, priority, available_at),
  KEY ix_jobs_locked (status, locked_at)                   -- reclaim stale 'running' jobs
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Monthly usage and cost per external provider (DOC-09, STS-07, NFR-14).
CREATE TABLE api_usage (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider    VARCHAR(40)   NOT NULL,                  -- anthropic, aerodatabox, openmeteo …
  period      CHAR(7) CHARACTER SET ascii NOT NULL,    -- YYYY-MM
  calls       INT UNSIGNED  NOT NULL DEFAULT 0,
  units       INT UNSIGNED  NOT NULL DEFAULT 0,        -- provider units / tokens
  est_cost    DECIMAL(10,4) NOT NULL DEFAULT 0,
  cost_currency CHAR(3)     NOT NULL DEFAULT 'USD',
  updated_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_usage_provider_period (provider, period)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dismissed timeline warnings: gaps and tight connections (TML-04, TML-05).
CREATE TABLE timeline_dismissals (
  trip_id       BIGINT UNSIGNED NOT NULL,
  warning_key   VARCHAR(150)  NOT NULL,                -- e.g. gap:night:2026-10-17
  dismissed_by  BIGINT UNSIGNED NOT NULL,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (trip_id, warning_key),
  CONSTRAINT fk_td_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE,
  CONSTRAINT fk_td_user FOREIGN KEY (dismissed_by) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO schema_migrations (version) VALUES ('003_p3_documents_jobs');