<?php
/**
 * Sale Controller — Pakki Parchi (فروخت اور بکری)
 * Creates sale, locks inventory, posts double-entry journal.
 * CRITICAL: Uses FOR UPDATE for anti-overselling.
 */

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

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

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

    /** GET /sales */
    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 = ['sm.is_reversed = 0'];
        $params = [];

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

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

        if (!empty($query['item_id'])) {
            $where[] = 'EXISTS (SELECT 1 FROM sale_items WHERE sale_id = sm.id AND item_id = ?)';
            $params[] = (int)$query['item_id'];
        }

        $whereStr = implode(' AND ', $where);
        
        $countStmt = $this->db->prepare("
            SELECT COUNT(DISTINCT sm.id) as total 
            FROM sale_master sm 
            JOIN accounts a ON sm.khareedar_id = a.id
            WHERE {$whereStr}
        ");
        $countStmt->execute($params);
        $total = (int)$countStmt->fetch()['total'];

        $sumParams = $params;
        if (!empty($query['item_id'])) {
            // Calculate proportional net_receivable matching item ledger
            $sumSql = "
                SELECT COALESCE(SUM(
                    IF(sm.gross_amount > 0, 
                       ROUND(sm.net_receivable * (si.gross_amount / sm.gross_amount), 2), 
                       0)
                ), 0) as total_sum
                FROM sale_master sm 
                JOIN accounts a ON sm.khareedar_id = a.id
                JOIN sale_items si ON sm.id = si.sale_id
                WHERE sm.is_reversed = 0
            ";
            
            $sumWhere = [];
            $sumParams = [];
            if (!empty($query['date'])) {
                $sumWhere[] = 'sm.voucher_date = ?';
                $sumParams[] = $query['date'];
            }
            if (!empty($query['party'])) {
                $sumWhere[] = 'a.name LIKE ?';
                $sumParams[] = '%' . $query['party'] . '%';
            }
            $sumWhere[] = 'si.item_id = ?';
            $sumParams[] = (int)$query['item_id'];
            
            $sumSql .= ' AND ' . implode(' AND ', $sumWhere);
        } else {
            // Sum net_receivable across all sales matching current filters
            $sumSql = "
                SELECT COALESCE(SUM(sm.net_receivable), 0) as total_sum
                FROM sale_master sm 
                JOIN accounts a ON sm.khareedar_id = a.id
                WHERE {$whereStr}
            ";
        }
        
        $sumStmt = $this->db->prepare($sumSql);
        $sumStmt->execute($sumParams);
        $totalSum = (float)$sumStmt->fetch()['total_sum'];

        $stmt = $this->db->prepare("
            SELECT sm.*, a.name as khareedar_name, 
                   COUNT(si.id) as item_count,
                   MAX(i.name) as item_name,
                   MAX(pl.lot_number) as lot_number,
                   SUM(si.weight) as sale_weight,
                   SUM(si.deduction) as total_deduction
            FROM sale_master sm
            JOIN accounts a ON sm.khareedar_id = a.id
            LEFT JOIN sale_items si ON sm.id = si.sale_id
            LEFT JOIN items i ON si.item_id = i.id
            LEFT JOIN purchase_lots pl ON si.lot_id = pl.id
            WHERE {$whereStr}
            GROUP BY sm.id
            ORDER BY sm.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_gross_amount' => $totalSum,
            'total_net_receivable' => $totalSum
        ]);
    }

    /** GET /sales/{id} */
    public function show(int $id): void {
        $stmt = $this->db->prepare("
            SELECT sm.*, a.name as khareedar_name,
                   pa.name as payment_method_name
            FROM sale_master sm
            JOIN accounts a ON sm.khareedar_id = a.id
            LEFT JOIN accounts pa ON sm.payment_method_id = pa.id
            WHERE sm.id = ?
        ");
        $stmt->execute([$id]);
        $sale = $stmt->fetch();
        if (!$sale) Response::notFound('پرچی نہیں ملی۔');

        // Fetch items
        $iStmt = $this->db->prepare("
            SELECT si.*, i.name as item_name, pl.lot_number, pl.remaining_qty, pl.zamidar_name
            FROM sale_items si 
            JOIN items i ON si.item_id = i.id
            JOIN purchase_lots pl ON si.lot_id = pl.id 
            WHERE si.sale_id = ?
        ");
        $iStmt->execute([$id]);
        $sale['items'] = $iStmt->fetchAll();

        // Fetch additions
        $aStmt = $this->db->prepare("SELECT sa.*, acc.name as account_name FROM sale_additions sa JOIN accounts acc ON sa.account_id = acc.id WHERE sa.sale_id = ?");
        $aStmt->execute([$id]);
        $sale['additions'] = $aStmt->fetchAll();

        Response::success($sale);
    }

    /** POST /sales — CRITICAL TRANSACTION WITH INVENTORY LOCK */
    public function store(array $data): void {
        Validator::check($data, [
            'voucher_date'  => 'required|date',
            'due_date'      => 'nullable|date',
            'khareedar_id'  => 'required|integer',
            'items'         => 'required|array',
            'net_receivable'=> 'required|numeric|non_negative'
        ]);

        if (empty($data['items'])) {
            Response::validationError("Items array is empty.", ['items' => ['At least one item is required']]);
        }

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

        $this->db->beginTransaction();
        try {
            $totalGrossAmount = 0;
            
            // ============ INVENTORY LOCK & DEDUCT (CRITICAL) ============
            foreach ($data['items'] as $item) {
                if (!isset($item['weight'], $item['rate'], $item['lot_id'], $item['item_id'])) {
                    throw new Exception("Incomplete item data provided.");
                }

                $lotStmt = $this->db->prepare("SELECT * FROM purchase_lots WHERE id = ? FOR UPDATE");
                $lotStmt->execute([$item['lot_id']]);
                $lot = $lotStmt->fetch();

                if (!$lot) {
                    $this->db->rollBack();
                    Response::notFound('لاٹ نہیں ملا۔ (Lot ID: ' . $item['lot_id'] . ')');
                }

                $saleWeight = floatval($item['weight']);
                $remainingQty = floatval($lot['remaining_qty']);

                if ($saleWeight > $remainingQty) {
                    $this->db->rollBack();
                    Response::validationError(
                        "فروخت وزن ({$saleWeight} KG) دستیاب وزن ({$remainingQty} KG) سے زیادہ ہے۔ (لاٹ #{$lot['lot_number']})",
                        ['weight' => ["Available: {$remainingQty} KG"]]
                    );
                }

                // Deduct inventory
                $newRemaining = $remainingQty - $saleWeight;
                $newStatus = $newRemaining <= 0 ? 'sold' : 'partial';
                $this->db->prepare("UPDATE purchase_lots SET remaining_qty = ?, status = ? WHERE id = ?")
                    ->execute([$newRemaining, $newStatus, $item['lot_id']]);

                $totalGrossAmount = round($totalGrossAmount + floatval($item['gross_amount']), 2);
            }

            // ============ INSERT SALE MASTER ============
            $voucherNo = $this->getNextVoucherNo();
            $additions = $data['additions'] ?? [];
            $totalAdditions = round(array_sum(array_column($additions, 'amount')), 2);

            $amountReceived = round(floatval($data['amount_received'] ?? 0), 2);
            $paymentMethodId = !empty($data['payment_method_id']) && $data['payment_method_id'] !== 'khata'
                ? (int)$data['payment_method_id'] : null;
            $netReceivable = round(floatval($data['net_receivable']), 2);
            $remainingBalance = round($netReceivable - $amountReceived, 2);

            // Notice: lot_id, item_id, sale_weight, rate, rate_basis are kept temporary to 0 or null if required by default or schema
            $stmt = $this->db->prepare("
                INSERT INTO sale_master 
                (uuid, voucher_no, voucher_date, due_date, khareedar_id, havala_name, lot_id, item_id,
                 sale_weight, rate_basis, rate, gross_amount, total_additions, net_receivable,
                 payment_method_id, amount_received, remaining_balance, created_by)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ");
            $stmt->execute([
                $uuid, $voucherNo, $data['voucher_date'], $data['due_date'] ?? null, $data['khareedar_id'],
                $data['havala_name'] ?? null, 
                (!empty($data['items'][0]['lot_id']) ? $data['items'][0]['lot_id'] : 0), 
                (!empty($data['items'][0]['item_id']) ? $data['items'][0]['item_id'] : 0),
                0, 40, 0,
                $totalGrossAmount, $totalAdditions, $netReceivable,
                $paymentMethodId, $amountReceived, $remainingBalance, $this->user->id
            ]);

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

            // ============ INSERT SALE ITEMS ============
            $itemStmt = $this->db->prepare("INSERT INTO sale_items (sale_id, lot_id, item_id, weight, deduction, rate, rate_basis, gross_amount) VALUES (?,?,?,?,?,?,?,?)");
            foreach ($data['items'] as $item) {
                $itemStmt->execute([
                    $saleId, 
                    $item['lot_id'], 
                    $item['item_id'], 
                    floatval($item['weight']), 
                    floatval($item['deduction'] ?? 0),
                    floatval($item['rate']), 
                    (int)($item['rate_basis'] ?? 40), 
                    floatval($item['gross_amount'])
                ]);
            }

            // Insert additions
            $systemAccounts = $this->getSystemAccountMap();
            $addStmt = $this->db->prepare("INSERT INTO sale_additions (sale_id, account_id, description, type, percentage, amount) VALUES (?,?,?,?,?,?)");
            foreach ($additions as $add) {
                $accId = $this->resolveAdditionAccount($add['description'], $systemAccounts);
                $type = $add['type'] ?? 'plus';
                $addStmt->execute([
                    $saleId, $accId, $add['description'], $type,
                    isset($add['percentage']) ? floatval($add['percentage']) : null,
                    floatval($add['amount'])
                ]);
            }

            // ============ JOURNAL ENTRIES ============
            $khareedarName = $this->db->query("SELECT name FROM accounts WHERE id = {$data['khareedar_id']}")->fetchColumn();

            $journalEntries = [];

            // DEBIT: Khareedar (net_receivable = gross + additions)
            $journalEntries[] = [
                'account_id' => (int)$data['khareedar_id'],
                'debit'      => $netReceivable,
                'credit'     => 0,
                'narration'  => "فروخت پرچی {$voucherNo} — قابل وصول"
            ];

            // CREDIT: Sale Account (gross amount)
            $journalEntries[] = [
                'account_id' => $systemAccounts['sale'],
                'debit'      => 0,
                'credit'     => $totalGrossAmount,
                'narration'  => "فروخت پرچی {$voucherNo} — فروخت"
            ];

            // CREDIT/DEBIT: Each addition depending on its type
            foreach ($additions as $add) {
                $amt = round(floatval($add['amount']), 2);
                if ($amt <= 0) continue;
                $type = $add['type'] ?? 'plus';
                $accId = $this->resolveAdditionAccount($add['description'], $systemAccounts);
                
                // If plus: Income => Credit. If minus: Expense/Discount => Debit.
                $journalEntries[] = [
                    'account_id' => $accId,
                    'debit'      => $type === 'minus' ? $amt : 0,
                    'credit'     => $type === 'plus' ? $amt : 0,
                    'narration'  => "فروخت پرچی {$voucherNo} — {$add['description']}"
                ];
            }

            $journalId = AccountingService::postJournal($this->db, [
                'date'        => $data['voucher_date'],
                'source_type' => 'sale',
                'source_id'   => $saleId,
                'narration'   => "فروخت پرچی {$voucherNo} — {$khareedarName}",
                'created_by'  => $this->user->id
            ], $journalEntries);

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

            // Settlement (if not khata)
            if ($paymentMethodId && $amountReceived > 0) {
                $settlementEntries = [
                    ['account_id' => $paymentMethodId, 'debit' => $amountReceived, 'credit' => 0, 'narration' => "وصولی — پرچی {$voucherNo}"],
                    ['account_id' => (int)$data['khareedar_id'], 'debit' => 0, 'credit' => $amountReceived, 'narration' => "وصولی — پرچی {$voucherNo}"]
                ];

                $settlementJournalId = AccountingService::postJournal($this->db, [
                    'date'        => $data['voucher_date'],
                    'source_type' => 'settlement',
                    'source_id'   => $saleId,
                    'narration'   => "وصولی فروخت پرچی {$voucherNo}",
                    'created_by'  => $this->user->id
                ], $settlementEntries);

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

            AuditLogger::log($this->db, $this->user->id, 'CREATED_SALE', 'sale_master', $saleId, null, $data);

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

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

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

        $stmt = $this->db->prepare("SELECT * FROM sale_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 {
            // Fetch old items
            $oldItemsStmt = $this->db->prepare("SELECT * FROM sale_items WHERE sale_id = ?");
            $oldItemsStmt->execute([$id]);
            $oldItems = $oldItemsStmt->fetchAll();

            // 1. Restore Inventory (Temporarily)
            $restoreStmt = $this->db->prepare("
                UPDATE purchase_lots 
                SET remaining_qty = remaining_qty + ?, 
                    status = CASE WHEN remaining_qty + ? >= net_weight THEN 'available' ELSE 'partial' END
                WHERE id = ?
            ");
            foreach ($oldItems as $oldItem) {
                $restoreStmt->execute([$oldItem['weight'], $oldItem['weight'], $oldItem['lot_id']]);
            }

            // 2. Validate & Reserve NEW Inventory
            $totalGrossAmount = 0;
            foreach ($data['items'] as $item) {
                $lotStmt = $this->db->prepare("SELECT * FROM purchase_lots WHERE id = ? FOR UPDATE");
                $lotStmt->execute([$item['lot_id']]);
                $lot = $lotStmt->fetch();
                
                $saleWeight = floatval($item['weight']);
                $remainingQty = floatval($lot['remaining_qty']);

                if ($saleWeight > $remainingQty) {
                    throw new Exception("بقیہ وزن ({$remainingQty} KG) کافی نہیں ہے۔ (لاٹ #{$lot['lot_number']})", 422);
                }

                // Deduct new inventory
                $newRemaining = $remainingQty - $saleWeight;
                $newStatus = $newRemaining <= 0 ? 'sold' : 'partial';
                $this->db->prepare("UPDATE purchase_lots SET remaining_qty = ?, status = ? WHERE id = ?")
                    ->execute([$newRemaining, $newStatus, $item['lot_id']]);
                
                $totalGrossAmount = round($totalGrossAmount + floatval($item['gross_amount']), 2);
            }

            // 3. Reverse old Journals
            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);
            }

            // 4. Update Sale Master First
            $additions = $data['additions'] ?? [];
            $totalAdditions = round(array_sum(array_column($additions, 'amount')), 2);

            $amountReceived = round(floatval($data['amount_received'] ?? 0), 2);
            $netReceivable = round(floatval($data['net_receivable']), 2);
            $remainingBalance = round($netReceivable - $amountReceived, 2);

            $stmt = $this->db->prepare("
                UPDATE sale_master SET
                    voucher_date = ?, due_date = ?, khareedar_id = ?, havala_name = ?, lot_id = ?, item_id = ?,
                    sale_weight = ?, rate_basis = ?, rate = ?, gross_amount = ?, total_additions = ?,
                    net_receivable = ?, amount_received = ?, remaining_balance = ?, updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            ");
            $stmt->execute([
                $data['voucher_date'], $data['due_date'] ?? null, $data['khareedar_id'], $data['havala_name'] ?? null, 
                (!empty($data['items'][0]['lot_id']) ? $data['items'][0]['lot_id'] : 0), 
                (!empty($data['items'][0]['item_id']) ? $data['items'][0]['item_id'] : 0),
                0, 40, 0,
                $totalGrossAmount, $totalAdditions,
                $netReceivable, $amountReceived, $remainingBalance, $id
            ]);

            // Clear old items and recreate
            $this->db->prepare("DELETE FROM sale_items WHERE sale_id = ?")->execute([$id]);
            $itemStmt = $this->db->prepare("INSERT INTO sale_items (sale_id, lot_id, item_id, weight, deduction, rate, rate_basis, gross_amount) VALUES (?,?,?,?,?,?,?,?)");
            foreach ($data['items'] as $item) {
                $itemStmt->execute([
                    $id, 
                    $item['lot_id'], 
                    $item['item_id'], 
                    floatval($item['weight']), 
                    floatval($item['deduction'] ?? 0),
                    floatval($item['rate']), 
                    (int)($item['rate_basis'] ?? 40), 
                    floatval($item['gross_amount'])
                ]);
            }

            // 5. Sync Additions
            $this->db->prepare("DELETE FROM sale_additions WHERE sale_id = ?")->execute([$id]);
            $systemAccounts = $this->getSystemAccountMap();
            $addStmt = $this->db->prepare("INSERT INTO sale_additions (sale_id, account_id, description, type, percentage, amount) VALUES (?,?,?,?,?,?)");
            foreach ($additions as $add) {
                $accId = $this->resolveAdditionAccount($add['description'], $systemAccounts);
                $type = $add['type'] ?? 'plus';
                $addStmt->execute([
                    $id, $accId, $add['description'], $type,
                    isset($add['percentage']) ? floatval($add['percentage']) : null,
                    floatval($add['amount'])
                ]);
            }

            // 6. Post NEW Journals
            $khareedarName = $this->db->query("SELECT name FROM accounts WHERE id = {$data['khareedar_id']}")->fetchColumn();
            $voucherNo = $old['voucher_no'];
            $journalEntries = [];
            $journalEntries[] = ['account_id' => (int)$data['khareedar_id'], 'debit' => $netReceivable, 'credit' => 0, 'narration' => "فروخت پرچی {$voucherNo}"];
            $journalEntries[] = ['account_id' => $systemAccounts['sale'], 'debit' => 0, 'credit' => $totalGrossAmount, 'narration' => "فروخت پرچی {$voucherNo}"];
            
            foreach ($additions as $add) {
                $amt = round(floatval($add['amount']), 2);
                if ($amt <= 0) continue;
                $type = $add['type'] ?? 'plus';
                $accId = $this->resolveAdditionAccount($add['description'], $systemAccounts);
                $journalEntries[] = [
                    'account_id' => $accId, 
                    'debit' => $type === 'minus' ? $amt : 0, 
                    'credit' => $type === 'plus' ? $amt : 0, 
                    'narration' => "فروخت پرچی {$voucherNo} — {$add['description']}"
                ];
            }

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

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

            // Settlement Posting
            $paymentMethodId = !empty($data['payment_method_id']) && $data['payment_method_id'] !== 'khata' 
                ? (int)$data['payment_method_id'] : null;
            if ($paymentMethodId && $amountReceived > 0) {
                $settlementEntries = [
                    ['account_id' => $paymentMethodId, 'debit' => $amountReceived, 'credit' => 0, 'narration' => "وصولی — پرچی {$voucherNo}"],
                    ['account_id' => (int)$data['khareedar_id'], 'debit' => 0, 'credit' => $amountReceived, '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 sale_master SET settlement_journal_id = ? WHERE id = ?")->execute([$settlementJournalId, $id]);
            } else {
                $this->db->prepare("UPDATE sale_master SET settlement_journal_id = NULL WHERE id = ?")->execute([$id]);
            }

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

            $this->db->commit();
            Response::success(['id' => $id], 'فروخت پرچی کامیابی سے اپڈیٹ ہو گئی۔');
        } catch (Exception $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

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

        $this->db->beginTransaction();
        try {
            // Reverse journals
            $reversalJournalId = AccountingService::reverseJournal($this->db, $sale['journal_id'], $this->user->id);
            if ($sale['settlement_journal_id']) {
                AccountingService::reverseJournal($this->db, $sale['settlement_journal_id'], $this->user->id);
            }

            // Restore inventory for all items
            $oldItemsStmt = $this->db->prepare("SELECT * FROM sale_items WHERE sale_id = ?");
            $oldItemsStmt->execute([$id]);
            $oldItems = $oldItemsStmt->fetchAll();

            $restoreStmt = $this->db->prepare("
                UPDATE purchase_lots 
                SET remaining_qty = remaining_qty + ?, 
                    status = CASE WHEN remaining_qty + ? >= net_weight THEN 'available' ELSE 'partial' END
                WHERE id = ?
            ");
            foreach ($oldItems as $oldItem) {
                $restoreStmt->execute([$oldItem['weight'], $oldItem['weight'], $oldItem['lot_id']]);
            }

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

            AuditLogger::log($this->db, $this->user->id, 'REVERSED_SALE', 'sale_master', $id, $sale);

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

    // ---- Private Helpers ----
    private function getNextVoucherNo(): string {
        $stmt = $this->db->query("SELECT MAX(id) as max_id FROM sale_master");
        $maxId = (int)($stmt->fetch()['max_id'] ?? 0);
        return "ب-" . ($maxId + 1004);
    }

    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 resolveAdditionAccount(string $desc, array $sysAcc): int {
        $d = mb_strtolower($desc);
        if (mb_strpos($d, 'مزدوری') !== false) return $sysAcc['mazdoori'];
        if (mb_strpos($d, 'کمیشن') !== false) return $sysAcc['commission'];
        if (mb_strpos($d, 'ٹورا') !== false) return $sysAcc['tora'];
        if (mb_strpos($d, 'مارکیٹ') !== false) return $sysAcc['market_fee'];
        return $sysAcc['commission']; // Default to commission for unknown additions
    }
}
