<?php
/**
 * install_guest_audit.php
 *
 * Upload this file to the application root (beside login.php), sign in as an
 * administrator, then open it once in the browser. It creates the audit table
 * and all guest-related triggers. It is safe to run more than once.
 */
require_once __DIR__ . '/includes/bootstrap.php';
require_login();

header('Content-Type: text/html; charset=utf-8');

function ga_h($value): string
{
    return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
}

function ga_table_exists(PDO $pdo, string $table): bool
{
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t");
    $stmt->execute([':t' => $table]);
    return (int)$stmt->fetchColumn() > 0;
}

function ga_columns(PDO $pdo, string $table): array
{
    $stmt = $pdo->prepare("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t");
    $stmt->execute([':t' => $table]);
    return array_map('strtolower', $stmt->fetchAll(PDO::FETCH_COLUMN));
}

function ga_pick(array $columns, array $candidates): ?string
{
    foreach ($candidates as $candidate) {
        if (in_array(strtolower($candidate), $columns, true)) {
            return $candidate;
        }
    }
    return null;
}

function ga_drop_trigger(PDO $pdo, string $name): void
{
    $pdo->exec('DROP TRIGGER IF EXISTS `' . str_replace('`', '``', $name) . '`');
}

function ga_context_values_sql(): string
{
    return "
        NULLIF(@guest_audit_user_id, 0),
        NULLIF(@guest_audit_username, ''),
        NULLIF(@guest_audit_request_uri, ''),
        NULLIF(@guest_audit_script_name, ''),
        NULLIF(@guest_audit_session_id, ''),
        NULLIF(@guest_audit_ip_address, ''),
        NULLIF(@guest_audit_user_agent, ''),
        NULLIF(@guest_audit_request_method, ''),
        NULLIF(@guest_audit_request_id, ''),
        CURRENT_USER(), CONNECTION_ID(), NOW()
    ";
}

$messages = [];
$errors = [];

try {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS guest_change_audit (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            event_type VARCHAR(40) NOT NULL,
            source_table VARCHAR(80) NOT NULL,
            source_row_id BIGINT NULL,
            order_id BIGINT NULL,
            rooming_id BIGINT NULL,
            guest_id BIGINT NULL,
            old_guest_id BIGINT NULL,
            new_guest_id BIGINT NULL,
            old_firstname VARCHAR(255) NULL,
            old_lastname VARCHAR(255) NULL,
            new_firstname VARCHAR(255) NULL,
            new_lastname VARCHAR(255) NULL,
            old_full_name VARCHAR(520) NULL,
            new_full_name VARCHAR(520) NULL,
            affected_order_ids TEXT NULL,
            app_user_id BIGINT NULL,
            app_username VARCHAR(190) NULL,
            request_uri VARCHAR(1000) NULL,
            script_name VARCHAR(255) NULL,
            session_id VARCHAR(190) NULL,
            ip_address VARCHAR(64) NULL,
            user_agent VARCHAR(1000) NULL,
            request_method VARCHAR(16) NULL,
            request_id VARCHAR(64) NULL,
            mysql_user VARCHAR(190) NULL,
            mysql_connection_id BIGINT NULL,
            changed_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            KEY idx_guest_audit_changed_at (changed_at),
            KEY idx_guest_audit_order (order_id, changed_at),
            KEY idx_guest_audit_guest (guest_id, changed_at),
            KEY idx_guest_audit_request (request_id),
            KEY idx_guest_audit_user (app_user_id, changed_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    $messages[] = 'Created/verified guest_change_audit.';

    $contextColumns = "app_user_id, app_username, request_uri, script_name, session_id, ip_address, user_agent, request_method, request_id, mysql_user, mysql_connection_id, changed_at";
    $contextValues = ga_context_values_sql();

    if (ga_table_exists($pdo, 'orders')) {
        $cols = ga_columns($pdo, 'orders');
        if (in_array('guest_id', $cols, true)) {
            ga_drop_trigger($pdo, 'trg_guest_audit_orders_guest_update');
            $pdo->exec("
                CREATE TRIGGER trg_guest_audit_orders_guest_update
                AFTER UPDATE ON orders
                FOR EACH ROW
                BEGIN
                    IF NOT (OLD.guest_id <=> NEW.guest_id) THEN
                        INSERT INTO guest_change_audit (
                            event_type, source_table, source_row_id, order_id,
                            guest_id, old_guest_id, new_guest_id,
                            old_firstname, old_lastname, new_firstname, new_lastname,
                            old_full_name, new_full_name,
                            {$contextColumns}
                        )
                        SELECT
                            'ORDER_GUEST_LINK_CHANGED', 'orders', NEW.id, NEW.id,
                            NEW.guest_id, OLD.guest_id, NEW.guest_id,
                            og.firstname, og.lastname, ng.firstname, ng.lastname,
                            TRIM(CONCAT(COALESCE(og.firstname,''), ' ', COALESCE(og.lastname,''))),
                            TRIM(CONCAT(COALESCE(ng.firstname,''), ' ', COALESCE(ng.lastname,''))),
                            {$contextValues}
                        FROM (SELECT 1) seed
                        LEFT JOIN guests og ON og.id = OLD.guest_id
                        LEFT JOIN guests ng ON ng.id = NEW.guest_id;
                    END IF;
                END
            ");
            $messages[] = 'Installed orders.guest_id change trigger.';
        }
    }

    if (ga_table_exists($pdo, 'guests')) {
        $cols = ga_columns($pdo, 'guests');
        $first = ga_pick($cols, ['firstname', 'first_name']);
        $last = ga_pick($cols, ['lastname', 'last_name', 'surname']);
        if ($first && $last) {
            $f = '`' . $first . '`';
            $l = '`' . $last . '`';
            ga_drop_trigger($pdo, 'trg_guest_audit_guests_name_update');
            $pdo->exec("
                CREATE TRIGGER trg_guest_audit_guests_name_update
                AFTER UPDATE ON guests
                FOR EACH ROW
                BEGIN
                    IF NOT (OLD.{$f} <=> NEW.{$f}) OR NOT (OLD.{$l} <=> NEW.{$l}) THEN
                        INSERT INTO guest_change_audit (
                            event_type, source_table, source_row_id, guest_id,
                            old_firstname, old_lastname, new_firstname, new_lastname,
                            old_full_name, new_full_name, affected_order_ids,
                            {$contextColumns}
                        )
                        VALUES (
                            'GUEST_NAME_CHANGED', 'guests', NEW.id, NEW.id,
                            OLD.{$f}, OLD.{$l}, NEW.{$f}, NEW.{$l},
                            TRIM(CONCAT(COALESCE(OLD.{$f},''), ' ', COALESCE(OLD.{$l},''))),
                            TRIM(CONCAT(COALESCE(NEW.{$f},''), ' ', COALESCE(NEW.{$l},''))),
                            (SELECT GROUP_CONCAT(o.id ORDER BY o.id SEPARATOR ',') FROM orders o WHERE o.guest_id = NEW.id),
                            {$contextValues}
                        );
                    END IF;
                END
            ");
            $messages[] = 'Installed guests name-change trigger.';
        }
    }

    if (ga_table_exists($pdo, 'order_rooming_guests')) {
        $cols = ga_columns($pdo, 'order_rooming_guests');
        $idCol = ga_pick($cols, ['id']);
        $orderCol = ga_pick($cols, ['order_id']);
        $roomingCol = ga_pick($cols, ['rooming_id', 'order_rooming_id']);
        $guestCol = ga_pick($cols, ['guest_id']);
        $first = ga_pick($cols, ['first_name', 'firstname']);
        $last = ga_pick($cols, ['last_name', 'lastname', 'surname']);
        $full = ga_pick($cols, ['guest_name', 'name', 'full_name']);

        if ($idCol && (($first && $last) || $full)) {
            $oldFirst = $first ? 'OLD.`' . $first . '`' : "NULL";
            $newFirst = $first ? 'NEW.`' . $first . '`' : "NULL";
            $oldLast = $last ? 'OLD.`' . $last . '`' : "NULL";
            $newLast = $last ? 'NEW.`' . $last . '`' : "NULL";
            $oldFull = $full ? 'OLD.`' . $full . '`' : "TRIM(CONCAT(COALESCE({$oldFirst},''), ' ', COALESCE({$oldLast},'')))";
            $newFull = $full ? 'NEW.`' . $full . '`' : "TRIM(CONCAT(COALESCE({$newFirst},''), ' ', COALESCE({$newLast},'')))";
            $changed = $full
                ? "NOT (OLD.`{$full}` <=> NEW.`{$full}`)"
                : "NOT ({$oldFirst} <=> {$newFirst}) OR NOT ({$oldLast} <=> {$newLast})";

            $newOrder = $orderCol ? 'NEW.`' . $orderCol . '`' : ($roomingCol ? "(SELECT r.order_id FROM order_rooming r WHERE r.id = NEW.`{$roomingCol}` LIMIT 1)" : 'NULL');
            $oldOrder = $orderCol ? 'OLD.`' . $orderCol . '`' : ($roomingCol ? "(SELECT r.order_id FROM order_rooming r WHERE r.id = OLD.`{$roomingCol}` LIMIT 1)" : 'NULL');
            $newRooming = $roomingCol ? 'NEW.`' . $roomingCol . '`' : 'NULL';
            $oldRooming = $roomingCol ? 'OLD.`' . $roomingCol . '`' : 'NULL';
            $newGuest = $guestCol ? 'NEW.`' . $guestCol . '`' : 'NULL';
            $oldGuest = $guestCol ? 'OLD.`' . $guestCol . '`' : 'NULL';

            ga_drop_trigger($pdo, 'trg_guest_audit_room_guest_insert');
            ga_drop_trigger($pdo, 'trg_guest_audit_room_guest_update');
            ga_drop_trigger($pdo, 'trg_guest_audit_room_guest_delete');

            $pdo->exec("
                CREATE TRIGGER trg_guest_audit_room_guest_insert
                AFTER INSERT ON order_rooming_guests
                FOR EACH ROW
                BEGIN
                    INSERT INTO guest_change_audit (
                        event_type, source_table, source_row_id, order_id, rooming_id, guest_id,
                        new_firstname, new_lastname, new_full_name,
                        {$contextColumns}
                    ) VALUES (
                        'ROOM_GUEST_INSERTED', 'order_rooming_guests', NEW.`{$idCol}`,
                        {$newOrder}, {$newRooming}, {$newGuest},
                        {$newFirst}, {$newLast}, {$newFull},
                        {$contextValues}
                    );
                END
            ");

            $pdo->exec("
                CREATE TRIGGER trg_guest_audit_room_guest_update
                AFTER UPDATE ON order_rooming_guests
                FOR EACH ROW
                BEGIN
                    IF {$changed} THEN
                        INSERT INTO guest_change_audit (
                            event_type, source_table, source_row_id, order_id, rooming_id, guest_id,
                            old_firstname, old_lastname, new_firstname, new_lastname,
                            old_full_name, new_full_name,
                            {$contextColumns}
                        ) VALUES (
                            'ROOM_GUEST_NAME_CHANGED', 'order_rooming_guests', NEW.`{$idCol}`,
                            {$newOrder}, {$newRooming}, {$newGuest},
                            {$oldFirst}, {$oldLast}, {$newFirst}, {$newLast},
                            {$oldFull}, {$newFull},
                            {$contextValues}
                        );
                    END IF;
                END
            ");

            $pdo->exec("
                CREATE TRIGGER trg_guest_audit_room_guest_delete
                AFTER DELETE ON order_rooming_guests
                FOR EACH ROW
                BEGIN
                    INSERT INTO guest_change_audit (
                        event_type, source_table, source_row_id, order_id, rooming_id, guest_id,
                        old_firstname, old_lastname, old_full_name,
                        {$contextColumns}
                    ) VALUES (
                        'ROOM_GUEST_DELETED', 'order_rooming_guests', OLD.`{$idCol}`,
                        {$oldOrder}, {$oldRooming}, {$oldGuest},
                        {$oldFirst}, {$oldLast}, {$oldFull},
                        {$contextValues}
                    );
                END
            ");
            $messages[] = 'Installed room-occupant insert/update/delete triggers.';
        } else {
            $messages[] = 'order_rooming_guests exists, but its name columns could not be identified.';
        }
    }
} catch (Throwable $e) {
    $errors[] = $e->getMessage();
}
?>
<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Guest Audit Installer</title>
    <style>
        body{font-family:Arial,sans-serif;background:#f4f7fb;color:#172033;margin:0;padding:32px}
        .card{max-width:900px;margin:auto;background:#fff;border-radius:16px;padding:28px;box-shadow:0 16px 45px rgba(21,36,62,.12)}
        h1{margin-top:0}.ok{background:#ecfdf3;border:1px solid #a7f3d0;padding:12px;border-radius:9px;margin:8px 0}.err{background:#fff1f2;border:1px solid #fecdd3;padding:12px;border-radius:9px;margin:8px 0}
        code,pre{background:#f1f5f9;border-radius:7px;padding:3px 6px} pre{padding:14px;overflow:auto}
    </style>
</head>
<body><div class="card">
<h1>Guest audit installation</h1>
<?php foreach ($messages as $message): ?><div class="ok"><?php echo ga_h($message); ?></div><?php endforeach; ?>
<?php foreach ($errors as $error): ?><div class="err"><?php echo ga_h($error); ?></div><?php endforeach; ?>
<?php if (!$errors): ?>
<p><strong>Installation completed.</strong> Replace the live <code>includes/bootstrap.php</code> with the patched file supplied alongside this installer.</p>
<p>To inspect the latest changes:</p>
<pre>SELECT *
FROM guest_change_audit
ORDER BY id DESC
LIMIT 200;</pre>
<p>You may remove this installer from the server after it succeeds. Do not remove the audit table or triggers.</p>
<?php endif; ?>
</div></body></html>
