<?php
declare(strict_types=1);

namespace App\Modules\Carnets\Models;

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

class Carnet extends Model
{
    protected static string $table = 'carnets';
    protected static string $primaryKey = 'id';
    protected static bool $timestamps = true;
    protected static bool $softDelete = false;
    protected static bool $dispatchEvents = true;

    protected static array $fillable = [
        'member_id', 'dependent_type', 'dependent_id', 'card_variant',
        'carnet_number', 'carnet_type', 'qr_code_data',
        'is_active', 'issued_by', 'deactivated_at', 'deactivated_reason',
        'replacement_count', 'previous_carnet_id',
        'print_count', 'last_printed_at', 'last_printed_by',
    ];

    public static function getActiveForMember(int $memberId): ?array
    {
        $db = App::getInstance()->db();
        return $db->selectOne(
            "SELECT * FROM carnets WHERE member_id = ? AND is_active = 1 AND dependent_type IS NULL ORDER BY issued_at DESC LIMIT 1",
            [$memberId]
        );
    }

    public static function getActiveForDependent(string $type, int $id): ?array
    {
        $db = App::getInstance()->db();
        return $db->selectOne(
            "SELECT * FROM carnets WHERE dependent_type = ? AND dependent_id = ? AND is_active = 1 ORDER BY issued_at DESC LIMIT 1",
            [$type, $id]
        );
    }

    public static function getAllForMember(int $memberId): array
    {
        $db = App::getInstance()->db();
        return $db->select(
            "SELECT c.*, e.full_name_ar as issued_by_name FROM carnets c LEFT JOIN employees e ON e.id = c.issued_by WHERE c.member_id = ? ORDER BY c.issued_at DESC",
            [$memberId]
        );
    }

    public static function generateNumber(): string
    {
        $db = App::getInstance()->db();
        $year = date('Y');
        $pattern = 'CRN-' . $year . '-%';
        $last = $db->selectOne(
            "SELECT carnet_number FROM carnets WHERE carnet_number LIKE ? ORDER BY id DESC LIMIT 1",
            [$pattern]
        );
        if ($last) {
            $parts = explode('-', $last['carnet_number']);
            $seq = (int) end($parts) + 1;
        } else {
            $seq = 1;
        }
        return 'CRN-' . $year . '-' . str_pad((string) $seq, 6, '0', STR_PAD_LEFT);
    }

    public static function search(array $filters, int $perPage = 25, int $page = 1): array
    {
        $db = App::getInstance()->db();
        $where = '1=1';
        $params = [];

        if ($filters['is_active'] !== '') {
            $where .= ' AND c.is_active = ?';
            $params[] = (int) $filters['is_active'];
        }
        if (!empty($filters['type'])) {
            $where .= ' AND c.carnet_type = ?';
            $params[] = $filters['type'];
        }
        if (!empty($filters['search'])) {
            $where .= ' AND (m.full_name_ar LIKE ? OR m.membership_number LIKE ? OR c.carnet_number LIKE ?)';
            $s = '%' . $filters['search'] . '%';
            $params[] = $s; $params[] = $s; $params[] = $s;
        }

        $countRow = $db->selectOne("SELECT COUNT(*) as cnt FROM carnets c JOIN members m ON m.id = c.member_id WHERE {$where}", $params);
        $total = (int) ($countRow['cnt'] ?? 0);
        $offset = ($page - 1) * $perPage;

        $rows = $db->select(
            "SELECT c.*, m.full_name_ar as member_name, m.membership_number, e.full_name_ar as issued_by_name
             FROM carnets c JOIN members m ON m.id = c.member_id LEFT JOIN employees e ON e.id = c.issued_by
             WHERE {$where} ORDER BY c.issued_at DESC LIMIT {$perPage} OFFSET {$offset}",
            $params
        );

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