<?php

function ensure_school_branches_schema(PDO $pdo): void {
    static $done = false;
    if ($done) {
        return;
    }
    $done = true;

    try {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS school_branches (
                id INT AUTO_INCREMENT PRIMARY KEY,
                branch_name VARCHAR(100) NOT NULL,
                branch_code VARCHAR(20) NOT NULL,
                address TEXT NULL,
                status ENUM('active', 'inactive') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY unique_branch_code (branch_code)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");

        $col = $pdo->query("SHOW COLUMNS FROM students LIKE 'branch_id'")->fetch();
        if (!$col) {
            $pdo->exec("ALTER TABLE students ADD COLUMN branch_id INT NULL DEFAULT NULL AFTER class_id");
            try {
                $pdo->exec("ALTER TABLE students ADD CONSTRAINT fk_students_branch_id FOREIGN KEY (branch_id) REFERENCES school_branches(id) ON DELETE SET NULL");
            } catch (Throwable $e) {
                // FK optional on shared hosting
            }
        }

        $defaults = [
            ['মূল শাখা', 'MAIN'],
            ['উত্তর শাখা', 'NORTH'],
            ['দক্ষিণ শাখা', 'SOUTH'],
        ];
        $ins = $pdo->prepare("INSERT IGNORE INTO school_branches (branch_name, branch_code) VALUES (?, ?)");
        foreach ($defaults as $d) {
            $ins->execute($d);
        }
    } catch (Throwable $e) {
        error_log('ensure_school_branches_schema: ' . $e->getMessage());
    }
}

function get_active_school_branches(PDO $pdo): array {
    ensure_school_branches_schema($pdo);
    try {
        return $pdo->query("SELECT id, branch_name, branch_code FROM school_branches WHERE status = 'active' ORDER BY branch_name")->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        return [];
    }
}
