CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role ENUM('owner','frontdesk') NOT NULL,
  username VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_username (username)
);

CREATE TABLE IF NOT EXISTS staff_names (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  active TINYINT(1) DEFAULT 1,
  UNIQUE KEY uq_name (name)
);

CREATE TABLE IF NOT EXISTS rooms (
  id INT AUTO_INCREMENT PRIMARY KEY,
  number VARCHAR(10) NOT NULL,
  floor INT NOT NULL,
  type VARCHAR(50) NOT NULL,
  base_rate DECIMAL(10,2) NOT NULL,
  status ENUM('active','maintenance') DEFAULT 'active',
  UNIQUE KEY uq_number (number)
);

CREATE TABLE IF NOT EXISTS guests (
  id CHAR(36) PRIMARY KEY,
  full_name VARCHAR(255) NOT NULL,
  nationality VARCHAR(100),
  id_or_passport_no VARCHAR(100),
  phone VARCHAR(50),
  email VARCHAR(255),
  age TINYINT UNSIGNED,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS bookings (
  id CHAR(36) PRIMARY KEY,
  guest_id CHAR(36) NOT NULL,
  room_id INT NOT NULL,
  check_in DATE NOT NULL,
  check_out DATE NOT NULL,
  nights INT NOT NULL,
  rate_per_night DECIMAL(10,2) NOT NULL,
  total_price DECIMAL(10,2) NOT NULL,
  status ENUM('confirmed','checked_in','checked_out','cancelled','no_show') DEFAULT 'confirmed',
  source ENUM('walk_in','phone','other') DEFAULT 'walk_in',
  notes TEXT,
  created_by_staff_name VARCHAR(100),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_booking_guest FOREIGN KEY (guest_id) REFERENCES guests(id),
  CONSTRAINT fk_booking_room FOREIGN KEY (room_id) REFERENCES rooms(id),
  INDEX idx_check_in (check_in),
  INDEX idx_check_out (check_out),
  INDEX idx_status (status),
  INDEX idx_room_dates (room_id, check_in, check_out)
);

CREATE TABLE IF NOT EXISTS payments (
  id CHAR(36) PRIMARY KEY,
  booking_id CHAR(36) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  method ENUM('cash','card','other') DEFAULT 'cash',
  type ENUM('deposit','balance','refund') NOT NULL,
  receipt_no VARCHAR(50),
  created_by_staff_name VARCHAR(100),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_payment_booking FOREIGN KEY (booking_id) REFERENCES bookings(id),
  INDEX idx_payment_booking (booking_id)
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id CHAR(36) PRIMARY KEY,
  entity_type ENUM('booking','payment','guest','room') NOT NULL,
  entity_id VARCHAR(100) NOT NULL,
  action ENUM('create','update','delete','check_in','check_out') NOT NULL,
  staff_name VARCHAR(100),
  changes JSON,
  device_id VARCHAR(100),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  client_created_at DATETIME,
  INDEX idx_entity (entity_type, entity_id),
  INDEX idx_created_at (created_at),
  INDEX idx_staff_name (staff_name)
);