<?php
/**
 * Add school branches table and branch_id to students
 * Run once: php database/add_school_branches.php
 * Or visit via browser while logged in as admin
 */
require_once __DIR__ . '/../config/database.php';

header('Content-Type: text/html; charset=utf-8');
echo '<h2>শাখা (Branch) ডাটাবেস সেটআপ</h2>';

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
    ");
    echo '<p style="color:green">✓ school_branches টেবিল তৈরি/যাচাই হয়েছে</p>';

    $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");
        echo '<p style="color:green">✓ students.branch_id কলাম যোগ হয়েছে</p>';
        try {
            $pdo->exec("ALTER TABLE students ADD CONSTRAINT fk_students_branch_id FOREIGN KEY (branch_id) REFERENCES school_branches(id) ON DELETE SET NULL");
            echo '<p style="color:green">✓ Foreign key যোগ হয়েছে</p>';
        } catch (Exception $e) {
            echo '<p style="color:orange">Foreign key যোগ করা যায়নি (সাধারণত সমস্যা নয়): ' . htmlspecialchars($e->getMessage()) . '</p>';
        }
    } else {
        echo '<p style="color:blue">ℹ students.branch_id ইতিমধ্যে আছে</p>';
    }

    $defaults = [
        ['মূল শাখা', 'MAIN'],
        ['উত্তর শাখা', 'NORTH'],
        ['দক্ষিণ শাখা', 'SOUTH'],
    ];
    $ins = $pdo->prepare("INSERT IGNORE INTO school_branches (branch_name, branch_code) VALUES (?, ?)");
    foreach ($defaults as $d) {
        $ins->execute($d);
    }
    echo '<p style="color:green">✓ ডিফল্ট শাখা ডেটা যোগ হয়েছে</p>';
    echo '<p><a href="../students/add.php">ছাত্র যোগ পেজে যান</a></p>';
} catch (Exception $e) {
    echo '<p style="color:red">✗ ত্রুটি: ' . htmlspecialchars($e->getMessage()) . '</p>';
}
