<?php
declare(strict_types=1);

namespace App\Modules\Inventory\Models;

use App\Core\Model;
use App\Core\App;

class InventoryItem extends Model
{
    protected static string $table = 'inventory_items';
    protected static string $primaryKey = 'id';
    protected static bool $timestamps = true;
    protected static bool $softDelete = true;
    protected static bool $autoTrackAuthor = true;

    protected static array $fillable = [
        'category_id',
        'sku',
        'barcode',
        'name_ar',
        'name_en',
        'description_ar',
        'unit_of_measure',
        'tracking_type',
        'cost_price',
        'sale_price_member',
        'sale_price_nonmember',
        'sale_price_player',
        'tax_rate',
        'is_sellable',
        'is_active',
        'image_document_id',
        'notes',
    ];

    /**
     * Get all tracking types with Arabic labels.
     */
    public static function getTrackingTypes(): array
    {
        return [
            'standard' => 'قياسي',
            'expiry'   => 'بتاريخ صلاحية',
            'asset'    => 'أصل ثابت',
        ];
    }

    /**
     * Get the Arabic label for a tracking type.
     */
    public static function getTrackingTypeLabel(string $type): string
    {
        $types = self::getTrackingTypes();
        return $types[$type] ?? $type;
    }

    /**
     * Get all units of measure with Arabic labels.
     */
    public static function getUnitsOfMeasure(): array
    {
        return [
            'piece' => 'قطعة',
            'kg'    => 'كيلوجرام',
            'liter' => 'لتر',
            'box'   => 'صندوق',
            'pack'  => 'عبوة',
            'meter' => 'متر',
            'set'   => 'طقم',
        ];
    }

    /**
     * Get the Arabic label for a unit of measure.
     */
    public static function getUnitLabel(string $unit): string
    {
        $units = self::getUnitsOfMeasure();
        return $units[$unit] ?? $unit;
    }

    /**
     * Get all active items ordered by name_ar.
     */
    public static function allActive(): array
    {
        return static::query()
            ->where('is_active', '=', 1)
            ->orderBy('name_ar', 'ASC')
            ->get();
    }

    /**
     * Get all sellable and active items ordered by name_ar.
     */
    public static function allSellable(): array
    {
        return static::query()
            ->where('is_sellable', '=', 1)
            ->where('is_active', '=', 1)
            ->orderBy('name_ar', 'ASC')
            ->get();
    }

    /**
     * Search items with filters and pagination.
     */
    public static function search(array $filters, int $perPage = 25, int $page = 1): array
    {
        $db = App::getInstance()->db();
        $where = '1=1';
        $params = [];
        $join = '';

        if (!empty($filters['q'])) {
            $search = '%' . $filters['q'] . '%';
            $where .= ' AND (i.`name_ar` LIKE ? OR i.`sku` LIKE ? OR i.`barcode` LIKE ?)';
            $params[] = $search;
            $params[] = $search;
            $params[] = $search;
        }

        if (!empty($filters['category_id'])) {
            $where .= ' AND i.`category_id` = ?';
            $params[] = (int) $filters['category_id'];
        }

        if (!empty($filters['tracking_type'])) {
            $where .= ' AND i.`tracking_type` = ?';
            $params[] = $filters['tracking_type'];
        }

        if (isset($filters['is_sellable']) && $filters['is_sellable'] !== '') {
            $where .= ' AND i.`is_sellable` = ?';
            $params[] = (int) $filters['is_sellable'];
        }

        if (!empty($filters['warehouse_id'])) {
            $join = ' JOIN `item_warehouse_stock` iws ON iws.`item_id` = i.`id`';
            $where .= ' AND iws.`warehouse_id` = ?';
            $params[] = (int) $filters['warehouse_id'];
        }

        $where .= ' AND i.`deleted_at` IS NULL';

        $countRow = $db->selectOne(
            "SELECT COUNT(*) as cnt FROM `inventory_items` i{$join} WHERE {$where}",
            $params
        );
        $total = (int) ($countRow['cnt'] ?? 0);
        $offset = ($page - 1) * $perPage;

        $rows = $db->select(
            "SELECT i.* FROM `inventory_items` i{$join} WHERE {$where} ORDER BY i.`name_ar` ASC LIMIT {$perPage} OFFSET {$offset}",
            $params
        );

        return ['data' => $rows, 'pagination' => \App\Core\Pagination::paginate($total, $perPage, $page)];
    }

    /**
     * Get the current stock quantity for an item in a specific warehouse.
     */
    public static function getStockInWarehouse(int $itemId, int $warehouseId): float
    {
        $db = App::getInstance()->db();
        $row = $db->selectOne(
            "SELECT `quantity` FROM `item_warehouse_stock` WHERE `item_id` = ? AND `warehouse_id` = ?",
            [$itemId, $warehouseId]
        );
        return (float) ($row['quantity'] ?? 0);
    }

    /**
     * Get the total stock quantity for an item across all warehouses.
     */
    public static function getTotalStock(int $itemId): float
    {
        $db = App::getInstance()->db();
        $row = $db->selectOne(
            "SELECT COALESCE(SUM(`quantity`), 0) as total FROM `item_warehouse_stock` WHERE `item_id` = ?",
            [$itemId]
        );
        return (float) ($row['total'] ?? 0);
    }
}
