<?php
declare(strict_types=1);

namespace App\Modules\Waiver\Models;

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

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

    protected static array $fillable = [
        'source_member_id', 'target_member_id', 'membership_number', 'form_number',
        'membership_value_at_waiver', 'waiver_fee_percentage', 'waiver_fee_amount',
        'original_dependent_count', 'original_spouses_count', 'original_children_count', 'original_temporary_count',
        'new_dependent_count', 'new_spouses_count', 'new_children_count', 'new_temporary_count',
        'excess_dependent_count', 'excess_spouses_count', 'excess_children_count', 'excess_temporary_count',
        'excess_fee_percentage', 'excess_fee_amount',
        'spouse_fee_type', 'spouse_fee_rate', 'spouse_fee_total',
        'child_fee_type', 'child_fee_rate', 'child_fee_total',
        'temporary_fee_type', 'temporary_fee_rate', 'temporary_fee_total',
        'board_approval_required', 'board_decision_reference',
        'approved_by', 'approved_at', 'annual_renewal_paid',
        'debts_cleared', 'target_debts_cleared',
        'archive_snapshot_id', 'workflow_instance_id', 'status', 'notes',
        'waiver_request_doc_path', 'target_form_doc_path',
    ];

    public static function search(array $filters, int $perPage = 25, int $page = 1): array
    {
        $db = App::getInstance()->db();
        $where = '1=1';
        $params = [];
        if (!empty($filters['status'])) { $where .= ' AND wr.status = ?'; $params[] = $filters['status']; }
        if (!empty($filters['search'])) {
            $where .= ' AND (m.full_name_ar LIKE ? OR wr.membership_number LIKE ?)';
            $s = '%' . $filters['search'] . '%'; $params[] = $s; $params[] = $s;
        }
        $countRow = $db->selectOne("SELECT COUNT(*) as cnt FROM waiver_requests wr JOIN members m ON m.id = wr.source_member_id WHERE {$where}", $params);
        $total = (int) ($countRow['cnt'] ?? 0);
        $offset = ($page - 1) * $perPage;
        $rows = $db->select(
            "SELECT wr.*, m.full_name_ar as source_name, tm.full_name_ar as target_name
             FROM waiver_requests wr JOIN members m ON m.id = wr.source_member_id
             LEFT JOIN members tm ON tm.id = wr.target_member_id
             WHERE {$where} ORDER BY wr.created_at DESC LIMIT {$perPage} OFFSET {$offset}", $params
        );
        return ['data' => $rows, 'pagination' => Pagination::paginate($total, $perPage, $page)];
    }
}