<?php
/**
 * functions.php - Core functions for the school management system
 * Contains all database interaction functions
 */

/**
 * Get all governments
 * @param mysqli $conn Database connection
 * @return array Array of governments
 */
function getGovernments($conn) {
    $query = "SELECT * FROM government ORDER BY GovName";
    $result = mysqli_query($conn, $query);
    $governments = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $governments[] = $row;
    }
    return $governments;
}

/**
 * Get educational directorates by government ID
 * @param mysqli $conn Database connection
 * @param int $govID Government ID
 * @return array Array of directorates
 */
function getEdaras($conn, $govID) {
    $query = "SELECT * FROM edara WHERE GovID = " . intval($govID) . " ORDER BY EdaraName";
    $result = mysqli_query($conn, $query);
    $edaras = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $edaras[] = $row;
    }
    return $edaras;
}

/**
 * Get schools by educational directorate ID
 * @param mysqli $conn Database connection
 * @param int $edaraID Educational directorate ID
 * @return array Array of schools
 */
function getSchools($conn, $edaraID) {
    $query = "SELECT * FROM school WHERE EdaraID = " . intval($edaraID) . " ORDER BY SchoolName";
    $result = mysqli_query($conn, $query);
    $schools = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $schools[] = $row;
    }
    return $schools;
}

/**
 * Get school data including description
 * @param mysqli $conn Database connection
 * @param int $schoolID School ID
 * @return array|null School data or null if not found
 */
function getSchoolData($conn, $schoolID) {
    $query = "SELECT * FROM school_basic_data WHERE SchoolID = " . intval($schoolID);
    $result = mysqli_query($conn, $query);
    if (mysqli_num_rows($result) > 0) {
        return mysqli_fetch_assoc($result);
    }
    return null;
}

/**
 * Get school name by ID
 * @param mysqli $conn Database connection
 * @param int $schoolID School ID
 * @return string School name or empty string
 */
function getSchoolName($conn, $schoolID) {
    $query = "SELECT SchoolName FROM school WHERE SchoolID = " . intval($schoolID);
    $result = mysqli_query($conn, $query);
    if (mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        return $row['SchoolName'];
    }
    return '';
}

/**
 * Count schools without description in a directorate
 * @param mysqli $conn Database connection
 * @param int $edaraID Educational directorate ID
 * @return int Number of schools without description
 */
function getSchoolsWithoutDescCount($conn, $edaraID) {
    $query = "SELECT COUNT(s.SchoolID) as count 
              FROM school s 
              LEFT JOIN school_basic_data sd ON s.SchoolID = sd.SchoolID 
              WHERE s.EdaraID = " . intval($edaraID) . " AND sd.SchoolID IS NULL";
    $result = mysqli_query($conn, $query);
    if (mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        return intval($row['count']);
    }
    return 0;
}

/**
 * Get schools without description in a directorate
 * @param mysqli $conn Database connection
 * @param int $edaraID Educational directorate ID
 * @return array Array of schools without description
 */
function getSchoolsWithoutDesc($conn, $edaraID) {
    $query = "SELECT s.SchoolID, s.SchoolName 
              FROM school s 
              LEFT JOIN school_basic_data sd ON s.SchoolID = sd.SchoolID 
              WHERE s.EdaraID = " . intval($edaraID) . " AND sd.SchoolID IS NULL 
              ORDER BY s.SchoolName";
    $result = mysqli_query($conn, $query);
    $schools = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $schools[] = $row;
    }
    return $schools;
}

/**
 * Save or update school description data
 * @param mysqli $conn Database connection
 * @param array $data School data array
 * @return bool True on success, false on failure
 */
function saveSchoolData($conn, $data) {
    $schoolId = intval($data['SchoolID']);
    
    // Check if data already exists for this school
    $checkQuery = "SELECT id FROM school_basic_data WHERE SchoolID = $schoolId";
    $checkResult = mysqli_query($conn, $checkQuery);
    
    $success = false;
    
    if (mysqli_num_rows($checkResult) > 0) {
        // Update existing record
        $query = "UPDATE school_basic_data SET 
            school_type = '" . mysqli_real_escape_string($conn, $data['school_type']) . "',
            study_period = '" . mysqli_real_escape_string($conn, $data['study_period']) . "',
            environment = '" . mysqli_real_escape_string($conn, $data['environment']) . "',
            has_electricity = " . intval($data['has_electricity']) . ",
            has_internet = " . intval($data['has_internet']) . ",
            toilet_count = " . intval($data['toilet_count']) . ",
            female_teachers = " . intval($data['female_teachers']) . ",
            male_teachers = " . intval($data['male_teachers']) . ",
            total_teachers = " . intval($data['total_teachers']) . ",
            university_qualified = " . intval($data['university_qualified']) . ",
            below_university_qualified = " . intval($data['below_university_qualified']) . ",
            experience_10_plus = " . intval($data['experience_10_plus']) . ",
            experience_less_10 = " . intval($data['experience_less_10']) . ",
            total_students = " . intval($data['total_students']) . ",
            total_male_students = " . intval($data['total_male_students']) . ",
            total_female_students = " . intval($data['total_female_students']) . ",
            grade_1_students = " . intval($data['grade_1_students']) . ",
            grade_2_students = " . intval($data['grade_2_students']) . ",
            grade_3_students = " . intval($data['grade_3_students']) . ",
            grade_4_students = " . intval($data['grade_4_students']) . ",
            grade_5_students = " . intval($data['grade_5_students']) . ",
            grade_6_students = " . intval($data['grade_6_students']) . ",
            egyptian_students = " . intval($data['egyptian_students']) . ",
            non_egyptian_students = " . intval($data['non_egyptian_students']) . "
            WHERE SchoolID = $schoolId";
        
        $success = mysqli_query($conn, $query);
    } else {
        // Insert new record
        $query = "INSERT INTO school_basic_data SET 
            SchoolID = $schoolId,
            school_type = '" . mysqli_real_escape_string($conn, $data['school_type']) . "',
            study_period = '" . mysqli_real_escape_string($conn, $data['study_period']) . "',
            environment = '" . mysqli_real_escape_string($conn, $data['environment']) . "',
            has_electricity = " . intval($data['has_electricity']) . ",
            has_internet = " . intval($data['has_internet']) . ",
            toilet_count = " . intval($data['toilet_count']) . ",
            female_teachers = " . intval($data['female_teachers']) . ",
            male_teachers = " . intval($data['male_teachers']) . ",
            total_teachers = " . intval($data['total_teachers']) . ",
            university_qualified = " . intval($data['university_qualified']) . ",
            below_university_qualified = " . intval($data['below_university_qualified']) . ",
            experience_10_plus = " . intval($data['experience_10_plus']) . ",
            experience_less_10 = " . intval($data['experience_less_10']) . ",
            total_students = " . intval($data['total_students']) . ",
            total_male_students = " . intval($data['total_male_students']) . ",
            total_female_students = " . intval($data['total_female_students']) . ",
            grade_1_students = " . intval($data['grade_1_students']) . ",
            grade_2_students = " . intval($data['grade_2_students']) . ",
            grade_3_students = " . intval($data['grade_3_students']) . ",
            grade_4_students = " . intval($data['grade_4_students']) . ",
            grade_5_students = " . intval($data['grade_5_students']) . ",
            grade_6_students = " . intval($data['grade_6_students']) . ",
            egyptian_students = " . intval($data['egyptian_students']) . ",
            non_egyptian_students = " . intval($data['non_egyptian_students']) . "";
        
        $success = mysqli_query($conn, $query);
    }
    
    // If data was saved successfully, update the data_entered field in school table
    if ($success) {
        $updateSchoolQuery = "UPDATE school SET data_entered = 1 WHERE SchoolID = $schoolId";
        $updateResult = mysqli_query($conn, $updateSchoolQuery);
        
        if (!$updateResult) {
            error_log("Failed to update data_entered for SchoolID: $schoolId - " . mysqli_error($conn));
        }
    }
    
    return $success;
}

/**
 * Get all grades
 * @param mysqli $conn Database connection
 * @return array Array of grades
 */
function getGrades($conn) {
    $query = "SELECT * FROM grade ORDER BY GradeID";
    $result = mysqli_query($conn, $query);
    $grades = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $grades[] = $row;
    }
    return $grades;
}

/**
 * Get government name by ID
 * @param mysqli $conn Database connection
 * @param int $govID Government ID
 * @return string Government name or empty string
 */
function getGovernmentName($conn, $govID) {
    $query = "SELECT GovName FROM government WHERE GovID = " . intval($govID);
    $result = mysqli_query($conn, $query);
    if (mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        return $row['GovName'];
    }
    return '';
}

/**
 * Get educational directorate name by ID
 * @param mysqli $conn Database connection
 * @param int $edaraID Educational directorate ID
 * @return string Directorate name or empty string
 */
function getEdaraName($conn, $edaraID) {
    $query = "SELECT EdaraName FROM edara WHERE EdaraID = " . intval($edaraID);
    $result = mysqli_query($conn, $query);
    if (mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        return $row['EdaraName'];
    }
    return '';
}

/**
 * Check if school has description data
 * @param mysqli $conn Database connection
 * @param int $schoolID School ID
 * @return bool True if has description, false otherwise
 */
function hasSchoolDescription($conn, $schoolID) {
    $query = "SELECT SchoolID FROM school_basic_data WHERE SchoolID = " . intval($schoolID);
    $result = mysqli_query($conn, $query);
    return (mysqli_num_rows($result) > 0);
}

/**
 * Get pending schools count by directorate
 * @param mysqli $conn Database connection
 * @param int $edaraID Educational directorate ID
 * @return array Array with pending and total counts
 */
function getPendingSchoolsCount($conn, $edaraID) {
    $query = "SELECT 
                COUNT(s.SchoolID) as total_schools,
                SUM(CASE WHEN sd.SchoolID IS NULL THEN 1 ELSE 0 END) as pending_schools
              FROM school s
              LEFT JOIN school_basic_data sd ON s.SchoolID = sd.SchoolID
              WHERE s.EdaraID = " . intval($edaraID);
    
    $result = mysqli_query($conn, $query);
    $data = ['total_schools' => 0, 'pending_schools' => 0];
    if (mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        $data['total_schools'] = intval($row['total_schools']);
        $data['pending_schools'] = intval($row['pending_schools']);
    }
    return $data;
}

/**
 * Validate school data before saving
 * @param array $data School data
 * @return array Array of validation errors
 */
function validateSchoolData($data) {
    $errors = [];
    
    $required = ['school_type', 'study_period', 'environment'];
    foreach ($required as $field) {
        if (empty($data[$field])) {
            $errors[] = "حقل " . str_replace('_', ' ', $field) . " مطلوب";
        }
    }
    
    // Validate numeric fields are non-negative
    $numericFields = [
        'toilet_count', 'female_teachers', 'male_teachers', 'total_teachers',
        'university_qualified', 'below_university_qualified',
        'experience_10_plus', 'experience_less_10',
        'total_students', 'total_male_students', 'total_female_students',
        'grade_1_students', 'grade_2_students', 'grade_3_students',
        'grade_4_students', 'grade_5_students', 'grade_6_students',
        'egyptian_students', 'non_egyptian_students'
    ];
    
    foreach ($numericFields as $field) {
        if (isset($data[$field]) && intval($data[$field]) < 0) {
            $errors[] = "حقل " . str_replace('_', ' ', $field) . " يجب أن يكون قيمة موجبة";
        }
    }
    
    return $errors;
}

/**
 * Check if a school has data entered
 * @param mysqli $conn Database connection
 * @param int $schoolID School ID
 * @return bool True if data is entered, false otherwise
 */
function isSchoolDataEntered($conn, $schoolID) {
    $query = "SELECT data_entered FROM school WHERE SchoolID = " . intval($schoolID);
    $result = mysqli_query($conn, $query);
    if (mysqli_num_rows($result) > 0) {
        $row = mysqli_fetch_assoc($result);
        return ($row['data_entered'] == 1);
    }
    return false;
}

/**
 * Get all schools with their data entered status
 * @param mysqli $conn Database connection
 * @param int $edaraID Educational directorate ID
 * @return array Array of schools with status
 */
function getSchoolsWithStatus($conn, $edaraID) {
    $query = "SELECT s.SchoolID, s.SchoolName, s.data_entered,
              CASE WHEN sd.SchoolID IS NOT NULL THEN 1 ELSE 0 END as has_data
              FROM school s
              LEFT JOIN school_basic_data sd ON s.SchoolID = sd.SchoolID
              WHERE s.EdaraID = " . intval($edaraID) . "
              ORDER BY s.SchoolName";
    
    $result = mysqli_query($conn, $query);
    $schools = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $schools[] = $row;
    }
    return $schools;
}
?>