<?php
require_once __DIR__ . '/../includes/payment_options.php';
require_once __DIR__ . '/Customers.php';
require_once __DIR__ . '/MraEisCompliance.php';
require_once __DIR__ . '/Sales.php';
require_once __DIR__ . '/SystemSettings.php';

class CreditSales {
    private $conn;

    public function __construct(PDO $conn) {
        $this->conn = $conn;
        $this->ensureSchema();
    }

    public function ensureSchema() {
        $this->conn->exec("CREATE TABLE IF NOT EXISTS credit_accounts (
            credit_id INT NOT NULL AUTO_INCREMENT,
            credit_number VARCHAR(40) NOT NULL,
            branch_id INT NOT NULL,
            transaction_id VARCHAR(80) NOT NULL,
            customer_name VARCHAR(255) NOT NULL,
            customer_phone VARCHAR(30) DEFAULT NULL,
            total_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            amount_paid DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            status ENUM('pending_controller_approval','approved','rejected','completed') NOT NULL DEFAULT 'pending_controller_approval',
            created_by INT NOT NULL,
            approved_by INT DEFAULT NULL,
            approved_at DATETIME DEFAULT NULL,
            approval_notes TEXT DEFAULT NULL,
            completed_at DATETIME DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (credit_id),
            UNIQUE KEY uq_credit_number (credit_number),
            KEY idx_credit_branch_status (branch_id, status),
            KEY idx_credit_transaction (transaction_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $this->ensureColumn('credit_accounts', 'customer_id', 'INT DEFAULT NULL');

        $this->conn->exec("CREATE TABLE IF NOT EXISTS credit_sale_items (
            id INT NOT NULL AUTO_INCREMENT,
            credit_id INT NOT NULL,
            sale_id INT NOT NULL,
            item_id INT DEFAULT NULL,
            unique_code VARCHAR(255) NOT NULL,
            item_name VARCHAR(255) DEFAULT NULL,
            unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            PRIMARY KEY (id),
            KEY idx_credit_items_credit (credit_id),
            KEY idx_credit_items_sale (sale_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $this->conn->exec("CREATE TABLE IF NOT EXISTS credit_payments (
            payment_id INT NOT NULL AUTO_INCREMENT,
            credit_id INT NOT NULL,
            branch_id INT NOT NULL,
            payment_date DATETIME NOT NULL,
            amount DECIMAL(14,2) NOT NULL,
            payment_method VARCHAR(40) NOT NULL DEFAULT 'cash',
            notes VARCHAR(255) DEFAULT NULL,
            recorded_by INT NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (payment_id),
            KEY idx_credit_payments_credit (credit_id),
            KEY idx_credit_payments_branch_date (branch_id, payment_date)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        // Weekly-report verification support, mirroring the same is_verified/report_id
        // convention used for order_payments (Order.php) and laybuy_items/sold_items/deposits
        // (Sales::verifyWeeklyReport()) — lets weekly2.php show "this week's" credit sale
        // payments once and mark them seen, without re-showing them forever.
        $this->ensureColumn('credit_payments', 'is_verified', 'TINYINT(1) NOT NULL DEFAULT 0');
        $this->ensureColumn('credit_payments', 'report_id', 'INT DEFAULT NULL');

        $this->conn->exec("CREATE TABLE IF NOT EXISTS branch_income (
            id INT NOT NULL AUTO_INCREMENT,
            branch_id INT NOT NULL,
            income_type ENUM('order','laybuy','other') NOT NULL DEFAULT 'other',
            customer_name VARCHAR(255) DEFAULT NULL,
            customer_phone VARCHAR(30) DEFAULT NULL,
            reference VARCHAR(100) DEFAULT NULL,
            description TEXT DEFAULT NULL,
            amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            payment_method VARCHAR(40) NOT NULL DEFAULT 'cash',
            recorded_by INT NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_branch_income_branch (branch_id),
            KEY idx_branch_income_type (income_type)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $this->conn->exec("CREATE TABLE IF NOT EXISTS credit_sale_deletions (
            id INT NOT NULL AUTO_INCREMENT,
            original_credit_id INT NOT NULL,
            credit_number VARCHAR(40) NOT NULL,
            branch_id INT NOT NULL,
            transaction_id VARCHAR(80) NOT NULL,
            customer_name VARCHAR(255) NOT NULL,
            customer_phone VARCHAR(30) DEFAULT NULL,
            total_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            amount_paid DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            status_at_deletion VARCHAR(50) NOT NULL,
            items_json TEXT DEFAULT NULL,
            payments_json TEXT DEFAULT NULL,
            deletion_notes TEXT DEFAULT NULL,
            deleted_by INT NOT NULL,
            deleted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_csd_branch (branch_id),
            KEY idx_csd_date (deleted_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $this->conn->exec("CREATE TABLE IF NOT EXISTS creditors (
            creditor_id INT NOT NULL AUTO_INCREMENT,
            creditor_name VARCHAR(255) NOT NULL,
            creditor_phone VARCHAR(30) DEFAULT NULL,
            description VARCHAR(255) DEFAULT NULL,
            amount_owed DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            amount_paid DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
            status ENUM('open','settled') NOT NULL DEFAULT 'open',
            created_by INT DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (creditor_id),
            KEY idx_creditors_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
    }


    private function ensureColumn(string $table, string $column, string $definition): void {
        $stmt = $this->conn->prepare('SHOW TABLES LIKE :table_name');
        $stmt->execute([':table_name' => $table]);
        if (!$stmt->fetchColumn()) {
            return;
        }
        $stmt = $this->conn->query("SHOW COLUMNS FROM `{$table}` LIKE " . $this->conn->quote($column));
        if (!$stmt->fetchColumn()) {
            $this->conn->exec("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}");
        }
    }
    public function createCreditSale(array $data, array $items) {
        $total = round((float)($data['total_amount'] ?? 0), 2);
        $paid = round(max(0, (float)($data['amount_paid'] ?? 0)), 2);
        if ($total <= 0 || $paid > $total) {
            throw new InvalidArgumentException('Credit sale total and opening payment are invalid.');
        }

        $creditNumber = $data['credit_number'] ?? $this->generateCreditNumber();
        $stmt = $this->conn->prepare("INSERT INTO credit_accounts (
            credit_number, branch_id, transaction_id, customer_id, customer_name, customer_phone,
            total_amount, amount_paid, balance, status, created_by
        ) VALUES (
            :credit_number, :branch_id, :transaction_id, :customer_id, :customer_name, :customer_phone,
            :total_amount, :amount_paid, :balance, 'pending_controller_approval', :created_by
        )");
        $stmt->execute([
            ':credit_number' => $creditNumber,
            ':branch_id' => (int)$data['branch_id'],
            ':transaction_id' => $data['transaction_id'],
            ':customer_id' => !empty($data['customer_id']) ? (int)$data['customer_id'] : null,
            ':customer_name' => $data['customer_name'],
            ':customer_phone' => $data['customer_phone'] ?: null,
            ':total_amount' => $total,
            ':amount_paid' => 0,
            ':balance' => $total,
            ':created_by' => (int)$data['created_by'],
        ]);
        $creditId = (int)$this->conn->lastInsertId();

        $itemStmt = $this->conn->prepare("INSERT INTO credit_sale_items (credit_id, sale_id, item_id, unique_code, item_name, unit_price)
            VALUES (:credit_id, :sale_id, :item_id, :unique_code, :item_name, :unit_price)");
        foreach ($items as $item) {
            $itemStmt->execute([
                ':credit_id' => $creditId,
                ':sale_id' => (int)$item['sale_id'],
                ':item_id' => $item['item_id'] ?? null,
                ':unique_code' => $item['unique_code'],
                ':item_name' => $item['item_name'] ?? null,
                ':unit_price' => (float)$item['unit_price'],
            ]);
        }

        if (!empty($data['customer_id'])) {
            (new Customers($this->conn))->recordInvoice((int)$data['customer_id'], (int)$data['branch_id'], 'credit_sale', $creditNumber, $data['transaction_id'], $creditId, null, $total, (int)$data['created_by'], 'Credit sale invoice');
        }

        if ($paid > 0) {
            $this->recordPayment($creditId, $paid, $data['payment_method'] ?? 'cash', 'Opening payment', (int)$data['created_by'], true);
        }

        return $creditId;
    }

    public function recordPayment($creditId, $amount, $paymentMethod, $notes, $userId, $recalculate = true) {
        $credit = $this->getCreditById((int)$creditId);
        if (!$credit) {
            throw new RuntimeException('Credit sale not found.');
        }
        if (!in_array($credit['status'], ['approved', 'pending_controller_approval'], true)) {
            throw new RuntimeException('Payments can only be posted to pending or approved credit sales.');
        }
        $amount = round((float)$amount, 2);
        if ($amount <= 0 || $amount > (float)$credit['balance']) {
            throw new InvalidArgumentException('Payment amount must be greater than zero and not exceed the balance.');
        }
        $paymentMethod = normalizePaymentMethodValue($paymentMethod);
        $stmt = $this->conn->prepare("INSERT INTO credit_payments (credit_id, branch_id, payment_date, amount, payment_method, notes, recorded_by)
            VALUES (:credit_id, :branch_id, NOW(), :amount, :payment_method, :notes, :recorded_by)");
        $stmt->execute([
            ':credit_id' => (int)$creditId,
            ':branch_id' => (int)$credit['branch_id'],
            ':amount' => $amount,
            ':payment_method' => $paymentMethod,
            ':notes' => trim((string)$notes) ?: null,
            ':recorded_by' => (int)$userId,
        ]);
        $paymentId = (int)$this->conn->lastInsertId();
        if (!empty($credit['customer_id'])) {
            (new Customers($this->conn))->recordPayment((int)$credit['customer_id'], (int)$credit['branch_id'], 'credit_sale', (string)$credit['credit_number'], (string)$credit['transaction_id'], (int)$creditId, null, $amount, $paymentMethod, (int)$userId, trim((string)$notes) ?: 'Credit sale payment');
        }
        if ($recalculate) {
            $this->recalculateCredit((int)$creditId);
        }
        return $paymentId;
    }

    public function prepareFinalEisReceipt(int $creditId, int $userId, string $paymentMethod = 'cash'): array {
        $credit = $this->getCreditById($creditId);
        if (!$credit) {
            throw new RuntimeException('Credit sale not found.');
        }
        if ((float)$credit['balance'] > 0 || ($credit['status'] ?? '') !== 'completed') {
            return ['status' => 'not_final', 'message' => 'Credit sale still has an outstanding balance.'];
        }

        $settings = new SystemSettings();
        if (!$settings->eisEnabled()) {
            return ['status' => 'disabled', 'message' => 'MRA EIS is disabled.'];
        }

        $eis = new MraEisCompliance($this->conn);
        $outbox = $this->getEisOutbox((string)$credit['transaction_id']);
        $transactionId = (string)$credit['transaction_id'];

        if (!$outbox) {
            $sales = new Sales($this->conn);
            $newTransactionId = $sales->generateMraEisInvoiceNumber((int)$credit['branch_id'], date('Y-m-d H:i:s'));
            if ($newTransactionId !== $transactionId) {
                $sales->replaceTransactionId($transactionId, $newTransactionId, (int)$credit['branch_id']);
                $this->conn->prepare('UPDATE credit_accounts SET transaction_id = :new_txn WHERE credit_id = :credit_id')
                    ->execute([':new_txn' => $newTransactionId, ':credit_id' => $creditId]);
                try {
                    $this->conn->prepare('UPDATE customer_ledger SET transaction_id = :new_txn WHERE credit_id = :credit_id AND transaction_id = :old_txn')
                        ->execute([':new_txn' => $newTransactionId, ':credit_id' => $creditId, ':old_txn' => $transactionId]);
                } catch (Throwable $e) {
                    error_log('Unable to sync credit customer ledger transaction ID: ' . $e->getMessage());
                }
                $transactionId = $newTransactionId;
            }
        }

        $paymentMethod = normalizePaymentMethodValue($paymentMethod);
        $this->conn->prepare('UPDATE sold_items SET payment_method = :method, is_ims_income = 1, sold_at = COALESCE(:completed_at, NOW()) WHERE transaction_id = :txn AND branch_id = :branch_id')
            ->execute([
                ':method' => $paymentMethod,
                ':completed_at' => $credit['completed_at'] ?: null,
                ':txn' => $transactionId,
                ':branch_id' => (int)$credit['branch_id'],
            ]);

        $eisResult = $eis->prepareSale($transactionId, (int)$credit['branch_id'], $userId, [
            'credit_id' => $creditId,
        ]);

        $submitResult = null;
        if (($eisResult['status'] ?? '') === 'ready' && !$settings->eisOfflineMode() && !empty($eisResult['id'])) {
            try {
                $submitResult = $eis->submitOutbox((int)$eisResult['id'], $userId);
            } catch (Throwable $e) {
                error_log('Credit sale EIS submission failed for ' . $transactionId . ': ' . $e->getMessage());
                $submitResult = ['success' => false, 'message' => $e->getMessage()];
            }
        }

        return [
            'status' => $submitResult && !empty($submitResult['success']) ? 'submitted' : ($eisResult['status'] ?? 'prepared'),
            'transaction_id' => $transactionId,
            'eis_result' => $eisResult,
            'submit_result' => $submitResult,
        ];
    }

    public function getEisOutbox(string $transactionId): ?array {
        try {
            $stmt = $this->conn->prepare("SELECT * FROM mra_eis_outbox WHERE entity_type = 'sale' AND source_key = :txn ORDER BY id DESC LIMIT 1");
            $stmt->execute([':txn' => trim($transactionId)]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            return $row ?: null;
        } catch (Throwable $e) {
            return null;
        }
    }

    public function approveCredit($creditId, $status, $userId, $notes = '') {
        if (!in_array($status, ['approved', 'rejected'], true)) {
            throw new InvalidArgumentException('Invalid credit approval status.');
        }
        $stmt = $this->conn->prepare("UPDATE credit_accounts SET status = :status, approved_by = :approved_by, approved_at = NOW(), approval_notes = :notes WHERE credit_id = :credit_id AND status = 'pending_controller_approval'");
        $stmt->execute([
            ':status' => $status,
            ':approved_by' => (int)$userId,
            ':notes' => trim((string)$notes) ?: null,
            ':credit_id' => (int)$creditId,
        ]);
        return $stmt->rowCount() > 0;
    }

    public function recalculateCredit($creditId) {
        $stmt = $this->conn->prepare("SELECT total_amount, COALESCE((SELECT SUM(amount) FROM credit_payments WHERE credit_id = credit_accounts.credit_id), 0) AS paid FROM credit_accounts WHERE credit_id = :credit_id");
        $stmt->execute([':credit_id' => (int)$creditId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$row) {
            return;
        }
        $paid = round((float)$row['paid'], 2);
        $balance = max(0, round((float)$row['total_amount'] - $paid, 2));
        $statusSql = $balance <= 0 ? ", status = 'completed', completed_at = COALESCE(completed_at, NOW())" : '';
        $update = $this->conn->prepare("UPDATE credit_accounts SET amount_paid = :paid, balance = :balance $statusSql WHERE credit_id = :credit_id");
        $update->execute([':paid' => $paid, ':balance' => $balance, ':credit_id' => (int)$creditId]);
        if ($balance <= 0) {
            $this->conn->prepare("UPDATE sold_items si JOIN credit_sale_items csi ON csi.sale_id = si.sale_id SET si.is_ims_income = 1 WHERE csi.credit_id = :credit_id")
                ->execute([':credit_id' => (int)$creditId]);
        }
    }

    public function getCreditById($creditId) {
        $stmt = $this->conn->prepare("SELECT ca.*, b.branch_name, u.username AS created_by_name
            FROM credit_accounts ca
            LEFT JOIN branches b ON b.branch_id = ca.branch_id
            LEFT JOIN users u ON u.user_id = ca.created_by
            WHERE ca.credit_id = :credit_id");
        $stmt->execute([':credit_id' => (int)$creditId]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    public function updateCredit(int $creditId, array $data): bool {
        $credit = $this->getCreditById($creditId);
        if (!$credit) {
            throw new RuntimeException('Credit account not found.');
        }

        $isPendingNoPayments = $credit['status'] === 'pending_controller_approval'
            && (float)$credit['amount_paid'] == 0;

        $fields       = [];
        $params       = [':credit_id' => $creditId];
        $itemsUpdated = false;

        // Customer info — always editable
        if (isset($data['customer_name']) && trim($data['customer_name']) !== '') {
            $fields[] = 'customer_name = :customer_name';
            $params[':customer_name'] = trim($data['customer_name']);
        }
        if (array_key_exists('customer_phone', $data)) {
            $fields[] = 'customer_phone = :customer_phone';
            $params[':customer_phone'] = trim($data['customer_phone']) ?: null;
        }

        // Item price updates — only while pending with no payments
        if ($isPendingNoPayments && !empty($data['items']) && is_array($data['items'])) {
            $updateItem = $this->conn->prepare(
                'UPDATE credit_sale_items SET unit_price = :price WHERE id = :id AND credit_id = :credit_id'
            );
            foreach ($data['items'] as $itemUpdate) {
                $itemId = (int)($itemUpdate['id'] ?? 0);
                $price  = round((float)($itemUpdate['unit_price'] ?? 0), 2);
                if ($itemId > 0 && $price > 0) {
                    $updateItem->execute([':price' => $price, ':id' => $itemId, ':credit_id' => $creditId]);
                    $itemsUpdated = true;
                }
            }
            if ($itemsUpdated) {
                // Recalculate total from updated item prices
                $sumStmt = $this->conn->prepare(
                    'SELECT COALESCE(SUM(unit_price), 0) FROM credit_sale_items WHERE credit_id = :credit_id'
                );
                $sumStmt->execute([':credit_id' => $creditId]);
                $newTotal = round((float)$sumStmt->fetchColumn(), 2);
                if ($newTotal > 0) {
                    $fields[] = 'total_amount = :total_amount';
                    $fields[] = 'balance = :balance';
                    $params[':total_amount'] = $newTotal;
                    $params[':balance']      = $newTotal;
                }
            }
        }

        // Manual total override — pending + no payments only, and only when items were not updated
        if (!$itemsUpdated && $isPendingNoPayments && isset($data['total_amount'])) {
            $total = round((float)$data['total_amount'], 2);
            if ($total > 0) {
                $fields[] = 'total_amount = :total_amount';
                $fields[] = 'balance = :balance';
                $params[':total_amount'] = $total;
                $params[':balance']      = $total;
            }
        }

        if (!$fields) {
            return $itemsUpdated;
        }
        $stmt = $this->conn->prepare(
            'UPDATE credit_accounts SET ' . implode(', ', $fields) . ' WHERE credit_id = :credit_id'
        );
        $stmt->execute($params);
        return $stmt->rowCount() > 0 || $itemsUpdated;
    }

    public function deleteCredit(int $creditId, int $deletedBy = 0, string $notes = ''): bool {
        $credit = $this->getCreditById($creditId);
        if (!$credit) {
            throw new RuntimeException('Credit account not found.');
        }
        if ($credit['status'] !== 'pending_controller_approval') {
            throw new RuntimeException('Only pending credit accounts can be deleted this way.');
        }
        if ((float)$credit['amount_paid'] > 0) {
            throw new RuntimeException('Credit accounts with recorded payments cannot be deleted this way.');
        }
        return $this->_deleteCreditWithAudit($credit, $deletedBy, $notes);
    }

    public function controllerDeleteCredit(int $creditId, int $deletedBy, string $notes = ''): bool {
        $credit = $this->getCreditById($creditId);
        if (!$credit) {
            throw new RuntimeException('Credit account not found.');
        }
        return $this->_deleteCreditWithAudit($credit, $deletedBy, $notes);
    }

    private function _deleteCreditWithAudit(array $credit, int $deletedBy, string $notes): bool {
        $creditId = (int)$credit['credit_id'];
        $items    = $this->getCreditItems($creditId);
        $payments = $this->getPayments($creditId);

        $this->conn->beginTransaction();
        try {
            // Audit record — snapshot everything before deletion
            $this->conn->prepare("
                INSERT INTO credit_sale_deletions
                    (original_credit_id, credit_number, branch_id, transaction_id,
                     customer_name, customer_phone, total_amount, amount_paid, balance,
                     status_at_deletion, items_json, payments_json, deletion_notes, deleted_by)
                VALUES
                    (:cid, :cnum, :bid, :txn,
                     :cname, :cphone, :total, :paid, :balance,
                     :status, :items, :payments, :notes, :by)
            ")->execute([
                ':cid'      => $creditId,
                ':cnum'     => $credit['credit_number'],
                ':bid'      => (int)$credit['branch_id'],
                ':txn'      => $credit['transaction_id'],
                ':cname'    => $credit['customer_name'],
                ':cphone'   => $credit['customer_phone'],
                ':total'    => (float)$credit['total_amount'],
                ':paid'     => (float)$credit['amount_paid'],
                ':balance'  => (float)$credit['balance'],
                ':status'   => $credit['status'],
                ':items'    => json_encode($items),
                ':payments' => json_encode($payments),
                ':notes'    => trim($notes) ?: null,
                ':by'       => $deletedBy,
            ]);

            // Return items to shelf
            $this->conn->prepare(
                "DELETE FROM sold_items WHERE transaction_id = :txn AND branch_id = :bid"
            )->execute([':txn' => $credit['transaction_id'], ':bid' => (int)$credit['branch_id']]);

            // Clean up EIS outbox if present
            try {
                $this->conn->prepare(
                    "DELETE FROM mra_eis_outbox WHERE source_key = :txn AND branch_id = :bid AND entity_type = 'sale'"
                )->execute([':txn' => $credit['transaction_id'], ':bid' => (int)$credit['branch_id']]);
            } catch (Throwable $e) { /* table may not exist in all installs */ }

            // Remove credit records
            $this->conn->prepare("DELETE FROM credit_sale_items WHERE credit_id = :id")->execute([':id' => $creditId]);
            $this->conn->prepare("DELETE FROM credit_payments WHERE credit_id = :id")->execute([':id' => $creditId]);
            $this->conn->prepare("DELETE FROM credit_accounts WHERE credit_id = :id")->execute([':id' => $creditId]);

            $this->conn->commit();
            return true;
        } catch (Throwable $e) {
            $this->conn->rollBack();
            throw $e;
        }
    }

    public function getDeletedCredits(?int $branchId, int $limit = 100): array {
        if ($branchId) {
            $stmt = $this->conn->prepare("
                SELECT csd.*, u.username AS deleted_by_name
                FROM credit_sale_deletions csd
                LEFT JOIN users u ON u.user_id = csd.deleted_by
                WHERE csd.branch_id = :bid
                ORDER BY csd.deleted_at DESC
                LIMIT " . max(1, $limit));
            $stmt->execute([':bid' => $branchId]);
        } else {
            $stmt = $this->conn->query("
                SELECT csd.*, u.username AS deleted_by_name, b.branch_name
                FROM credit_sale_deletions csd
                LEFT JOIN users u ON u.user_id = csd.deleted_by
                LEFT JOIN branches b ON b.branch_id = csd.branch_id
                ORDER BY csd.deleted_at DESC
                LIMIT " . max(1, $limit));
        }
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getCreditItems($creditId) {
        $stmt = $this->conn->prepare("SELECT * FROM credit_sale_items WHERE credit_id = :credit_id ORDER BY id ASC");
        $stmt->execute([':credit_id' => (int)$creditId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getPayments($creditId) {
        $stmt = $this->conn->prepare("SELECT cp.*, u.username AS recorded_by_name FROM credit_payments cp LEFT JOIN users u ON u.user_id = cp.recorded_by WHERE cp.credit_id = :credit_id ORDER BY cp.payment_date ASC, cp.payment_id ASC");
        $stmt->execute([':credit_id' => (int)$creditId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Unverified credit sale payments for a branch, for the weekly verification report.
     * Shown for reference/reconciliation only — NOT added to the weekly income total,
     * since a credit sale's full value is already recognized as income exactly once
     * (via its linked sold_items row flipping to is_ims_income=1) when the credit is
     * fully paid off. Counting these installments again here would double it.
     */
    public function getUnverifiedPayments($branchId) {
        $stmt = $this->conn->prepare("
            SELECT cp.payment_id, cp.credit_id, cp.amount, cp.payment_method, cp.notes, cp.payment_date,
                   ca.credit_number, ca.customer_name, ca.status
            FROM credit_payments cp
            JOIN credit_accounts ca ON ca.credit_id = cp.credit_id
            WHERE cp.branch_id = :branch_id AND cp.is_verified = 0
            ORDER BY cp.payment_date ASC
        ");
        $stmt->execute([':branch_id' => (int)$branchId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getCredits($branchId = null, $status = null, $limit = 200) {
        $where = [];
        $params = [];
        if ($branchId) {
            $where[] = 'ca.branch_id = :branch_id';
            $params[':branch_id'] = (int)$branchId;
        }
        if ($status) {
            $where[] = 'ca.status = :status';
            $params[':status'] = $status;
        }
        $sql = "SELECT ca.*, b.branch_name FROM credit_accounts ca LEFT JOIN branches b ON b.branch_id = ca.branch_id";
        if ($where) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }
        $sql .= ' ORDER BY ca.created_at DESC LIMIT ' . max(1, (int)$limit);
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getSummary($branchId = null) {
        $where = $branchId ? 'WHERE branch_id = :branch_id' : '';
        $stmt = $this->conn->prepare("SELECT COUNT(*) AS total_accounts, COALESCE(SUM(total_amount),0) AS total_credit, COALESCE(SUM(amount_paid),0) AS total_paid, COALESCE(SUM(balance),0) AS total_balance, COALESCE(SUM(balance),0) AS outstanding_balance, SUM(status IN ('pending_controller_approval','approved')) AS open_accounts, SUM(status='pending_controller_approval') AS pending_approval, SUM(status='pending_controller_approval') AS pending_accounts FROM credit_accounts $where");
        $stmt->execute($branchId ? [':branch_id' => (int)$branchId] : []);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: ['total_accounts'=>0,'total_credit'=>0,'total_paid'=>0,'total_balance'=>0,'outstanding_balance'=>0,'open_accounts'=>0,'pending_approval'=>0,'pending_accounts'=>0];
    }

    public function recordIncome(int $branchId, string $type, array $data, int $userId): int {
        if (!in_array($type, ['order', 'laybuy', 'other'], true)) {
            throw new InvalidArgumentException('Invalid income type.');
        }
        $amount = round((float)($data['amount'] ?? 0), 2);
        if ($amount <= 0) {
            throw new InvalidArgumentException('Amount must be greater than zero.');
        }
        $stmt = $this->conn->prepare("
            INSERT INTO branch_income
                (branch_id, income_type, customer_name, customer_phone, reference, description, amount, payment_method, recorded_by)
            VALUES
                (:branch_id, :type, :customer_name, :customer_phone, :reference, :description, :amount, :payment_method, :recorded_by)
        ");
        $stmt->execute([
            ':branch_id'      => $branchId,
            ':type'           => $type,
            ':customer_name'  => trim($data['customer_name'] ?? '') ?: null,
            ':customer_phone' => trim($data['customer_phone'] ?? '') ?: null,
            ':reference'      => trim($data['reference'] ?? '') ?: null,
            ':description'    => trim($data['description'] ?? '') ?: null,
            ':amount'         => $amount,
            ':payment_method' => normalizePaymentMethodValue($data['payment_method'] ?? 'cash'),
            ':recorded_by'    => $userId,
        ]);
        return (int)$this->conn->lastInsertId();
    }

    public function getIncomeRecords(?int $branchId, int $limit = 150): array {
        if ($branchId) {
            $stmt = $this->conn->prepare("
                SELECT bi.*, u.username AS recorded_by_name
                FROM branch_income bi
                LEFT JOIN users u ON u.user_id = bi.recorded_by
                WHERE bi.branch_id = :branch_id
                ORDER BY bi.created_at DESC
                LIMIT " . max(1, $limit));
            $stmt->execute([':branch_id' => $branchId]);
        } else {
            $stmt = $this->conn->query("
                SELECT bi.*, u.username AS recorded_by_name, b.branch_name
                FROM branch_income bi
                LEFT JOIN users u ON u.user_id = bi.recorded_by
                LEFT JOIN branches b ON b.branch_id = bi.branch_id
                ORDER BY bi.created_at DESC
                LIMIT " . max(1, $limit));
        }
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function deleteIncome(int $id, ?int $branchId): bool {
        if ($branchId) {
            $stmt = $this->conn->prepare("DELETE FROM branch_income WHERE id = :id AND branch_id = :branch_id");
            $stmt->execute([':id' => $id, ':branch_id' => $branchId]);
        } else {
            $stmt = $this->conn->prepare("DELETE FROM branch_income WHERE id = :id");
            $stmt->execute([':id' => $id]);
        }
        return $stmt->rowCount() > 0;
    }

    public function getCreditors() {
        $stmt = $this->conn->query("SELECT * FROM creditors ORDER BY created_at DESC LIMIT 200");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function generateCreditNumber() {
        return 'CR-' . date('Ymd') . '-' . strtoupper(substr(str_replace('.', '', uniqid('', true)), -6));
    }
}