<?php
// إخفاء الأخطاء لضمان استجابة JSON نظيفة
error_reporting(0);
ini_set('display_errors', 0);
header('Content-Type: application/json; charset=utf-8');

session_start();
require_once 'db_connect.php';

if (!$conn) {
    echo json_encode(['status' => 'error', 'message' => 'فشل الاتصال بقاعدة البيانات']);
    exit;
}

$current_school = isset($_SESSION['school_name']) ? $conn->real_escape_string($_SESSION['school_name']) : '';

// 1. استقبال الفلاتر 
$stage_filter  = isset($_POST['stage']) ? $conn->real_escape_string($_POST['stage']) : '';
$dept_filter   = isset($_POST['dept'])  ? $conn->real_escape_string($_POST['dept'])  : '';
$section_filter= isset($_POST['section'])? $conn->real_escape_string($_POST['section']): '';

// شرط الفلترة الموحد للطلاب
$filter_sql = "";
if (!empty($current_school) && $current_school !== 'مستقل') { $filter_sql .= " AND s.school_name = '$current_school'"; }
if (!empty($stage_filter))   { $filter_sql .= " AND s.stage = '$stage_filter'"; }
if (!empty($dept_filter))    { $filter_sql .= " AND s.department = '$dept_filter'"; }
if (!empty($section_filter)) { $filter_sql .= " AND s.branch = '$section_filter'"; }


// 📊 1. الحسابات الأساسية للطلاب (الإجمالي، الخصم، المستحق، المستلم)
$students_query = "
    SELECT 
        COALESCE(SUM(f.net_total), 0) as total_net_expected,
        COALESCE(SUM(f.total_paid), 0) as total_paid,
        COALESCE(SUM(f.discount_amount), 0) as total_discount
    FROM students s
    LEFT JOIN acc_student_financials f ON s.student_id = f.student_id
    WHERE 1=1 $filter_sql
";
$students_stats = $conn->query($students_query)->fetch_assoc();

$net_expected = floatval($students_stats['total_net_expected']);
$paid = floatval($students_stats['total_paid']);
$discount = floatval($students_stats['total_discount']);
$gross_expected = $net_expected + $discount; // الأقساط الكلية قبل الخصم
$total_debt = $net_expected - $paid;
if ($total_debt < 0) $total_debt = 0;


// 📊 2. الأقساط المؤجلة (تستحق بعد شهر كانون الثاني / يناير من العام الحالي)
$delayed_query = "
    SELECT COALESCE(SUM(i.amount_due), 0) as total_delayed
    FROM acc_installments i
    JOIN acc_student_financials f ON i.financial_id = f.financial_id
    JOIN students s ON f.student_id = s.student_id
    WHERE i.is_paid = 0 AND i.due_date > CONCAT(YEAR(CURDATE()), '-01-31') $filter_sql
";
$delayed_installments = floatval($conn->query($delayed_query)->fetch_assoc()['total_delayed']);


// 📊 3. الوارد الكلي لتاريخ اليوم (محصلات المحاسب اليومية)
$today_query = "
    SELECT COALESCE(SUM(p.amount_paid), 0) as today_income
    FROM acc_payments p
    JOIN acc_student_financials f ON p.financial_id = f.financial_id
    JOIN students s ON f.student_id = s.student_id
    WHERE DATE(p.payment_date) = CURDATE() $filter_sql
";
$today_income = floatval($conn->query($today_query)->fetch_assoc()['today_income']);


// ==========================================
// 🚀 4. بناء خلاصة الأقسام الشاملة (للطباعة والإحصائيات) بشكل ديناميكي 🚀
// ==========================================
$ledger = [];
$ledger_total_in = 0;
$ledger_count_in = 0;
$ledger_total_out = 0;
$ledger_count_out = 0;

$total_expenses_general = 0; // لجمع كافة المصاريف
$total_salaries = 0; // لعزل الرواتب للبطاقات العلوية

// جلب كافة الأقسام والصناديق الخاصة بهذه المدرسة من جدول acc_financial_categories
$cat_query = "SELECT category_name, category_type FROM acc_financial_categories WHERE school_name = '$current_school' OR is_system_default = 1";
$cat_res = $conn->query($cat_query);

if ($cat_res && $cat_res->num_rows > 0) {
    while ($cat = $cat_res->fetch_assoc()) {
        $c_name = $conn->real_escape_string($cat['category_name']);
        $c_type = $cat['category_type']; // 'income' أو 'expense'
        $amt = 0;
        $cnt = 0;

        // أ. إذا كان القسم هو الأقساط الدراسية (يجلب من جدول المدفوعات للطلاب)
        if (strpos($c_name, 'رسوم وأقساط') !== false || $c_name == 'رسوم وأقساط دراسية') {
            // نطبق فلاتر الطلاب على الأقساط لكي يتغير الرقم عند فرز المراحل
            $q = "SELECT SUM(amount_paid) as amt, COUNT(payment_id) as cnt FROM acc_payments p JOIN acc_student_financials f ON p.financial_id = f.financial_id JOIN students s ON f.student_id = s.student_id WHERE 1=1 $filter_sql";
            $res = $conn->query($q)->fetch_assoc();
            $amt = floatval($res['amt']); $cnt = intval($res['cnt']);
        } 
        // ب. إذا كان القسم هو رواتب الكادر (يجلب من جدول الرواتب)
        elseif (strpos($c_name, 'رواتب وأجور') !== false || $c_name == 'رواتب وأجور الكادر') {
            $q = "SELECT SUM(sp.net_paid) as amt, COUNT(sp.sp_id) as cnt FROM acc_salary_payments sp JOIN acc_employee_contracts c ON sp.contract_id = c.contract_id JOIN teachers t ON c.reference_id = t.id WHERE 1=1";
            if (!empty($current_school) && $current_school !== 'مستقل') { $q .= " AND t.school_name = '$current_school'"; }
            $res = $conn->query($q)->fetch_assoc();
            $amt = floatval($res['amt']); $cnt = intval($res['cnt']);
            
            $total_salaries = $amt;
            $total_expenses_general += $amt;
        } 
        // ج. الأقسام الديناميكية الأخرى (تجلب من جدول الصرفيات/الإيرادات العام)
        else {
            // لا نطبق فلاتر المراحل على المصاريف لأنها مصاريف عامة للمدرسة
            $q = "SELECT SUM(amount) as amt, COUNT(expense_id) as cnt FROM acc_expenses WHERE category_name = '$c_name'";
            $res = $conn->query($q)->fetch_assoc();
            $amt = floatval($res['amt']); $cnt = intval($res['cnt']);
            
            if ($c_type == 'expense') { 
                $total_expenses_general += $amt; 
            }
        }

        // تحديد نوع الحركة برمجيًا
        $movement_type = ($c_type == 'income') ? 'in' : 'out';

        $ledger[] = [
    'cat_id' => isset($cat['category_id']) ? $cat['category_id'] : 0, // أضفنا المعرف هنا
    'name' => $cat['category_name'],
    'type' => $movement_type,
    'amount' => $amt,
    'count' => $cnt
];

        // تجميع الفوتر النهائي
        if ($movement_type == 'in') {
            $ledger_total_in += $amt;
            $ledger_count_in += $cnt;
        } else {
            $ledger_total_out += $amt;
            $ledger_count_out += $cnt;
        }
    }
}

// حساب الصافي النهائي
$total_expenses = $total_expenses_general; // (الرواتب + كافة المصاريف)
$treasury_net = $ledger_total_in - $ledger_total_out; // الرصيد النقدي المتوفر (الخزينة)

// حساب الصافي النهائي
$total_expenses = $total_expenses_general; // (الرواتب + كافة المصاريف)
$treasury_net = $ledger_total_in - $ledger_total_out; // الرصيد النقدي المتوفر (الخزينة)


// ==========================================
// 🚀 5. جلب السجل العام لكافة السندات الحقيقية (Vouchers) 🚀
// ==========================================
$all_vouchers = [];

// أ. جلب سندات القبض (من دفعات الطلاب)
$q_payments = "SELECT p.payment_id as id, p.payment_date as date, p.amount_paid as amount, 'رسوم وأقساط دراسية' as cat, 'in' as type, CONCAT('دفعة قسط من الطالب: ', s.student_name) as description, p.received_by as receiver 
               FROM acc_payments p 
               JOIN acc_student_financials f ON p.financial_id = f.financial_id 
               JOIN students s ON f.student_id = s.student_id 
               WHERE 1=1 $filter_sql ORDER BY p.payment_date DESC";
$res_payments = $conn->query($q_payments);
if ($res_payments) {
    while ($row = $res_payments->fetch_assoc()) { $all_vouchers[] = $row; }
}

// ب. جلب سندات الصرف (رواتب الكادر)
$q_salaries_vouchers = "SELECT sp.sp_id as id, sp.payment_date as date, sp.net_paid as amount, 'رواتب وأجور الكادر' as cat, 'out' as type, CONCAT('صرف راتب شهر: ', sp.payment_month) as description, t.display_name as receiver 
                        FROM acc_salary_payments sp 
                        JOIN acc_employee_contracts c ON sp.contract_id = c.contract_id 
                        JOIN teachers t ON c.reference_id = t.id 
                        WHERE 1=1";
if (!empty($current_school) && $current_school !== 'مستقل') { $q_salaries_vouchers .= " AND t.school_name = '$current_school'"; }
$res_salaries = $conn->query($q_salaries_vouchers);
if ($res_salaries) {
    while ($row = $res_salaries->fetch_assoc()) { $all_vouchers[] = $row; }
}

// ج. جلب كافة السندات الأخرى (من جدول acc_expenses)
$q_expenses = "SELECT expense_id as id, expense_date as date, amount, category_name as cat, voucher_type as type, description, recorded_by as receiver 
               FROM acc_expenses 
               WHERE (school_id = (SELECT id FROM schools WHERE school_name='$current_school' LIMIT 1) OR school_id IS NULL) 
               ORDER BY expense_date DESC";
// ملاحظة: تأكد من إضافة عمود voucher_type (in / out) لجدول acc_expenses
$res_expenses = $conn->query($q_expenses);
if ($res_expenses) {
    while ($row = $res_expenses->fetch_assoc()) { 
        // تحويل نوع السند (income/expense) إلى (in/out) ليتطابق مع الواجهة
        if(isset($row['type']) && $row['type'] == 'income') $row['type'] = 'in';
        if(isset($row['type']) && $row['type'] == 'expense') $row['type'] = 'out';
        $all_vouchers[] = $row; 
    }
}

// ترتيب جميع السندات تنازلياً حسب التاريخ
usort($all_vouchers, function($a, $b) {
    return strtotime($b['date']) - strtotime($a['date']);
});


// ==========================================
// 🚀 إرسال الرد المجمع كـ JSON إلى الواجهة
// ==========================================
echo json_encode([
    'status' => 'success',
    'data' => [
        'gross_expected'       => $gross_expected,
        'total_discount'       => $discount,
        'net_expected'         => $net_expected,
        'total_paid'           => $paid,
        'total_debt'           => $total_debt,
        'delayed_installments' => $delayed_installments,
        'today_income'         => $today_income,
        'total_salaries'       => $total_salaries,
        'total_expenses'       => $total_expenses,
        'treasury_net'         => $treasury_net,
        // بيانات التقرير الختامي والمجاميع
        'ledger_total_in'      => $ledger_total_in,
        'ledger_count_in'      => $ledger_count_in,
        'ledger_total_out'     => $ledger_total_out,
        'ledger_count_out'     => $ledger_count_out
    ],
    'ledger' => $ledger
], JSON_UNESCAPED_UNICODE);

exit;
?>
