<?php
// report_base.php - ملف أساسي للتقارير
require 'db.php';
require 'helpers.php';
require_committee();

// تفعيل تصحيح الأخطاء
error_reporting(E_ALL);
ini_set('display_errors', 1);

$committeeId   = (int)($_SESSION['committee_id'] ?? 0);
$committeeName = (string)($_SESSION['committee_name'] ?? '');
$canAll        = (bool)($_SESSION['committee_can_all'] ?? false);
$scopeCollege  = isset($_SESSION['committee_college_id']) ? (int)$_SESSION['committee_college_id'] : null;

$mysqli1->set_charset('utf8mb4');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// إعدادات التصفية
$filter_college_id = isset($_GET['filter_college']) && $_GET['filter_college'] !== '' ? (int)$_GET['filter_college'] : null;
$filter_section_id = isset($_GET['filter_section']) && $_GET['filter_section'] !== '' ? (int)$_GET['filter_section'] : null;
$filter_program_id = isset($_GET['filter_program']) && $_GET['filter_program'] !== '' ? (int)$_GET['filter_program'] : null;
$filter_batch_id = isset($_GET['filter_batch']) && $_GET['filter_batch'] !== '' ? (int)$_GET['filter_batch'] : null;
$filter_semester_id = isset($_GET['filter_semester']) && $_GET['filter_semester'] !== '' ? (int)$_GET['filter_semester'] : null;
$filter_applied = isset($_GET['filter_applied']) ? (bool)$_GET['filter_applied'] : false;
$export = isset($_GET['export']) && $_GET['export'] === 'excel';

// جلب خيارات التصفية الأساسية
$colleges = $programs = $batches = $semesters = $sections = [];

try {
    // الكليات
    $colleges_stmt = $mysqli1->prepare("SELECT id, name FROM colleges WHERE deleted_at IS NULL ORDER BY name");
    $colleges_stmt->execute();
    $colleges = $colleges_stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    $colleges_stmt->close();
    
    // البرامج
    $programs_stmt = $mysqli1->prepare("SELECT id, name FROM programs WHERE deleted_at IS NULL ORDER BY name");
    $programs_stmt->execute();
    $programs = $programs_stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    $programs_stmt->close();
    
    // الدفعات
    $batches_stmt = $mysqli1->prepare("SELECT id, value FROM batches WHERE deleted_at IS NULL ORDER BY id");
    $batches_stmt->execute();
    $batches = $batches_stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    $batches_stmt->close();
    
    // الفصول الدراسية
    $semesters_stmt = $mysqli1->prepare("SELECT id, name, code FROM semesters WHERE deleted_at IS NULL ORDER BY id DESC");
    $semesters_stmt->execute();
    $semesters = $semesters_stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    $semesters_stmt->close();
    
    // الأقسام (إذا كانت هناك كلية محددة)
    if ($filter_college_id && !empty($filter_college_id)) {
        $sections_sql = "SELECT id, name FROM sections WHERE parent = ? AND deleted_at IS NULL ORDER BY name";
        $sections_stmt = $mysqli1->prepare($sections_sql);
        $sections_stmt->bind_param("i", $filter_college_id);
        $sections_stmt->execute();
        $sections = $sections_stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $sections_stmt->close();
    }
    
} catch (Exception $e) {
    error_log("Error loading filter options: " . $e->getMessage());
}

// بناء WHERE بسيط
$where = " WHERE s.deleted_at IS NULL ";
$types = ''; 
$params = [];

// تحديد نطاق الكلية للجنة
if (!$canAll && $scopeCollege !== null) {
    $where .= " AND s.college_id = ? ";
    $types .= 'i'; 
    $params[] = $scopeCollege;
    if (!$filter_college_id) {
        $filter_college_id = $scopeCollege;
    }
} elseif (!$canAll && $scopeCollege === null) {
    $where .= " AND 1=0 "; // لا يمكنه رؤية أي شيء
}

// تطبيق الفلاتر الأساسية
if ($filter_college_id && $filter_college_id > 0) {
    $where .= " AND s.college_id = ? ";
    $types .= 'i'; 
    $params[] = $filter_college_id;
}

if ($filter_section_id && $filter_section_id > 0) {
    $where .= " AND s.section_id = ? ";
    $types .= 'i'; 
    $params[] = $filter_section_id;
}

if ($filter_program_id && $filter_program_id > 0) {
    $where .= " AND s.program_id = ? ";
    $types .= 'i'; 
    $params[] = $filter_program_id;
}

if ($filter_batch_id && $filter_batch_id > 0) {
    $where .= " AND s.batch_id = ? ";
    $types .= 'i'; 
    $params[] = $filter_batch_id;
}

// الحصول على أسماء الفلاتر للتحديث الديناميكي
$college_name = "";
$section_name = "";
$program_name = "";
$batch_name = "";
$semester_name = "";

if ($filter_college_id) {
    foreach ($colleges as $college) {
        if ($college['id'] == $filter_college_id) {
            $college_name = $college['name'];
            break;
        }
    }
}

if ($filter_section_id && !empty($sections)) {
    foreach ($sections as $section) {
        if ($section['id'] == $filter_section_id) {
            $section_name = $section['name'];
            break;
        }
    }
}

if ($filter_program_id) {
    foreach ($programs as $program) {
        if ($program['id'] == $filter_program_id) {
            $program_name = $program['name'];
            break;
        }
    }
}

if ($filter_batch_id) {
    foreach ($batches as $batch) {
        if ($batch['id'] == $filter_batch_id) {
            $batch_name = $batch['value'];
            break;
        }
    }
}

if ($filter_semester_id) {
    foreach ($semesters as $semester) {
        if ($semester['id'] == $filter_semester_id) {
            $semester_name = $semester['name'] . ' (' . $semester['code'] . ')';
            break;
        }
    }
}

// دالة لبناء استعلام الطلاب - إصدارة مصححة
function buildStudentsQuery($where, $types, $params, $status_type, $filter_semester_id = null) {
    
    // بناء الاستعلام الأساسي
    $sql = "
        SELECT 
            s.student_id, 
            s.student_name, 
            s.student_name_eng, 
            s.gender,
            s.phone,
            s.email,
            s.national_id,
            c.name AS college_name,
            sec.name AS section_name,
            p.name AS program_name,
            b.value AS batch_name,
            sem.name AS semester_name,
            sem.code AS semester_code,
            COALESCE(reg_stats.registration_date, s.created_at) as registration_date,
            COALESCE(reg_stats.total_paid_amount, 0) as total_paid_amount,
            COALESCE(reg_stats.total_unpaid_amount, 0) as total_unpaid_amount,
            COALESCE(reg_stats.total_fees, 0) as total_fees,
            COALESCE(reg_stats.registration_status, 'غير مسجل') as registration_status,
            COALESCE(reg_stats.status_detail, 'not_registered') as status_detail
            
        FROM students s
        LEFT JOIN colleges c ON c.id = s.college_id
        LEFT JOIN sections sec ON sec.id = s.section_id
        LEFT JOIN programs p ON p.id = s.program_id
        LEFT JOIN batches b ON b.id = s.batch_id
        LEFT JOIN (
            SELECT 
                r.student_id,
                r.semester_id,
                SUM(CASE WHEN r.is_paid = 1 THEN r.payment_amount ELSE 0 END) as total_paid_amount,
                SUM(CASE WHEN r.is_paid = 0 THEN r.payment_amount ELSE 0 END) as total_unpaid_amount,
                SUM(r.study_fees) as total_fees,
                CASE 
                    WHEN COUNT(r.id) = 0 THEN 'غير مسجل'
                    WHEN SUM(CASE WHEN r.is_paid = 0 THEN 1 ELSE 0 END) = COUNT(r.id) THEN 'غير مسجل'
                    WHEN SUM(CASE WHEN r.is_paid = 0 THEN 1 ELSE 0 END) = 0 THEN 'مسجل كاملاً'
                    ELSE 'تسجيل غير مكتمل'
                END as registration_status,
                CASE 
                    WHEN COUNT(r.id) = 0 THEN 'not_registered'
                    WHEN SUM(CASE WHEN r.is_paid = 0 THEN 1 ELSE 0 END) = COUNT(r.id) THEN 'not_paid'
                    WHEN SUM(CASE WHEN r.is_paid = 0 THEN 1 ELSE 0 END) = 0 THEN 'paid'
                    ELSE 'partial'
                END as status_detail,
                MAX(r.created_at) as registration_date
            FROM registrations r
            WHERE r.deleted_at IS NULL
    ";
    
    $bindParams = [];
    $bindTypes = '';
    
    // إضافة فلتر الفصل الدراسي للـ subquery
    if ($filter_semester_id && $filter_semester_id > 0) {
        $sql .= " AND r.semester_id = ? ";
        $bindTypes .= 'i';
        $bindParams[] = $filter_semester_id;
    }
    
    $sql .= "            GROUP BY r.student_id, r.semester_id
        ) reg_stats ON reg_stats.student_id = s.student_id
        LEFT JOIN semesters sem ON sem.id = COALESCE(reg_stats.semester_id, ?)
        $where
    ";
    
    // إضافة بارامتر للـ JOIN مع semesters
    if ($filter_semester_id && $filter_semester_id > 0) {
        $bindTypes .= 'i';
        $bindParams[] = $filter_semester_id;
    } else {
        // إذا لم يكن هناك فصل محدد، نستخدم آخر فصل
        $bindTypes .= 'i';
        $bindParams[] = getCurrentSemesterId();
    }
    
    // إضافة فلتر الحالة - بتصحيح
    if ($status_type === 'paid') {
        $sql .= " AND (reg_stats.status_detail = 'paid' OR reg_stats.status_detail IS NULL) ";
    } elseif ($status_type === 'partial') {
        $sql .= " AND reg_stats.status_detail = 'partial' ";
    } elseif ($status_type === 'not_registered') {
        $sql .= " AND (reg_stats.status_detail IN ('not_registered', 'not_paid') OR reg_stats.status_detail IS NULL) ";
    }
    
    $sql .= " ORDER BY s.student_name, s.student_id ";
    
    // دمج بارامترات WHERE مع بارامترات الفصل الدراسي
    if ($types) {
        $bindTypes = $types . $bindTypes;
        $bindParams = array_merge($params, $bindParams);
    }
    
    return ['sql' => $sql, 'types' => $bindTypes, 'params' => $bindParams];
}

// دالة للحصول على معرف الفصل الحالي
function getCurrentSemesterId() {
    global $mysqli1;
    $sql = "SELECT id FROM semesters WHERE deleted_at IS NULL ORDER BY id DESC LIMIT 1";
    $result = $mysqli1->query($sql);
    if ($row = $result->fetch_assoc()) {
        return $row['id'];
    }
    return 1; // قيمة افتراضية
}

// دالة لتنفيذ الاستعلام والحصول على النتائج
function executeStudentsQuery($queryData) {
    global $mysqli1;
    
    $students = [];
    $total_count = 0;
    $total_paid = 0;
    $total_unpaid = 0;
    $total_fees = 0;
    
    try {
        // استعلام العد
        $count_sql = "SELECT COUNT(*) as total_count FROM (" . $queryData['sql'] . ") as subquery";
        
        $count_stmt = $mysqli1->prepare($count_sql);
        
        if ($queryData['types'] && !empty($queryData['params'])) {
            $count_stmt->bind_param($queryData['types'], ...$queryData['params']);
        }
        
        $count_stmt->execute();
        $count_result = $count_stmt->get_result()->fetch_assoc();
        $total_count = $count_result['total_count'] ?? 0;
        $count_stmt->close();
        
        // إذا كان هناك بيانات، جلبها
        if ($total_count > 0) {
            // جلب البيانات مع الترحيم
            $page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
            $per_page = 50;
            $offset = ($page - 1) * $per_page;
            
            $data_sql = $queryData['sql'] . " LIMIT ? OFFSET ?";
            $data_types = $queryData['types'] . 'ii';
            $data_params = array_merge($queryData['params'], [$per_page, $offset]);
            
            $data_stmt = $mysqli1->prepare($data_sql);
            $data_stmt->bind_param($data_types, ...$data_params);
            $data_stmt->execute();
            $students = $data_stmt->get_result()->fetch_all(MYSQLI_ASSOC);
            $data_stmt->close();
            
            // استعلام الإحصائيات
            $stats_sql = "
                SELECT 
                    COALESCE(SUM(total_paid_amount), 0) as total_paid,
                    COALESCE(SUM(total_unpaid_amount), 0) as total_unpaid,
                    COALESCE(SUM(total_fees), 0) as total_fees
                FROM (" . $queryData['sql'] . ") as subquery
            ";
            
            $stats_stmt = $mysqli1->prepare($stats_sql);
            if ($queryData['types'] && !empty($queryData['params'])) {
                $stats_stmt->bind_param($queryData['types'], ...$queryData['params']);
            }
            $stats_stmt->execute();
            $stats_result = $stats_stmt->get_result()->fetch_assoc();
            $total_paid = $stats_result['total_paid'] ?? 0;
            $total_unpaid = $stats_result['total_unpaid'] ?? 0;
            $total_fees = $stats_result['total_fees'] ?? 0;
            $stats_stmt->close();
        }
        
    } catch (Exception $e) {
        error_log("Error executing students query: " . $e->getMessage());
    }
    
    return [
        'students' => $students,
        'total_count' => $total_count,
        'total_paid' => $total_paid,
        'total_unpaid' => $total_unpaid,
        'total_fees' => $total_fees,
        'per_page' => $per_page,
        'current_page' => isset($page) ? $page : 1
    ];
}

// دالة لاستعلام أبسط في حالة فشل الاستعلام المعقد
function executeSimpleStudentsQuery($where, $types, $params, $status_type) {
    global $mysqli1;
    
    $students = [];
    $total_count = 0;
    
    try {
        // استعلام مبسط للتحقق من البيانات
        $simple_sql = "
            SELECT 
                s.student_id, 
                s.student_name, 
                s.student_name_eng,
                c.name AS college_name
            FROM students s
            LEFT JOIN colleges c ON c.id = s.college_id
            WHERE s.deleted_at IS NULL
        ";
        
        // إضافة فلتر الحالة المبسط
        if ($status_type === 'paid') {
            $simple_sql .= " AND EXISTS (
                SELECT 1 FROM registrations r 
                WHERE r.student_id = s.student_id 
                AND r.deleted_at IS NULL
                AND r.is_paid = 1
                GROUP BY r.student_id
                HAVING COUNT(CASE WHEN r.is_paid = 0 THEN 1 END) = 0
            )";
        }
        
        $simple_sql .= " ORDER BY s.student_name LIMIT 20";
        
        $result = $mysqli1->query($simple_sql);
        if ($result) {
            $students = $result->fetch_all(MYSQLI_ASSOC);
            $total_count = count($students);
        }
    } catch (Exception $e) {
        error_log("Error in simple query: " . $e->getMessage());
    }
    
    return ['students' => $students, 'total_count' => $total_count];
}

// دالة لتصدير البيانات إلى Excel
function exportToExcel($students, $report_title, $filename) {
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $filename . '.xls"');
    header('Cache-Control: max-age=0');
    
    echo '<html dir="rtl">';
    echo '<head><meta charset="UTF-8"></head>';
    echo '<body>';
    echo '<table border="1">';
    echo '<tr><th colspan="15" style="background-color: #f2f2f2; font-size: 16px; padding: 10px;">' . $report_title . '</th></tr>';
    echo '<tr>';
    echo '<th>#</th>';
    echo '<th>الرقم الجامعي</th>';
    echo '<th>اسم الطالب</th>';
    echo '<th>الاسم بالإنجليزية</th>';
    echo '<th>الجنس</th>';
    echo '<th>الهاتف</th>';
    echo '<th>البريد الإلكتروني</th>';
    echo '<th>الكلية</th>';
    echo '<th>القسم</th>';
    echo '<th>البرنامج</th>';
    echo '<th>الدفعة</th>';
    echo '<th>الفصل الدراسي</th>';
    echo '<th>المبلغ المدفوع</th>';
    echo '<th>المبلغ غير المدفوع</th>';
    echo '<th>إجمالي الرسوم</th>';
    echo '<th>حالة التسجيل</th>';
    echo '</tr>';
    
    $counter = 1;
    foreach ($students as $student) {
        echo '<tr>';
        echo '<td>' . $counter++ . '</td>';
        echo '<td>' . htmlspecialchars($student['student_id'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($student['student_name'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($student['student_name_eng'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($student['gender'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($student['phone'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($student['email'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($student['college_name'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($student['section_name'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($student['program_name'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars($student['batch_name'] ?? '') . '</td>';
        echo '<td>' . htmlspecialchars(($student['semester_name'] ?? '') . ' (' . ($student['semester_code'] ?? '') . ')') . '</td>';
        echo '<td>' . number_format($student['total_paid_amount'] ?? 0, 0) . '</td>';
        echo '<td>' . number_format($student['total_unpaid_amount'] ?? 0, 0) . '</td>';
        echo '<td>' . number_format($student['total_fees'] ?? 0, 0) . '</td>';
        echo '<td>' . htmlspecialchars($student['registration_status'] ?? '') . '</td>';
        echo '</tr>';
    }
    
    echo '</table>';
    echo '</body></html>';
    exit;
}

// دالة لعرض معلومات التصحيح
function showDebugInfo($queryData, $result) {
    if (isset($_GET['debug']) && $_GET['debug'] == 1) {
        echo '<div class="alert alert-info mt-3">';
        echo '<h5>معلومات التصحيح:</h5>';
        echo '<strong>SQL Query:</strong><br>';
        echo '<pre style="font-size: 10px; max-height: 200px; overflow: auto;">' . htmlspecialchars($queryData['sql']) . '</pre>';
        echo '<strong>Query Types:</strong> ' . htmlspecialchars($queryData['types']) . '<br>';
        echo '<strong>Query Params:</strong> ' . htmlspecialchars(json_encode($queryData['params'])) . '<br>';
        echo '<strong>Total Students Found:</strong> ' . $result['total_count'] . '<br>';
        echo '</div>';
    }
}

// دالة لاختبار البيانات الأساسية
function testBasicData() {
    global $mysqli1, $filter_semester_id;
    
    echo '<div class="alert alert-warning mt-3">';
    echo '<h5>اختبار البيانات الأساسية:</h5>';
    
    // عدد الطلاب الإجمالي
    $total_students = $mysqli1->query("SELECT COUNT(*) as total FROM students WHERE deleted_at IS NULL")->fetch_assoc();
    echo 'إجمالي الطلاب في النظام: ' . $total_students['total'] . '<br>';
    
    // عدد سجلات التسجيلات
    $total_registrations = $mysqli1->query("SELECT COUNT(*) as total FROM registrations WHERE deleted_at IS NULL")->fetch_assoc();
    echo 'إجمالي سجلات التسجيل: ' . $total_registrations['total'] . '<br>';
    
    // حالة التسجيلات
    echo 'تحليل حالات التسجيل:<br>';
    
    // المسجلين كاملاً
    $paid_query = "
        SELECT COUNT(DISTINCT student_id) as paid_count
        FROM registrations 
        WHERE deleted_at IS NULL
        AND student_id IN (
            SELECT student_id 
            FROM registrations 
            WHERE deleted_at IS NULL
            GROUP BY student_id
            HAVING SUM(CASE WHEN is_paid = 0 THEN 1 ELSE 0 END) = 0
        )
    ";
    $paid_result = $mysqli1->query($paid_query);
    if ($row = $paid_result->fetch_assoc()) {
        echo '• قائمة الطلاب الذين أكملوا سداد الرسوم: ' . $row['paid_count'] . '<br>';
    }
    
    // التسجيلات غير المكتملة
    $partial_query = "
        SELECT COUNT(DISTINCT student_id) as partial_count
        FROM registrations 
        WHERE deleted_at IS NULL
        AND student_id IN (
            SELECT student_id 
            FROM registrations 
            WHERE deleted_at IS NULL
            GROUP BY student_id
            HAVING SUM(CASE WHEN is_paid = 0 THEN 1 ELSE 0 END) > 0
            AND SUM(CASE WHEN is_paid = 1 THEN 1 ELSE 0 END) > 0
        )
    ";
    $partial_result = $mysqli1->query($partial_query);
    if ($row = $partial_result->fetch_assoc()) {
        echo '• الطلاب بالتسجيلات غير المكتملة: ' . $row['partial_count'] . '<br>';
    }
    
    // غير المسجلين
    $not_registered_query = "
        SELECT COUNT(DISTINCT student_id) as not_registered_count
        FROM registrations 
        WHERE deleted_at IS NULL
        AND student_id IN (
            SELECT student_id 
            FROM registrations 
            WHERE deleted_at IS NULL
            GROUP BY student_id
            HAVING SUM(CASE WHEN is_paid = 0 THEN 1 ELSE 0 END) = COUNT(*)
        )
    ";
    $not_registered_result = $mysqli1->query($not_registered_query);
    if ($row = $not_registered_result->fetch_assoc()) {
        echo '• الطلاب غير المسجلين (جميع الدفعيات غير مدفوعة): ' . $row['not_registered_count'] . '<br>';
    }
    
    // الطلاب بدون أي تسجيلات
    $no_reg_query = "
        SELECT COUNT(*) as no_reg_count
        FROM students s
        WHERE s.deleted_at IS NULL
        AND NOT EXISTS (
            SELECT 1 FROM registrations r 
            WHERE r.student_id = s.student_id 
            AND r.deleted_at IS NULL
        )
    ";
    $no_reg_result = $mysqli1->query($no_reg_query);
    if ($row = $no_reg_result->fetch_assoc()) {
        echo '• الطلاب بدون أي تسجيلات: ' . $row['no_reg_count'] . '<br>';
    }
    
    echo '</div>';
}
?>