<?php
require_once 'Logger.php';
require_once 'Notifications.php';
require_once __DIR__ . '/Sales.php';
require_once __DIR__ . '/MraEisCompliance.php';
require_once __DIR__ . '/SystemSettings.php';

class Laybuy {
    private $db;
    private $logger;
    private $notifications;
    private $hasPaymentMethodColumn = null;

    public function __construct($db, $logger = null) {
        $this->db = $db;
        $this->logger = $logger;
        $this->notifications = new Notifications($db, $logger);
        $this->ensureLaybuyPaymentColumns();
        $this->ensureLaybuyCodeColumns();
        $this->ensureAllRecordsAreLaybuys();
    }

    // Customer loans were merged into laybuys; self-heals any leftover
    // 'loan' rows on every request so the app can treat every record as
    // a laybuy without a data migration step.
    private function ensureAllRecordsAreLaybuys() {
        try {
            $this->db->exec("UPDATE loans_laybuys SET method = 'laybuy' WHERE method = 'loan'");
        } catch (Throwable $e) {
            error_log("Unable to normalize loans_laybuys.method to laybuy: " . $e->getMessage());
        }
    }

    private function ensureLaybuyPaymentColumns() {
        try {
            $stmt = $this->db->query("SHOW COLUMNS FROM laybuy_items LIKE 'payment_method'");
            if ($stmt->fetchColumn()) {
                $this->hasPaymentMethodColumn = true;
                return;
            }
            $this->db->exec("ALTER TABLE laybuy_items ADD COLUMN payment_method VARCHAR(40) NOT NULL DEFAULT 'cash' AFTER body_amount");
            $this->hasPaymentMethodColumn = true;
        } catch (Throwable $e) {
            $this->hasPaymentMethodColumn = false;
            error_log("Unable to ensure laybuy payment columns: " . $e->getMessage());
        }
    }

    private function ensureLaybuyCodeColumns() {
        try {
            $stmt = $this->db->query("SHOW COLUMNS FROM laybuy_codes LIKE 'unique_code'");
            $column = $stmt->fetch(PDO::FETCH_ASSOC);
            // items.unique_code is VARCHAR(50); laybuy_codes was created as VARCHAR(11)
            // which truncates/rejects longer item codes (e.g. 'GGC/2500003640021')
            // and throws a "Data too long for column" error when assigning a final-payment code.
            if ($column && stripos($column['Type'], 'varchar(50)') === false) {
                $this->db->exec("ALTER TABLE laybuy_codes MODIFY unique_code VARCHAR(50) NOT NULL");
            }
        } catch (Throwable $e) {
            error_log("Unable to ensure laybuy_codes.unique_code column width: " . $e->getMessage());
        }
    }

    public function register($body_amount, $record_id, $payment_date, $total_paid, $notes, $branch_id, $payment_method = 'cash') {
        $paymentId = $this->insertPayment($body_amount, $record_id, $payment_date, $notes, $branch_id, $payment_method);
        return (bool)$paymentId;
    }

    public function recordPayment($record_id, $body_amount, $payment_date, $notes, $branch_id, $payment_method = 'cash', array $codes = []) {
        $record = $this->getLaybuyRecord($record_id);
        if (!$record) {
            return ['success' => false, 'message' => 'Laybuy record not found.'];
        }
        if ((int)$record['branch_id'] !== (int)$branch_id) {
            return ['success' => false, 'message' => 'This laybuy belongs to another branch.'];
        }
        if (strtolower((string)$record['status']) === 'completed') {
            return ['success' => false, 'message' => 'This laybuy is already complete.'];
        }

        $body_amount = round((float)$body_amount, 2);
        $laybuyTotal = round((float)$record['amount'], 2);
        $currentPaid = round((float)$record['total_paid'], 2);
        $balance = round($laybuyTotal - $currentPaid, 2);
        $newTotalPaid = round($currentPaid + $body_amount, 2);

        if ($body_amount <= 0) {
            return ['success' => false, 'message' => 'Payment amount must be greater than zero.'];
        }
        if ($newTotalPaid - $laybuyTotal > 0.009) {
            return ['success' => false, 'message' => 'Payment exceeds the outstanding laybuy balance.'];
        }

        $isFinalPayment = abs($newTotalPaid - $laybuyTotal) < 0.01;
        // Every laybuy is tied to a real inventory item, so the final payment always
        // requires assigning item unique code(s) that total the full selling price.
        $requiresCompletionCodes = $isFinalPayment;
        $cleanCodes = [];
        if ($requiresCompletionCodes) {
            try {
                $cleanCodes = $this->normalizeCompletionCodes($codes);
            } catch (InvalidArgumentException $e) {
                return ['success' => false, 'message' => $e->getMessage()];
            }
            if (empty($cleanCodes)) {
                return ['success' => false, 'message' => 'Assign at least one item code for the final laybuy payment.'];
            }
            $codeValueTotal = array_sum(array_column($cleanCodes, 'value'));
            if (abs(round($codeValueTotal, 2) - $laybuyTotal) >= 0.01) {
                return ['success' => false, 'message' => 'Assigned code values must equal the full laybuy selling price.'];
            }
        }

        $settings = null;
        $sales = null;
        $transactionId = null;
        $eisCompliance = null;
        $imsOnly = false;

        try {
            if ($requiresCompletionCodes) {
                $sales = new Sales($this->db, $this->logger);
                $settings = new SystemSettings($this->db);
                $imsOnly = $settings->imsOnly();
                $transactionId = $settings->eisEnabled() && !$imsOnly
                    ? $sales->generateMraEisInvoiceNumber($branch_id, $payment_date)
                    : $sales->generateLocalTransactionNumber();
                $eisCompliance = $imsOnly ? null : new MraEisCompliance($this->db);
            }

            $this->db->beginTransaction();

            $paymentId = $this->insertPayment($body_amount, $record_id, $payment_date, $notes, $branch_id, $payment_method);
            $this->setLaybuyPaidAndStatus($record_id, $newTotalPaid, $isFinalPayment ? 'completed' : null);

            $eisResult = null;
            if ($requiresCompletionCodes) {
                foreach ($cleanCodes as $code) {
                    $this->insertLaybuyCode($record_id, $payment_date, $code['unique_code'], $code['value'], $code['notes'] ?: $notes);
                    // is_ims_income = false: the real income for a laybuy is already counted via
                    // laybuy_items.body_amount across its payment history — this sold_items row only
                    // records which physical item was handed over, so it must not double-count in the
                    // weekly "Unverified Sales" report (matches the same convention used for order
                    // completions in controller_action.php's complete_order_code action).
                    $sales->createSale(
                        $payment_date,
                        $code['unique_code'],
                        $code['value'],
                        $branch_id,
                        $_SESSION['user_id'],
                        $transactionId,
                        $code['value'],
                        $record['customer_name'] ?? null,
                        $record['customer_contact'] ?? null,
                        false,
                        null,
                        'B2C',
                        null,
                        null,
                        false
                    );
                }
                if (!$imsOnly) {
                    $eisResult = $eisCompliance->prepareSale($transactionId, $branch_id, $_SESSION['user_id'], [
                        'laybuy_record_id' => (int)$record_id,
                        'laybuy_payment_id' => (int)$paymentId,
                    ]);
                }
            }

            $this->db->commit();

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

            if ($isFinalPayment) {
                $message = $imsOnly
                    ? 'Final payment recorded. Laybuy completed and item code(s) assigned in IMS Only mode.'
                    : 'Final payment recorded. Laybuy completed and full-value EIS sale prepared.';
            } else {
                $message = 'Payment recorded successfully.';
            }
            if ($requiresCompletionCodes && !empty($submitResult['success'])) {
                $message .= ' MRA EIS receipt submitted.';
            }

            return [
                'success' => true,
                'message' => $message,
                'final_payment' => $isFinalPayment,
                'transaction_id' => $transactionId,
                'payment_id' => $paymentId,
                'eis_result' => $eisResult,
                'submit_result' => $submitResult,
            ];
        } catch (Throwable $e) {
            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }
            error_log('Laybuy payment failed: ' . $e->getMessage());
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    private function getLaybuyRecord($record_id) {
        $stmt = $this->db->prepare("SELECT * FROM loans_laybuys WHERE record_id = :record_id LIMIT 1");
        $stmt->execute([':record_id' => (int)$record_id]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    private function normalizeCompletionCodes(array $codes) {
        $normalized = [];
        $seen = [];
        foreach ($codes as $code) {
            $uniqueCode = trim((string)($code['unique_code'] ?? ''));
            $value = round((float)($code['value'] ?? 0), 2);
            $notes = trim((string)($code['notes'] ?? ''));
            if ($uniqueCode === '' && $value == 0.0) {
                continue;
            }
            if ($uniqueCode === '') {
                throw new InvalidArgumentException('Each final-payment item must have a unique code.');
            }
            if ($value <= 0) {
                throw new InvalidArgumentException('Each final-payment item value must be greater than zero.');
            }
            $key = strtolower($uniqueCode);
            if (isset($seen[$key])) {
                throw new InvalidArgumentException('Duplicate item code on final payment: ' . $uniqueCode);
            }
            $seen[$key] = true;
            $normalized[] = ['unique_code' => $uniqueCode, 'value' => $value, 'notes' => $notes];
        }
        return $normalized;
    }

    private function insertPayment($body_amount, $record_id, $payment_date, $notes, $branch_id, $payment_method) {
        $recorded_by = (int)$_SESSION['user_id'];
        if ($this->hasPaymentMethodColumn) {
            try {
                $stmt = $this->db->prepare("INSERT INTO laybuy_items (body_amount, payment_method, record_id, payment_date, notes, branch_id, recorded_by) VALUES (:body_amount, :payment_method, :record_id, :payment_date, :notes, :branch_id, :recorded_by)");
                $stmt->execute([
                    ':body_amount' => $body_amount,
                    ':payment_method' => $payment_method,
                    ':record_id' => (int)$record_id,
                    ':payment_date' => $payment_date,
                    ':notes' => $notes,
                    ':branch_id' => (int)$branch_id,
                    ':recorded_by' => $recorded_by,
                ]);
            } catch (Throwable $e) {
                if (stripos($e->getMessage(), 'payment_method') === false) {
                    throw $e;
                }
                $this->hasPaymentMethodColumn = false;
            }
        }

        if (!$this->hasPaymentMethodColumn) {
            $stmt = $this->db->prepare("INSERT INTO laybuy_items (body_amount, record_id, payment_date, notes, branch_id, recorded_by) VALUES (:body_amount, :record_id, :payment_date, :notes, :branch_id, :recorded_by)");
            $stmt->execute([
                ':body_amount' => $body_amount,
                ':record_id' => (int)$record_id,
                ':payment_date' => $payment_date,
                ':notes' => $notes,
                ':branch_id' => (int)$branch_id,
                ':recorded_by' => $recorded_by,
            ]);
        }

        $paymentId = $this->db->lastInsertId();
        $message = "Laybuy payment " . $paymentId . " for record " . $record_id . " has been recorded.";
        $this->notifications->createNotification($branch_id, $message, 'Admin');
        return $paymentId;
    }

    private function setLaybuyPaidAndStatus($record_id, $total_paid, $status = null) {
        $query = "UPDATE loans_laybuys SET total_paid = :total_paid";
        $params = [':total_paid' => $total_paid, ':record_id' => (int)$record_id];
        if ($status !== null) {
            $query .= ", status = :status";
            $params[':status'] = $status;
        }
        $query .= " WHERE record_id = :record_id";
        $stmt = $this->db->prepare($query);
        $stmt->execute($params);
    }

    private function insertLaybuyCode($record_id, $txn_date, $unique_code, $value, $notes) {
        $stmt = $this->db->prepare("INSERT INTO laybuy_codes (record_id, date_added, unique_code, value, notes, recorded_by) VALUES (:record_id, :date_added, :unique_code, :value, :notes, :recorded_by)");
        $stmt->execute([
            ':record_id' => (int)$record_id,
            ':date_added' => $txn_date,
            ':unique_code' => $unique_code,
            ':value' => $value,
            ':notes' => $notes,
            ':recorded_by' => (int)$_SESSION['user_id'],
        ]);
    }

    public function AssignCodes($record_id, $txn_date, $unique_code, $value, $notes, $branch_id) {
        try {
            $this->db->beginTransaction();
            $recorded_by = $_SESSION['user_id'];

            $stmt0 = $this->db->prepare("SELECT item_id, current_location_id FROM items WHERE unique_code = ? LIMIT 1");
            $stmt0->execute([$unique_code]);
            $item = $stmt0->fetch(PDO::FETCH_ASSOC);
            if (!$item) {
                throw new Exception("Item with unique code {$unique_code} was not found.");
            }
            if ((int)$item['current_location_id'] !== (int)$branch_id) {
                throw new Exception("Item {$unique_code} is not assigned to this branch.");
            }

            $stmtSold = $this->db->prepare("SELECT sale_id FROM sold_items WHERE unique_code = ? LIMIT 1");
            $stmtSold->execute([$unique_code]);
            if ($stmtSold->fetch(PDO::FETCH_ASSOC)) {
                throw new Exception("Item {$unique_code} has already been sold or assigned to a laybuy record.");
            }

            $stmt1 = $this->db->prepare("INSERT INTO laybuy_codes (record_id, date_added, unique_code, value, notes, recorded_by) VALUES (?, ?, ?, ?, ?, ?)");
            $stmt1->execute([$record_id, $txn_date, $unique_code, $value, $notes, $recorded_by]);

            // sold_by/unit_price/total_price/sold_at/customer_* must be populated here because
            // sales reports (Sales::getSoldItemsByBranch/getSoldItemsDetailed, weekly reports)
            // INNER JOIN sold_items to users on sold_by - a NULL sold_by silently drops the row
            // from those reports even though the code was successfully assigned.
            $record = $this->getLaybuyRecord($record_id);
            $transaction_id = "txnLAYBUY" . $record_id;
            // is_ims_income = 0: the real income for a laybuy is already counted via
            // laybuy_items.body_amount across its payment history — this row only records
            // which physical item was handed over, so it must not double-count in the
            // weekly "Unverified Sales" report.
            $stmt2 = $this->db->prepare("INSERT INTO sold_items (item_id, unique_code, branch_id, transaction_id, sold_by, unit_price, total_price, sold_at, customer_name, customer_phone, is_ims_income) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0)");
            $stmt2->execute([
                $item['item_id'],
                $unique_code,
                $branch_id,
                $transaction_id,
                $recorded_by,
                $value,
                $value,
                $txn_date,
                $record['customer_name'] ?? null,
                $record['customer_contact'] ?? null,
            ]);

            $this->db->commit();
            return ['success' => true, 'message' => "Code {$unique_code} assigned successfully."];
        } catch (Throwable $e) {
            $this->db->rollBack();
            error_log("Laybuy::AssignCodes failed for record {$record_id}, code {$unique_code}: " . $e->getMessage());
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    public function assignMultipleCodes($recordId, $txnDate, array $codes, $notes, $branchId) {
        try {
            $normalized = [];
            foreach ($codes as $row) {
                $uniqueCode = trim((string)($row['unique_code'] ?? ''));
                $value = round((float)($row['value'] ?? 0), 2);
                if ($uniqueCode === '' || $value <= 0) {
                    throw new Exception('Every row requires a unique code and a positive value.');
                }
                if (isset($normalized[$uniqueCode])) {
                    throw new Exception('Code ' . $uniqueCode . ' was entered more than once.');
                }
                $normalized[$uniqueCode] = $value;
            }
            if (!$normalized) {
                throw new Exception('Add at least one unique code.');
            }

            $this->db->beginTransaction();
            $recordedBy = (int)$_SESSION['user_id'];
            $record = $this->getLaybuyRecord($recordId);
            $transactionId = 'txnLAYBUY' . (int)$recordId;
            $itemStmt = $this->db->prepare('SELECT item_id, current_location_id FROM items WHERE unique_code = ? LIMIT 1');
            $soldStmt = $this->db->prepare('SELECT sale_id FROM sold_items WHERE unique_code = ? LIMIT 1');
            $codeStmt = $this->db->prepare('INSERT INTO laybuy_codes (record_id, date_added, unique_code, value, notes, recorded_by) VALUES (?, ?, ?, ?, ?, ?)');
            $saleStmt = $this->db->prepare('INSERT INTO sold_items (item_id, unique_code, branch_id, transaction_id, sold_by, unit_price, total_price, sold_at, customer_name, customer_phone, is_ims_income) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0)');

            foreach ($normalized as $uniqueCode => $value) {
                $itemStmt->execute([$uniqueCode]);
                $item = $itemStmt->fetch(PDO::FETCH_ASSOC);
                if (!$item) {
                    throw new Exception('Item with unique code ' . $uniqueCode . ' was not found.');
                }
                if ((int)$item['current_location_id'] !== (int)$branchId) {
                    throw new Exception('Item ' . $uniqueCode . ' is not assigned to this branch.');
                }
                $soldStmt->execute([$uniqueCode]);
                if ($soldStmt->fetch(PDO::FETCH_ASSOC)) {
                    throw new Exception('Item ' . $uniqueCode . ' has already been sold or assigned.');
                }
                $codeStmt->execute([(int)$recordId, $txnDate, $uniqueCode, $value, $notes, $recordedBy]);
                $saleStmt->execute([$item['item_id'], $uniqueCode, (int)$branchId, $transactionId, $recordedBy, $value, $value, $txnDate, $record['customer_name'] ?? null, $record['customer_contact'] ?? null]);
            }

            $this->db->commit();
            return ['success' => true, 'message' => count($normalized) . ' code(s) assigned successfully.'];
        } catch (Throwable $e) {
            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }
            error_log('Laybuy::assignMultipleCodes failed for record ' . $recordId . ': ' . $e->getMessage());
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    function getLayBuyForWeek($reportId){
        $query = "SELECT * FROM loans_laybuys INNER JOIN laybuy_items ON loans_laybuys.record_id=laybuy_items.record_id WHERE laybuy_items.report_id=$reportId ";
        $stmt = $this->db->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    function getBranchLayBuyStats($userBranchId, $startDate, $endDate){
        $query = "SELECT * FROM laybuy_items WHERE branch_id = :userBranchId && payment_date BETWEEN :startDate AND :endDate";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':userBranchId', $userBranchId);
        $stmt->bindParam(':startDate', $startDate);
        $stmt->bindParam(':endDate', $endDate);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    function getBranchExpensesStats($userBranchId, $startDate, $endDate){
        $query = "SELECT * FROM operational_expenses WHERE branch_id = :userBranchId && expense_date BETWEEN :startDate AND :endDate";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':userBranchId', $userBranchId);
        $stmt->bindParam(':startDate', $startDate);
        $stmt->bindParam(':endDate', $endDate);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function login($username, $password) {
        $sql = "SELECT * FROM users WHERE username = :username";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':username', $username);
        $stmt->execute();
        $user = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($user && password_verify($password, $user['password'])) {
            return $user;
        }
        return false;
    }

    public function getUserById($id) {
        $sql = "SELECT * FROM users WHERE user_id = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function updateLayBuyHeader($body_amount, $record_id, $total_paid) {
        $new_total_paid=$total_paid+$body_amount;
        $query = "UPDATE loans_laybuys SET total_paid = :total_paid WHERE record_id = :record_id";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':total_paid', $new_total_paid);
        $stmt->bindParam(':record_id', $record_id);
        return $stmt->execute();
    }

    public function verifyWeeklyPayments($reportId,$recorded_by){
        $query = "UPDATE laybuy_items SET verified = 1 WHERE recorded_by = :recorded_by";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':recorded_by', $recorded_by);
        return $stmt->execute();
    }

    public function deleteUser($id) {
        $query = "DELETE FROM users WHERE user_id = :id";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':id', $id);
        return $stmt->execute();
    }

    public function getPaymentDetails($recordId) {
        $query = "SELECT * FROM laybuy_items INNER JOIN users ON laybuy_items.recorded_by=users.user_id WHERE laybuy_items.record_id=:record_id ORDER BY laybuy_items.payment_date ASC, laybuy_items.id ASC";
        $stmt = $this->db->prepare($query);
        $stmt->execute([':record_id' => $recordId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function deletePaymentDetails($payment_id) {
        $query = "DELETE FROM laybuy_items WHERE id = :payment_id";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':payment_id', $payment_id);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function deleteCodesDetails($unique_code) {
        $query = "DELETE s, l FROM sold_items s JOIN laybuy_codes l ON s.unique_code = l.unique_code WHERE s.unique_code = :unique_code";
        $stmt = $this->db->prepare($query);
        $stmt->execute([':unique_code' => $unique_code]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getUserBranch($userId) {
        $query = "SELECT branch_id FROM users WHERE user_id = :user_id";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':user_id', $userId);
        $stmt->execute();
        return $stmt->fetchColumn();
    }

    public function isUserInBranch($userId, $branchId) {
        $query = "SELECT COUNT(*) FROM users WHERE user_id = :user_id AND branch_id = :branch_id";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':branch_id', $branchId);
        $stmt->execute();
        return $stmt->fetchColumn() > 0;
    }

    public function getWeeklyPaymentDetails($recordId, $branchId) {
        // assigned_codes: the item unique code(s) handed over when this laybuy was completed
        // (only populated on the final payment's row, via laybuy_codes.record_id).
        $query = "SELECT loans_laybuys.*, laybuy_items.*,
                    (SELECT GROUP_CONCAT(lc.unique_code SEPARATOR ', ')
                     FROM laybuy_codes lc WHERE lc.record_id = loans_laybuys.record_id) AS assigned_codes
                  FROM loans_laybuys
                  INNER JOIN laybuy_items ON loans_laybuys.record_id=laybuy_items.record_id
                  WHERE loans_laybuys.branch_id= :branch_id && laybuy_items.verified=0";
        $stmt = $this->db->prepare($query);
        $stmt->execute([':branch_id' => $branchId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getCodesDetails($recordId) {
        $query = "SELECT * FROM laybuy_codes INNER JOIN items ON laybuy_codes.unique_code=items.unique_code WHERE laybuy_codes.record_id=:recordId";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':recordId', $recordId);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function AssignCode($record_id, $unique_code, $branch_id, $user_id , $payment_date){
        $sql = "SELECT item_id FROM items WHERE unique_code = :unique_code";
        $stmt = $this->db->prepare($sql);
        $stmt->bindParam(':unique_code', $unique_code);
        $stmt->execute();
        $item = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$item) {
            return false;
        }

        $transaction_id ="txnLAYBUY".$record_id;
        // is_ims_income = 0: the real income for a laybuy is already counted via
        // laybuy_items.body_amount across its payment history — this row only records
        // which physical item was handed over, so it must not double-count in the
        // weekly "Unverified Sales" report.
        $sql = "INSERT INTO sold_items (item_id, unique_code, branch_id, transaction_id, is_ims_income) VALUES (:item_id,:unique_code,:branch_id,:transaction_id,0)";
        $stmt = $this->db->prepare($sql);
        $result = $stmt->execute([
            ':item_id' => $item['item_id'],
            ':unique_code' => $unique_code,
            ':branch_id' => $branch_id,
            ':transaction_id' => $transaction_id,
        ]);

        $sql = "UPDATE loans_laybuys SET unique_code = :unique_code WHERE record_id = :record_id";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':unique_code' => $unique_code, ':record_id' => $record_id]);
        return $result;
    }
}
?>
