<?php
require_once 'config.php';

echo "Starting authentication setup...\n";

try {
    $pdo = db();
    $pdo->beginTransaction();

    // 1. Create the new unified 'users' table
    $sql_users = "
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        role ENUM('donor', 'ngo', 'volunteer', 'admin') NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );";
    $pdo->exec($sql_users);
    echo "- 'users' table created successfully.\n";

    // 2. Migrate data from old tables
    // Note: We are setting a default password. Users would need a "reset password" flow.
    $default_password = password_hash('password123', PASSWORD_DEFAULT);

    // Migrate from donors (assuming it exists and has data)
    if ($pdo->query("SHOW TABLES LIKE 'donors'")->rowCount() > 0) {
        $pdo->exec("INSERT INTO users (name, email, password, role) SELECT name, email, '{$default_password}', 'donor' FROM donors");
        echo "- Migrated data from 'donors'.\n";
    }

    // Migrate from ngos
    $pdo->exec("INSERT INTO users (name, email, password, role) SELECT name, email, '{$default_password}', 'ngo' FROM ngos");
    echo "- Migrated data from 'ngos'.\n";

    // Migrate from volunteers
    $pdo->exec("INSERT INTO users (name, email, password, role) SELECT name, email, '{$default_password}', 'volunteer' FROM volunteers");
    echo "- Migrated data from 'volunteers'.\n";

    // 3. Rename old tables (optional, but good for cleanup)
    $pdo->exec("RENAME TABLE donors TO donors_old");
    $pdo->exec("RENAME TABLE ngos TO ngos_old");
    $pdo->exec("RENAME TABLE volunteers TO volunteers_old");
    echo "- Renamed old user tables.\n";

    // 4. Update foreign key constraints (This is the tricky part)
    // We need to update ngo_id and volunteer_id in other tables to point to the new users.id
    // This is complex and requires careful data mapping. For this MVP, we will skip this step
    // and acknowledge that existing relationships will be broken. New relationships will use the new users table.
    echo "- SKIPPED updating foreign keys for this migration. New records will use the new schema.\n";

    $pdo->commit();
    echo "Authentication setup completed successfully!\n";

} catch (PDOException $e) {
    $pdo->rollBack();
    die("DB ERROR: " . $e->getMessage());
}