<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

final class Customer extends Model
{
    protected string $table = 'customers';
    protected string $primaryKey = 'customer_id';

    public const STATUSES = ['active', 'inactive'];

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

        if (!empty($filters['status']) && in_array($filters['status'], self::STATUSES, true)) {
            $sql .= ' AND status = :st';
            $params['st'] = $filters['status'];
        }
        if (!empty($filters['q'])) {
            $sql .= ' AND (name LIKE :q1 OR email LIKE :q2 OR contact_person 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 '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 customers WHERE tenant_id = :t AND deleted_at IS NULL';
        $params = ['t' => $tenantId];
        if (!empty($filters['status']) && in_array($filters['status'], self::STATUSES, true)) {
            $sql .= ' AND status = :st';
            $params['st'] = $filters['status'];
        }
        if (!empty($filters['q'])) {
            $sql .= ' AND (name LIKE :q OR email LIKE :q2 OR contact_person 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 customers WHERE tenant_id = :t AND customer_id = :id AND deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $id]
        );
    }

    public static function optionsForTenant(int $tenantId): array
    {
        return DB::all(
            'SELECT customer_id, name FROM customers WHERE tenant_id = :t AND status = :st AND deleted_at IS NULL ORDER BY name',
            ['t' => $tenantId, 'st' => 'active']
        );
    }

    public static function create(int $tenantId, array $data): int
    {
        $payload = [
            'tenant_id'      => $tenantId,
            'name'           => $data['name'],
            'contact_person' => $data['contact_person'] ?? null,
            'email'          => $data['email'] ?? null,
            'phone'          => $data['phone'] ?? null,
            'address'        => $data['address'] ?? null,
            'tax_id'         => $data['tax_id'] ?? null,
            'notes'          => $data['notes'] ?? null,
            'status'         => in_array($data['status'] ?? '', self::STATUSES, true) ? $data['status'] : 'active',
        ];
        return (int) DB::insert('customers', $payload);
    }

    public static function update(int $tenantId, int $id, array $data): void
    {
        $allowed = ['name', 'contact_person', 'email', 'phone', 'address', 'tax_id', 'notes', 'status'];
        $set = [];
        $params = ['t' => $tenantId, 'id' => $id];
        foreach ($allowed as $col) {
            if (!array_key_exists($col, $data)) continue;
            $val = $data[$col];
            if ($col === 'status' && !in_array($val, self::STATUSES, true)) continue;
            $set[] = "`{$col}` = :{$col}";
            $params[$col] = $val;
        }
        if (!$set) return;
        DB::run(
            'UPDATE customers SET ' . implode(', ', $set) . ' WHERE tenant_id = :t AND customer_id = :id AND deleted_at IS NULL',
            $params
        );
    }

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