<?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 FROM members WHERE id = ? AND is_archived = 0",
                [$memberId]
            );
            if (!$member || $member['status'] !== 'active') {
                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'",
                [$memberId, $fy]
            );
            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');

                $db->insert('subscriptions', [
                    'member_id'       => $memberId,
                    'financial_year'  => $fy,
                    'person_type'     => 'member',
                    'person_id'       => null,
                    '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}");
            }

            // ── 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 FROM members WHERE id = ? AND is_archived = 0",
                [$memberId]
            );
            if (!$member || $member['status'] !== 'active') {
                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');

            $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'          => 'pending',
                'created_at'      => $ts,
                'updated_at'      => $ts,
                'created_by'      => $empId,
            ]);

            Logger::info("SubscriptionSyncService: added {$personType} #{$personId} to FY {$fy} for member #{$memberId}");
        } catch (\Throwable $e) {
            Logger::error("SubscriptionSyncService::syncForDependent failed: " . $e->getMessage(), [
                'person_type' => $personType,
                'person_id'   => $personId,
                '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'];
    }
}
