<?php
require_once __DIR__ . '/config.php';

try {
    // 1. Connect without DB to create it
    $pdo_admin = new PDO('mysql:host='.DB_HOST.';charset=utf8mb4', DB_USER, DB_PASS);
    $pdo_admin->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo_admin->exec("CREATE DATABASE IF NOT EXISTS `".DB_NAME."`");
    $pdo_admin = null; // close connection

    // 2. Now use the standard connection from config.php
    $pdo = db();

    // Create users table
    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50) NOT NULL UNIQUE,
            password VARCHAR(255) NOT NULL,
            role ENUM('admin', 'guru', 'siswa') NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )"
    );
    echo "Table 'users' created successfully or already exists.\n";

    // --- Add new columns to users table (idempotent) ---
    $new_columns = [
        'email' => "ADD COLUMN email VARCHAR(255) NULL UNIQUE AFTER username",
        'google_id' => "ADD COLUMN google_id VARCHAR(255) NULL UNIQUE AFTER email",
        'avatar_url' => "ADD COLUMN avatar_url VARCHAR(255) NULL AFTER google_id",
        'remember_token' => "ADD COLUMN remember_token VARCHAR(255) NULL DEFAULT NULL AFTER password"
    ];

    $check_col_stmt = $pdo->prepare("SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = :db_name AND table_name = 'users' AND column_name = :col_name");

    foreach ($new_columns as $column_name => $alter_statement) {
        $check_col_stmt->execute([':db_name' => DB_NAME, ':col_name' => $column_name]);
        if ($check_col_stmt->fetchColumn() === false) {
            $pdo->exec("ALTER TABLE users " . $alter_statement);
            echo "Column '{$column_name}' added to 'users' table.\n";
        } else {
            echo "Column '{$column_name}' already exists in 'users' table. Skipping.\n";
        }
    }
    
    // Make password nullable for Google-only users
    $pdo->exec("ALTER TABLE users MODIFY password VARCHAR(255) NULL");


    // Create tasks table
    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS tasks (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            description TEXT,
            created_by_user_id INT NOT NULL,
            due_date DATETIME NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE CASCADE
        )"
    );
    echo "Table 'tasks' created successfully or already exists.\n";

    // Create task_assignments table
    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS task_assignments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            task_id INT NOT NULL,
            assigned_to_user_id INT NOT NULL,
            status ENUM('todo', 'in_progress', 'done') NOT NULL DEFAULT 'todo',
            assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            completed_at DATETIME NULL,
            FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
            FOREIGN KEY (assigned_to_user_id) REFERENCES users(id) ON DELETE CASCADE
        )"
    );
    echo "Table 'task_assignments' created successfully or already exists.\n";


    // Insert demo users
    $users = [
        ['username' => 'admin', 'password' => 'admin123', 'role' => 'admin'],
        ['username' => 'guru', 'password' => 'guru123', 'role' => 'guru'],
        ['username' => 'siswa', 'password' => 'siswa123', 'role' => 'siswa']
    ];

    $insert_stmt = $pdo->prepare("INSERT INTO users (username, password, role) VALUES (:username, :password, :role)");
    $check_stmt = $pdo->prepare("SELECT id FROM users WHERE username = :username");

    foreach ($users as $user) {
        $check_stmt->execute(['username' => $user['username']]);
        if ($check_stmt->fetch()) {
            echo "User '{$user['username']}' already exists. Skipping.\n";
        } else {
            $hashed_password = password_hash($user['password'], PASSWORD_DEFAULT);
            $insert_stmt->execute([
                ':username' => $user['username'],
                ':password' => $hashed_password,
                ':role' => $user['role']
            ]);
            echo "User '{$user['username']}' inserted.\n";
        }
    }

    echo "Database setup completed successfully!\n";

} catch (PDOException $e) {
    die("Database error: " . $e->getMessage());
}