-- =====================================================================
--  PMS Enhancement Migration (v2)
--  Idempotent: safe to run multiple times.
--  Adds new columns/tables required by the enhanced PMS module.
-- =====================================================================

-- ---------- reservations: cancellation, special requests, audit author
SET @s := (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_schema = DATABASE() AND table_name = 'reservations' AND column_name = 'cancelled_at') = 0,
    'ALTER TABLE reservations ADD COLUMN cancelled_at DATETIME NULL',
    'SELECT 1'));
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s := (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_schema = DATABASE() AND table_name = 'reservations' AND column_name = 'cancel_reason') = 0,
    'ALTER TABLE reservations ADD COLUMN cancel_reason VARCHAR(255) NULL',
    'SELECT 1'));
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s := (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_schema = DATABASE() AND table_name = 'reservations' AND column_name = 'special_requests') = 0,
    'ALTER TABLE reservations ADD COLUMN special_requests TEXT NULL',
    'SELECT 1'));
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s := (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_schema = DATABASE() AND table_name = 'reservations' AND column_name = 'created_by') = 0,
    'ALTER TABLE reservations ADD COLUMN created_by INT NULL',
    'SELECT 1'));
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ---------- night_audits: ADR / RevPAR
SET @s := (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_schema = DATABASE() AND table_name = 'night_audits' AND column_name = 'adr') = 0,
    'ALTER TABLE night_audits ADD COLUMN adr DECIMAL(12,2) DEFAULT 0',
    'SELECT 1'));
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s := (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_schema = DATABASE() AND table_name = 'night_audits' AND column_name = 'revpar') = 0,
    'ALTER TABLE night_audits ADD COLUMN revpar DECIMAL(12,2) DEFAULT 0',
    'SELECT 1'));
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ---------- reservation_charges (folio line items)
CREATE TABLE IF NOT EXISTS reservation_charges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reservation_id INT NOT NULL,
    description VARCHAR(255) NOT NULL,
    category VARCHAR(50) DEFAULT 'other',
    amount DECIMAL(12,2) DEFAULT 0,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_res (reservation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- helpful indexes (idempotent)
SET @s := (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
       WHERE table_schema = DATABASE() AND table_name = 'reservations' AND index_name = 'idx_res_status') = 0,
    'CREATE INDEX idx_res_status ON reservations(status)',
    'SELECT 1'));
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s := (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
       WHERE table_schema = DATABASE() AND table_name = 'reservations' AND index_name = 'idx_res_dates') = 0,
    'CREATE INDEX idx_res_dates ON reservations(room_id, check_in, check_out)',
    'SELECT 1'));
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;