<?php
namespace App\Core;

use Throwable;

class PosService {
    public static function accessRoles(): array {
        return ['admin','staff','vendor','pos_manager','pos_cashier'];
    }

    public static function requireAccess(string $login = 'public/login.php'): void {
        Auth::requireRole(self::accessRoles(), $login);
        if (Auth::role() === 'staff') {
            Auth::requireAdminModule(self::staffModuleForRequest());
        }
    }

    private static function staffModuleForRequest(): string {
        $script = basename((string)($_SERVER['SCRIPT_NAME'] ?? ''), '.php');
        return match ($script) {
            'reports' => 'pos-reports',
            'refunds', 'pos-refund' => 'pos-refunds',
            'settings', 'pos-settings' => 'pos-settings',
            'shifts', 'pos-shift' => 'pos-shifts',
            'print-labels' => 'barcode-qr',
            'held' => 'pos-sales',
            default => 'pos',
        };
    }

    public static function can(string $ability): bool {
        $role = (string)Auth::role();
        if (in_array($role, ['admin','staff','vendor'], true)) return true;
        $userId = (int)Auth::id();
        try {
            $row = Database::fetchOne(
                'SELECT COUNT(*) total FROM pos_staff_assignments psa
                 JOIN pos_role_permissions prp ON prp.pos_role_id = psa.pos_role_id
                 WHERE psa.user_id=? AND psa.status="active" AND prp.ability=? LIMIT 1',
                [$userId, $ability]
            );
            return (int)($row['total'] ?? 0) > 0;
        } catch (Throwable $e) {
            return in_array($role, ['pos_manager','pos_cashier'], true) && in_array($ability, ['pos.sell','pos.hold'], true);
        }
    }

    public static function currentVendorId(): ?int {
        $role = (string)Auth::role();
        $userId = (int)Auth::id();
        if ($role === 'vendor') {
            $vendor = Database::fetchOne('SELECT id FROM vendors WHERE user_id=? AND status IN ("approved","active") LIMIT 1', [$userId]);
            return $vendor ? (int)$vendor['id'] : null;
        }
        if (in_array($role, ['pos_manager','pos_cashier'], true)) {
            $assignment = Database::fetchOne(
                'SELECT psa.vendor_id FROM pos_staff_assignments psa JOIN vendors v ON v.id=psa.vendor_id WHERE psa.user_id=? AND psa.status="active" AND v.status IN ("approved","active") ORDER BY psa.id DESC LIMIT 1',
                [$userId]
            );
            return $assignment ? (int)$assignment['vendor_id'] : null;
        }
        if (in_array($role, ['admin','staff'], true)) {
            $requested = (int)($_GET['vendor_id'] ?? $_POST['vendor_id'] ?? $_SESSION['pos_vendor_id'] ?? 0);
            if ($requested > 0) {
                $vendor = Database::fetchOne('SELECT id FROM vendors WHERE id=? AND status IN ("approved","active") LIMIT 1', [$requested]);
                if ($vendor) {
                    $_SESSION['pos_vendor_id'] = (int)$vendor['id'];
                    return (int)$vendor['id'];
                }
            }
            $vendor = Database::fetchOne('SELECT id FROM vendors WHERE status IN ("approved","active") ORDER BY id ASC LIMIT 1');
            if ($vendor) {
                $_SESSION['pos_vendor_id'] = (int)$vendor['id'];
                return (int)$vendor['id'];
            }
        }
        return null;
    }

    public static function resolveVendorId(?int $requestedVendorId = null): ?int {
        $role = (string)Auth::role();
        if (in_array($role, ['vendor','pos_manager','pos_cashier'], true)) {
            return self::currentVendorId();
        }
        if (in_array($role, ['admin','staff'], true) && (int)$requestedVendorId > 0) {
            $vendor = Database::fetchOne('SELECT id FROM vendors WHERE id=? AND status IN ("approved","active") LIMIT 1', [(int)$requestedVendorId]);
            if ($vendor) {
                $_SESSION['pos_vendor_id'] = (int)$vendor['id'];
                return (int)$vendor['id'];
            }
            return null;
        }
        return self::currentVendorId();
    }

    public static function approvedVendors(): array {
        if (!in_array((string)Auth::role(), ['admin','staff'], true)) return [];
        return Database::safeFetchAll('SELECT id,shop_name FROM vendors WHERE status IN ("approved","active") ORDER BY shop_name,id');
    }

    public static function saleNo(): string {
        return 'POS-' . date('Ymd-His') . '-' . random_int(100, 999);
    }

    public static function fallbackProducts(): array {
        $items = function_exists('sample_products') ? sample_products() : [];
        return array_map(function($p){
            return [
                'id' => (int)($p['id'] ?? 0),
                'name' => (string)($p['name'] ?? ''),
                'sku' => (string)($p['sku'] ?? ''),
                'barcode' => (string)($p['barcode'] ?? $p['sku'] ?? ''),
                'sale_price' => (float)($p['sale_price'] ?? $p['regular_price'] ?? 0),
                'regular_price' => (float)($p['regular_price'] ?? 0),
                'stock' => (int)($p['stock'] ?? 0),
                'image' => (string)($p['image'] ?? ''),
                'vendor_id' => 1,
                'vendor_name' => (string)($p['shop_name'] ?? 'Demo Vendor')
            ];
        }, $items);
    }

    public static function searchProducts(string $query = '', ?int $vendorId = null, int $limit = 20): array {
        $q = trim($query);
        try {
            $params = [];
            $sql = 'SELECT p.id,p.vendor_id,p.name,p.sku,p.barcode,p.product_code,p.sale_price,p.regular_price,p.stock,p.image,v.shop_name AS vendor_name
                    FROM products p LEFT JOIN vendors v ON v.id=p.vendor_id
                    WHERE p.status IN ("published","active","approved")';
            if ($vendorId) { $sql .= ' AND p.vendor_id=?'; $params[] = $vendorId; }
            if ($q !== '') {
                $sql .= ' AND (p.name LIKE ? OR p.sku LIKE ? OR p.barcode LIKE ? OR p.product_code LIKE ?)';
                $like = '%' . $q . '%';
                array_push($params, $like, $like, $like, $like);
            }
            $sql .= ' ORDER BY p.name ASC LIMIT ' . max(1, min(100, $limit));
            $rows = Database::fetchAll($sql, $params);
            if ($rows) return $rows;
        } catch (Throwable $e) { error_log('POS product search failed: ' . $e->getMessage()); }
        if (APP_ENV === 'production') return [];
        $fallback = self::fallbackProducts();
        if ($q === '') return array_slice($fallback, 0, $limit);
        $needle = strtolower($q);
        return array_values(array_filter($fallback, function($p) use ($needle) {
            return str_contains(strtolower($p['name'] . ' ' . $p['sku'] . ' ' . $p['barcode']), $needle);
        }));
    }

    public static function productByCode(string $code, ?int $vendorId = null): ?array {
        $code = trim($code);
        if ($code === '') return null;
        try {
            $params = [$code, $code, $code, $code];
            $sql = 'SELECT p.id,p.vendor_id,p.name,p.sku,p.barcode,p.product_code,p.sale_price,p.regular_price,p.stock,p.image,v.shop_name AS vendor_name
                    FROM products p LEFT JOIN vendors v ON v.id=p.vendor_id
                    WHERE (p.barcode=? OR p.sku=? OR p.product_code=? OR p.id=?)';
            if ($vendorId) { $sql .= ' AND p.vendor_id=?'; $params[] = $vendorId; }
            $sql .= ' LIMIT 1';
            $row = Database::fetchOne($sql, $params);
            if ($row) return $row;
        } catch (Throwable $e) { error_log('POS product lookup failed: ' . $e->getMessage()); }
        if (APP_ENV !== 'production') {
            foreach (self::fallbackProducts() as $p) {
                if ((string)$p['barcode'] === $code || (string)$p['sku'] === $code || (string)$p['id'] === $code) return $p;
            }
        }
        return null;
    }

    public static function registers(?int $vendorId = null): array {
        try {
            if ($vendorId) return Database::fetchAll('SELECT * FROM pos_registers WHERE vendor_id=? ORDER BY id ASC', [$vendorId]);
            return Database::fetchAll('SELECT * FROM pos_registers ORDER BY id ASC LIMIT 50');
        } catch (Throwable $e) { return []; }
    }

    public static function defaultRegister(?int $vendorId = null): ?array {
        $role = (string)Auth::role();
        if (in_array($role, ['pos_manager','pos_cashier'], true)) {
            try {
                $assigned = Database::fetchOne('SELECT pr.* FROM pos_staff_assignments psa JOIN pos_registers pr ON pr.id=psa.register_id WHERE psa.user_id=? AND psa.status="active" AND pr.status="active" ORDER BY psa.id DESC LIMIT 1', [(int)Auth::id()]);
                if ($assigned) return $assigned;
            } catch (Throwable $e) {}
        }
        $registers = self::registers($vendorId);
        return $registers[0] ?? null;
    }

    public static function openShift(?int $registerId, int $cashierId, float $openingCash): array {
        if (!$registerId || $cashierId < 1) return ['ok'=>false,'message'=>'No POS register is assigned.'];
        $openingCash = max(0.0, round($openingCash, 2));
        try {
            return Database::transaction(function() use ($registerId,$cashierId,$openingCash) {
                $vendorId = (int)(self::currentVendorId() ?? 0);
                $register = Database::fetchOne('SELECT * FROM pos_registers WHERE id=? AND vendor_id=? AND status="active" LIMIT 1 FOR UPDATE', [$registerId,$vendorId]);
                if (!$register) throw new \RuntimeException('The register is inactive or not assigned to this vendor.');
                if (in_array((string)Auth::role(), ['pos_manager','pos_cashier'], true)) {
                    $assignment = Database::fetchOne(
                        'SELECT id,register_id FROM pos_staff_assignments WHERE user_id=? AND vendor_id=? AND status="active" ORDER BY id DESC LIMIT 1',
                        [$cashierId,$vendorId]
                    );
                    if (!$assignment || (!empty($assignment['register_id']) && (int)$assignment['register_id'] !== (int)$registerId)) {
                        throw new \RuntimeException('This cashier is not assigned to the selected register.');
                    }
                }
                $existing = Database::fetchOne('SELECT * FROM pos_shifts WHERE register_id=? AND cashier_id=? AND status="open" ORDER BY id DESC LIMIT 1 FOR UPDATE', [$registerId,$cashierId]);
                if ($existing) return ['ok'=>true,'shift'=>$existing,'message'=>'Shift already open.'];
                Database::execute('INSERT INTO pos_shifts (register_id,cashier_id,opening_cash,closing_cash,expected_cash,status,opened_at,created_at) VALUES (?,?,?,?,?,?,NOW(),NOW())', [$registerId,$cashierId,$openingCash,0,$openingCash,'open']);
                $id = Database::lastInsertId();
                Security::logActivity($cashierId, 'pos_shift_opened', ['shift_id'=>$id,'register_id'=>$registerId,'opening_cash'=>$openingCash]);
                return ['ok'=>true,'shift'=>Database::fetchOne('SELECT * FROM pos_shifts WHERE id=?', [$id]),'message'=>'Shift opened.'];
            });
        } catch (Throwable $e) {
            return ['ok'=>false,'message'=>$e instanceof \RuntimeException ? $e->getMessage() : 'Could not open the POS shift.'];
        }
    }

    public static function closeShift(int $shiftId, int $cashierId, float $closingCash): array {
        if ($shiftId < 1 || $cashierId < 1) return ['ok'=>false,'message'=>'Invalid shift.'];
        $closingCash = max(0.0, round($closingCash, 2));
        try {
            return Database::transaction(function() use ($shiftId,$cashierId,$closingCash) {
                $vendorId = (int)(self::currentVendorId() ?? 0);
                $shift = Database::fetchOne(
                    'SELECT ps.* FROM pos_shifts ps JOIN pos_registers pr ON pr.id=ps.register_id WHERE ps.id=? AND ps.cashier_id=? AND ps.status="open" AND pr.vendor_id=? LIMIT 1 FOR UPDATE',
                    [$shiftId,$cashierId,$vendorId]
                );
                if (!$shift) throw new \RuntimeException('The open shift was not found for this cashier and vendor.');
                $variance = round($closingCash - (float)$shift['expected_cash'], 2);
                Database::execute('UPDATE pos_shifts SET closing_cash=?,variance=?,status="closed",closed_at=NOW(),updated_at=NOW() WHERE id=?', [$closingCash,$variance,$shiftId]);
                Security::logActivity($cashierId, 'pos_shift_closed', ['shift_id'=>$shiftId,'closing_cash'=>$closingCash,'variance'=>$variance]);
                return ['ok'=>true,'variance'=>$variance,'message'=>'Shift closed successfully.'];
            });
        } catch (Throwable $e) {
            return ['ok'=>false,'message'=>$e instanceof \RuntimeException ? $e->getMessage() : 'Could not close the POS shift.'];
        }
    }

    public static function cashMovement(int $shiftId, int $cashierId, string $type, float $amount, string $reason = ''): array {
        $type = strtolower(trim($type));
        $amount = round($amount, 2);
        if (!in_array($type, ['cash_in','cash_out'], true) || $amount <= 0) return ['ok'=>false,'message'=>'Enter a valid cash movement.'];
        try {
            return Database::transaction(function() use ($shiftId,$cashierId,$type,$amount,$reason) {
                $vendorId = (int)(self::currentVendorId() ?? 0);
                $shift = Database::fetchOne(
                    'SELECT ps.id FROM pos_shifts ps JOIN pos_registers pr ON pr.id=ps.register_id WHERE ps.id=? AND ps.cashier_id=? AND ps.status="open" AND pr.vendor_id=? LIMIT 1 FOR UPDATE',
                    [$shiftId,$cashierId,$vendorId]
                );
                if (!$shift) throw new \RuntimeException('The open shift was not found.');
                Database::execute('INSERT INTO pos_cash_movements (shift_id,movement_type,amount,reason,created_by,created_at) VALUES (?,?,?,?,?,NOW())', [$shiftId,$type,$amount,substr($reason,0,1000),$cashierId]);
                $delta = $type === 'cash_in' ? $amount : -$amount;
                Database::execute('UPDATE pos_shifts SET expected_cash=GREATEST(0,expected_cash+?),updated_at=NOW() WHERE id=?', [$delta,$shiftId]);
                Security::logActivity($cashierId, 'pos_cash_movement', ['shift_id'=>$shiftId,'type'=>$type,'amount'=>$amount]);
                return ['ok'=>true,'message'=>'Cash movement saved.'];
            });
        } catch (Throwable $e) {
            return ['ok'=>false,'message'=>$e instanceof \RuntimeException ? $e->getMessage() : 'Could not save the cash movement.'];
        }
    }

    public static function currentShift(?int $registerId, int $cashierId): ?array {
        if (!$registerId) return null;
        try { return Database::fetchOne('SELECT * FROM pos_shifts WHERE register_id=? AND cashier_id=? AND status="open" ORDER BY id DESC LIMIT 1', [$registerId,$cashierId]); }
        catch (Throwable $e) { return null; }
    }

    public static function totals(array $items, float $discount = 0, float $taxPercent = 0): array {
        $subtotal = 0.0;
        foreach ($items as $item) {
            $subtotal += ((float)($item['price'] ?? 0)) * max(1, (int)($item['qty'] ?? 1));
        }
        $discount = max(0, min($subtotal, $discount));
        $taxable = max(0, $subtotal - $discount);
        $tax = round($taxable * max(0, $taxPercent) / 100, 2);
        $total = round($taxable + $tax, 2);
        return ['subtotal'=>$subtotal,'discount'=>$discount,'tax'=>$tax,'total'=>$total];
    }

    public static function createSale(array $payload): array {
        $rawItems = $payload['items'] ?? [];
        if (!is_array($rawItems) || !$rawItems) return ['ok'=>false,'message'=>'Cart is empty.'];
        if (count($rawItems) > 200) return ['ok'=>false,'message'=>'A POS sale cannot contain more than 200 cart lines.'];

        $role = (string)Auth::role();
        $cashierId = (int)Auth::id();
        $requestedVendorId = (int)($payload['vendor_id'] ?? 0);
        $assignedVendorId = self::currentVendorId();
        if (in_array($role, ['vendor','pos_manager','pos_cashier'], true)) {
            $vendorId = (int)($assignedVendorId ?? 0);
            if ($requestedVendorId > 0 && $requestedVendorId !== $vendorId) {
                return ['ok'=>false,'message'=>'The selected vendor is not assigned to this account.'];
            }
        } else {
            $vendorId = $requestedVendorId > 0 ? $requestedVendorId : (int)($assignedVendorId ?? 0);
        }
        if ($vendorId < 1) return ['ok'=>false,'message'=>'No valid vendor is assigned to this POS session.'];

        $registerId = (int)($payload['register_id'] ?? 0);
        $shiftId = (int)($payload['shift_id'] ?? 0);
        if ($registerId < 1 || $shiftId < 1) return ['ok'=>false,'message'=>'Open a valid register shift before completing a sale.'];

        $customerId = !empty($payload['customer_id']) ? (int)$payload['customer_id'] : null;
        $customerText = trim((string)($payload['customer_text'] ?? ''));
        $requestedDiscount = max(0.0, round((float)($payload['discount_amount'] ?? 0), 2));
        $taxPercent = max(0.0, min(100.0, round((float)($payload['tax_percent'] ?? 0), 4)));
        $rawPayments = $payload['payments'] ?? [];
        if (!is_array($rawPayments) || !$rawPayments) return ['ok'=>false,'message'=>'Select at least one payment method.'];
        if (count($rawPayments) > 10) return ['ok'=>false,'message'=>'Too many split-payment entries.'];

        $saleNo = self::saleNo();
        try {
            return Database::transaction(function() use (
                $vendorId,$registerId,$shiftId,$cashierId,$customerId,$customerText,$rawItems,
                $rawPayments,$requestedDiscount,$taxPercent,$saleNo,$payload
            ) {
                $register = Database::fetchOne(
                    'SELECT * FROM pos_registers WHERE id=? AND vendor_id=? AND status="active" LIMIT 1 FOR UPDATE',
                    [$registerId,$vendorId]
                );
                if (!$register) throw new \RuntimeException('The POS register is inactive or does not belong to this vendor.');

                $shift = Database::fetchOne(
                    'SELECT * FROM pos_shifts WHERE id=? AND register_id=? AND cashier_id=? AND status="open" LIMIT 1 FOR UPDATE',
                    [$shiftId,$registerId,$cashierId]
                );
                if (!$shift) throw new \RuntimeException('This shift is closed, expired, or belongs to another cashier.');

                if ($customerId) {
                    $customer = Database::fetchOne('SELECT id FROM users WHERE id=? AND role="customer" AND status="active" LIMIT 1', [$customerId]);
                    if (!$customer) throw new \RuntimeException('The selected customer account is unavailable.');
                } elseif ($customerText !== '') {
                    $customer = Database::fetchOne(
                        'SELECT id FROM users WHERE role="customer" AND status="active" AND (phone=? OR email=?) ORDER BY id DESC LIMIT 1',
                        [$customerText,$customerText]
                    );
                    if ($customer) $customerId = (int)$customer['id'];
                }

                // Merge duplicate product/variant lines before locking stock so one cart cannot oversell the same row.
                $requested = [];
                foreach ($rawItems as $item) {
                    if (!is_array($item)) continue;
                    $productId = (int)($item['product_id'] ?? $item['id'] ?? 0);
                    $variantId = max(0, (int)($item['variant_id'] ?? 0));
                    $qty = (int)($item['qty'] ?? 1);
                    if ($productId < 1 || $qty < 1 || $qty > 10000) throw new \RuntimeException('One or more cart quantities are invalid.');
                    $key = $productId . ':' . $variantId;
                    if (!isset($requested[$key])) $requested[$key] = ['product_id'=>$productId,'variant_id'=>$variantId,'qty'=>0];
                    $requested[$key]['qty'] += $qty;
                    if ($requested[$key]['qty'] > 10000) throw new \RuntimeException('One or more cart quantities are too large.');
                }
                if (!$requested) throw new \RuntimeException('Cart is empty.');

                $validatedItems = [];
                $subtotal = 0.0;
                foreach ($requested as $request) {
                    $product = Database::fetchOne(
                        'SELECT id,vendor_id,name,sku,barcode,sale_price,regular_price,stock,status
                         FROM products
                         WHERE id=? AND vendor_id=? AND status IN ("published","active","approved")
                         LIMIT 1 FOR UPDATE',
                        [$request['product_id'],$vendorId]
                    );
                    if (!$product) throw new \RuntimeException('A cart product is unavailable or belongs to another vendor.');

                    $variant = null;
                    $stock = (int)$product['stock'];
                    $sku = (string)($product['sku'] ?? '');
                    $unitPrice = (float)($product['sale_price'] ?: $product['regular_price']);
                    if ($request['variant_id'] > 0) {
                        $variant = Database::fetchOne(
                            'SELECT id,product_id,variant_name,sku,price,stock FROM product_variants WHERE id=? AND product_id=? LIMIT 1 FOR UPDATE',
                            [$request['variant_id'],$product['id']]
                        );
                        if (!$variant) throw new \RuntimeException('A selected product variant is unavailable.');
                        $stock = (int)$variant['stock'];
                        if (!empty($variant['sku'])) $sku = (string)$variant['sku'];
                        if ((float)$variant['price'] > 0) $unitPrice = (float)$variant['price'];
                    }
                    if ($unitPrice < 0) throw new \RuntimeException('A cart product has an invalid selling price.');
                    if ($stock < (int)$request['qty']) {
                        throw new \RuntimeException(($product['name'] ?? 'Product') . ' has only ' . $stock . ' item(s) in stock.');
                    }

                    $lineTotal = round($unitPrice * (int)$request['qty'], 2);
                    $subtotal += $lineTotal;
                    $validatedItems[] = [
                        'product'=>$product,
                        'variant'=>$variant,
                        'variant_id'=>(int)$request['variant_id'],
                        'qty'=>(int)$request['qty'],
                        'unit_price'=>round($unitPrice, 2),
                        'line_total'=>$lineTotal,
                        'sku'=>$sku,
                        'balance_after'=>$stock - (int)$request['qty'],
                    ];
                }

                $subtotal = round($subtotal, 2);
                $discount = min($subtotal, $requestedDiscount);
                $taxable = max(0.0, $subtotal - $discount);
                $taxAmount = round($taxable * $taxPercent / 100, 2);
                $total = round($taxable + $taxAmount, 2);

                $allowedMethods = ['cash','bkash','nagad','card','wallet','bank','other'];
                $payments = [];
                $paid = 0.0;
                $cashPaid = 0.0;
                $walletPaid = 0.0;
                foreach ($rawPayments as $payment) {
                    if (!is_array($payment)) continue;
                    $method = strtolower(trim((string)($payment['method'] ?? 'cash')));
                    if (!in_array($method, $allowedMethods, true)) throw new \RuntimeException('Unsupported POS payment method.');
                    $amount = round((float)($payment['amount'] ?? 0), 2);
                    if ($amount <= 0) continue;
                    $payments[] = [
                        'method'=>$method,
                        'amount'=>$amount,
                        'reference_no'=>substr(trim((string)($payment['reference_no'] ?? '')),0,190),
                        'provider'=>substr(trim((string)($payment['provider'] ?? '')),0,80),
                    ];
                    $paid += $amount;
                    if ($method === 'cash') $cashPaid += $amount;
                    if ($method === 'wallet') $walletPaid += $amount;
                }
                $paid = round($paid, 2);
                if (!$payments) throw new \RuntimeException('Enter a valid payment amount.');
                if ($paid + 0.009 < $total) throw new \RuntimeException('Paid amount is lower than the server-calculated sale total.');
                $change = round(max(0.0, $paid - $total), 2);
                if ($change > $cashPaid + 0.009) throw new \RuntimeException('Non-cash payments cannot create cash change.');
                if ($walletPaid > 0) {
                    if (!$customerId) throw new \RuntimeException('Select a customer before using wallet payment.');
                    if (!WalletService::debit($customerId, 'purchase', $walletPaid, 'POS payment ' . $saleNo)) {
                        throw new \RuntimeException('The customer purchase wallet has insufficient balance.');
                    }
                }

                Database::execute(
                    'INSERT INTO pos_sales
                    (sale_no,vendor_id,register_id,shift_id,cashier_id,customer_id,subtotal,discount_amount,tax_amount,total_amount,paid_amount,change_amount,payment_status,sale_status,note,created_at,updated_at)
                    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW())',
                    [$saleNo,$vendorId,$registerId,$shiftId,$cashierId,$customerId,$subtotal,$discount,$taxAmount,$total,$paid,$change,'paid','completed',substr((string)($payload['note'] ?? ''),0,1000),date('Y-m-d H:i:s')]
                );
                $saleId = Database::lastInsertId();

                foreach ($validatedItems as $item) {
                    $product = $item['product'];
                    $variant = $item['variant'];
                    $displayName = (string)$product['name'];
                    if ($variant && !empty($variant['variant_name'])) $displayName .= ' - ' . $variant['variant_name'];
                    Database::execute(
                        'INSERT INTO pos_sale_items
                        (sale_id,product_id,variant_id,sku,barcode,product_name,qty,unit_price,discount_amount,line_total,created_at)
                        VALUES (?,?,?,?,?,?,?,?,?,?,NOW())',
                        [$saleId,$product['id'],$item['variant_id'] ?: null,$item['sku'],$product['barcode'] ?? '',$displayName,$item['qty'],$item['unit_price'],0,$item['line_total']]
                    );

                    if ($item['variant_id'] > 0) {
                        Database::execute('UPDATE product_variants SET stock=stock-?,updated_at=NOW() WHERE id=? AND stock>=?', [$item['qty'],$item['variant_id'],$item['qty']]);
                    }
                    Database::execute('UPDATE products SET stock=stock-?,sold_count=sold_count+?,updated_at=NOW() WHERE id=? AND stock>=?', [$item['qty'],$item['qty'],$product['id'],$item['qty']]);
                    Database::execute(
                        'INSERT INTO pos_stock_movements (sale_id,product_id,variant_id,vendor_id,movement_type,qty,note,created_by,created_at) VALUES (?,?,?,?,?,?,?,?,NOW())',
                        [$saleId,$product['id'],$item['variant_id'] ?: null,$vendorId,'sale',-$item['qty'],'POS sale '.$saleNo,$cashierId]
                    );
                    Database::execute(
                        'INSERT INTO stock_ledgers (product_id,variant_id,vendor_id,movement_type,quantity,balance_after,note,created_by,created_at) VALUES (?,?,?,?,?,?,?,?,NOW())',
                        [$product['id'],$item['variant_id'] ?: null,$vendorId,'pos_sale',-$item['qty'],$item['balance_after'],'POS sale '.$saleNo,$cashierId]
                    );
                }

                foreach ($payments as $payment) {
                    Database::execute(
                        'INSERT INTO pos_payments (sale_id,payment_method,amount,reference_no,provider,status,created_at) VALUES (?,?,?,?,?,?,NOW())',
                        [$saleId,$payment['method'],$payment['amount'],$payment['reference_no'],$payment['provider'],'paid']
                    );
                }

                $netCash = round(max(0.0, $cashPaid - $change), 2);
                if ($netCash > 0) Database::execute('UPDATE pos_shifts SET expected_cash=expected_cash+?,updated_at=NOW() WHERE id=?', [$netCash,$shiftId]);
                Security::logActivity($cashierId, 'pos_sale_completed', [
                    'sale_id'=>$saleId,'sale_no'=>$saleNo,'vendor_id'=>$vendorId,'total'=>$total,
                    'discount'=>$discount,'tax'=>$taxAmount,'payment_methods'=>array_column($payments,'method')
                ]);
                return [
                    'ok'=>true,'sale_id'=>$saleId,'sale_no'=>$saleNo,
                    'totals'=>['subtotal'=>$subtotal,'discount'=>$discount,'tax'=>$taxAmount,'total'=>$total],
                    'paid'=>$paid,'change'=>$change,'message'=>'Sale completed.'
                ];
            });
        } catch (Throwable $e) {
            error_log('POS sale failed: ' . $e->getMessage());
            $safe = $e instanceof \RuntimeException ? $e->getMessage() : 'Could not complete POS sale. Check the database and server log.';
            return ['ok'=>false,'message'=>$safe];
        }
    }

    public static function createRefund(string $saleNo, float $amount, string $method, string $reason = ''): array {
        $saleNo = trim($saleNo);
        $amount = round($amount, 2);
        $method = strtolower(trim($method));
        if ($saleNo === '' || $amount <= 0) return ['ok'=>false,'message'=>'Enter a valid sale number and refund amount.'];
        if (!in_array($method, ['cash','bkash','nagad','card','wallet','bank','other'], true)) return ['ok'=>false,'message'=>'Unsupported refund method.'];
        $userId = (int)Auth::id();
        $vendorId = (int)(self::currentVendorId() ?? 0);
        if ($vendorId < 1) return ['ok'=>false,'message'=>'No vendor is assigned to this POS session.'];
        try {
            return Database::transaction(function() use ($saleNo,$amount,$method,$reason,$userId,$vendorId) {
                $sale = Database::fetchOne('SELECT * FROM pos_sales WHERE sale_no=? AND vendor_id=? LIMIT 1 FOR UPDATE', [$saleNo,$vendorId]);
                if (!$sale) throw new \RuntimeException('Sale not found for this vendor.');
                if (!in_array((string)$sale['sale_status'], ['completed','partially_refunded','refunded'], true)) throw new \RuntimeException('This sale cannot be refunded in its current state.');
                $refundedRow = Database::fetchOne('SELECT COALESCE(SUM(amount),0) total FROM pos_refunds WHERE sale_id=? AND status IN ("approved","paid","completed")', [$sale['id']]);
                $alreadyRefunded = round((float)($refundedRow['total'] ?? 0), 2);
                $remaining = round(max(0.0, (float)$sale['total_amount'] - $alreadyRefunded), 2);
                if ($remaining <= 0) throw new \RuntimeException('This sale is already fully refunded.');
                if ($amount > $remaining + 0.009) throw new \RuntimeException('Refund exceeds the remaining refundable amount of ' . number_format($remaining, 2) . '.');
                if ($method === 'wallet') {
                    $customerId = (int)($sale['customer_id'] ?? 0);
                    if ($customerId < 1) throw new \RuntimeException('Wallet refunds require a customer account on the original sale.');
                    if (!WalletService::credit($customerId, 'purchase', $amount, 'POS refund ' . $saleNo, (int)$sale['id'])) {
                        throw new \RuntimeException('Could not credit the customer wallet.');
                    }
                }
                $refundNo = 'REF-' . date('Ymd-His') . '-' . random_int(100,999);
                Database::execute(
                    'INSERT INTO pos_refunds (sale_id,refund_no,amount,method,reason,status,created_by,created_at,updated_at) VALUES (?,?,?,?,?,?,?,NOW(),NOW())',
                    [$sale['id'],$refundNo,$amount,$method,substr($reason,0,2000),'approved',$userId]
                );
                $newRefunded = round($alreadyRefunded + $amount, 2);
                $status = $newRefunded + 0.009 >= (float)$sale['total_amount'] ? 'refunded' : 'partially_refunded';
                Database::execute('UPDATE pos_sales SET sale_status=?,updated_at=NOW() WHERE id=?', [$status,$sale['id']]);

                if ($method === 'cash') {
                    $shift = Database::fetchOne('SELECT * FROM pos_shifts WHERE register_id=? AND cashier_id=? AND status="open" ORDER BY id DESC LIMIT 1 FOR UPDATE', [$sale['register_id'],$userId]);
                    if ($shift) {
                        Database::execute('INSERT INTO pos_cash_movements (shift_id,movement_type,amount,reason,created_by,created_at) VALUES (?,"cash_out",?,?,?,NOW())', [$shift['id'],$amount,'Refund '.$refundNo,$userId]);
                        Database::execute('UPDATE pos_shifts SET expected_cash=GREATEST(0,expected_cash-?),updated_at=NOW() WHERE id=?', [$amount,$shift['id']]);
                    }
                }
                Security::logActivity($userId, 'pos_refund_created', ['sale_id'=>(int)$sale['id'],'refund_no'=>$refundNo,'amount'=>$amount,'method'=>$method]);
                return ['ok'=>true,'refund_no'=>$refundNo,'remaining'=>round(max(0.0,$remaining-$amount),2),'message'=>'Refund approved and logged.'];
            });
        } catch (Throwable $e) {
            return ['ok'=>false,'message'=>$e instanceof \RuntimeException ? $e->getMessage() : 'Could not create the refund.'];
        }
    }

    public static function receipt(int $saleId, ?int $vendorId = null): ?array {
        try {
            $vendorId ??= self::currentVendorId();
            if (!$vendorId) return null;
            $sale = Database::fetchOne('SELECT ps.*,u.name AS cashier_name,v.shop_name FROM pos_sales ps LEFT JOIN users u ON u.id=ps.cashier_id LEFT JOIN vendors v ON v.id=ps.vendor_id WHERE ps.id=? AND ps.vendor_id=? LIMIT 1', [$saleId,$vendorId]);
            if (!$sale) return null;
            $items = Database::fetchAll('SELECT * FROM pos_sale_items WHERE sale_id=? ORDER BY id ASC', [$saleId]);
            $payments = Database::fetchAll('SELECT * FROM pos_payments WHERE sale_id=? ORDER BY id ASC', [$saleId]);
            return ['sale'=>$sale,'items'=>$items,'payments'=>$payments];
        } catch (Throwable $e) { return null; }
    }
}
