-- =====================================================================
-- Travel Docket · Migration 007 · Phase P7 — Email ingestion and exports
-- Covers: ING-01..03, RPT-01, RPT-04, MAP-06, WTH-07, EVT-08, NFR-05
-- =====================================================================

SET NAMES utf8mb4;

-- Plus-address token: docket+<ingest_token>@yourdomain (ING-01)
ALTER TABLE users
  ADD COLUMN ingest_token CHAR(16) CHARACTER SET ascii NULL AFTER session_version,
  ADD UNIQUE KEY uq_users_ingest_token (ingest_token);

-- Sender allowlist: every address a user may forward from (ING-03)
CREATE TABLE user_email_addresses (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id      BIGINT UNSIGNED NOT NULL,
  email        VARCHAR(190)  NOT NULL,
  verified_at  DATETIME      NULL,
  created_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_email_addr (email),
  KEY ix_uea_user (user_id),
  CONSTRAINT fk_uea_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Every message the IMAP poller sees (ING-02, ING-03)
CREATE TABLE inbound_emails (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  message_id     VARCHAR(255)  NOT NULL,
  user_id        BIGINT UNSIGNED NULL,                  -- resolved from token + sender
  from_addr      VARCHAR(190)  NOT NULL,
  to_addr        VARCHAR(190)  NOT NULL,
  subject        VARCHAR(255)  NULL,
  received_utc   DATETIME      NOT NULL,
  status         ENUM('accepted','rejected','processed','failed') NOT NULL,
  reject_reason  VARCHAR(120)  NULL,
  raw_path       VARCHAR(255)  NULL,                    -- .eml outside web root
  documents_created TINYINT UNSIGNED NOT NULL DEFAULT 0,
  created_at     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inbound_msgid (message_id),
  KEY ix_inbound_user (user_id, received_utc),
  CONSTRAINT fk_inbound_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Generated files: docket PDF, expense CSV, settlement statement (RPT-01, RPT-03, RPT-04)
CREATE TABLE exports (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid          CHAR(36) CHARACTER SET ascii NOT NULL,
  trip_id       BIGINT UNSIGNED NOT NULL,
  kind          ENUM('docket_pdf','expense_csv','settlement_pdf') NOT NULL,
  status        ENUM('queued','running','ready','failed') NOT NULL DEFAULT 'queued',
  file_path     VARCHAR(255)  NULL,
  size_bytes    INT UNSIGNED  NULL,
  error         VARCHAR(500)  NULL,
  requested_by  BIGINT UNSIGNED NOT NULL,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at  DATETIME      NULL,
  expires_at    DATETIME      NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_exports_uuid (uuid),
  KEY ix_exports_trip (trip_id, kind),
  CONSTRAINT fk_export_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE,
  CONSTRAINT fk_export_user FOREIGN KEY (requested_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO schema_migrations (version) VALUES ('007_p7_ingestion_exports');