SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------- CUSTOMERS ----------
CREATE TABLE IF NOT EXISTS customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,                -- optional link to users table (portal login)
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(32) NOT NULL,
  country VARCHAR(64) NULL,
  city VARCHAR(100) NULL,
  address VARCHAR(255) NULL,
  id_type VARCHAR(32) NULL,                    -- passport, national id, driver license
  id_number VARCHAR(64) NULL,
  marketing_opt_in TINYINT(1) NOT NULL DEFAULT 0,
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_email (email),
  INDEX idx_phone (phone),
  CONSTRAINT fk_cust_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS customer_preferences (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  pref_key VARCHAR(64) NOT NULL,
  pref_value VARCHAR(255) NULL,
  UNIQUE KEY uk_cust_pref (customer_id, pref_key),
  CONSTRAINT fk_cp_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- ROOM INVENTORY ----------
CREATE TABLE IF NOT EXISTS buildings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS floors (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  building_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(64) NOT NULL,
  level INT NOT NULL DEFAULT 0,
  UNIQUE KEY uk_building_level (building_id, level),
  CONSTRAINT fk_floor_building FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS room_types (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(32) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  slug VARCHAR(190) NOT NULL UNIQUE,
  short_description VARCHAR(320) NULL,
  description TEXT NULL,
  max_adults TINYINT UNSIGNED NOT NULL DEFAULT 2,
  max_children TINYINT UNSIGNED NOT NULL DEFAULT 0,
  max_occupancy TINYINT UNSIGNED NOT NULL DEFAULT 2,
  bed_configuration VARCHAR(190) NULL,
  base_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  weekend_price DECIMAL(15,2) NULL,
  extra_adult_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  extra_child_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  min_stay_nights INT NOT NULL DEFAULT 1,
  max_stay_nights INT NOT NULL DEFAULT 30,
  cancellation_policy TEXT NULL,
  is_published TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_published_sort (is_published, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS amenities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  icon VARCHAR(64) NULL,
  category VARCHAR(64) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS room_type_amenities (
  room_type_id BIGINT UNSIGNED NOT NULL,
  amenity_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (room_type_id, amenity_id),
  CONSTRAINT fk_rta_rt FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE CASCADE,
  CONSTRAINT fk_rta_amenity FOREIGN KEY (amenity_id) REFERENCES amenities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS room_type_media (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_type_id BIGINT UNSIGNED NOT NULL,
  media_id BIGINT UNSIGNED NOT NULL,
  is_cover TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  INDEX idx_rtm_rt_sort (room_type_id, sort_order),
  CONSTRAINT fk_rtm_rt FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE CASCADE,
  CONSTRAINT fk_rtm_media FOREIGN KEY (media_id) REFERENCES media_library(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rooms (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_number VARCHAR(32) NOT NULL UNIQUE,
  room_type_id BIGINT UNSIGNED NOT NULL,
  building_id BIGINT UNSIGNED NULL,
  floor_id BIGINT UNSIGNED NULL,
  status ENUM('available','occupied','dirty','cleaning','clean','out_of_order','maintenance','blocked')
      NOT NULL DEFAULT 'available',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  notes VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_status (status),
  INDEX idx_type (room_type_id),
  CONSTRAINT fk_room_rt FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE RESTRICT,
  CONSTRAINT fk_room_building FOREIGN KEY (building_id) REFERENCES buildings(id) ON DELETE SET NULL,
  CONSTRAINT fk_room_floor FOREIGN KEY (floor_id) REFERENCES floors(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- RATE PLANS & SEASONAL RATES ----------
CREATE TABLE IF NOT EXISTS rate_plans (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(32) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  room_type_id BIGINT UNSIGNED NOT NULL,
  price_modifier_type ENUM('fixed','percent_delta') NOT NULL DEFAULT 'fixed',
  price_modifier_value DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  valid_from DATE NULL,
  valid_to DATE NULL,
  min_stay_nights INT NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_rt (room_type_id),
  CONSTRAINT fk_rp_rt FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seasonal_rates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_type_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  date_from DATE NOT NULL,
  date_to DATE NOT NULL,
  price DECIMAL(15,2) NOT NULL,
  priority INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_rt_dates (room_type_id, date_from, date_to),
  CONSTRAINT fk_sr_rt FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- BLACKOUT / MAINTENANCE BLOCKS ----------
CREATE TABLE IF NOT EXISTS room_blocks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id BIGINT UNSIGNED NULL,                   -- NULL = block entire room type
  room_type_id BIGINT UNSIGNED NOT NULL,
  date_from DATE NOT NULL,
  date_to DATE NOT NULL,                          -- inclusive block window
  reason VARCHAR(255) NULL,
  block_type ENUM('maintenance','blackout','hold','out_of_order') NOT NULL DEFAULT 'maintenance',
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_rt_dates (room_type_id, date_from, date_to),
  INDEX idx_room_dates (room_id, date_from, date_to),
  CONSTRAINT fk_rb_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
  CONSTRAINT fk_rb_rt FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- BOOKING HOLDS (short-lived inventory locks) ----------
CREATE TABLE IF NOT EXISTS booking_holds (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference VARCHAR(32) NOT NULL UNIQUE,
  room_type_id BIGINT UNSIGNED NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  check_in DATE NOT NULL,
  check_out DATE NOT NULL,
  expires_at DATETIME NOT NULL,
  session_token VARCHAR(64) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_expires (expires_at),
  INDEX idx_rt_dates (room_type_id, check_in, check_out),
  CONSTRAINT fk_bh_rt FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- RESERVATIONS ----------
CREATE TABLE IF NOT EXISTS reservations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference VARCHAR(32) NOT NULL UNIQUE,
  customer_id BIGINT UNSIGNED NOT NULL,
  status ENUM(
    'inquiry','pending_payment','pending_confirmation','confirmed',
    'checked_in','checked_out','cancellation_requested','cancelled',
    'no_show','refunded','expired'
  ) NOT NULL DEFAULT 'pending_payment',
  check_in DATE NOT NULL,
  check_out DATE NOT NULL,
  adults INT NOT NULL DEFAULT 1,
  children INT NOT NULL DEFAULT 0,
  rooms_count INT NOT NULL DEFAULT 1,
  source ENUM('website','walkin','phone','agent','admin') NOT NULL DEFAULT 'website',
  special_requests TEXT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'NGN',
  subtotal DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  discount_total DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  tax_total DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  total DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  deposit_due DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  amount_paid DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  balance_due DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  promotion_code VARCHAR(32) NULL,
  payment_method VARCHAR(32) NULL,
  hold_expires_at DATETIME NULL,
  confirmed_at DATETIME NULL,
  cancelled_at DATETIME NULL,
  cancellation_reason VARCHAR(255) NULL,
  tax_snapshot_json LONGTEXT NULL,                 -- immutable tax breakdown at booking time
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_status_dates (status, check_in, check_out),
  INDEX idx_customer (customer_id),
  INDEX idx_created (created_at),
  CONSTRAINT fk_res_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reservation_rooms (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reservation_id BIGINT UNSIGNED NOT NULL,
  room_type_id BIGINT UNSIGNED NOT NULL,
  room_id BIGINT UNSIGNED NULL,                    -- assigned at booking (locked) OR later at check-in
  rate_plan_id BIGINT UNSIGNED NULL,
  check_in DATE NOT NULL,                          -- per-room dates (allow different dates per room)
  check_out DATE NOT NULL,
  adults INT NOT NULL DEFAULT 1,
  children INT NOT NULL DEFAULT 0,
  nightly_rate DECIMAL(15,2) NOT NULL DEFAULT 0.00,      -- snapshot
  subtotal DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  extra_guest_charges DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  total DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  rate_snapshot_json LONGTEXT NULL,                -- immutable per-night breakdown
  created_at DATETIME NOT NULL,
  INDEX idx_res (reservation_id),
  INDEX idx_rt_dates (room_type_id, check_in, check_out),
  INDEX idx_room_dates (room_id, check_in, check_out),
  CONSTRAINT fk_rr_res FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE CASCADE,
  CONSTRAINT fk_rr_rt FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE RESTRICT,
  CONSTRAINT fk_rr_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE SET NULL,
  CONSTRAINT fk_rr_rate FOREIGN KEY (rate_plan_id) REFERENCES rate_plans(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reservation_guests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reservation_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(32) NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  age_group ENUM('adult','child','infant') NOT NULL DEFAULT 'adult',
  created_at DATETIME NOT NULL,
  INDEX idx_res (reservation_id),
  CONSTRAINT fk_rg_res FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE CASCADE,
  CONSTRAINT fk_rg_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reservation_status_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reservation_id BIGINT UNSIGNED NOT NULL,
  from_status VARCHAR(32) NULL,
  to_status VARCHAR(32) NOT NULL,
  reason VARCHAR(255) NULL,
  actor_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_res_created (reservation_id, created_at),
  CONSTRAINT fk_rsh_res FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reservation_payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reservation_id BIGINT UNSIGNED NOT NULL,
  payment_transaction_id BIGINT UNSIGNED NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'NGN',
  method VARCHAR(32) NOT NULL,                     -- cash, bank_transfer, card, pos, other
  status ENUM('pending','verified','failed','refunded') NOT NULL DEFAULT 'pending',
  reference VARCHAR(64) NULL,
  paid_at DATETIME NULL,
  verified_by BIGINT UNSIGNED NULL,
  notes VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_res_status (reservation_id, status),
  CONSTRAINT fk_rp_res FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- PAYMENTS ----------
CREATE TABLE IF NOT EXISTS payment_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference VARCHAR(64) NOT NULL UNIQUE,           -- our internal ref
  idempotency_key VARCHAR(128) NOT NULL UNIQUE,
  provider VARCHAR(32) NOT NULL,                   -- paystack, flutterwave, manual_transfer
  provider_reference VARCHAR(128) NULL,
  reservation_id BIGINT UNSIGNED NULL,
  customer_id BIGINT UNSIGNED NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency CHAR(3) NOT NULL,
  status ENUM('initialized','pending','success','failed','refunded','cancelled') NOT NULL DEFAULT 'initialized',
  channel VARCHAR(32) NULL,                        -- card, bank, ussd
  gateway_response_json LONGTEXT NULL,             -- raw provider payload
  initiated_at DATETIME NOT NULL,
  completed_at DATETIME NULL,
  INDEX idx_res (reservation_id),
  INDEX idx_provider_ref (provider, provider_reference),
  INDEX idx_status (status),
  CONSTRAINT fk_pt_res FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE SET NULL,
  CONSTRAINT fk_pt_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payment_webhook_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(32) NOT NULL,
  event_id VARCHAR(128) NOT NULL,                  -- unique per provider to enforce idempotency
  event_type VARCHAR(64) NOT NULL,
  signature_valid TINYINT(1) NOT NULL DEFAULT 0,
  payload_json LONGTEXT NOT NULL,
  processed_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uk_provider_event (provider, event_id),
  INDEX idx_processed (processed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS refunds (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reservation_id BIGINT UNSIGNED NOT NULL,
  payment_transaction_id BIGINT UNSIGNED NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency CHAR(3) NOT NULL,
  reason VARCHAR(255) NOT NULL,
  status ENUM('pending','approved','processed','rejected') NOT NULL DEFAULT 'pending',
  approved_by BIGINT UNSIGNED NULL,
  provider_reference VARCHAR(128) NULL,
  created_at DATETIME NOT NULL,
  processed_at DATETIME NULL,
  INDEX idx_res (reservation_id),
  CONSTRAINT fk_ref_res FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- PROMOTIONS ----------
CREATE TABLE IF NOT EXISTS promotions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(32) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  description TEXT NULL,
  discount_type ENUM('percent','fixed') NOT NULL DEFAULT 'percent',
  discount_value DECIMAL(15,2) NOT NULL,
  min_nights INT NOT NULL DEFAULT 0,
  min_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  valid_from DATE NULL,
  valid_to DATE NULL,
  max_uses INT NULL,
  uses_count INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS promotion_redemptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  promotion_id BIGINT UNSIGNED NOT NULL,
  reservation_id BIGINT UNSIGNED NOT NULL,
  discount_amount DECIMAL(15,2) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uk_promo_res (promotion_id, reservation_id),
  CONSTRAINT fk_pr_promo FOREIGN KEY (promotion_id) REFERENCES promotions(id) ON DELETE RESTRICT,
  CONSTRAINT fk_pr_res FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- TAX RULES ----------
CREATE TABLE IF NOT EXISTS tax_rules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(32) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  rate DECIMAL(8,4) NOT NULL,                     -- e.g. 7.5000 for 7.5%
  is_inclusive TINYINT(1) NOT NULL DEFAULT 0,
  applies_to ENUM('rooms','restaurant','bar','facility','all') NOT NULL DEFAULT 'rooms',
  effective_from DATE NULL,
  effective_to DATE NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- NOTIFICATION QUEUE & TEMPLATES ----------
CREATE TABLE IF NOT EXISTS notification_templates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(64) NOT NULL UNIQUE,               -- booking.confirmed, booking.pending, payment.received
  channel ENUM('email','sms','whatsapp','inapp') NOT NULL,
  subject VARCHAR(255) NULL,
  body TEXT NOT NULL,                             -- supports {{variable}} placeholders
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notification_queue (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  template_code VARCHAR(64) NOT NULL,
  channel ENUM('email','sms','whatsapp','inapp') NOT NULL,
  recipient VARCHAR(190) NOT NULL,
  subject VARCHAR(255) NULL,
  body TEXT NOT NULL,
  context_json LONGTEXT NULL,
  status ENUM('queued','sending','sent','failed','cancelled') NOT NULL DEFAULT 'queued',
  attempts INT NOT NULL DEFAULT 0,
  last_error VARCHAR(255) NULL,
  scheduled_at DATETIME NOT NULL,
  sent_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_status_scheduled (status, scheduled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notification_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  queue_id BIGINT UNSIGNED NULL,
  channel VARCHAR(16) NOT NULL,
  recipient VARCHAR(190) NOT NULL,
  status VARCHAR(16) NOT NULL,
  provider_message_id VARCHAR(128) NULL,
  error_message VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_queue (queue_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;