<?php
// booking_setup_common.php
// Place this file in: /rooms/modules/reservations/booking_setup_common.php
require_once __DIR__ . '/../../includes/bootstrap.php';
require_login();

if (!function_exists('h')) {
    function h($value): string
    {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
}

if (!function_exists('bs_flash')) {
    function bs_flash(string $type, string $message): void
    {
        if (session_status() === PHP_SESSION_NONE) {
            session_start();
        }
        $_SESSION['booking_setup_flash'][] = [
            'type' => $type,
            'message' => $message,
        ];
    }
}

if (!function_exists('bs_get_flash')) {
    function bs_get_flash(): array
    {
        if (session_status() === PHP_SESSION_NONE) {
            session_start();
        }
        $rows = $_SESSION['booking_setup_flash'] ?? [];
        unset($_SESSION['booking_setup_flash']);
        return is_array($rows) ? $rows : [];
    }
}

if (!function_exists('bs_column_exists')) {
    function bs_column_exists(PDO $pdo, string $table, string $column): bool
    {
        static $cache = [];
        $key = $table . '.' . $column;
        if (array_key_exists($key, $cache)) {
            return $cache[$key];
        }
        try {
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table_name AND COLUMN_NAME = :column_name");
            $stmt->execute([':table_name' => $table, ':column_name' => $column]);
            $cache[$key] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            $cache[$key] = false;
        }
        return $cache[$key];
    }
}

if (!function_exists('bs_table_exists')) {
    function bs_table_exists(PDO $pdo, string $table): bool
    {
        try {
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table_name");
            $stmt->execute([':table_name' => $table]);
            return ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            return false;
        }
    }
}

if (!function_exists('bs_safe_code')) {
    function bs_safe_code(string $value): string
    {
        $value = strtoupper(trim($value));
        $value = preg_replace('/[^A-Z0-9_\-]+/', '_', $value);
        $value = trim((string)$value, '_-');
        return $value;
    }
}

if (!function_exists('bs_current_user_id')) {
    function bs_current_user_id(): ?int
    {
        foreach (['user_id', 'consultant_id', 'login_user_id', 'id', 'uid'] as $key) {
            if (isset($_SESSION[$key]) && (int)$_SESSION[$key] > 0) {
                return (int)$_SESSION[$key];
            }
        }
        return null;
    }
}

if (!function_exists('bs_ensure_lookup_table')) {
    function bs_ensure_lookup_table(PDO $pdo, string $table): void
    {
        $pdo->exec("CREATE TABLE IF NOT EXISTS `$table` (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            code VARCHAR(60) NOT NULL,
            name VARCHAR(150) NOT NULL,
            description VARCHAR(255) NULL,
            sort_order INT NOT NULL DEFAULT 1,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            created_by INT UNSIGNED NULL,
            updated_by INT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_{$table}_code (code),
            INDEX idx_{$table}_active (is_active),
            INDEX idx_{$table}_sort (sort_order)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }
}

if (!function_exists('bs_ensure_booking_setup_schema')) {
    function bs_ensure_booking_setup_schema(PDO $pdo): void
    {
        bs_ensure_lookup_table($pdo, 'booking_sources');
        bs_ensure_lookup_table($pdo, 'booking_statuses');
        bs_ensure_lookup_table($pdo, 'booking_payment_statuses');
        bs_ensure_lookup_table($pdo, 'booking_payment_due_types');

        bs_repair_lookup_table($pdo, 'booking_sources');
        bs_repair_lookup_table($pdo, 'booking_statuses');
        bs_repair_lookup_table($pdo, 'booking_payment_statuses');
        bs_repair_lookup_table($pdo, 'booking_payment_due_types');

        $pdo->exec("CREATE TABLE IF NOT EXISTS booking_color_codes (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            rule_key VARCHAR(80) NOT NULL,
            rule_name VARCHAR(150) NOT NULL,
            booking_status_code VARCHAR(60) NULL,
            payment_due_code VARCHAR(60) NULL,
            payment_status_code VARCHAR(60) NULL,
            booking_source_code VARCHAR(60) NULL,
            invoiced_condition ENUM('any','yes','no') NOT NULL DEFAULT 'any',
            balance_condition ENUM('any','has_balance','no_balance') NOT NULL DEFAULT 'any',
            bg_color VARCHAR(20) NOT NULL DEFAULT '#9CA3AF',
            text_color VARCHAR(20) NOT NULL DEFAULT '#FFFFFF',
            priority_no INT NOT NULL DEFAULT 100,
            css_class VARCHAR(100) NULL,
            description VARCHAR(255) NULL,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            created_by INT UNSIGNED NULL,
            updated_by INT UNSIGNED NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_booking_color_codes_key (rule_key),
            INDEX idx_booking_color_codes_priority (is_active, priority_no),
            INDEX idx_booking_color_codes_status (booking_status_code),
            INDEX idx_booking_color_codes_due (payment_due_code),
            INDEX idx_booking_color_codes_paystatus (payment_status_code),
            INDEX idx_booking_color_codes_source (booking_source_code)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");


        /*
         * Repair older booking_color_codes tables.
         * Some sites already had a simpler booking_color_codes table before this setup module
         * was added. CREATE TABLE IF NOT EXISTS does not add missing columns, so we add them
         * safely here before the default seed INSERT uses rule_key and related rule fields.
         */
        $bookingColorColumns = [
            'rule_key'            => "ALTER TABLE booking_color_codes ADD COLUMN rule_key VARCHAR(80) NULL AFTER id",
            'rule_name'           => "ALTER TABLE booking_color_codes ADD COLUMN rule_name VARCHAR(150) NULL AFTER rule_key",
            'booking_status_code' => "ALTER TABLE booking_color_codes ADD COLUMN booking_status_code VARCHAR(60) NULL AFTER rule_name",
            'payment_due_code'    => "ALTER TABLE booking_color_codes ADD COLUMN payment_due_code VARCHAR(60) NULL AFTER booking_status_code",
            'payment_status_code' => "ALTER TABLE booking_color_codes ADD COLUMN payment_status_code VARCHAR(60) NULL AFTER payment_due_code",
            'booking_source_code' => "ALTER TABLE booking_color_codes ADD COLUMN booking_source_code VARCHAR(60) NULL AFTER payment_status_code",
            'invoiced_condition'  => "ALTER TABLE booking_color_codes ADD COLUMN invoiced_condition ENUM('any','yes','no') NOT NULL DEFAULT 'any' AFTER booking_source_code",
            'balance_condition'   => "ALTER TABLE booking_color_codes ADD COLUMN balance_condition ENUM('any','has_balance','no_balance') NOT NULL DEFAULT 'any' AFTER invoiced_condition",
            'bg_color'            => "ALTER TABLE booking_color_codes ADD COLUMN bg_color VARCHAR(20) NOT NULL DEFAULT '#9CA3AF' AFTER balance_condition",
            'text_color'          => "ALTER TABLE booking_color_codes ADD COLUMN text_color VARCHAR(20) NOT NULL DEFAULT '#FFFFFF' AFTER bg_color",
            'priority_no'         => "ALTER TABLE booking_color_codes ADD COLUMN priority_no INT NOT NULL DEFAULT 100 AFTER text_color",
            'css_class'           => "ALTER TABLE booking_color_codes ADD COLUMN css_class VARCHAR(100) NULL AFTER priority_no",
            'description'         => "ALTER TABLE booking_color_codes ADD COLUMN description VARCHAR(255) NULL AFTER css_class",
            'is_active'           => "ALTER TABLE booking_color_codes ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1 AFTER description",
            'created_by'          => "ALTER TABLE booking_color_codes ADD COLUMN created_by INT UNSIGNED NULL AFTER is_active",
            'updated_by'          => "ALTER TABLE booking_color_codes ADD COLUMN updated_by INT UNSIGNED NULL AFTER created_by",
            'created_at'          => "ALTER TABLE booking_color_codes ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER updated_by",
            'updated_at'          => "ALTER TABLE booking_color_codes ADD COLUMN updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP AFTER created_at",
        ];

        foreach ($bookingColorColumns as $column => $ddl) {
            if (!bs_column_exists($pdo, 'booking_color_codes', $column)) {
                try { $pdo->exec($ddl); } catch (Throwable $e) { /* non-fatal; next use will report exact issue */ }
            }
        }

        try {
            $pdo->exec("UPDATE booking_color_codes SET rule_key = UPPER(REPLACE(COALESCE(NULLIF(rule_key,''), NULLIF(code,''), NULLIF(rule_name,''), NULLIF(description,''), CONCAT('RULE_', id)), ' ', '_')) WHERE rule_key IS NULL OR rule_key = ''");
        } catch (Throwable $e) { /* ignore legacy columns that may not exist */ }

        try {
            $pdo->exec("UPDATE booking_color_codes SET rule_name = COALESCE(NULLIF(rule_name,''), NULLIF(name,''), NULLIF(description,''), rule_key) WHERE rule_name IS NULL OR rule_name = ''");
        } catch (Throwable $e) { /* ignore legacy columns that may not exist */ }

        try { $pdo->exec("ALTER TABLE booking_color_codes MODIFY rule_key VARCHAR(80) NOT NULL"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE booking_color_codes MODIFY rule_name VARCHAR(150) NOT NULL"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE booking_color_codes ADD UNIQUE KEY uq_booking_color_codes_key (rule_key)"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE booking_color_codes ADD INDEX idx_booking_color_codes_priority (is_active, priority_no)"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE booking_color_codes ADD INDEX idx_booking_color_codes_status (booking_status_code)"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE booking_color_codes ADD INDEX idx_booking_color_codes_due (payment_due_code)"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE booking_color_codes ADD INDEX idx_booking_color_codes_paystatus (payment_status_code)"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE booking_color_codes ADD INDEX idx_booking_color_codes_source (booking_source_code)"); } catch (Throwable $e) {}

        $orderColumns = [
            'invoiced'          => "ALTER TABLE orders ADD COLUMN invoiced TINYINT(1) NOT NULL DEFAULT 0 AFTER status",
            'booking_source'    => "ALTER TABLE orders ADD COLUMN booking_source VARCHAR(60) NULL AFTER invoiced",
            'payment_due'       => "ALTER TABLE orders ADD COLUMN payment_due VARCHAR(60) NULL AFTER booking_source",
            'invoice_number'    => "ALTER TABLE orders ADD COLUMN invoice_number VARCHAR(120) NULL AFTER payment_due",
            'qrCodeUrl'         => "ALTER TABLE orders ADD COLUMN qrCodeUrl VARCHAR(500) NULL AFTER invoice_number",
            'verificationCode'  => "ALTER TABLE orders ADD COLUMN verificationCode VARCHAR(120) NULL AFTER qrCodeUrl",
            'fiscalDeviceId'    => "ALTER TABLE orders ADD COLUMN fiscalDeviceId VARCHAR(120) NULL AFTER verificationCode",
            'globalNumber'      => "ALTER TABLE orders ADD COLUMN globalNumber VARCHAR(120) NULL AFTER fiscalDeviceId",
            'fiscalDayNumber'   => "ALTER TABLE orders ADD COLUMN fiscalDayNumber VARCHAR(120) NULL AFTER globalNumber",
            'payment_status'    => "ALTER TABLE orders ADD COLUMN payment_status VARCHAR(60) NULL AFTER fiscalDayNumber",
        ];

        foreach ($orderColumns as $column => $ddl) {
            if (!bs_column_exists($pdo, 'orders', $column)) {
                try { $pdo->exec($ddl); } catch (Throwable $e) { /* Keep setup pages usable even when DDL is restricted. */ }
            }
        }

        // Disabled auto-seeding on every page load because existing legacy tables with NOT NULL key columns can fail before the setup page opens.
        // Defaults can be inserted separately with SQL or added through the setup pages.
        // bs_seed_booking_setup_defaults($pdo);
    }
}

if (!function_exists('bs_lookup_key_column')) {
    function bs_lookup_key_column(string $table): string
    {
        $map = [
            'booking_sources' => 'source_key',
            'booking_statuses' => 'status_key',
            'booking_payment_statuses' => 'status_key',
            'booking_payment_due_types' => 'due_key',
        ];
        return $map[$table] ?? '';
    }
}

if (!function_exists('bs_repair_lookup_table')) {
    function bs_repair_lookup_table(PDO $pdo, string $table): void
    {
        $keyColumn = bs_lookup_key_column($table);

        /*
         * Strong repair for older tables:
         * Some earlier setup files created status_key/source_key/due_key as NOT NULL
         * without a default, while the seed insert did not always include that column.
         * Always add the mapped key column when missing, then make it nullable while
         * we backfill it. Inserts below also always include it when mapped.
         */
        if ($keyColumn !== '') {
            try { $pdo->exec("ALTER TABLE `$table` ADD COLUMN `$keyColumn` VARCHAR(60) NULL AFTER id"); } catch (Throwable $e) {}
            try { $pdo->exec("ALTER TABLE `$table` MODIFY `$keyColumn` VARCHAR(60) NULL"); } catch (Throwable $e) {}
        }

        foreach ([
            'code' => "ALTER TABLE `$table` ADD COLUMN code VARCHAR(60) NULL AFTER id",
            'name' => "ALTER TABLE `$table` ADD COLUMN name VARCHAR(150) NULL AFTER code",
            'description' => "ALTER TABLE `$table` ADD COLUMN description VARCHAR(255) NULL AFTER name",
            'sort_order' => "ALTER TABLE `$table` ADD COLUMN sort_order INT NOT NULL DEFAULT 1 AFTER description",
            'is_active' => "ALTER TABLE `$table` ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1 AFTER sort_order",
            'created_by' => "ALTER TABLE `$table` ADD COLUMN created_by INT UNSIGNED NULL AFTER is_active",
            'updated_by' => "ALTER TABLE `$table` ADD COLUMN updated_by INT UNSIGNED NULL AFTER created_by",
            'created_at' => "ALTER TABLE `$table` ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER updated_by",
            'updated_at' => "ALTER TABLE `$table` ADD COLUMN updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP AFTER created_at",
        ] as $column => $ddl) {
            if (!bs_column_exists($pdo, $table, $column)) {
                try { $pdo->exec($ddl); } catch (Throwable $e) {}
            }
        }

        if ($keyColumn !== '') {
            try { $pdo->exec("UPDATE `$table` SET code = COALESCE(NULLIF(code, ''), NULLIF(`$keyColumn`, ''), CONCAT('CODE_', id)) WHERE code IS NULL OR code = ''"); } catch (Throwable $e) {}
            try { $pdo->exec("UPDATE `$table` SET `$keyColumn` = COALESCE(NULLIF(`$keyColumn`, ''), NULLIF(code, ''), CONCAT('KEY_', id)) WHERE `$keyColumn` IS NULL OR `$keyColumn` = ''"); } catch (Throwable $e) {}
            try { $pdo->exec("ALTER TABLE `$table` MODIFY `$keyColumn` VARCHAR(60) NOT NULL DEFAULT ''"); } catch (Throwable $e) {}
            try { $pdo->exec("ALTER TABLE `$table` ADD UNIQUE KEY uq_{$table}_{$keyColumn} (`$keyColumn`)"); } catch (Throwable $e) {}
        }
        try { $pdo->exec("UPDATE `$table` SET name = COALESCE(NULLIF(name, ''), code) WHERE name IS NULL OR name = ''"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE `$table` MODIFY code VARCHAR(60) NOT NULL"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE `$table` MODIFY name VARCHAR(150) NOT NULL"); } catch (Throwable $e) {}
        try { $pdo->exec("ALTER TABLE `$table` ADD UNIQUE KEY uq_{$table}_code (code)"); } catch (Throwable $e) {}
    }
}

if (!function_exists('bs_seed_lookup')) {
    function bs_seed_lookup(PDO $pdo, string $table, array $rows): void
    {
        bs_repair_lookup_table($pdo, $table);
        $keyColumn = bs_lookup_key_column($table);
        /* If this table has a mapped key column, force the insert to include it.
           This avoids MySQL strict-mode errors like: status_key doesn't have a default value. */
     //   $hasKeyColumn = ($keyColumn !== '');
$hasKeyColumn = ($keyColumn !== '');
        if ($hasKeyColumn) {
            $stmt = $pdo->prepare("INSERT INTO `$table` (`$keyColumn`, code, name, description, sort_order, is_active, created_by)
                VALUES (:lookup_key, :code, :name, :description, :sort_order, 1, :created_by)
                ON DUPLICATE KEY UPDATE
                    `$keyColumn` = VALUES(`$keyColumn`),
                    name = VALUES(name),
                    description = VALUES(description),
                    sort_order = VALUES(sort_order),
                    is_active = VALUES(is_active)");
        } else {
            $stmt = $pdo->prepare("INSERT INTO `$table` (code, name, description, sort_order, is_active, created_by)
                VALUES (:code, :name, :description, :sort_order, 1, :created_by)
                ON DUPLICATE KEY UPDATE
                    name = VALUES(name),
                    description = VALUES(description),
                    sort_order = VALUES(sort_order),
                    is_active = VALUES(is_active)");
        }

        foreach ($rows as $row) {
            $params = [
                ':code' => $row['code'],
                ':name' => $row['name'],
                ':description' => $row['description'] ?? null,
                ':sort_order' => (int)($row['sort_order'] ?? 1),
                ':created_by' => bs_current_user_id(),
            ];
            if ($hasKeyColumn) {
                $params[':lookup_key'] = $row['code'];
            }
            $stmt->execute($params);
        }
    }
}

if (!function_exists('bs_seed_booking_setup_defaults')) {
    function bs_seed_booking_setup_defaults(PDO $pdo): void
    {
        bs_seed_lookup($pdo, 'booking_statuses', [
            ['code' => 'PROVISIONAL', 'name' => 'Provisional', 'sort_order' => 10],
            ['code' => 'CONFIRMED', 'name' => 'Confirmed', 'sort_order' => 20],
            ['code' => 'CHECKED_IN', 'name' => 'Checked In', 'sort_order' => 30],
            ['code' => 'CHECKED_OUT', 'name' => 'Checked Out', 'sort_order' => 40],
            ['code' => 'CANCELLED', 'name' => 'Cancelled', 'sort_order' => 90],
            ['code' => 'NO_SHOW', 'name' => 'No Show', 'sort_order' => 95],
        ]);

        bs_seed_lookup($pdo, 'booking_payment_due_types', [
            ['code' => 'ACCOUNT', 'name' => 'Account', 'sort_order' => 10],
            ['code' => 'PAY_ON_ARRIVAL', 'name' => 'Pay On Arrival', 'sort_order' => 20],
            ['code' => 'CHASE_PAYMENT', 'name' => 'Chase Payment', 'sort_order' => 30],
            ['code' => 'ONLINE_PAYMENT', 'name' => 'Online Payment', 'sort_order' => 40],
            ['code' => 'PREPAID', 'name' => 'Prepaid', 'sort_order' => 50],
        ]);

        bs_seed_lookup($pdo, 'booking_payment_statuses', [
            ['code' => 'UNPAID', 'name' => 'Unpaid', 'sort_order' => 10],
            ['code' => 'PART_PAID', 'name' => 'Part Paid', 'sort_order' => 20],
            ['code' => 'PAID', 'name' => 'Paid', 'sort_order' => 30],
            ['code' => 'INVOICED', 'name' => 'Invoiced', 'sort_order' => 40],
            ['code' => 'ACTIVE_OUT_BALANCE', 'name' => 'Active Out With Balance', 'sort_order' => 50],
        ]);

        bs_seed_lookup($pdo, 'booking_sources', [
            ['code' => 'DIRECT', 'name' => 'Direct', 'sort_order' => 10],
            ['code' => 'AGENT', 'name' => 'Agent', 'sort_order' => 20],
            ['code' => 'ONLINE', 'name' => 'Online', 'sort_order' => 30],
            ['code' => 'WALK_IN', 'name' => 'Walk In', 'sort_order' => 40],
            ['code' => 'GROUP', 'name' => 'Group', 'sort_order' => 50],
        ]);

        $stmt = $pdo->prepare("INSERT INTO booking_color_codes
            (rule_key, rule_name, booking_status_code, payment_due_code, payment_status_code, booking_source_code, invoiced_condition, balance_condition, bg_color, text_color, priority_no, css_class, description, is_active, created_by)
            VALUES
            (:rule_key, :rule_name, :booking_status_code, :payment_due_code, :payment_status_code, :booking_source_code, :invoiced_condition, :balance_condition, :bg_color, :text_color, :priority_no, :css_class, :description, 1, :created_by)
            ON DUPLICATE KEY UPDATE
                rule_name = VALUES(rule_name),
                booking_status_code = VALUES(booking_status_code),
                payment_due_code = VALUES(payment_due_code),
                payment_status_code = VALUES(payment_status_code),
                booking_source_code = VALUES(booking_source_code),
                invoiced_condition = VALUES(invoiced_condition),
                balance_condition = VALUES(balance_condition),
                bg_color = VALUES(bg_color),
                text_color = VALUES(text_color),
                priority_no = VALUES(priority_no),
                css_class = VALUES(css_class),
                description = VALUES(description),
                is_active = VALUES(is_active)");

        $rules = [
            ['ACTIVE_OUT', 'Active Out - Balance', 'CHECKED_OUT', null, null, null, 'any', 'has_balance', '#DC2626', '#FFFFFF', 10, 'booking-active-out', 'Checked out but still has outstanding balance.'],
            ['CHASE_PAYMENT', 'Chase Payment', null, 'CHASE_PAYMENT', null, null, 'any', 'any', '#EC4899', '#FFFFFF', 20, 'booking-chase-payment', 'Booking requires payment follow-up.'],
            ['PAY_ON_ARRIVAL', 'Pay On Arrival', null, 'PAY_ON_ARRIVAL', null, null, 'any', 'any', '#8B5CF6', '#FFFFFF', 30, 'booking-pay-on-arrival', 'Guest will pay on arrival.'],
            ['CHECKED_IN', 'Checked In', 'CHECKED_IN', null, null, null, 'any', 'any', '#22C55E', '#FFFFFF', 40, 'booking-checked-in', 'Guest is checked in.'],
            ['ONLINE_PAYMENT', 'Online Payment', null, 'ONLINE_PAYMENT', null, 'ONLINE', 'any', 'any', '#F97316', '#FFFFFF', 50, 'booking-online-payment', 'Online payment or online source booking.'],
            ['CONFIRMED', 'Confirmed', 'CONFIRMED', null, null, null, 'any', 'any', '#FACC15', '#111827', 60, 'booking-confirmed', 'Confirmed booking.'],
            ['INVOICED', 'Invoiced', null, null, 'INVOICED', null, 'yes', 'any', '#FDBA74', '#111827', 70, 'booking-invoiced', 'Booking has been invoiced.'],
            ['PROVISIONAL', 'Provisional', 'PROVISIONAL', null, null, null, 'any', 'any', '#9CA3AF', '#FFFFFF', 80, 'booking-provisional', 'Provisional booking.'],
        ];

        foreach ($rules as $r) {
            $stmt->execute([
                ':rule_key' => $r[0],
                ':rule_name' => $r[1],
                ':booking_status_code' => $r[2],
                ':payment_due_code' => $r[3],
                ':payment_status_code' => $r[4],
                ':booking_source_code' => $r[5],
                ':invoiced_condition' => $r[6],
                ':balance_condition' => $r[7],
                ':bg_color' => $r[8],
                ':text_color' => $r[9],
                ':priority_no' => $r[10],
                ':css_class' => $r[11],
                ':description' => $r[12],
                ':created_by' => bs_current_user_id(),
            ]);
        }
    }
}

if (!function_exists('bs_get_lookup_options')) {
    function bs_get_lookup_options(PDO $pdo, string $table, bool $includeBlank = true): array
    {
        bs_ensure_booking_setup_schema($pdo);
        $stmt = $pdo->query("SELECT code, name FROM `$table` WHERE is_active = 1 ORDER BY sort_order ASC, name ASC");
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        if ($includeBlank) {
            array_unshift($rows, ['code' => '', 'name' => 'Any / Not Applicable']);
        }
        return $rows;
    }
}

if (!function_exists('bs_handle_lookup_post')) {
    function bs_handle_lookup_post(PDO $pdo, string $table, string $redirect): void
    {
        if (($_SERVER['REQUEST_METHOD'] ?? 'GET') !== 'POST') {
            return;
        }
        if (function_exists('verify_csrf')) {
            verify_csrf();
        }

        bs_repair_lookup_table($pdo, $table);

        $action = (string)($_POST['action'] ?? '');
        $id = (int)($_POST['id'] ?? 0);
        $keyColumn = bs_lookup_key_column($table);
        // If this lookup table maps to a key column, always use that column in INSERT/UPDATE.
        // This avoids MySQL strict-mode errors on legacy tables where status_key/source_key/due_key is NOT NULL.
        $hasKeyColumn = ($keyColumn !== '');

        if ($action === 'delete') {
            if ($id <= 0) {
                throw new RuntimeException('Invalid record selected.');
            }
            $stmt = $pdo->prepare("UPDATE `$table` SET is_active = 0, updated_by = :updated_by WHERE id = :id");
            $stmt->execute([
                ':id' => $id,
                ':updated_by' => bs_current_user_id(),
            ]);
            bs_flash('success', 'Record disabled successfully.');
            header('Location: ' . $redirect);
            exit;
        }

        if ($action === 'save') {
            $code = bs_safe_code((string)($_POST['code'] ?? ''));
            $name = trim((string)($_POST['name'] ?? ''));
            $description = trim((string)($_POST['description'] ?? ''));
            $sortOrder = (int)($_POST['sort_order'] ?? 1);
            $isActive = isset($_POST['is_active']) ? 1 : 0;

            if ($code === '') {
                throw new RuntimeException('Code is required.');
            }
            if ($name === '') {
                throw new RuntimeException('Name is required.');
            }

            if ($id > 0) {
                if ($hasKeyColumn) {
                    $stmt = $pdo->prepare("UPDATE `$table`
                        SET `$keyColumn` = :lookup_key,
                            code = :code,
                            name = :name,
                            description = :description,
                            sort_order = :sort_order,
                            is_active = :is_active,
                            updated_by = :updated_by
                        WHERE id = :id");
                    $stmt->execute([
                        ':lookup_key' => $code,
                        ':code' => $code,
                        ':name' => $name,
                        ':description' => $description !== '' ? $description : null,
                        ':sort_order' => $sortOrder,
                        ':is_active' => $isActive,
                        ':updated_by' => bs_current_user_id(),
                        ':id' => $id,
                    ]);
                } else {
                    $stmt = $pdo->prepare("UPDATE `$table`
                        SET code = :code,
                            name = :name,
                            description = :description,
                            sort_order = :sort_order,
                            is_active = :is_active,
                            updated_by = :updated_by
                        WHERE id = :id");
                    $stmt->execute([
                        ':code' => $code,
                        ':name' => $name,
                        ':description' => $description !== '' ? $description : null,
                        ':sort_order' => $sortOrder,
                        ':is_active' => $isActive,
                        ':updated_by' => bs_current_user_id(),
                        ':id' => $id,
                    ]);
                }
            } else {
                if ($hasKeyColumn) {
                    $stmt = $pdo->prepare("INSERT INTO `$table`
                        (`$keyColumn`, code, name, description, sort_order, is_active, created_by)
                        VALUES
                        (:lookup_key, :code, :name, :description, :sort_order, :is_active, :created_by)");
                    $stmt->execute([
                        ':lookup_key' => $code,
                        ':code' => $code,
                        ':name' => $name,
                        ':description' => $description !== '' ? $description : null,
                        ':sort_order' => $sortOrder,
                        ':is_active' => $isActive,
                        ':created_by' => bs_current_user_id(),
                    ]);
                } else {
                    $stmt = $pdo->prepare("INSERT INTO `$table`
                        (code, name, description, sort_order, is_active, created_by)
                        VALUES
                        (:code, :name, :description, :sort_order, :is_active, :created_by)");
                    $stmt->execute([
                        ':code' => $code,
                        ':name' => $name,
                        ':description' => $description !== '' ? $description : null,
                        ':sort_order' => $sortOrder,
                        ':is_active' => $isActive,
                        ':created_by' => bs_current_user_id(),
                    ]);
                }
            }

            bs_flash('success', 'Record saved successfully.');
            header('Location: ' . $redirect);
            exit;
        }
    }
}

if (!function_exists('bs_render_header')) {
    function bs_render_header(string $title): void
    {
        $GLOBALS['pageTitle'] = $title;
        if (file_exists(__DIR__ . '/../../includes/header.php')) {
            include __DIR__ . '/../../includes/header.php';
        } elseif (file_exists(__DIR__ . '/../../header.php')) {
            include __DIR__ . '/../../header.php';
        }
        echo '<style>
            .setup-wrap{max-width:1200px;margin:18px auto;padding:0 12px;}
            .setup-card{background:#fff;border:1px solid #e5e7eb;border-radius:18px;box-shadow:0 10px 25px rgba(15,23,42,.06);overflow:hidden;margin-bottom:18px;}
            .setup-card-header{padding:16px 20px;background:linear-gradient(135deg,#0f172a,#1e3a8a);color:#fff;display:flex;align-items:center;justify-content:space-between;gap:12px;}
            .setup-card-header h3{margin:0;font-size:18px;font-weight:800;}
            .setup-card-body{padding:18px 20px;}
            .setup-grid{display:grid;grid-template-columns:repeat(12,1fr);gap:12px;align-items:end;}
            .setup-col-2{grid-column:span 2}.setup-col-3{grid-column:span 3}.setup-col-4{grid-column:span 4}.setup-col-5{grid-column:span 5}.setup-col-6{grid-column:span 6}.setup-col-12{grid-column:span 12}
            .setup-label{font-weight:700;font-size:12px;color:#334155;margin-bottom:4px;display:block;}
            .setup-input,.setup-select{width:100%;border:1px solid #cbd5e1;border-radius:10px;padding:9px 10px;min-height:38px;background:#fff;}
            .setup-btn{border:0;border-radius:10px;padding:9px 14px;font-weight:800;cursor:pointer;text-decoration:none;display:inline-flex;align-items:center;gap:6px;}
            .setup-btn-primary{background:#2563eb;color:#fff}.setup-btn-danger{background:#dc2626;color:#fff}.setup-btn-light{background:#f1f5f9;color:#0f172a}.setup-btn-dark{background:#0f172a;color:#fff}
            .setup-table{width:100%;border-collapse:collapse;}
            .setup-table th{background:#f8fafc;color:#334155;text-align:left;font-size:12px;text-transform:uppercase;padding:10px;border-bottom:1px solid #e5e7eb;}
            .setup-table td{padding:10px;border-bottom:1px solid #eef2f7;vertical-align:middle;}
            .setup-pill{display:inline-flex;align-items:center;border-radius:999px;padding:4px 9px;font-weight:800;font-size:12px;}
            .setup-tabs{display:flex;flex-wrap:wrap;gap:8px;margin-bottom:14px;}
            .setup-tabs a{padding:8px 12px;border-radius:999px;background:#f1f5f9;color:#0f172a;text-decoration:none;font-weight:800;font-size:13px;}
            .setup-tabs a.active{background:#2563eb;color:#fff;}
            @media(max-width:900px){.setup-grid{grid-template-columns:1fr}.setup-col-2,.setup-col-3,.setup-col-4,.setup-col-5,.setup-col-6,.setup-col-12{grid-column:span 1}}
        </style>';
    }
}

if (!function_exists('bs_render_footer')) {
    function bs_render_footer(): void
    {
        if (file_exists(__DIR__ . '/../../includes/footer.php')) {
            include __DIR__ . '/../../includes/footer.php';
        } elseif (file_exists(__DIR__ . '/../../footer.php')) {
            include __DIR__ . '/../../footer.php';
        }
    }
}

if (!function_exists('bs_render_setup_tabs')) {
    function bs_render_setup_tabs(string $active): void
    {
        $tabs = [
            'booking_sources.php' => ['key' => 'sources', 'label' => 'Booking Sources'],
            'booking_statuses.php' => ['key' => 'statuses', 'label' => 'Booking Statuses'],
            'payment_statuses.php' => ['key' => 'payment_statuses', 'label' => 'Payment Statuses'],
            'payment_due_types.php' => ['key' => 'payment_due', 'label' => 'Payment Due Types'],
            'booking_color_rules.php' => ['key' => 'color_rules', 'label' => 'Booking Color Rules'],
        ];
        echo '<div class="setup-tabs">';
        foreach ($tabs as $href => $tab) {
            $class = $active === $tab['key'] ? 'active' : '';
            echo '<a class="' . h($class) . '" href="' . h($href) . '">' . h($tab['label']) . '</a>';
        }
        echo '</div>';
    }
}

if (!function_exists('bs_render_flashes')) {
    function bs_render_flashes(): void
    {
        foreach (bs_get_flash() as $flash) {
            $type = $flash['type'] === 'success' ? 'success' : 'danger';
            echo '<div class="alert alert-' . h($type) . '">' . h((string)$flash['message']) . '</div>';
        }
    }
}

if (!function_exists('bs_fetch_lookup_rows')) {
    function bs_fetch_lookup_rows(PDO $pdo, string $table): array
    {
        $stmt = $pdo->query("SELECT * FROM `$table` ORDER BY is_active DESC, sort_order ASC, name ASC");
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
}

if (!function_exists('bs_render_lookup_page')) {
    function bs_render_lookup_page(PDO $pdo, string $title, string $table, string $activeTab, string $self): void
    {
        $table = trim($table);
        if ($table === '') {
            throw new RuntimeException('Setup table name is missing.');
        }
        bs_ensure_booking_setup_schema($pdo);
        try { bs_handle_lookup_post($pdo, $table, $self); } catch (Throwable $e) { bs_flash('danger', $e->getMessage()); }
        $editId = (int)($_GET['edit_id'] ?? 0);
        $edit = null;
        if ($editId > 0) {
            $stmt = $pdo->prepare("SELECT * FROM `$table` WHERE id = :id LIMIT 1");
            $stmt->execute([':id' => $editId]);
            $edit = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
        }
        $rows = bs_fetch_lookup_rows($pdo, $table);

        bs_render_header($title);
        echo '<div class="setup-wrap">';
        bs_render_setup_tabs($activeTab);
        bs_render_flashes();
        echo '<div class="setup-card"><div class="setup-card-header"><h3>' . h($title) . '</h3><a class="setup-btn setup-btn-light" href="' . h($self) . '">New</a></div><div class="setup-card-body">';
        echo '<form method="post"><input type="hidden" name="action" value="save"><input type="hidden" name="id" value="' . h((string)($edit['id'] ?? 0)) . '">';
        if (function_exists('csrf_input')) { echo csrf_input(); }
        echo '<div class="setup-grid">';
        echo '<div class="setup-col-3"><label class="setup-label">Code</label><input class="setup-input" name="code" value="' . h($edit['code'] ?? '') . '" placeholder="e.g. DIRECT" required></div>';
        echo '<div class="setup-col-4"><label class="setup-label">Name</label><input class="setup-input" name="name" value="' . h($edit['name'] ?? '') . '" placeholder="Display name" required></div>';
        echo '<div class="setup-col-3"><label class="setup-label">Description</label><input class="setup-input" name="description" value="' . h($edit['description'] ?? '') . '"></div>';
        echo '<div class="setup-col-1"><label class="setup-label">Sort</label><input class="setup-input" type="number" name="sort_order" value="' . h((string)($edit['sort_order'] ?? 1)) . '"></div>';
        $checked = !isset($edit['is_active']) || (int)$edit['is_active'] === 1 ? 'checked' : '';
        echo '<div class="setup-col-1"><label class="setup-label">Active</label><label><input type="checkbox" name="is_active" value="1" ' . $checked . '> Yes</label></div>';
        echo '<div class="setup-col-12"><button class="setup-btn setup-btn-primary" type="submit">Save</button></div>';
        echo '</div></form></div></div>';

        echo '<div class="setup-card"><div class="setup-card-header"><h3>Existing Records</h3></div><div class="setup-card-body"><div class="table-responsive"><table class="setup-table"><thead><tr><th>Code</th><th>Name</th><th>Description</th><th>Sort</th><th>Status</th><th style="width:160px">Action</th></tr></thead><tbody>';
        foreach ($rows as $row) {
            echo '<tr>';
            echo '<td><strong>' . h($row['code']) . '</strong></td>';
            echo '<td>' . h($row['name']) . '</td>';
            echo '<td>' . h($row['description'] ?? '') . '</td>';
            echo '<td>' . h((string)$row['sort_order']) . '</td>';
            echo '<td>' . ((int)$row['is_active'] === 1 ? '<span class="setup-pill" style="background:#dcfce7;color:#166534">Active</span>' : '<span class="setup-pill" style="background:#fee2e2;color:#991b1b">Disabled</span>') . '</td>';
            echo '<td><a class="setup-btn setup-btn-light" href="' . h($self) . '?edit_id=' . (int)$row['id'] . '">Edit</a> ';
            echo '<form method="post" style="display:inline" onsubmit="return confirm(\'Disable this record?\')"><input type="hidden" name="action" value="delete"><input type="hidden" name="id" value="' . (int)$row['id'] . '">';
            if (function_exists('csrf_input')) { echo csrf_input(); }
            echo '<button class="setup-btn setup-btn-danger" type="submit">Disable</button></form></td>';
            echo '</tr>';
        }
        if (!$rows) {
            echo '<tr><td colspan="6">No records found.</td></tr>';
        }
        echo '</tbody></table></div></div></div></div>';
        bs_render_footer();
    }
}
