<?php
declare(strict_types=1);
require_once __DIR__ . '/api/bootstrap.php';

header('Content-Type: text/html; charset=utf-8');
echo "<h2>Running Hostiva System Patch v2...</h2><pre>";

$pdo = db();

function patch_exec(PDO $pdo, string $label, string $sql) {
    try {
        $pdo->exec($sql);
        echo "✅ $label\n";
    } catch (Throwable $e) {
        $msg = $e->getMessage();
        if (strpos($msg, 'Duplicate column') !== false || strpos($msg, 'Duplicate key') !== false || strpos($msg, 'already exists') !== false) {
            echo "⏭️  SKIP $label (already done)\n";
        } else {
            echo "❌ ERROR $label: $msg\n";
        }
    }
}

// ── FIX 1: Add missing guest_user_id column to bookings (causes bookings to fail on insert)
patch_exec($pdo, 'Add guest_user_id to bookings',
    "ALTER TABLE bookings ADD COLUMN guest_user_id INT DEFAULT NULL AFTER created_by");

// ── FIX 2: Add missing property_assignments table (for RBAC filtering)
patch_exec($pdo, 'Create property_assignments table', "
    CREATE TABLE IF NOT EXISTS property_assignments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        property_id INT NOT NULL,
        user_id INT NOT NULL,
        role VARCHAR(50) DEFAULT 'staff',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uk_prop_user (property_id, user_id),
        FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
");

// ── FIX 3: Ensure inbox + messaging permissions exist
patch_exec($pdo, 'Add inbox permissions',
    "INSERT IGNORE INTO permissions (code, module, label) VALUES
    ('inbox.view', 'inbox', 'View inbox & chat'),
    ('inbox.manage', 'inbox', 'Manage messaging')");

// ── FIX 4: Grant inbox to ALL roles (staff, manager, admin, viewer, super_admin)
patch_exec($pdo, 'Grant inbox.view to all roles', "
    INSERT IGNORE INTO role_permissions (role_id, permission_id)
    SELECT r.id, p.id FROM roles r, permissions p
    WHERE p.code IN ('inbox.view', 'inbox.manage')
");

// ── FIX 5: Grant reports.view to ALL roles (so all staff can generate reports)
patch_exec($pdo, 'Grant reports.view to all roles', "
    INSERT IGNORE INTO role_permissions (role_id, permission_id)
    SELECT r.id, p.id FROM roles r, permissions p
    WHERE p.code = 'reports.view'
");

// ── FIX 6: Grant bookings.manage to staff (role_id=4) so they can add bookings
patch_exec($pdo, 'Grant bookings.manage to staff', "
    INSERT IGNORE INTO role_permissions (role_id, permission_id)
    SELECT 4, p.id FROM permissions p WHERE p.code IN ('bookings.manage', 'bookings.view')
");

// ── FIX 7: Add OTA V4 permissions to manager & admin if missing
patch_exec($pdo, 'Grant V4 permissions to admin & manager', "
    INSERT IGNORE INTO role_permissions (role_id, permission_id)
    SELECT r.id, p.id FROM roles r, permissions p
    WHERE r.name IN ('super_admin', 'admin', 'manager')
    AND p.code IN ('ota.view','ota.manage','calendar.view','calendar.manage','command.view','automations.view','finance.advanced')
");

// ── FIX 8: Add V5 owner/jobs permissions
patch_exec($pdo, 'Grant V5 permissions to super_admin & admin', "
    INSERT IGNORE INTO role_permissions (role_id, permission_id)
    SELECT r.id, p.id FROM roles r, permissions p
    WHERE r.name IN ('super_admin', 'admin')
    AND p.code IN ('owner.portal','owner.finance','owner.bookings','owner.messages','owner.support','jobs.run','automations.builder')
");

// ── FIX 9: Ensure all V4 permissions exist
patch_exec($pdo, 'Add all V4/V5 permission codes', "
    INSERT IGNORE INTO permissions (code, module, label) VALUES
    ('command.view','command','View command center'),
    ('calendar.view','calendar','View calendar'),
    ('calendar.manage','calendar','Manage calendar'),
    ('ota.view','ota','View OTA sync'),
    ('ota.manage','ota','Manage OTA connections'),
    ('automations.view','automations','View automations'),
    ('automations.manage','automations','Manage automations'),
    ('automations.builder','automations','Edit automation workflows'),
    ('ai.view','ai','View AI insights'),
    ('finance.advanced','finance','Advanced finance & owner statements'),
    ('owner.portal','owner','Access owner portal'),
    ('owner.finance','owner','View owner financials'),
    ('owner.bookings','owner','View owner bookings'),
    ('owner.messages','owner','Owner messaging'),
    ('owner.support','owner','Owner support tickets'),
    ('jobs.run','system','Run background jobs'),
    ('reports.manage','reports','Manage & export reports')
");

// ── FIX 10: Add performance index on bookings.guest_user_id
patch_exec($pdo, 'Add guest_user_id index',
    "ALTER TABLE bookings ADD INDEX idx_guest_user (guest_user_id)");

echo "\n<b>✅ All patches applied! You can now:</b>\n";
echo " - All staff can send & receive messages\n";
echo " - All staff can add/view bookings\n";
echo " - All staff can generate reports\n";
echo " - OTA/Calendar/Operations modules have correct permissions\n";
echo "</pre>";
echo "<p><b>Refresh your system and test the inbox, bookings, and reports.</b></p>";
