<?php
class Purchase extends Model {
    protected static string $table = 'purchases';

    public static function createWithItems(array $purchase, array $items, int $locationId): int {
        $tid = static::tenantId();
        $db  = db();
        $db->beginTransaction();
        try {
            $purchase['tenant_id']   = $tid;
            $purchase['location_id'] = $locationId;
            $purchase['user_id']     = Auth::userId();
            $purchase['purchase_no'] = generateRef(setting('purchase_prefix','PUR'));

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

            foreach ($items as $item) {
                $batchId = null;
                if (!empty($item['batch_no'])) {
                    $batchId = Batch::findOrCreate($item['product_id'], $item['batch_no'], $item['expiry_date'] ?? null);
                }
                $item['tenant_id']   = $tid;
                $item['purchase_id'] = $purchaseId;
                $item['batch_id']    = $batchId;
                $c = implode(',', array_map(fn($k) => "`{$k}`", array_keys($item)));
                $v = implode(',', array_fill(0, count($item), '?'));
                $db->prepare("INSERT INTO purchase_items ({$c}) VALUES ({$v})")->execute(array_values($item));

                // Add stock
                adjustStock($item['product_id'], $locationId, $item['quantity'], $batchId, $tid);
                logMovement($item['product_id'], $locationId, 'purchase', $item['quantity'], $purchase['purchase_no']);
                
                // Update product last cost price
                $db->prepare("UPDATE products SET cost_price = ? WHERE id = ? AND tenant_id = ?")
                   ->execute([$item['unit_cost'], $item['product_id'], $tid]);
            }

            // Update supplier balance if credit
            if (!empty($purchase['supplier_id']) && ($purchase['payment_status'] ?? '') === 'credit') {
                $db->prepare("UPDATE suppliers SET balance = balance + ? WHERE id = ? AND tenant_id = ?")
                   ->execute([$purchase['total'], $purchase['supplier_id'], $tid]);
            }

            // Record in supplier ledger if supplier exists
            if (!empty($purchase['supplier_id'])) {
                // Only record debit if payment status is not 'paid'
                if (($purchase['payment_status'] ?? '') !== 'paid') {
                    $due = $purchase['total'] - ($purchase['amount_paid'] ?? 0);
                    if ($due > 0) {
                        SupplierLedger::addEntry([
                            'supplier_id' => $purchase['supplier_id'],
                            'transaction_type' => 'purchase',
                            'reference_id' => $purchaseId,
                            'reference_no' => $purchase['purchase_no'],
                            'debit' => $due,
                            'credit' => 0,
                            'description' => 'Purchase ' . $purchase['purchase_no'],
                            'transaction_date' => date('Y-m-d')
                        ]);
                    }
                }
            }

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

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

        // Fetch supplier details
        if (!empty($purchase['supplier_id'])) {
            $stmt = db()->prepare("SELECT id, name, email, phone FROM suppliers WHERE id=? AND tenant_id=?");
            $stmt->execute([$purchase['supplier_id'], $tid]);
            $purchase['supplier'] = $stmt->fetch();
        }

        // Fetch location details
        $stmt = db()->prepare("SELECT id, name FROM locations WHERE id=? AND tenant_id=?");
        $stmt->execute([$purchase['location_id'], $tid]);
        $purchase['location'] = $stmt->fetch();

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

        // Fetch tenant business details
        $purchase['tenant'] = Auth::tenant();

        // Fetch items with unit_cost
        $stmt = db()->prepare(
            "SELECT pi.*,p.name as product_name,p.sku,pi.unit_cost as cost_price FROM purchase_items pi
             JOIN products p ON p.id=pi.product_id
             WHERE pi.purchase_id=? AND pi.tenant_id=?"
        );
        $stmt->execute([$id,$tid]);
        $purchase['items'] = $stmt->fetchAll();

        return $purchase;
    }

    public static function listPaginated(int $page=1, array $filters=[]): array {
        $tid  = static::tenantId();
        $where= "pu.tenant_id=:tid";
        $p    = [':tid'=>$tid];
        if (!empty($filters['search']))    { $where .= " AND (pu.purchase_no LIKE :q OR s.name LIKE :q)"; $p[':q']="%{$filters['search']}%"; }
        if (!empty($filters['date_from'])) { $where .= " AND DATE(pu.created_at)>=:df"; $p[':df']=$filters['date_from']; }
        if (!empty($filters['date_to']))   { $where .= " AND DATE(pu.created_at)<=:dt"; $p[':dt']=$filters['date_to']; }

        $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 purchases pu LEFT JOIN suppliers s ON s.id=pu.supplier_id WHERE {$where}");
        $cnt->execute($p); $total = (int)$cnt->fetchColumn();

        $sql = "SELECT pu.id, pu.purchase_no, pu.created_at, pu.supplier_id, pu.location_id, pu.subtotal, pu.tax_amount, pu.discount_amount, pu.total, pu.amount_paid, pu.payment_method, pu.payment_status, pu.status, pu.note, s.name as supplier_name, l.name as location_name
                FROM purchases pu
                LEFT JOIN suppliers s ON s.id=pu.supplier_id
                LEFT JOIN locations l ON l.id=pu.location_id
                WHERE {$where} ORDER BY pu.created_at DESC LIMIT {$pp} OFFSET {$off}";
        $stmt = db()->prepare($sql); $stmt->execute($p);
        return ['data'=>$stmt->fetchAll(),'total'=>$total,'last_page'=>$perPage === 'all' ? 1 : (int)ceil($total/$pp),'page'=>$page];
    }
}
