<?php
header('Content-Type: application/json');
session_start();

require_once('../libs/classes/DbPathsArray.php');
require_once('../libs/classes/App_DB.php');

if (!isset($_SESSION['userid'])) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit;
}

$db = new App_DB(DBU::$dba_user);
$input = json_decode(file_get_contents('php://input'), true);
$action = $_GET['action'] ?? $input['action'] ?? '';

switch ($action) {
    case 'get_fee_types':
        $res = $db->getRecordSet("SELECT * FROM fee_types ORDER BY fee_name ASC");
        echo json_encode(['success' => true, 'data' => $res]);
        break;

    case 'save_fee_type':
        $name = $input['fee_name'];
        $recurring = $input['is_recurring'] ?? 1;
        $id = $input['id'] ?? null;
        if ($id) {
            $db->dbQuery("UPDATE fee_types SET fee_name = ?, is_recurring = ? WHERE id = ?", [$name, $recurring, $id]);
        } else {
            $db->dbQuery("INSERT INTO fee_types (fee_name, is_recurring) VALUES (?, ?)", [$name, $recurring]);
        }
        echo json_encode(['success' => true, 'message' => 'Fee type saved']);
        break;

    case 'get_students':
        $darja = $_GET['darja'] ?? '';
        $start_year = $_GET['start_year'] ?? '';

        if (!$darja || !$start_year) {
            echo json_encode(['success' => false, 'message' => 'Please select both Darja and Starting Year']);
            exit;
        }

        $sql = "SELECT DISTINCT r.sno, r.stdName, r.fatherName, reg.registrationNo
                FROM registrationinfo r
                JOIN stddarjaat st ON st.stdSno = r.sno
                LEFT JOIN regnumbers reg ON r.sno = reg.regSno
                WHERE r.isActive = 1
                  AND st.darja = ?
                  AND st.isCurrent = 1
                  AND YEAR(st.promotionDate) = ?
                ORDER BY r.stdName ASC";
        $res = $db->getRecordSet($sql, [$darja, $start_year]);
        echo json_encode(['success' => true, 'data' => $res]);
        break;

    case 'assign_fee':
        $std_id = $input['student_id'];
        $type_id = $input['fee_type_id'];
        $amount = $input['amount'];
        $due_day = $input['due_day'] ?? 10;
        $fee_month = $input['fee_month'] ?? date('m');
        $fee_year = $input['fee_year'] ?? date('Y');

        $exists = $db->getValue("SELECT 1 FROM student_fee_assignments WHERE student_id = ? AND fee_type_id = ? AND fee_month = ? AND fee_year = ?", [$std_id, $type_id, $fee_month, $fee_year]);

        $db->dbQuery("DELETE FROM student_fee_assignments WHERE student_id = ? AND fee_type_id = ? AND fee_month = ? AND fee_year = ?", [$std_id, $type_id, $fee_month, $fee_year]);
        $db->dbQuery("INSERT INTO student_fee_assignments (student_id, fee_type_id, amount, due_day, fee_month, fee_year) VALUES (?, ?, ?, ?, ?, ?)", [$std_id, $type_id, $amount, $due_day, $fee_month, $fee_year]);

        $msg = 'فیس کامیابی کے ساتھ تفویض کر دی گئی ہے ';
        if ($exists) {
            $msg .= "\n" . 'اس طالب علم کو پہلے ہی یہ فیس تفویض کی جا چکی تھی، ریکارڈ اپڈیٹ کر دیا گیا ہے۔';
        }
        echo json_encode(['success' => true, 'message' => $msg]);
        break;

    case 'assign_fee_bulk':
        $std_ids = $input['student_ids'] ?? [];
        $type_id = $input['fee_type_id'];
        $amount = $input['amount'];
        $due_day = $input['due_day'] ?? 10;
        $fee_month = $input['fee_month'] ?? null;
        $fee_year = $input['fee_year'] ?? null;

        if (empty($std_ids) || !$type_id || !$amount) {
            echo json_encode(['success' => false, 'message' => 'Missing required fields']);
            exit;
        }

        $overwrite_count = 0;
        try {
            $db->beginTransactions();
            foreach ($std_ids as $std_id) {
                // Check if already assigned
                $exists = $db->getValueTrans("SELECT 1 FROM student_fee_assignments WHERE student_id = ? AND fee_type_id = ? AND fee_month = ? AND fee_year = ?", [$std_id, $type_id, $fee_month, $fee_year]);
                if ($exists) {
                    $overwrite_count++;
                }

                // Overwrite existing assignment for same student + fee type + month + year
                $db->dbQueryTrans("DELETE FROM student_fee_assignments WHERE student_id = ? AND fee_type_id = ? AND fee_month = ? AND fee_year = ?", [$std_id, $type_id, $fee_month, $fee_year]);
                // Insert new assigned fee with fee_month and fee_year
                $db->dbQueryTrans("INSERT INTO student_fee_assignments (student_id, fee_type_id, amount, due_day, fee_month, fee_year) VALUES (?, ?, ?, ?, ?, ?)", [$std_id, $type_id, $amount, $due_day, $fee_month, $fee_year]);
            }
            $db->commitTransaction();

            $msg = 'فیس کامیابی کے ساتھ ' . count($std_ids) . ' طلبہ کو تفویض کر دی گئی ہے ';
            if ($overwrite_count > 0) {
                $msg .= "\n" . 'منتخب کردہ طلبہ میں سے کچھ کو پہلے ہی یہ فیس تفویض کی جا چکی ہے، ان کا ریکارڈ اپڈیٹ کر دیا گیا ہے۔';
            }
            echo json_encode(['success' => true, 'message' => $msg]);
        } catch (Exception $e) {
            $db->rollBackTransaction();
            echo json_encode(['success' => false, 'message' => 'فیس تفویض کرنے میں خرابی: ' . $e->getMessage()]);
        }
        break;

    case 'get_assignments':
        $std_id = $_GET['student_id'] ?? '';
        $curr_month = (int) date('m');
        $curr_year = (int) date('Y');

        $sql = "SELECT a.*, a.fee_month, a.fee_year, t.fee_name, r.stdName, r.fatherName, reg.registrationNo 
                FROM student_fee_assignments a 
                JOIN fee_types t ON a.fee_type_id = t.id
                JOIN registrationinfo r ON a.student_id = r.sno
                LEFT JOIN regnumbers reg ON r.sno = reg.regSno
                WHERE a.is_active = 1
                AND a.is_paid = 0";

        $params = [];

        if ($std_id) {
            $sql .= " AND a.student_id = ?";
            $params[] = $std_id;
        }

        $res = $db->getRecordSet($sql, $params);
        echo json_encode(['success' => true, 'data' => $res]);
        break;

    case 'collect_fee_bulk':
        $std_ids = $input['student_ids'] ?? [];
        $type_id = $input['fee_type_id'];
        $amount = $input['amount'];
        $date = $input['payment_date'] ?? date('Y-m-d');
        $method = $input['payment_method'] ?? 'Cash';
        $ref = $input['reference_no'] ?? '';
        $month_year = $input['month_year'] ?? date('m-Y');
        $parts = explode('-', $month_year);
        $fee_month = $parts[0] ?? date('m');
        $fee_year = $parts[1] ?? date('Y');

        if (empty($std_ids) || !$type_id || !$amount) {
            echo json_encode(['success' => false, 'message' => 'Missing required fields']);
            exit;
        }

        // --- NEW: Assignment Check ---
        $missing_assignments = [];
        foreach ($std_ids as $std_id) {
            $assigned = $db->getValue("SELECT 1 FROM student_fee_assignments WHERE student_id = ? AND fee_type_id = ? AND fee_month + 0 = ? + 0 AND fee_year = ? LIMIT 1", [$std_id, $type_id, $fee_month, $fee_year]);
            if (!$assigned) {
                $std_name = $db->getValue("SELECT stdName FROM registrationinfo WHERE sno = ?", [$std_id]);
                $missing_assignments[] = $std_name;
            }
        }
        if (!empty($missing_assignments)) {
            $names = implode(', ', $missing_assignments);
            echo json_encode([
                'success' => false,
                'message' => "منتخب طلبہ کے لیے اس ماہ کی فیس تفویض نہیں کی گئی ہے۔ پہلے ان طلبہ کو فیس تفویض کریں۔\n(طلبہ: $names)"
            ]);
            exit;
        }

        // --- NEW: Duplicate Check (Stop if any student already has a record) ---
        $duplicate_students = [];
        foreach ($std_ids as $std_id) {
            $exists = $db->getValue("SELECT 1 FROM fee_collections WHERE student_id = ? AND fee_type_id = ? AND fee_month = ? AND fee_year = ? LIMIT 1", [$std_id, $type_id, $fee_month, $fee_year]);
            if ($exists) {
                $std_name = $db->getValue("SELECT stdName FROM registrationinfo WHERE sno = ?", [$std_id]);
                $duplicate_students[] = $std_name;
            }
        }

        if (!empty($duplicate_students)) {
            $names = implode(', ', $duplicate_students);
            echo json_encode([
                'success' => false,
                'message' => "منتخب درجہ، سال اور مہینے کے لیے فیس پہلے ہی ریکارڈ کی جا چکی ہے۔\n(طلبہ: $names)"
            ]);
            exit;
        }

        $results = [];
        $success_count = 0;

        foreach ($std_ids as $std_id) {
            try {
                $db->beginTransactions();

                // 1. Insert into fee_collections
                $sqlCol = "INSERT INTO fee_collections (student_id, fee_type_id, amount, payment_date, payment_method, reference_no, fee_month, fee_year) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
                $db->dbQueryTrans($sqlCol, [$std_id, $type_id, $amount, $date, $method, $ref, $fee_month, $fee_year]);
                $collection_id = $db->getLastInsertId();

                // 2. Generate Receipt Record
                $receipt_no = "RCP-" . date('Ymd') . "-" . str_pad($collection_id, 4, '0', STR_PAD_LEFT);
                $qr_data = json_encode(['receipt' => $receipt_no, 'student' => $std_id, 'amount' => $amount, 'date' => $date]);
                $sqlRec = "INSERT INTO fee_receipts (receipt_no, student_id, total_amount, payment_date, qr_code_data) VALUES (?, ?, ?, ?, ?)";
                $db->dbQueryTrans($sqlRec, [$receipt_no, $std_id, $amount, $date, $qr_data]);

                // 3. Automatic Accounting Integration
                $cat_id = $db->getValueTrans("SELECT id FROM accounts_categories WHERE category_name = 'Student Fee' AND category_type = 'income' LIMIT 1");
                if (!$cat_id) {
                    $db->dbQueryTrans("INSERT INTO accounts_categories (category_name, category_type) VALUES ('Student Fee', 'income')");
                    $cat_id = $db->getLastInsertId();
                }

                $std_name = $std_name ?: $db->getValueTrans("SELECT stdName FROM registrationinfo WHERE sno = ?", [$std_id]);
                $fee_name = $fee_name ?: $db->getValueTrans("SELECT fee_name FROM fee_types WHERE id = ?", [$type_id]);
                $desc = "Fee Collection: $fee_name for $std_name ($month_year)";

                $sqlAcc = "INSERT INTO accounts_transactions (txn_date, txn_type, category_id, amount, payment_method, reference_no, description, source_module, reference_id) VALUES (?, 'income', ?, ?, ?, ?, ?, 'student_fee', ?)";
                $db->dbQueryTrans($sqlAcc, [$date, $cat_id, $amount, $method, $receipt_no, $desc, $collection_id]);

                // 4. Mark Assignment as Paid
                $db->dbQueryTrans("UPDATE student_fee_assignments SET is_paid = 1 WHERE student_id = ? AND fee_type_id = ? AND (fee_month IS NULL OR (fee_month + 0 = ? + 0 AND fee_year = ?))", [$std_id, $type_id, $fee_month, $fee_year]);

                $db->commitTransaction();
                $results[] = ['student_id' => $std_id, 'success' => true, 'receipt_no' => $receipt_no];
                $success_count++;
            } catch (Exception $e) {
                $db->rollBackTransaction();
                $results[] = ['student_id' => $std_id, 'success' => false, 'message' => $e->getMessage()];
            }
        }

        echo json_encode([
            'success' => $success_count > 0,
            'message' => "$success_count طلبہ کی فیس کامیابی کے ساتھ جمع کر دی گئی ہے",
            'results' => $results
        ]);
        break;

    case 'collect_fee':
        $std_id = $input['student_id'];
        $type_id = $input['fee_type_id'];
        $amount = $input['amount'];
        $date = $input['payment_date'] ?? date('Y-m-d');
        $method = $input['payment_method'] ?? 'Cash';
        $ref = $input['reference_no'] ?? '';
        $month_year = $input['month_year'] ?? date('m-Y');
        $parts = explode('-', $month_year);
        $fee_month = $parts[0] ?? date('m');
        $fee_year = $parts[1] ?? date('Y');

        // --- NEW: Assignment Check ---
        $assigned = $db->getValue("SELECT 1 FROM student_fee_assignments WHERE student_id = ? AND fee_type_id = ? AND fee_month + 0 = ? + 0 AND fee_year = ? LIMIT 1", [$std_id, $type_id, $fee_month, $fee_year]);
        if (!$assigned) {
            echo json_encode(['success' => false, 'message' => 'اس طالب علم کے لیے اس ماہ کی فیس تفویض نہیں کی گئی ہے۔ پہلے فیس تفویض کریں۔']);
            exit;
        }

        // --- NEW: Duplicate Check ---
        $exists = $db->getValue("SELECT 1 FROM fee_collections WHERE student_id = ? AND fee_type_id = ? AND fee_month = ? AND fee_year = ? LIMIT 1", [$std_id, $type_id, $fee_month, $fee_year]);
        if ($exists) {
            echo json_encode([
                'success' => false,
                'message' => "منتخب درجہ، سال اور مہینے کے لیے فیس پہلے ہی ریکارڈ کی جا چکی ہے۔"
            ]);
            exit;
        }

        try {
            $db->beginTransactions();

            // 1. Insert into fee_collections
            $sqlCol = "INSERT INTO fee_collections (student_id, fee_type_id, amount, payment_date, payment_method, reference_no, fee_month, fee_year) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            $db->dbQueryTrans($sqlCol, [$std_id, $type_id, $amount, $date, $method, $ref, $fee_month, $fee_year]);
            $collection_id = $db->getLastInsertId();

            // 2. Generate Receipt Record
            $receipt_no = "RCP-" . date('Ymd') . "-" . str_pad($collection_id, 4, '0', STR_PAD_LEFT);
            $qr_data = json_encode(['receipt' => $receipt_no, 'student' => $std_id, 'amount' => $amount, 'date' => $date]);
            $sqlRec = "INSERT INTO fee_receipts (receipt_no, student_id, total_amount, payment_date, qr_code_data) VALUES (?, ?, ?, ?, ?)";
            $db->dbQueryTrans($sqlRec, [$receipt_no, $std_id, $amount, $date, $qr_data]);

            // 3. Automatic Accounting Integration (Income Transaction)
            // First, find or create the "Student Fee" category
            $cat_id = $db->getValueTrans("SELECT id FROM accounts_categories WHERE category_name = 'Student Fee' AND category_type = 'income' LIMIT 1");
            if (!$cat_id) {
                $db->dbQueryTrans("INSERT INTO accounts_categories (category_name, category_type) VALUES ('Student Fee', 'income')");
                $cat_id = $db->getLastInsertId();
            }

            $std_name = $db->getValueTrans("SELECT stdName FROM registrationinfo WHERE sno = ?", [$std_id]);
            $fee_name = $db->getValueTrans("SELECT fee_name FROM fee_types WHERE id = ?", [$type_id]);
            $desc = "Fee Collection: $fee_name for $std_name ($month_year)";

            $sqlAcc = "INSERT INTO accounts_transactions (txn_date, txn_type, category_id, amount, payment_method, reference_no, description, source_module, reference_id) VALUES (?, 'income', ?, ?, ?, ?, ?, 'student_fee', ?)";
            $db->dbQueryTrans($sqlAcc, [$date, $cat_id, $amount, $method, $receipt_no, $desc, $collection_id]);

            // 4. Mark Assignment as Paid
            $db->dbQueryTrans("UPDATE student_fee_assignments SET is_paid = 1 WHERE student_id = ? AND fee_type_id = ? AND (fee_month IS NULL OR (fee_month + 0 = ? + 0 AND fee_year = ?))", [$std_id, $type_id, $fee_month, $fee_year]);

            $db->commitTransaction();
            echo json_encode(['success' => true, 'message' => 'Fee collected and account updated', 'receipt_no' => $receipt_no]);
        } catch (Exception $e) {
            $db->rollBackTransaction();
            echo json_encode(['success' => false, 'message' => 'Error processing fee: ' . $e->getMessage()]);
        }
        break;

    case 'get_ledger':
        $std_id = $_GET['student_id'] ?? '';
        if (!$std_id) {
            echo json_encode(['success' => false, 'message' => 'Student ID missing']);
            exit;
        }
        $collections = $db->getRecordSet("SELECT c.*, CONCAT(LPAD(c.fee_month, 2, '0'), '-', c.fee_year) as month_year, t.fee_name FROM fee_collections c JOIN fee_types t ON c.fee_type_id = t.id WHERE c.student_id = ? ORDER BY c.payment_date DESC", [$std_id]);
        $assignments = $db->getRecordSet("SELECT a.*, t.fee_name FROM student_fee_assignments a JOIN fee_types t ON a.fee_type_id = t.id WHERE a.student_id = ?", [$std_id]);
        echo json_encode(['success' => true, 'collections' => $collections, 'assignments' => $assignments]);
        break;

    case 'get_ledger_global':
        $month = $_GET['fee_month'] ?? '';
        $year = $_GET['fee_year'] ?? '';
        $sql = "SELECT c.*, CONCAT(LPAD(c.fee_month, 2, '0'), '-', c.fee_year) as month_year, t.fee_name, r.stdName, r.fatherName 
                FROM fee_collections c 
                JOIN fee_types t ON c.fee_type_id = t.id 
                JOIN registrationinfo r ON c.student_id = r.sno
                WHERE 1=1";
        $params = [];
        if ($month) {
            $sql .= " AND c.fee_month = ?";
            $params[] = $month;
        }
        if ($year) {
            $sql .= " AND c.fee_year = ?";
            $params[] = $year;
        }
        $sql .= " ORDER BY c.payment_date DESC";
        $res = $db->getRecordSet($sql, $params);
        echo json_encode(['success' => true, 'data' => $res]);
        break;

    case 'get_defaulters':
        $month_year = $_GET['month_year'] ?? date('m-Y');
        $parts = explode('-', $month_year);
        $fee_month = (int) ($parts[0] ?? date('m'));
        $fee_year = (int) ($parts[1] ?? date('Y'));

        // Find students who have assignments but NO collection for the given month/year
        $sql = "SELECT a.*, t.fee_name, r.stdName, r.fatherName, reg.registrationNo
                FROM student_fee_assignments a
                JOIN fee_types t ON a.fee_type_id = t.id
                JOIN registrationinfo r ON a.student_id = r.sno
                LEFT JOIN regnumbers reg ON r.sno = reg.regSno
                WHERE a.is_active = :is_active AND a.is_paid = :is_paid 
                ORDER BY a.student_id ASC, a.fee_month ASC";
        $res = $db->getRecordSet($sql, [':is_active' => '1', ':is_paid' => '0']);
        echo json_encode(['success' => true, 'data' => $res]);
        break;

    case 'get_receipt':
        $receipt_no = $_GET['receipt_no'] ?? '';
        if (!$receipt_no) {
            echo json_encode(['success' => false, 'message' => 'Receipt number missing']);
            exit;
        }

        $sql = "SELECT r.*, s.stdName, s.fatherName, reg.registrationNo, c.amount as collection_amount, 
                CONCAT(LPAD(c.fee_month, 2, '0'), '-', c.fee_year) as month_year, t.fee_name
                FROM fee_receipts r
                JOIN registrationinfo s ON r.student_id = s.sno
                LEFT JOIN regnumbers reg ON s.sno = reg.regSno
                JOIN fee_collections c ON c.id = CAST(SUBSTRING_INDEX(r.receipt_no, '-', -1) AS UNSIGNED)
                JOIN fee_types t ON c.fee_type_id = t.id
                WHERE r.receipt_no = ?
                LIMIT 1";

        $res = $db->getRecordFilled($sql, [$receipt_no]);
        if ($res) {
            echo json_encode(['success' => true, 'data' => $res]);
        } else {
            echo json_encode(['success' => false, 'message' => 'Receipt not found']);
        }
        break;

    case 'dashboard_summary':
        // 1. Today's Collection
        $today = date('Y-m-d');
        $today_coll = $db->getValue("SELECT SUM(amount) FROM fee_collections WHERE payment_date = ?", [$today]) ?: 0;

        // 2. This Month's Collection
        $month_curr = date('Y-m-01');
        $month_end = date('Y-m-t');
        $month_coll = $db->getValue("SELECT SUM(amount) FROM fee_collections WHERE payment_date BETWEEN ? AND ?", [$month_curr, $month_end]) ?: 0;

        // 3. Total Students
        $total_students = $db->getValue("SELECT COUNT(sno) FROM registrationinfo WHERE isActive = 1") ?: 0;

        // 4. Defaulters Count for Current Month
        $current_month_year = date('m-Y');
        $parts = explode('-', $current_month_year);
        $curr_month = (int) $parts[0];
        $curr_year = (int) $parts[1];

        $defaulters = $db->getValue("SELECT COUNT(DISTINCT a.id) 
                                     FROM student_fee_assignments a
                                     WHERE a.is_active = 1
                                     AND a.is_paid = 0");

        // 5. Recent Collections (Last 5)
        $recent_sql = "SELECT c.id, c.created_at, c.amount, c.payment_date, r.stdName, c.fee_month, c.fee_year, t.fee_name 
                       FROM fee_collections c 
                       JOIN registrationinfo r ON c.student_id = r.sno 
                       JOIN fee_types t ON c.fee_type_id = t.id
                       ORDER BY r.sno ASC, c.created_at ASC LIMIT 10";
        $recent_collections = $db->getRecordSet($recent_sql);

        echo json_encode([
            'success' => true,
            'data' => [
                'today_collection' => $today_coll,
                'month_collection' => $month_coll,
                'total_students' => $total_students,
                'defaulters_count' => $defaulters,
                'recent_collections' => $recent_collections
            ]
        ]);
        break;

    case 'delete_collection':
        $id = $input['id'] ?? '';
        if (!$id) {
            echo json_encode(['success' => false, 'message' => 'Collection ID missing']);
            exit;
        }

        try {
            $db->beginTransactions();

            // 1. Get receipt_no to delete from receipts and accounts
            $collection = $db->getRecordFilled("SELECT * FROM fee_collections WHERE id = ?", [$id]);
            if (!$collection) {
                throw new Exception("Collection record not found");
            }

            // The receipt number is built using the collection ID, let's find it. (e.g., RCP-YYYYMMDD-ID)
            $receipts = $db->getRecordSet("SELECT receipt_no FROM fee_receipts WHERE student_id = ?", [$collection['student_id']]);
            $targetReceipt = '';
            foreach ($receipts as $r) {
                $parts = explode('-', $r['receipt_no']);
                if (isset($parts[2]) && (int) $parts[2] == $id) {
                    $targetReceipt = $r['receipt_no'];
                    break;
                }
            }

            // 2. Always delete associated financial transaction
            $db->dbQueryTrans("DELETE FROM accounts_transactions WHERE source_module = 'student_fee' AND reference_id = ?", [$id]);

            // 3. Delete receipt if found
            if ($targetReceipt) {
                $db->dbQueryTrans("DELETE FROM fee_receipts WHERE receipt_no = ?", [$targetReceipt]);
            }

            // 3. Delete from fee_collections
            $db->dbQueryTrans("DELETE FROM fee_collections WHERE id = ?", [$id]);

            // 4. Mark Assignment as Unpaid
            $db->dbQueryTrans("UPDATE student_fee_assignments SET is_paid = 0 WHERE student_id = ? AND fee_type_id = ? AND fee_month + 0 = ? + 0 AND fee_year = ?", [$collection['student_id'], $collection['fee_type_id'], $collection['fee_month'], $collection['fee_year']]);

            $db->commitTransaction();
            echo json_encode(['success' => true, 'message' => 'Collection deleted successfully']);
        } catch (Exception $e) {
            $db->rollBackTransaction();
            echo json_encode(['success' => false, 'message' => 'Error deleting collection: ' . $e->getMessage()]);
        }
        break;

    case 'get_collection_by_id':
        $id = $_GET['id'] ?? '';
        if (!$id) {
            echo json_encode(['success' => false, 'message' => 'Collection ID missing']);
            exit;
        }
        $sql = "SELECT c.*, CONCAT(LPAD(c.fee_month, 2, '0'), '-', c.fee_year) as month_year, t.fee_name, r.stdName, r.fatherName 
                FROM fee_collections c 
                JOIN fee_types t ON c.fee_type_id = t.id 
                JOIN registrationinfo r ON c.student_id = r.sno
                WHERE c.id = ?";
        $res = $db->getRecordFilled($sql, [$id]);
        if ($res) {
            echo json_encode(['success' => true, 'data' => $res]);
        } else {
            echo json_encode(['success' => false, 'message' => 'Collection not found']);
        }
        break;

    case 'update_collection':
        $id = $input['id'] ?? '';
        $amount = $input['amount'] ?? '';
        $date = $input['payment_date'] ?? '';
        $method = $input['payment_method'] ?? '';
        $month_year = $input['month_year'] ?? '';

        if (!$id || !$amount) {
            echo json_encode(['success' => false, 'message' => 'Missing required fields']);
            exit;
        }

        try {
            $db->beginTransactions();

            // Setup variables
            $collection = $db->getRecordFilled("SELECT * FROM fee_collections WHERE id = ?", [$id]);
            if (!$collection) {
                throw new Exception("Collection not found");
            }

            // Update fee_collections
            $parts = explode('-', $month_year);
            $fee_month = $parts[0] ?? date('m');
            $fee_year = $parts[1] ?? date('Y');

            $db->dbQueryTrans("UPDATE fee_collections SET amount = ?, payment_date = ?, payment_method = ?, fee_month = ?, fee_year = ? WHERE id = ?", [$amount, $date, $method, $fee_month, $fee_year, $id]);

            // Try to find matching receipt to update total amounts
            $receipts = $db->getRecordSet("SELECT receipt_no FROM fee_receipts WHERE student_id = ?", [$collection['student_id']]);
            $targetReceipt = '';
            foreach ($receipts as $r) {
                $parts = explode('-', $r['receipt_no']);
                if (isset($parts[2]) && (int) $parts[2] == $id) {
                    $targetReceipt = $r['receipt_no'];
                    break;
                }
            }

            if ($targetReceipt) {
                $db->dbQueryTrans("UPDATE fee_receipts SET total_amount = ?, payment_date = ? WHERE receipt_no = ?", [$amount, $date, $targetReceipt]);
            }

            // Always update accounting transaction
            $desc = "Updated Fee Collection for period ($month_year)";
            $db->dbQueryTrans("UPDATE accounts_transactions SET amount = ?, txn_date = ?, description = ? WHERE source_module = 'student_fee' AND reference_id = ?", [$amount, $date, $desc, $id]);

            $db->commitTransaction();
            echo json_encode(['success' => true, 'message' => 'Collection updated successfully']);
        } catch (Exception $e) {
            $db->rollBackTransaction();
            echo json_encode(['success' => false, 'message' => 'Error updating collection: ' . $e->getMessage()]);
        }
        break;

    case 'delete_assignment':
        $id = $input['id'] ?? '';
        if (!$id) {
            echo json_encode(['success' => false, 'message' => 'Missing ID']);
            exit;
        }
        $db->dbQuery("DELETE FROM student_fee_assignments WHERE id = ?", [$id]);
        echo json_encode(['success' => true, 'message' => 'Assignment deleted']);
        break;

    case 'update_assignment':
        $id = $input['id'] ?? '';
        $amount = $input['amount'] ?? '';
        $due_day = $input['due_day'] ?? '';
        $fee_month = $input['fee_month'] ?? null;
        $fee_year = $input['fee_year'] ?? null;
        if (!$id || !$amount || !$due_day) {
            echo json_encode(['success' => false, 'message' => 'Missing fields']);
            exit;
        }
        $db->dbQuery("UPDATE student_fee_assignments SET amount = ?, due_day = ?, fee_month = ?, fee_year = ? WHERE id = ?", [$amount, $due_day, $fee_month, $fee_year, $id]);
        echo json_encode(['success' => true, 'message' => 'Assignment updated']);
        break;

    default:
        echo json_encode(['success' => false, 'message' => 'Invalid action']);
        break;
}
?>