<?php

function portalSelfHealingSchema(PDO $pdo)
{
    $schema = [

        // STUDENTS TABLE
        'students' => [
            'columns' => [
                'payment_plan_permission' => "ENUM('Both','One-time Only','Installment Only') DEFAULT 'Both'",
                'accommodation_status' => "ENUM('Campus','Off-Campus') DEFAULT 'Campus'",
                'hostel_fee_paid_session' => "VARCHAR(80) NULL",
                'school_fee_paid_session' => "VARCHAR(80) NULL",
                'matric_no' => "VARCHAR(50) NULL",
                'admission_no' => "VARCHAR(50) NULL",
                'application_no' => "VARCHAR(50) NULL"
            ]
        ],

        // PAYMENT TRANSACTIONS
        'payment_transactions' => [
            'create' => "
                CREATE TABLE payment_transactions (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    student_id INT,
                    transaction_type VARCHAR(100),
                    amount DECIMAL(12,2),
                    status VARCHAR(30),
                    reference VARCHAR(120),
                    installment_number INT DEFAULT 1,
                    total_installments INT DEFAULT 1,
                    receipt_data TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            "
        ],

        // AUTO NUMBER SEQUENCES
        'auto_number_sequences' => [
            'create' => "
                CREATE TABLE auto_number_sequences (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    sequence_key VARCHAR(120) UNIQUE,
                    last_seq INT DEFAULT 0,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            "
        ],

        // AUTO NUMBER FORMATS
        'auto_number_formats' => [
            'create' => "
                CREATE TABLE auto_number_formats (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    number_type VARCHAR(20),
                    scope_type VARCHAR(20),
                    scope_id INT DEFAULT 0,
                    prefix VARCHAR(120),
                    affix VARCHAR(120),
                    serial_digits INT DEFAULT 4,
                    start_from INT DEFAULT 1,
                    is_active TINYINT(1) DEFAULT 1,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            "
        ],

        // ACADEMIC SESSIONS
        'academic_sessions' => [
            'create' => "
                CREATE TABLE academic_sessions (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(30) UNIQUE,
                    start_date DATE NULL,
                    end_date DATE NULL,
                    is_current TINYINT(1) DEFAULT 0,
                    is_active TINYINT(1) DEFAULT 1,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            "
        ],

        // EMAIL TEMPLATES
        'email_templates' => [
            'create' => "
                CREATE TABLE email_templates (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    template_key VARCHAR(80) UNIQUE,
                    template_name VARCHAR(120),
                    subject VARCHAR(255),
                    body_html MEDIUMTEXT,
                    is_active TINYINT(1) DEFAULT 1,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            "
        ]
    ];

    foreach ($schema as $table => $rules) {

        if (!portalTableExists($pdo, $table)) {

            if (isset($rules['create'])) {
                $pdo->exec($rules['create']);
            }

        }

        if (isset($rules['columns'])) {

            foreach ($rules['columns'] as $column => $definition) {

                if (!portalColumnExists($pdo, $table, $column)) {

                    $pdo->exec("ALTER TABLE `$table` ADD COLUMN `$column` $definition");

                }

            }

        }

    }
}