<?php
class Sale extends Model {
    protected static string $table = 'sales';

    public static function createWithItems(array $sale, array $items, int $locationId): int {
        $tid = static::tenantId();
        $db = db();
        $db->beginTransaction();
        try {
            // Backward compatibility for callers still using paid_amount.
            if (array_key_exists('paid_amount', $sale) && !array_key_exists('amount_paid', $sale)) {
                $sale['amount_paid'] = $sale['paid_amount'];
                unset($sale['paid_amount']);
            }

            $sale['tenant_id']   = $tid;
            $sale['location_id'] = $locationId;
            $sale['user_id']     = Auth::userId();
            $sale['sale_no']     = generateRef(setting('sale_prefix', 'SAL'));

            // Calculate totals if not provided or to verify
            $subtotal = 0;
            foreach ($items as $item) {
                $subtotal += $item['quantity'] * $item['unit_price'];
            }
            $sale['subtotal'] = $subtotal;
            $sale['total']    = $subtotal + ($sale['tax_amount'] ?? 0) - ($sale['discount_amount'] ?? 0);
            if (isset($sale['amount_paid']) && $sale['amount_paid'] > $sale['total']) {
                $sale['amount_paid'] = $sale['total'];
            }

            $cols = implode(',', array_map(fn($c) => "`{$c}`", array_keys($sale)));
            $vals = implode(',', array_fill(0, count($sale), '?'));
            $db->prepare("INSERT INTO sales ({$cols}) VALUES ({$vals})")->execute(array_values($sale));
            $saleId = (int)$db->lastInsertId();

            foreach ($items as $item) {
                $item['tenant_id'] = $tid;
                $item['sale_id']   = $saleId;
                $lineDiscount = (float)($item['discount'] ?? 0);
                $item['total'] = (float)$item['quantity'] * (float)$item['unit_price'] - $lineDiscount;

                ensureStockAvailable($item['product_id'], $locationId, -$item['quantity'], $item['batch_id'] ?? null, $tid);

                $c = implode(',', array_map(fn($k) => "`{$k}`", array_keys($item)));
                $v = implode(',', array_fill(0, count($item), '?'));
                $db->prepare("INSERT INTO sale_items ({$c}) VALUES ({$v})")->execute(array_values($item));

                // Deduct stock
                adjustStock($item['product_id'], $locationId, -$item['quantity'], $item['batch_id'] ?? null, $tid);
                logMovement($item['product_id'], $locationId, 'sale', -$item['quantity'], $sale['sale_no']);
            }

            // Update customer balance if credit/partial
            if (!empty($sale['customer_id']) && $sale['payment_status'] !== 'paid') {
                $due = $sale['total'] - ($sale['amount_paid'] ?? $sale['paid_amount'] ?? 0);
                if ($due > 0) {
                    $db->prepare("UPDATE customers SET balance = balance + ? WHERE id = ? AND tenant_id = ?")
                       ->execute([$due, $sale['customer_id'], $tid]);
                }
            }

            // Record in customer ledger if customer exists
            if (!empty($sale['customer_id'])) {
                // Only record debit if payment status is not 'paid'
                if (($sale['payment_status'] ?? '') !== 'paid') {
                    $due = $sale['total'] - ($sale['amount_paid'] ?? 0);
                    if ($due > 0) {
                        CustomerLedger::addEntry([
                            'customer_id' => $sale['customer_id'],
                            'transaction_type' => 'sale',
                            'reference_id' => $saleId,
                            'reference_no' => $sale['sale_no'],
                            'debit' => $due,
                            'credit' => 0,
                            'description' => 'Sale ' . $sale['sale_no'],
                            'transaction_date' => date('Y-m-d')
                        ]);
                    }
                }
            }

            $db->commit();
            return $saleId;
        } catch (Exception $e) {
            $db->rollBack();
            throw $e;
        }
    }

    public static function withItems(int $id): ?array {
        $sale = static::find($id);
        if (!$sale) return null;
        $tid = static::tenantId();

        // Fetch user details
        if (!empty($sale['user_id'])) {
            $stmt = db()->prepare("SELECT id, name FROM users WHERE id=? AND tenant_id=?");
            $stmt->execute([$sale['user_id'], $tid]);
            $sale['user'] = $stmt->fetch();
        }

        $stmt = db()->prepare(
            "SELECT si.*, p.name as product_name, p.sku, p.unit FROM sale_items si
             JOIN products p ON p.id = si.product_id
             WHERE si.sale_id = ? AND si.tenant_id = ?"
        );
        $stmt->execute([$id, $tid]);
        $sale['items'] = $stmt->fetchAll();
        return $sale;
    }

    public static function listPaginated(int $page = 1, array $filters = []): array {
        $tid = static::tenantId();
        $where = "s.tenant_id = ?";
        $params = [$tid];

        if (!empty($filters['search'])) {
            $where .= " AND (s.sale_no LIKE ? OR c.name LIKE ?)";
            $searchParam = "%{$filters['search']}%";
            $params[] = $searchParam;
            $params[] = $searchParam;
        }
        if (!empty($filters['date_from'])) { $where .= " AND DATE(s.created_at) >= ?"; $params[] = $filters['date_from']; }
        if (!empty($filters['date_to'])) { $where .= " AND DATE(s.created_at) <= ?"; $params[] = $filters['date_to']; }
        if (!empty($filters['status'])) { $where .= " AND s.status = ?"; $params[] = $filters['status']; }
        if (!empty($filters['location_id'])) { $where .= " AND s.location_id = ?"; $params[] = $filters['location_id']; }
        if (!empty($filters['user_id'])) { $where .= " AND s.user_id = ?"; $params[] = $filters['user_id']; }

        $perPage = $_GET['per_page'] ?? ITEMS_PER_PAGE;
        $pp = $perPage === 'all' ? PHP_INT_MAX : (int)$perPage;
        $off = ($page-1)*$pp;
        $cnt = db()->prepare("SELECT COUNT(*) FROM sales s LEFT JOIN customers c ON c.id = s.customer_id WHERE {$where}");
        $cnt->execute($params); $total = (int)$cnt->fetchColumn();

        // Calculate totals from ALL matching records (not just current page)
        // Exclude refunded sales to prevent negative outstanding values
        $totalsWhere = $where . " AND s.status != 'refunded'";
        $totalsSql = "SELECT COALESCE(SUM(s.total), 0) as total_amount, COALESCE(SUM(LEAST(s.amount_paid, s.total)), 0) as total_paid
                      FROM sales s
                      LEFT JOIN customers c ON c.id = s.customer_id
                      WHERE {$totalsWhere}";
        $totalsStmt = db()->prepare($totalsSql);
        $totalsStmt->execute($params);
        $totals = $totalsStmt->fetch();

        // Calculate grouping by payment method
        $paymentMethodsSql = "SELECT s.payment_method, COALESCE(SUM(LEAST(s.amount_paid, s.total)), 0) as amount
                              FROM sales s
                              LEFT JOIN customers c ON c.id = s.customer_id
                              WHERE {$totalsWhere} AND s.amount_paid > 0
                              GROUP BY s.payment_method";
        $paymentMethodsStmt = db()->prepare($paymentMethodsSql);
        $paymentMethodsStmt->execute($params);
        $paymentMethods = [];
        foreach ($paymentMethodsStmt->fetchAll() as $row) {
            $method = $row['payment_method'] ?: 'unknown';
            $paymentMethods[$method] = (float)$row['amount'];
        }

        $sql = "SELECT s.*, c.name as customer_name, l.name as location_name, u.name as user_name
                FROM sales s
                LEFT JOIN customers c ON c.id = s.customer_id
                LEFT JOIN locations l ON l.id = s.location_id
                LEFT JOIN users u ON u.id = s.user_id
                WHERE {$where} ORDER BY s.created_at DESC LIMIT {$pp} OFFSET {$off}";
        $stmt = db()->prepare($sql); $stmt->execute($params);

        return [
            'data' => $stmt->fetchAll(),
            'total' => $total,
            'last_page' => $perPage === 'all' ? 1 : (int)ceil($total/$pp),
            'page' => $page,
            'totals' => [
                'total_amount' => (float)$totals['total_amount'],
                'total_paid' => (float)$totals['total_paid'],
                'outstanding' => max(0, (float)$totals['total_amount'] - (float)$totals['total_paid']),
                'by_method' => $paymentMethods
            ]
        ];
    }
}
