-- 004: Bring the migration files in sync with the live schema.
-- Previously these changes only existed as ad-hoc migrate_*.js scripts (or not at all),
-- so a fresh install via `npm run migrate` produced a schema the app could not run on.
-- migrate.js tolerates "duplicate column/key/table" errors, so this is safe to re-run.

-- users: personal accounts need display_name + active flag (auth.js selects both)
ALTER TABLE users ADD COLUMN display_name VARCHAR(100) NULL AFTER username;
ALTER TABLE users ADD COLUMN active TINYINT(1) NOT NULL DEFAULT 1 AFTER display_name;

-- staff_names: link a staff name to a personal frontdesk account
ALTER TABLE staff_names ADD COLUMN user_id INT NULL;

-- guests: birth data + updated_at (sync/pull relies on updated_at to sync guest edits)
ALTER TABLE guests ADD COLUMN date_of_birth DATE NULL;
ALTER TABLE guests ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- bookings: booking number, currency, source metadata, occupancy, meal plan
ALTER TABLE bookings ADD COLUMN booking_no VARCHAR(20) NULL AFTER id;
ALTER TABLE bookings ADD UNIQUE KEY uq_booking_no (booking_no);
ALTER TABLE bookings ADD COLUMN currency VARCHAR(3) NOT NULL DEFAULT 'EGP' AFTER rate_per_night;
ALTER TABLE bookings MODIFY COLUMN source VARCHAR(100) NOT NULL DEFAULT 'walk_in';
ALTER TABLE bookings ADD COLUMN source_note VARCHAR(255) NULL;
ALTER TABLE bookings ADD COLUMN source_booking_id VARCHAR(100) NULL;
ALTER TABLE bookings ADD COLUMN adults INT NOT NULL DEFAULT 1;
ALTER TABLE bookings ADD COLUMN children INT NOT NULL DEFAULT 0;
ALTER TABLE bookings ADD COLUMN children_ages JSON NULL;
ALTER TABLE bookings ADD COLUMN meal_plan VARCHAR(100) NULL;

-- payments: custom methods are stored by name, so the enum must be widened
ALTER TABLE payments MODIFY COLUMN method VARCHAR(100) NOT NULL DEFAULT 'cash';

-- rooms: housekeeping (also in 003, harmless duplicate)
ALTER TABLE rooms ADD COLUMN housekeeping_status ENUM('clean','dirty','needs_cleaning') NOT NULL DEFAULT 'clean';

-- Lookup tables managed from the Settings page
CREATE TABLE IF NOT EXISTS room_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  UNIQUE KEY uq_room_type_name (name)
);

CREATE TABLE IF NOT EXISTS booking_sources (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  has_booking_id TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_booking_source_name (name)
);

CREATE TABLE IF NOT EXISTS payment_methods (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_payment_method_name (name)
);

CREATE TABLE IF NOT EXISTS hotel_currencies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(3) NOT NULL,
  rate_to_egp DECIMAL(15,6) NOT NULL DEFAULT 1.000000,
  UNIQUE KEY uq_currency_code (code)
);

CREATE TABLE IF NOT EXISTS meal_plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_meal_plan_name (name)
);

CREATE TABLE IF NOT EXISTS booking_expenses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  booking_id CHAR(36) NOT NULL,
  description VARCHAR(255) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_expense_booking FOREIGN KEY (booking_id) REFERENCES bookings(id),
  INDEX idx_expense_booking (booking_id)
);

CREATE TABLE IF NOT EXISTS guest_documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  guest_id CHAR(36) NOT NULL,
  type VARCHAR(30) NOT NULL DEFAULT 'other',
  original_name VARCHAR(255) NOT NULL,
  stored_filename VARCHAR(255) NOT NULL,
  mime_type VARCHAR(100) NOT NULL,
  uploaded_by VARCHAR(100),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_doc_guest FOREIGN KEY (guest_id) REFERENCES guests(id),
  INDEX idx_doc_guest (guest_id)
);

-- Seed system rows (INSERT IGNORE keeps re-runs safe)
INSERT IGNORE INTO booking_sources (name, is_system, has_booking_id) VALUES
  ('walk_in', 1, 0), ('phone', 1, 0), ('other', 1, 0);
INSERT IGNORE INTO payment_methods (name, is_system) VALUES
  ('cash', 1), ('card', 1), ('other', 1);
INSERT IGNORE INTO hotel_currencies (code, rate_to_egp) VALUES ('EGP', 1.000000);