
<?php
require_once 'Logger.php';
require_once 'Tracking.php';
require_once 'Notifications.php';
require_once __DIR__ . '/SystemSettings.php';
require_once __DIR__ . '/../includes/payment_options.php';

class Sales {
    private $conn;
    private $logger;
    private $tracking;
    private $notifications;

    public function __construct($conn, $logger = null) {
        $this->conn = $conn;
        $this->logger = $logger;
        $this->tracking = new Tracking($conn, $logger);
        $this->notifications = new Notifications($conn, $logger);
        $this->ensureVatReliefColumns();
        $this->ensureEisSiteProductColumns();
        $this->ensurePaymentColumns();
        $this->ensureCustomerColumns();
        $this->ensureVerifiedColumn();
    }


    private function ensureVerifiedColumn() {
        if (!$this->tableExists('sold_items')) {
            return;
        }
        $stmt = $this->conn->query('SHOW COLUMNS FROM sold_items LIKE \'is_verified\'');
        if (!$stmt->fetchColumn()) {
            $this->conn->exec('ALTER TABLE sold_items ADD COLUMN is_verified TINYINT(1) AS (transaction_id LIKE \'%\_VERIFIED\_%\') STORED');
        }
    }


    private function ensurePaymentColumns() {
        if (!$this->tableExists('sold_items')) {
            return;
        }
        $stmt = $this->conn->query("SHOW COLUMNS FROM sold_items LIKE 'payment_method'");
        if (!$stmt->fetchColumn()) {
            $this->conn->exec("ALTER TABLE sold_items ADD COLUMN payment_method VARCHAR(40) NOT NULL DEFAULT 'cash'");
        }
    }


    private function ensureCustomerColumns() {
        if (!$this->tableExists('sold_items')) {
            return;
        }
        $stmt = $this->conn->query("SHOW COLUMNS FROM sold_items LIKE 'customer_id'");
        if (!$stmt->fetchColumn()) {
            $this->conn->exec('ALTER TABLE sold_items ADD COLUMN customer_id INT DEFAULT NULL');
        }
    }
    private function ensureEisSiteProductColumns() {
        foreach (['items', 'sold_items'] as $table) {
            if (!$this->tableExists($table)) {
                continue;
            }
            $stmt = $this->conn->query("SHOW COLUMNS FROM `$table` LIKE 'eis_site_product_id'");
            if (!$stmt->fetchColumn()) {
                $this->conn->exec("ALTER TABLE `$table` ADD COLUMN `eis_site_product_id` INT(11) NULL DEFAULT NULL");
            }
            try {
                $this->conn->exec("CREATE INDEX idx_{$table}_eis_site_product ON `$table` (eis_site_product_id)");
            } catch (Exception $e) {
                // Index already exists on some MySQL/MariaDB versions.
            }
        }

        // sold_items.unique_code has no index despite being queried on every sale
        // (isItemSold(), duplicate-sale checks across branches) - without it, each
        // lookup is a full table scan that gets slower as sales accumulate, and the
        // same missing index makes the EIS batch-mapping page's LEFT JOIN sold_items
        // (checking which items are unsold) scan the whole table too.
        if ($this->tableExists('sold_items')) {
            try {
                $this->conn->exec("CREATE INDEX idx_sold_items_unique_code ON sold_items (unique_code)");
            } catch (Exception $e) {
                // Index already exists on some MySQL/MariaDB versions.
            }
        }
    }

    private function ensureVatReliefColumns() {
        if (!$this->tableExists('sold_items')) {
            return;
        }
        $stmt = $this->conn->query('SHOW COLUMNS FROM sold_items LIKE \'vat_relief\'');
        if (!$stmt->fetchColumn()) {
            $this->conn->exec('ALTER TABLE sold_items ADD COLUMN vat_relief TINYINT(1) NOT NULL DEFAULT 0');
        }
        $stmt = $this->conn->query('SHOW COLUMNS FROM sold_items LIKE \'buyer_tin\'');
        if (!$stmt->fetchColumn()) {
            $this->conn->exec('ALTER TABLE sold_items ADD COLUMN buyer_tin VARCHAR(30) DEFAULT NULL');
        }
        $stmt = $this->conn->query('SHOW COLUMNS FROM sold_items LIKE \'eis_transaction_type\'');
        if (!$stmt->fetchColumn()) {
            $this->conn->exec("ALTER TABLE sold_items ADD COLUMN eis_transaction_type ENUM('B2C', 'B2B') NOT NULL DEFAULT 'B2C'");
        }
        $stmt = $this->conn->query('SHOW COLUMNS FROM sold_items LIKE \'buyer_authorization_code\'');
        if (!$stmt->fetchColumn()) {
            $this->conn->exec('ALTER TABLE sold_items ADD COLUMN buyer_authorization_code VARCHAR(100) DEFAULT NULL');
        }
        foreach ([
            'vat5_certificate_number' => 'VARCHAR(120) DEFAULT NULL',
            'vat5_certificate_details' => 'TEXT DEFAULT NULL',
            'original_unit_price' => 'DECIMAL(15,2) DEFAULT NULL',
            'discount_amount' => 'DECIMAL(15,2) NOT NULL DEFAULT 0',
            'is_ims_income' => 'TINYINT(1) NOT NULL DEFAULT 1',
        ] as $column => $definition) {
            $stmt = $this->conn->query("SHOW COLUMNS FROM sold_items LIKE '" . $column . "'");
            if (!$stmt->fetchColumn()) {
                $this->conn->exec('ALTER TABLE sold_items ADD COLUMN `' . $column . '` ' . $definition);
            }
        }
    }

    public function isItemSold($uniqueCode) {
        try {
            $sql = "SELECT si.*, i.item_name, i.item_details
                    FROM sold_items si
                    JOIN items i ON si.item_id = i.item_id
                    WHERE si.unique_code = :unique_code
                    LIMIT 1";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':unique_code', $uniqueCode);
            $stmt->execute();

            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            return $result ? $result : false;
        } catch (Exception $e) {
            error_log("Error checking if item is sold: " . $e->getMessage());
            return false;
        }
    }

    public function generateMraEisInvoiceNumber($branchId, $saleDate = null) {
        $date = $this->normalizeSaleDateTime($saleDate ?: date('Y-m-d H:i:s'));
        $julianDate = $this->toJulianDate($date);
        $terminal = $this->getBranchMraEisTerminal($branchId);

        if (!$terminal) {
            throw new Exception("No activated MRA EIS terminal is assigned to this branch.");
        }

        $taxpayerId = $this->activationResponseNumber($terminal, 'taxpayerId');
        $terminalPosition = $this->activationResponseNumber($terminal, 'terminalPosition');

        if ($taxpayerId === null || $terminalPosition === null) {
            throw new Exception("MRA EIS terminal activation response is missing taxpayerId or terminalPosition for invoice number generation.");
        }

        $prefix = implode('-', [
            $this->base10ToMraBase64($taxpayerId),
            $this->base10ToMraBase64($terminalPosition),
            $this->base10ToMraBase64($julianDate),
        ]);

        for ($attempt = 0; $attempt < 10; $attempt++) {
            $sequence = $this->nextDailyTransactionSequence($branchId, $prefix) + $attempt;
            $invoiceNumber = $prefix . '-' . $this->base10ToMraBase64($sequence);

            if (!$this->transactionIdentifierExists($invoiceNumber)) {
                return $invoiceNumber;
            }
        }

        throw new Exception("Unable to generate a unique MRA EIS invoice number for this sale.");
    }

    public function generateLocalTransactionNumber() {
        do {
            $transactionId = strtoupper(str_replace('.', '', uniqid('TXN-', true)));
        } while ($this->transactionIdentifierExists($transactionId));

        return $transactionId;
    }

    private function transactionIdentifierExists($transactionId) {
        $stmt = $this->conn->prepare('SELECT COUNT(*) FROM sold_items WHERE transaction_id = :transaction_id');
        $stmt->execute([':transaction_id' => $transactionId]);
        if ((int)$stmt->fetchColumn() > 0) {
            return true;
        }

        if (!$this->tableExists('mra_eis_outbox')) {
            return false;
        }
        $stmt = $this->conn->prepare("
            SELECT COUNT(*)
            FROM mra_eis_outbox
            WHERE entity_type = 'sale'
              AND source_key = :transaction_id
        ");
        $stmt->execute([':transaction_id' => $transactionId]);
        return (int)$stmt->fetchColumn() > 0;
    }

    private function nextDailyTransactionSequence($branchId, $invoicePrefix) {
        $likePattern = $invoicePrefix . '-%';
        $stmt = $this->conn->prepare("
            SELECT COUNT(DISTINCT transaction_id) + 1
            FROM sold_items
            WHERE branch_id = :branch_id
              AND transaction_id LIKE :like_pattern
        ");
        $stmt->execute([
            ':branch_id' => (int)$branchId,
            ':like_pattern' => $likePattern,
        ]);

        return max(1, (int)$stmt->fetchColumn());
    }

    private function getBranchMraEisTerminal($branchId) {
        if (!$this->tableExists('mra_eis_terminals')) {
            return null;
        }

        $stmt = $this->conn->prepare("
            SELECT *
            FROM mra_eis_terminals
            WHERE branch_id = :branch_id
              AND status = 'activated'
              AND activation_response IS NOT NULL
              AND activation_response != ''
            ORDER BY (environment = 'production') DESC, updated_at DESC, id DESC
            LIMIT 1
        ");
        $stmt->execute([':branch_id' => (int)$branchId]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    private function activationResponseNumber(array $terminal, $key) {
        $decoded = json_decode((string)($terminal['activation_response'] ?? ''), true);
        if (!is_array($decoded)) {
            return null;
        }

        $value = $this->firstNestedValue($decoded, $key);
        if ($value === null || !is_numeric($value)) {
            return null;
        }

        return (int)$value;
    }

    private function firstNestedValue($value, $key) {
        if (!is_array($value)) {
            return null;
        }

        foreach ($value as $currentKey => $currentValue) {
            if ((string)$currentKey === (string)$key && !is_array($currentValue)) {
                return $currentValue;
            }

            if (is_array($currentValue)) {
                $found = $this->firstNestedValue($currentValue, $key);
                if ($found !== null) {
                    return $found;
                }
            }
        }

        return null;
    }

    private function toJulianDate($date) {
        $timestamp = strtotime($date);
        if (!$timestamp) {
            $timestamp = time();
        }

        $year = (int)date('Y', $timestamp);
        $month = (int)date('n', $timestamp);
        $day = (int)date('j', $timestamp);

        if ($month <= 2) {
            $year -= 1;
            $month += 12;
        }

        $a = intdiv($year, 100);
        $b = 2 - $a + intdiv($a, 4);

        return (int)(floor(365.25 * ($year + 4716)) + floor(30.6001 * ($month + 1)) + $day + $b - 1524);
    }

    private function base10ToMraBase64($number) {
        $number = (int)$number;
        if ($number === 0) {
            return 'A';
        }

        $base64Chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
        $result = '';

        while ($number > 0) {
            $remainder = $number % 64;
            $result = $base64Chars[$remainder] . $result;
            $number = intdiv($number, 64);
        }

        return $result;
    }

    public function createSale($saleDate, $uniqueCode, $saleAmount, $branchId, $userId, $transactionId, $effectivePrice, $customerName = null, $customerPhone = null, $vatRelief = false, $buyerTin = null, $eisTransactionType = 'B2C', $buyerAuthorizationCode = null, $vat5CertificateNumber = null, $isImsIncome = true, $paymentMethod = 'cash', $customerId = null, $vat5CertificateDetails = null) {
        $paymentMethod = normalizePaymentMethodValue($paymentMethod);
        if ($uniqueCode === null) {
            $uniqueCode = 'CUSTOM-' . uniqid();
        }
        try {
            $saleDateTime = $this->normalizeSaleDateTime($saleDate);
            $soldItem = $this->isItemSold($uniqueCode);
            if ($soldItem) {
                throw new Exception("This item ({$uniqueCode}) has already been sold on " . $soldItem['sold_at']);
            }

            $sql = "SELECT item_id, current_location_id, eis_site_product_id FROM items WHERE unique_code = :unique_code";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':unique_code', $uniqueCode);
            $stmt->execute();
            $item = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$item) {
                throw new Exception("Item with unique code {$uniqueCode} not found");
            }

            if ($item['current_location_id'] != $branchId) {
                throw new Exception("Item {$uniqueCode} is not assigned to this branch");
            }

            $eisSiteProductId = isset($item['eis_site_product_id']) && $item['eis_site_product_id'] !== null
                ? (int)$item['eis_site_product_id']
                : null;

            $sysSettings = new SystemSettings();
            if ($eisSiteProductId === null) {
                if ($sysSettings->eisEnabled()) {
                    throw new Exception(
                        "Item {$uniqueCode} has not been mapped to an MRA EIS product batch. " .
                        "Go to Admin ? EIS Stock Batch Mapping and assign this product before selling."
                    );
                } elseif (!$sysSettings->imsOnly()) {
                    throw new Exception(
                        "Item {$uniqueCode} has not been mapped to an MRA EIS product batch. " .
                        "Enable 'IMS Only' in Settings to allow selling unmapped items, or map this item first."
                    );
                }
            }

            $sql = "INSERT INTO sold_items (
                        branch_id,
                        item_id,
                        unique_code,
                        unit_price,
                        total_price,
                        sold_by,
                        transaction_id,
                        sold_at,
                        customer_name,
                        customer_phone,
                        customer_id,
                        vat_relief,
                        buyer_tin,
                        eis_transaction_type,
                        buyer_authorization_code,
                        vat5_certificate_number
                        ,vat5_certificate_details
                        ,payment_method
                        ,original_unit_price
                        ,discount_amount
                        ,eis_site_product_id
                        ,is_ims_income
                    ) VALUES (
                        :branch_id,
                        :item_id,
                        :unique_code,
                        :effective_price,
                        :total_price,
                        :sold_by,
                        :transaction_id,
                        :sale_date,
                        :customer_name,
                        :customer_phone,
                        :customer_id,
                        :vat_relief,
                        :buyer_tin,
                        :eis_transaction_type,
                        :buyer_authorization_code,
                        :vat5_certificate_number
                        ,:vat5_certificate_details
                        ,:payment_method
                        ,:original_unit_price
                        ,:discount_amount
                        ,:eis_site_product_id
                        ,:is_ims_income
                    )";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':branch_id', $branchId);
            $stmt->bindParam(':item_id', $item['item_id']);
            $stmt->bindParam(':unique_code', $uniqueCode);
            $stmt->bindParam(':effective_price', $effectivePrice);
            $stmt->bindParam(':total_price', $effectivePrice);
            $stmt->bindParam(':sold_by', $userId);
            $stmt->bindParam(':transaction_id', $transactionId);
            $stmt->bindParam(':sale_date', $saleDateTime);
            $stmt->bindParam(':customer_name', $customerName, PDO::PARAM_STR);
            $stmt->bindParam(':customer_phone', $customerPhone, PDO::PARAM_STR);
            $stmt->bindValue(':customer_id', $customerId ? (int)$customerId : null, $customerId ? PDO::PARAM_INT : PDO::PARAM_NULL);
            $stmt->bindValue(':vat_relief', $vatRelief ? 1 : 0, PDO::PARAM_INT);
            $stmt->bindValue(':buyer_tin', $buyerTin ?: null, $buyerTin ? PDO::PARAM_STR : PDO::PARAM_NULL);
            $stmt->bindValue(':eis_transaction_type', strtoupper((string)$eisTransactionType) === 'B2B' ? 'B2B' : 'B2C');
            $stmt->bindValue(':buyer_authorization_code', $buyerAuthorizationCode ?: null, $buyerAuthorizationCode ? PDO::PARAM_STR : PDO::PARAM_NULL);
            $stmt->bindValue(':vat5_certificate_number', $vat5CertificateNumber ?: null, $vat5CertificateNumber ? PDO::PARAM_STR : PDO::PARAM_NULL);
            $vat5CertificateDetailsJson = is_array($vat5CertificateDetails) ? json_encode($vat5CertificateDetails) : ($vat5CertificateDetails ?: null);
            $stmt->bindValue(':vat5_certificate_details', $vat5CertificateDetailsJson, $vat5CertificateDetailsJson ? PDO::PARAM_STR : PDO::PARAM_NULL);
            $stmt->bindValue(':payment_method', $paymentMethod, PDO::PARAM_STR);
            $stmt->bindValue(':original_unit_price', round((float)$saleAmount, 2));
            $stmt->bindValue(':discount_amount', round(max(0, (float)$saleAmount - (float)$effectivePrice), 2));
            $stmt->bindValue(':eis_site_product_id', $eisSiteProductId, PDO::PARAM_INT);
            $stmt->bindValue(':is_ims_income', $isImsIncome ? 1 : 0, PDO::PARAM_INT);

            $result = $stmt->execute();
            if (!$result) {
                $errorInfo = $stmt->errorInfo();
                error_log("Database error executing sale insert for {$uniqueCode}: " . $errorInfo[2]);
                throw new Exception("Database error while recording sale for item {$uniqueCode}: " . $errorInfo[2]);
            }
            $saleId = $this->conn->lastInsertId();

            if (!$sysSettings->imsOnly() && $eisSiteProductId !== null) {
                // Atomically decrement EIS stock quantity. The quantity > 0 guard
                // prevents negative stock under concurrent sales of the last unit.
                $deductStmt = $this->conn->prepare("
                    UPDATE `mra_eis_site_products`
                    SET    `quantity` = `quantity` - 1
                    WHERE  `id`       = :id
                      AND  `quantity` > 0
                ");
                $deductStmt->execute([':id' => $eisSiteProductId]);
                if ($deductStmt->rowCount() === 0) {
                    throw new Exception(
                        "MRA EIS stock for item {$uniqueCode} is exhausted (quantity reached zero). " .
                        "Sync the site products and contact your administrator."
                    );
                }
            }

            $message = "Sale transaction ".$transactionId." has been recorded for branch ".$branchId.".";
            $this->notifications->createNotification($branchId, $message, 'Admin');

            if ($this->logger) {
                error_log("Sales: About to log CREATE for sale_id: $saleId");
                $saleData = [
                    'unique_code' => $uniqueCode,
                    'sale_amount' => $saleAmount,
                    'branch_id' => $branchId,
                    'user_id' => $userId,
                    'transaction_id' => $transactionId,
                    'effective_price' => $effectivePrice,
                    'sold_at' => $saleDateTime,
                    'customer_name' => $customerName,
                    'customer_phone' => $customerPhone,
                    'customer_id' => $customerId,
                    'vat_relief' => (bool)$vatRelief,
                    'buyer_tin' => $buyerTin,
                    'eis_transaction_type' => $eisTransactionType,
                    'buyer_authorization_code' => $buyerAuthorizationCode,
                    'payment_method' => $paymentMethod
                ];
                $result = $this->logger->logSale('CREATE', $saleId, $saleData);
                error_log("Sales: Log result for CREATE: " . ($result ? 'SUCCESS' : 'FAILED'));
            } else {
                error_log("Sales: Logger is null, cannot log CREATE");
            }

            $this->tracking->logEvent(
                $item['item_id'],
                $uniqueCode,
                'sold',
                $branchId,
                null,
                $userId,
                [
                    'sale_date' => $saleDateTime,
                    'sale_amount' => $saleAmount,
                    'effective_price' => $effectivePrice,
                    'customer_name' => $customerName,
                    'customer_phone' => $customerPhone,
                    'customer_id' => $customerId,
                    'transaction_id' => $transactionId,
                    'vat_relief' => (bool)$vatRelief,
                    'buyer_tin' => $buyerTin,
                    'eis_transaction_type' => $eisTransactionType,
                    'buyer_authorization_code' => $buyerAuthorizationCode,
                    'payment_method' => $paymentMethod
                ]
            );

            return (int)$saleId;
        } catch (Exception $e) {
            error_log("Error creating sale: " . $e->getMessage());
            throw $e;
        }
    }

    public function getRecentBranchTransactions($branchId, $limit = 10) {
        $limit = max(1, min(50, (int)$limit));
        $stmt = $this->conn->prepare("
            SELECT
                si.transaction_id,
                MIN(si.sale_id) AS sale_id,
                MAX(si.sold_at) AS sold_at,
                MAX(si.customer_name) AS customer_name,
                MAX(si.customer_phone) AS customer_phone,
                MAX(si.buyer_tin) AS buyer_tin,
                MAX(si.eis_transaction_type) AS eis_transaction_type,
                COUNT(*) AS item_count,
                ROUND(SUM(si.total_price), 2) AS transaction_total,
                MAX(outbox.id) AS eis_outbox_id,
                MAX(outbox.status) AS eis_status,
                MAX(outbox.validation_url) AS eis_validation_url,
                MAX(outbox.last_error) AS eis_last_error
            FROM sold_items si
            LEFT JOIN mra_eis_outbox outbox
                ON outbox.entity_type = 'sale'
                AND outbox.source_key = si.transaction_id
            WHERE si.branch_id = :branch_id
              AND si.transaction_id IS NOT NULL
              AND si.transaction_id != ''
            GROUP BY si.transaction_id
            ORDER BY MAX(si.sold_at) DESC, MAX(si.sale_id) DESC
            LIMIT " . $limit
        );
        $stmt->bindValue(':branch_id', (int)$branchId, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    public function replaceTransactionId($oldTransactionId, $newTransactionId, $branchId = null) {
        $oldTransactionId = trim((string)$oldTransactionId);
        $newTransactionId = trim((string)$newTransactionId);

        if ($oldTransactionId === '' || $newTransactionId === '' || $oldTransactionId === $newTransactionId) {
            return false;
        }

        try {
            $this->conn->beginTransaction();

            $duplicateSql = "SELECT COUNT(*) FROM sold_items WHERE transaction_id = :new_transaction_id AND transaction_id != :old_transaction_id";
            $duplicateParams = [
                ':new_transaction_id' => $newTransactionId,
                ':old_transaction_id' => $oldTransactionId,
            ];
            if ($branchId !== null) {
                $duplicateSql .= " AND branch_id = :branch_id";
                $duplicateParams[':branch_id'] = (int)$branchId;
            }

            $duplicateStmt = $this->conn->prepare($duplicateSql);
            $duplicateStmt->execute($duplicateParams);
            if ((int)$duplicateStmt->fetchColumn() > 0) {
                throw new Exception("Transaction ID {$newTransactionId} is already in use.");
            }

            $updateSql = "UPDATE sold_items SET transaction_id = :new_transaction_id WHERE transaction_id = :old_transaction_id";
            $updateParams = [
                ':new_transaction_id' => $newTransactionId,
                ':old_transaction_id' => $oldTransactionId,
            ];
            if ($branchId !== null) {
                $updateSql .= " AND branch_id = :branch_id";
                $updateParams[':branch_id'] = (int)$branchId;
            }

            $updateStmt = $this->conn->prepare($updateSql);
            $updateStmt->execute($updateParams);
            $updatedRows = $updateStmt->rowCount();

            if ($this->tableExists('mra_eis_outbox')) {
                $outboxConflict = $this->conn->prepare("
                    SELECT COUNT(*)
                    FROM mra_eis_outbox
                    WHERE entity_type = 'sale'
                      AND source_key = :new_transaction_id
                      AND source_key != :old_transaction_id
                ");
                $outboxConflict->execute([
                    ':new_transaction_id' => $newTransactionId,
                    ':old_transaction_id' => $oldTransactionId,
                ]);

                if ((int)$outboxConflict->fetchColumn() === 0) {
                    $outboxStmt = $this->conn->prepare("
                        UPDATE mra_eis_outbox
                        SET source_key = :new_transaction_id
                        WHERE entity_type = 'sale'
                          AND source_key = :old_transaction_id
                    ");
                    $outboxStmt->execute([
                        ':new_transaction_id' => $newTransactionId,
                        ':old_transaction_id' => $oldTransactionId,
                    ]);
                }
            }

            if ($this->tableExists('mra_eis_compliance_events')) {
                $eventStmt = $this->conn->prepare("
                    UPDATE mra_eis_compliance_events
                    SET source_key = :new_transaction_id
                    WHERE entity_type = 'sale'
                      AND source_key = :old_transaction_id
                ");
                $eventStmt->execute([
                    ':new_transaction_id' => $newTransactionId,
                    ':old_transaction_id' => $oldTransactionId,
                ]);
            }

            $this->conn->commit();
            return $updatedRows > 0;
        } catch (Exception $e) {
            if ($this->conn->inTransaction()) {
                $this->conn->rollBack();
            }
            error_log("Error replacing sale transaction ID: " . $e->getMessage());
            throw $e;
        }
    }

    private function normalizeSaleDateTime($saleDate) {
        $value = trim((string)$saleDate);
        if ($value === '' || substr($value, 0, 10) === '0000-00-00') {
            return date('Y-m-d H:i:s');
        }

        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $value)) {
            return $value . ' ' . date('H:i:s');
        }

        $timestamp = strtotime($value);
        if (!$timestamp) {
            return date('Y-m-d H:i:s');
        }

        return date('Y-m-d H:i:s', $timestamp);
    }

    private function tableExists($tableName) {
        try {
            $stmt = $this->conn->prepare("SHOW TABLES LIKE :table_name");
            $stmt->execute([':table_name' => $tableName]);
            return (bool)$stmt->fetchColumn();
        } catch (Exception $e) {
            return false;
        }
    }

    public function getSaleByUniqueCode($uniqueCode) {
        try {
            $sql = "SELECT s.*, i.item_name, i.item_details
                    FROM sales s
                    JOIN items i ON s.item_id = i.item_id
                    WHERE s.unique_code = :unique_code
                    ORDER BY s.sale_id DESC LIMIT 1";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':unique_code', $uniqueCode);
            $stmt->execute();
            return $stmt->fetch(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting sale by unique code: " . $e->getMessage());
            return false;
        }
    }

    public function getAllSales() {
        try {
            $sql = "SELECT s.*, i.item_name, i.item_details, b.branch_name, u.username as sold_by
                    FROM sales s
                    JOIN items i ON s.item_id = i.item_id
                    JOIN branches b ON s.branch_id = b.branch_id
                    JOIN users u ON s.created_by = u.user_id
                    ORDER BY s.sale_date DESC";
            $stmt = $this->conn->prepare($sql);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting all sales: " . $e->getMessage());
            return false;
        }
    }

    public function getSalesByBranch($branchId, $startDate = null, $endDate = null) {
        try {
            $sql = "SELECT si.*, i.item_name, i.item_details, u.username as sold_by
                    FROM sold_items si
                    JOIN items i ON si.item_id = i.item_id
                    JOIN users u ON si.sold_by = u.user_id
                    WHERE si.branch_id = :branch_id";

            if ($startDate && $endDate) {
                $sql .= " AND DATE(si.sold_at) BETWEEN :start_date AND :end_date";
            }

            $sql .= " ORDER BY si.sold_at DESC";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':branch_id', $branchId);

            if ($startDate && $endDate) {
                $stmt->bindParam(':start_date', $startDate);
                $stmt->bindParam(':end_date', $endDate);
            }

            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting sales by branch: " . $e->getMessage());
            return false;
        }
    }

    public function getSalesByDateRange($startDate, $endDate, $branchId = null) {
        try {
            $sql = "SELECT s.*, i.item_name, i.item_details, b.branch_name, u.username as sold_by
                    FROM sales s
                    JOIN items i ON s.item_id = i.item_id
                    JOIN branches b ON s.branch_id = b.branch_id
                    JOIN users u ON s.created_by = u.user_id
                    WHERE s.sale_date BETWEEN :start_date AND :end_date";

            if ($branchId) {
                $sql .= " AND s.branch_id = :branch_id";
            }

            $sql .= " ORDER BY s.sale_date DESC";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':start_date', $startDate);
            $stmt->bindParam(':end_date', $endDate);

            if ($branchId) {
                $stmt->bindParam(':branch_id', $branchId);
            }

            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting sales by date range: " . $e->getMessage());
            return false;
        }
    }

    public function generateSalesReport($startDate, $endDate, $branchId = null) {
        try {
            $sql = "SELECT
                        COUNT(*) as total_sales,
                        SUM(sale_amount) as total_amount,
                        AVG(sale_amount) as average_sale,
                        MIN(sale_amount) as min_sale,
                        MAX(sale_amount) as max_sale";

            if ($branchId) {
                $sql .= ", b.branch_name";
            }

            $sql .= " FROM sales s";

            if ($branchId) {
                $sql .= " JOIN branches b ON s.branch_id = b.branch_id";
            }

            $sql .= " WHERE s.sale_date BETWEEN :start_date AND :end_date";

            if ($branchId) {
                $sql .= " AND s.branch_id = :branch_id";
            }

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':start_date', $startDate);
            $stmt->bindParam(':end_date', $endDate);

            if ($branchId) {
                $stmt->bindParam(':branch_id', $branchId);
            }

            $stmt->execute();
            return $stmt->fetch(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error generating sales report: " . $e->getMessage());
            return false;
        }
    }

    public function getSoldItemsByBranch($branchId) {
        try {
            $sql = "SELECT si.sale_id, si.unique_code, si.quantity, si.unit_price, si.total_price,
                        si.sold_at, i.item_name, i.item_details, u.username as sold_by, si.transaction_id
                    FROM sold_items si
                    JOIN items i ON si.item_id = i.item_id
                    JOIN users u ON si.sold_by = u.user_id
                    WHERE si.branch_id = :branch_id
                    AND si.transaction_id NOT LIKE '%\_VERIFIED\_%'
                    AND COALESCE(si.is_ims_income, 1) = 1
                    ORDER BY si.sold_at DESC";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':branch_id', $branchId);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting sold items by branch: " . $e->getMessage());
            return false;
        }
    }

    public function getSoldItemsDetailed($branchId, $startDate = null, $endDate = null) {
        try {
            $sql = "SELECT si.sale_id, si.unique_code, si.unit_price, si.total_price,
                        si.sold_at, i.item_name, i.item_details, i.description, i.unique_code as item_unique_code,
                        u.username as sold_by, u.user_id, b.branch_name, si.transaction_id
                    FROM sold_items si
                    JOIN items i ON si.item_id = i.item_id
                    JOIN users u ON si.sold_by = u.user_id
                    JOIN branches b ON si.branch_id = b.branch_id
                    WHERE si.branch_id = :branch_id
                    AND si.transaction_id NOT LIKE '%\_VERIFIED\_%'
                    AND COALESCE(si.is_ims_income, 1) = 1";

            if ($startDate && $endDate) {
                $sql .= " AND si.sold_at BETWEEN :start_date AND :end_date";
            }

            $sql .= " ORDER BY si.sold_at DESC";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':branch_id', $branchId);

            if ($startDate && $endDate) {
                $stmt->bindParam(':start_date', $startDate);
                $stmt->bindParam(':end_date', $endDate);
            }

            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting detailed sold items by branch: " . $e->getMessage());
            return false;
        }
    }

    public function generateWeeklyReport($branchId, $startDate, $endDate) {
        try {
            error_log("Generating report for branch: $branchId, from: $startDate, to: $endDate");
            if (!$branchId || !$startDate || !$endDate) {
                error_log("Invalid parameters: branchId=$branchId, startDate=$startDate, endDate=$endDate");
                return false;
            }

            $sql = "SELECT
                        si.sale_id,
                        si.sold_at as date,
                        i.item_name,
                        i.item_details,
                        si.unique_code,
                        si.total_price as amount,
                        si.transaction_id,
                        'pending' as verification_status
                    FROM sold_items si
                    JOIN items i ON si.item_id = i.item_id
                    WHERE si.branch_id = :branch_id
                    AND si.transaction_id NOT LIKE '%\_VERIFIED\_%'
                    AND COALESCE(si.is_ims_income, 1) = 1
                    AND NOT EXISTS (SELECT 1 FROM credit_sale_items csi WHERE csi.sale_id = si.sale_id)
                    ORDER BY si.sold_at ASC";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
            $stmt->execute();

            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            error_log("Found " . count($result) . " records");
            return $result;
        } catch (Exception $e) {
            error_log("Error generating weekly report: " . $e->getMessage());
            return false;
        }
    }


      public function generateWeeklyReportEdit($branchId, $startDate, $endDate) {
        try {
            error_log("Generating report for branch: $branchId, from: $startDate, to: $endDate");
            if (!$branchId || !$startDate || !$endDate) {
                error_log("Invalid parameters: branchId=$branchId, startDate=$startDate, endDate=$endDate");
                return false;
            }

            $sql = "SELECT
                        si.sale_id,
                        si.sold_at as date,
                        i.item_name,
                        i.item_details,
                        si.unique_code,
                        si.total_price as amount,
                        si.transaction_id,
                        'pending' as verification_status
                    FROM sold_items si
                    JOIN items i ON si.item_id = i.item_id
                    WHERE si.branch_id = :branch_id
                    AND si.transaction_id NOT LIKE '%\_VERIFIED\_%'
                    AND COALESCE(si.is_ims_income, 1) = 1
                    ORDER BY si.sold_at ASC";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
            $stmt->execute();

            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            error_log("Found " . count($result) . " records");
            return $result;
        } catch (Exception $e) {
            error_log("Error generating weekly report: " . $e->getMessage());
            return false;
        }
    }

  

  public function getSaleById($saleId) {
        try {
            $sql = "SELECT transaction_id, branch_id, sold_at, customer_name, customer_phone
                    FROM sold_items
                    WHERE sale_id = :sale_id";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':sale_id', $saleId, PDO::PARAM_INT);
            $stmt->execute();
            $saleInfo = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$saleInfo) {
                return false;
            }

            $sql = "SELECT si.*, i.item_name, i.item_details, u.username as sold_by_username, b.branch_name
                    FROM sold_items si
                    JOIN items i ON si.item_id = i.item_id
                    JOIN users u ON si.sold_by = u.user_id
                    JOIN branches b ON si.branch_id = b.branch_id
                    WHERE si.transaction_id = :transaction_id
                    ORDER BY si.sale_id";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':transaction_id', $saleInfo['transaction_id']);
            $stmt->execute();
            $sales = $stmt->fetchAll(PDO::FETCH_ASSOC);

            if (!$sales) {
                return false;
            }

            return [
                'transaction_id' => $saleInfo['transaction_id'],
                'branch_id' => $saleInfo['branch_id'],
                'sold_at' => $saleInfo['sold_at'],
                'customer_name' => $saleInfo['customer_name'],
                'customer_phone' => $saleInfo['customer_phone'],
                'items' => $sales
            ];
        } catch (Exception $e) {
            error_log("Error getting sale by ID: " . $e->getMessage());
            return false;
        }
    }

    public function updateSale($saleId, $data) {
        $oldData = null;
        if ($this->logger) {
            $oldSale = $this->getSaleById($saleId);
            if ($oldSale) {
                $oldData = [
                    'transaction_id' => $oldSale['transaction_id'],
                    'branch_id' => $oldSale['branch_id'],
                    'customer_name' => $oldSale['customer_name'],
                    'customer_phone' => $oldSale['customer_phone'],
                    'items_count' => count($oldSale['items'])
                ];
            }
        }

        try {
            $this->conn->beginTransaction();

            $sql = "SELECT transaction_id, branch_id FROM sold_items WHERE sale_id = :sale_id";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':sale_id', $saleId, PDO::PARAM_INT);
            $stmt->execute();
            $saleInfo = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$saleInfo) {
                throw new Exception("Sale not found.");
            }
            $transactionId = $saleInfo['transaction_id'];
            $originalBranchId = $saleInfo['branch_id'];

            $quantity = (int)($data['quantity'] ?? 0);
            $uniqueCodes = $data['unique_code'] ?? [];
            $itemIds = $data['item_id'] ?? [];
            $prices = $data['price'] ?? [];
            $amountPaid = (float)($data['amount_paid'] ?? 0);
            $customerName = $data['customer_name'] ?? null;
            $customerPhone = $data['customer_phone'] ?? null;
            $soldAt = $this->normalizeSaleDateTime($data['sold_at'] ?? null);

            if ($quantity <= 0 || count($uniqueCodes) != $quantity || count($itemIds) != $quantity || count($prices) != $quantity) {
                throw new Exception("Invalid quantity or mismatched item data.");
            }

            $total = array_sum($prices);
            $maxDiscountRate = 0.17;
            $minimumAmount = $total * (1 - $maxDiscountRate);
            if ($amountPaid < $minimumAmount) {
                throw new Exception("Amount paid cannot be less than " . number_format($minimumAmount, 2) . ". Maximum discount allowed is 17%.");
            }
            if ($amountPaid > $total) {
                throw new Exception("Amount paid cannot exceed the total price of " . number_format($total, 2) . ".");
            }

            $effectivePricePerItem = $amountPaid / $quantity;
            $userId = $data['sold_by'] ?? $_SESSION['user_id'];
            $branchId = $data['branch_id'] ?? $originalBranchId;

            for ($i = 0; $i < $quantity; $i++) {
                $uniqueCode = $uniqueCodes[$i];
                $itemId = $itemIds[$i];

                $sql = "SELECT item_id, current_location_id FROM items WHERE unique_code = :unique_code";
                $stmt = $this->conn->prepare($sql);
                $stmt->bindParam(':unique_code', $uniqueCode);
                $stmt->execute();
                $item = $stmt->fetch(PDO::FETCH_ASSOC);
                if (!$item || $item['item_id'] != $itemId) {
                    throw new Exception("Invalid unique code or item ID for item " . ($i + 1) . ".");
                }
                if ($item['current_location_id'] != $branchId) {
                    throw new Exception("Item $uniqueCode is not assigned to the selected branch.");
                }

                $sql = "SELECT sale_id FROM sold_items WHERE unique_code = :unique_code AND transaction_id != :transaction_id";
                $stmt = $this->conn->prepare($sql);
                $stmt->bindParam(':unique_code', $uniqueCode);
                $stmt->bindParam(':transaction_id', $transactionId);
                $stmt->execute();
                if ($stmt->fetch()) {
                    throw new Exception("Item $uniqueCode has already been sold in another transaction.");
                }
            }

            $sql = "DELETE FROM sold_items WHERE transaction_id = :transaction_id";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':transaction_id', $transactionId);
            $stmt->execute();

            $sqlInsert = "INSERT INTO sold_items (branch_id, item_id, unique_code, unit_price, total_price, sold_by, transaction_id, sold_at, customer_name, customer_phone)
                          VALUES (:branch_id, :item_id, :unique_code, :unit_price, :total_price, :sold_by, :transaction_id, :sold_at, :customer_name, :customer_phone)";
            $stmtInsert = $this->conn->prepare($sqlInsert);

            for ($i = 0; $i < $quantity; $i++) {
                $params = [
                    ':branch_id' => $branchId,
                    ':item_id' => $itemIds[$i],
                    ':unique_code' => $uniqueCodes[$i],
                    ':unit_price' => $effectivePricePerItem,
                    ':total_price' => $effectivePricePerItem,
                    ':sold_by' => $userId,
                    ':transaction_id' => $transactionId,
                    ':sold_at' => $soldAt,
                    ':customer_name' => $customerName,
                    ':customer_phone' => $customerPhone
                ];
                if (!$stmtInsert->execute($params)) {
                    throw new Exception("Failed to insert sale for item " . ($i + 1) . ".");
                }
            }

            $this->conn->commit();

            if ($this->logger && $oldData) {
                $newData = [
                    'transaction_id' => $transactionId,
                    'branch_id' => $branchId,
                    'customer_name' => $customerName,
                    'customer_phone' => $customerPhone,
                    'items_count' => $quantity
                ];
                $this->logger->logSale('UPDATE', $saleId, $newData, $oldData);
            }

            return (int)$saleId;
        } catch (Exception $e) {
            $this->conn->rollBack();
            error_log("Error updating sale: " . $e->getMessage());
            return false;
        }
    }

    public function getAvailableItems($branchId) {
        try {
            $sql = "SELECT i.item_id, i.item_name, i.item_details, i.unique_code
                    FROM items i
                    WHERE i.current_location_id = :branch_id
                    AND i.item_id NOT IN (SELECT item_id FROM sold_items WHERE transaction_id NOT LIKE '%_VERIFIED_%')";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting available items: " . $e->getMessage());
            return [];
        }
    }

    
	public function getReportDetails($e_report_id) {
        try {
            $sql = "SELECT wr.week_start_date
                    FROM weekly_reports wr
                    WHERE wr.report_id = :reportId
                    ";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':reportId', $e_report_id, PDO::PARAM_INT);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting available items: " . $e->getMessage());
            return [];
        }
    }

	
	
	
	
	public function getBranches() {
        try {
            $sql = "SELECT branch_id, branch_name FROM branches";
            $stmt = $this->conn->prepare($sql);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting branches: " . $e->getMessage());
            return [];
        }
    }

    public function getUsers() {
        try {
            $sql = "SELECT user_id, username FROM users WHERE role = 'branch_manager' OR role = 'staff'";
            $stmt = $this->conn->prepare($sql);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting users: " . $e->getMessage());
            return [];
        }
    }

    public function verifyWeeklyReport($branchId, $weekStartDate, $verifiedBy) {
        try {
            $this->conn->beginTransaction();

            $sql = "INSERT INTO weekly_reports (branch_id, week_start_date, verification_status, verified_by, verified_at)
                    VALUES (:branch_id, :week_start_date, 'verified', :verified_by, NOW())";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':branch_id', $branchId);
            $stmt->bindParam(':week_start_date', $weekStartDate);
            $stmt->bindParam(':verified_by', $verifiedBy);

            $stmt->execute();
            $reportId = $this->conn->lastInsertId();

            $updateSoldItemsSql = "UPDATE sold_items
                       SET report_id = :report_id, transaction_id = CONCAT(transaction_id, '_VERIFIED_', :report_id)
                       WHERE branch_id = :branch_id
                       AND transaction_id NOT LIKE '%\_VERIFIED\_%'";

            $updateSoldItemsStmt = $this->conn->prepare($updateSoldItemsSql);
            $updateSoldItemsStmt->bindParam(':report_id', $reportId);
            $updateSoldItemsStmt->bindParam(':branch_id', $branchId);
            $updateSoldItemsStmt->execute();
			
			
			$updateLayBuyItemsSql = "UPDATE laybuy_items
                       SET verified = 1 , report_id=:report_id
                       WHERE branch_id = :branch_id AND verified='0'
                       ";

            $updateLayBuyItemsStmt = $this->conn->prepare($updateLayBuyItemsSql);
            $updateLayBuyItemsStmt->bindParam(':report_id', $reportId);
            $updateLayBuyItemsStmt->bindParam(':branch_id', $branchId);
            $updateLayBuyItemsStmt->execute();
			
			$message = "You have recieved weekly report ".$reportId. " from branch.".$branchId;
			$this->notifications->createNotification($branchId, $message, 'Admin');
			
			
			
			
			

            $updateDepositsSql = "UPDATE deposits
                                SET report_id=:report_id, notes = CONCAT(IFNULL(notes, ''), ' [Verified in Report #', :report_id, ']')
                                WHERE branch_id = :branch_id
                                AND (notes IS NULL OR notes NOT LIKE '%[Verified in Report #%')";

            $updateDepositsStmt = $this->conn->prepare($updateDepositsSql);
            $updateDepositsStmt->bindParam(':report_id', $reportId);
            $updateDepositsStmt->bindParam(':branch_id', $branchId);
            $updateDepositsStmt->execute();

            if ($this->tableExists('order_payments')) {
                $this->ensureOrderPaymentVerificationColumns();
                $updateOrderPaymentsSql = "UPDATE order_payments
                                    SET is_verified = 1, report_id = :report_id
                                    WHERE branch_id = :branch_id AND is_verified = 0";
                $updateOrderPaymentsStmt = $this->conn->prepare($updateOrderPaymentsSql);
                $updateOrderPaymentsStmt->bindParam(':report_id', $reportId);
                $updateOrderPaymentsStmt->bindParam(':branch_id', $branchId);
                $updateOrderPaymentsStmt->execute();
            }

            if ($this->tableExists('credit_payments')) {
                $this->ensureCreditPaymentVerificationColumns();
                $updateCreditPaymentsSql = "UPDATE credit_payments
                                    SET is_verified = 1, report_id = :report_id
                                    WHERE branch_id = :branch_id AND is_verified = 0";
                $updateCreditPaymentsStmt = $this->conn->prepare($updateCreditPaymentsSql);
                $updateCreditPaymentsStmt->bindParam(':report_id', $reportId);
                $updateCreditPaymentsStmt->bindParam(':branch_id', $branchId);
                $updateCreditPaymentsStmt->execute();
            }

            $this->conn->commit();

            return (int)$reportId;
        } catch (Exception $e) {
            if ($this->conn->inTransaction()) {
                $this->conn->rollBack();
            }
            error_log("Error verifying weekly report: " . $e->getMessage());
            return false;
        }
    }

    private function ensureOrderPaymentVerificationColumns() {
        $stmt = $this->conn->query("SHOW COLUMNS FROM order_payments LIKE 'is_verified'");
        if (!$stmt->fetchColumn()) {
            $this->conn->exec('ALTER TABLE order_payments ADD COLUMN is_verified TINYINT(1) NOT NULL DEFAULT 0');
        }
        $stmt = $this->conn->query("SHOW COLUMNS FROM order_payments LIKE 'report_id'");
        if (!$stmt->fetchColumn()) {
            $this->conn->exec('ALTER TABLE order_payments ADD COLUMN report_id INT DEFAULT NULL');
        }
    }

    private function ensureCreditPaymentVerificationColumns() {
        $stmt = $this->conn->query("SHOW COLUMNS FROM credit_payments LIKE 'is_verified'");
        if (!$stmt->fetchColumn()) {
            $this->conn->exec('ALTER TABLE credit_payments ADD COLUMN is_verified TINYINT(1) NOT NULL DEFAULT 0');
        }
        $stmt = $this->conn->query("SHOW COLUMNS FROM credit_payments LIKE 'report_id'");
        if (!$stmt->fetchColumn()) {
            $this->conn->exec('ALTER TABLE credit_payments ADD COLUMN report_id INT DEFAULT NULL');
        }
    }

    public function isWeekVerified($branchId, $weekStartDate) {
        try {
            $sql = "SELECT verification_status
                    FROM weekly_reports
                    WHERE branch_id = :branch_id
                    AND week_start_date = :week_start_date
                    AND verification_status = 'verified'";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':branch_id', $branchId);
            $stmt->bindParam(':week_start_date', $weekStartDate);
            $stmt->execute();

            return (bool)$stmt->fetch(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error checking week verification status: " . $e->getMessage());
            return false;
        }
    }

    public function checkSalesExist($branchId) {
        try {
            $sql = "SELECT COUNT(*) as count FROM sold_items WHERE branch_id = :branch_id";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
            $stmt->execute();

            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            error_log("Total sales found for branch $branchId: " . $result['count']);

            return $result['count'] > 0;
        } catch (Exception $e) {
            error_log("Error checking sales existence: " . $e->getMessage());
            return false;
        }
    }

    public function addDeposit($branchId, $date, $amount) {
        try {
            $query = "INSERT INTO deposits (branch_id, deposit_date, amount) VALUES (:branch_id, :deposit_date, :amount)";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
            $stmt->bindParam(':deposit_date', $date);
            $stmt->bindParam(':amount', $amount);
            $result = $stmt->execute();

            if ($result) {
                $depositId = $this->conn->lastInsertId();
                $message = "Deposit ".$depositId." for branch ".$branchId." has been recorded.";
                $this->notifications->createNotification($branchId, $message, 'Admin');
            }

            return $result;
        } catch (Exception $e) {
            error_log("Error adding deposit: " . $e->getMessage());
            return false;
        }
    }

    public function getTotalDeposits($branchId, $startDate, $endDate) {
        try {
            $query = "SELECT SUM(amount) AS total FROM deposits
                     WHERE branch_id = :branch_id
                     AND deposit_date BETWEEN :start_date AND :end_date
                     AND (notes IS NULL OR notes NOT LIKE '%[Verified in Report #%')";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
            $stmt->bindParam(':start_date', $startDate);
            $stmt->bindParam(':end_date', $endDate);
            $stmt->execute();
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            return $result['total'] ?? 0;
        } catch (Exception $e) {
            error_log("Error fetching total deposits: " . $e->getMessage());
            return 0;
        }
    }

    public function getDetailedSalesForWeek($branchId, $startDate, $endDate, $reportId = null) {
        try {
            $sql = "SELECT
                        si.sale_id,
                        si.unique_code,
                        si.total_price,
                        si.sold_at,
                        i.item_name,
                        i.item_details,
                        u.username as sold_by,
                        si.transaction_id
                    FROM sold_items si
                    JOIN items i ON si.item_id = i.item_id
                    JOIN users u ON si.sold_by = u.user_id
                    WHERE si.branch_id = :branch_id";

            if ($reportId !== null) {
                $sql .= " AND si.transaction_id LIKE :transaction_pattern";
            } else {
                $sql .= " AND DATE(si.sold_at) BETWEEN :start_date AND :end_date";
            }

            $sql .= " ORDER BY si.sold_at ASC";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);

            if ($reportId !== null) {
                $transactionPattern = '%_VERIFIED_' . $reportId;
                $stmt->bindParam(':transaction_pattern', $transactionPattern, PDO::PARAM_STR);
            } else {
                $stmt->bindParam(':start_date', $startDate);
                $stmt->bindParam(':end_date', $endDate);
            }

            $stmt->execute();

            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
            error_log("Sales::getDetailedSalesForWeek (Report ID: $reportId) - Query Results Count: " . count($results));
            return $results;
        } catch (PDOException $e) {
            error_log("Error getting detailed sales for week/report: " . $e->getMessage());
            return [];
        }
    }
	
	/*
    public function getSalesForPeriod($startDate, $endDate, $selectedBranchId) {
        try {
            $sql = "SELECT si.*, i.item_name, i.item_details, i.unit_price as cost_price, b.branch_name
                    FROM sold_items si
                    JOIN items i ON si.item_id = i.item_id
                    JOIN branches b ON si.branch_id = b.branch_id
                    WHERE COALESCE(si.is_ims_income, 1) = 1
                AND si.sold_at BETWEEN :start_date AND :end_date";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':start_date', $startDate);
            $stmt->bindParam(':end_date', $endDate);
            $stmt->execute();

            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting sales for period: " . $e->getMessage());
            return false;
        }
    }  */
	
	public function getSalesForPeriod($startDate, $endDate, $selectedBranchId = null) {
    try {
        $sql = "SELECT
                    si.*,
                    i.item_name,
                    i.item_details,
                    i.unit_price AS cost_price,
                    b.branch_name
                FROM sold_items si
                JOIN items i ON si.item_id = i.item_id
                JOIN branches b ON si.branch_id = b.branch_id
                WHERE COALESCE(si.is_ims_income, 1) = 1
                AND DATE(si.sold_at) BETWEEN :start_date AND :end_date";

        // Add branch filter ONLY if a specific branch is selected
        if (!empty($selectedBranchId) && $selectedBranchId !== 'all') {
            $sql .= " AND si.branch_id = :branch_id";
        }

        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':start_date', $startDate);
        $stmt->bindParam(':end_date', $endDate);

        // Bind branch_id only if used
        if (!empty($selectedBranchId) && $selectedBranchId !== 'all') {
            $stmt->bindParam(':branch_id', $selectedBranchId, PDO::PARAM_INT);
        }

        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);

    } catch (Exception $e) {
        error_log("Error getting sales for period: " . $e->getMessage());
        return false;
    }
}
	
	
	
	
	

    public function deleteSaleByTransactionId($transactionId, $branchId) {
        try {
            $saleData = null;
            if ($this->logger) {
                $sql = "SELECT si.*, i.item_name, i.item_details FROM sold_items si
                        JOIN items i ON si.item_id = i.item_id
                        WHERE si.transaction_id = :transaction_id
                        AND si.branch_id = :branch_id";
                $stmt = $this->conn->prepare($sql);
                $stmt->bindParam(':transaction_id', $transactionId, PDO::PARAM_STR);
                $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
                $stmt->execute();
                $sales = $stmt->fetchAll(PDO::FETCH_ASSOC);
                if ($sales) {
                    $saleData = [
                        'transaction_id' => $transactionId,
                        'branch_id' => $branchId,
                        'items_count' => count($sales),
                        'total_amount' => array_sum(array_column($sales, 'total_price')),
                        'customer_name' => $sales[0]['customer_name'] ?? null,
                        'customer_phone' => $sales[0]['customer_phone'] ?? null
                    ];
                }
            }

            $sql = "DELETE FROM sold_items
                    WHERE transaction_id = :transaction_id
                    AND branch_id = :branch_id
                    AND transaction_id NOT LIKE '%_VERIFIED_%'";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':transaction_id', $transactionId, PDO::PARAM_STR);
            $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
            $result = $stmt->execute();

            if ($result && $this->logger && $saleData) {
                $this->logger->logSale('DELETE', null, null, $saleData);
            }

            error_log("Deleted sales for transaction_id: $transactionId, branch_id: $branchId, success: " . ($result ? 'Yes' : 'No'));
            return $result;
        } catch (PDOException $e) {
            error_log("Error deleting sales by transaction ID: " . $e->getMessage());
            return false;
        }
    }

    public function getBranchSalesStats($branchId, $startDate, $endDate) {
        try {
            $sql = "SELECT
                        COUNT(*) as total_sales,
                        COUNT(DISTINCT DATE(si.sold_at)) as active_days,
                        SUM(si.total_price) as total_revenue,
                        AVG(si.total_price) as avg_sale_amount,
                        MAX(si.total_price) as max_sale_amount,
                        MIN(si.total_price) as min_sale_amount
                    FROM sold_items si
                    WHERE si.branch_id = :branch_id
                    AND COALESCE(si.is_ims_income, 1) = 1
                    AND si.sold_at BETWEEN :start_date AND :end_date";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
            $stmt->bindParam(':start_date', $startDate);
            $stmt->bindParam(':end_date', $endDate);
            $stmt->execute();
            $salesStats = $stmt->fetch(PDO::FETCH_ASSOC);

            $dailySql = "SELECT
                            DATE(si.sold_at) as sale_date,
                            COUNT(*) as sales_count,
                            SUM(si.total_price) as daily_revenue
                        FROM sold_items si
                        WHERE si.branch_id = :branch_id
                        AND COALESCE(si.is_ims_income, 1) = 1
                        AND si.sold_at BETWEEN :start_date AND :end_date
                        GROUP BY DATE(si.sold_at)
                        ORDER BY sale_date";

            $dailyStmt = $this->conn->prepare($dailySql);
            $dailyStmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
            $dailyStmt->bindParam(':start_date', $startDate);
            $dailyStmt->bindParam(':end_date', $endDate);
            $dailyStmt->execute();
            $dailyTrends = $dailyStmt->fetchAll(PDO::FETCH_ASSOC);

            $topItemsSql = "SELECT
                                i.item_name,
                                GROUP_CONCAT(DISTINCT NULLIF(i.item_details, '') SEPARATOR '; ') AS item_details,
                                COUNT(*) as sales_count,
                                SUM(si.total_price) as total_revenue
                            FROM sold_items si
                            JOIN items i ON si.item_id = i.item_id
                            WHERE si.branch_id = :branch_id
                            AND COALESCE(si.is_ims_income, 1) = 1
                            AND si.sold_at BETWEEN :start_date AND :end_date
                            GROUP BY i.item_name
                            ORDER BY total_revenue DESC
                            LIMIT 10";

            $topItemsStmt = $this->conn->prepare($topItemsSql);
            $topItemsStmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
            $topItemsStmt->bindParam(':start_date', $startDate);
            $topItemsStmt->bindParam(':end_date', $endDate);
            $topItemsStmt->execute();
            $topItems = $topItemsStmt->fetchAll(PDO::FETCH_ASSOC);

            return [
                'stats' => $salesStats,
                'daily_trends' => $dailyTrends,
                'top_items' => $topItems
            ];
        } catch (Exception $e) {
            error_log("Error getting branch sales stats: " . $e->getMessage());
            return [
                'stats' => [
                    'total_sales' => 0,
                    'active_days' => 0,
                    'total_revenue' => 0,
                    'avg_sale_amount' => 0,
                    'max_sale_amount' => 0,
                    'min_sale_amount' => 0
                ],
                'daily_trends' => [],
                'top_items' => []
            ];
        }
    }

	public function getBranchExpenseStats($branchId, $startDate, $endDate) {
    try {
        // Overall Expense Stats
        $sql = "SELECT
                    COUNT(*) as total_expenses,
                    COUNT(DISTINCT DATE(oe.expense_date)) as active_days,
                    SUM(oe.amount) as total_expense,
                    AVG(oe.amount) as avg_expense_amount,
                    MAX(oe.amount) as max_expense_amount,
                    MIN(oe.amount) as min_expense_amount
                FROM operational_expenses oe
                WHERE oe.branch_id = :branch_id
                AND oe.expense_date BETWEEN :start_date AND :end_date";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
        $stmt->bindParam(':start_date', $startDate);
        $stmt->bindParam(':end_date', $endDate);
        $stmt->execute();
        $expenseStats = $stmt->fetch(PDO::FETCH_ASSOC);

        //  Daily Expense Trends
        $dailySql = "SELECT
                        DATE(oe.expense_date) as expense_date,
                        COUNT(*) as expense_count,
                        SUM(oe.amount) as daily_total
                    FROM operational_expenses oe
                    WHERE oe.branch_id = :branch_id
                    AND oe.expense_date BETWEEN :start_date AND :end_date
                    GROUP BY DATE(oe.expense_date)
                    ORDER BY expense_date";

        $dailyStmt = $this->conn->prepare($dailySql);
        $dailyStmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
        $dailyStmt->bindParam(':start_date', $startDate);
        $dailyStmt->bindParam(':end_date', $endDate);
        $dailyStmt->execute();
        $dailyTrends = $dailyStmt->fetchAll(PDO::FETCH_ASSOC);

        //  Top Expense Categories / Items
        $topExpensesSql = "SELECT
                                e.expense_name,
                                COUNT(*) as expense_count,
                                SUM(oe.amount) as total_amount
                            FROM operational_expenses oe
                            JOIN expenses e ON oe.expense_id = e.expense_id
                            WHERE oe.branch_id = :branch_id
                            AND oe.expense_date BETWEEN :start_date AND :end_date
                            GROUP BY e.expense_name
                            ORDER BY total_amount DESC
                            LIMIT 10";

        $topExpensesStmt = $this->conn->prepare($topExpensesSql);
        $topExpensesStmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
        $topExpensesStmt->bindParam(':start_date', $startDate);
        $topExpensesStmt->bindParam(':end_date', $endDate);
        $topExpensesStmt->execute();
        $topExpenses = $topExpensesStmt->fetchAll(PDO::FETCH_ASSOC);

        return [
            'stats' => $expenseStats,
            'daily_trends' => $dailyTrends,
            'top_expenses' => $topExpenses
        ];

    } catch (Exception $e) {
        error_log("Error getting branch expense stats: " . $e->getMessage());

        return [
            'stats' => [
                'total_expenses' => 0,
                'active_days' => 0,
                'total_expense' => 0,
                'avg_expense_amount' => 0,
                'max_expense_amount' => 0,
                'min_expense_amount' => 0
            ],
            'daily_trends' => [],
            'top_expenses' => []
        ];
    }
}




    public function getBranchInventoryStats($branchId) {
        try {
            $sql = "SELECT
                        COUNT(*) as total_items,
                        SUM(CASE WHEN i.condition_status = 'NEW' THEN 1 ELSE 0 END) as new_items,
                        SUM(CASE WHEN i.condition_status = 'REFURBISHED' THEN 1 ELSE 0 END) as refurbished_items,
                        SUM(CASE WHEN i.condition_status = 'USED' THEN 1 ELSE 0 END) as used_items,
                        AVG(i.unit_price) as avg_price,
                        SUM(i.unit_price) as total_value
                    FROM items i
                    WHERE i.current_location_id = :branch_id
                    AND i.item_id NOT IN (SELECT item_id FROM sold_items WHERE transaction_id NOT LIKE '%_VERIFIED_%')";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
            $stmt->execute();
            $stats = $stmt->fetch(PDO::FETCH_ASSOC);

            $categorySql = "SELECT
                                i.item_name,
                                GROUP_CONCAT(DISTINCT NULLIF(i.item_details, '') SEPARATOR '; ') AS item_details,
                                COUNT(*) as item_count,
                                SUM(i.unit_price) as total_value
                            FROM items i
                            WHERE i.current_location_id = :branch_id
                            AND i.item_id NOT IN (SELECT item_id FROM sold_items WHERE transaction_id NOT LIKE '%_VERIFIED_%')
                            GROUP BY i.item_name
                            ORDER BY item_count DESC
                            LIMIT 10";

            $categoryStmt = $this->conn->prepare($categorySql);
            $categoryStmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
            $categoryStmt->execute();
            $itemsByCategory = $categoryStmt->fetchAll(PDO::FETCH_ASSOC);

            return array_merge($stats, ['items_by_category' => $itemsByCategory]);
        } catch (Exception $e) {
            error_log("Error getting branch inventory stats: " . $e->getMessage());
            return [
                'total_items' => 0,
                'new_items' => 0,
                'refurbished_items' => 0,
                'used_items' => 0,
                'avg_price' => 0,
                'total_value' => 0,
                'items_by_category' => []
            ];
        }
    }

    public function getLowStockItems($branchId) {
        try {
            $warehouseBranchId = 7;
            $lowStockThreshold = ($branchId == $warehouseBranchId) ? 20 : 10;

            $sql = "SELECT
                        i.item_name,
                        GROUP_CONCAT(DISTINCT NULLIF(i.item_details, '') SEPARATOR '; ') AS item_details,
                        COUNT(*) as item_count,
                        AVG(i.unit_price) as avg_price,
                        SUM(i.unit_price) as total_value,
                        :threshold as low_stock_threshold
                    FROM items i
                    WHERE i.current_location_id = :branch_id
                    AND i.item_id NOT IN (SELECT item_id FROM sold_items WHERE transaction_id NOT LIKE '%_VERIFIED_%')
                    GROUP BY i.item_name
                    HAVING COUNT(*) < :threshold
                    ORDER BY item_count ASC";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
            $stmt->bindParam(':threshold', $lowStockThreshold, PDO::PARAM_INT);
            $stmt->execute();
            $lowStockItems = $stmt->fetchAll(PDO::FETCH_ASSOC);

            $totalLowStockItems = count($lowStockItems);
            $totalLowStockValue = array_sum(array_column($lowStockItems, 'total_value'));

            return [
                'low_stock_items' => $lowStockItems,
                'total_low_stock_items' => $totalLowStockItems,
                'total_low_stock_value' => $totalLowStockValue,
                'low_stock_threshold' => $lowStockThreshold
            ];
        } catch (Exception $e) {
            error_log("Error getting low stock items: " . $e->getMessage());
            return [
                'low_stock_items' => [],
                'total_low_stock_items' => 0,
                'total_low_stock_value' => 0,
                'low_stock_threshold' => ($branchId == 7) ? 20 : 10
            ];
        }
    }
	
	
	public function discardReport($reportId,$notification_branch_id) {
				
		$del_keyword = "[Verified in Report #".$reportId."]";
		$del_keyword_sold_items = "VERIFIED_".$reportId;
		$message = "Report ".$reportId. " has been rejected by the admin. All deposits and sales have been reversed. Contact the admin as soon as possible.";
			$this->notifications->createNotification($notification_branch_id, $message, 'branch');
			
           
			$sql = "UPDATE deposits SET notes = REPLACE(notes, ' [Verified in Report #', '') WHERE report_id = :reportId";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':reportId', $reportId);
            $stmt->execute();
		
			
			
			
			
			
			
			$sql = "UPDATE sold_items SET transaction_id = REPLACE(transaction_id, '_VERIFIED_', '') WHERE report_id = :reportId";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':reportId', $reportId);
            $stmt->execute();
		
			
		
		
			$sql = "UPDATE sold_items SET transaction_id = REPLACE(transaction_id, '', '') WHERE report_id = :reportId";
            $stmt = $this->conn->prepare($sql);
			$stmt->bindParam(':reportId', $reportId);
            $stmt->execute();
			
			$sql = "UPDATE laybuy_items SET verified =0 WHERE report_id= :reportId";
            $stmt = $this->conn->prepare($sql);
			$stmt->bindParam(':reportId', $reportId);
            $stmt->execute();
			
			
			$sql = "DELETE FROM weekly_reports WHERE report_id = :reportId";
            $stmt = $this->conn->prepare($sql);
			$stmt->bindParam(':reportId', $reportId);
			$stmt->execute();
			
	
	
   }
	
	public function requestEdit($reportId,$notification_branch_id) {
				
		$del_keyword = "[Verified in Report #".$reportId."]";
		$del_keyword_sold_items = "VERIFIED_".$reportId;
		$message = "Error #9001! We found errors in report ".$reportId. " Please edit it as soon as possible. <h1>Click here to edit </h1> ";
		
			$this->notifications->createNotification($notification_branch_id, $message, 'branch');
           
			/*
			
			
			
			
			$sql = "DELETE FROM laybuy_items WHERE report_id = :reportId";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':reportId', $reportId);
            $stmt->execute();
		
			$sql = "DELETE FROM deposits WHERE notes LIKE '%$del_keyword%'";
            $stmt = $this->conn->prepare($sql);
			//$stmt->bindParam(':reportId', $reportId);
            $stmt->execute();
			
			
			$sql = "DELETE FROM sold_items WHERE report_id = :reportId";
            $stmt = $this->conn->prepare($sql);
			$stmt->bindParam(':reportId', $reportId);
            $stmt->execute();
			
			$sql = "DELETE FROM weekly_reports WHERE report_id = :reportId";
            $stmt = $this->conn->prepare($sql);
			$stmt->bindParam(':reportId', $reportId);
			$stmt->execute();
			
	
		*/
   }
	
	
	

    public function getTargetBalance($branchId, $month = null) {
        try {
            $month = $month ?? date('Y-m');

            $stmt = $this->conn->prepare("SELECT target_amount FROM sales_targets WHERE branch_id = ?");
            $stmt->execute([$branchId]);
            $target = $stmt->fetch(PDO::FETCH_ASSOC);

            $targetAmount = $target ? floatval($target['target_amount']) : 0;

            $startDate = $month . '-01';
            $endDate = date('Y-m-t', strtotime($startDate));
            $salesStats = $this->getBranchSalesStats($branchId, $startDate, $endDate);
            $currentSales = floatval($salesStats['stats']['total_revenue'] ?? 0);

            $balance = $targetAmount - $currentSales;
            $progressPercentage = $targetAmount > 0 ? min(100, ($currentSales / $targetAmount) * 100) : 0;

            return [
                'target_amount' => $targetAmount,
                'current_sales' => $currentSales,
                'balance' => $balance,
                'progress_percentage' => $progressPercentage,
                'target_set' => $targetAmount > 0,
                'target_met' => $balance <= 0 && $targetAmount > 0
            ];
        } catch (Exception $e) {
            error_log("Error getting target balance: " . $e->getMessage());
            return [
                'target_amount' => 0,
                'current_sales' => 0,
                'balance' => 0,
                'progress_percentage' => 0,
                'target_set' => false,
                'target_met' => false
            ];
        }
    }
	
	public function getExpenseAllocationBalance($branchId, $month = null) {
    try {
        $month = $month ?? date('Y-m');

        // Get allocated expense budget
        $stmt = $this->conn->prepare("
            SELECT allocation_amount 
            FROM expense_allocation 
            WHERE branch_id = ?
        ");
        $stmt->execute([$branchId]);
        $allocation = $stmt->fetch(PDO::FETCH_ASSOC);

        $allocationAmount = $allocation ? floatval($allocation['allocation_amount']) : 0;

        // Date range for the month
        $startDate = $month . '-01';
        $endDate = date('Y-m-t', strtotime($startDate));

        // Get expense stats (using your modified function)
        $expenseStats = $this->getBranchExpenseStats($branchId, $startDate, $endDate);
        $currentExpenses = floatval($expenseStats['stats']['total_expense'] ?? 0);

        // Calculate balance
        $balance = $allocationAmount - $currentExpenses;

        // Usage percentage (how much of allocation is used)
        $usagePercentage = $allocationAmount > 0 
            ? min(100, ($currentExpenses / $allocationAmount) * 100) 
            : 0;

        return [
            'allocation_amount' => $allocationAmount,
            'current_expenses' => $currentExpenses,
            'balance' => $balance,
            'usage_percentage' => $usagePercentage,
            'allocation_set' => $allocationAmount > 0,
            'allocation_exceeded' => $currentExpenses > $allocationAmount && $allocationAmount > 0
        ];

    } catch (Exception $e) {
        error_log("Error getting expense allocation balance: " . $e->getMessage());

        return [
            'allocation_amount' => 0,
            'current_expenses' => 0,
            'balance' => 0,
            'usage_percentage' => 0,
            'allocation_set' => false,
            'allocation_exceeded' => false
        ];
    }
}
	
	
	
	 public function updateTotalSales($reportId, $updatedTotal) {
        try {
            $sql = "UPDATE weekly_reports SET total_sales= :updatedTotal WHERE report_id=:reportId";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':reportId', $reportId);
            $stmt->bindParam(':updatedTotal', $updatedTotal);
            $stmt->execute();

            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting sales for period: " . $e->getMessage());
            return false;
        }
    }

   
    public function updateWeeklyDates($report_id, $report_date) {
        try {
            $sql = "UPDATE weekly_reports SET week_start_date = :report_date WHERE report_id=:reportId";

            $stmt = $this->conn->prepare($sql);
            $stmt->bindParam(':reportId', $report_id);
            $stmt->bindParam(':report_date', $report_date);
            $stmt->execute();

            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            error_log("Error getting sales for period: " . $e->getMessage());
            return false;
        }
    }

   
	
	
	
}
?>






