<?php
declare(strict_types=1);

namespace App\Modules\Waiver\Services;

use App\Core\App;
use App\Core\EventBus;
use App\Core\Logger;
use App\Modules\Archive\Services\ArchiveService;

final class WaiverProcessor
{
    public static function execute(int $waiverId): array
    {
        $db = App::getInstance()->db();
        $employee = App::getInstance()->currentEmployee();

        $waiver = $db->selectOne("SELECT * FROM waiver_requests WHERE id = ?", [$waiverId]);
        if (!$waiver || !in_array($waiver['status'], ['approved', 'fee_paid'])) {
            return ['success' => false, 'error' => 'طلب التنازل غير صالح أو غير مُعتمد'];
        }

        if (!$waiver['target_member_id']) {
            return ['success' => false, 'error' => 'لم يتم تحديد العضو المستفيد'];
        }

        $sourceMember = $db->selectOne("SELECT * FROM members WHERE id = ?", [(int) $waiver['source_member_id']]);
        if (!$sourceMember) return ['success' => false, 'error' => 'العضو المصدر غير موجود'];

        $targetMember = $db->selectOne("SELECT * FROM members WHERE id = ?", [(int) $waiver['target_member_id']]);
        if (!$targetMember) return ['success' => false, 'error' => 'العضو المستفيد غير موجود'];

        $db->beginTransaction();
        try {
            $snapshotId = ArchiveService::takeSnapshot('members', (int) $sourceMember['id'], 'waiver', 'تنازل — طلب #' . $waiverId);

            $db->update('members', [
                'membership_number' => null,
                'status'            => 'waived',
                'is_archived'       => 1,
                'archived_at'       => date('Y-m-d H:i:s'),
                'archived_by'       => $employee ? (int) $employee->id : null,
                'updated_at'        => date('Y-m-d H:i:s'),
            ], '`id` = ?', [(int) $waiver['source_member_id']]);

            $db->update('members', [
                'membership_number' => $waiver['membership_number'],
                'status'            => 'active',
                'membership_type'   => $sourceMember['membership_type'] ?? 'working',
                'updated_at'        => date('Y-m-d H:i:s'),
            ], '`id` = ?', [(int) $waiver['target_member_id']]);

            ArchiveService::recordNumberTransfer($waiver['membership_number'], 'waiver', 'members', (int) $waiver['target_member_id']);

            $db->update('waiver_requests', [
                'archive_snapshot_id' => $snapshotId,
                'status'              => 'completed',
                'updated_at'          => date('Y-m-d H:i:s'),
            ], '`id` = ?', [$waiverId]);

            $db->commit();

            EventBus::dispatch('waiver.completed', [
                'waiver_id'        => $waiverId,
                'source_member_id' => (int) $waiver['source_member_id'],
                'target_member_id' => (int) $waiver['target_member_id'],
                'membership_number'=> $waiver['membership_number'],
                'fee_amount'       => $waiver['waiver_fee_amount'] ?? '0.00',
                'fee_percentage'   => $waiver['waiver_fee_percentage'] ?? '30',
            ]);

            return ['success' => true, 'snapshot_id' => $snapshotId];
        } catch (\Throwable $e) {
            $db->rollBack();
            Logger::error("Waiver failed: " . $e->getMessage());
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }

    /**
     * Comprehensive debt check: member + all dependents (spouses, children, temporary).
     * Returns detailed breakdown per person.
     */
    public static function checkDebtsComprehensive(int $memberId): array
    {
        $db = App::getInstance()->db();
        $member = $db->selectOne("SELECT id, full_name_ar, membership_number FROM members WHERE id = ?", [$memberId]);
        if (!$member) return ['clear' => true, 'debts' => [], 'total' => '0.00', 'details' => []];

        $memberName = $member['full_name_ar'] ?? 'عضو #' . $memberId;
        $details = [];

        // 1. Check subscriptions for this member (including person_type entries for dependents)
        $subs = $db->select(
            "SELECT id, person_type, person_id, person_name, financial_year, (total_amount - paid_amount + fine_amount) as due
             FROM subscriptions WHERE member_id = ? AND status IN ('pending','overdue') AND (total_amount - paid_amount + fine_amount) > 0",
            [$memberId]
        );
        foreach ($subs as $sub) {
            $personLabel = self::getPersonLabel($sub['person_type'] ?? 'member', $sub['person_name'] ?? $memberName);
            $details[] = [
                'member_id' => $memberId,
                'member_name' => $memberName,
                'person_name' => $sub['person_name'] ?? $memberName,
                'person_type' => $sub['person_type'] ?? 'member',
                'person_label' => $personLabel,
                'debt_type' => 'اشتراك سنوي',
                'period' => $sub['financial_year'] ?? '',
                'amount' => $sub['due'],
                'description' => 'اشتراك سنوي ' . ($sub['financial_year'] ?? '') . ' — ' . $personLabel,
            ];
        }

        // 2. Fines on main member
        $fines = $db->select(
            "SELECT id, (amount - paid_amount) as due, created_at FROM fines WHERE member_id = ? AND status IN ('imposed','pending','appeal_upheld') AND (amount - paid_amount) > 0",
            [$memberId]
        );
        foreach ($fines as $fine) {
            $details[] = [
                'member_id' => $memberId,
                'member_name' => $memberName,
                'person_name' => $memberName,
                'person_type' => 'member',
                'person_label' => 'العضو الأساسي',
                'debt_type' => 'غرامة',
                'period' => substr($fine['created_at'] ?? '', 0, 10),
                'amount' => $fine['due'],
                'description' => 'غرامة — العضو الأساسي',
            ];
        }

        // 3. Installments on main member
        $installments = $db->select(
            "SELECT s.id, s.due_date, (s.amount - s.paid_amount) as due
             FROM installment_schedule s JOIN installment_plans p ON p.id = s.installment_plan_id
             WHERE p.member_id = ? AND p.status IN ('active','overdue') AND s.status IN ('pending','overdue') AND (s.amount - s.paid_amount) > 0",
            [$memberId]
        );
        foreach ($installments as $inst) {
            $details[] = [
                'member_id' => $memberId,
                'member_name' => $memberName,
                'person_name' => $memberName,
                'person_type' => 'member',
                'person_label' => 'العضو الأساسي',
                'debt_type' => 'قسط عضوية',
                'period' => $inst['due_date'] ?? '',
                'amount' => $inst['due'],
                'description' => 'قسط عضوية مستحق ' . ($inst['due_date'] ?? '') . ' — العضو الأساسي',
            ];
        }

        // 4. Pending payment requests
        $requests = $db->select(
            "SELECT id, payment_type, amount, description_ar FROM payment_requests
             WHERE member_id = ? AND status = 'pending' AND is_voided = 0",
            [$memberId]
        );
        foreach ($requests as $req) {
            $details[] = [
                'member_id' => $memberId,
                'member_name' => $memberName,
                'person_name' => $memberName,
                'person_type' => 'member',
                'person_label' => 'العضو الأساسي',
                'debt_type' => 'طلب دفع معلق',
                'period' => '',
                'amount' => $req['amount'],
                'description' => ($req['description_ar'] ?? 'طلب دفع') . ' — العضو الأساسي',
            ];
        }

        $totalDebt = '0.00';
        foreach ($details as $d) {
            $totalDebt = bcadd($totalDebt, (string) $d['amount'], 2);
        }

        return [
            'clear' => empty($details),
            'debts' => $details,
            'total' => $totalDebt,
            'member_name' => $memberName,
        ];
    }

    /**
     * Simple debt check (for backward compat and quick validation).
     */
    public static function checkDebts(int $memberId): array
    {
        $result = self::checkDebtsComprehensive($memberId);
        // Group by type for legacy format
        $grouped = [];
        foreach ($result['debts'] as $d) {
            $key = $d['debt_type'];
            if (!isset($grouped[$key])) {
                $grouped[$key] = ['type' => $key, 'count' => 0, 'amount' => '0.00'];
            }
            $grouped[$key]['count']++;
            $grouped[$key]['amount'] = bcadd($grouped[$key]['amount'], (string) $d['amount'], 2);
        }
        return [
            'clear' => $result['clear'],
            'debts' => array_values($grouped),
            'total' => $result['total'],
        ];
    }

    /**
     * Count dependents for a member (spouses + children + temporary) — per-category.
     */
    public static function countDependents(int $memberId): array
    {
        $db = App::getInstance()->db();
        $spouses = (int) ($db->selectOne("SELECT COUNT(*) as cnt FROM spouses WHERE member_id = ? AND is_archived = 0", [$memberId])['cnt'] ?? 0);
        $children = (int) ($db->selectOne("SELECT COUNT(*) as cnt FROM children WHERE member_id = ? AND is_archived = 0", [$memberId])['cnt'] ?? 0);
        $temps = (int) ($db->selectOne("SELECT COUNT(*) as cnt FROM temporary_members WHERE member_id = ? AND is_archived = 0", [$memberId])['cnt'] ?? 0);

        return [
            'spouses' => $spouses,
            'children' => $children,
            'temporary' => $temps,
            'total' => $spouses + $children + $temps,
        ];
    }

    /**
     * Get detailed dependent data for display (names, ages, DOB, categories).
     */
    public static function getDependentDetails(int $memberId): array
    {
        $db = App::getInstance()->db();

        $spouses = $db->select(
            "SELECT id, full_name_ar, national_id, status FROM spouses WHERE member_id = ? AND is_archived = 0 ORDER BY id",
            [$memberId]
        );

        $children = $db->select(
            "SELECT id, full_name_ar, national_id, date_of_birth, gender, classification, status
             FROM children WHERE member_id = ? AND is_archived = 0 ORDER BY date_of_birth",
            [$memberId]
        );

        // Calculate age and category for each child
        $today = new \DateTime();
        foreach ($children as &$child) {
            $dob = !empty($child['date_of_birth']) ? new \DateTime($child['date_of_birth']) : null;
            if ($dob) {
                $age = $dob->diff($today);
                $child['age_years'] = $age->y;
                $child['age_months'] = $age->m;
                $child['age_display'] = $age->y . ' سنة' . ($age->m > 0 ? ' و ' . $age->m . ' شهر' : '');
                if ($age->y < 12) {
                    $child['age_category'] = 'أقل من 12 سنة';
                    $child['age_category_code'] = 'under_12';
                } elseif ($age->y < 16) {
                    $child['age_category'] = 'من 12 إلى أقل من 16 سنة';
                    $child['age_category_code'] = '12_to_16';
                } elseif ($age->y < 18) {
                    $child['age_category'] = 'من 16 إلى أقل من 18 سنة';
                    $child['age_category_code'] = '16_to_18';
                } else {
                    $child['age_category'] = '18 سنة فأكثر';
                    $child['age_category_code'] = '18_plus';
                }
            } else {
                $child['age_years'] = null;
                $child['age_months'] = null;
                $child['age_display'] = '—';
                $child['age_category'] = 'غير محدد';
                $child['age_category_code'] = 'unknown';
            }
        }
        unset($child);

        $temporary = $db->select(
            "SELECT id, full_name_ar, national_id, status FROM temporary_members WHERE member_id = ? AND is_archived = 0 ORDER BY id",
            [$memberId]
        );

        return [
            'spouses' => $spouses,
            'children' => $children,
            'temporary' => $temporary,
        ];
    }

    /**
     * Compare dependents per-category: source (original) vs target (new).
     */
    public static function compareDependents(array $original, array $target): array
    {
        $excessSpouses = max(0, $target['spouses'] - $original['spouses']);
        $excessChildren = max(0, $target['children'] - $original['children']);
        $excessTemporary = max(0, $target['temporary'] - $original['temporary']);

        return [
            'excess_spouses' => $excessSpouses,
            'excess_children' => $excessChildren,
            'excess_temporary' => $excessTemporary,
            'excess_total' => $excessSpouses + $excessChildren + $excessTemporary,
            'has_excess' => ($excessSpouses + $excessChildren + $excessTemporary) > 0,
        ];
    }

    /**
     * Calculate per-category excess fees based on board-set rates.
     */
    public static function calculateExcessFees(array $excess, array $feeConfig, string $membershipValue): array
    {
        $spouseFee = '0.00';
        $childFee = '0.00';
        $tempFee = '0.00';

        if ($excess['excess_spouses'] > 0 && !empty($feeConfig['spouse_fee_type'])) {
            $spouseFee = self::calcCategoryFee(
                $excess['excess_spouses'],
                $feeConfig['spouse_fee_type'],
                $feeConfig['spouse_fee_rate'] ?? '0',
                $membershipValue
            );
        }

        if ($excess['excess_children'] > 0 && !empty($feeConfig['child_fee_type'])) {
            $childFee = self::calcCategoryFee(
                $excess['excess_children'],
                $feeConfig['child_fee_type'],
                $feeConfig['child_fee_rate'] ?? '0',
                $membershipValue
            );
        }

        if ($excess['excess_temporary'] > 0 && !empty($feeConfig['temporary_fee_type'])) {
            $tempFee = self::calcCategoryFee(
                $excess['excess_temporary'],
                $feeConfig['temporary_fee_type'],
                $feeConfig['temporary_fee_rate'] ?? '0',
                $membershipValue
            );
        }

        $total = bcadd(bcadd($spouseFee, $childFee, 2), $tempFee, 2);

        return [
            'spouse_fee_total' => $spouseFee,
            'child_fee_total' => $childFee,
            'temporary_fee_total' => $tempFee,
            'total_excess_fee' => $total,
        ];
    }

    private static function calcCategoryFee(int $excessCount, string $type, string $rate, string $membershipValue): string
    {
        if ($type === 'percentage') {
            $perUnit = bcdiv(bcmul($membershipValue, $rate, 4), '100', 2);
        } else {
            $perUnit = $rate;
        }
        return bcmul($perUnit, (string) $excessCount, 2);
    }

    /**
     * Identify excess dependents (individual persons) for per-person fee assignment.
     * Compares target dependents vs original counts — returns the EXTRA individuals.
     */
    public static function getExcessIndividuals(int $targetMemberId, array $originalCounts): array
    {
        $db = App::getInstance()->db();
        $today = new \DateTime();
        $excess = [];

        // Spouses: if target has more than original, the LAST ones are considered excess
        $spouses = $db->select(
            "SELECT id, full_name_ar, national_id, status FROM spouses WHERE member_id = ? AND is_archived = 0 ORDER BY id",
            [$targetMemberId]
        );
        $allowedSpouses = $originalCounts['spouses'] ?? 0;
        if (count($spouses) > $allowedSpouses) {
            $excessSpouses = array_slice($spouses, $allowedSpouses);
            $order = 1;
            foreach ($excessSpouses as $s) {
                $excess[] = [
                    'person_type' => 'spouse',
                    'person_id' => (int) $s['id'],
                    'person_name' => $s['full_name_ar'] ?? '',
                    'person_order' => $order++,
                    'national_id' => $s['national_id'] ?? '',
                    'status' => $s['status'] ?? 'active',
                    'age_years' => null,
                    'age_category' => null,
                    'age_category_code' => null,
                    'date_of_birth' => null,
                    'relationship' => null,
                ];
            }
        }

        // Children: last N are excess
        $children = $db->select(
            "SELECT id, full_name_ar, national_id, date_of_birth, gender, relationship, classification, status
             FROM children WHERE member_id = ? AND is_archived = 0 ORDER BY date_of_birth",
            [$targetMemberId]
        );
        $allowedChildren = $originalCounts['children'] ?? 0;
        if (count($children) > $allowedChildren) {
            $excessChildren = array_slice($children, $allowedChildren);
            $order = 1;
            foreach ($excessChildren as $c) {
                $dob = !empty($c['date_of_birth']) ? new \DateTime($c['date_of_birth']) : null;
                $ageYears = $dob ? $dob->diff($today)->y : null;
                $ageCat = null;
                $ageCatCode = null;
                if ($ageYears !== null) {
                    if ($ageYears < 12) { $ageCat = 'أقل من 12 سنة'; $ageCatCode = 'under_12'; }
                    elseif ($ageYears < 16) { $ageCat = 'من 12 إلى أقل من 16 سنة'; $ageCatCode = '12_to_16'; }
                    elseif ($ageYears < 18) { $ageCat = 'من 16 إلى أقل من 18 سنة'; $ageCatCode = '16_to_18'; }
                    elseif ($ageYears < 25) { $ageCat = 'من 18 إلى أقل من 25 سنة'; $ageCatCode = '18_to_25'; }
                    else { $ageCat = '25 سنة فأكثر'; $ageCatCode = '25_plus'; }
                }
                $excess[] = [
                    'person_type' => 'child',
                    'person_id' => (int) $c['id'],
                    'person_name' => $c['full_name_ar'] ?? '',
                    'person_order' => $order++,
                    'national_id' => $c['national_id'] ?? '',
                    'status' => $c['status'] ?? 'active',
                    'age_years' => $ageYears,
                    'age_category' => $ageCat,
                    'age_category_code' => $ageCatCode,
                    'date_of_birth' => $c['date_of_birth'] ?? null,
                    'relationship' => $c['relationship'] ?? null,
                    'gender' => $c['gender'] ?? null,
                    'classification' => $c['classification'] ?? null,
                ];
            }
        }

        // Temporary: last N are excess
        $temps = $db->select(
            "SELECT id, full_name_ar, national_id, status FROM temporary_members WHERE member_id = ? AND is_archived = 0 ORDER BY id",
            [$targetMemberId]
        );
        $allowedTemps = $originalCounts['temporary'] ?? 0;
        if (count($temps) > $allowedTemps) {
            $excessTemps = array_slice($temps, $allowedTemps);
            $order = 1;
            foreach ($excessTemps as $t) {
                $excess[] = [
                    'person_type' => 'temporary',
                    'person_id' => (int) $t['id'],
                    'person_name' => $t['full_name_ar'] ?? '',
                    'person_order' => $order++,
                    'national_id' => $t['national_id'] ?? '',
                    'status' => $t['status'] ?? 'active',
                    'age_years' => null,
                    'age_category' => null,
                    'age_category_code' => null,
                    'date_of_birth' => null,
                    'relationship' => null,
                ];
            }
        }

        return $excess;
    }

    /**
     * Save per-individual fees set by the board.
     */
    public static function saveIndividualFees(int $waiverId, array $fees, string $membershipValue): array
    {
        $db = App::getInstance()->db();

        // Clear old fees for this waiver
        $db->delete('waiver_individual_fees', '`waiver_request_id` = ?', [$waiverId]);

        $spouseTotal = '0.00';
        $childTotal = '0.00';
        $tempTotal = '0.00';

        foreach ($fees as $fee) {
            $feeType = $fee['fee_type'] ?? null;
            $feeRate = $fee['fee_rate'] ?? '0';
            $feeAmount = '0.00';

            if ($feeType === 'percentage' && is_numeric($feeRate)) {
                $feeAmount = bcdiv(bcmul($membershipValue, (string) $feeRate, 4), '100', 2);
            } elseif ($feeType === 'fixed' && is_numeric($feeRate)) {
                $feeAmount = number_format((float) $feeRate, 2, '.', '');
            }

            $dob = trim((string) ($fee['date_of_birth'] ?? ''));

            $db->insert('waiver_individual_fees', [
                'waiver_request_id' => $waiverId,
                'person_type' => $fee['person_type'],
                'person_id' => (int) $fee['person_id'],
                'person_name' => $fee['person_name'] ?? '',
                'person_order' => (int) ($fee['person_order'] ?? 1),
                'fee_type' => $feeType ?: null,
                'fee_rate' => is_numeric($feeRate) ? $feeRate : null,
                'fee_amount' => $feeAmount,
                'age_years' => $fee['age_years'] ?? null,
                'age_category' => $fee['age_category_code'] ?? $fee['age_category'] ?? null,
                'date_of_birth' => $dob !== '' ? $dob : null,
                'relationship' => ($fee['relationship'] ?? '') !== '' ? $fee['relationship'] : null,
                'status' => ($fee['status'] ?? '') !== '' ? $fee['status'] : null,
                'notes' => ($fee['notes'] ?? '') !== '' ? $fee['notes'] : null,
            ]);

            if ($fee['person_type'] === 'spouse') $spouseTotal = bcadd($spouseTotal, $feeAmount, 2);
            elseif ($fee['person_type'] === 'child') $childTotal = bcadd($childTotal, $feeAmount, 2);
            elseif ($fee['person_type'] === 'temporary') $tempTotal = bcadd($tempTotal, $feeAmount, 2);
        }

        $grandTotal = bcadd(bcadd($spouseTotal, $childTotal, 2), $tempTotal, 2);

        return [
            'spouse_total' => $spouseTotal,
            'child_total' => $childTotal,
            'temporary_total' => $tempTotal,
            'grand_total' => $grandTotal,
        ];
    }

    /**
     * Load saved per-individual fees for a waiver.
     */
    public static function getIndividualFees(int $waiverId): array
    {
        $db = App::getInstance()->db();
        return $db->select(
            "SELECT * FROM waiver_individual_fees WHERE waiver_request_id = ? ORDER BY person_type, person_order",
            [$waiverId]
        );
    }

    private static function getPersonLabel(?string $personType, string $personName): string
    {
        return match ($personType) {
            'member' => 'العضو الأساسي (' . $personName . ')',
            'spouse' => 'الزوجة (' . $personName . ')',
            'child' => 'الابن/ة (' . $personName . ')',
            'temporary' => 'العضو المؤقت (' . $personName . ')',
            default => $personName,
        };
    }
}
