<?php
/**
 * Purchase Controller — Katchi Parchi (آمد اور خریدی)
 * Creates purchase, generates inventory lot, posts double-entry journal.
 */

class PurchaseController {
    private PDO $db;
    private object $user;

    public function __construct(PDO $db, object $user) {
        $this->db = $db;
        $this->user = $user;
    }

    /** GET /purchases/next-voucher */
    public function nextVoucher(): void {
        Response::success([
            'voucher_no' => $this->getNextVoucherNo(),
            'lot_number' => $this->getNextLotNumber()
        ]);
    }

    /** GET /purchases */
    public function index(array $query): void {
        $page = max(1, (int)($query['page'] ?? 1));
        $limit = min(100, max(10, (int)($query['limit'] ?? 50)));
        $offset = ($page - 1) * $limit;

        $where = ['pm.is_reversed = 0'];
        $params = [];

        if (!empty($query['date'])) {
            $where[] = 'pm.voucher_date = ?';
            $params[] = $query['date'];
        }

        if (!empty($query['item_id'])) {
            $where[] = 'pm.item_id = ?';
            $params[] = (int)$query['item_id'];
        }

        if (!empty($query['party'])) {
            $where[] = 'a.name LIKE ?';
            $params[] = '%' . $query['party'] . '%';
        }

        $whereStr = implode(' AND ', $where);

        $countStmt = $this->db->prepare("
            SELECT COUNT(DISTINCT pm.id) as total 
            FROM purchase_master pm 
            JOIN accounts a ON pm.zamidar_id = a.id
            WHERE {$whereStr}
        ");
        $countStmt->execute($params);
        $total = (int)$countStmt->fetch()['total'];

        $sumStmt = $this->db->prepare("
            SELECT COALESCE(SUM(pm.total_gross_amount), 0) as total_amount 
            FROM purchase_master pm 
            JOIN accounts a ON pm.zamidar_id = a.id
            WHERE {$whereStr}
        ");
        $sumStmt->execute($params);
        $totalPurchaseAmount = (float)$sumStmt->fetch()['total_amount'];

        $stmt = $this->db->prepare("
            SELECT pm.*, a.name as zamidar_name, 
                   GROUP_CONCAT(DISTINCT i.name SEPARATOR ', ') as item_name,
                   GROUP_CONCAT(DISTINCT pl.lot_number SEPARATOR ', ') as lot_numbers,
                   SUM(pl.net_weight) as total_net_weight
            FROM purchase_master pm
            JOIN accounts a ON pm.zamidar_id = a.id
            JOIN purchase_lots pl ON pm.id = pl.purchase_id
            JOIN items i ON pl.item_id = i.id
            WHERE {$whereStr}
            GROUP BY pm.id
            ORDER BY pm.id DESC
            LIMIT ? OFFSET ?
        ");

        // Bind where params (positional)
        foreach ($params as $key => $value) {
            $stmt->bindValue($key + 1, $value);
        }
        
        // Bind LIMIT and OFFSET as integers at the END
        $paramCount = count($params);
        $stmt->bindValue($paramCount + 1, $limit, PDO::PARAM_INT);
        $stmt->bindValue($paramCount + 2, $offset, PDO::PARAM_INT);

        $stmt->execute();

        Response::success([
            'data' => $stmt->fetchAll(),
            'pagination' => ['total' => $total, 'page' => $page, 'limit' => $limit],
            'total_purchase_amount' => $totalPurchaseAmount
        ]);
    }

    /** GET /purchases/{id} */
    public function show(int $id): void {
        $stmt = $this->db->prepare("
            SELECT pm.*, a.name as zamidar_name, i.name as item_name, pa.name as payment_method_name
            FROM purchase_master pm
            JOIN accounts a ON pm.zamidar_id = a.id
            JOIN items i ON pm.item_id = i.id
            LEFT JOIN accounts pa ON pm.payment_method_id = pa.id
            WHERE pm.id = ?
        ");
        $stmt->execute([$id]);
        $purchase = $stmt->fetch();
        if (!$purchase) Response::notFound('پرچی نہیں ملی۔');

        // Get deductions
        $dStmt = $this->db->prepare("SELECT pd.*, a.name as account_name FROM purchase_deductions pd JOIN accounts a ON pd.account_id = a.id WHERE pd.purchase_id = ?");
        $dStmt->execute([$id]);
        $purchase['deductions'] = $dStmt->fetchAll();

        // Get lot
        $lStmt = $this->db->prepare("SELECT * FROM purchase_lots WHERE purchase_id = ?");
        $lStmt->execute([$id]);
        $purchase['lots'] = $lStmt->fetchAll();

        Response::success($purchase);
    }

    /** POST /purchases — CRITICAL TRANSACTION */
    public function store(array $data): void {
        Validator::check($data, [
            'voucher_date'  => 'required|date',
            'zamidar_id'    => 'required|integer',
            'items'         => 'required|array',
            'net_payable'   => 'required|numeric|non_negative'
        ]);

        $items = $data['items'];
        if (empty($items)) {
            Response::error('براہ کرم کم از کم ایک جنس شامل کریں۔');
        }

        // Idempotency check
        $uuid = $data['uuid'] ?? UUID::generate();
        $existingId = UUID::checkIdempotency($this->db, 'purchase_master', $uuid);
        if ($existingId) {
            $this->show($existingId);
            return;
        }

        $this->db->beginTransaction();
        try {
            $voucherNo = $this->getNextVoucherNo();

            // 2. Calculate total deductions and total gross amount
            // Amounts are signed: positive = added to bill, negative = deducted from bill
            $deductions = $data['deductions'] ?? [];
            $totalDeductions = 0;
            foreach ($deductions as $ded) {
                $totalDeductions += floatval($ded['amount']); // already signed from frontend
            }
            
            $totalGrossAmount = 0;
            foreach ($items as $item) {
                $totalGrossAmount += floatval($item['gross_amount']);
            }

            // 3. Insert purchase_master
            // For backward compatibility, we store the first item's details in the master row
            $firstItem = $items[0];

            $stmt = $this->db->prepare("
                INSERT INTO purchase_master 
                (uuid, voucher_no, voucher_date, zamidar_id, item_id, total_gross_amount, vehicle_no, packing, bori_qty, tara,
                 gross_weight, kat, net_weight, rate_basis, rate, gross_amount, total_deductions, net_payable,
                 payment_method_id, amount_paid, remaining_balance, created_by)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ");

            $amountPaid = floatval($data['amount_paid'] ?? 0);
            $paymentMethodId = !empty($data['payment_method_id']) && $data['payment_method_id'] !== 'khata' 
                ? (int)$data['payment_method_id'] : null;
            $netPayable = floatval($data['net_payable']);
            $remainingBalance = $netPayable - $amountPaid;

            $stmt->execute([
                $uuid,
                $voucherNo,
                $data['voucher_date'],
                $data['zamidar_id'],
                $firstItem['item_id'],
                $totalGrossAmount,
                $data['vehicle_no'] ?? null,
                $firstItem['packing'] ?? 'بوری',
                (int)($firstItem['bori_qty'] ?? 0),
                floatval($firstItem['tara'] ?? 0),
                floatval($firstItem['gross_weight']),
                floatval($firstItem['kat'] ?? 0),
                floatval($firstItem['net_weight']),
                (int)($firstItem['rate_basis'] ?? 40),
                floatval($firstItem['rate']),
                floatval($firstItem['gross_amount']),
                $totalDeductions,
                $netPayable,
                $paymentMethodId,
                $amountPaid,
                $remainingBalance,
                $this->user->id
            ]);

            $purchaseId = (int)$this->db->lastInsertId();

            // 4. Insert deductions
            $systemAccounts = $this->getSystemAccountMap();
            $deductionStmt = $this->db->prepare("
                INSERT INTO purchase_deductions (purchase_id, account_id, description, amount) VALUES (?,?,?,?)
            ");

            foreach ($deductions as $ded) {
                $accId = $this->resolveDeductionAccount($ded['description'], $systemAccounts);
                $deductionStmt->execute([
                    $purchaseId,
                    $accId,
                    $ded['description'],
                    floatval($ded['amount'])
                ]);
            }

            // 5. Generate Lots (Inventory IN)
            $zamidarName = $this->db->query("SELECT name FROM accounts WHERE id = {$data['zamidar_id']}")->fetchColumn();
            
            $lotStmt = $this->db->prepare("
                INSERT INTO purchase_lots 
                (purchase_id, lot_number, item_id, item_name, zamidar_name, zamidar_id, packing, bori_qty, tara, gross_weight, kat, net_weight, rate_basis, rate, gross_amount, remaining_qty)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ");

            foreach ($items as $idx => $item) {
                $lotNumber = $this->getNextLotNumber($idx); // Pass index to maybe differentiate or handle multiple
                $itemName = $this->db->query("SELECT name FROM items WHERE id = {$item['item_id']}")->fetchColumn();

                $lotStmt->execute([
                    $purchaseId,
                    $lotNumber,
                    $item['item_id'],
                    $itemName,
                    $zamidarName,
                    $data['zamidar_id'],
                    $item['packing'] ?? 'بوری',
                    (int)($item['bori_qty'] ?? 0),
                    floatval($item['tara'] ?? 0),
                    floatval($item['gross_weight']),
                    floatval($item['kat'] ?? 0),
                    floatval($item['net_weight']),
                    (int)($item['rate_basis'] ?? 40),
                    floatval($item['rate']),
                    floatval($item['gross_amount']),
                    floatval($item['net_weight'])
                ]);
            }

            // 6. Build Journal Entries (Double Entry)
            $journalEntries = [];

            // DEBIT: Purchase Account (gross amount)
            $journalEntries[] = [
                'account_id' => $systemAccounts['purchase'],
                'debit'      => $totalGrossAmount,
                'credit'     => 0,
                'narration'  => "خرید پرچی {$voucherNo} — کل رقم"
            ];

            // CREDIT: Zamidar Account (net payable)
            $journalEntries[] = [
                'account_id' => (int)$data['zamidar_id'],
                'debit'      => 0,
                'credit'     => $netPayable,
                'narration'  => "خرید پرچی {$voucherNo} — قابل ادائیگی"
            ];

            // Journal entries for each deduction/addition
            foreach ($deductions as $ded) {
                $signedAmt = floatval($ded['amount']);
                if ($signedAmt == 0) continue;
                $accId = $this->resolveDeductionAccount($ded['description'], $systemAccounts);
                if ($signedAmt > 0) {
                    // Addition: debit the expense account (cost to buyer is added)
                    $journalEntries[] = [
                        'account_id' => $accId,
                        'debit'      => $signedAmt,
                        'credit'     => 0,
                        'narration'  => "خرید پرچی {$voucherNo} — {$ded['description']} (+)"
                    ];
                } else {
                    // Deduction: credit the expense account (reduces farmer's payable)
                    $journalEntries[] = [
                        'account_id' => $accId,
                        'debit'      => 0,
                        'credit'     => abs($signedAmt),
                        'narration'  => "خرید پرچی {$voucherNo} — {$ded['description']} (-)"
                    ];
                }
            }

            // Post the main journal
            $journalId = AccountingService::postJournal($this->db, [
                'date'        => $data['voucher_date'],
                'source_type' => 'purchase',
                'source_id'   => $purchaseId,
                'narration'   => "خرید پرچی {$voucherNo} — {$zamidarName}",
                'created_by'  => $this->user->id
            ], $journalEntries);

            // Update purchase with journal_id
            $this->db->prepare("UPDATE purchase_master SET journal_id = ? WHERE id = ?")->execute([$journalId, $purchaseId]);

            // 7. Settlement (if payment is not khata/udhar)
            if ($paymentMethodId && $amountPaid > 0) {
                AccountingService::assertSufficientBankCashBalance($this->db, $paymentMethodId, $amountPaid);

                $settlementEntries = [
                    ['account_id' => (int)$data['zamidar_id'], 'debit' => $amountPaid, 'credit' => 0, 'narration' => "ادائیگی — پرچی {$voucherNo}"],
                    ['account_id' => $paymentMethodId, 'debit' => 0, 'credit' => $amountPaid, 'narration' => "ادائیگی — پرچی {$voucherNo}"]
                ];

                $settlementJournalId = AccountingService::postJournal($this->db, [
                    'date'        => $data['voucher_date'],
                    'source_type' => 'settlement',
                    'source_id'   => $purchaseId,
                    'narration'   => "ادائیگی خرید پرچی {$voucherNo}",
                    'created_by'  => $this->user->id
                ], $settlementEntries);

                $this->db->prepare("UPDATE purchase_master SET settlement_journal_id = ? WHERE id = ?")->execute([$settlementJournalId, $purchaseId]);
            }

            AuditLogger::log($this->db, $this->user->id, 'CREATED_PURCHASE', 'purchase_master', $purchaseId, null, $data);

            $this->db->commit();
            Response::created(['id' => $purchaseId, 'voucher_no' => $voucherNo, 'lot_number' => $lotNumber], 'پرچی کامیابی سے محفوظ ہو گئی۔');

        } catch (Exception $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    /** PUT /purchases/{id} */
    public function update(int $id, array $data): void {
        Validator::check($data, [
            'voucher_date'  => 'required|date',
            'zamidar_id'    => 'required|integer',
            'items'         => 'required|array',
            'net_payable'   => 'required|numeric|non_negative'
        ]);

        $items = $data['items'];
        if (empty($items)) {
            Response::error('براہ کرم کم از کم ایک جنس شامل کریں۔');
        }

        $stmt = $this->db->prepare("SELECT * FROM purchase_master WHERE id = ? FOR UPDATE");
        $stmt->execute([$id]);
        $old = $stmt->fetch();
        if (!$old) Response::notFound('پرچی نہیں ملی۔');
        if ($old['is_reversed']) Response::conflict('ریورس شدہ پرچی میں تبدیلی نہیں کی جا سکتی۔');

        $this->db->beginTransaction();
        try {
            // 1. Reverse old financial footprint
            AccountingService::reverseJournal($this->db, $old['journal_id'], $this->user->id);
            if ($old['settlement_journal_id']) {
                AccountingService::reverseJournal($this->db, $old['settlement_journal_id'], $this->user->id);
            }

            // 2. Calculate aggregations (amounts are signed from frontend)
            $deductions = $data['deductions'] ?? [];
            $totalDeductions = 0;
            foreach ($deductions as $ded) {
                $totalDeductions += floatval($ded['amount']);
            }

            $totalGrossAmount = 0;
            foreach ($items as $item) {
                $totalGrossAmount += floatval($item['gross_amount']);
            }
            $firstItem = $items[0];

            // 3. Update Master Record
            $stmt = $this->db->prepare("
                UPDATE purchase_master SET
                    voucher_date = ?, zamidar_id = ?, item_id = ?, total_gross_amount = ?, vehicle_no = ?, 
                    packing = ?, bori_qty = ?, tara = ?, gross_weight = ?, kat = ?, 
                    net_weight = ?, rate_basis = ?, rate = ?, gross_amount = ?, 
                    total_deductions = ?, net_payable = ?, amount_paid = ?, remaining_balance = ?,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            ");

            $amountPaid = floatval($data['amount_paid'] ?? 0);
            $netPayable = floatval($data['net_payable']);
            $remainingBalance = $netPayable - $amountPaid;

            $stmt->execute([
                $data['voucher_date'], 
                $data['zamidar_id'], 
                $firstItem['item_id'], 
                $totalGrossAmount,
                $data['vehicle_no'] ?? null,
                $firstItem['packing'] ?? 'بوری', 
                (int)($firstItem['bori_qty'] ?? 0), 
                floatval($firstItem['tara'] ?? 0),
                floatval($firstItem['gross_weight']), 
                floatval($firstItem['kat'] ?? 0), 
                floatval($firstItem['net_weight']),
                (int)($firstItem['rate_basis'] ?? 40), 
                floatval($firstItem['rate']), 
                floatval($firstItem['gross_amount']),
                $totalDeductions, 
                $netPayable, 
                $amountPaid, 
                $remainingBalance, 
                $id
            ]);

            // 4. Sync Deductions
            $this->db->prepare("DELETE FROM purchase_deductions WHERE purchase_id = ?")->execute([$id]);
            $systemAccounts = $this->getSystemAccountMap();
            $dedStmt = $this->db->prepare("INSERT INTO purchase_deductions (purchase_id, account_id, description, amount) VALUES (?,?,?,?)");
            foreach ($deductions as $ded) {
                $accId = $this->resolveDeductionAccount($ded['description'], $systemAccounts);
                $dedStmt->execute([$id, $accId, $ded['description'], floatval($ded['amount'])]);
            }

            // 5. Update Inventory Lots (Dynamic Multi-Item Sync)
            $existingLots = $this->db->query("SELECT id FROM purchase_lots WHERE purchase_id = {$id} ORDER BY id ASC")->fetchAll(PDO::FETCH_ASSOC);
            $zamidarName = $this->db->query("SELECT name FROM accounts WHERE id = {$data['zamidar_id']}")->fetchColumn();

            $updateLotStmt = $this->db->prepare("
                UPDATE purchase_lots SET 
                    item_id = ?, item_name = ?, zamidar_name = ?, zamidar_id = ?, 
                    packing = ?, bori_qty = ?, tara = ?, gross_weight = ?, kat = ?, net_weight = ?, 
                    rate_basis = ?, rate = ?, gross_amount = ?, remaining_qty = ?
                WHERE id = ?
            ");

            $insertLotStmt = $this->db->prepare("
                INSERT INTO purchase_lots 
                (purchase_id, lot_number, item_id, item_name, zamidar_name, zamidar_id, packing, bori_qty, tara, gross_weight, kat, net_weight, rate_basis, rate, gross_amount, remaining_qty)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ");

            foreach ($items as $idx => $item) {
                $itemName = $this->db->query("SELECT name FROM items WHERE id = {$item['item_id']}")->fetchColumn();
                
                if (isset($existingLots[$idx])) {
                    // Update existing lot sequentially
                    $lotId = $existingLots[$idx]['id'];
                    $updateLotStmt->execute([
                        $item['item_id'], $itemName, $zamidarName, $data['zamidar_id'],
                        $item['packing'] ?? 'بوری', (int)($item['bori_qty'] ?? 0), floatval($item['tara'] ?? 0),
                        floatval($item['gross_weight']), floatval($item['kat'] ?? 0), floatval($item['net_weight']),
                        (int)($item['rate_basis'] ?? 40), floatval($item['rate']), floatval($item['gross_amount']),
                        floatval($item['net_weight']), $lotId
                    ]);
                } else {
                    // Insert new lot if user added rows during edit
                    $lotNumber = $this->getNextLotNumber($idx); 
                    $insertLotStmt->execute([
                        $id, $lotNumber, $item['item_id'], $itemName, $zamidarName, $data['zamidar_id'],
                        $item['packing'] ?? 'بوری', (int)($item['bori_qty'] ?? 0), floatval($item['tara'] ?? 0),
                        floatval($item['gross_weight']), floatval($item['kat'] ?? 0), floatval($item['net_weight']),
                        (int)($item['rate_basis'] ?? 40), floatval($item['rate']), floatval($item['gross_amount']),
                        floatval($item['net_weight'])
                    ]);
                }
            }
            
            // Delete extra lots if user removed rows (Raises PDOException if already sold, protecting stock)
            for ($i = count($items); $i < count($existingLots); $i++) {
                $lotId = $existingLots[$i]['id'];
                $this->db->prepare("DELETE FROM purchase_lots WHERE id = ?")->execute([$lotId]);
            }

            // 6. Post NEW Journal Entries
            $voucherNo = $old['voucher_no'];
            $journalEntries = [];
            $journalEntries[] = ['account_id' => $systemAccounts['purchase'], 'debit' => $totalGrossAmount, 'credit' => 0, 'narration' => "خرید پرچی {$voucherNo} — کل رقم"];
            $journalEntries[] = ['account_id' => (int)$data['zamidar_id'], 'debit' => 0, 'credit' => $netPayable, 'narration' => "خرید پرچی {$voucherNo} — قابل ادائیگی"];
            
            foreach ($deductions as $ded) {
                $signedAmt = floatval($ded['amount']);
                if ($signedAmt == 0) continue;
                $accId = $this->resolveDeductionAccount($ded['description'], $systemAccounts);
                if ($signedAmt > 0) {
                    $journalEntries[] = ['account_id' => $accId, 'debit' => $signedAmt, 'credit' => 0, 'narration' => "خرید پرچی {$voucherNo} — {$ded['description']} (+)"];
                } else {
                    $journalEntries[] = ['account_id' => $accId, 'debit' => 0, 'credit' => abs($signedAmt), 'narration' => "خرید پرچی {$voucherNo} — {$ded['description']} (-)"];
                }
            }

            $journalId = AccountingService::postJournal($this->db, [
                'date' => $data['voucher_date'],
                'source_type' => 'purchase',
                'source_id' => $id,
                'narration' => "تصحیح خرید پرچی {$voucherNo} — {$zamidarName}",
                'created_by' => $this->user->id
            ], $journalEntries);

            $this->db->prepare("UPDATE purchase_master SET journal_id = ? WHERE id = ?")->execute([$journalId, $id]);

            // 7. Settlement Re-posting (if payment exists)
            $paymentMethodId = !empty($data['payment_method_id']) && $data['payment_method_id'] !== 'khata' 
                ? (int)$data['payment_method_id'] : null;
            if ($paymentMethodId && $amountPaid > 0) {
                AccountingService::assertSufficientBankCashBalance($this->db, $paymentMethodId, $amountPaid);

                $settlementEntries = [
                    ['account_id' => (int)$data['zamidar_id'], 'debit' => $amountPaid, 'credit' => 0, 'narration' => "ادائیگی — پرچی {$voucherNo}"],
                    ['account_id' => $paymentMethodId, 'debit' => 0, 'credit' => $amountPaid, 'narration' => "ادائیگی — پرچی {$voucherNo}"]
                ];
                $settlementJournalId = AccountingService::postJournal($this->db, [
                    'date' => $data['voucher_date'],
                    'source_type' => 'settlement',
                    'source_id' => $id,
                    'narration' => "ادائیگی خرید پرچی {$voucherNo}",
                    'created_by' => $this->user->id
                ], $settlementEntries);
                $this->db->prepare("UPDATE purchase_master SET settlement_journal_id = ? WHERE id = ?")->execute([$settlementJournalId, $id]);
            } else {
                $this->db->prepare("UPDATE purchase_master SET settlement_journal_id = NULL WHERE id = ?")->execute([$id]);
            }

            AuditLogger::log($this->db, $this->user->id, 'UPDATED_PURCHASE', 'purchase_master', $id, $old, $data);

            $this->db->commit();
            Response::success(['id' => $id], 'پرچی کامیابی سے اپڈیٹ ہو گئی۔');
        } catch (Exception $e) {
            $this->db->rollBack();
            // If delete fails due to FK ON RESTRICT
            if (strpos($e->getMessage(), '1451') !== false) {
                Response::conflict('اس پرچی کا مال فروخت ہو چکا ہے اور ریفرنسڈ ہے، مزید تبدیلی ممکن نہیں۔');
            }
            throw $e;
        }
    }

    /** POST /purchases/{id}/reverse */
    public function reverse(int $id): void {
        $stmt = $this->db->prepare("SELECT * FROM purchase_master WHERE id = ?");
        $stmt->execute([$id]);
        $purchase = $stmt->fetch();
        if (!$purchase) Response::notFound('پرچی نہیں ملی۔');
        if ($purchase['is_reversed']) Response::conflict('یہ پرچی پہلے سے ریورس ہو چکی ہے۔');

        $this->db->beginTransaction();
        try {
            // 1. Reverse main journal if exists
            $reversalJournalId = null;
            if (!empty($purchase['journal_id'])) {
                $reversalJournalId = AccountingService::reverseJournal($this->db, $purchase['journal_id'], $this->user->id);
            }

            // 2. Reverse settlement journal if exists
            if (!empty($purchase['settlement_journal_id'])) {
                AccountingService::reverseJournal($this->db, $purchase['settlement_journal_id'], $this->user->id);
            }

            // 3. Restore lot inventory
            $this->db->prepare("
                UPDATE purchase_lots SET remaining_qty = net_weight, status = 'available' WHERE purchase_id = ?
            ")->execute([$id]);

            // 4. Mark purchase as reversed
            $this->db->prepare("
                UPDATE purchase_master SET is_reversed = 1, reversed_by_journal_id = ? WHERE id = ?
            ")->execute([$reversalJournalId, $id]);

            AuditLogger::log($this->db, $this->user->id, 'REVERSED_PURCHASE', 'purchase_master', $id, $purchase);

            $this->db->commit();
            Response::success(null, 'پرچی ریورس ہو گئی۔ تمام اکاؤنٹس بحال ہو گئے۔');
        } catch (Exception $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    /** GET /lots — Available lots for sale */
    public function lots(array $query): void {
        $includeLotId = !empty($query['include_lot_id']) ? (int)$query['include_lot_id'] : null;
        
        // Include 'available' and 'partial' lots, PLUS the specific lot being edited (even if sold)
        if ($includeLotId) {
            $where = ["(pl.status IN ('available', 'partial') OR pl.id = ?)"];
            $params = [$includeLotId];
        } else {
            $where = ["pl.status IN ('available', 'partial')"];
            $params = [];
        }

        if (!empty($query['item_id'])) {
            $where[] = 'pl.item_id = ?';
            $params[] = (int)$query['item_id'];
        }

        $whereStr = implode(' AND ', $where);
        $stmt = $this->db->prepare("
            SELECT pl.*, i.name as item_name, a.name as zamidar_name
            FROM purchase_lots pl
            JOIN items i ON pl.item_id = i.id
            JOIN accounts a ON pl.zamidar_id = a.id
            WHERE {$whereStr}
            ORDER BY pl.id DESC
        ");
        $stmt->execute($params);

        Response::success($stmt->fetchAll());
    }

    // ---- Private Helpers ----

    private function getNextVoucherNo(): string {
        $stmt = $this->db->query("SELECT MAX(id) as max_id FROM purchase_master");
        $maxId = (int)($stmt->fetch()['max_id'] ?? 0);
        $next = $maxId + 1004; // Start from 1004 as per user preference
        return "خ-{$next}";
    }

    private function getNextLotNumber(): string {
        $stmt = $this->db->query("SELECT MAX(id) as max_id FROM purchase_lots");
        $maxId = (int)($stmt->fetch()['max_id'] ?? 0);
        $next = $maxId + 1004;
        return "L-{$next}";
    }

    private function getSystemAccountMap(): array {
        $stmt = $this->db->query("SELECT id, sub_type FROM accounts WHERE is_system = 1");
        $map = [];
        foreach ($stmt->fetchAll() as $row) {
            $map[$row['sub_type']] = (int)$row['id'];
        }
        return $map;
    }

    private function resolveDeductionAccount(string $description, array $systemAccounts): int {
        $desc = mb_strtolower($description);
        if (mb_strpos($desc, 'مزدوری') !== false || mb_strpos($desc, 'mazdoori') !== false) return $systemAccounts['mazdoori'];
        if (mb_strpos($desc, 'کمیشن') !== false || mb_strpos($desc, 'commission') !== false) return $systemAccounts['commission'];
        if (mb_strpos($desc, 'کرایہ') !== false || mb_strpos($desc, 'kiraya') !== false) return $systemAccounts['kiraya'];
        if (mb_strpos($desc, 'ٹورا') !== false || mb_strpos($desc, 'tora') !== false) return $systemAccounts['tora'];
        if (mb_strpos($desc, 'مارکیٹ') !== false || mb_strpos($desc, 'market') !== false) return $systemAccounts['market_fee'];
        // Fallback to general expense
        return $systemAccounts['expense'] ?? $systemAccounts['mazdoori'];
    }
}
