-- =====================================================================
-- Travel Docket · Migration 002 · Phase P2 — Bookings, places, timeline
-- Covers: BKG-01..06, PLC-01..04, TRP-02, TML-01..03
-- Hierarchy: trips → bookings → segments (legs) / stays
-- =====================================================================

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
-- Places master + aliases (PLC-01, PLC-02). SBC / KSR Bengaluru / BLR → one city.
-- A station or airport is its own place with parent_place_id → the city.
-- ---------------------------------------------------------------------
CREATE TABLE places (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid             CHAR(36) CHARACTER SET ascii NOT NULL,
  parent_place_id  BIGINT UNSIGNED NULL,
  place_type       ENUM('city','airport','rail_station','bus_stand','hotel','poi','other') NOT NULL,
  name             VARCHAR(150)  NOT NULL,
  code             VARCHAR(10)   NULL,                  -- IATA / station code
  address          VARCHAR(255)  NULL,
  city             VARCHAR(100)  NULL,
  state            VARCHAR(100)  NULL,
  state_code       VARCHAR(10)   NULL,                  -- e.g. IN-KA (used by holidays)
  country_code     CHAR(2)       NOT NULL DEFAULT 'IN',
  lat              DECIMAL(9,6)  NULL,
  lng              DECIMAL(9,6)  NULL,
  tz               VARCHAR(64)   NULL,
  geocode_status   ENUM('ok','pending','failed','manual') NOT NULL DEFAULT 'pending', -- PLC-05
  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,
  PRIMARY KEY (id),
  UNIQUE KEY uq_places_uuid (uuid),
  UNIQUE KEY uq_places_type_code (place_type, code),   -- NULL codes allowed repeatedly
  KEY ix_places_parent (parent_place_id),
  KEY ix_places_geo (lat, lng),
  FULLTEXT KEY ft_places_name (name, city),
  CONSTRAINT fk_places_parent FOREIGN KEY (parent_place_id) REFERENCES places (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE place_aliases (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  place_id    BIGINT UNSIGNED NOT NULL,
  alias       VARCHAR(150)  NOT NULL,
  alias_norm  VARCHAR(150)  NOT NULL,                   -- lower-case, punctuation stripped
  PRIMARY KEY (id),
  UNIQUE KEY uq_alias_place (place_id, alias_norm),
  KEY ix_alias_norm (alias_norm),
  CONSTRAINT fk_alias_place FOREIGN KEY (place_id) REFERENCES places (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ---------------------------------------------------------------------
-- Bookings (BKG-01, BKG-04). One PNR / confirmation = one booking.
-- ---------------------------------------------------------------------
CREATE TABLE bookings (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid                CHAR(36) CHARACTER SET ascii NOT NULL,
  trip_id             BIGINT UNSIGNED NOT NULL,
  booking_type        ENUM('flight','rail','bus','cab','hotel','other') NOT NULL,
  provider            VARCHAR(80)   NULL,               -- IndiGo, IRCTC, KSRTC, MakeMyTrip…
  pnr                 VARCHAR(40)   NULL,
  status              ENUM('confirmed','waitlisted','rac','pending','cancelled','refunded') NOT NULL DEFAULT 'confirmed',
  fare_amount         DECIMAL(12,2) NULL,
  fare_currency       CHAR(3)       NOT NULL DEFAULT 'INR',
  booked_on           DATE          NULL,
  contact_phone       VARCHAR(20)   NULL,
  notes               TEXT          NULL,
  source              ENUM('manual','upload','email') NOT NULL DEFAULT 'manual',
  source_document_id  BIGINT UNSIGNED NULL,             -- FK added in 003
  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_bookings_uuid (uuid),
  KEY ix_bookings_trip (trip_id),
  KEY ix_bookings_pnr (pnr),
  KEY ix_bookings_provider_pnr (provider, pnr),        -- duplicate detection (BKG-07)
  CONSTRAINT fk_bkg_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE,
  CONSTRAINT fk_bkg_user FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Segments = legs (BKG-02, BKG-05). Times in UTC + departure/arrival zones.
CREATE TABLE segments (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid            CHAR(36) CHARACTER SET ascii NOT NULL,
  booking_id      BIGINT UNSIGNED NOT NULL,
  seq             TINYINT UNSIGNED NOT NULL DEFAULT 1,
  mode            ENUM('flight','rail','bus','cab','auto','metro','ferry','other') NOT NULL,
  carrier         VARCHAR(80)   NULL,
  service_no      VARCHAR(20)   NULL,                   -- flight no / train no / bus service no
  service_name    VARCHAR(120)  NULL,
  from_place_id   BIGINT UNSIGNED NOT NULL,
  to_place_id     BIGINT UNSIGNED NOT NULL,
  dep_utc         DATETIME      NOT NULL,
  dep_tz          VARCHAR(64)   NOT NULL DEFAULT 'Asia/Kolkata',
  arr_utc         DATETIME      NULL,
  arr_tz          VARCHAR(64)   NOT NULL DEFAULT 'Asia/Kolkata',
  dep_terminal    VARCHAR(20)   NULL,                   -- from ticket; live values in 006
  dep_platform    VARCHAR(10)   NULL,
  arr_terminal    VARCHAR(20)   NULL,
  travel_class    VARCHAR(30)   NULL,                   -- Economy / 3A / CC / Sleeper AC …
  distance_km     SMALLINT UNSIGNED NULL,
  is_cancelled    TINYINT(1)    NOT NULL DEFAULT 0,
  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_segments_uuid (uuid),
  UNIQUE KEY uq_segment_seq (booking_id, seq),
  KEY ix_segments_dep (dep_utc),
  KEY ix_segments_from (from_place_id, dep_utc),
  KEY ix_segments_to (to_place_id, arr_utc),
  CONSTRAINT fk_seg_booking FOREIGN KEY (booking_id) REFERENCES bookings (id) ON DELETE CASCADE,
  CONSTRAINT fk_seg_from    FOREIGN KEY (from_place_id) REFERENCES places (id),
  CONSTRAINT fk_seg_to      FOREIGN KEY (to_place_id) REFERENCES places (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Hotel stays (BKG-06). A hotel booking has one or more stays.
CREATE TABLE stays (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid             CHAR(36) CHARACTER SET ascii NOT NULL,
  booking_id       BIGINT UNSIGNED NOT NULL,
  place_id         BIGINT UNSIGNED NOT NULL,            -- place_type = 'hotel'
  hotel_name       VARCHAR(150)  NOT NULL,
  address          VARCHAR(255)  NULL,
  checkin_utc      DATETIME      NOT NULL,
  checkout_utc     DATETIME      NOT NULL,
  tz               VARCHAR(64)   NOT NULL DEFAULT 'Asia/Kolkata',
  room_type        VARCHAR(80)   NULL,
  rooms            TINYINT UNSIGNED NOT NULL DEFAULT 1,
  guests           TINYINT UNSIGNED NULL,
  confirmation_no  VARCHAR(40)   NULL,
  contact_phone    VARCHAR(20)   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_stays_uuid (uuid),
  KEY ix_stays_booking (booking_id),
  KEY ix_stays_checkin (checkin_utc),
  KEY ix_stays_place (place_id),
  CONSTRAINT fk_stay_booking FOREIGN KEY (booking_id) REFERENCES bookings (id) ON DELETE CASCADE,
  CONSTRAINT fk_stay_place   FOREIGN KEY (place_id) REFERENCES places (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Who is on a booking (BKG-03). Drives default expense split (EXP-05).
CREATE TABLE booking_travellers (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id    BIGINT UNSIGNED NOT NULL,
  traveller_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_booking_traveller (booking_id, traveller_id),
  KEY ix_bt_traveller (traveller_id),
  CONSTRAINT fk_bt_booking   FOREIGN KEY (booking_id) REFERENCES bookings (id) ON DELETE CASCADE,
  CONSTRAINT fk_bt_traveller FOREIGN KEY (traveller_id) REFERENCES travellers (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Seat / coach / berth per traveller per leg (BKG-03, STS-04 updates seat_status).
CREATE TABLE segment_seats (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  segment_id    BIGINT UNSIGNED NOT NULL,
  traveller_id  BIGINT UNSIGNED NOT NULL,
  coach         VARCHAR(10)   NULL,
  seat          VARCHAR(10)   NULL,
  berth         VARCHAR(20)   NULL,                     -- LB / MB / UB / SL / SU
  seat_status   VARCHAR(20)   NULL,                     -- CNF / RAC 12 / WL 34 / GNWL 5
  updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_segment_seat (segment_id, traveller_id),
  KEY ix_ss_traveller (traveller_id),
  CONSTRAINT fk_ss_segment   FOREIGN KEY (segment_id) REFERENCES segments (id) ON DELETE CASCADE,
  CONSTRAINT fk_ss_traveller FOREIGN KEY (traveller_id) REFERENCES travellers (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO schema_migrations (version) VALUES ('002_p2_bookings_places');