-- =====================================================================
-- Travel Docket · Migration 001 · Phase P1 — Foundation
-- Covers: PLT-01..06, TRP-01, TRP-03..06, OPS-06
-- MySQL 8.0+ · InnoDB · utf8mb4
-- Conventions:
--   id    BIGINT UNSIGNED  internal key, used only for joins
--   uuid  CHAR(36) ascii   public key used in URLs/API (NFR-01: no ID guessing)
--                          and client-generated for offline writes (PWA-05)
--   *_utc DATETIME         always UTC (NFR-13); local zone kept in *_tz (IANA)
--   money DECIMAL(12,2) + CHAR(3) ISO currency (NFR-13)
--   deleted_at             soft delete, so offline clients can sync deletions
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS schema_migrations (
  version     VARCHAR(100)  NOT NULL,
  applied_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ---------------------------------------------------------------------
-- Users and authentication (PLT-02..05)
-- ---------------------------------------------------------------------
CREATE TABLE users (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid              CHAR(36) CHARACTER SET ascii NOT NULL,
  email             VARCHAR(190)  NOT NULL,
  password_hash     VARCHAR(255)  NOT NULL,
  name              VARCHAR(120)  NOT NULL,
  phone             VARCHAR(20)   NULL,
  default_currency  CHAR(3)       NOT NULL DEFAULT 'INR',
  home_tz           VARCHAR(64)   NOT NULL DEFAULT 'Asia/Kolkata',
  is_admin          TINYINT(1)    NOT NULL DEFAULT 0,
  status            ENUM('active','disabled') NOT NULL DEFAULT 'active',
  session_version   INT UNSIGNED  NOT NULL DEFAULT 1,   -- bump to invalidate all sessions (PLT-03)
  email_verified_at DATETIME      NULL,
  last_login_at     DATETIME      NULL,
  created_at        DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_uuid (uuid),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE password_resets (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     BIGINT UNSIGNED NOT NULL,
  token_hash  CHAR(64) CHARACTER SET ascii NOT NULL,   -- sha256 of link token
  otp_hash    VARCHAR(255)  NULL,                      -- password_hash() of 6-digit OTP
  otp_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  expires_at  DATETIME      NOT NULL,                  -- created + 30 min
  used_at     DATETIME      NULL,
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_pwreset_token (token_hash),
  KEY ix_pwreset_user (user_id, expires_at),
  CONSTRAINT fk_pwreset_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE login_attempts (                          -- PLT-04: 5 attempts / 15 min
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email        VARCHAR(190)  NOT NULL,
  ip           VARBINARY(16) NOT NULL,                 -- INET6_ATON()
  success      TINYINT(1)    NOT NULL DEFAULT 0,
  attempted_at DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_login_email_time (email, attempted_at),
  KEY ix_login_ip_time (ip, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ---------------------------------------------------------------------
-- Trips, members, invites, share links (TRP-01, TRP-03, TRP-04, TRP-06)
-- ---------------------------------------------------------------------
CREATE TABLE trips (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid              CHAR(36) CHARACTER SET ascii NOT NULL,
  owner_user_id     BIGINT UNSIGNED NOT NULL,
  title             VARCHAR(150)  NOT NULL,
  notes             TEXT          NULL,
  cover_path        VARCHAR(255)  NULL,
  base_currency     CHAR(3)       NOT NULL DEFAULT 'INR',
  home_tz           VARCHAR(64)   NOT NULL DEFAULT 'Asia/Kolkata',
  start_date        DATE          NULL,                 -- derived from legs/stays (TRP-02)
  end_date          DATE          NULL,
  dates_overridden  TINYINT(1)    NOT NULL DEFAULT 0,   -- 1 = keep manual dates
  archived_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_trips_uuid (uuid),
  KEY ix_trips_owner (owner_user_id),
  KEY ix_trips_dates (start_date, end_date),
  CONSTRAINT fk_trips_owner FOREIGN KEY (owner_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE trip_members (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  trip_id     BIGINT UNSIGNED NOT NULL,
  user_id     BIGINT UNSIGNED NOT NULL,
  role        ENUM('owner','editor','viewer') NOT NULL DEFAULT 'editor',
  joined_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_trip_member (trip_id, user_id),
  KEY ix_trip_members_user (user_id),
  CONSTRAINT fk_tm_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE,
  CONSTRAINT fk_tm_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE trip_invites (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid         CHAR(36) CHARACTER SET ascii NOT NULL,
  trip_id      BIGINT UNSIGNED NOT NULL,
  email        VARCHAR(190)  NOT NULL,
  role         ENUM('editor','viewer') NOT NULL DEFAULT 'editor',
  token_hash   CHAR(64) CHARACTER SET ascii NOT NULL,
  invited_by   BIGINT UNSIGNED NOT NULL,
  expires_at   DATETIME      NOT NULL,
  accepted_at  DATETIME      NULL,
  revoked_at   DATETIME      NULL,
  created_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_invites_uuid (uuid),
  UNIQUE KEY uq_invites_token (token_hash),
  KEY ix_invites_trip_email (trip_id, email),
  CONSTRAINT fk_inv_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE,
  CONSTRAINT fk_inv_by   FOREIGN KEY (invited_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE share_links (                             -- TRP-04
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  trip_id       BIGINT UNSIGNED NOT NULL,
  token_hash    CHAR(64) CHARACTER SET ascii NOT NULL,
  created_by    BIGINT UNSIGNED NOT NULL,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at    DATETIME      NULL,
  last_used_at  DATETIME      NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_share_token (token_hash),
  KEY ix_share_trip (trip_id),
  CONSTRAINT fk_share_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE,
  CONSTRAINT fk_share_by   FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Travellers: people on the trip, with or without accounts (TRP-05).
-- kind='kitty' is the common-pool pseudo-member (EXP-06); never gets shares.
CREATE TABLE travellers (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid          CHAR(36) CHARACTER SET ascii NOT NULL,
  trip_id       BIGINT UNSIGNED NOT NULL,
  user_id       BIGINT UNSIGNED NULL,
  kind          ENUM('person','kitty') NOT NULL DEFAULT 'person',
  display_name  VARCHAR(120)  NOT NULL,
  age_group     ENUM('adult','senior','child','infant') NOT NULL DEFAULT 'adult',
  share_units   DECIMAL(5,2)  NOT NULL DEFAULT 1.00,   -- default weight for 'shares' split
  phone         VARCHAR(20)   NULL,
  sort_order    SMALLINT      NOT NULL DEFAULT 0,
  created_by    BIGINT UNSIGNED 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_travellers_uuid (uuid),
  UNIQUE KEY uq_traveller_user (trip_id, user_id),     -- one traveller per account per trip
  KEY ix_travellers_trip (trip_id),
  CONSTRAINT fk_trav_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE,
  CONSTRAINT fk_trav_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ---------------------------------------------------------------------
-- Audit log (PLT-06)
-- ---------------------------------------------------------------------
CREATE TABLE audit_log (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     BIGINT UNSIGNED NULL,
  trip_id     BIGINT UNSIGNED NULL,
  entity      VARCHAR(40)   NOT NULL,                  -- table name
  entity_id   BIGINT UNSIGNED NOT NULL,
  action      ENUM('create','update','delete','restore') NOT NULL,
  old_values  JSON          NULL,
  new_values  JSON          NULL,
  ip          VARBINARY(16) NULL,
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_audit_entity (entity, entity_id),
  KEY ix_audit_trip_time (trip_id, created_at),
  KEY ix_audit_user_time (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO schema_migrations (version) VALUES ('001_p1_core');