<?php
declare(strict_types=1);

namespace App\Modules\Transfers\Models;

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

class TransferRequest extends Model
{
    protected static string $table = 'transfer_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', 'transfer_type',
        'child_id', 'spouse_id', 'source_membership_number',
        'new_membership_number', 'new_membership_value', 'form_number',
        'source_companions_count', 'target_companions_count',
        'companion_surcharge', 'companion_surcharge_breakdown',
        'years_since_acquisition', 'qualification_code',
        'fee_percentage', 'separation_fee', 'form_fee',
        'annual_subscription_fee', 'total_fee',
        'archive_snapshot_id', 'workflow_instance_id',
        'board_decision_notes', 'approved_by', 'approved_at',
        'completed_at', 'status', 'notes',
        'is_employed', 'graduation_date', 'work_date', 'marriage_date', 'date_turned_25', 'effective_transfer_date',
        'target_spouses_count', 'target_children_count', 'target_temps_count',
    ];

    public static function getForMember(int $memberId): array
    {
        $db = App::getInstance()->db();
        return $db->select(
            "SELECT tr.*, m.full_name_ar as source_name, m.membership_number as source_number,
                    tm.full_name_ar as target_name, tm.membership_number as target_number
             FROM transfer_requests tr
             JOIN members m ON m.id = tr.source_member_id
             LEFT JOIN members tm ON tm.id = tr.target_member_id
             WHERE tr.source_member_id = ? OR tr.target_member_id = ?
             ORDER BY tr.created_at DESC",
            [$memberId, $memberId]
        );
    }

    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 tr.status = ?';
            $params[] = $filters['status'];
        }
        if (!empty($filters['transfer_type'])) {
            $where .= ' AND tr.transfer_type = ?';
            $params[] = $filters['transfer_type'];
        }
        if (!empty($filters['search'])) {
            $where .= ' AND (m.full_name_ar LIKE ? OR m.membership_number LIKE ?)';
            $s = '%' . $filters['search'] . '%';
            $params[] = $s;
            $params[] = $s;
        }

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

        $rows = $db->select(
            "SELECT tr.*, m.full_name_ar as source_name, m.membership_number as source_number,
                    tm.full_name_ar as target_name, c.full_name_ar as child_name, s.full_name_ar as spouse_name
             FROM transfer_requests tr
             JOIN members m ON m.id = tr.source_member_id
             LEFT JOIN members tm ON tm.id = tr.target_member_id
             LEFT JOIN children c ON c.id = tr.child_id
             LEFT JOIN spouses s ON s.id = tr.spouse_id
             WHERE {$where} ORDER BY tr.created_at DESC LIMIT {$perPage} OFFSET {$offset}",
            $params
        );

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

    public static function getTransferTypeLabel(string $type): string
    {
        return match ($type) {
            'child_separation'   => 'فصل أبناء',
            'child_mandatory_25' => 'تحويل وجوبي (25 سنة)',
            'divorce'            => 'طلاق',
            'death'              => 'وفاة',
            'waiver'             => 'تنازل',
            'sports_conversion'  => 'تحويل رياضي',
            'cross_branch'       => 'تحويل بين فروع',
            default              => $type,
        };
    }
}