SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------- GUEST STAYS (one per reservation that is checked in) ----------
CREATE TABLE IF NOT EXISTS guest_stays (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reservation_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  status ENUM('in_house','checked_out','cancelled') NOT NULL DEFAULT 'in_house',
  check_in_at DATETIME NOT NULL,
  expected_check_out DATE NOT NULL,
  actual_check_out_at DATETIME NULL,
  checked_in_by BIGINT UNSIGNED NULL,
  checked_out_by BIGINT UNSIGNED NULL,
  folio_closed_at DATETIME NULL,
  balance_on_close DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  currency CHAR(3) NOT NULL DEFAULT 'NGN',
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uk_reservation (reservation_id),
  INDEX idx_status (status),
  INDEX idx_customer (customer_id),
  CONSTRAINT fk_gs_res FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE RESTRICT,
  CONSTRAINT fk_gs_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- GUEST FOLIOS (one per stay) ----------
CREATE TABLE IF NOT EXISTS guest_folios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  folio_number VARCHAR(32) NOT NULL UNIQUE,
  guest_stay_id BIGINT UNSIGNED NOT NULL,
  reservation_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  status ENUM('open','settled','closed','void') NOT NULL DEFAULT 'open',
  currency CHAR(3) NOT NULL DEFAULT 'NGN',
  charges_total DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  payments_total DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  opened_at DATETIME NOT NULL,
  settled_at DATETIME NULL,
  closed_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_status (status),
  UNIQUE KEY uk_stay (guest_stay_id),
  CONSTRAINT fk_gf_stay FOREIGN KEY (guest_stay_id) REFERENCES guest_stays(id) ON DELETE RESTRICT,
  CONSTRAINT fk_gf_res FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE RESTRICT,
  CONSTRAINT fk_gf_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- FOLIO LINE ITEMS ----------
CREATE TABLE IF NOT EXISTS guest_folio_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  folio_id BIGINT UNSIGNED NOT NULL,
  item_type ENUM('room','restaurant','bar','laundry','service','facility','tax','discount','adjustment','misc') NOT NULL,
  department VARCHAR(64) NULL,                   -- 'rooms','restaurant','general_bar','vip_bar','spa' etc.
  source_type VARCHAR(64) NULL,                  -- 'reservation_room','pos_order','manual','tax_rule'
  source_id BIGINT UNSIGNED NULL,
  reference VARCHAR(64) NULL,                    -- human-readable idempotency for external refs
  description VARCHAR(255) NOT NULL,
  quantity DECIMAL(12,3) NOT NULL DEFAULT 1.000,
  unit_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  tax_code VARCHAR(32) NULL,
  tax_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  currency CHAR(3) NOT NULL DEFAULT 'NGN',
  posted_by BIGINT UNSIGNED NULL,
  posted_at DATETIME NOT NULL,
  voided_at DATETIME NULL,
  voided_by BIGINT UNSIGNED NULL,
  void_reason VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_folio (folio_id, posted_at),
  INDEX idx_source (source_type, source_id),
  INDEX idx_department (department)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- FOLIO PAYMENTS (settlements against the folio, distinct from booking deposits) ----------
CREATE TABLE IF NOT EXISTS folio_payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  folio_id BIGINT UNSIGNED NOT NULL,
  payment_transaction_id BIGINT UNSIGNED NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency CHAR(3) NOT NULL,
  method ENUM('cash','card','bank_transfer','pos','room_charge','other') NOT NULL,
  reference VARCHAR(64) NULL,
  status ENUM('pending','posted','voided') NOT NULL DEFAULT 'posted',
  notes VARCHAR(255) NULL,
  received_by BIGINT UNSIGNED NULL,
  received_at DATETIME NOT NULL,
  voided_at DATETIME NULL,
  voided_by BIGINT UNSIGNED NULL,
  void_reason VARCHAR(255) NULL,
  INDEX idx_folio (folio_id),
  CONSTRAINT fk_fp_folio FOREIGN KEY (folio_id) REFERENCES guest_folios(id) ON DELETE CASCADE,
  CONSTRAINT fk_fp_tx FOREIGN KEY (payment_transaction_id) REFERENCES payment_transactions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- ROOM TRANSFERS ----------
CREATE TABLE IF NOT EXISTS stay_room_transfers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  guest_stay_id BIGINT UNSIGNED NOT NULL,
  from_room_id BIGINT UNSIGNED NULL,
  to_room_id BIGINT UNSIGNED NOT NULL,
  effective_date DATE NOT NULL,
  reason VARCHAR(255) NULL,
  performed_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_stay (guest_stay_id),
  CONSTRAINT fk_srt_stay FOREIGN KEY (guest_stay_id) REFERENCES guest_stays(id) ON DELETE CASCADE,
  CONSTRAINT fk_srt_from FOREIGN KEY (from_room_id) REFERENCES rooms(id) ON DELETE SET NULL,
  CONSTRAINT fk_srt_to FOREIGN KEY (to_room_id) REFERENCES rooms(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- STAY EXTENSIONS / EARLY DEPARTURES ----------
CREATE TABLE IF NOT EXISTS stay_date_changes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  guest_stay_id BIGINT UNSIGNED NOT NULL,
  reservation_room_id BIGINT UNSIGNED NOT NULL,
  change_type ENUM('extend','shorten') NOT NULL,
  old_check_out DATE NOT NULL,
  new_check_out DATE NOT NULL,
  extra_charge DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  performed_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_stay (guest_stay_id),
  CONSTRAINT fk_sdc_stay FOREIGN KEY (guest_stay_id) REFERENCES guest_stays(id) ON DELETE CASCADE,
  CONSTRAINT fk_sdc_rr FOREIGN KEY (reservation_room_id) REFERENCES reservation_rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- INVOICES (financial document separate from the folio ledger) ----------
CREATE TABLE IF NOT EXISTS invoices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_number VARCHAR(32) NOT NULL UNIQUE,
  customer_id BIGINT UNSIGNED NOT NULL,
  reservation_id BIGINT UNSIGNED NULL,
  guest_stay_id BIGINT UNSIGNED NULL,
  folio_id BIGINT UNSIGNED NULL,
  currency CHAR(3) NOT NULL DEFAULT 'NGN',
  subtotal DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  tax_total DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  discount_total DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  total 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,
  status ENUM('draft','issued','partially_paid','paid','void') NOT NULL DEFAULT 'draft',
  issued_at DATETIME NULL,
  due_at DATETIME NULL,
  tax_snapshot_json LONGTEXT NULL,
  billing_name VARCHAR(190) NULL,
  billing_email VARCHAR(190) NULL,
  billing_address VARCHAR(255) NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_customer (customer_id),
  INDEX idx_status (status),
  CONSTRAINT fk_inv_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
  CONSTRAINT fk_inv_res FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE SET NULL,
  CONSTRAINT fk_inv_stay FOREIGN KEY (guest_stay_id) REFERENCES guest_stays(id) ON DELETE SET NULL,
  CONSTRAINT fk_inv_folio FOREIGN KEY (folio_id) REFERENCES guest_folios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoice_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id BIGINT UNSIGNED NOT NULL,
  folio_item_id BIGINT UNSIGNED NULL,
  description VARCHAR(255) NOT NULL,
  department VARCHAR(64) NULL,
  quantity DECIMAL(12,3) NOT NULL DEFAULT 1.000,
  unit_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  tax_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  line_total DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME NOT NULL,
  INDEX idx_invoice (invoice_id),
  CONSTRAINT fk_ii_inv FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS receipts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  receipt_number VARCHAR(32) NOT NULL UNIQUE,
  invoice_id BIGINT UNSIGNED NULL,
  folio_id BIGINT UNSIGNED NULL,
  folio_payment_id BIGINT UNSIGNED NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency CHAR(3) NOT NULL,
  method VARCHAR(32) NOT NULL,
  issued_by BIGINT UNSIGNED NULL,
  issued_at DATETIME NOT NULL,
  INDEX idx_invoice (invoice_id),
  CONSTRAINT fk_rc_inv FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
  CONSTRAINT fk_rc_folio FOREIGN KEY (folio_id) REFERENCES guest_folios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;