<?php
require_once '../core/bootstrap.php';

// --- Security Check ---
// Only allow access if logged in as Admin/Super Admin
if (!isset($_SESSION['user_id']) || !in_array($_SESSION['user_role'] ?? '', ['super_admin', 'admin'])) {
    die('Access Denied. You must be logged in as an Admin to run this script.');
}

$db = new Database();
echo "<div style='font-family: sans-serif; padding: 20px;'>";
echo "<h1>Database Schema Verification</h1>";

function columnExists($db, $table, $column) {
    try {
        $db->query("SHOW COLUMNS FROM $table LIKE '$column'");
        $res = $db->single();
        return !empty($res);
    } catch (Exception $e) {
        return false;
    }
}

function tableExists($db, $table) {
    try {
        $db->query("SHOW TABLES LIKE '$table'");
        $res = $db->single();
        return !empty($res);
    } catch (Exception $e) {
        return false;
    }
}

// 1. Check Leagues Table Columns
echo "<h3>Checking 'leagues' table...</h3>";
if (tableExists($db, 'leagues')) {
    $league_cols = [
        'reg_fee_total' => 'DECIMAL(10,2) DEFAULT 0.00',
        'reg_deposit_pct' => 'DECIMAL(5,2) DEFAULT 50.00',
        'reg_inst2_pct' => 'DECIMAL(5,2) DEFAULT 30.00',
        'reg_inst2_games' => 'INT DEFAULT 5',
        'reg_inst3_pct' => 'DECIMAL(5,2) DEFAULT 20.00',
        'reg_inst3_games' => 'INT DEFAULT 10'
    ];

    foreach ($league_cols as $col => $def) {
        if (!columnExists($db, 'leagues', $col)) {
            echo "Adding column '$col' to 'leagues'... ";
            try {
                $db->query("ALTER TABLE leagues ADD COLUMN $col $def");
                $db->execute();
                echo "<span style='color:green'>Done</span><br>";
            } catch (Exception $e) {
                echo "<span style='color:red'>Failed: " . $e->getMessage() . "</span><br>";
            }
        } else {
            echo "Column '$col' exists.<br>";
        }
    }
} else {
    echo "<span style='color:red'>Error: 'leagues' table does not exist.</span><br>";
}

// 2. Check Wallets Table
echo "<h3>Checking 'wallets' table...</h3>";
if (tableExists($db, 'wallets')) {
    if (columnExists($db, 'wallets', 'type') && !columnExists($db, 'wallets', 'entity_type')) {
        echo "Renaming 'type' to 'entity_type' in 'wallets'... ";
        try {
            $db->query("ALTER TABLE wallets CHANGE COLUMN type entity_type VARCHAR(50) NOT NULL");
            $db->execute();
            echo "<span style='color:green'>Done</span><br>";
        } catch (Exception $e) {
            echo "<span style='color:red'>Failed: " . $e->getMessage() . "</span><br>";
        }
    } elseif (!columnExists($db, 'wallets', 'entity_type')) {
         echo "Adding 'entity_type' to 'wallets'... ";
         try {
            $db->query("ALTER TABLE wallets ADD COLUMN entity_type VARCHAR(50) NOT NULL AFTER id");
            $db->execute();
            echo "<span style='color:green'>Done</span><br>";
         } catch (Exception $e) {
            echo "<span style='color:red'>Failed: " . $e->getMessage() . "</span><br>";
         }
    } else {
        echo "Column 'entity_type' exists.<br>";
        
        // Check if ENUM contains 'site'
        $db->query("SHOW COLUMNS FROM wallets LIKE 'entity_type'");
        $col = $db->single();
        if ($col && stripos($col->Type, "'site'") === false) {
            echo "Updating 'entity_type' enum to include 'site'... ";
            try {
                $db->query("ALTER TABLE wallets MODIFY COLUMN entity_type ENUM('user','team','club','board','site') NOT NULL");
                $db->execute();
                echo "<span style='color:green'>Done</span><br>";
            } catch (Exception $e) {
                echo "<span style='color:red'>Failed: " . $e->getMessage() . "</span><br>";
            }
        }
    }
} else {
    echo "<span style='color:red'>Error: 'wallets' table does not exist.</span><br>";
}

// 3. Check Wallet Transactions Table
echo "<h3>Checking 'wallet_transactions' table...</h3>";
if (!tableExists($db, 'wallet_transactions') && tableExists($db, 'transactions')) {
    echo "Renaming 'transactions' to 'wallet_transactions'... ";
    try {
        $db->query("RENAME TABLE transactions TO wallet_transactions");
        $db->execute();
        echo "<span style='color:green'>Done</span><br>";
    } catch (Exception $e) {
        echo "<span style='color:red'>Failed: " . $e->getMessage() . "</span><br>";
    }
} elseif (!tableExists($db, 'wallet_transactions')) {
    echo "Creating 'wallet_transactions' table... ";
    try {
        $sql = "CREATE TABLE `wallet_transactions` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `wallet_id` int(11) NOT NULL,
          `type` varchar(50) NOT NULL,
          `amount` decimal(10,2) NOT NULL,
          `description` varchar(255) DEFAULT NULL,
          `reference` varchar(100) DEFAULT NULL,
          `status` varchar(20) DEFAULT 'pending',
          `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
          PRIMARY KEY (`id`),
          KEY `wallet_id` (`wallet_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        $db->query($sql);
        $db->execute();
        echo "<span style='color:green'>Done</span><br>";
    } catch (Exception $e) {
        echo "<span style='color:red'>Failed: " . $e->getMessage() . "</span><br>";
    }
} else {
    echo "Table 'wallet_transactions' exists.<br>";
}

if (tableExists($db, 'wallet_transactions')) {
    echo "Updating 'wallet_transactions' type column... ";
    try {
        $db->query("ALTER TABLE `wallet_transactions` CHANGE `type` `type` ENUM('deposit','withdrawal','payment','transfer_in','transfer_out','fine','membership','registration_fee') NOT NULL;");
        $db->execute();
        echo "<span style='color:green'>Done</span><br>";
    } catch (Exception $e) {
        echo "<span style='color:orange'>Warning: " . $e->getMessage() . "</span><br>";
    }
}

echo "<h3>Checking 'push_subscriptions' table...</h3>";
if (!tableExists($db, 'push_subscriptions')) {
    echo "Creating 'push_subscriptions' table... ";
    try {
        $sql = "CREATE TABLE `push_subscriptions` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `user_id` int(11) NOT NULL,
          `endpoint` TEXT NOT NULL,
          `p256dh_key` VARCHAR(255) NOT NULL,
          `auth_key` VARCHAR(255) NOT NULL,
          `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
          PRIMARY KEY (`id`),
          UNIQUE KEY `endpoint_unique` (`endpoint`(512)),
          KEY `user_id` (`user_id`),
          CONSTRAINT `fk_user_subscriptions` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        $db->query($sql);
        $db->execute();
        echo "<span style='color:green'>Done</span><br>";
    } catch (Exception $e) {
        echo "<span style='color:red'>Failed: " . $e->getMessage() . "</span><br>";
    }
} else {
    echo "Table 'push_subscriptions' exists.<br>";
}

echo "<h3>Checking 'referee_reports' table...</h3>";
if (!tableExists($db, 'referee_reports')) {
    echo "Creating 'referee_reports' table... ";
    try {
        $sql = "CREATE TABLE `referee_reports` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `fixture_id` int(11) NOT NULL,
          `referee_id` int(11) NOT NULL,
          `incident_type` varchar(50) NOT NULL,
          `minute` int(11) DEFAULT NULL,
          `description` text NOT NULL,
          `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
          PRIMARY KEY (`id`),
          KEY `fixture_id` (`fixture_id`),
          KEY `referee_id` (`referee_id`),
          CONSTRAINT `fk_report_fixture` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE,
          CONSTRAINT `fk_report_referee` FOREIGN KEY (`referee_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        $db->query($sql);
        $db->execute();
        echo "<span style='color:green'>Done</span><br>";
    } catch (Exception $e) {
        echo "<span style='color:red'>Failed: " . $e->getMessage() . "</span><br>";
    }
} else {
    echo "Table 'referee_reports' exists.<br>";
}

echo "<h3>Checking 'fixtures' table columns...</h3>";
if (tableExists($db, 'fixtures')) {
    if (!columnExists($db, 'fixtures', 'referee_payment_status')) {
        echo "Adding 'referee_payment_status' to 'fixtures'... ";
        try {
            $db->query("ALTER TABLE fixtures ADD COLUMN referee_payment_status ENUM('pending', 'paid') DEFAULT 'pending'");
            $db->execute();
            echo "<span style='color:green'>Done</span><br>";
        } catch (Exception $e) {
            echo "<span style='color:red'>Failed: " . $e->getMessage() . "</span><br>";
        }
    } else {
        echo "Column 'referee_payment_status' exists.<br>";
    }
} else {
    echo "<span style='color:red'>Error: 'fixtures' table does not exist.</span><br>";
}

echo "<h3>Checking 'injuries' table...</h3>";
if (!tableExists($db, 'injuries')) {
    echo "Creating 'injuries' table... ";
    try {
        $sql = "CREATE TABLE `injuries` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `player_id` int(11) NOT NULL,
          `injury_description` varchar(255) NOT NULL,
          `start_date` date NOT NULL,
          `expected_return_date` date DEFAULT NULL,
          `status` enum('Active','Recovering','Fit') NOT NULL DEFAULT 'Active',
          PRIMARY KEY (`id`),
          KEY `player_id` (`player_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        $db->query($sql);
        $db->execute();
        echo "<span style='color:green'>Done</span><br>";
    } catch (Exception $e) {
        echo "<span style='color:red'>Failed: " . $e->getMessage() . "</span><br>";
    }
} else {
    echo "Table 'injuries' exists.<br>";
}

echo "<h3>Checking 'player_suspensions' table...</h3>";
if (!tableExists($db, 'player_suspensions')) {
    echo "Creating 'player_suspensions' table... ";
    try {
        $sql = "CREATE TABLE `player_suspensions` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `player_id` int(11) NOT NULL,
          `reason` varchar(255) NOT NULL,
          `fixture_incurred_id` int(11) DEFAULT NULL,
          `matches_to_serve` int(11) NOT NULL DEFAULT 1,
          `status` enum('Active','Served') NOT NULL DEFAULT 'Active',
          PRIMARY KEY (`id`),
          KEY `player_id` (`player_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        $db->query($sql);
        $db->execute();
        echo "<span style='color:green'>Done</span><br>";
    } catch (Exception $e) {
        echo "<span style='color:red'>Failed: " . $e->getMessage() . "</span><br>";
    }
} else {
    echo "Table 'player_suspensions' exists.<br>";
}

echo "<h3>Checking 'notifications' table...</h3>";
if (!tableExists($db, 'notifications')) {
    echo "Creating 'notifications' table... ";
    try {
        $sql = "CREATE TABLE `notifications` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `user_id` int(11) NOT NULL,
          `type` varchar(50) NOT NULL,
          `message` text NOT NULL,
          `is_read` tinyint(1) DEFAULT 0,
          `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
          PRIMARY KEY (`id`),
          KEY `user_id` (`user_id`),
          CONSTRAINT `fk_notifications_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        $db->query($sql);
        $db->execute();
        echo "<span style='color:green'>Done</span><br>";
    } catch (Exception $e) {
        echo "<span style='color:red'>Failed: " . $e->getMessage() . "</span><br>";
    }
} else {
    echo "Table 'notifications' exists.<br>";
}

echo "<br><hr><strong>Verification Complete.</strong> <a href='dashboard.php'>Return to Dashboard</a>";
echo "</div>";
?>