<?php

require_once __DIR__ . '/module_importers.php';

define('MIGRATION_ACCESS', true);



// Only catch fatal errors as JSON — do NOT exit on notices/warnings

register_shutdown_function(function() {

    $e = error_get_last();

    if ($e && in_array($e['type'], [E_ERROR, E_PARSE, E_CORE_ERROR, E_COMPILE_ERROR], true)) {

        if (!headers_sent()) header('Content-Type: application/json');

        echo json_encode(['success' => false, 'error' => "{$e['message']} in {$e['file']}:{$e['line']}"]);

    }

});

ini_set('display_errors', '0');

error_reporting(E_ALL);



require_once '../config.php';

require_once '../validation/validator.php';

require_once '../cleansing/phone_formatter.php';

require_once '../cleansing/name_formatter.php';

require_once '../cleansing/deduplicator.php';



header('Content-Type: application/json');



$action = $_GET['action'] ?? '';

$conn = getDBConnection();



if ($action === 'validate') {

    handleValidation($conn);

} elseif ($action === 'import') {

    handleImport($conn);

}



function handleValidation($conn) {

    if (empty($_FILES['files']['name'])) {

        echo json_encode(['success' => false, 'error' => 'No files received. Check PHP upload_max_filesize and post_max_size settings.']);

        return;

    }

    $files  = $_FILES['files'];

    $source = $_POST['source'] ?? '';

    $results = [

        'total' => 0,

        'valid' => 0,

        'warnings' => 0,

        'errors' => 0,

        'issues' => [],

        'suggestions' => [],

        'duplicates' => [],

        'existing_in_db' => [],

        'duplicate_count' => 0,

        'existing_count' => 0

    ];

    

    // Process each uploaded file

    for ($i = 0; $i < count($files['name']); $i++) {

        $file = [

            'name' => $files['name'][$i],

            'tmp_name' => $files['tmp_name'][$i]

        ];

        

        // Determine file type from filename (add-on module types first)

        $type = moduleDetectType($file['name']);

        if ($type !== null) {

            // add-on types validate leniently and skip dedup below

        } elseif (strpos($file['name'], 'student') !== false) {

            $type = 'students';

        } elseif (strpos($file['name'], 'staff') !== false) {

            $type = 'staff';

        } elseif (strpos($file['name'], 'fee') !== false) {

            $type = 'fees';

        } elseif (strpos($file['name'], 'payment') !== false) {

            $type = 'payments';

        } elseif (strpos($file['name'], 'subject') !== false) {

            $type = 'subjects';

        } else {

            continue;

        }

        

        // Check for upload errors on this file

        $uploadError = $files['error'][$i] ?? UPLOAD_ERR_OK;

        if ($uploadError !== UPLOAD_ERR_OK) {

            $results['issues'][] = ['row' => 0, 'type' => 'error', 'field' => 'file', 'message' => "Upload error code {$uploadError} for {$file['name']}"];

            $results['errors']++;

            continue;

        }



        $fileResults = validateCSV($file['tmp_name'], $type, $source, $conn);

        

        // Merge results

        $results['total'] += $fileResults['total'];

        $results['valid'] += $fileResults['valid'];

        $results['warnings'] += $fileResults['warnings'];

        $results['errors'] += $fileResults['errors'];

        $results['issues'] = array_merge($results['issues'], $fileResults['issues']);

        $results['suggestions'] = array_merge($results['suggestions'], $fileResults['suggestions']);

        $results['duplicates'] = array_merge($results['duplicates'], $fileResults['duplicates']);

        $results['existing_in_db'] = array_merge($results['existing_in_db'], $fileResults['existing_in_db']);

    }

    

    $results['duplicate_count'] = count($results['duplicates']);

    $results['existing_count'] = count($results['existing_in_db']);

    

    echo json_encode($results);

}



function handleImport($conn) {

    $files = $_FILES['files'];

    $source = $_POST['source'];

    

    $results = [

        'imported' => 0,

        'failed' => 0,

        'details' => []

    ];

    

    // Start transaction

    $conn->begin_transaction();

    

    try {

        for ($i = 0; $i < count($files['name']); $i++) {

            $file = [

                'name' => $files['name'][$i],

                'tmp_name' => $files['tmp_name'][$i]

            ];

            

            // Determine type (add-on module types first)

            $type = moduleDetectType($file['name']);

            if ($type !== null) {

                $imported = moduleImportDispatch($type, $file['tmp_name'], $conn);

            } elseif (strpos($file['name'], 'student') !== false) {

                $type     = 'students';

                $imported = importStudents($file['tmp_name'], $source, $conn);

            } elseif (strpos($file['name'], 'staff') !== false) {

                $type     = 'staff';

                $imported = importStaff($file['tmp_name'], $source, $conn);

            } elseif (strpos($file['name'], 'fee') !== false) {

                $type     = 'fees';

                $imported = importFees($file['tmp_name'], $source, $conn);

            } elseif (strpos($file['name'], 'payment') !== false) {

                $type     = 'payments';

                $imported = importPayments($file['tmp_name'], $source, $conn);

            } elseif (strpos($file['name'], 'subject') !== false) {

                $type     = 'subjects';

                $imported = importSubjects($file['tmp_name'], $source, $conn);

            } else {

                continue;

            }



            // importStaff returns ['count'=>N, 'credentials'=>[...]], others return int

            if (is_array($imported)) {

                $n = $imported['count'];

                $results['imported'] += $n;

                $results['details'][] = "$type: $n records imported/updated";

                foreach ($imported['credentials'] as $cred) {

                    $results['details'][] = "  ↳ $cred";

                }

            } else {

                $results['imported'] += $imported;

                $results['details'][] = "$type: $imported records imported/updated";

            }

        }

        

        $conn->commit();

        logMigration("Import completed: {$results['imported']} records imported", 'SUCCESS');

        

    } catch (Exception $e) {

        $conn->rollback();

        logMigration("Import failed: " . $e->getMessage(), 'ERROR');

        $results['failed'] = -1;

        $results['error'] = $e->getMessage();

    }

    

    echo json_encode($results);

}



function validateCSV($file, $type, $source, $conn) {

    $handle = $file ? @fopen($file, 'r') : false;

    if (!$handle) {

        return ['total'=>0,'valid'=>0,'warnings'=>0,'errors'=>1,'issues'=>[

            ['row'=>0,'type'=>'error','field'=>'file','message'=>"Could not open uploaded file: $file"]

        ],'suggestions'=>[],'duplicates'=>[],'existing_in_db'=>[]];

    }

    $headers = fgetcsv($handle);

    

    $results = [

        'total' => 0,

        'valid' => 0,

        'warnings' => 0,

        'errors' => 0,

        'issues' => [],

        'suggestions' => [],

        'duplicates' => [],

        'existing_in_db' => []

    ];

    

    $data = [];

    $rowNumber = 1;

    

    while (($row = fgetcsv($handle)) !== FALSE) {

        $results['total']++;

        $rowNumber++;

        

        $record = array_combine($headers, $row);

        $data[] = $record;

        

        // Validate based on type

        $validator = new DataValidator($conn);

        $validation = $validator->validate($record, $type);

        

        if ($validation['valid']) {

            $results['valid']++;

        } else {

            $results['errors']++;

        }

        

        $results['warnings'] += $validation['warnings'];

        

        // Add issues

        foreach ($validation['errors'] as $error) {

            $results['issues'][] = [

                'row' => $rowNumber,

                'type' => 'error',

                'field' => $error['field'],

                'message' => $error['message']

            ];

        }

        

        // Add suggestions

        foreach ($validation['suggestions'] as $suggestion) {

            $results['suggestions'][] = [

                'row' => $rowNumber,

                'field' => $suggestion['field'],

                'message' => $suggestion['message'],

                'value' => $suggestion['value']

            ];

        }

    }

    

    fclose($handle);

    

    // Find duplicates

    $deduplicator = new Deduplicator($conn);

    

    switch ($type) {

        case 'students':

            $results['duplicates'] = $deduplicator->findDuplicateStudents($data);

            $results['existing_in_db'] = $deduplicator->checkExistingInDatabase($data, 'students');

            break;

        case 'staff':

            $results['duplicates'] = $deduplicator->findDuplicateStaff($data);

            $results['existing_in_db'] = $deduplicator->checkExistingInDatabase($data, 'staff');

            break;

        case 'fees':

            $results['duplicates'] = $deduplicator->findDuplicateFees($data);

            $results['existing_in_db'] = $deduplicator->checkExistingInDatabase($data, 'fees');

            break;

        case 'payments':

            $results['duplicates'] = $deduplicator->findDuplicatePayments($data);

            $results['existing_in_db'] = $deduplicator->checkExistingInDatabase($data, 'payments');

            break;

        case 'subjects':

            $results['duplicates'] = $deduplicator->findDuplicateSubjects($data);

            $results['existing_in_db'] = $deduplicator->checkExistingInDatabase($data, 'subjects');

            break;

    }

    

    return $results;

}



/**

 * Return null for N/A, blank, or dash values

 */

function nullIfNA($v) {

    if ($v === null) return null;

    $t = trim((string) $v);

    return ($t === '' || strtoupper($t) === 'N/A' || $t === '-') ? null : $t;

}



/**

 * Generate a unique username from a full name

 */

function generateUsername(string $fullName, mysqli $conn): string {

    $parts = preg_split('/\s+/', strtolower(trim($fullName)));

    $base  = ($parts[0] ?? 'staff') . '.' . (end($parts) ?: 'user');

    $base  = preg_replace('/[^a-z0-9.]/', '', $base);

    $username = $base;

    $i = 1;

    while (true) {

        $esc = $conn->real_escape_string($username);

        $r = $conn->query("SELECT id FROM users WHERE username = '{$esc}'");

        if (!$r || $r->num_rows === 0) break;

        $username = $base . $i++;

    }

    return $username;

}



/**

 * Import Students — upsert by student_id, then by first_name+last_name+class

 */

function importStudents($file, $source, $conn) {

    $handle  = fopen($file, 'r');

    $headers = fgetcsv($handle);

    $count   = 0;



    while (($row = fgetcsv($handle)) !== false) {

        $raw  = array_combine($headers, $row);



        // Sanitise: N/A → null

        $data = array_map('nullIfNA', $raw);



        // Required fields

        $firstName = formatName((string) ($data['first_name'] ?? ''));

        $lastName  = formatName((string) ($data['last_name']  ?? ''));

        $class     = $data['class'] ?? null;

        if ($firstName === '' || $lastName === '' || $class === null) continue;



        // Format phones

        $fatherPhone  = formatGhanaPhone((string) ($data['father_phone']  ?? ''));

        $motherPhone  = formatGhanaPhone((string) ($data['mother_phone']  ?? ''));

        $phone        = formatGhanaPhone((string) ($data['phone']         ?? ''));

        $emergPhone   = formatGhanaPhone((string) ($data['emergency_contact_phone'] ?? ''));



        // Resolve student ID

        $studentId = $data['student_id'] ?? null;

        if (empty($studentId)) {

            $studentId = generateStudentId($conn);

        }



        // Check existence: by student_id first, then by name+class

        $existingId = null;

        $esc = $conn->real_escape_string($studentId);

        $chk = $conn->query("SELECT id FROM students WHERE student_id = '{$esc}'");

        if ($chk && $chk->num_rows > 0) {

            $existingId = (int) $chk->fetch_assoc()['id'];

        } else {

            $ef = $conn->real_escape_string($firstName);

            $el = $conn->real_escape_string($lastName);

            $ec = $conn->real_escape_string($class);

            $chk2 = $conn->query(

                "SELECT id FROM students WHERE first_name='{$ef}' AND last_name='{$el}' AND class='{$ec}'"

            );

            if ($chk2 && $chk2->num_rows > 0) {

                $existingId = (int) $chk2->fetch_assoc()['id'];

            }

        }



        if ($existingId) {

            // UPDATE existing record

            $stmt = $conn->prepare(

                "UPDATE students SET

                    student_id=?, first_name=?, last_name=?, sex=?, date_of_birth=?, place_of_birth=?,

                    nationality=?, house_no=?, father_name=?, father_occupation=?, father_address=?,

                    father_phone=?, mother_name=?, mother_occupation=?, mother_address=?, mother_phone=?,

                    religion=?, parents_live_together=?, guardian_relationship=?, has_handicap=?,

                    handicap_description=?, languages_spoken=?, last_school=?, last_class=?,

                    emergency_contact_name=?, emergency_contact_address=?, emergency_contact_phone=?,

                    class=?, section=?, parent_name=?, phone=?, email=?, address=?

                WHERE id=?"

            );

            $n = null;

            $stmt->bind_param(

                'ssssssssssssssssssssssssssssssssi',

                $studentId, $firstName, $lastName,

                $data['sex'], $data['date_of_birth'], $data['place_of_birth'],

                $data['nationality'], $data['house_no'],

                $data['father_name'], $data['father_occupation'], $data['father_address'],

                $fatherPhone,

                $data['mother_name'], $data['mother_occupation'], $data['mother_address'],

                $motherPhone,

                $data['religion'], $data['parents_live_together'], $data['guardian_relationship'],

                $data['has_handicap'], $data['handicap_description'], $data['languages_spoken'],

                $data['last_school'], $data['last_class'],

                $data['emergency_contact_name'], $data['emergency_contact_address'], $emergPhone,

                $class, $data['section'], $data['parent_name'],

                $phone, $data['email'], $data['address'],

                $existingId

            );

        } else {

            // INSERT new record

            $stmt = $conn->prepare(

                "INSERT INTO students (

                    student_id, first_name, last_name, sex, date_of_birth, place_of_birth,

                    nationality, house_no, father_name, father_occupation, father_address,

                    father_phone, mother_name, mother_occupation, mother_address, mother_phone,

                    religion, parents_live_together, guardian_relationship, has_handicap,

                    handicap_description, languages_spoken, last_school, last_class,

                    emergency_contact_name, emergency_contact_address, emergency_contact_phone,

                    photo_path, signature_path, class, section, parent_name, phone, email, address, status

                ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'active')"

            );

            $n = null;

            $stmt->bind_param(

                'sssssssssssssssssssssssssssssssssss',

                $studentId, $firstName, $lastName,

                $data['sex'], $data['date_of_birth'], $data['place_of_birth'],

                $data['nationality'], $data['house_no'],

                $data['father_name'], $data['father_occupation'], $data['father_address'],

                $fatherPhone,

                $data['mother_name'], $data['mother_occupation'], $data['mother_address'],

                $motherPhone,

                $data['religion'], $data['parents_live_together'], $data['guardian_relationship'],

                $data['has_handicap'], $data['handicap_description'], $data['languages_spoken'],

                $data['last_school'], $data['last_class'],

                $data['emergency_contact_name'], $data['emergency_contact_address'], $emergPhone,

                $n, $n,   // photo_path, signature_path

                $class, $data['section'], $data['parent_name'],

                $phone, $data['email'], $data['address']

            );

        }



        if ($stmt->execute()) {

            $count++;

        }

        $stmt->close();

    }



    fclose($handle);

    return $count;

}



/**

 * Import Staff — upsert by staff_id/full_name, create login account for new staff

 */

function importStaff($file, $source, $conn) {

    $handle  = fopen($file, 'r');

    $headers = fgetcsv($handle);

    $count   = 0;

    $createdUsers = [];



    while (($row = fgetcsv($handle)) !== false) {

        $raw  = array_combine($headers, $row);

        $data = array_map('nullIfNA', $raw);



        $fullName = formatName((string) ($data['full_name'] ?? ''));

        if ($fullName === '') continue;



        $phone   = formatGhanaPhone((string) ($data['phone'] ?? ''));

        $staffId = $data['staff_id'] ?? null;

        if (empty($staffId)) {

            $staffId = generateStaffId($conn);

        }



        // Check existence: by staff_id, then by full_name

        $existingId = null;

        $esc = $conn->real_escape_string($staffId);

        $chk = $conn->query("SELECT id FROM staff WHERE staff_id = '{$esc}'");

        if ($chk && $chk->num_rows > 0) {

            $existingId = (int) $chk->fetch_assoc()['id'];

        } else {

            $en = $conn->real_escape_string($fullName);

            $chk2 = $conn->query("SELECT id FROM staff WHERE full_name = '{$en}'");

            if ($chk2 && $chk2->num_rows > 0) {

                $existingId = (int) $chk2->fetch_assoc()['id'];

            }

        }



        $n = null; // placeholder for null params



        if ($existingId) {

            $stmt = $conn->prepare(

                "UPDATE staff SET

                    staff_id=?, full_name=?, reg_no=?, academic_qualification=?, professional_qualification=?,

                    class_taught=?, present_rank=?, date_promoted_to_present_rank=?, date_of_first_appointment=?,

                    year_of_certification=?, age=?, sex=?, date_of_birth=?, place_of_birth=?, nationality=?,

                    residential_address=?, institutions_attended=?, teaching_experience=?, previous_schools=?,

                    bank_name=?, bank_account=?, date_posted_to_present_station=?, expected_retirement_date=?,

                    phone=?, tel_no=?, religious_denomination=?, email=?, marital_status=?, languages_spoken=?,

                    next_of_kin=?, next_of_kin_phone=?, digital_address=?, ssnit_number=?, district=?,

                    tin_number=?, region=?, hometown=?, date_of_employment=?, salary=?,

                    class_assigned=?, subject_to_teach=?, approved_by=?

                WHERE id=?"

            );

            $stmt->bind_param(

                'ssssssssssssssssssssssssssssssssssssssssssi',

                $staffId, $fullName,

                $data['reg_no'], $data['academic_qualification'], $data['professional_qualification'],

                $data['class_taught'], $data['present_rank'],

                $data['date_promoted_to_present_rank'], $data['date_of_first_appointment'],

                $data['year_of_certification'], $data['age'], $data['sex'],

                $data['date_of_birth'], $data['place_of_birth'], $data['nationality'],

                $data['residential_address'], $data['institutions_attended'],

                $data['teaching_experience'], $data['previous_schools'],

                $data['bank_name'], $data['bank_account'],

                $data['date_posted_to_present_station'], $data['expected_retirement_date'],

                $phone, $data['tel_no'], $data['religious_denomination'],

                $data['email'], $data['marital_status'], $data['languages_spoken'],

                $data['next_of_kin'], $data['next_of_kin_phone'], $data['digital_address'],

                $data['ssnit_number'], $data['district'], $data['tin_number'],

                $data['region'], $data['hometown'],

                $data['date_of_employment'], $data['salary'],

                $data['class_assigned'], $data['subject_to_teach'], $data['approved_by'],

                $existingId

            );

        } else {

            $stmt = $conn->prepare(

                "INSERT INTO staff (

                    staff_id, full_name, reg_no, academic_qualification, professional_qualification,

                    class_taught, present_rank, date_promoted_to_present_rank, date_of_first_appointment,

                    year_of_certification, age, sex, date_of_birth, place_of_birth, nationality,

                    residential_address, institutions_attended, teaching_experience, previous_schools,

                    bank_name, bank_account, date_posted_to_present_station, expected_retirement_date,

                    phone, tel_no, religious_denomination, email, marital_status, languages_spoken,

                    next_of_kin, next_of_kin_phone, digital_address, ssnit_number, district,

                    tin_number, region, hometown, photo_path, date_of_employment, salary,

                    class_assigned, subject_to_teach, approved_by, status

                ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'active')"

            );

            $stmt->bind_param(

                'sssssssssssssssssssssssssssssssssssssssssss',

                $staffId, $fullName,

                $data['reg_no'], $data['academic_qualification'], $data['professional_qualification'],

                $data['class_taught'], $data['present_rank'],

                $data['date_promoted_to_present_rank'], $data['date_of_first_appointment'],

                $data['year_of_certification'], $data['age'], $data['sex'],

                $data['date_of_birth'], $data['place_of_birth'], $data['nationality'],

                $data['residential_address'], $data['institutions_attended'],

                $data['teaching_experience'], $data['previous_schools'],

                $data['bank_name'], $data['bank_account'],

                $data['date_posted_to_present_station'], $data['expected_retirement_date'],

                $phone, $data['tel_no'], $data['religious_denomination'],

                $data['email'], $data['marital_status'], $data['languages_spoken'],

                $data['next_of_kin'], $data['next_of_kin_phone'], $data['digital_address'],

                $data['ssnit_number'], $data['district'], $data['tin_number'],

                $data['region'], $data['hometown'],

                $n,   // photo_path

                $data['date_of_employment'], $data['salary'],

                $data['class_assigned'], $data['subject_to_teach'], $data['approved_by']

            );

        }



        if (!$stmt->execute()) {

            $stmt->close();

            continue;

        }

        $stmt->close();

        $count++;



        // Create login account only for new staff (not existing)

        if (!$existingId) {

            $username = generateUsername($fullName, $conn);

            // Password pattern: Staff@ + numeric part of staff_id (e.g. Staff@001)

            $numericPart = preg_replace('/[^0-9]/', '', $staffId) ?: rand(1000, 9999);

            $tempPassword = 'Staff@' . $numericPart;

            $hashedPassword = password_hash($tempPassword, PASSWORD_DEFAULT);

            $email = $data['email'];

            $role  = 'tutor';



            $uStmt = $conn->prepare(

                "INSERT IGNORE INTO users (username, password, full_name, email, phone, role, is_active, created_at)

                 VALUES (?, ?, ?, ?, ?, 'tutor', 1, NOW())"

            );

            $uStmt->bind_param('sssss', $username, $hashedPassword, $fullName, $email, $phone);

            $uStmt->execute();

            $uStmt->close();



            $createdUsers[] = "{$fullName} → login: {$username} / pass: {$tempPassword}";

        }

    }



    fclose($handle);



    // Return count plus generated credentials so they appear in the import summary

    return ['count' => $count, 'credentials' => $createdUsers];

}



/**

 * Import Fees - NEW FUNCTION

 */

function importFees($file, $source, $conn) {

    $handle = fopen($file, 'r');

    $headers = fgetcsv($handle);

    $count = 0;

    

    while (($row = fgetcsv($handle)) !== FALSE) {

        $data = array_combine($headers, $row);

        

        // Check if fee structure already exists

        $check = $conn->query("SELECT id FROM fee_structures 

                               WHERE class_name = '{$data['class_name']}' 

                               AND fee_type = '{$data['fee_type']}'

                               AND academic_year = '{$data['academic_year']}'");

        if ($check->num_rows > 0) {

            continue; // Skip existing

        }

        

        // Format amount

        $amount = floatval($data['amount']);

        

        $query = "INSERT INTO fee_structures (class_name, fee_type, amount, due_date, academic_year) 

                  VALUES (?, ?, ?, ?, ?)";

        

        $stmt = $conn->prepare($query);

        $stmt->bind_param(

            "ssdss",

            $data['class_name'],

            $data['fee_type'],

            $amount,

            $data['due_date'],

            $data['academic_year']

        );

        

        if ($stmt->execute()) {

            $count++;

        }

        $stmt->close();

    }

    

    fclose($handle);

    return $count;

}



/**

 * Import Payments - NEW FUNCTION

 */

function importPayments($file, $source, $conn) {

    $handle = fopen($file, 'r');

    $headers = fgetcsv($handle);

    $count = 0;

    

    while (($row = fgetcsv($handle)) !== FALSE) {

        $data = array_combine($headers, $row);

        

        // Find student ID

        $studentQuery = "SELECT id FROM students WHERE student_id = ?";

        $stmt = $conn->prepare($studentQuery);

        $stmt->bind_param("s", $data['student_id']);

        $stmt->execute();

        $studentResult = $stmt->get_result();

        $student = $studentResult->fetch_assoc();

        

        if (!$student) {

            continue; // Skip if student not found

        }

        

        // Check if payment already exists (prevent duplicates)

        $checkQuery = "SELECT id FROM payments 

                       WHERE student_id = ? 

                       AND fee_structure_id = ? 

                       AND payment_date = ?";

        $stmt = $conn->prepare($checkQuery);

        $stmt->bind_param("iis", $student['id'], $data['fee_structure_id'], $data['payment_date']);

        $stmt->execute();

        if ($stmt->get_result()->num_rows > 0) {

            continue; // Skip duplicate

        }

        

        // Format amount

        $amount = floatval($data['amount_paid']);

        

        $query = "INSERT INTO payments (

            student_id, fee_structure_id, amount_paid, payment_date, 

            payment_method, transaction_id, notes

        ) VALUES (?, ?, ?, ?, ?, ?, ?)";

        

        $stmt = $conn->prepare($query);

        $stmt->bind_param(

            "iidssss",

            $student['id'],

            $data['fee_structure_id'],

            $amount,

            $data['payment_date'],

            $data['payment_method'] ?? 'cash',

            $data['transaction_id'] ?? '',

            $data['notes'] ?? ''

        );

        

        if ($stmt->execute()) {

            $count++;

            

            // If this is a feeding fee, also add to weekly_feeding_payments

            $feeQuery = "SELECT fee_type FROM fee_structures WHERE id = ?";

            $stmt2 = $conn->prepare($feeQuery);

            $stmt2->bind_param("i", $data['fee_structure_id']);

            $stmt2->execute();

            $feeResult = $stmt2->get_result();

            $fee = $feeResult->fetch_assoc();

            

            if ($fee && strtolower($fee['fee_type']) == 'feeding fee') {

                // Calculate week dates

                $paymentDate = new DateTime($data['payment_date']);

                $weekStart = clone $paymentDate;

                $weekStart->modify('monday this week');

                $weekEnd = clone $weekStart;

                $weekEnd->modify('friday this week');

                

                $weekQuery = "INSERT INTO weekly_feeding_payments (

                    student_id, payment_id, week_start_date, week_end_date, amount_paid, sms_sent

                ) VALUES (?, ?, ?, ?, ?, 0)";

                

                $stmt3 = $conn->prepare($weekQuery);

                $paymentId = $stmt->insert_id;

                $stmt3->bind_param(

                    "iissd",

                    $student['id'],

                    $paymentId,

                    $weekStart->format('Y-m-d'),

                    $weekEnd->format('Y-m-d'),

                    $amount

                );

                $stmt3->execute();

                $stmt3->close();

            }

            $stmt2->close();

        }

        $stmt->close();

    }

    

    fclose($handle);

    return $count;

}



/**

 * Import Subjects

 */

function importSubjects($file, $source, $conn) {

    $handle = fopen($file, 'r');

    $headers = fgetcsv($handle);

    $count = 0;

    

    while (($row = fgetcsv($handle)) !== FALSE) {

        $data = array_combine($headers, $row);

        

        // Check if subject already exists

        $check = $conn->query("SELECT id FROM subjects WHERE subject_code = '{$data['subject_code']}'");

        

        if ($check->num_rows > 0) {

            $subjectRow = $check->fetch_assoc();

            $subjectId = $subjectRow['id'];

        } else {

            // Insert new subject

            $subjectQuery = "INSERT INTO subjects (subject_name, subject_code, active) 

                             VALUES (?, ?, 'yes')";

            $stmt = $conn->prepare($subjectQuery);

            $stmt->bind_param("ss", $data['subject_name'], $data['subject_code']);

            $stmt->execute();

            $subjectId = $stmt->insert_id;

            $stmt->close();

        }

        

        // Insert into class_subjects if class_name is provided

        if (!empty($data['class_name']) && $subjectId) {

            $classCheck = $conn->query("SELECT id FROM class_subjects 

                                        WHERE class_name = '{$data['class_name']}' 

                                        AND subject_id = $subjectId");

            

            if ($classCheck->num_rows == 0) {

                $classQuery = "INSERT INTO class_subjects (

                    class_name, subject_id, class_score_percent, exam_score_percent, is_compulsory

                ) VALUES (?, ?, ?, ?, 'yes')";

                

                $classStmt = $conn->prepare($classQuery);

                $classScorePercent = $data['class_score_percent'] ?? 50;

                $examScorePercent = $data['exam_score_percent'] ?? 50;

                

                $classStmt->bind_param(

                    "siii", 

                    $data['class_name'], 

                    $subjectId, 

                    $classScorePercent, 

                    $examScorePercent

                );

                $classStmt->execute();

                $classStmt->close();

            }

        }

        

        $count++;

    }

    

    fclose($handle);

    return $count;

}



/**

 * Generate Student ID

 */

function generateStudentId($conn) {

    $result = $conn->query("SELECT MAX(CAST(SUBSTRING(student_id, 4) AS UNSIGNED)) as max FROM students WHERE student_id LIKE 'STU%'");

    $row = $result->fetch_assoc();

    $next = ($row['max'] ?? 0) + 1;

    return 'STU' . str_pad($next, 3, '0', STR_PAD_LEFT);

}



/**

 * Generate Staff ID

 */

function generateStaffId($conn) {

    $result = $conn->query("SELECT MAX(CAST(SUBSTRING(staff_id, 4) AS UNSIGNED)) as max FROM staff WHERE staff_id LIKE 'STF%'");

    $row = $result->fetch_assoc();

    $next = ($row['max'] ?? 0) + 1;

    return 'STF' . str_pad($next, 3, '0', STR_PAD_LEFT);

}



// logMigration() is defined in migration/config.php

?>