<?php

/**

 * Save Manual Data Entries

 */



define('MIGRATION_ACCESS', true);

session_start();



// Fix the path to config.php

require_once '../../config.php';

require_once __DIR__ . '/../../lib/academic_period.php';

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

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

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



// Check authentication

if (!isset($_SESSION['user_id']) || $_SESSION['role'] !== 'admin') {

    header('Location: ../../index.php');

    exit;

}



$conn = getDBConnection();

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

$data = $_POST[$type] ?? [];



$results = [

    'success' => 0,

    'failed' => 0,

    'errors' => []

];



$conn->begin_transaction();



try {

    foreach ($data as $index => $record) {

        $success = false;

        

        switch ($type) {

            case 'students':

                $success = saveStudent($conn, $record);

                break;

            case 'staff':

                $success = saveStaff($conn, $record);

                break;

            case 'fees':

                $success = saveFee($conn, $record);

                break;

            case 'payments':

                $success = savePayment($conn, $record);

                break;

            case 'subjects':

                $success = saveSubject($conn, $record);

                break;

            case 'scores':

                $success = saveScore($conn, $record);

                break;

        }

        

        if ($success) {

            $results['success']++;

        } else {

            $results['failed']++;

            $results['errors'][] = "Record " . ($index + 1) . " failed to save";

        }

    }

    

    $conn->commit();

    

} catch (Exception $e) {

    $conn->rollback();

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

}



// Redirect back with results

session_start();

$_SESSION['migration_results'] = $results;

header("Location: manual_entry.php?type=$type&saved=1");

exit;



function saveStudent($conn, $data) {

    // Format data

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

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

    $data['father_name'] = formatName($data['father_name'] ?? '');

    $data['mother_name'] = formatName($data['mother_name'] ?? '');

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

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

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

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

    

    // Generate student ID if not provided

    if (empty($data['student_id'])) {

        $data['student_id'] = generateStudentId($conn);

    }

    

    $query = "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')";

    

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

    if (!$stmt) {

        error_log("Prepare failed: " . $conn->error);

        return false;

    }

    

    $stmt->bind_param(

        "ssssssssssssssssssssssssssssssssss",

        $data['student_id'],

        $data['first_name'],

        $data['last_name'],

        $data['sex'] ?? null,

        $data['date_of_birth'] ?? null,

        $data['place_of_birth'] ?? null,

        $data['nationality'] ?? null,

        $data['house_no'] ?? null,

        $data['father_name'] ?? null,

        $data['father_occupation'] ?? null,

        $data['father_address'] ?? null,

        $data['father_phone'] ?? null,

        $data['mother_name'] ?? null,

        $data['mother_occupation'] ?? null,

        $data['mother_address'] ?? null,

        $data['mother_phone'] ?? null,

        $data['religion'] ?? null,

        $data['parents_live_together'] ?? null,

        $data['guardian_relationship'] ?? null,

        $data['has_handicap'] ?? null,

        $data['handicap_description'] ?? null,

        $data['languages_spoken'] ?? null,

        $data['last_school'] ?? null,

        $data['last_class'] ?? null,

        $data['emergency_contact_name'] ?? null,

        $data['emergency_contact_address'] ?? null,

        $data['emergency_contact_phone'] ?? null,

        null, // photo_path

        null, // signature_path

        $data['class'],

        $data['section'] ?? null,

        $data['parent_name'] ?? null,

        $data['phone'] ?? null,

        $data['email'] ?? null,

        $data['address'] ?? null

    );

    

    $result = $stmt->execute();

    $stmt->close();

    return $result;

}



function saveStaff($conn, $data) {

    // Generate staff ID if not provided

    if (empty($data['staff_id'])) {

        $data['staff_id'] = generateStaffId($conn);

    }

    

    $query = "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 = $conn->prepare($query);

    if (!$stmt) {

        error_log("Prepare failed: " . $conn->error);

        return false;

    }

    

    // Bind parameters (44 parameters)

    $stmt->bind_param(

        "ssssssssssssssssssssssssssssssssssssssssss",

        $data['staff_id'],

        $data['full_name'],

        $data['reg_no'] ?? null,

        $data['academic_qualification'] ?? null,

        $data['professional_qualification'] ?? null,

        $data['class_taught'] ?? null,

        $data['present_rank'] ?? null,

        $data['date_promoted_to_present_rank'] ?? null,

        $data['date_of_first_appointment'] ?? null,

        $data['year_of_certification'] ?? null,

        $data['age'] ?? null,

        $data['sex'] ?? null,

        $data['date_of_birth'] ?? null,

        $data['place_of_birth'] ?? null,

        $data['nationality'] ?? null,

        $data['residential_address'] ?? null,

        $data['institutions_attended'] ?? null,

        $data['teaching_experience'] ?? null,

        $data['previous_schools'] ?? null,

        $data['bank_name'] ?? null,

        $data['bank_account'] ?? null,

        $data['date_posted_to_present_station'] ?? null,

        $data['expected_retirement_date'] ?? null,

        $data['phone'] ?? null,

        $data['tel_no'] ?? null,

        $data['religious_denomination'] ?? null,

        $data['email'] ?? null,

        $data['marital_status'] ?? null,

        $data['languages_spoken'] ?? null,

        $data['next_of_kin'] ?? null,

        $data['next_of_kin_phone'] ?? null,

        $data['digital_address'] ?? null,

        $data['ssnit_number'] ?? null,

        $data['district'] ?? null,

        $data['tin_number'] ?? null,

        $data['region'] ?? null,

        $data['hometown'] ?? null,

        null, // photo_path

        $data['date_of_employment'] ?? null,

        $data['salary'] ?? null,

        $data['class_assigned'] ?? null,

        $data['subject_to_teach'] ?? null,

        $data['approved_by'] ?? null

    );

    

    $result = $stmt->execute();

    $stmt->close();

    return $result;

}



function saveFee($conn, $data) {

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

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

    

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

    if (!$stmt) return false;

    

    $stmt->bind_param(

        "ssdss",

        $data['class_name'],

        $data['fee_type'],

        $data['amount'],

        $data['due_date'],

        $data['academic_year'] ?? date('Y') . '-' . (date('Y')+1)

    );

    

    $result = $stmt->execute();

    $stmt->close();

    return $result;

}



function savePayment($conn, $data) {

    // Find student internal ID

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

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

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

    $stmt->execute();

    $studentResult = $stmt->get_result();

    $student = $studentResult->fetch_assoc();

    $stmt->close();

    

    if (!$student) return false;

    

    $query = "INSERT INTO payments (student_id, fee_structure_id, amount_paid, payment_date, payment_method, transaction_id, notes) 

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

    

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

    if (!$stmt) return false;

    

    $stmt->bind_param(

        "iidssss",

        $student['id'],

        $data['fee_structure_id'],

        $data['amount_paid'],

        $data['payment_date'],

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

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

        $data['notes'] ?? ''

    );

    

    $result = $stmt->execute();

    $paymentId = $stmt->insert_id;

    $stmt->close();

    

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

    if ($result && !empty($data['is_feeding_fee'])) {

        $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)";

        

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

        $weekStmt->bind_param(

            "iissd",

            $student['id'],

            $paymentId,

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

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

            $data['amount_paid']

        );

        $weekStmt->execute();

        $weekStmt->close();

    }

    

    return $result;

}



function saveSubject($conn, $data) {

    // First insert into subjects

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

                     VALUES (?, ?, ?)";

    

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

    if (!$stmt) return false;

    

    $active = $data['active'] ?? 'yes';

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

    

    $result = $stmt->execute();

    $subjectId = $stmt->insert_id;

    $stmt->close();

    

    // Then insert into class_subjects if class_name provided

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

        $classQuery = "INSERT INTO class_subjects (

            class_name, subject_id, class_score_percent, exam_score_percent, is_compulsory

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

        

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

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

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

        $isCompulsory = $data['is_compulsory'] ?? 'yes';

        

        $classStmt->bind_param(

            "siiis",

            $data['class_name'],

            $subjectId,

            $classScorePercent,

            $examScorePercent,

            $isCompulsory

        );

        $classStmt->execute();

        $classStmt->close();

    }

    

    return $result;

}



function saveScore($conn, $data) {

    // ON DUPLICATE KEY UPDATE, because `scores` now carries a unique key on

    // (student_id, subject_id, term, academic_year) — re-running an import would

    // otherwise abort on the first row it had already loaded.

    $query = "INSERT INTO scores (

        student_id, subject_id, class_name, class_score, exam_score, raw_class_score, raw_exam_score,

        weighted_class_score, weighted_exam_score, total_score, grade, remark, term, academic_year

    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

    ON DUPLICATE KEY UPDATE

        class_name           = VALUES(class_name),

        class_score          = VALUES(class_score),

        exam_score           = VALUES(exam_score),

        raw_class_score      = VALUES(raw_class_score),

        raw_exam_score       = VALUES(raw_exam_score),

        weighted_class_score = VALUES(weighted_class_score),

        weighted_exam_score  = VALUES(weighted_exam_score),

        total_score          = VALUES(total_score),

        grade                = VALUES(grade),

        remark               = VALUES(remark)";

    

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

    if (!$stmt) return false;

    

    $totalScore = ($data['class_score'] ?? 0) + ($data['exam_score'] ?? 0);

    

    // Simple grade calculation

    $grade = 'F';

    $remark = 'Fail';

    if ($totalScore >= 80) { $grade = 'A'; $remark = 'Excellent'; }

    elseif ($totalScore >= 70) { $grade = 'B'; $remark = 'Very Good'; }

    elseif ($totalScore >= 60) { $grade = 'C'; $remark = 'Good'; }

    elseif ($totalScore >= 50) { $grade = 'D'; $remark = 'Pass'; }

    elseif ($totalScore >= 40) { $grade = 'E'; $remark = 'Poor'; }

    

    // bind_param takes its arguments by reference, so each one has to be a plain

    // variable — the inline `?? 0` expressions used here were fatal on PHP 8.

    $studentId     = (int) $data['student_id'];

    $subjectId     = (int) $data['subject_id'];

    $className     = $data['class_name'] ?? academicStudentClass($conn, $studentId);

    $classScore    = $data['class_score'] ?? 0;

    $examScore     = $data['exam_score'] ?? 0;

    $rawClassScore = $data['raw_class_score'] ?? 0;

    $rawExamScore  = $data['raw_exam_score'] ?? 0;

    $wClassScore   = $data['weighted_class_score'] ?? 0;

    $wExamScore    = $data['weighted_exam_score'] ?? 0;

    $finalGrade    = $data['grade'] ?? $grade;

    $finalRemark   = $data['remark'] ?? $remark;

    $termValue     = $data['term'];

    $yearValue     = $data['academic_year'];



    $stmt->bind_param(

        "iisddddddssss",

        $studentId,

        $subjectId,

        $className,

        $classScore,

        $examScore,

        $rawClassScore,

        $rawExamScore,

        $wClassScore,

        $wExamScore,

        $totalScore,

        $finalGrade,

        $finalRemark,

        $termValue,

        $yearValue

    );

    

    $result = $stmt->execute();

    $stmt->close();

    return $result;

}



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);

}



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);

}

?>