
-- Create custom tables for the hardcoded app
CREATE TABLE IF NOT EXISTS kmz_roles (
  user_id BIGINT UNSIGNED NOT NULL,
  role ENUM('super_admin','office_admin','customer') NOT NULL DEFAULT 'customer',
  created_at DATETIME NOT NULL,
  PRIMARY KEY (user_id),
  KEY (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bookings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_code VARCHAR(20) NOT NULL,
  customer_user_id BIGINT UNSIGNED NOT NULL,
  booking_date DATE NOT NULL,
  slot_time TIME NOT NULL,
  pax_adults INT NOT NULL DEFAULT 1,
  pax_kids INT NOT NULL DEFAULT 0,
  status ENUM('pending','reserved','paid','checked_in','cancelled','refunded') NOT NULL DEFAULT 'pending',
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  balance_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  source_order_id BIGINT UNSIGNED NULL, -- Woo order id (if any)
  yoco_checkout_id VARCHAR(64) NULL,
  check_in_at DATETIME NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY (booking_code),
  KEY (customer_user_id),
  KEY (booking_date, slot_time),
  KEY (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS add_ons (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(120) NOT NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0,
  qty INT NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  KEY (booking_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id BIGINT UNSIGNED NULL,
  gateway VARCHAR(40) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(8) NOT NULL DEFAULT 'ZAR',
  external_id VARCHAR(64) NULL, -- yoco payment id / checkout id
  status ENUM('created','pending','paid','failed','refunded') NOT NULL DEFAULT 'created',
  raw_json MEDIUMTEXT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY (booking_id),
  KEY (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS time_slots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  slot_date DATE NOT NULL,
  slot_time TIME NOT NULL,
  capacity_total INT NOT NULL DEFAULT 12,
  capacity_booked INT NOT NULL DEFAULT 0,
  closed_reason VARCHAR(200) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY (slot_date, slot_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  details TEXT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
