<?php
namespace App\Models;

use CodeIgniter\Model;

class OrderModel extends Model
{
    protected $table = 'cart';
    protected $primaryKey = 'id';
    protected $allowedFields = ['product_id','variant_id','user_id','quantity'];

    // ----------------------------
    // CART FUNCTIONS
    // ----------------------------


public function insertOrder($orderData, $cartItems)
{
    $db = \Config\Database::connect();

    // Insert into orders table
    $db->table('orders')->insert($orderData);
    $order_id = $db->insertID(); // get last inserted ID

    // Insert items into order_items table
    foreach($cartItems as $item){
        $db->table('order_items')->insert([
            'order_id'     => $order_id,
            'product_id'   => $item['id'],
            'product_name' => $item['name'],
            'variant'      => $item['unit'],
            'price'        => $item['offer_price'],
            'quantity'     => (int)$item['quantity'],
            'subtotal'     => (float)$item['offer_price'] * (int)$item['quantity']
        ]);
    }

    // Optional: Clear user's cart
    $this->where('user_id', session()->get('user_id'))->delete();

    return $order_id;
}



    public function addToCartItem($productId, $variantId, $quantity, $userId)
    {
        $existing = $this->where([
            'product_id' => $productId,
            'variant_id' => $variantId,
            'user_id' => $userId
        ])->first();

        if($existing){
            return $this->update($existing['id'], [
                'quantity' => $existing['quantity'] + $quantity
            ]);
        }

        return $this->insert([
            'product_id' => $productId,
            'variant_id' => $variantId,
            'user_id' => $userId,
            'quantity' => $quantity
        ]);
    }

    public function getCartCount($userId)
    {
        return $this->where('user_id', $userId)
                    ->selectSum('quantity')
                    ->first()['quantity'] ?? 0;
    }

public function getCartItems($userId)
{
    $items = $this->where('cart.user_id', $userId)
        ->select('cart.id, products.name, products.image, product_variants.unit, product_variants.offer_price, cart.quantity')
        ->join('products', 'products.id = cart.product_id')
        ->join('product_variants', 'product_variants.id = cart.variant_id')
        ->findAll();

    // Ensure fallback image exists
    foreach ($items as &$item) {
        $item['image'] = !empty($item['image']) && file_exists(FCPATH.'uploads/products/'.$item['image'])
                         ? $item['image']
                         : 'no-image.png';
    }

    return $items;
}



    

    public function emptyTable($userId)
    {
        $this->where('user_id', $userId)->delete();
    }

    // ----------------------------
    // PRODUCT FUNCTIONS
    // ----------------------------

    /**
     * Get products grouped by categories
     */
    public function getAllProductsByCategory()
    {
        $db = \Config\Database::connect();
        $builder = $db->table('products p');
        $builder->select('
            p.id, 
            p.name, 
            p.image, 
            p.category_id,
            c.category_name,
            pv.id as variant_id, 
            pv.unit, 
            pv.price, 
            pv.offer_price, 
            pv.quantity
        ');
        $builder->join('categories c','c.id = p.category_id','left');
        $builder->join('product_variants pv','pv.product_id = p.id','left');
        $query = $builder->get();

        $categories = [];

        foreach($query->getResultArray() as $row){
            $catName = $row['category_name'] ?? 'Uncategorized';

            if(!isset($categories[$catName])){
                $categories[$catName] = [];
            }

            $pid = $row['id'];

            if(!isset($categories[$catName][$pid])){
                $categories[$catName][$pid] = [
                    'info'=>[
                        'id'=>$row['id'],
                        'name'=>$row['name'],
                        'image'=>$row['image'],
                        'category_id'=>$row['category_id']
                    ],
                    'variants'=>[]
                ];
            }

            // Attach variant only if exists
            if($row['variant_id']){
                $categories[$catName][$pid]['variants'][] = [
                    'variant_id'=>$row['variant_id'],
                    'unit'=>$row['unit'],
                    'price'=>$row['price'],
                    'offer_price'=>$row['offer_price'],
                    'quantity'=>$row['quantity']
                ];
            }
        }

        return $categories; // category → products → variants
    }
    
    
}
