<?php
// Include session configuration
require_once 'session_config_emp.php';

// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Check if session is valid
if (!validateUserSession()) {
    // Redirect to login
    header('Location: login_emp.php');
    exit();
}

// Check if form data exists
if (!isset($_SESSION['form_data'])) {
    header("Location: login_emp.php");
    exit();
}

// Verify CSRF token - check both POST and session
$csrf_valid = false;

if (isset($_POST['csrf_token']) && validateCsrfToken($_POST['csrf_token'])) {
    $csrf_valid = true;
} elseif (isset($_SESSION['final_csrf_token']) && validateCsrfToken($_SESSION['final_csrf_token'])) {
    $csrf_valid = true;
    unset($_SESSION['final_csrf_token']); // Clear after use
}

if (!$csrf_valid) {
    die("CSRF token validation failed");
}

// Database connection
require_once 'connect.php';

// Function to sanitize input data
function sanitizeInput($data) {
    global $conn;
    return $conn->real_escape_string(trim($data));
}

// Function to convert date from DD-MM-YYYY to YYYY-MM-DD
function convertToMySQLDate($date) {
    if (empty($date) || trim($date) === '' || $date === 'dd-mm-yyyy' || $date === '0000-00-00') {
        return null; // ✅ Return NULL for empty dates
    }
    
    $date = trim($date);
    
    // If already in YYYY-MM-DD format, return as-is
    if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
        return $date;
    }
    
    // Handle DD-MM-YYYY format (from your form)
    if (preg_match('/^(\d{2})-(\d{2})-(\d{4})$/', $date, $matches)) {
        $day = $matches[1];
        $month = $matches[2];
        $year = $matches[3];
        
        // Validate date - checkdate expects (month, day, year)
        if (checkdate((int)$month, (int)$day, (int)$year)) {
            return $year . '-' . $month . '-' . $day;
        } else {
            error_log("Invalid date in convertToMySQLDate: $date (checkdate failed)");
            return null;
        }
    }
    
    // Handle YYYY-MM format (for training years from emp4.php)
    if (preg_match('/^(\d{4})-(\d{2})$/', $date, $matches)) {
        $year = $matches[1];
        $month = $matches[2];
        // Add day as 01 for month-only dates
        return $year . '-' . $month . '-01';
    }
    
    error_log("Date format not recognized in convertToMySQLDate: $date");
    return null; // ✅ Return NULL for unrecognized formats
}

// Function to convert month-year to date
function convertMonthYearToDate($monthYear) {
    if (empty($monthYear) || trim($monthYear) === '') return null;
    
    $monthYear = trim($monthYear);
    
    // Handle YYYY-MM format
    if (preg_match('/^(\d{4})-(\d{2})$/', $monthYear, $matches)) {
        $year = $matches[1];
        $month = $matches[2];
        
        if ((int)$month >= 1 && (int)$month <= 12) {
            return $year . '-' . $month . '-01';
        }
    }
    
    return null;
}

try {
    $formData = $_SESSION['form_data'];
    
    // Debug: Log ALL date fields from emp2, emp3, emp4
    error_log("=== DATE FIELDS DEBUG ===");
    
    // Academic dates from emp2.php
    $academicDates = ['tesscpass', 'tehscpass', 'tebspass', 'temapass', 'temphillpass', 'tephdpass', 'teotherpass'];
    foreach ($academicDates as $field) {
        if (isset($formData[$field])) {
            $value = $formData[$field];
            error_log("$field: '$value' => MySQL: '" . convertToMySQLDate($value) . "'");
        }
    }
    
    // Professional dates from emp3.php
    $professionalDates = ['teptpass', 'tebedpass', 'temedpass', 'teo1pass', 'teo2pass', 'teo3pass'];
    foreach ($professionalDates as $field) {
        if (isset($formData[$field])) {
            $value = $formData[$field];
            error_log("$field: '$value' => MySQL: '" . convertToMySQLDate($value) . "'");
        }
    }
    
    // Training dates from emp4.php
    for ($i = 1; $i <= 5; $i++) {
        $field = "tetr{$i}date";
        if (isset($formData[$field])) {
            $value = $formData[$field];
            error_log("$field: '$value' => MySQL: '" . convertToMySQLDate($value) . "'");
        }
    }
	    
    // Check subject fields from emp5.php
    for ($i = 1; $i <= 10; $i++) {
        $subjectField = "tesub{$i}sub";
        if (isset($formData[$subjectField])) {
            error_log("Subject $i - $subjectField: " . $formData[$subjectField]);
        }
    }
    
    // Check promotion fields from emp6.php
    for ($i = 1; $i <= 5; $i++) {
        $prevDesigField = "tepro{$i}pre";
        if (isset($formData[$prevDesigField])) {
            error_log("Promotion $i - $prevDesigField: " . $formData[$prevDesigField]);
        }
    }
    
    // Check transfer fields from emp7.php
    for ($i = 1; $i <= 5; $i++) {
        $fromField = "tetra{$i}from";
        if (isset($formData[$fromField])) {
            error_log("Transfer $i - $fromField: " . $formData[$fromField]);
        }
    }
    
    // Start transaction for data integrity
    $conn->begin_transaction();

    // 1. CREATE EMPLOYEE RECORD
    // Extract personal information from form data
    $personal_no = sanitizeInput($formData['personalno'] ?? '');
    $full_name = sanitizeInput($formData['tename'] ?? '');
    $father_name = sanitizeInput($formData['tefname'] ?? '');
    $cnic = sanitizeInput($formData['tecnic'] ?? '');
    $gender = sanitizeInput($formData['tege'] ?? '');
    $marital_status = isset($formData['temarst']) ? sanitizeInput($formData['temarst']) : null;
    $date_of_birth = convertToMySQLDate($formData['tebirthday'] ?? '');
    $retirement_date = convertToMySQLDate($formData['teretirement'] ?? '');
    $birth_place = isset($formData['tebplace']) ? sanitizeInput($formData['tebplace']) : null;
    $employment_quota_id = (int)($formData['teemq'] ?? 0);
    $mobile_no = sanitizeInput($formData['temobno'] ?? '');
    $whatsapp_no = isset($formData['tewano']) ? sanitizeInput($formData['tewano']) : null;
    $gpf_no = isset($formData['tegpfno']) ? sanitizeInput($formData['tegpfno']) : null;
    $ddo_code = sanitizeInput($formData['teddono'] ?? '');
    $next_of_kin_name = sanitizeInput($formData['tenok'] ?? '');
    $next_of_kin_relation_id = (int)($formData['tenokre'] ?? 0);
    $village = isset($formData['tevillage']) ? sanitizeInput($formData['tevillage']) : null;
    $post_office = isset($formData['tepo']) ? sanitizeInput($formData['tepo']) : null;
    $district_id = (int)($formData['district'] ?? 0);
    $tehsil_id = (int)($formData['tehsil'] ?? 0);
    $constituency_id = (int)($formData['constituency'] ?? 0);
    $union_council_id = (int)($formData['unionCouncil'] ?? 0);
    $employment_type_id = (int)($formData['employmentType'] ?? 0);
    $gross_pay = (float)($formData['grossPay'] ?? 0);
    $initially_appointed_as_id = (int)($formData['iaa'] ?? 0);
    $appointment_date = convertToMySQLDate($formData['doapp'] ?? '');
    $current_designation_id = (int)($formData['tecudesig'] ?? 0);

    // ✅ FIXED: Handle optional fields - convert empty to NULL
    $promotion_date = !empty($formData['dojcp']) ? convertToMySQLDate($formData['dojcp']) : null;
    $current_bps_id = (int)($formData['tecups'] ?? 0);

    // ✅ CRITICAL FIX: Handle current_time_scale_id - empty becomes NULL
    $current_time_scale_id = !empty($formData['current_time_scale']) ? (int)$formData['current_time_scale'] : null;

    // ✅ FIXED: Handle optional time_scale_date
    $time_scale_date = !empty($formData['tetimes']) ? convertToMySQLDate($formData['tetimes']) : null;

    $current_school_emis = sanitizeInput($formData['workin'] ?? '');
    $working_since = convertToMySQLDate($formData['wtis'] ?? '');
    
    // Handle photo path
    $photoPath = 'uploads/employee_photos/default.jpg';
    if (isset($formData['photo_path']) && !empty($formData['photo_path'])) {
        $photoPath = $formData['photo_path'];
        if (!file_exists($photoPath)) {
            $photoPath = 'uploads/employee_photos/default.jpg';
        }
    }

    // Debug logging for optional fields
    error_log("=== EMPLOYEE DATA DEBUG ===");
    error_log("Current Time Scale ID: " . ($current_time_scale_id ?? 'NULL'));
    error_log("Promotion Date: " . ($promotion_date ?? 'NULL'));
    error_log("Time Scale Date: " . ($time_scale_date ?? 'NULL'));
    error_log("=======================");

    // Insert into employees table
    $employeeSql = "INSERT INTO employees (
        personal_no, full_name, father_name, cnic, gender, marital_status, 
        date_of_birth, retirement_date, birth_place, employment_quota_id, mobile_no, 
        whatsapp_no, gpf_no, ddo_code, next_of_kin_name, next_of_kin_relation_id, 
        village, post_office, district_id, tehsil_id, constituency_id, union_council_id, 
        employment_type_id, gross_pay, initially_appointed_as_id, appointment_date, 
        current_designation_id, promotion_date, current_bps_id, current_time_scale_id, 
        time_scale_date, current_school_emis, working_since, photo_path, status, created_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active', NOW())";
    
    $employeeStmt = $conn->prepare($employeeSql);
    if (!$employeeStmt) {
        throw new Exception("Prepare failed for employee: " . $conn->error);
    }
    
    // Bind parameters - note the types:
    // 'i' = integer (can be NULL)
    // 's' = string (can be NULL)
    // 'd' = double/float
    $employeeStmt->bind_param(
        "sssssssssisssssissiiiiiisssiiissss",
        $personal_no, $full_name, $father_name, $cnic, $gender, 
        $marital_status, $date_of_birth, $retirement_date, $birth_place, 
        $employment_quota_id, $mobile_no, $whatsapp_no, $gpf_no, $ddo_code, 
        $next_of_kin_name, $next_of_kin_relation_id, $village, $post_office, 
        $district_id, $tehsil_id, $constituency_id, $union_council_id, 
        $employment_type_id, $gross_pay, $initially_appointed_as_id, 
        $appointment_date, $current_designation_id, $promotion_date, 
        $current_bps_id, $current_time_scale_id, $time_scale_date, 
        $current_school_emis, $working_since, $photoPath
    );
    
    if (!$employeeStmt->execute()) {
        error_log("Employee insert error: " . $employeeStmt->error);
        throw new Exception("Failed to create employee record: " . $employeeStmt->error);
    }
    
    $employee_id = $conn->insert_id;
    $employeeStmt->close();

    error_log("Employee created with ID: " . $employee_id);

    // 2. INSERT ACADEMIC QUALIFICATIONS
    saveAcademicQualifications($conn, $employee_id, $formData);

    // 3. INSERT PROFESSIONAL QUALIFICATIONS  
    saveProfessionalQualifications($conn, $employee_id, $formData);

    // 4. INSERT TRAININGS - CORRECTED FIELD NAMES
    saveTrainings($conn, $employee_id, $formData);

    // 5. INSERT SUBJECTS TAUGHT - CORRECTED FIELD NAMES
    saveSubjectsTaught($conn, $employee_id, $formData);

    // 6. INSERT PROMOTIONS - CORRECTED FIELD NAMES
    savePromotions($conn, $employee_id, $formData);

    // 7. INSERT TRANSFERS - CORRECTED FIELD NAMES
    saveTransfers($conn, $employee_id, $formData);

    // Commit transaction
    $conn->commit();

    // Get counts for summary
    $counts = getRecordCounts($conn, $employee_id);

    // Debug: Log counts
    error_log("=== RECORD COUNTS ===");
    foreach ($counts as $key => $value) {
        error_log("$key: $value");
    }

    // Get employee details for confirmation
    $employee = getEmployeeDetails($conn, $employee_id);

    // Clear session data after successful submission
    unset($_SESSION['form_data']);
	
    // Keep employee_id for profile viewing
	// unset($_SESSION['employee_id']); // REMOVE THIS LINE
    
    // Regenerate CSRF token for security (instead of unsetting)
    regenerateCsrfToken();

    // Display success page
    displaySuccessPage($employee, $counts, $employee_id);

} catch (Exception $e) {
    // Rollback transaction on error
    if (isset($conn) && $conn instanceof mysqli) {
        $conn->rollback();
    }
    
    // Log the error
    error_log("Employee submission error: " . $e->getMessage());
    
    // Display error page
    displayErrorPage($e->getMessage());
}

if (isset($conn) && $conn instanceof mysqli) {
    $conn->close();
}

// ===== HELPER FUNCTIONS =====

function saveAcademicQualifications($conn, $employee_id, $formData) {
    // Academic qualifications mapping - CORRECTED FIELD NAMES
    $academicDegrees = [
        'tesscpass' => ['type' => 'Secondary', 'name' => 'Secondary School Certificate'],
        'tehscpass' => ['type' => 'Higher Secondary', 'name' => 'Higher Secondary Certificate'],
        'tebspass' => ['type' => 'Bachelor', 'name' => 'Bachelor Degree'],
        'temapass' => ['type' => 'Master', 'name' => 'Master Degree'],
        'temphillpass' => ['type' => 'M.Phil', 'name' => 'M.Phil'],
        'tephdpass' => ['type' => 'Ph.D', 'name' => 'Ph.D'],
        'teotherpass' => ['type' => 'Other', 'name' => 'Other Qualification']
    ];

    foreach ($academicDegrees as $passField => $degreeInfo) {
        // Check if passing year exists and is not empty
        if (isset($formData[$passField]) && !empty(trim($formData[$passField]))) {
            $originalDate = sanitizeInput($formData[$passField]);
            $passing_year = convertToMySQLDate($originalDate); // Convert to MySQL format
            
            // Determine the correct field names for related data
            $subjectsField = str_replace('pass', 'sub', $passField);
            $universityField = str_replace('pass', 'uni', $passField);
            $totalField = str_replace('pass', 'tot', $passField);
            $obtainedField = str_replace('pass', 'om', $passField);
            $divisionField = str_replace('pass', 'div', $passField);
            
            $subjects = isset($formData[$subjectsField]) ? sanitizeInput($formData[$subjectsField]) : '';
            $university_board = isset($formData[$universityField]) ? sanitizeInput($formData[$universityField]) : '';
            $total_marks = isset($formData[$totalField]) ? (float)$formData[$totalField] : 0;
            $obtained_marks = isset($formData[$obtainedField]) ? (float)$formData[$obtainedField] : 0;
            $division_grade = isset($formData[$divisionField]) ? sanitizeInput($formData[$divisionField]) : '';

            // DEBUG: Log the values
            error_log("=== Academic Qualification ===");
            error_log("Employee ID: " . $employee_id);
            error_log("Degree: " . $degreeInfo['name']);
            error_log("Original Date: " . $originalDate);
            error_log("Converted Date: " . $passing_year);
            error_log("Subjects: " . ($subjects ?: 'empty'));
            error_log("University: " . ($university_board ?: 'empty'));

            // Only insert if we have a valid date
            if ($passing_year !== null) {
                $sql = "INSERT INTO academic_qualifications (
                    employee_id, degree_type, degree_name, passing_year, subjects, 
                    university_board, total_marks, obtained_marks, division_grade
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

                $stmt = $conn->prepare($sql);
                if (!$stmt) {
                    error_log("Prepare failed for academic: " . $conn->error);
                    continue;
                }

                $stmt->bind_param(
                    "issssddss",
                    $employee_id, 
                    $degreeInfo['type'], 
                    $degreeInfo['name'], 
                    $passing_year, 
                    $subjects, 
                    $university_board, 
                    $total_marks, 
                    $obtained_marks, 
                    $division_grade
                );

                if (!$stmt->execute()) {
                    error_log("Execute failed for academic: " . $stmt->error);
                } else {
                    error_log("Successfully inserted academic record: " . $degreeInfo['name']);
                }
                
                $stmt->close();
            } else {
                error_log("Skipping academic qualification - invalid date format: " . $originalDate);
            }
        } else {
            error_log("Skipping academic qualification - $passField not set or empty");
        }
    }
}

function saveProfessionalQualifications($conn, $employee_id, $formData) {
    // Professional qualifications mapping - CORRECTED
    $professionalDegrees = [
        'teptpass' => 'Primary Teaching Certificate',
        'tebedpass' => 'Bachelor of Education',
        'temedpass' => 'Master of Education'
    ];

    $count = 0;
    foreach ($professionalDegrees as $passField => $qualification_name) {
        if (isset($formData[$passField]) && !empty(trim($formData[$passField]))) {
            $originalDate = sanitizeInput($formData[$passField]);
            $passing_year = convertToMySQLDate($originalDate); // Convert to MySQL format
            
            $institutionField = str_replace('pass', 'is', $passField);
            $institution = isset($formData[$institutionField]) ? sanitizeInput($formData[$institutionField]) : '';
            
            $totalField = str_replace('pass', 'tot', $passField);
            $obtainedField = str_replace('pass', 'om', $passField);
            $divisionField = str_replace('pass', 'div', $passField);
            
            $total_marks = isset($formData[$totalField]) ? (float)$formData[$totalField] : 0;
            $obtained_marks = isset($formData[$obtainedField]) ? (float)$formData[$obtainedField] : 0;
            $division_grade = isset($formData[$divisionField]) ? sanitizeInput($formData[$divisionField]) : '';

            error_log("=== Professional Qualification ===");
            error_log("Qualification: $qualification_name");
            error_log("Original Date: $originalDate");
            error_log("Converted Date: $passing_year");
            error_log("Institution: " . ($institution ?: 'empty'));

            // Only insert if we have a valid date
            if ($passing_year !== null) {
                $sql = "INSERT INTO professional_qualifications (
                    employee_id, qualification_name, passing_year, institution, 
                    total_marks, obtained_marks, division_grade
                ) VALUES (?, ?, ?, ?, ?, ?, ?)";

                $stmt = $conn->prepare($sql);
                if (!$stmt) {
                    error_log("Prepare failed for professional: " . $conn->error);
                    continue;
                }

                $stmt->bind_param(
                    "isssdds",
                    $employee_id, $qualification_name, $passing_year, $institution,
                    $total_marks, $obtained_marks, $division_grade
                );

                if (!$stmt->execute()) {
                    error_log("Execute failed for professional: " . $stmt->error);
                } else {
                    $count++;
                    error_log("Successfully inserted professional record: $qualification_name");
                }
                $stmt->close();
            } else {
                error_log("Skipping professional qualification - invalid date format: " . $originalDate);
            }
        }
    }

    // Other professional qualifications (up to 3)
    for ($i = 1; $i <= 3; $i++) {
        $nameField = "teo{$i}name";
        if (isset($formData[$nameField]) && !empty(trim($formData[$nameField]))) {
            $qualification_name = sanitizeInput($formData[$nameField]);
            $passField = "teo{$i}pass";
            $institutionField = "teo{$i}is";
            $totalField = "teo{$i}tot";
            $obtainedField = "teo{$i}om";
            $divisionField = "teo{$i}div";
            
            if (isset($formData[$passField]) && !empty(trim($formData[$passField]))) {
                $originalDate = sanitizeInput($formData[$passField]);
                $passing_year = convertToMySQLDate($originalDate);
                $institution = isset($formData[$institutionField]) ? sanitizeInput($formData[$institutionField]) : '';
                $total_marks = isset($formData[$totalField]) ? (float)$formData[$totalField] : 0;
                $obtained_marks = isset($formData[$obtainedField]) ? (float)$formData[$obtainedField] : 0;
                $division_grade = isset($formData[$divisionField]) ? sanitizeInput($formData[$divisionField]) : '';

                // Only insert if we have a valid date
                if ($passing_year !== null) {
                    $sql = "INSERT INTO professional_qualifications (
                        employee_id, qualification_name, passing_year, institution, 
                        total_marks, obtained_marks, division_grade
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)";

                    $stmt = $conn->prepare($sql);
                    if ($stmt) {
                        $stmt->bind_param(
                            "isssdds",
                            $employee_id, $qualification_name, $passing_year, $institution,
                            $total_marks, $obtained_marks, $division_grade
                        );

                        if ($stmt->execute()) {
                            $count++;
                            error_log("Successfully inserted other professional: $qualification_name");
                        }
                        $stmt->close();
                    }
                } else {
                    error_log("Skipping other professional qualification - invalid date format: " . $originalDate);
                }
            }
        }
    }
    
    error_log("Total professional qualifications inserted: $count");
}


function saveTrainings($conn, $employee_id, $formData) {
    $count = 0;
    // Insert in-service training (up to 5) - CORRECTED FIELD NAMES FROM emp4.php
    for ($i = 1; $i <= 5; $i++) {
        // CORRECT FIELD NAMES: tetr1name, tetr1date, tetr1ins, tetr1pla, tetr1dur
        $nameField = "tetr{$i}name";
        $dateField = "tetr{$i}date";  // CHANGED from "year" to "date"
        $instituteField = "tetr{$i}ins";
        $placeField = "tetr{$i}pla";
        $durationField = "tetr{$i}dur";
        
        // Check if training name exists AND is not empty
        if (isset($formData[$nameField]) && !empty(trim($formData[$nameField]))) {
            $training_name = sanitizeInput($formData[$nameField]);
            $originalDate = isset($formData[$dateField]) ? sanitizeInput($formData[$dateField]) : '';
            $institution = isset($formData[$instituteField]) ? sanitizeInput($formData[$instituteField]) : '';
            $place = isset($formData[$placeField]) ? sanitizeInput($formData[$placeField]) : '';
            $duration_days = isset($formData[$durationField]) ? (int)$formData[$durationField] : 0;

            error_log("=== Training Record $i ===");
            error_log("Training Name Field: $nameField = $training_name");
            error_log("Training Date Field: $dateField = $originalDate");
            error_log("Institution Field: $instituteField = $institution");

            // Convert training date to MySQL format
            $training_date = null;
            if (!empty($originalDate)) {
                $training_date = convertToMySQLDate($originalDate); // CHANGED from convertMonthYearToDate
                error_log("Converted Training Date: $training_date");
            }
            
            // Insert even if some fields are empty, but we need at least training name
            if (!empty($training_name)) {
                $sql = "INSERT INTO trainings (
                    employee_id, training_name, training_date, institution, 
                    place, duration_days
                ) VALUES (?, ?, ?, ?, ?, ?)";

                $stmt = $conn->prepare($sql);
                if ($stmt) {
                    $stmt->bind_param(
                        "issssi",
                        $employee_id, $training_name, $training_date, $institution, 
                        $place, $duration_days
                    );

                    if ($stmt->execute()) {
                        $count++;
                        error_log("Successfully inserted training: $training_name");
                    } else {
                        error_log("Failed to insert training: " . $stmt->error);
                    }
                    $stmt->close();
                } else {
                    error_log("Failed to prepare statement for training $i");
                }
            }
        } else {
            error_log("Skipping training $i - name field empty or not set");
        }
    }
    
    error_log("Total trainings inserted: $count");
}

function saveSubjectsTaught($conn, $employee_id, $formData) {
    $count = 0;
    // Insert subjects teaching (up to 10) - CORRECTED FIELD NAMES FROM emp5.php
    for ($i = 1; $i <= 10; $i++) {
        // CORRECT FIELD NAMES: tesub1sub, tesub1cla, tesub1sec, tesub1per
        $subjectField = "tesub{$i}sub";
        $classField = "tesub{$i}cla";
		$sectionField = "tesub{$i}sec";
        $periodField = "tesub{$i}per";
        
        $subject_code = isset($formData[$subjectField]) ? (int)$formData[$subjectField] : 0;
        $class_id = isset($formData[$classField]) ? (int)$formData[$classField] : 0;
		$section_id   = isset($formData[$sectionField]) ? (int)$formData[$sectionField] : 0;
        $periods_per_week = isset($formData[$periodField]) ? (int)$formData[$periodField] : 0;

        // Log for debugging
        if ($subject_code > 0 || $class_id > 0 || $periods_per_week > 0) {
            error_log("=== Subject Taught $i ===");
            error_log("Subject Field: $subjectField = $subject_code");
            error_log("Class Field: $classField = $class_id");
			error_log("Section Field: $sectionField = $section_id");
            error_log("Period Field: $periodField = $periods_per_week");
        }

        // Insert record even if some values are 0
        if ($subject_code > 0 || $class_id > 0 || $section_id > 0 || $periods_per_week > 0) {
            $sql = "INSERT INTO subjects_taught (
                employee_id, subject_code, class_id, section_id, periods_per_week
            ) VALUES (?, ?, ?, ?, ?)";

            $stmt = $conn->prepare($sql);
            if ($stmt) {
                $stmt->bind_param(
                    "iiiii",
                    $employee_id, $subject_code, $class_id, $section_id, $periods_per_week
                );

                if ($stmt->execute()) {
                    $count++;
                    error_log("Successfully inserted subject record");
                } else {
                    error_log("Failed to insert subject: " . $stmt->error);
                }
                $stmt->close();
            } else {
                error_log("Failed to prepare statement for subject $i");
            }
        }
    }
    
    error_log("Total subjects taught inserted: $count");
}

function savePromotions($conn, $employee_id, $formData) {
    $count = 0;
    // Insert promotion history (up to 5) - CORRECTED FIELD NAMES FROM emp6.php
    for ($i = 1; $i <= 5; $i++) {
        // CORRECT FIELD NAMES: tepro1pre, tepro1presc, tepro1new, tepro1newsc, tepro1date
        $prevDesigField = "tepro{$i}pre";
        $prevScaleField = "tepro{$i}presc";
        $newDesigField = "tepro{$i}new";
        $newScaleField = "tepro{$i}newsc";
        $dateField = "tepro{$i}date";
        
        $previous_designation_id = isset($formData[$prevDesigField]) ? (int)$formData[$prevDesigField] : 0;
        $previous_scale_id = isset($formData[$prevScaleField]) ? (int)$formData[$prevScaleField] : 0;
        $new_designation_id = isset($formData[$newDesigField]) ? (int)$formData[$newDesigField] : 0;
        $new_scale_id = isset($formData[$newScaleField]) ? (int)$formData[$newScaleField] : 0;
        
        $originalDate = $formData[$dateField] ?? '';
        $promotion_date = convertToMySQLDate($originalDate);

        // Debug log
        if ($previous_designation_id > 0 || $new_designation_id > 0) {
            error_log("=== Promotion Record $i ===");
            error_log("Prev Designation Field: $prevDesigField = $previous_designation_id");
            error_log("Prev Scale Field: $prevScaleField = $previous_scale_id");
            error_log("New Designation Field: $newDesigField = $new_designation_id");
            error_log("New Scale Field: $newScaleField = $new_scale_id");
            error_log("Date Field: $dateField = '$originalDate' => '$promotion_date'");
        }

        // Insert even if some values are 0
        if ($previous_designation_id > 0 || $new_designation_id > 0 || $previous_scale_id > 0 || $new_scale_id > 0) {
            // If no date provided but we have data, use current date
            if (empty($promotion_date) && ($previous_designation_id > 0 || $new_designation_id > 0)) {
                $promotion_date = date('Y-m-d');

            }
            
            $sql = "INSERT INTO promotions (
                employee_id, previous_designation_id, previous_scale_id, 
                new_designation_id, new_scale_id, promotion_date
            ) VALUES (?, ?, ?, ?, ?, ?)";

            $stmt = $conn->prepare($sql);
            if ($stmt) {
                $stmt->bind_param(
                    "iiiiis",
                    $employee_id, $previous_designation_id, $previous_scale_id,
                    $new_designation_id, $new_scale_id, $promotion_date
                );

                if ($stmt->execute()) {
                    $count++;
                    error_log("Successfully inserted promotion record");
                } else {
                    error_log("Failed to insert promotion: " . $stmt->error);
                }
                $stmt->close();
            } else {
                error_log("Failed to prepare statement for promotion $i");
            }
        }
    }
    
    error_log("Total promotions inserted: $count");
}


function saveTransfers($conn, $employee_id, $formData) {
    $count = 0;
    // Insert transfer history (up to 5) - CORRECTED FIELD NAMES FROM emp7.php
    for ($i = 1; $i <= 5; $i++) {
        // CORRECT FIELD NAMES: tetra1from, tetra1to, tetra1reason, tetra1date
        $fromField = "tetra{$i}from";
        $toField = "tetra{$i}to";
        $reasonField = "tetra{$i}reason";
        $dateField = "tetra{$i}date";
        
        $from_school_emis = isset($formData[$fromField]) ? sanitizeInput($formData[$fromField]) : '';
        $to_school_emis = isset($formData[$toField]) ? sanitizeInput($formData[$toField]) : '';
        $reason_id = isset($formData[$reasonField]) ? (int)$formData[$reasonField] : 0;
        
        $originalDate = $formData[$dateField] ?? '';
        $transfer_date = convertToMySQLDate($originalDate);

        // Debug log
        if (!empty($from_school_emis) || !empty($to_school_emis)) {
            error_log("=== Transfer Record $i ===");
            error_log("From Field: $fromField = '$from_school_emis'");
            error_log("To Field: $toField = '$to_school_emis'");
            error_log("Reason Field: $reasonField = $reason_id");
            error_log("Date Field: $dateField = '$originalDate' => '$transfer_date'");
        }

        // Insert even if some fields are empty
        if (!empty($from_school_emis) || !empty($to_school_emis) || $reason_id > 0) {
            // If no date provided but we have data, use current date
            if (empty($transfer_date) && (!empty($from_school_emis) || !empty($to_school_emis))) {
                $transfer_date = date('Y-m-d');
            }
            
            $sql = "INSERT INTO transfers (
                employee_id, from_school_emis, to_school_emis, 
                reason_id, transfer_date
            ) VALUES (?, ?, ?, ?, ?)";

            $stmt = $conn->prepare($sql);
            if ($stmt) {
                $stmt->bind_param(
                    "ssiis",
                    $employee_id, $from_school_emis, $to_school_emis,
                    $reason_id, $transfer_date
                );

                if ($stmt->execute()) {
                    $count++;
                    error_log("Successfully inserted transfer record");
                } else {
                    error_log("Failed to insert transfer: " . $stmt->error);
                }
                $stmt->close();
            } else {
                error_log("Failed to prepare statement for transfer $i");
            }
        }
    }
    
    error_log("Total transfers inserted: $count");
}

function getRecordCounts($conn, $employee_id) {
    $counts = [
        'academic' => 0,
        'professional' => 0,
        'trainings' => 0,
        'subjects' => 0,
        'promotions' => 0,
        'transfers' => 0
    ];
    
    $tables = [
        'academic' => 'academic_qualifications',
        'professional' => 'professional_qualifications', 
        'trainings' => 'trainings',
        'subjects' => 'subjects_taught',
        'promotions' => 'promotions',
        'transfers' => 'transfers'
    ];

    foreach ($tables as $key => $table) {
        $countSql = "SELECT COUNT(*) as count FROM {$table} WHERE employee_id = ?";
        $countStmt = $conn->prepare($countSql);
        if ($countStmt) {
            $countStmt->bind_param("i", $employee_id);
            if ($countStmt->execute()) {
                $countResult = $countStmt->get_result();
                $counts[$key] = $countResult->fetch_assoc()['count'];
            }
            $countStmt->close();
        }
        
        error_log("Count for $key: " . $counts[$key]);
    }

    return $counts;
}

function getEmployeeDetails($conn, $employee_id) {
    $employee = [];
    $employeeDetailSql = "SELECT * FROM employees WHERE employee_id = ?";
    $employeeDetailStmt = $conn->prepare($employeeDetailSql);
    if ($employeeDetailStmt) {
        $employeeDetailStmt->bind_param("i", $employee_id);
        $employeeDetailStmt->execute();
        $employeeResult = $employeeDetailStmt->get_result();
        $employee = $employeeResult->fetch_assoc();
        $employeeDetailStmt->close();
    }
    
    return $employee;
}

function displaySuccessPage($employee, $counts, $employee_id) {
    // Get photo path from employee data
    $photoPath = $employee['photo_path'] ?? 'uploads/employee_photos/default.jpg';
    // Make sure the photo path is valid
    if ($photoPath === 'uploads/employee_photos/default.jpg' || !file_exists($photoPath)) {
        $photoPath = 'uploads/employee_photos/default.jpg';
    }
    ?>
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Employee Profile Submitted Successfully</title>
        <link href="https://fonts.googleapis.com/css2?family=Roboto:wght@300;400;500;700&display=swap" rel="stylesheet">
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">
        <style>
            :root {
                --primary-color: #3498db;
                --secondary-color: #2980b9;
                --success-color: #27ae60;
                --warning-color: #f39c12;
                --light-gray: #f5f5f5;
                --border-radius: 8px;
                --box-shadow: 0 4px 12px rgba(0, 0, 0, 0.1);
            }
            * {
                box-sizing: border-box;
                margin: 0;
                padding: 0;
            }
            body {
                font-family: 'Roboto', sans-serif;
                line-height: 1.6;
                background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
                min-height: 100vh;
                display: flex;
                align-items: center;
                justify-content: center;
                padding: 20px;
            }
            .success-container {
                background: white;
                border-radius: var(--border-radius);
                box-shadow: var(--box-shadow);
                padding: 40px;
                max-width: 800px;
                width: 100%;
                text-align: center;
            }
            .success-icon {
                font-size: 80px;
                color: var(--success-color);
                margin-bottom: 20px;
            }
            .success-title {
                color: var(--success-color);
                font-size: 32px;
                margin-bottom: 20px;
                font-weight: 500;
            }
            .success-message {
                color: #555;
                font-size: 18px;
                margin-bottom: 30px;
            }
            .employee-photo-section {
                margin: 20px 0;
                display: flex;
                flex-direction: column;
                align-items: center;
            }
            .employee-photo {
                width: 150px;
                height: 150px;
                border-radius: 50%;
                object-fit: cover;
                border: 5px solid var(--primary-color);
                box-shadow: 0 4px 8px rgba(0,0,0,0.2);
                margin-bottom: 15px;
            }
            .photo-label {
                background: var(--primary-color);
                color: white;
                padding: 8px 20px;
                border-radius: 20px;
                font-size: 14px;
                font-weight: 500;
            }
            .summary-section {
                background: var(--light-gray);
                padding: 25px;
                border-radius: var(--border-radius);
                margin: 25px 0;
                text-align: left;
            }
            .summary-title {
                color: var(--primary-color);
                font-size: 20px;
                margin-bottom: 15px;
                text-align: center;
            }
            .summary-grid {
                display: grid;
                grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
                gap: 15px;
                margin-top: 15px;
            }
            .summary-item {
                background: white;
                padding: 15px;
                border-radius: var(--border-radius);
                text-align: center;
                box-shadow: 0 2px 4px rgba(0,0,0,0.1);
                transition: transform 0.3s ease;
            }
            .summary-item:hover {
                transform: translateY(-5px);
            }
            .summary-number {
                font-size: 32px;
                font-weight: bold;
                color: var(--primary-color);
                margin-bottom: 8px;
            }
            .summary-label {
                color: #666;
                font-size: 16px;
                font-weight: 500;
            }
            .action-buttons {
                display: flex;
                gap: 15px;
                justify-content: center;
                margin-top: 30px;
                flex-wrap: wrap;
            }
            .btn {
                padding: 12px 24px;
                border: none;
                border-radius: var(--border-radius);
                font-size: 16px;
                font-weight: 500;
                cursor: pointer;
                text-decoration: none;
                display: inline-flex;
                align-items: center;
                justify-content: center;
                transition: all 0.3s ease;
                min-width: 180px;
            }
            .btn-primary {
                background-color: var(--primary-color);
                color: white;
            }
            .btn-primary:hover {
                background-color: var(--secondary-color);
                transform: translateY(-2px);
                box-shadow: 0 4px 8px rgba(0,0,0,0.2);
            }
            .btn-success {
                background-color: var(--success-color);
                color: white;
            }
            .btn-success:hover {
                background-color: #219a52;
                transform: translateY(-2px);
                box-shadow: 0 4px 8px rgba(0,0,0,0.2);
            }
            .btn-warning {
                background-color: var(--warning-color);
                color: white;
            }
            .btn-warning:hover {
                background-color: #e67e22;
                transform: translateY(-2px);
                box-shadow: 0 4px 8px rgba(0,0,0,0.2);
            }
            .btn i {
                margin-right: 8px;
            }
            .employee-details {
                background: var(--light-gray);
                padding: 20px;
                border-radius: var(--border-radius);
                margin: 20px 0;
                text-align: left;
            }
            .detail-row {
                display: flex;
                justify-content: space-between;
                padding: 12px 0;
                border-bottom: 1px solid #ddd;
            }
            .detail-row:last-child {
                border-bottom: none;
            }
            .detail-label {
                font-weight: 500;
                color: #555;
                min-width: 150px;
            }
            .detail-value {
                color: #333;
                font-weight: 500;
            }
            .employee-id-badge {
                display: inline-block;
                background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
                color: white;
                padding: 8px 20px;
                border-radius: 20px;
                font-size: 14px;
                margin-top: 10px;
                font-weight: 500;
            }
            @media (max-width: 768px) {
                .success-container {
                    padding: 20px;
                }
                .action-buttons {
                    flex-direction: column;
                }
                .btn {
                    width: 100%;
                }
                .summary-grid {
                    grid-template-columns: repeat(2, 1fr);
                }
                .detail-row {
                    flex-direction: column;
                }
                .detail-label {
                    margin-bottom: 5px;
                }
            }
            @media (max-width: 480px) {
                .summary-grid {
                    grid-template-columns: 1fr;
                }
            }
        </style>
    </head>
    <body>
        <div class="success-container">
            <div class="success-icon">
                <i class="fas fa-check-circle"></i>
            </div>
            
            <h1 class="success-title">Employee Profile Submitted Successfully!</h1>
            
            <!-- Employee Photo Section (Replacing Employee ID) -->
            <div class="employee-photo-section">
                <img src="<?php echo htmlspecialchars($photoPath); ?>" 
                     alt="Employee Photo" 
                     class="employee-photo"
                     onerror="this.src='uploads/employee_photos/default.jpg'">
                <div class="photo-label">
                    <i class="fas fa-camera"></i> Employee Photo
                </div>
            </div>
            
            <p class="success-message">
                Thank you! The employee profile has been successfully submitted and saved in the system.
            </p>

            <!-- Employee Basic Information -->
            <div class="employee-details">
                <h3 style="color: var(--primary-color); margin-bottom: 15px; text-align: center;">
                    <i class="fas fa-user"></i> Employee Summary
                </h3>
                <div class="detail-row">
                    <span class="detail-label">Personal Number:</span>
                    <span class="detail-value"><?php echo htmlspecialchars($employee['personal_no'] ?? 'N/A'); ?></span>
                </div>
                <div class="detail-row">
                    <span class="detail-label">Full Name:</span>
                    <span class="detail-value"><?php echo htmlspecialchars($employee['full_name'] ?? 'N/A'); ?></span>
                </div>
                <div class="detail-row">
                    <span class="detail-label">Father's Name:</span>
                    <span class="detail-value"><?php echo htmlspecialchars($employee['father_name'] ?? 'N/A'); ?></span>
                </div>
                <div class="detail-row">
                    <span class="detail-label">CNIC:</span>
                    <span class="detail-value"><?php echo htmlspecialchars($employee['cnic'] ?? 'N/A'); ?></span>
                </div>
                <div class="detail-row">
                    <span class="detail-label">Current School:</span>
                    <span class="detail-value"><?php echo htmlspecialchars($employee['current_school_emis'] ?? 'N/A'); ?></span>
                </div>
                <div class="detail-row">
                    <span class="detail-label">Mobile No:</span>
                    <span class="detail-value"><?php echo htmlspecialchars($employee['mobile_no'] ?? 'N/A'); ?></span>
                </div>
            </div>

            <!-- Data Summary -->
            <div class="summary-section">
                <h3 class="summary-title"><i class="fas fa-chart-bar"></i> Data Summary</h3>
                <div class="summary-grid">
                    <div class="summary-item">
                        <div class="summary-number"><?php echo htmlspecialchars($counts['academic'] ?? 0); ?></div>
                        <div class="summary-label">Academic Qualifications</div>
                    </div>
                    <div class="summary-item">
                        <div class="summary-number"><?php echo htmlspecialchars($counts['professional'] ?? 0); ?></div>
                        <div class="summary-label">Professional Qualifications</div>
                    </div>
                    <div class="summary-item">
                        <div class="summary-number"><?php echo htmlspecialchars($counts['trainings'] ?? 0); ?></div>
                        <div class="summary-label">In-Service Trainings</div>
                    </div>
                    <div class="summary-item">
                        <div class="summary-number"><?php echo htmlspecialchars($counts['subjects'] ?? 0); ?></div>
                        <div class="summary-label">Subjects Taught</div>
                    </div>
                    <div class="summary-item">
                        <div class="summary-number"><?php echo htmlspecialchars($counts['promotions'] ?? 0); ?></div>
                        <div class="summary-label">Promotion Records</div>
                    </div>
                    <div class="summary-item">
                        <div class="summary-number"><?php echo htmlspecialchars($counts['transfers'] ?? 0); ?></div>
                        <div class="summary-label">Transfer Records</div>
                    </div>
                </div>
            </div>

            <!-- Action Buttons -->
            <div class="action-buttons">
                <a href="employee_profile_view.php?id=<?php echo $employee_id; ?>" class="btn btn-success">
                    <i class="fas fa-eye"></i> View Profile
                </a>
                
                <a href="employee_dashboard.php?new_emp=<?php echo $employee_id; ?>&cnic=<?php echo urlencode($employee['cnic']); ?>" class="btn btn-warning">
                    <i class="fas fa-tachometer-alt"></i> Go to Dashboard
                </a>
            </div>

            <!-- Important Notes -->
            <div style="margin-top: 30px; padding: 15px; background: #e8f4fc; border-radius: var(--border-radius);">
                <h4 style="color: #2c3e50; margin-bottom: 10px;">
                    <i class="fas fa-info-circle"></i> Important Notes:
                </h4>
                <ul style="text-align: left; color: #555; margin-left: 20px;">
                    <li>Use Your Personal Number for future reference.</li>
                    <li>You can view and edit the profile anytime using the Personal Number or CNIC Number.</li>
                    <li>All data has been verified and saved in the system.</li>
                </ul>
            </div>
        </div>
        
        <script>
            // Add some interactivity
            document.addEventListener('DOMContentLoaded', function() {
                // Add animation to summary items
                const summaryItems = document.querySelectorAll('.summary-item');
                summaryItems.forEach((item, index) {
                    item.style.animationDelay = `${index * 0.1}s`;
                    item.classList.add('animate-pop');
                });
                
                // Check if photo loads successfully
                const employeePhoto = document.querySelector('.employee-photo');
                employeePhoto.addEventListener('error', function() {
                    this.src = 'uploads/employee_photos/default.jpg';
                });
            });
        </script>
    </body>
    </html>
    <?php
}

function displayErrorPage($error_message) {
    ?>
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Submission Error</title>
        <link href="https://fonts.googleapis.com/css2?family=Roboto:wght@300;400;500;700&display=swap" rel="stylesheet">
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">
        <style>
            :root {
                --error-color: #e74c3c;
                --primary-color: #3498db;
            }
            body {
                font-family: 'Roboto', sans-serif;
                background: linear-gradient(135deg, #ff6b6b 0%, #ee5a24 100%);
                min-height: 100vh;
                display: flex;
                align-items: center;
                justify-content: center;
                padding: 20px;
                margin: 0;
            }
            .error-container {
                background: white;
                border-radius: 10px;
                box-shadow: 0 8px 25px rgba(0,0,0,0.2);
                padding: 40px;
                max-width: 500px;
                width: 100%;
                text-align: center;
            }
            .error-icon {
                font-size: 80px;
                color: var(--error-color);
                margin-bottom: 20px;
            }
            .error-title {
                color: var(--error-color);
                font-size: 28px;
                margin-bottom: 15px;
            }
            .error-message {
                color: #555;
                margin-bottom: 25px;
                line-height: 1.6;
            }
            .btn {
                padding: 12px 24px;
                background: var(--primary-color);
                color: white;
                border: none;
                border-radius: 5px;
                font-size: 16px;
                cursor: pointer;
                text-decoration: none;
                display: inline-flex;
                align-items: center;
                gap: 8px;
                transition: all 0.3s ease;
            }
            .btn:hover {
                background: #2980b9;
                transform: translateY(-2px);
            }
        </style>
    </head>
    <body>
        <div class="error-container">
            <div class="error-icon">
                <i class="fas fa-exclamation-triangle"></i>
            </div>
            <h1 class="error-title">Submission Failed</h1>
            <p class="error-message">
                We encountered an error while processing your submission:<br>
                <strong><?php echo htmlspecialchars($error_message); ?></strong>
            </p>
            <p style="color: #666; margin-bottom: 25px;">
                Please try again or contact system administrator if the problem persists.
            </p>
            <a href="emp1.php" class="btn">
                <i class="fas fa-arrow-left"></i> Back to Form
            </a>
        </div>
    </body>
    </html>
    <?php
}
?>