<?php
declare(strict_types=1);

namespace App\Modules\Subscriptions\Services;

use App\Core\App;
use App\Core\Logger;
use App\Modules\Rules\Services\RuleEngine;

/**
 * Adds subscription rows for newly-activated members and their dependents.
 *
 * syncForMember   — called on member.activated; creates the member's own FY row + all active dependents
 * syncForDependent — called on spouse/child/temporary.fee_paid and *.added (fee=0)
 */
final class SubscriptionSyncService
{
    /**
     * Create subscription rows for a newly-activated member and all their currently-active dependents.
     * Safe to call multiple times — dedup guard prevents duplicate rows.
     */
    public static function syncForMember(int $memberId): void
    {
        try {
            $db = App::getInstance()->db();

            $member = $db->selectOne(
                "SELECT id, full_name_ar, membership_type, status, transferred_from_waiver_id FROM members WHERE id = ? AND is_archived = 0",
                [$memberId]
            );
            if (!$member || $member['status'] !== 'active') {
                return;
            }

            // Waiver-acquired members have no subscriptions — waiver fee covers the annual
            if (!empty($member['transferred_from_waiver_id'])) {
                return;
            }

            $exemptTypes = self::getExemptTypes();
            if (in_array($member['membership_type'], $exemptTypes, true)) {
                return;
            }

            $fy         = financial_year();
            $yearSuffix = explode('/', $fy)[0] ?? '';
            $today      = date('Y-m-d');
            $ts         = date('Y-m-d H:i:s');
            $employee   = App::getInstance()->currentEmployee();
            $empId      = $employee ? (int) $employee->id : null;

            // ── Member's own subscription row ─────────────────────────────
            $existing = $db->selectOne(
                "SELECT id FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = 'member' AND (person_id = ? OR person_id IS NULL)",
                [$memberId, $fy, $memberId]
            );
            if (!$existing) {
                $rateMap = [
                    'SVC_ANNUAL_MEMBER_' . $yearSuffix,
                    'SVC_ANNUAL_MEMBER',
                ];
                $rate = '';
                foreach ($rateMap as $code) {
                    $rate = self::getRate($code);
                    if ($rate !== '') break;
                }
                if ($rate === '') $rate = '492.00';

                $yearAdjustment = RuleEngine::get('SUBSCRIPTION_YEAR_ADJUSTMENT_' . $yearSuffix);
                $discountPct = $yearAdjustment['discount_percentage'] ?? null;
                $discount    = $discountPct ? bcdiv(bcmul($rate, $discountPct, 4), '100', 2) : '0.00';
                $total       = bcsub($rate, $discount, 2);

                $devFeeRule  = RuleEngine::get('DEVELOPMENT_FEE');
                $devFee      = (string) ($devFeeRule['amount'] ?? '35.00');

                try {
                    $db->insert('subscriptions', [
                        'member_id'       => $memberId,
                        'financial_year'  => $fy,
                        'person_type'     => 'member',
                        'person_id'       => $memberId,
                        'person_name'     => $member['full_name_ar'],
                        'base_amount'     => $rate,
                        'development_fee' => $devFee,
                        'discount_amount' => $discount,
                        'total_amount'    => $total,
                        'status'          => 'pending',
                        'created_at'      => $ts,
                        'updated_at'      => $ts,
                        'created_by'      => $empId,
                    ]);
                    Logger::info("SubscriptionSyncService: added member #{$memberId} to FY {$fy}");
                } catch (\Throwable $e) {
                    if (str_contains($e->getMessage(), 'Duplicate entry')) {
                        Logger::info("SubscriptionSyncService: member #{$memberId} FY {$fy} already exists (race guard)");
                    } else {
                        throw $e;
                    }
                }
            }

            // ── All active dependents ─────────────────────────────────────
            $dependentMap = [
                'spouse'    => ['table' => 'spouses',           'id_col' => 'id'],
                'child'     => ['table' => 'children',          'id_col' => 'id'],
                'temporary' => ['table' => 'temporary_members', 'id_col' => 'id'],
            ];
            foreach ($dependentMap as $personType => $cfg) {
                $deps = $db->select(
                    "SELECT id, full_name_ar FROM {$cfg['table']} WHERE member_id = ? AND is_archived = 0 AND status = 'active'",
                    [$memberId]
                );
                foreach ($deps as $dep) {
                    self::syncForDependent($personType, (int) $dep['id'], $memberId);
                }
            }
        } catch (\Throwable $e) {
            Logger::error("SubscriptionSyncService::syncForMember failed: " . $e->getMessage(), [
                'member_id' => $memberId,
            ]);
        }
    }

    /**
     * @param string $personType  'spouse' | 'child' | 'temporary'
     * @param int    $personId    PK in the respective table
     * @param int    $memberId    FK to members
     */
    public static function syncForDependent(string $personType, int $personId, int $memberId): void
    {
        try {
            $db = App::getInstance()->db();

            // Only sync for active members (not seasonal/honorary types)
            $member = $db->selectOne(
                "SELECT id, membership_type, status, transferred_from_waiver_id FROM members WHERE id = ? AND is_archived = 0",
                [$memberId]
            );
            if (!$member || $member['status'] !== 'active') {
                return;
            }

            if (!empty($member['transferred_from_waiver_id'])) {
                return;
            }

            $exemptTypes = self::getExemptTypes();
            if (in_array($member['membership_type'], $exemptTypes, true)) {
                return;
            }

            $fy = financial_year();

            // Dedup guard — matches SubscriptionGenerator logic exactly
            $existing = $db->selectOne(
                "SELECT id FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = ? AND person_id = ?",
                [$memberId, $fy, $personType, $personId]
            );
            if ($existing) {
                return;
            }

            // Resolve the dependent's name from their table
            $tableMap = [
                'spouse'    => 'spouses',
                'child'     => 'children',
                'temporary' => 'temporary_members',
            ];
            $table = $tableMap[$personType] ?? null;
            if (!$table) {
                return;
            }

            $person = $db->selectOne(
                "SELECT full_name_ar FROM {$table} WHERE id = ? AND is_archived = 0",
                [$personId]
            );
            if (!$person) {
                return;
            }

            // Resolve rate using the same logic as SubscriptionGenerator
            $yearSuffix = explode('/', $fy)[0] ?? '';
            $rateMap = [
                'spouse'    => ['SVC_ANNUAL_SPOUSE_' . $yearSuffix, 'SVC_ANNUAL_SPOUSE', '492.00'],
                'child'     => ['SVC_ANNUAL_CHILD_' . $yearSuffix,  'SVC_ANNUAL_CHILD',  '222.00'],
                'temporary' => ['SVC_ANNUAL_TEMP_' . $yearSuffix,   'SVC_ANNUAL_TEMP',   '222.00'],
            ];
            [$specific, $generic, $hardFallback] = $rateMap[$personType];
            $rate = self::getRate($specific) ?: self::getRate($generic) ?: $hardFallback;

            // Year-specific discount
            $yearAdjustment = RuleEngine::get('SUBSCRIPTION_YEAR_ADJUSTMENT_' . $yearSuffix);
            $discountPct = $yearAdjustment['discount_percentage'] ?? null;
            $discount = $discountPct ? bcdiv(bcmul($rate, $discountPct, 4), '100', 2) : '0.00';
            $total = bcsub($rate, $discount, 2);

            $employee = App::getInstance()->currentEmployee();
            $empId    = $employee ? (int) $employee->id : null;
            $ts       = date('Y-m-d H:i:s');

            // Dependents added on/after FY start already paid subscription via addition fee
            $fyStartYear = (int) (explode('/', $fy)[0] ?? date('Y'));
            $fyStartDate = $fyStartYear . '-07-01';
            $depCreatedAt = $db->selectOne("SELECT created_at FROM {$table} WHERE id = ?", [$personId]);
            $status = ($depCreatedAt && $depCreatedAt['created_at'] >= $fyStartDate) ? 'paid' : 'pending';

            try {
                $db->insert('subscriptions', [
                    'member_id'       => $memberId,
                    'financial_year'  => $fy,
                    'person_type'     => $personType,
                    'person_id'       => $personId,
                    'person_name'     => $person['full_name_ar'],
                    'base_amount'     => $rate,
                    'development_fee' => '0.00',
                    'discount_amount' => $discount,
                    'total_amount'    => $total,
                    'status'          => $status,
                    'created_at'      => $ts,
                    'updated_at'      => $ts,
                    'created_by'      => $empId,
                ]);
                Logger::info("SubscriptionSyncService: added {$personType} #{$personId} to FY {$fy} for member #{$memberId} (status={$status})");
            } catch (\Throwable $insertErr) {
                if (str_contains($insertErr->getMessage(), 'Duplicate entry')) {
                    Logger::info("SubscriptionSyncService: {$personType} #{$personId} FY {$fy} already exists (race guard)");
                } else {
                    throw $insertErr;
                }
            }
        } catch (\Throwable $e) {
            Logger::error("SubscriptionSyncService::syncForDependent failed: " . $e->getMessage(), [
                'person_type' => $personType,
                'person_id'   => $personId,
                'member_id'   => $memberId,
            ]);
        }
    }

    /**
     * Re-sync subscriptions for a member after membership composition changes.
     * Adds missing dependents, removes subscription rows for archived/removed dependents (only unpaid).
     */
    public static function refreshForMember(int $memberId): void
    {
        try {
            $db = App::getInstance()->db();
            $fy = financial_year();

            $member = $db->selectOne(
                "SELECT id, membership_type, status, transferred_from_waiver_id FROM members WHERE id = ? AND is_archived = 0",
                [$memberId]
            );
            if (!$member || $member['status'] !== 'active') {
                return;
            }

            if (!empty($member['transferred_from_waiver_id'])) {
                return;
            }

            $exemptTypes = self::getExemptTypes();
            if (in_array($member['membership_type'], $exemptTypes, true)) {
                return;
            }

            // Ensure member's own row exists
            self::syncForMember($memberId);

            // Get current active dependents
            $dependentMap = [
                'spouse'    => 'spouses',
                'child'     => 'children',
                'temporary' => 'temporary_members',
            ];

            $activePersonIds = [];
            foreach ($dependentMap as $personType => $table) {
                $deps = $db->select(
                    "SELECT id FROM {$table} WHERE member_id = ? AND is_archived = 0 AND status = 'active'",
                    [$memberId]
                );
                foreach ($deps as $dep) {
                    $activePersonIds[$personType][] = (int) $dep['id'];
                    self::syncForDependent($personType, (int) $dep['id'], $memberId);
                }
            }

            // Remove unpaid subscription rows for dependents no longer active
            $existingSubs = $db->select(
                "SELECT id, person_type, person_id FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type != 'member' AND status IN ('pending','overdue')",
                [$memberId, $fy]
            );
            foreach ($existingSubs as $sub) {
                $type = $sub['person_type'];
                $pid  = (int) $sub['person_id'];
                if (!in_array($pid, $activePersonIds[$type] ?? [], true)) {
                    $db->delete('subscriptions', '`id` = ?', [(int) $sub['id']]);
                }
            }
        } catch (\Throwable $e) {
            Logger::error("SubscriptionSyncService::refreshForMember failed: " . $e->getMessage(), [
                'member_id' => $memberId,
            ]);
        }
    }

    /**
     * Mark all pending/overdue subscriptions for the current FY as paid.
     * Used after transfer operations where the annual fee was already collected.
     */
    public static function markCurrentYearPaid(int $memberId, ?int $paymentId = null): void
    {
        try {
            $db = App::getInstance()->db();
            $fy = financial_year();
            $ts = date('Y-m-d H:i:s');
            $employee = App::getInstance()->currentEmployee();
            $paidBy = $employee ? (int) $employee->id : null;

            $pendingSubs = $db->select(
                "SELECT id, total_amount, fine_amount, development_fee FROM subscriptions WHERE member_id = ? AND financial_year = ? AND status IN ('pending','overdue')",
                [$memberId, $fy]
            );

            foreach ($pendingSubs as $sub) {
                $paidAmount = bcadd(
                    bcadd($sub['total_amount'], $sub['fine_amount'] ?? '0', 2),
                    $sub['development_fee'] ?? '0',
                    2
                );
                $db->update('subscriptions', [
                    'paid_amount' => $paidAmount,
                    'payment_id'  => $paymentId,
                    'status'      => 'paid',
                    'paid_at'     => $ts,
                    'paid_by'     => $paidBy,
                    'updated_at'  => $ts,
                ], '`id` = ?', [(int) $sub['id']]);
            }
        } catch (\Throwable $e) {
            Logger::error("SubscriptionSyncService::markCurrentYearPaid failed: " . $e->getMessage(), [
                'member_id' => $memberId,
            ]);
        }
    }

    private static function getExemptTypes(): array
    {
        try {
            $db  = App::getInstance()->db();
            $fy  = financial_year();
            $rows = $db->select(
                "SELECT membership_type FROM subscription_rate_overrides WHERE financial_year = ? AND is_exempt = 1",
                [$fy]
            );
            $types = array_column($rows, 'membership_type');
            return empty($types) ? ['honorary', 'seasonal'] : $types;
        } catch (\Throwable $e) {
            return ['honorary', 'seasonal'];
        }
    }

    private static function getRate(string $serviceCode): string
    {
        $db    = App::getInstance()->db();
        $today = date('Y-m-d');
        $row   = $db->selectOne(
            "SELECT base_amount FROM service_catalog WHERE service_code = ? AND is_active = 1 AND branch_id IS NULL AND effective_from <= ? AND (effective_to IS NULL OR effective_to >= ?) ORDER BY effective_from DESC LIMIT 1",
            [$serviceCode, $today, $today]
        );
        if (!$row || !$row['base_amount'] || bccomp($row['base_amount'], '0', 2) <= 0) {
            return '';
        }
        return $row['base_amount'];
    }
}
