CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT NOT NULL UNIQUE,
  first_name VARCHAR(80),
  last_name VARCHAR(80),
  username VARCHAR(80),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE passengers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  full_name VARCHAR(120) NOT NULL,
  gender ENUM('Male','Female') NOT NULL,
  contact_number VARCHAR(30) NOT NULL,
  passport_last4 VARCHAR(8) NOT NULL,
  passport_enc VARBINARY(255) NOT NULL,
  passport_expiry DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(user_id),
  CONSTRAINT fk_passengers_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE ticket_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  direction ENUM('SG_JB','JB_SG') NOT NULL,
  depart_date DATE NOT NULL,
  times_json JSON NOT NULL,
  passenger_id INT NOT NULL,
  amount INT NOT NULL DEFAULT 1000,
  currency CHAR(3) NOT NULL DEFAULT 'sgd',
  stripe_payment_intent_id VARCHAR(64) NULL,
  stripe_status VARCHAR(32) NULL,
  paid_at DATETIME NULL,
  status ENUM('draft','pending_payment','pending_purchase','completed','cancelled') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX(user_id),
  CONSTRAINT fk_req_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_req_passenger FOREIGN KEY (passenger_id) REFERENCES passengers(id) ON DELETE RESTRICT
);