<?php

namespace Modules\Inventory\Models;

use core\Model;
use core\Database;

class Product extends Model
{
    protected string $table = 'products';
    protected string $primaryKey = 'id';
    protected bool $timestamps = true;

    protected array $fillable = [
        'name', 'sku', 'barcode', 'category_id', 'supplier_id', 'description',
        'unit_price', 'cost_price', 'tax_percent', 'reorder_level', 'current_stock',
        'min_stock', 'max_stock', 'unit_type', 'track_inventory', 'is_active',
    ];

    protected array $casts = [
        'unit_price' => 'float',
        'cost_price' => 'float',
        'tax_percent' => 'float',
        'current_stock' => 'int',
        'reorder_level' => 'int',
        'min_stock' => 'int',
        'max_stock' => 'int',
        'track_inventory' => 'bool',
        'is_active' => 'bool',
    ];

    public function search(string $query): array
    {
        $db = Database::getInstance();
        return $db->table('products p')
            ->select('p.id, p.name, p.sku, p.barcode, p.unit_price, p.current_stock, p.track_inventory,
                     c.name as category_name')
            ->leftJoin('product_categories c', 'p.category_id', '=', 'c.id')
            ->where('p.deleted_at', 'IS', null)
            ->where('p.is_active', 1)
            ->where(function ($q) use ($query) {
                $q->where('p.name', 'LIKE', '%' . $query . '%')
                  ->orWhere('p.sku', 'LIKE', '%' . $query . '%')
                  ->orWhere('p.barcode', 'LIKE', '%' . $query . '%');
            })
            ->orderBy('p.name', 'ASC')
            ->limit(50)
            ->get();
    }

    public function getLowStock(?int $branchId = null): array
    {
        $db = Database::getInstance();
        $query = $db->table('products p')
            ->select('p.*, c.name as category_name, s.name as supplier_name')
            ->leftJoin('product_categories c', 'p.category_id', '=', 'c.id')
            ->leftJoin('suppliers s', 'p.supplier_id', '=', 's.id')
            ->where('p.deleted_at', 'IS', null)
            ->where('p.is_active', 1)
            ->where('p.track_inventory', 1)
            ->whereRaw('p.current_stock <= p.reorder_level');

        if ($branchId) {
            $query->where('p.branch_id', $branchId);
        }

        return $query->orderBy('p.current_stock', 'ASC')
            ->get();
    }

    public function getExpiring(int $days = 30): array
    {
        $db = Database::getInstance();
        return $db->table('product_batches pb')
            ->select('pb.*, p.name as product_name, p.sku, p.unit_price,
                     s.name as supplier_name')
            ->leftJoin('products p', 'pb.product_id', '=', 'p.id')
            ->leftJoin('suppliers s', 'p.supplier_id', '=', 's.id')
            ->where('pb.expiry_date', '<=', date('Y-m-d', strtotime('+' . $days . ' days')))
            ->where('pb.expiry_date', '>=', date('Y-m-d'))
            ->where('pb.quantity', '>', 0)
            ->where('p.deleted_at', 'IS', null)
            ->orderBy('pb.expiry_date', 'ASC')
            ->get();
    }

    public function getBySupplier(int $supplierId): array
    {
        $db = Database::getInstance();
        return $db->table('products')
            ->where('supplier_id', $supplierId)
            ->where('deleted_at', 'IS', null)
            ->where('is_active', 1)
            ->orderBy('name', 'ASC')
            ->get();
    }

    public function getStockValue(): array
    {
        $db = Database::getInstance();
        $result = $db->table('products')
            ->select('
                SUM(current_stock * cost_price) as total_cost_value,
                SUM(current_stock * unit_price) as total_retail_value,
                COUNT(*) as total_products,
                SUM(current_stock) as total_units
            ')
            ->where('deleted_at', 'IS', null)
            ->where('is_active', 1)
            ->first();

        return [
            'total_cost_value' => (float)($result->total_cost_value ?? 0),
            'total_retail_value' => (float)($result->total_retail_value ?? 0),
            'total_products' => (int)($result->total_products ?? 0),
            'total_units' => (int)($result->total_units ?? 0),
        ];
    }

    public function updateStock(int $id, int $quantity): void
    {
        $db = Database::getInstance();
        $db->table('products')
            ->where('id', $id)
            ->update([
                'current_stock' => $quantity,
                'updated_at' => date(DATE_TIME_FORMAT),
            ]);
    }

    public function deductStock(int $id, int $quantity): void
    {
        $db = Database::getInstance();
        $db->query(
            "UPDATE products SET current_stock = current_stock - :qty, updated_at = :updated WHERE id = :id",
            [
                ':qty' => $quantity,
                ':updated' => date(DATE_TIME_FORMAT),
                ':id' => $id,
            ]
        );
    }
}
