<?php
/**
 * Database Configuration
 * Uses environment variables for secure credential management
 */

// Load environment configuration
require_once __DIR__ . '/EnvLoader.php';

// Get database credentials from environment
$host = EnvLoader::get('DB_HOST', 'localhost');
$port = EnvLoader::get('DB_PORT', '3306');
$dbname = EnvLoader::get('DB_NAME', 'school_management');
$username = EnvLoader::get('DB_USER', 'root');
$password = EnvLoader::get('DB_PASS', 'root');
$charset = EnvLoader::get('DB_CHARSET', 'utf8mb4');

// kgmb.page.gd on InfinityFree: fix wrong/missing .env still pointing at localhost
$httpHost = $_SERVER['HTTP_HOST'] ?? '';
if (stripos($httpHost, 'kgmb.page.gd') !== false || stripos($httpHost, 'kgmb.totalh.net') !== false) {
    $hostLower = strtolower(trim($host));
    if (in_array($hostLower, ['localhost', '127.0.0.1', ''], true)) {
        $host = 'sql301.infinityfree.com';
    }
    if ($dbname === 'school_management' || $dbname === '') {
        $dbname = 'if0_35599065_kgmb';
    }
    if ($username === 'root' || $username === '') {
        $username = 'if0_35599065';
    }
}

// zashof.com production — always override regardless of .env content
if (stripos($httpHost, 'zashof.com') !== false) {
    $host     = 'localhost';
    $dbname   = 'zashofco_sm';
    $username = 'zashofco_sm';
    $password = 'Doel@24434#';
}

if (!function_exists('buildMysqlDsn')) {
    function buildMysqlDsn($host, $port, $dbname, $charset) {
        $dsn = 'mysql:host=' . $host . ';dbname=' . $dbname . ';charset=' . $charset;
        if ($port !== '' && $port !== '3306') {
            $dsn = 'mysql:host=' . $host . ';port=' . $port . ';dbname=' . $dbname . ';charset=' . $charset;
        }
        return $dsn;
    }
}

// Define base URL for the project
if (!defined('BASE_URL')) {
    $protocol = isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? 'https' : 'http';
    $host_url = isset($_SERVER['HTTP_HOST']) ? $_SERVER['HTTP_HOST'] : 'localhost';
    $basePath = isset($_SERVER['SCRIPT_NAME']) ? dirname(dirname($_SERVER['SCRIPT_NAME'])) : '/bidda';
    $baseUrl = $protocol . '://' . $host_url . $basePath;
    
    if ($baseUrl === $protocol . '://' . $host_url . '/') {
        $baseUrl = $protocol . '://' . $host_url;
    }
    
    define('BASE_URL', $baseUrl);
}

// Define application constants
if (!defined('APP_NAME')) {
    define('APP_NAME', EnvLoader::get('APP_NAME', 'Bidda School Management'));
}

if (!defined('APP_ENV')) {
    define('APP_ENV', EnvLoader::get('APP_ENV', 'production'));
}

if (!defined('APP_DEBUG')) {
    define('APP_DEBUG', EnvLoader::getBool('APP_DEBUG', false));
}

// Set timezone
date_default_timezone_set(EnvLoader::get('APP_TIMEZONE', 'Asia/Dhaka'));

try {
    $pdo = new PDO(buildMysqlDsn($host, $port, $dbname, $charset), $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_TIMEOUT => 30
    ]);

    // Set SQL mode for compatibility
    $pdo->exec("SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'");
    
} catch(PDOException $e) {
    // Log error securely (don't expose details to users in production)
    error_log("Database connection failed: " . $e->getMessage());
    
    if (APP_DEBUG) {
        $hint = '';
        if (strpos($e->getMessage(), '2002') !== false) {
            $hint = ' Use DB_HOST=sql301.infinityfree.com (not localhost/127.0.0.1). Prefer env.local.php next to index.php.';
        }
        die(
            'Database connection failed: ' . $e->getMessage()
            . ' [host=' . $host . ', db=' . $dbname . ', user=' . $username . ']'
            . $hint
        );
    } else {
        die("Unable to connect to database. Please try again later.");
    }
}

// Function to create database if not exists
function createDatabase() {
    global $host, $username, $password, $dbname, $charset;
    
    try {
        $pdo_temp = new PDO("mysql:host=$host;charset=$charset", $username, $password);
        $pdo_temp->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        $sql = "CREATE DATABASE IF NOT EXISTS `$dbname` CHARACTER SET $charset COLLATE {$charset}_unicode_ci";
        $pdo_temp->exec($sql);
        
        return true;
    } catch(PDOException $e) {
        error_log("Database creation error: " . $e->getMessage());
        return false;
    }
}

// Function to create tables
function createTables() {
    global $pdo;
    
    try {
        // Users table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) UNIQUE NOT NULL,
                password VARCHAR(255) NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                role ENUM('admin', 'teacher', 'student', 'parent') NOT NULL,
                status ENUM('active', 'inactive') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        ");
        
        // Classes table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS classes (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50) NOT NULL,
                section VARCHAR(10) NOT NULL,
                class_teacher_id INT,
                capacity INT DEFAULT 40,
                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_class_section (name, section)
            )
        ");
        
        // Subjects table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS subjects (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                code VARCHAR(20) UNIQUE NOT NULL,
                description TEXT,
                status ENUM('active', 'inactive') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        ");
        
        // Teachers table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS teachers (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT UNIQUE,
                employee_id VARCHAR(20) UNIQUE NOT NULL,
                first_name VARCHAR(50) NOT NULL,
                last_name VARCHAR(50) NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                phone VARCHAR(20),
                address TEXT,
                date_of_birth DATE,
                gender ENUM('male', 'female', 'other'),
                qualification VARCHAR(200),
                experience_years INT DEFAULT 0,
                joining_date DATE,
                salary DECIMAL(10,2),
                photo VARCHAR(255),
                status ENUM('active', 'inactive') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
            )
        ");
        
        // School branches
        $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,
                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)
            )
        ");

        // Students table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS students (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT UNIQUE,
                student_id VARCHAR(20) UNIQUE NOT NULL,
                first_name VARCHAR(50) NOT NULL,
                last_name VARCHAR(50) NOT NULL,
                email VARCHAR(100),
                phone VARCHAR(20),
                address TEXT,
                date_of_birth DATE,
                gender ENUM('male', 'female', 'other'),
                blood_group VARCHAR(10) NULL DEFAULT NULL,
                class_id INT,
                branch_id INT NULL DEFAULT NULL,
                roll_number INT,
                admission_date DATE,
                guardian_name VARCHAR(100),
                guardian_phone VARCHAR(20),
                guardian_email VARCHAR(100),
                guardian_address TEXT,
                photo VARCHAR(255),
                status ENUM('active', 'inactive', 'graduated', 'transferred') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
                FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL,
                UNIQUE KEY unique_class_roll (class_id, roll_number)
            )
        ");
        
        // Class subjects mapping
        $pdo->exec("CREATE TABLE IF NOT EXISTS class_subjects (
            id INT AUTO_INCREMENT PRIMARY KEY,
            class_id INT NOT NULL,
            subject_id INT NOT NULL,
            teacher_id INT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
            FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL,
            UNIQUE KEY unique_class_subject (class_id, subject_id)
        )");
        
        // Class teachers mapping
        $pdo->exec("CREATE TABLE IF NOT EXISTS class_teachers (
            id INT AUTO_INCREMENT PRIMARY KEY,
            class_id INT NOT NULL,
            teacher_id INT NOT NULL,
            assignment_type ENUM('primary', 'assistant', 'subject') DEFAULT 'primary',
            assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            assigned_by INT,
            status ENUM('active', 'inactive') DEFAULT 'active',
            FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
            FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
            FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL,
            UNIQUE KEY unique_class_teacher (class_id, teacher_id)
        )");
        
        // Student subjects mapping
        $pdo->exec("CREATE TABLE IF NOT EXISTS student_subjects (
            id INT AUTO_INCREMENT PRIMARY KEY,
            student_id INT NOT NULL,
            subject_id INT NOT NULL,
            is_fourth_subject BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
            FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
            UNIQUE KEY unique_student_subject (student_id, subject_id)
        )");

        // Add is_fourth_subject column if it doesn't exist
        try {
            $pdo->exec("ALTER TABLE student_subjects ADD COLUMN IF NOT EXISTS is_fourth_subject BOOLEAN DEFAULT FALSE AFTER subject_id");
        } catch (Exception $e) {
            // Column might already exist
        }
        
        // Exams table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS exams (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                class_id INT NOT NULL,
                exam_date DATE NOT NULL,
                end_date DATE,
                start_time TIME,
                end_time TIME,
                total_marks INT DEFAULT 100,
                pass_marks INT DEFAULT 40,
                description TEXT,
                status ENUM('scheduled', 'ongoing', 'completed', 'cancelled') DEFAULT 'scheduled',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
            )
        ");
        
        // Exam subjects
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS exam_subjects (
                id INT AUTO_INCREMENT PRIMARY KEY,
                exam_id INT NOT NULL,
                subject_id INT NOT NULL,
                max_marks INT DEFAULT 100,
                pass_marks INT DEFAULT 40,
                exam_date DATE,
                start_time TIME,
                end_time TIME,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
                FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
                UNIQUE KEY unique_exam_subject (exam_id, subject_id)
            )
        ");
        
        // Results table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS results (
                id INT AUTO_INCREMENT PRIMARY KEY,
                student_id INT NOT NULL,
                exam_id INT NOT NULL,
                subject_id INT NOT NULL,
                marks_obtained DECIMAL(5,2),
                grade VARCHAR(5),
                remarks TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
                FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
                FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
                UNIQUE KEY unique_student_exam_subject (student_id, exam_id, subject_id)
            )
        ");
        
        // Attendance table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS attendance (
                id INT AUTO_INCREMENT PRIMARY KEY,
                student_id INT NULL,
                teacher_id INT NULL,
                class_id INT NULL,
                date DATE NOT NULL,
                status ENUM('present', 'absent', 'late', 'excused') NOT NULL,
                remarks TEXT,
                marked_by INT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE SET NULL,
                FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE SET NULL,
                FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL,
                FOREIGN KEY (marked_by) REFERENCES users(id) ON DELETE SET NULL,
                UNIQUE KEY unique_student_date (student_id, date),
                UNIQUE KEY unique_teacher_date (teacher_id, date)
            )
        ");
        
        // Fees table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS fees (
                id INT AUTO_INCREMENT PRIMARY KEY,
                student_id INT NOT NULL,
                fee_type VARCHAR(50) NOT NULL,
                amount DECIMAL(10,2) NOT NULL,
                due_date DATE NOT NULL,
                paid_amount DECIMAL(10,2) DEFAULT 0,
                paid_date DATE,
                status ENUM('pending', 'partial', 'paid', 'overdue') DEFAULT 'pending',
                academic_year VARCHAR(10),
                month VARCHAR(20),
                remarks TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
            )
        ");
        
        // Fee payments table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS fee_payments (
                id INT AUTO_INCREMENT PRIMARY KEY,
                fee_id INT NOT NULL,
                amount DECIMAL(10,2) NOT NULL,
                payment_method ENUM('cash', 'bank', 'online', 'cheque') DEFAULT 'cash',
                transaction_id VARCHAR(100),
                payment_date DATE NOT NULL,
                received_by INT,
                remarks TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (fee_id) REFERENCES fees(id) ON DELETE CASCADE,
                FOREIGN KEY (received_by) REFERENCES users(id) ON DELETE SET NULL,
                INDEX idx_fee_id (fee_id),
                INDEX idx_payment_date (payment_date),
                INDEX idx_payment_method (payment_method)
            )
        ");
        
        // Bank accounts table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS bank_accounts (
                id INT AUTO_INCREMENT PRIMARY KEY,
                bank_name VARCHAR(100) NOT NULL,
                branch_name VARCHAR(100) NOT NULL,
                account_no VARCHAR(50) NOT NULL,
                account_name VARCHAR(100) NOT NULL,
                account_type ENUM('savings', 'current', 'fixed', 'mobile') DEFAULT 'savings',
                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_account (bank_name, branch_name, account_no)
            )
        ");
        
        // Bank transactions table
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS bank_transactions (
                id INT AUTO_INCREMENT PRIMARY KEY,
                student_id INT NOT NULL,
                bank_account_id INT NOT NULL,
                amount DECIMAL(10,2) NOT NULL,
                transaction_id VARCHAR(100),
                receipt_image VARCHAR(255),
                status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
                submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                verified_at TIMESTAMP NULL,
                verified_by INT NULL,
                notes TEXT,
                FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
                FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE CASCADE,
                INDEX idx_student_id (student_id),
                INDEX idx_bank_account_id (bank_account_id),
                INDEX idx_transaction_id (transaction_id),
                INDEX idx_status (status),
                INDEX idx_submitted_at (submitted_at)
            )
        ");
        
        // Messages log table
        $pdo->exec(
            "CREATE TABLE IF NOT EXISTS messages_log (
                id INT AUTO_INCREMENT PRIMARY KEY,
                message_type VARCHAR(50) NOT NULL,
                message_content TEXT NOT NULL,
                recipient_count INT NOT NULL,
                recipients TEXT,
                sent_recipients TEXT,
                failed_recipients TEXT,
                sender_id INT,
                sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                delivered_at TIMESTAMP NULL,
                status ENUM('sent', 'delivered', 'failed', 'pending') DEFAULT 'pending',
                delivery_report TEXT,
                method VARCHAR(50) DEFAULT 'non_musking',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE SET NULL
            )"
        );
        
        // Teacher leave applications table
        $pdo->exec("CREATE TABLE IF NOT EXISTS teacher_leaves (
            id INT AUTO_INCREMENT PRIMARY KEY,
            teacher_id INT NOT NULL,
            leave_type VARCHAR(50) NOT NULL,
            start_date DATE NOT NULL,
            end_date DATE NOT NULL,
            reason TEXT NOT NULL,
            status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
            applied_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            approved_by INT NULL,
            approved_date TIMESTAMP NULL,
            remarks TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
            FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
        )");
        
        return true;
    } catch(PDOException $e) {
        error_log("Table creation error: " . $e->getMessage());
        return false;
    }
}

// Function to insert default data
function insertDefaultData() {
    global $pdo;
    
    try {
        // Check if admin user exists
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE role = 'admin'");
        $stmt->execute();
        $admin_count = $stmt->fetchColumn();
        
        if ($admin_count == 0) {
            // Create default admin user
            $admin_password = password_hash('admin123', PASSWORD_DEFAULT);
            $stmt = $pdo->prepare("
                INSERT INTO users (username, password, email, role) 
                VALUES ('admin', ?, 'admin@school.com', 'admin')
            ");
            $stmt->execute([$admin_password]);
            
            // Insert default classes
            $classes = [
                ['প্রথম শ্রেণী', 'ক'],
                ['দ্বিতীয় শ্রেণী', 'ক'],
                ['তৃতীয় শ্রেণী', 'ক'],
                ['চতুর্থ শ্রেণী', 'ক'],
                ['পঞ্চম শ্রেণী', 'ক'],
                ['ষষ্ঠ শ্রেণী', 'ক'],
                ['সপ্তম শ্রেণী', 'ক'],
                ['অষ্টম শ্রেণী', 'ক'],
                ['নবম শ্রেণী', 'ক'],
                ['দশম শ্রেণী', 'ক']
            ];
            
            foreach ($classes as $class) {
                $stmt = $pdo->prepare("INSERT INTO classes (name, section) VALUES (?, ?)");
                $stmt->execute($class);
            }
            
            // Insert default subjects
            $subjects = [
                ['বাংলা', 'BAN'],
                ['ইংরেজি', 'ENG'],
                ['গণিত', 'MATH'],
                ['বিজ্ঞান', 'SCI'],
                ['সমাজবিজ্ঞান', 'SOC'],
                ['ধর্ম', 'REL'],
                ['শারীরিক শিক্ষা', 'PE'],
                ['কম্পিউটার', 'COMP']
            ];
            
            foreach ($subjects as $subject) {
                $stmt = $pdo->prepare("INSERT INTO subjects (name, code) VALUES (?, ?)");
                $stmt->execute($subject);
            }
        }
        
        return true;
    } catch(PDOException $e) {
        error_log("Default data insertion error: " . $e->getMessage());
        return false;
    }
}

// Auto-setup only for local development (never on InfinityFree/production — causes timeout/blank page)
$runDbSetup = EnvLoader::get('APP_ENV') === 'development'
    && EnvLoader::getBool('DB_AUTO_SETUP', false);

if ($runDbSetup) {
    if (!createDatabase()) {
        die('Failed to create database');
    }
    if (!createTables()) {
        die('Failed to create tables');
    }
    if (!insertDefaultData()) {
        die('Failed to insert default data');
    }
}

// CSRF Token Helper Functions
if (!function_exists('generate_csrf_token')) {
    function generate_csrf_token() {
        if (empty($_SESSION['csrf_token'])) {
            $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
        }
        return $_SESSION['csrf_token'];
    }
}

if (!function_exists('verify_csrf_token')) {
    function verify_csrf_token($token) {
        return isset($_SESSION['csrf_token']) && hash_equals($_SESSION['csrf_token'], $token);
    }
}

if (!function_exists('csrf_field')) {
    function csrf_field() {
        return '<input type="hidden" name="csrf_token" value="' . generate_csrf_token() . '">';
    }
}

// Session ini (index.php calls session_start)
if (session_status() === PHP_SESSION_NONE) {
    ini_set('session.cookie_httponly', '1');
    if (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') {
        ini_set('session.cookie_secure', '1');
    }
    ini_set('session.cookie_samesite', 'Strict');
    ini_set('session.gc_maxlifetime', (string) EnvLoader::getInt('SESSION_LIFETIME', 7200));
}
