<?php
declare(strict_types=1);

namespace App\Modules\Members\Services;

use App\Core\App;
use App\Modules\ServiceCatalog\Models\ServicePrice;
use App\Modules\Members\Services\BoardOfferService;

/**
 * Calculates the TOTAL bill for a member including all additions.
 * Used during initial creation to show accumulated charges before payment.
 */
final class BillingService
{
    public static function getMemberBill(int $memberId): array
    {
        $db = App::getInstance()->db();
        $member = $db->selectOne("SELECT * FROM members WHERE id = ? AND is_archived = 0", [$memberId]);
        if (!$member) return [];

        $membershipValue = $member['membership_value'] ?? '0.00';
        $items = [];

        // ── 1. Form Fee (505) ──
        $formFeePaid = false;
        $formFeePending = false;
        try {
            $ff = $db->selectOne(
                "SELECT id FROM payments WHERE member_id = ? AND payment_type = 'form_fee' AND is_voided = 0 LIMIT 1",
                [$memberId]
            );
            $formFeePaid = ($ff !== null);
            if (!$formFeePaid) {
                $ffReq = $db->selectOne(
                    "SELECT id, status FROM payment_requests WHERE member_id = ? AND payment_type = 'form_fee' AND is_voided = 0 AND status IN ('pending','processing') LIMIT 1",
                    [$memberId]
                );
                $formFeePending = ($ffReq !== null);
                if (!$formFeePending) {
                    $ffCompleted = $db->selectOne(
                        "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type = 'form_fee' AND is_voided = 0 AND status = 'completed' LIMIT 1",
                        [$memberId]
                    );
                    $formFeePaid = ($ffCompleted !== null);
                }
            }
        } catch (\Throwable $e) {}

        $formFeeAmount = ServicePrice::getPrice('SVC_NEW_FORM', '505.00');

        $items[] = [
            'type'        => 'form_fee',
            'label'       => 'رسوم استمارة عضوية',
            'amount'      => $formFeeAmount,
            'paid'        => $formFeePaid,
            'included'    => false,
            'category'    => 'required',
            'breakdown'   => ['رسوم الاستمارة: 500.00 ج.م', 'طابع شهداء: 5.00 ج.م'],
        ];

        // ── 2. Membership Value ──
        $membershipPaid = false;
        $membershipPending = false;
        try {
            $mp = $db->selectOne(
                "SELECT id FROM payments WHERE member_id = ? AND payment_type IN ('membership_fee','down_payment') AND is_voided = 0 LIMIT 1",
                [$memberId]
            );
            $membershipPaid = ($mp !== null);
            if (!$membershipPaid) {
                $mpReq = $db->selectOne(
                    "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type IN ('membership_fee','down_payment') AND is_voided = 0 AND status IN ('pending','processing') LIMIT 1",
                    [$memberId]
                );
                $membershipPending = ($mpReq !== null);
                if (!$membershipPending) {
                    $mpCompleted = $db->selectOne(
                        "SELECT id FROM payment_requests WHERE member_id = ? AND payment_type IN ('membership_fee','down_payment') AND is_voided = 0 AND status = 'completed' LIMIT 1",
                        [$memberId]
                    );
                    $membershipPaid = ($mpCompleted !== null);
                }
            }
        } catch (\Throwable $e) {}

        $qualName = '—';
        if ($member['qualification_id']) {
            $q = $db->selectOne("SELECT name_ar FROM qualifications WHERE id = ?", [(int) $member['qualification_id']]);
            $qualName = $q['name_ar'] ?? '—';
        }

        $items[] = [
            'type'        => 'membership_fee',
            'label'       => 'قيمة العضوية (' . $qualName . ')',
            'amount'      => $membershipValue,
            'paid'        => $membershipPaid,
            'included'    => false,
            'category'    => 'required',
        ];

        // ── 2b. Special Discount ──
        if (!empty($member['special_discount_id'])) {
            $discountRow = $db->selectOne(
                "SELECT sd.name_ar, sd.discount_percentage FROM special_discounts sd WHERE sd.id = ?",
                [(int) $member['special_discount_id']]
            );
            if ($discountRow) {
                $discountAmount = $member['discount_amount'] ?? bcdiv(bcmul($membershipValue, $discountRow['discount_percentage'], 4), '100', 2);
                // Discount is a negative line item
                $items[] = [
                    'type'        => 'special_discount',
                    'label'       => 'خصم خاص: ' . $discountRow['name_ar'] . ' (' . $discountRow['discount_percentage'] . '%)',
                    'amount'      => '-' . $discountAmount,
                    'paid'        => false,
                    'included'    => false,
                    'category'    => 'discount',
                ];
            }
        }

        // ── 3. Spouses ──
        $spouses = [];
        try {
            $spouses = $db->select(
                "SELECT * FROM spouses WHERE member_id = ? AND is_archived = 0 ORDER BY spouse_order",
                [$memberId]
            );
        } catch (\Throwable $e) {}

        foreach ($spouses as $s) {
            $order = (int) $s['spouse_order'];
            $fee = $s['addition_fee'] ?? '0.00';
            $isFirstFree = ($order === 1 && bccomp($fee, '0', 2) <= 0);
            $isPaidViaStatus = ($s['status'] === 'active' && bccomp($fee, '0', 2) > 0);

            $breakdown = null;
            if (!empty($s['fee_breakdown_json'])) {
                $breakdown = json_decode($s['fee_breakdown_json'], true);
                if (!is_array($breakdown)) $breakdown = null;
            }

            $items[] = [
                'type'          => 'spouse_fee',
                'label'         => 'زوجة #' . $order . ' — ' . $s['full_name_ar'],
                'amount'        => $isFirstFree ? '0.00' : $fee,
                'paid'          => $isPaidViaStatus,
                'included'      => $isFirstFree,
                'included_note' => $isFirstFree ? 'مشمولة في قيمة العضوية' : null,
                'entity_type'   => 'spouses',
                'entity_id'     => (int) $s['id'],
                'category'      => 'addition',
                'breakdown'     => $breakdown,
            ];
        }

        // ── 4. Children ──
        $children = [];
        try {
            $children = $db->select(
                "SELECT * FROM children WHERE member_id = ? AND is_archived = 0 ORDER BY child_order",
                [$memberId]
            );
        } catch (\Throwable $e) {}

        foreach ($children as $c) {
            $order = (int) $c['child_order'];
            $fee = $c['addition_fee'] ?? '0.00';
            $classification = $c['classification'] ?? 'included';
            $isIncluded = ($classification === 'included' && bccomp($fee, '0', 2) <= 0);
            $isPaidViaStatus = (($c['status'] ?? '') === 'active' && bccomp($fee, '0', 2) > 0);

            $classLabel = match ($classification) {
                'included'           => 'مشمول (بدون رسوم)',
                'dependent_with_fee' => 'تابع (برسوم)',
                'temporary'          => 'مؤقت',
                default              => $classification,
            };

            $breakdown = null;
            if (!empty($c['fee_breakdown_json'])) {
                $breakdown = json_decode($c['fee_breakdown_json'], true);
                if (!is_array($breakdown)) $breakdown = null;
            }

            $items[] = [
                'type'          => 'child_fee',
                'label'         => ($c['gender'] === 'male' ? 'ابن' : 'ابنة') . ' #' . $order . ' — ' . $c['full_name_ar'] . ' (' . (int) ($c['age_years'] ?? 0) . ' سنة)',
                'amount'        => $fee,
                'paid'          => $isPaidViaStatus,
                'included'      => $isIncluded,
                'included_note' => $isIncluded ? 'مشمول — ' . $classLabel : $classLabel,
                'entity_type'   => 'children',
                'entity_id'     => (int) $c['id'],
                'category'      => 'addition',
                'breakdown'     => $breakdown,
            ];
        }

        // ── 5. Temporary Members ──
        try {
            $temps = $db->select(
                "SELECT * FROM temporary_members WHERE member_id = ? AND is_archived = 0 ORDER BY id",
                [$memberId]
            );
            foreach ($temps as $t) {
                $breakdown = null;
                if (!empty($t['fee_breakdown_json'])) {
                    $breakdown = json_decode($t['fee_breakdown_json'], true);
                    if (!is_array($breakdown)) $breakdown = null;
                }

                $tFee = $t['addition_fee'] ?? '0.00';
                $isPaidViaStatus = (($t['status'] ?? '') === 'active' && bccomp($tFee, '0', 2) > 0);

                $items[] = [
                    'type'        => 'temp_fee',
                    'label'       => 'عضو مؤقت — ' . $t['full_name_ar'] . ' (' . $t['category'] . ')',
                    'amount'      => $tFee,
                    'paid'        => $isPaidViaStatus,
                    'included'    => false,
                    'entity_type' => 'temporary_members',
                    'entity_id'   => (int) $t['id'],
                    'category'    => 'addition',
                    'breakdown'   => $breakdown,
                ];
            }
        } catch (\Throwable $e) {}

        // ── Check what's already paid ──
        try {
            $paidAdditions = $db->select(
                "SELECT related_entity_type, related_entity_id FROM payments
                 WHERE member_id = ? AND payment_type = 'addition_fee' AND is_voided = 0",
                [$memberId]
            );
            foreach ($paidAdditions as $pa) {
                foreach ($items as &$item) {
                    if (($item['entity_type'] ?? '') === $pa['related_entity_type']
                        && ($item['entity_id'] ?? 0) == $pa['related_entity_id']) {
                        $item['paid'] = true;
                    }
                }
                unset($item);
            }
        } catch (\Throwable $e) {}

        // ── Check payment_requests for completed or pending additions + load breakdowns ──
        try {
            $additionRequests = $db->select(
                "SELECT related_entity_type, related_entity_id, status, notes, payment_type FROM payment_requests
                 WHERE member_id = ? AND is_voided = 0",
                [$memberId]
            );
            foreach ($additionRequests as $ar) {
                foreach ($items as &$item) {
                    $matchesEntity = ($item['entity_type'] ?? '') === ($ar['related_entity_type'] ?? '')
                        && ($item['entity_id'] ?? 0) == ($ar['related_entity_id'] ?? 0)
                        && ($ar['payment_type'] === 'addition_fee');
                    $matchesType = !isset($item['entity_type'])
                        && (($item['type'] === 'form_fee' && $ar['payment_type'] === 'form_fee')
                         || ($item['type'] === 'membership_fee' && in_array($ar['payment_type'], ['membership_fee', 'down_payment'], true)));

                    if ($matchesEntity || $matchesType) {
                        if ($ar['status'] === 'completed') {
                            $item['paid'] = true;
                        } elseif (in_array($ar['status'], ['pending', 'processing'], true)) {
                            $item['in_queue'] = true;
                        }
                        if (!empty($ar['notes'])) {
                            $nd = json_decode($ar['notes'], true);
                            if (is_array($nd) && !empty($nd['fee_breakdown'])) {
                                $item['breakdown'] = $nd['fee_breakdown'];
                            }
                        }
                    }
                }
                unset($item);
            }
        } catch (\Throwable $e) {}

        // ── 6. Board Offers (cash discount) ──
        $cashOffer = BoardOfferService::getCashDiscount();
        if ($cashOffer && ($cashOffer['applies_to'] === 'membership_fee' || $cashOffer['applies_to'] === 'all')) {
            $items[] = [
                'type'        => 'board_offer',
                'label'       => 'عرض مجلس الإدارة: ' . $cashOffer['title_ar'] . ' (' . $cashOffer['discount_percentage'] . '%)',
                'amount'      => '0.00',
                'paid'        => false,
                'included'    => false,
                'category'    => 'offer',
                'offer'       => $cashOffer,
            ];
        }

        // ── Calculate totals ──
        $totalRequired = '0.00';
        $totalPaid = '0.00';
        $totalPending = '0.00';
        $totalInQueue = '0.00';
        $totalIncluded = '0.00';

        foreach ($items as $item) {
            if ($item['included']) {
                $totalIncluded = bcadd($totalIncluded, $item['amount'], 2);
                continue;
            }
            $totalRequired = bcadd($totalRequired, $item['amount'], 2);
            if ($item['paid']) {
                $totalPaid = bcadd($totalPaid, $item['amount'], 2);
            } elseif (!empty($item['in_queue'])) {
                $totalInQueue = bcadd($totalInQueue, $item['amount'], 2);
            } else {
                $totalPending = bcadd($totalPending, $item['amount'], 2);
            }
        }

        return [
            'items'              => $items,
            'membership_value'   => $membershipValue,
            'total_required'     => $totalRequired,
            'total_paid'         => $totalPaid,
            'total_pending'      => $totalPending,
            'total_in_queue'     => $totalInQueue,
            'total_included'     => $totalIncluded,
            'form_fee_paid'      => $formFeePaid,
            'form_fee_pending'   => $formFeePending ?? false,
            'membership_paid'    => $membershipPaid,
            'membership_pending' => $membershipPending ?? false,
            'all_paid'           => (bccomp($totalPending, '0', 2) <= 0 && bccomp($totalInQueue, '0', 2) <= 0),
        ];
    }
}