<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

final class ProductService extends Model
{
    protected string $table = 'products_services';
    protected string $primaryKey = 'item_id';

    public const TYPES    = ['product', 'service'];
    public const UNITS    = ['ea', 'hr', 'kg', 'g', 'm', 'cm', 'mm', 'l', 'ml', 'm2', 'm3', 'pkt', 'box', 'set', 'lot'];

    public static function listForTenant(int $tenantId, array $filters = [], ?int $limit = null, int $offset = 0, string $sort = 'name_asc'): array
    {
        $sql = 'SELECT * FROM products_services WHERE tenant_id = :t AND deleted_at IS NULL';
        $params = ['t' => $tenantId];

        if (!empty($filters['type']) && in_array($filters['type'], self::TYPES, true)) {
            $sql .= ' AND type = :ty';
            $params['ty'] = $filters['type'];
        }
        if (!empty($filters['category'])) {
            $sql .= ' AND category = :cat';
            $params['cat'] = $filters['category'];
        }
        if (isset($filters['is_active'])) {
            $sql .= ' AND is_active = :act';
            $params['act'] = $filters['is_active'] ? 1 : 0;
        }
        if (!empty($filters['q'])) {
            $sql .= ' AND (name LIKE :q1 OR sku LIKE :q2 OR description LIKE :q3)';
            $q = '%' . $filters['q'] . '%';
            $params['q1'] = $q;
            $params['q2'] = $q;
            $params['q3'] = $q;
        }

        switch ($sort) {
            case 'name_desc':  $sql .= ' ORDER BY name DESC'; break;
            case 'price_asc':  $sql .= ' ORDER BY unit_price ASC'; break;
            case 'price_desc': $sql .= ' ORDER BY unit_price DESC'; break;
            case 'newest':     $sql .= ' ORDER BY created_at DESC'; break;
            case 'oldest':     $sql .= ' ORDER BY created_at ASC'; break;
            default:           $sql .= ' ORDER BY name ASC'; break;
        }

        if ($limit !== null) {
            $sql .= sprintf(' LIMIT %d OFFSET %d', max(1, $limit), max(0, $offset));
        }
        return DB::all($sql, $params);
    }

    public static function countForTenant(int $tenantId, array $filters = []): int
    {
        $sql = 'SELECT COUNT(*) FROM products_services WHERE tenant_id = :t AND deleted_at IS NULL';
        $params = ['t' => $tenantId];
        if (!empty($filters['type']) && in_array($filters['type'], self::TYPES, true)) {
            $sql .= ' AND type = :ty';
            $params['ty'] = $filters['type'];
        }
        if (!empty($filters['category'])) {
            $sql .= ' AND category = :cat';
            $params['cat'] = $filters['category'];
        }
        if (isset($filters['is_active'])) {
            $sql .= ' AND is_active = :act';
            $params['act'] = $filters['is_active'] ? 1 : 0;
        }
        if (!empty($filters['q'])) {
            $sql .= ' AND (name LIKE :q OR sku LIKE :q2 OR description LIKE :q3)';
            $q = '%' . $filters['q'] . '%';
            $params['q'] = $q;
            $params['q2'] = $q;
            $params['q3'] = $q;
        }
        return (int) DB::value($sql, $params);
    }

    public static function findInTenant(int $tenantId, int $id): ?array
    {
        return DB::one(
            'SELECT * FROM products_services WHERE tenant_id = :t AND item_id = :id AND deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $id]
        );
    }

    public static function optionsForTenant(int $tenantId, ?string $type = null): array
    {
        $sql = 'SELECT item_id, name, type, unit_price, unit FROM products_services WHERE tenant_id = :t AND is_active = 1 AND deleted_at IS NULL';
        $params = ['t' => $tenantId];
        if ($type !== null && in_array($type, self::TYPES, true)) {
            $sql .= ' AND type = :ty';
            $params['ty'] = $type;
        }
        $sql .= ' ORDER BY type, name';
        return DB::all($sql, $params);
    }

    public static function categoriesForTenant(int $tenantId): array
    {
        $rows = DB::all(
            'SELECT DISTINCT category FROM products_services WHERE tenant_id = :t AND category IS NOT NULL AND category != \'\' AND deleted_at IS NULL ORDER BY category',
            ['t' => $tenantId]
        );
        return array_column($rows, 'category');
    }

    public static function create(int $tenantId, array $data): int
    {
        $payload = [
            'tenant_id'   => $tenantId,
            'type'        => in_array($data['type'] ?? '', self::TYPES, true) ? $data['type'] : 'product',
            'name'        => $data['name'],
            'sku'         => $data['sku'] ?? null,
            'description' => $data['description'] ?? null,
            'unit'        => $data['unit'] ?? 'ea',
            'unit_cost'   => !empty($data['unit_cost']) ? (float) $data['unit_cost'] : null,
            'unit_price'  => !empty($data['unit_price']) ? (float) $data['unit_price'] : 0.00,
            'taxable'     => !empty($data['taxable']) ? 1 : 0,
            'tax_rate'    => !empty($data['tax_rate']) ? (float) $data['tax_rate'] : 0.00,
            'category'    => $data['category'] ?? null,
            'is_active'   => !empty($data['is_active']) ? 1 : 0,
        ];
        return (int) DB::insert('products_services', $payload);
    }

    public static function update(int $tenantId, int $id, array $data): void
    {
        $allowed = ['type', 'name', 'sku', 'description', 'unit', 'unit_cost', 'unit_price', 'taxable', 'tax_rate', 'category', 'is_active'];
        $set = [];
        $params = ['t' => $tenantId, 'id' => $id];
        foreach ($allowed as $col) {
            if (!array_key_exists($col, $data)) continue;
            $val = $data[$col];
            if ($col === 'type' && !in_array($val, self::TYPES, true)) continue;
            if (in_array($col, ['unit_cost', 'unit_price', 'tax_rate'], true)) {
                $val = ($val === '' || $val === null) ? null : (float) $val;
            }
            if (in_array($col, ['taxable', 'is_active'], true)) {
                $val = !empty($val) ? 1 : 0;
            }
            $set[] = "`{$col}` = :{$col}";
            $params[$col] = $val;
        }
        if (!$set) return;
        DB::run(
            'UPDATE products_services SET ' . implode(', ', $set) . ' WHERE tenant_id = :t AND item_id = :id AND deleted_at IS NULL',
            $params
        );
    }

    public static function softDelete(int $tenantId, int $id): void
    {
        DB::run(
            'UPDATE products_services SET deleted_at = NOW() WHERE tenant_id = :t AND item_id = :id AND deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $id]
        );
    }
}