CREATE DATABASE IF NOT EXISTS myarrival CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE myarrival;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  email VARCHAR(255) NULL,
  country_code CHAR(2) NULL,
  preferred_language VARCHAR(10) NOT NULL DEFAULT 'en',
  role ENUM('customer','agent','admin') NOT NULL DEFAULT 'customer',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_users_email (email)
) ENGINE=InnoDB;

CREATE TABLE trips (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  arrival_date DATE NULL,
  airport VARCHAR(120) NULL,
  flight_number VARCHAR(40) NULL,
  accommodation VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_trips_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  trip_id BIGINT UNSIGNED NULL,
  service_code VARCHAR(60) NOT NULL,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'MYR',
  status ENUM('pending','paid','cancelled','refunded') NOT NULL DEFAULT 'pending',
  processing_stage ENUM('not_started','information_received','review_started','service_completed') NOT NULL DEFAULT 'not_started',
  refund_eligibility ENUM('eligible','limited','generally_non_refundable','refunded') NOT NULL DEFAULT 'eligible',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_orders_trip FOREIGN KEY (trip_id) REFERENCES trips(id) ON DELETE SET NULL,
  INDEX idx_orders_status (status), INDEX idx_orders_stage (processing_stage)
) ENGINE=InnoDB;

CREATE TABLE policy_versions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  policy_type ENUM('terms','refund','privacy') NOT NULL,
  version VARCHAR(30) NOT NULL,
  title VARCHAR(255) NOT NULL,
  effective_at DATETIME NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_policy_version (policy_type, version)
) ENGINE=InnoDB;

CREATE TABLE consent_records (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NULL,
  terms_policy_id BIGINT UNSIGNED NULL,
  refund_policy_id BIGINT UNSIGNED NULL,
  privacy_policy_id BIGINT UNSIGNED NULL,
  terms_accepted_at DATETIME NULL,
  refund_accepted_at DATETIME NULL,
  privacy_acknowledged_at DATETIME NULL,
  information_confirmed_at DATETIME NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_consent_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_consent_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  CONSTRAINT fk_consent_terms FOREIGN KEY (terms_policy_id) REFERENCES policy_versions(id) ON DELETE SET NULL,
  CONSTRAINT fk_consent_refund FOREIGN KEY (refund_policy_id) REFERENCES policy_versions(id) ON DELETE SET NULL,
  CONSTRAINT fk_consent_privacy FOREIGN KEY (privacy_policy_id) REFERENCES policy_versions(id) ON DELETE SET NULL,
  INDEX idx_consent_order (order_id)
) ENGINE=InnoDB;

CREATE TABLE audit_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  order_id BIGINT UNSIGNED NULL,
  event_type VARCHAR(80) NOT NULL,
  metadata JSON NULL,
  ip_address VARCHAR(45) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_audit_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  INDEX idx_audit_event (event_type), INDEX idx_audit_order (order_id)
) ENGINE=InnoDB;

CREATE TABLE conversations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  trip_id BIGINT UNSIGNED NULL,
  order_id BIGINT UNSIGNED NULL,
  assigned_agent_id BIGINT UNSIGNED NULL,
  channel ENUM('web') NOT NULL DEFAULT 'web',
  status ENUM('waiting','in_progress','resolved','closed') NOT NULL DEFAULT 'waiting',
  priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  topic ENUM('general','mdac','egate','passport','accommodation','flight','family','payment','technical','human_assistance','other') NOT NULL DEFAULT 'general',
  outcome ENUM('unresolved','answered','customer_completed','escalated','refund','product_issue','abandoned','other') NOT NULL DEFAULT 'unresolved',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  resolved_at TIMESTAMP NULL,
  CONSTRAINT fk_conv_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_conv_trip FOREIGN KEY (trip_id) REFERENCES trips(id) ON DELETE SET NULL,
  CONSTRAINT fk_conv_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  CONSTRAINT fk_conv_agent FOREIGN KEY (assigned_agent_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_conv_status (status), INDEX idx_conv_updated (updated_at)
) ENGINE=InnoDB;

CREATE TABLE conversation_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  conversation_id BIGINT UNSIGNED NOT NULL,
  sender_type ENUM('customer','agent','system') NOT NULL,
  sender_id BIGINT UNSIGNED NULL,
  body TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_msg_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
  INDEX idx_msg_conv_created (conversation_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE conversation_tags (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  conversation_id BIGINT UNSIGNED NOT NULL,
  tag VARCHAR(80) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_tag_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
  UNIQUE KEY uq_conv_tag (conversation_id, tag)
) ENGINE=InnoDB;

CREATE TABLE product_insights (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  conversation_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  priority ENUM('low','medium','high') NOT NULL DEFAULT 'medium',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_insight_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(60) NULL,
  provider_reference VARCHAR(120) NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'MYR',
  status ENUM('pending','paid','failed','refunded') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_pay_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  INDEX idx_pay_order (order_id)
) ENGINE=InnoDB;

INSERT INTO policy_versions (policy_type, version, title, effective_at, is_current) VALUES
('terms','1.0','MyArrival Terms & Conditions',NOW(),1),
('refund','1.0','MyArrival Refund Policy',NOW(),1),
('privacy','1.0','MyArrival Privacy Policy',NOW(),1);

ALTER TABLE conversations ADD COLUMN chat_token_hash CHAR(64) NULL AFTER channel;
CREATE UNIQUE INDEX uq_conversations_chat_token ON conversations(chat_token_hash);

CREATE TABLE rate_limits (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bucket VARCHAR(60) NOT NULL,
  key_hash CHAR(64) NOT NULL,
  window_start BIGINT NOT NULL,
  count INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY uq_rate_bucket_key (bucket,key_hash),
  INDEX idx_rate_window (window_start)
) ENGINE=InnoDB;

CREATE TABLE traveller_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NULL,
  nationality VARCHAR(100) NULL,
  passport_number_enc TEXT NULL,
  date_of_birth_enc TEXT NULL,
  passport_country VARCHAR(100) NULL,
  gender VARCHAR(30) NULL,
  accommodation_enc TEXT NULL,
  purpose VARCHAR(80) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_profile_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_profile_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  INDEX idx_profile_user (user_id), INDEX idx_profile_order (order_id)
) ENGINE=InnoDB;
USE myarrival;

ALTER TABLE payments
  ADD COLUMN stripe_checkout_session_id VARCHAR(255) NULL AFTER provider_reference,
  ADD COLUMN stripe_payment_intent_id VARCHAR(255) NULL AFTER stripe_checkout_session_id;

CREATE UNIQUE INDEX uq_payments_stripe_session ON payments(stripe_checkout_session_id);
CREATE INDEX idx_payments_provider_reference ON payments(provider, provider_reference);

ALTER TABLE orders
  ADD COLUMN stripe_checkout_session_id VARCHAR(255) NULL AFTER currency;
CREATE UNIQUE INDEX uq_orders_stripe_session ON orders(stripe_checkout_session_id);

CREATE TABLE stripe_webhook_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  stripe_event_id VARCHAR(255) NOT NULL,
  event_type VARCHAR(120) NOT NULL,
  processed_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_stripe_event (stripe_event_id),
  INDEX idx_stripe_event_type (event_type)
) ENGINE=InnoDB;