<?php
header('Content-Type: application/json; charset=utf-8');
require_once '../includes/db.php';
require_once '../includes/auth_middleware.php';

$action = isset($_GET['action']) ? $_GET['action'] : '';
$pageKey = 'debts';

function checkDebtorOwnership($debtorId) {
    global $conn;
    if (canViewOthersData()) return;
    
    // If it's a name-based base64 encoded string
    if (is_string($debtorId) && strpos($debtorId, 'name_') === 0) {
        $dname = base64_decode(substr($debtorId, 5));
        $stmt = $conn->prepare("SELECT user_id FROM debtors WHERE TRIM(LOWER(name)) = TRIM(LOWER(?)) LIMIT 1");
        $stmt->bind_param('s', $dname);
    } else {
        $stmt = $conn->prepare("SELECT user_id FROM debtors WHERE id = ?");
        $stmt->bind_param('i', $debtorId);
    }
    $stmt->execute();
    $d_user = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    $myId = (int)($_SESSION['user']['id'] ?? 0);
    if ($d_user && (int)$d_user['user_id'] !== $myId) {
        header('HTTP/1.1 403 Forbidden');
        echo json_encode(['status' => 'error', 'message' => 'عذراً، لا تمتلك الصلاحية للوصول لبيانات هذا المدين.']);
        exit;
    }
}

function checkFormOwnership($formId) {
    global $conn;
    if (canViewOthersData()) return;
    
    $stmt = $conn->prepare("SELECT user_id FROM forms WHERE id = ?");
    $stmt->bind_param('i', $formId);
    $stmt->execute();
    $f_user = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    $myId = (int)($_SESSION['user']['id'] ?? 0);
    if ($f_user && (int)$f_user['user_id'] !== $myId) {
        header('HTTP/1.1 403 Forbidden');
        echo json_encode(['status' => 'error', 'message' => 'عذراً، لا تمتلك الصلاحية للوصول لبيانات هذه الاستمارة.']);
        exit;
    }
}

/**
 * Ensures a debtor is also registered in the customers table automatically if missing.
 */
function ensureCustomerExists($conn, $name, $phone = '') {
    $name = trim($name);
    if (empty($name)) return;
    
    $stmt = $conn->prepare("SELECT id FROM customers WHERE TRIM(LOWER(name)) = TRIM(LOWER(?)) LIMIT 1");
    $stmt->bind_param('s', $name);
    $stmt->execute();
    $cust = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    if (!$cust) {
        $notes = 'تم إضافته تلقائياً من شاشة الديون';
        $status = 'active';
        $stmt_ins = $conn->prepare("INSERT INTO customers (name, phone, notes, status) VALUES (?, ?, ?, ?)");
        $stmt_ins->bind_param('ssss', $name, $phone, $notes, $status);
        $stmt_ins->execute();
        $stmt_ins->close();
        updateSystemState($conn, 'customers');
    }
}

// Enforce granular RBAC for debts API
if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    if (in_array($action, ['list_debtors', 'get_stats', 'get_debtor_history', 'get_print_data'])) {
        checkApiPagePermission($pageKey, 'view');
    }
} elseif ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if ($action === 'save_debtor') {
        // save_debtor can be Add or Edit
        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $id = trim($input['id'] ?? '');
        if (!empty($id)) {
            checkApiPagePermission($pageKey, 'edit');
        } else {
            checkApiPagePermission($pageKey, 'add');
        }
    } elseif ($action === 'save_transaction') {
        // save_transaction is adding a deposit/withdrawal repayment
        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $tx_id = trim($input['id'] ?? '');
        if (!empty($tx_id)) {
            checkApiPagePermission($pageKey, 'edit');
        } else {
            checkApiPagePermission($pageKey, 'add');
        }
    } elseif (in_array($action, ['delete_transaction', 'delete_debtor'])) {
        checkApiPagePermission($pageKey, 'delete');
    }
}

    // =====================================================================
    // GET Requests
    // =====================================================================
    if ($_SERVER['REQUEST_METHOD'] === 'GET') {

        // List all debtors with calculated outstanding balances (including partially paid forms)
        if ($action === 'list_debtors') {
            // Fetch all customer phones from customers table
            $customer_phones = [];
            $cust_res = $conn->query("SELECT name, phone FROM customers WHERE phone IS NOT NULL AND phone != ''");
            if ($cust_res) {
                while ($c = $cust_res->fetch_assoc()) {
                    $c_canonical = normalizeArabicName($c['name']);
                    $customer_phones[$c_canonical] = $c['phone'];
                }
            }

            $name_map = [];

            // 1. Fetch real debtors and their manual balances
            $whereDebtors = "";
            if (!canViewOthersData()) {
                $myId = (int)$_SESSION['user']['id'];
                $whereDebtors = " WHERE d.user_id = $myId ";
            }
            $result = $conn->query("
                SELECT d.id, d.name, d.phone, d.created_at,
                    COALESCE(SUM(CASE WHEN t.type = 'withdrawal' OR t.type = 'debt' THEN t.amount ELSE 0 END), 0) AS manual_debt,
                    COALESCE(SUM(CASE WHEN t.type = 'deposit' OR t.type = 'repayment' THEN t.amount ELSE 0 END), 0) AS manual_repaid,
                    MAX(CASE WHEN t.type = 'deposit' OR t.type = 'repayment' THEN t.created_at ELSE NULL END) AS last_repay_date
                FROM debtors d
                LEFT JOIN safe_transactions t ON d.id = t.debtor_id AND (t.form_id IS NULL OR t.form_id = '' OR t.form_id = '0')
                $whereDebtors
                GROUP BY d.id
            ");
            if ($result) {
                while ($r = $result->fetch_assoc()) {
                    $canonical = normalizeArabicName($r['name']);
                    if (!isset($name_map[$canonical])) {
                        $name_map[$canonical] = [
                            'name' => $r['name'],
                            'phone' => $r['phone'],
                            'createdAt' => $r['created_at'],
                            'manual_balance' => 0.0,
                            'manual_debt' => 0.0,
                            'form_balance' => 0.0,
                            'unpaid_forms_count' => 0,
                            'paid_forms_count' => 0,
                            'real_ids' => [],
                            'total_repaid' => 0.0,
                            'last_payment_date' => null,
                            'items' => []
                        ];
                    }
                    $m_debt = (float)($r['manual_debt'] ?? 0);
                    $m_repaid = (float)($r['manual_repaid'] ?? 0);
                    $m_balance = $m_debt - $m_repaid;

                    $name_map[$canonical]['manual_balance'] += $m_balance;
                    $name_map[$canonical]['manual_debt'] += $m_debt;
                    $name_map[$canonical]['total_repaid'] += $m_repaid;
                    $name_map[$canonical]['real_ids'][] = (string)$r['id'];
                    if (!empty($r['phone'])) {
                        $name_map[$canonical]['phone'] = $r['phone'];
                    }
                    if (empty($name_map[$canonical]['createdAt']) || strcmp($r['created_at'] ?? '', $name_map[$canonical]['createdAt']) > 0) {
                        $name_map[$canonical]['createdAt'] = $r['created_at'] ?? '';
                    }
                    if (!empty($r['last_repay_date'])) {
                        if (empty($name_map[$canonical]['last_payment_date']) ||
                            strcmp($r['last_repay_date'], $name_map[$canonical]['last_payment_date']) > 0) {
                            $name_map[$canonical]['last_payment_date'] = $r['last_repay_date'];
                        }
                    }
                }
            }

            // 2. Fetch active non-cancelled forms and calculate form balances
            $whereForms = "";
            if (!canViewOthersData()) {
                $myId = (int)$_SESSION['user']['id'];
                $whereForms = " AND f.user_id = $myId ";
            }
            $forms_sql = "
                SELECT f.id, f.customer_name, f.created_at, f.grand_total, f.status,
                    COALESCE(paid_sum.paid, 0) AS total_paid,
                    COALESCE(paid_sum.total_deposits, 0) AS total_deposits,
                    paid_sum.last_deposit_date
                FROM forms f
                LEFT JOIN (
                    SELECT form_id,
                        SUM(CASE WHEN type = 'deposit' OR type = 'repayment' THEN amount WHEN type = 'withdrawal' OR type = 'debt' THEN -amount ELSE 0 END) AS paid,
                        SUM(CASE WHEN type = 'deposit' OR type = 'repayment' THEN amount WHEN type = 'withdrawal' OR type = 'debt' THEN -amount ELSE 0 END) AS total_deposits,
                        MAX(CASE WHEN type = 'deposit' OR type = 'repayment' THEN created_at ELSE NULL END) AS last_deposit_date
                    FROM safe_transactions
                    WHERE form_id IS NOT NULL AND form_id != ''
                    GROUP BY form_id
                ) paid_sum ON f.id = paid_sum.form_id
                WHERE f.status != 'cancelled' $whereForms
            ";
            $forms_res = $conn->query($forms_sql);
            if ($forms_res) {
                while ($row = $forms_res->fetch_assoc()) {
                    $canonical = normalizeArabicName($row['customer_name']);
                    if (!isset($name_map[$canonical])) {
                        $name_map[$canonical] = [
                            'name' => $row['customer_name'],
                            'phone' => '',
                            'createdAt' => $row['created_at'] ?? '',
                            'manual_balance' => 0.0,
                            'manual_debt' => 0.0,
                            'form_balance' => 0.0,
                            'unpaid_forms_count' => 0,
                            'paid_forms_count' => 0,
                            'real_ids' => [],
                            'total_repaid' => 0.0,
                            'last_payment_date' => null,
                            'items' => []
                        ];
                    }
                    $form_balance = (float)$row['grand_total'] - (float)$row['total_paid'];
                    $name_map[$canonical]['form_balance'] += $form_balance;
                    if ($form_balance > 0.01) {
                        $name_map[$canonical]['unpaid_forms_count']++;
                    } else {
                        $name_map[$canonical]['paid_forms_count']++;
                    }
                    if (empty($name_map[$canonical]['createdAt']) || strcmp($row['created_at'] ?? '', $name_map[$canonical]['createdAt']) > 0) {
                        $name_map[$canonical]['createdAt'] = $row['created_at'] ?? '';
                    }
                    $name_map[$canonical]['total_repaid'] += (float)($row['total_deposits'] ?? 0);
                    if (!empty($row['last_deposit_date'])) {
                        if (empty($name_map[$canonical]['last_payment_date']) ||
                            strcmp($row['last_deposit_date'], $name_map[$canonical]['last_payment_date']) > 0) {
                            $name_map[$canonical]['last_payment_date'] = $row['last_deposit_date'];
                        }
                    }

                    // Append form item
                    $name_map[$canonical]['items'][] = [
                        'id' => 'form_' . $row['id'],
                        'formId' => (string)$row['id'],
                        'formNumber' => '#' . $row['id'],
                        'formDate' => !empty($row['created_at']) ? substr($row['created_at'], 0, 10) : '',
                        'totalAmount' => (float)$row['grand_total'],
                        'totalPaid' => (float)$row['total_paid'],
                        'remainingAmount' => (float)$form_balance,
                        'isForm' => true,
                        'status' => $row['status'] ?: '0'
                    ];
                }
            }

            // Build list of final grouped debtors
            $debtors = [];
            foreach ($name_map as $canonical => $item) {
                // If customer has unallocated manual repayments (negative manual_balance) AND unpaid forms:
                // allocate/offset unallocated repayments against form items (oldest first)
                if ($item['manual_balance'] < -0.01 && !empty($item['items'])) {
                    $unallocated = abs($item['manual_balance']);
                    foreach ($item['items'] as &$f_item) {
                        if (!empty($f_item['isForm']) && $f_item['remainingAmount'] > 0.01 && $unallocated > 0.01) {
                            $deduct = min($unallocated, $f_item['remainingAmount']);
                            $f_item['totalPaid'] += $deduct;
                            $f_item['remainingAmount'] -= $deduct;
                            $unallocated -= $deduct;
                            if ($f_item['remainingAmount'] <= 0.01) {
                                $f_item['status'] = '1';
                            } else {
                                $f_item['status'] = '2';
                            }
                        }
                    }
                    unset($f_item);
                    
                    if ($unallocated <= 0.01) {
                        $item['manual_balance'] = 0.0;
                    } else {
                        $item['manual_balance'] = -$unallocated;
                    }
                }

                $balance = $item['manual_balance'] + $item['form_balance'];
                
                // Add manual debt item if manual_balance > 0.001
                if ($item['manual_balance'] > 0.001) {
                    $m_total_amt = (float)($item['manual_debt'] > 0 ? $item['manual_debt'] : $item['manual_balance']);
                    $item['items'][] = [
                        'id' => 'manual_' . (!empty($item['real_ids']) ? $item['real_ids'][0] : '0'),
                        'formId' => null,
                        'formNumber' => 'دين يدوي',
                        'formDate' => !empty($item['createdAt']) ? substr($item['createdAt'], 0, 10) : '',
                        'totalAmount' => $m_total_amt,
                        'totalPaid' => (float)($item['total_repaid'] ?? 0),
                        'remainingAmount' => (float)$item['manual_balance'],
                        'isForm' => false,
                        'status' => 'manual'
                    ];
                } else if ($item['manual_balance'] < -0.01) {
                    $item['items'][] = [
                        'id' => 'manual_' . (!empty($item['real_ids']) ? $item['real_ids'][0] : '0'),
                        'formId' => null,
                        'formNumber' => 'رصيد زايد (دائن)',
                        'formDate' => !empty($item['createdAt']) ? substr($item['createdAt'], 0, 10) : '',
                        'totalAmount' => 0,
                        'totalPaid' => (float)($item['total_repaid'] ?? 0),
                        'remainingAmount' => (float)$item['manual_balance'],
                        'isForm' => false,
                        'status' => 'credit'
                    ];
                }

                // If items array is still empty (e.g. only legacy balance or no forms)
                if (empty($item['items'])) {
                    $item['items'][] = [
                        'id' => 'balance_' . (!empty($item['real_ids']) ? $item['real_ids'][0] : '0'),
                        'formId' => null,
                        'formNumber' => 'رصيد سابق',
                        'formDate' => !empty($item['createdAt']) ? substr($item['createdAt'], 0, 10) : '',
                        'totalAmount' => (float)$balance,
                        'totalPaid' => 0.0,
                        'remainingAmount' => (float)$balance,
                        'isForm' => false,
                        'status' => 'balance'
                    ];
                }

                // Resolve phone number if empty
                $phone = $item['phone'];
                if (empty($phone)) {
                    if (isset($customer_phones[$canonical])) {
                        $phone = $customer_phones[$canonical];
                    } else {
                        $phone = 'من سجل الفواتير';
                    }
                }

                $debtors[] = [
                    'id' => 'name_' . base64_encode($item['name']),
                    'isForm' => false,
                    'name' => $item['name'],
                    'phone' => $phone,
                    'balance' => $balance,
                    'createdAt' => $item['createdAt'],
                    'manualBalance' => $item['manual_balance'],
                    'formBalance' => $item['form_balance'],
                    'unpaidFormsCount' => $item['unpaid_forms_count'],
                    'paidFormsCount' => $item['paid_forms_count'],
                    'realIds' => $item['real_ids'],
                    'totalRepaid' => $item['total_repaid'] ?? 0.0,
                    'lastPaymentDate' => $item['last_payment_date'] ?? null,
                    'items' => $item['items']
                ];
            }

            // Sort alphabetically by Customer Name
            usort($debtors, function($a, $b) {
                return strcmp($a['name'], $b['name']);
            });

            echo json_encode(['status' => 'success', 'debtors' => $debtors]);
            exit;
        }

        // Get all transactions for a specific debtor
        if ($action === 'get_transactions') {
            $debtor_id = trim($_GET['debtor_id'] ?? '');
            if (empty($debtor_id)) {
                echo json_encode(['status' => 'error', 'message' => 'معرف المدين مطلوب']);
                exit;
            }
            checkDebtorOwnership($debtor_id);

            $txs = [];
            $has_forms = false;
            $total_cost = 0.0;
            $total_paid = 0.0;

            if (strpos($debtor_id, 'name_') === 0) {
                $name = base64_decode(substr($debtor_id, 5));
                $norm_name = normalizeArabicName($name);

                // 1. Fetch matching real debtors from the database
                $deb_res = $conn->query("SELECT id, name FROM debtors");
                $debtor_ids = [];
                if ($deb_res) {
                    while ($r = $deb_res->fetch_assoc()) {
                        if (normalizeArabicName($r['name']) === $norm_name) {
                            $debtor_ids[] = (string)$r['id'];
                        }
                    }
                }

                // 2. Fetch forms for this customer name first to build a form status map
                $forms_sql = "SELECT * FROM forms WHERE status != 'cancelled'";
                if (!canViewOthersData()) {
                    $myId = (int)$_SESSION['user']['id'];
                    $forms_sql .= " AND user_id = $myId";
                }
                $forms_res = $conn->query($forms_sql);
                $forms = [];
                $form_statuses = [];
                if ($forms_res) {
                    while ($f = $forms_res->fetch_assoc()) {
                        if (normalizeArabicName($f['customer_name']) === $norm_name) {
                            $forms[] = $f;
                            $form_statuses[(string)$f['id']] = $f['status'] ?: '0';
                        }
                    }
                }

                // 3. Fetch manual transactions
                if (!empty($debtor_ids)) {
                    $in_clause = implode(',', array_fill(0, count($debtor_ids), '?'));
                    $stmt = $conn->prepare("SELECT * FROM safe_transactions WHERE debtor_id IN ($in_clause) AND (form_id IS NULL OR form_id = '' OR form_id = '0') ORDER BY date DESC, id DESC");
                    $types = str_repeat('s', count($debtor_ids));
                    $stmt->bind_param($types, ...$debtor_ids);
                    $stmt->execute();
                    $res_tx = $stmt->get_result();
                    while ($r = $res_tx->fetch_assoc()) {
                        $f_id = $r['form_id'];
                        $f_status = '';
                        if (!empty($f_id)) {
                            if (isset($form_statuses[$f_id])) {
                                $f_status = $form_statuses[$f_id];
                            } else {
                                $f_stmt = $conn->prepare("SELECT status FROM forms WHERE id = ?");
                                $f_stmt->bind_param('i', $f_id);
                                $f_stmt->execute();
                                $f_res = $f_stmt->get_result()->fetch_assoc();
                                if ($f_res) {
                                    $f_status = $f_res['status'] ?: '0';
                                    $form_statuses[$f_id] = $f_status;
                                }
                                $f_stmt->close();
                            }
                        }
                        $txs[] = [
                            'id' => (string)$r['id'],
                            'debtorId' => $debtor_id,
                            'safeId' => (string)$r['safe_id'],
                            'description' => $r['description'],
                            'type' => ($r['type'] === 'withdrawal' || $r['type'] === 'debt') ? 'debt' : 'repayment',
                            'date' => $r['date'],
                            'createdAt' => $r['created_at'],
                            'amount' => (float)$r['amount'],
                            'formId' => $f_id,
                            'formStatus' => $f_status
                        ];
                    }
                    $stmt->close();
                }

                if (!empty($forms)) {
                    $has_forms = true;
                    foreach ($forms as $form) {
                        $form_db_id = (string)$form['id'];
                        $grand_total = (float)$form['grand_total'];
                        $total_cost += $grand_total;

                        // Form cost virtual transaction
                        $txs[] = [
                            'id' => 'form_cost_' . $form_db_id,
                            'debtorId' => $debtor_id,
                            'safeId' => '0',
                            'description' => 'قيمة الاستمارة #' . $form_db_id,
                            'type' => 'debt',
                            'date' => date('Y-m-d', strtotime($form['created_at'])),
                            'createdAt' => $form['created_at'],
                            'amount' => $grand_total,
                            'formId' => $form_db_id,
                            'formStatus' => $form['status'] ?: '0'
                        ];

                        // Form payments
                        $stmt = $conn->prepare("SELECT * FROM safe_transactions WHERE form_id = ? AND (type = 'deposit' OR type = 'withdrawal') ORDER BY date DESC, id DESC");
                        $stmt->bind_param('s', $form_db_id);
                        $stmt->execute();
                        $res_pay = $stmt->get_result();
                        while ($r = $res_pay->fetch_assoc()) {
                            $pay_amount = (float)$r['amount'];
                            $is_withdrawal = ($r['type'] === 'withdrawal');
                            if ($is_withdrawal) {
                                $total_paid -= $pay_amount;
                            } else {
                                $total_paid += $pay_amount;
                            }

                            $txs[] = [
                                'id' => (string)$r['id'],
                                'debtorId' => $debtor_id,
                                'safeId' => (string)$r['safe_id'],
                                'description' => $r['description'] . ' (استمارة #' . $form_db_id . ')',
                                'type' => $is_withdrawal ? 'withdrawal' : 'repayment',
                                'date' => $r['date'],
                                'createdAt' => $r['created_at'],
                                'amount' => $pay_amount,
                                'formId' => $form_db_id,
                                'formStatus' => $form['status'] ?: '0'
                            ];
                        }
                        $stmt->close();
                    }
                }

                // Sort all transactions combined
                usort($txs, function($a, $b) {
                    // date descending
                    $cmp = strcmp($b['date'], $a['date']);
                    if ($cmp !== 0) return $cmp;
                    // createdAt descending
                    $cmp = strcmp($b['createdAt'], $a['createdAt']);
                    if ($cmp !== 0) return $cmp;
                    // id descending
                    return strcmp($b['id'], $a['id']);
                });

                echo json_encode([
                    'status' => 'success',
                    'transactions' => $txs,
                    'hasForms' => $has_forms,
                    'formsSummary' => [
                        'totalCost' => $total_cost,
                        'totalPaid' => $total_paid,
                        'remaining' => $total_cost - $total_paid
                    ]
                ]);
                exit;
            } else {
                // Keep original behavior as fallback (just in case single form ID is requested)
                if (strpos($debtor_id, 'form_') === 0) {
                    $form_db_id = substr($debtor_id, 5);
                    $form_stmt = $conn->prepare("SELECT * FROM forms WHERE id = ?");
                    $form_stmt->bind_param('i', $form_db_id);
                    $form_stmt->execute();
                    $form = $form_stmt->get_result()->fetch_assoc();
                    $form_stmt->close();
                    if (!$form) {
                        echo json_encode(['status' => 'error', 'message' => 'الاستمارة غير موجودة']);
                        exit;
                    }
                    $grand_total = (float)$form['grand_total'];
                    $txs = [];
                    $txs[] = [
                        'id' => 'form_cost_' . $form_db_id,
                        'debtorId' => $debtor_id,
                        'safeId' => '0',
                        'description' => 'إجمالي قيمة الاستمارة المستحقة',
                        'type' => 'debt',
                        'date' => date('Y-m-d', strtotime($form['created_at'])),
                        'createdAt' => $form['created_at'],
                        'amount' => $grand_total,
                        'isFormCost' => true,
                        'formId' => $form_db_id,
                        'formStatus' => $form['status'] ?: '0'
                    ];
                    $stmt = $conn->prepare("SELECT * FROM safe_transactions WHERE form_id = ? AND (type = 'deposit' OR type = 'withdrawal') ORDER BY date DESC, id DESC");
                    $stmt->bind_param('s', $form_db_id);
                    $stmt->execute();
                    $res = $stmt->get_result();
                    while ($r = $res->fetch_assoc()) {
                        $is_withdrawal = ($r['type'] === 'withdrawal');
                        $txs[] = [
                            'id' => (string)$r['id'],
                            'debtorId' => $debtor_id,
                            'safeId' => (string)$r['safe_id'],
                            'description' => $r['description'],
                            'type' => $is_withdrawal ? 'withdrawal' : 'repayment',
                            'date' => $r['date'],
                            'createdAt' => $r['created_at'],
                            'amount' => (float)$r['amount'],
                            'formId' => $form_db_id,
                            'formStatus' => $form['status'] ?: '0'
                        ];
                    }
                    $stmt->close();
                    echo json_encode(['status' => 'success', 'transactions' => $txs]);
                    exit;
                } else {
                    $stmt = $conn->prepare("SELECT * FROM safe_transactions WHERE debtor_id = ? ORDER BY date DESC, id DESC");
                    $stmt->bind_param('s', $debtor_id);
                    $stmt->execute();
                    $res = $stmt->get_result();
                    $txs = [];
                    $form_statuses = [];
                    while ($r = $res->fetch_assoc()) {
                        $f_id = $r['form_id'];
                        $f_status = '';
                        if (!empty($f_id)) {
                            if (isset($form_statuses[$f_id])) {
                                $f_status = $form_statuses[$f_id];
                            } else {
                                $f_stmt = $conn->prepare("SELECT status FROM forms WHERE id = ?");
                                $f_stmt->bind_param('i', $f_id);
                                $f_stmt->execute();
                                $f_res = $f_stmt->get_result()->fetch_assoc();
                                if ($f_res) {
                                    $f_status = $f_res['status'] ?: '0';
                                    $form_statuses[$f_id] = $f_status;
                                }
                                $f_stmt->close();
                            }
                        }
                        $txs[] = [
                            'id' => (string)$r['id'],
                            'debtorId' => (string)$r['debtor_id'],
                            'safeId' => (string)$r['safe_id'],
                            'description' => $r['description'],
                            'type' => ($r['type'] === 'withdrawal' || $r['type'] === 'debt') ? 'debt' : 'repayment',
                            'date' => $r['date'],
                            'createdAt' => $r['created_at'],
                            'amount' => (float)$r['amount'],
                            'formId' => $f_id,
                            'formStatus' => $f_status
                        ];
                    }
                    $stmt->close();
                    echo json_encode(['status' => 'success', 'transactions' => $txs]);
                    exit;
                }
            }
        }

        // Get statistics summary (Outstanding total, active debtors, total repayments)
        if ($action === 'get_stats') {
            $name_map = [];

            // 1. Fetch real debtors and their manual balances
            $whereDebtors = "";
            if (!canViewOthersData()) {
                $myId = (int)$_SESSION['user']['id'];
                $whereDebtors = " WHERE d.user_id = $myId ";
            }
            $result = $conn->query("
                SELECT d.id, d.name, d.phone, d.created_at,
                    COALESCE(SUM(CASE WHEN t.type = 'withdrawal' OR t.type = 'debt' THEN t.amount ELSE 0 END), 0) -
                    COALESCE(SUM(CASE WHEN t.type = 'deposit' OR t.type = 'repayment' THEN t.amount ELSE 0 END), 0) AS manual_balance
                FROM debtors d
                LEFT JOIN safe_transactions t ON d.id = t.debtor_id AND (t.form_id IS NULL OR t.form_id = '' OR t.form_id = '0')
                $whereDebtors
                GROUP BY d.id
            ");
            if ($result) {
                while ($r = $result->fetch_assoc()) {
                    $canonical = normalizeArabicName($r['name']);
                    if (!isset($name_map[$canonical])) {
                        $name_map[$canonical] = [
                            'name' => $r['name'],
                            'manual_balance' => 0.0,
                            'form_balance' => 0.0
                        ];
                    }
                    $name_map[$canonical]['manual_balance'] += (float)$r['manual_balance'];
                }
            }

            // 2. Fetch active non-cancelled forms and calculate form balances
            $whereForms = "";
            if (!canViewOthersData()) {
                $myId = (int)$_SESSION['user']['id'];
                $whereForms = " AND f.user_id = $myId ";
            }
            $forms_sql = "
                SELECT f.id, f.customer_name, f.created_at, f.grand_total,
                    COALESCE(paid_sum.paid, 0) AS total_paid
                FROM forms f
                LEFT JOIN (
                    SELECT form_id, SUM(CASE WHEN type = 'deposit' THEN amount WHEN type = 'withdrawal' THEN -amount ELSE 0 END) AS paid
                    FROM safe_transactions
                    WHERE form_id IS NOT NULL AND form_id != ''
                    GROUP BY form_id
                ) paid_sum ON f.id = paid_sum.form_id
                WHERE f.status != 'cancelled' $whereForms
            ";
            $forms_res = $conn->query($forms_sql);
            if ($forms_res) {
                while ($row = $forms_res->fetch_assoc()) {
                    $canonical = normalizeArabicName($row['customer_name']);
                    if (!isset($name_map[$canonical])) {
                        $name_map[$canonical] = [
                            'name' => $row['customer_name'],
                            'manual_balance' => 0.0,
                            'form_balance' => 0.0
                        ];
                    }
                    $form_balance = (float)$row['grand_total'] - (float)$row['total_paid'];
                    $name_map[$canonical]['form_balance'] += $form_balance;
                }
            }

            // Calculate stats
            $total_outstanding = 0.0;
            $active_debtors = 0;
            foreach ($name_map as $item) {
                $balance = $item['manual_balance'] + $item['form_balance'];
                if ($balance > 0.01) {
                    $total_outstanding += $balance;
                    $active_debtors++;
                }
            }

            // Total Repayments (Sum of all safe deposits/repayments for debtors and forms)
            $whereTxs = "";
            if (!canViewOthersData()) {
                $myId = (int)$_SESSION['user']['id'];
                $whereTxs = " AND user_id = $myId ";
            }
            $rep_res = $conn->query("
                SELECT SUM(CASE WHEN type = 'deposit' OR type = 'repayment' THEN amount ELSE 0 END) AS total_repayments 
                FROM safe_transactions 
                WHERE (
                    (debtor_id IS NOT NULL AND debtor_id != '') OR 
                    (form_id IS NOT NULL AND form_id != '')
                ) $whereTxs
            ");
            $rep_data = $rep_res ? $rep_res->fetch_assoc() : null;
            $total_repayments = (float)($rep_data['total_repayments'] ?? 0);

            // Shift Debt Repayments (what was collected during the active shift for previous or current debts)
            $shift = getSystemShiftRange($conn);
            $shift_start = $shift['start'];
            $shift_end   = $shift['end'];

            $shift_rep_res = $conn->query("
                SELECT SUM(CASE WHEN type = 'deposit' OR type = 'repayment' THEN amount ELSE 0 END) AS shift_repayments 
                FROM safe_transactions 
                WHERE (
                    (debtor_id IS NOT NULL AND debtor_id != '') OR 
                    (form_id IS NOT NULL AND form_id != '')
                ) AND created_at >= '$shift_start' AND created_at < '$shift_end' $whereTxs
            ");
            $shift_rep_data = $shift_rep_res ? $shift_rep_res->fetch_assoc() : null;
            $shift_repayments = (float)($shift_rep_data['shift_repayments'] ?? 0);

            echo json_encode([
                'status' => 'success',
                'stats' => [
                    'totalOutstanding' => $total_outstanding,
                    'activeDebtors' => $active_debtors,
                    'totalRepayments' => $total_repayments,
                    'shiftRepayments' => $shift_repayments
                ]
            ]);
            exit;
        }

        // Get all outstanding debts for printingسسسس
        if ($action === 'get_print_data') {
            $debtors_out = [];

            // ─── 1. Real debtors with manual transactions ───────────────────
            $res = $conn->query("
                SELECT d.id, d.name, d.phone,
                    t.id AS txn_id, t.description, t.type, t.amount, t.date, t.form_id
                FROM debtors d
                LEFT JOIN safe_transactions t ON d.id = t.debtor_id
                WHERE t.form_id IS NULL OR t.form_id = '' OR t.form_id = '0'
                ORDER BY d.name, t.date ASC
            ");
            $debtor_manual = [];
            if ($res) {
                while ($r = $res->fetch_assoc()) {
                    $key = normalizeArabicName($r['name']);
                    if (!isset($debtor_manual[$key])) {
                        $debtor_manual[$key] = ['name' => $r['name'], 'phone' => $r['phone'], 'balance' => 0.0, 'txns' => []];
                    }
                    if ($r['txn_id']) {
                        $amt = (float)$r['amount'];
                        if ($r['type'] === 'withdrawal' || $r['type'] === 'debt') {
                            $debtor_manual[$key]['balance'] += $amt;
                        } else {
                            $debtor_manual[$key]['balance'] -= $amt;
                        }
                        $debtor_manual[$key]['txns'][] = [
                            'type' => $r['type'],
                            'description' => $r['description'],
                            'amount' => $amt,
                            'date' => $r['date']
                        ];
                    }
                }
            }
            // Only keep debtors with positive balance
            foreach ($debtor_manual as $key => $d) {
                if ($d['balance'] > 0.01) {
                    $items = $d['txns'];
                    if (empty($items)) {
                        $items[] = [
                            'type' => 'debt',
                            'description' => 'دين يدوي',
                            'amount' => (float)$d['balance'],
                            'remaining' => (float)$d['balance'],
                            'date' => '—'
                        ];
                    }
                    $debtors_out[$key] = [
                        'name' => $d['name'],
                        'phone' => $d['phone'],
                        'type' => 'manual',
                        'balance' => (float)$d['balance'],
                        'items' => $items
                    ];
                }
            }

            // ─── 2. Form-based debtors ──────────────────────────────────────
            $form_res = $conn->query("
                SELECT f.id AS form_id, f.customer_name, f.grand_total, f.created_at,
                    COALESCE(p.paid, 0) AS total_paid
                FROM forms f
                LEFT JOIN (
                    SELECT form_id,
                        SUM(CASE WHEN type='deposit' OR type='repayment' THEN amount WHEN type='withdrawal' OR type='debt' THEN -amount ELSE 0 END) AS paid
                    FROM safe_transactions WHERE form_id IS NOT NULL AND form_id != ''
                    GROUP BY form_id
                ) p ON f.id = p.form_id
                WHERE f.status != 'cancelled'
                ORDER BY f.customer_name, f.created_at ASC
            ");
            if ($form_res) {
                while ($f = $form_res->fetch_assoc()) {
                    $remaining = (float)$f['grand_total'] - (float)$f['total_paid'];
                    if ($remaining <= 0.01) continue;

                    $key = normalizeArabicName($f['customer_name']);

                    // Merge with manual debtor if same name
                    if (!isset($debtors_out[$key])) {
                        $debtors_out[$key] = [
                            'name' => $f['customer_name'],
                            'phone' => '',
                            'type' => 'form',
                            'balance' => 0.0,
                            'items' => []
                        ];
                    }
                    $debtors_out[$key]['balance'] += $remaining;
                    $debtors_out[$key]['items'][] = [
                        'formId' => $f['form_id'],
                        'grandTotal' => (float)$f['grand_total'],
                        'totalPaid' => (float)$f['total_paid'],
                        'remaining' => $remaining,
                        'date' => substr($f['created_at'], 0, 10),
                        'type' => 'form'
                    ];
                }
            }


            // Sort by name
            usort($debtors_out, fn($a, $b) => strcmp($a['name'], $b['name']));

            echo json_encode(['status' => 'success', 'debtors' => array_values($debtors_out)]);
            exit;
        }
    }



    // =====================================================================
    // POST Requests
    // =====================================================================
    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
        $input = json_decode(file_get_contents('php://input'), true) ?? [];

        // Save Debtor (Add or Edit)
        if ($action === 'save_debtor') {
            $id     = trim($input['id'] ?? '');
            $name   = trim($input['name'] ?? '');
            $phone  = trim($input['phone'] ?? '');
            $amount = (float)($input['amount'] ?? 0);

            if (empty($name)) {
                echo json_encode(['status' => 'error', 'message' => 'اسم المدين مطلوب']);
                exit;
            }

            // Ensure debtor is automatically added to customers table if missing
            ensureCustomerExists($conn, $name, $phone);

            if (strpos($id, 'name_') === 0) {
                $old_name = base64_decode(substr($id, 5));
                
                // Find real debtors matching old name
                $stmt = $conn->prepare("SELECT id FROM debtors WHERE TRIM(LOWER(name)) = TRIM(LOWER(?))");
                $stmt->bind_param('s', $old_name);
                $stmt->execute();
                $deb_rows = $stmt->get_result();
                $stmt->close();
                
                $ids = [];
                while ($r = $deb_rows->fetch_assoc()) {
                    $ids[] = $r['id'];
                }
                
                if (!empty($ids)) {
                    $in_clause = implode(',', array_map('intval', $ids));
                    $stmt = $conn->prepare("UPDATE debtors SET name = ?, phone = ? WHERE id IN ($in_clause)");
                    $stmt->bind_param('ss', $name, $phone);
                    $stmt->execute();
                    $stmt->close();
                } else {
                    // If they don't exist in debtors yet, insert new debtor
                    $myUserId = isset($_SESSION['user']['id']) ? (int)$_SESSION['user']['id'] : 1;
                    $stmt = $conn->prepare("INSERT INTO debtors (name, phone, user_id) VALUES (?, ?, ?)");
                    $stmt->bind_param('ssi', $name, $phone, $myUserId);
                    $stmt->execute();
                    $inserted_id = $conn->insert_id;
                    $stmt->close();

                    if ($amount > 0 && $inserted_id) {
                        $debtor_id_str = (string)$inserted_id;
                        $stmt_tx = $conn->prepare("INSERT INTO safe_transactions (safe_id, description, type, date, amount, debtor_id, user_id) VALUES (1, 'تسجيل دين مبدئي على الزبون', 'debt', CURDATE(), ?, ?, ?)");
                        $stmt_tx->bind_param('dsi', $amount, $debtor_id_str, $myUserId);
                        $stmt_tx->execute();
                        $stmt_tx->close();
                        updateSystemState($conn, 'safe_transactions');
                    }
                }
                
                // Sync customers table
                $stmt = $conn->prepare("UPDATE customers SET name = ?, phone = ? WHERE TRIM(LOWER(name)) = TRIM(LOWER(?))");
                $stmt->bind_param('sss', $name, $phone, $old_name);
                $stmt->execute();
                $stmt->close();

                // Sync forms table customer_name
                $stmt = $conn->prepare("UPDATE forms SET customer_name = ? WHERE TRIM(LOWER(customer_name)) = TRIM(LOWER(?))");
                $stmt->bind_param('ss', $name, $old_name);
                $stmt->execute();
                $stmt->close();
                
                updateSystemState($conn, 'debtors');
                updateSystemState($conn, 'forms');

                echo json_encode(['status' => 'success', 'message' => 'تم تحديث بيانات المدين بنجاح']);
                exit;
            }

            if (!empty($id)) {
                // Edit standard ID
                $old_res = $conn->query("SELECT * FROM debtors WHERE id = " . intval($id));
                $old_debtor = $old_res ? $old_res->fetch_assoc() : null;

                $stmt = $conn->prepare("UPDATE debtors SET name = ?, phone = ? WHERE id = ?");
                $stmt->bind_param('ssi', $name, $phone, $id);
                if ($stmt->execute()) {
                    $new_res = $conn->query("SELECT * FROM debtors WHERE id = " . intval($id));
                    $new_debtor = $new_res ? $new_res->fetch_assoc() : null;
                    logActivity($conn, 'update', 'debtors', $id, $old_debtor, $new_debtor);
                    updateSystemState($conn, 'debtors');
                    echo json_encode(['status' => 'success', 'message' => 'تم تحديث بيانات المدين بنجاح']);
                } else {
                    echo json_encode(['status' => 'error', 'message' => 'فشل تحديث بيانات المدين']);
                }
                $stmt->close();
            } else {
                // Check if a debtor with this name already exists
                $stmt_check = $conn->prepare("SELECT id FROM debtors WHERE TRIM(LOWER(name)) = TRIM(LOWER(?)) LIMIT 1");
                $stmt_check->bind_param('s', $name);
                $stmt_check->execute();
                $exist = $stmt_check->get_result()->fetch_assoc();
                $stmt_check->close();

                $myUserId = isset($_SESSION['user']['id']) ? (int)$_SESSION['user']['id'] : 1;

                if ($exist) {
                    $new_id = (int)$exist['id'];
                } else {
                    // Insert standard ID
                    $stmt = $conn->prepare("INSERT INTO debtors (name, phone, user_id) VALUES (?, ?, ?)");
                    $stmt->bind_param('ssi', $name, $phone, $myUserId);
                    if ($stmt->execute()) {
                        $new_id = $conn->insert_id;
                        $new_res = $conn->query("SELECT * FROM debtors WHERE id = " . intval($new_id));
                        $new_debtor = $new_res ? $new_res->fetch_assoc() : null;
                        logActivity($conn, 'insert', 'debtors', $new_id, null, $new_debtor);
                    } else {
                        echo json_encode(['status' => 'error', 'message' => 'فشل إضافة المدين']);
                        exit;
                    }
                    $stmt->close();
                }

                // Record initial debt transaction if amount > 0
                if ($amount > 0) {
                    $debtor_id_str = (string)$new_id;
                    $stmt_tx = $conn->prepare("INSERT INTO safe_transactions (safe_id, description, type, date, amount, debtor_id, user_id) VALUES (1, 'تسجيل دين مبدئي على الزبون', 'debt', CURDATE(), ?, ?, ?)");
                    $stmt_tx->bind_param('dsi', $amount, $debtor_id_str, $myUserId);
                    $stmt_tx->execute();
                    $stmt_tx->close();
                    updateSystemState($conn, 'safe_transactions');
                }

                updateSystemState($conn, 'debtors');
                echo json_encode(['status' => 'success', 'message' => 'تم إضافة المدين والدين بنجاح']);
            }
            exit;
        }

        // Delete Debtor
        if ($action === 'delete_debtor') {
            $id = trim($input['id'] ?? '');
            if (empty($id)) {
                echo json_encode(['status' => 'error', 'message' => 'معرف المدين مطلوب']);
                exit;
            }
            checkDebtorOwnership($id);

            if (strpos($id, 'name_') === 0) {
                $name = base64_decode(substr($id, 5));
                $stmt = $conn->prepare("SELECT id FROM debtors WHERE TRIM(LOWER(name)) = TRIM(LOWER(?))");
                $stmt->bind_param('s', $name);
                $stmt->execute();
                $res = $stmt->get_result();
                $ids = [];
                while ($r = $res->fetch_assoc()) {
                    $ids[] = $r['id'];
                }
                $stmt->close();
                
                if (!empty($ids)) {
                    $conn->begin_transaction();
                    try {
                        $in_clause = implode(',', array_map('intval', $ids));
                        $conn->query("DELETE FROM safe_transactions WHERE debtor_id IN ($in_clause)");
                        $conn->query("DELETE FROM debtors WHERE id IN ($in_clause)");
                        updateSystemState($conn, 'debtors');
                        updateSystemState($conn, 'safe_transactions');
                        $conn->commit();
                    } catch (Exception $e) {
                        $conn->rollback();
                        echo json_encode(['status' => 'error', 'message' => 'فشلت العملية: ' . $e->getMessage()]);
                        exit;
                    }
                }
                echo json_encode(['status' => 'success', 'message' => 'تم حذف المدين وكافة حركاته بنجاح']);
                exit;
            }

            if (strpos($id, 'form_') === 0) {
                echo json_encode(['status' => 'error', 'message' => 'لا يمكن حذف استمارات الشراء من هنا، يرجى حذفها أو إلغاؤها من صفحة الاستمارات']);
                exit;
            }

            $old_res = $conn->query("SELECT * FROM debtors WHERE id = " . intval($id));
            $old_debtor = $old_res ? $old_res->fetch_assoc() : null;

            $conn->begin_transaction();
            try {
                // Delete all transactions of this debtor in safe_transactions
                $stmt_tx = $conn->prepare("DELETE FROM safe_transactions WHERE debtor_id = ?");
                $stmt_tx->bind_param('s', $id);
                $stmt_tx->execute();
                $stmt_tx->close();

                // Delete debtor
                $stmt_deb = $conn->prepare("DELETE FROM debtors WHERE id = ?");
                $stmt_deb->bind_param('i', $id);
                $stmt_deb->execute();
                $stmt_deb->close();

                logActivity($conn, 'delete', 'debtors', $id, $old_debtor, null);
                updateSystemState($conn, 'debtors');
                updateSystemState($conn, 'safe_transactions');

                $conn->commit();
                echo json_encode(['status' => 'success', 'message' => 'تم حذف المدين وكافة حركاته بنجاح']);
            } catch (Exception $e) {
                $conn->rollback();
                echo json_encode(['status' => 'error', 'message' => 'فشلت العملية: ' . $e->getMessage()]);
            }
            exit;
        }

         // Save Transaction
        if ($action === 'save_transaction') {
            $debtor_id   = trim($input['debtorId'] ?? '');
            $safe_id     = (int)($input['safeId'] ?? 0);
            $description = trim($input['description'] ?? '');
            $type        = trim($input['type'] ?? ''); // 'debt' or 'repayment'
            $date        = trim($input['date'] ?? '');
            $amount      = (float)($input['amount'] ?? 0);

            if (empty($safe_id) || $safe_id <= 0) {
                $safe_id = 1; // Default to primary safe to satisfy DB foreign key constraint
            }

            if (empty($debtor_id) || empty($description) || empty($type) || empty($date) || $amount <= 0) {
                echo json_encode(['status' => 'error', 'message' => 'بيانات المعاملة غير مكتملة']);
                exit;
            }
            checkDebtorOwnership($debtor_id);

            if (strpos($debtor_id, 'form_') === 0) {
                echo json_encode(['status' => 'error', 'message' => 'لا يمكن إضافة حركات يدوية لاستمارات الشراء من هنا، يرجى إضافتها من صفحة الاستمارات']);
                exit;
            }

            $name = '';
            if (strpos($debtor_id, 'name_') === 0) {
                $name = base64_decode(substr($debtor_id, 5));
                
                // Check if there is an existing debtor with this name
                $stmt = $conn->prepare("SELECT id FROM debtors WHERE TRIM(LOWER(name)) = TRIM(LOWER(?)) LIMIT 1");
                $stmt->bind_param('s', $name);
                $stmt->execute();
                $deb_row = $stmt->get_result()->fetch_assoc();
                $stmt->close();
                
                if ($deb_row) {
                    $real_debtor_id = $deb_row['id'];
                } else {
                    // Fetch phone from customers
                    $phone = '';
                    $stmt = $conn->prepare("SELECT phone FROM customers WHERE TRIM(LOWER(name)) = TRIM(LOWER(?)) LIMIT 1");
                    $stmt->bind_param('s', $name);
                    $stmt->execute();
                    $c_row = $stmt->get_result()->fetch_assoc();
                    $stmt->close();
                    if ($c_row && !empty($c_row['phone'])) {
                        $phone = $c_row['phone'];
                    } else {
                        $phone = 'من سجل الفواتير'; // Fallback
                    }
                    
                    // Create new debtor
                    $stmt = $conn->prepare("INSERT INTO debtors (name, phone, user_id) VALUES (?, ?, ?)");
                    $stmt->bind_param('ssi', $name, $phone, $_SESSION['user']['id']);
                    $stmt->execute();
                    $real_debtor_id = $conn->insert_id;
                    $stmt->close();
                }
                
                $debtor_id = (string)$real_debtor_id;
            } else {
                // Find name from debtor ID
                $stmt = $conn->prepare("SELECT name FROM debtors WHERE id = ?");
                $stmt->bind_param('i', $debtor_id);
                $stmt->execute();
                $deb_row = $stmt->get_result()->fetch_assoc();
                $stmt->close();
                if ($deb_row) {
                    $name = $deb_row['name'];
                }
            }

            if (!empty($name)) {
                ensureCustomerExists($conn, $name);
            }

            if ($type === 'repayment') {
                // Calculate total debt for this debtor to prevent overpayment and negative balance
                $debtor_name_to_check = $name;
                if (empty($debtor_name_to_check) && !empty($debtor_id)) {
                    $d_res = $conn->query("SELECT name FROM debtors WHERE id = '" . intval($debtor_id) . "'");
                    if ($d_res && $d_row = $d_res->fetch_assoc()) {
                        $debtor_name_to_check = $d_row['name'];
                    }
                }

                $total_debt = 0.0;
                if (!empty($debtor_name_to_check)) {
                    $r_m = $conn->query("
                        SELECT COALESCE(SUM(CASE WHEN t.type = 'withdrawal' OR t.type = 'debt' THEN t.amount ELSE -t.amount END), 0) AS m_bal
                        FROM safe_transactions t
                        INNER JOIN debtors d ON t.debtor_id = d.id
                        WHERE (t.form_id IS NULL OR t.form_id = '' OR t.form_id = '0')
                        AND TRIM(LOWER(d.name)) = TRIM(LOWER('" . $conn->real_escape_string($debtor_name_to_check) . "'))
                    ");
                    if ($r_m && $row_m = $r_m->fetch_assoc()) {
                        $total_debt += max(0.0, (float)$row_m['m_bal']);
                    }

                    $f_res = $conn->query("
                        SELECT f.grand_total, COALESCE(paid_sum.paid, 0) AS total_paid
                        FROM forms f
                        LEFT JOIN (
                            SELECT form_id, SUM(CASE WHEN type = 'deposit' OR type = 'repayment' THEN amount WHEN type = 'withdrawal' OR type = 'debt' THEN -amount ELSE 0 END) AS paid
                            FROM safe_transactions
                            WHERE form_id IS NOT NULL AND form_id != ''
                            GROUP BY form_id
                        ) paid_sum ON f.id = paid_sum.form_id
                        WHERE f.status != 'cancelled' AND TRIM(LOWER(f.customer_name)) = TRIM(LOWER('" . $conn->real_escape_string($debtor_name_to_check) . "'))
                    ");
                    if ($f_res) {
                        while ($fr = $f_res->fetch_assoc()) {
                            $rem = (float)$fr['grand_total'] - (float)$fr['total_paid'];
                            if ($rem > 0.01) {
                                $total_debt += $rem;
                            }
                        }
                    }
                }

                if ($amount > ($total_debt + 0.01)) {
                    $fmt_amount = number_format($amount, 0);
                    $fmt_debt = number_format(max(0, $total_debt), 0);
                    echo json_encode([
                        'status' => 'error',
                        'message' => "عذراً، المبلغ المدخل ($fmt_amount) أكبر من إجمالي الدين المستحق على هذا الحساب ($fmt_debt). لا يمكن تسديد مبلغ أكبر من قيمة الدين."
                    ]);
                    exit;
                }

                $conn->begin_transaction();
                try {
                    allocateCustomerPayment($conn, $name, $amount, $safe_id, $date, $description, $debtor_id);
                    updateSystemState($conn, 'safe_transactions');
                    updateSystemState($conn, 'forms');
                    updateSystemState($conn, 'debtors');
                    $conn->commit();
                    echo json_encode([
                        'status' => 'success',
                        'message' => 'تم تسجيل حركة تسديد الدين لهذا الحساب بنجاح وتوزيعها على الفواتير المستحقة',
                        'tx_id' => (string)time(),
                        'amount' => $amount,
                        'debtorId' => $debtor_id
                    ]);
                } catch (Exception $e) {
                    $conn->rollback();
                    echo json_encode(['status' => 'error', 'message' => 'فشلت العملية: ' . $e->getMessage()]);
                }
                exit;
            } else {
                // 'debt' is mapped to 'debt' type in DB so it doesn't affect safe balance
                $db_type = 'debt';

                $stmt = $conn->prepare("INSERT INTO safe_transactions (safe_id, description, type, date, amount, debtor_id, user_id) VALUES (?, ?, ?, ?, ?, ?, ?)");
                $stmt->bind_param('isssdsi', $safe_id, $description, $db_type, $date, $amount, $debtor_id, $_SESSION['user']['id']);
                if ($stmt->execute()) {
                    $new_id = $conn->insert_id;
                    $new_res = $conn->query("SELECT * FROM safe_transactions WHERE id = " . intval($new_id));
                    $new_tx = $new_res ? $new_res->fetch_assoc() : null;
                    logActivity($conn, 'insert', 'safe_transactions', $new_id, null, $new_tx);
                    updateSystemState($conn, 'safe_transactions');
                    echo json_encode(['status' => 'success', 'message' => 'تم تسجيل حركة الدين بنجاح']);
                } else {
                    echo json_encode(['status' => 'error', 'message' => 'فشل تسجيل حركة الدين']);
                }
                $stmt->close();
                exit;
            }
        }

        // Pay Form (Full or Partial Payment)
        if ($action === 'pay_form_full' || $action === 'pay_form_partial') {
            $form_id    = (int)($input['formId'] ?? 0);
            $safe_id    = (int)($input['safeId'] ?? 0);
            $debtor_id  = trim($input['debtorId'] ?? '');
            $date       = trim($input['date'] ?? date('Y-m-d'));
            $custom_amt = isset($input['amount']) ? (float)$input['amount'] : 0;
            $custom_desc = trim($input['description'] ?? '');

            if (!$form_id || !$safe_id) {
                echo json_encode(['status' => 'error', 'message' => 'بيانات غير مكتملة: يرجى تحديد الاستمارة والخزنة']);
                exit;
            }
            checkFormOwnership($form_id);

            // Get form grand_total and already paid
            $stmt = $conn->prepare("SELECT grand_total, customer_name FROM forms WHERE id = ?");
            $stmt->bind_param('i', $form_id);
            $stmt->execute();
            $form_row = $stmt->get_result()->fetch_assoc();
            $stmt->close();

            if (!$form_row) {
                echo json_encode(['status' => 'error', 'message' => 'الاستمارة غير موجودة']);
                exit;
            }

            $grand_total = (float)$form_row['grand_total'];
            $customer_name = $form_row['customer_name'];

            $form_id_str_q = (string)$form_id;
            $stmt = $conn->prepare("SELECT SUM(CASE WHEN type='deposit' OR type='repayment' THEN amount WHEN type='withdrawal' OR type='debt' THEN -amount ELSE 0 END) AS paid FROM safe_transactions WHERE form_id = ?");
            $stmt->bind_param('s', $form_id_str_q);
            $stmt->execute();
            $paid_row = $stmt->get_result()->fetch_assoc();
            $stmt->close();

            $already_paid = (float)($paid_row['paid'] ?? 0);
            $remaining = $grand_total - $already_paid;

            if ($remaining <= 0.01) {
                echo json_encode(['status' => 'error', 'message' => 'هذه الاستمارة مسددة بالكامل بالفعل']);
                exit;
            }

            // Determine payment amount
            if ($custom_amt > 0) {
                if ($custom_amt > ($remaining + 0.01)) {
                    $fmt_custom = number_format($custom_amt, 0);
                    $fmt_rem = number_format(max(0, $remaining), 0);
                    echo json_encode([
                        'status' => 'error',
                        'message' => "عذراً، المبلغ المدخل ($fmt_custom) أكبر من الدين المتبقي للاستمارة ($fmt_rem). لا يمكن تسديد مبلغ أكبر من قيمة الدين."
                    ]);
                    exit;
                }
                $pay_amount = $custom_amt;
            } else {
                $pay_amount = $remaining;
            }

            if ($pay_amount <= 0) {
                echo json_encode(['status' => 'error', 'message' => 'يرجى إدخال مبلغ تسديد صالح أكبر من الصفر']);
                exit;
            }

            $is_full = ($remaining - $pay_amount) <= 0.01;

            // Resolve debtor real ID
            $real_debtor_id = null;
            if (!empty($debtor_id)) {
                if (strpos($debtor_id, 'name_') === 0) {
                    $dname = base64_decode(substr($debtor_id, 5));
                    $norm_dname = normalizeArabicName($dname);
                    $deb_res = $conn->query("SELECT id, name FROM debtors");
                    if ($deb_res) {
                        while ($dr = $deb_res->fetch_assoc()) {
                            if (normalizeArabicName($dr['name']) === $norm_dname) {
                                $real_debtor_id = $dr['id'];
                                break;
                            }
                        }
                    }
                } else {
                    $real_debtor_id = (int)$debtor_id;
                }
            }
            if (!$real_debtor_id) {
                $norm_cname = normalizeArabicName($customer_name);
                $deb_res = $conn->query("SELECT id, name FROM debtors");
                if ($deb_res) {
                    while ($dr = $deb_res->fetch_assoc()) {
                        if (normalizeArabicName($dr['name']) === $norm_cname) {
                            $real_debtor_id = $dr['id'];
                            break;
                        }
                    }
                }
            }

            if (!empty($custom_desc)) {
                $description = $custom_desc;
            } else {
                $description = $is_full ? ('سداد كامل للاستمارة #' . $form_id) : ('سداد جزء من الاستمارة #' . $form_id);
            }
            $form_id_str = (string)$form_id;
 
            $conn->begin_transaction();
            try {
                $stmt = $conn->prepare("INSERT INTO safe_transactions (safe_id, description, type, date, amount, debtor_id, form_id, user_id) VALUES (?, ?, 'deposit', ?, ?, ?, ?, ?)");
                $stmt->bind_param('issdisi', $safe_id, $description, $date, $pay_amount, $real_debtor_id, $form_id_str, $_SESSION['user']['id']);
                $stmt->execute();
                $tx_id = $conn->insert_id;
                $stmt->close();
 
                // Update form status: '1' for full, '2' for partial
                $new_status = $is_full ? '1' : '2';
                $stmt = $conn->prepare("UPDATE forms SET status = ? WHERE id = ?");
                $stmt->bind_param('si', $new_status, $form_id);
                $stmt->execute();
                $stmt->close();
 
                $tx_res = $conn->query("SELECT * FROM safe_transactions WHERE id = " . intval($tx_id));
                $tx_row = $tx_res ? $tx_res->fetch_assoc() : null;
                logActivity($conn, 'insert', 'safe_transactions', $tx_id, null, $tx_row);
                updateSystemState($conn, 'safe_transactions');
                updateSystemState($conn, 'forms');

                $conn->commit();
                $msg = $is_full ? ('تم تسديد الاستمارة #' . $form_id . ' بالكامل بنجاح') : ('تم تسجيل الدفعة الجزئية للاستمارة #' . $form_id . ' بنجاح');
                echo json_encode([
                    'status' => 'success',
                    'message' => $msg,
                    'amount' => $pay_amount,
                    'isFull' => $is_full,
                    'tx_id' => (string)$tx_id,
                    'formId' => $form_id
                ]);
            } catch (Exception $e) {
                $conn->rollback();
                echo json_encode(['status' => 'error', 'message' => 'فشلت العملية: ' . $e->getMessage()]);
            }
            exit;
        }


        if ($action === 'delete_transaction') {
            $id = (int)($input['id'] ?? 0);
            if (!$id) {
                echo json_encode(['status' => 'error', 'message' => 'معرف الحركة مطلوب']);
                exit;
            }
            if (!canViewOthersData()) {
                $myId = (int)$_SESSION['user']['id'];
                $chk = $conn->prepare("SELECT user_id FROM safe_transactions WHERE id = ?");
                $chk->bind_param('i', $id);
                $chk->execute();
                $tx_row = $chk->get_result()->fetch_assoc();
                $chk->close();
                if ($tx_row && (int)$tx_row['user_id'] !== $myId) {
                    header('HTTP/1.1 403 Forbidden');
                    echo json_encode(['status' => 'error', 'message' => 'عذراً، لا تمتلك الصلاحية لحذف هذه الحركة.']);
                    exit;
                }
            }

            $old_res = $conn->query("SELECT * FROM safe_transactions WHERE id = " . intval($id));
            $old_tx = $old_res ? $old_res->fetch_assoc() : null;

            $stmt = $conn->prepare("DELETE FROM safe_transactions WHERE id = ?");
            $stmt->bind_param('i', $id);
            if ($stmt->execute()) {
                logActivity($conn, 'delete', 'safe_transactions', $id, $old_tx, null);
                updateSystemState($conn, 'safe_transactions');
                updateSystemState($conn, 'forms'); // since deleting transaction changes form total paid
                echo json_encode(['status' => 'success', 'message' => 'تم حذف الحركة بنجاح']);
            } else {
                echo json_encode(['status' => 'error', 'message' => 'فشل حذف الحركة']);
            }
            $stmt->close();
            exit;
        }
    }

    echo json_encode(['status' => 'error', 'message' => 'طلب غير صالح']);
    ?>
