<?php
declare(strict_types=1);

namespace App\Modules\Subscriptions\Services;

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

final class SubscriptionGenerator
{
    public static function generateForYear(string $financialYear, ?int $branchId = null): array
    {
        $db = App::getInstance()->db();
        $employee = App::getInstance()->currentEmployee();
        $ts = date('Y-m-d H:i:s');
        $empId = $employee ? (int) $employee->id : null;

        // Get year-specific rates (e.g. 2026/2027 -> suffix 2026), fall back to generic code
        $yearParts = explode('/', $financialYear);
        $yearSuffix = $yearParts[0] ?? '';
        $memberRate = self::getRate('SVC_ANNUAL_MEMBER_' . $yearSuffix) ?: self::getRate('SVC_ANNUAL_MEMBER') ?: '492.00';
        $spouseRate = self::getRate('SVC_ANNUAL_SPOUSE_' . $yearSuffix) ?: self::getRate('SVC_ANNUAL_SPOUSE') ?: '492.00';
        $childRate = self::getRate('SVC_ANNUAL_CHILD_' . $yearSuffix) ?: self::getRate('SVC_ANNUAL_CHILD') ?: '222.00';
        $tempRate = self::getRate('SVC_ANNUAL_TEMP_' . $yearSuffix) ?: self::getRate('SVC_ANNUAL_TEMP') ?: '222.00';
        $devFeeData = RuleEngine::get('DEVELOPMENT_FEE');
        $devFee = $devFeeData['amount'] ?? '35.00';

        // Year-specific discount: applied on final total (base + dev fee) per row
        $yearAdjustment = RuleEngine::get('SUBSCRIPTION_YEAR_ADJUSTMENT_' . $yearSuffix);
        $discountPct = null;
        if ($yearAdjustment && isset($yearAdjustment['discount_percentage'])) {
            $discountPct = $yearAdjustment['discount_percentage'];
        }

        // Get exempt membership types from subscription_rate_overrides
        $exemptTypes = [];
        try {
            $overrides = $db->select(
                "SELECT membership_type FROM subscription_rate_overrides WHERE financial_year = ? AND is_exempt = 1",
                [$financialYear]
            );
            foreach ($overrides as $o) {
                $exemptTypes[] = $o['membership_type'];
            }
        } catch (\Throwable $e) {
            $exemptTypes = ['honorary', 'seasonal'];
        }
        if (empty($exemptTypes)) {
            $exemptTypes = ['honorary', 'seasonal'];
        }

        $excludePlaceholders = implode(',', array_fill(0, count($exemptTypes), '?'));
        $memberWhere = "m.status = 'active' AND m.is_archived = 0 AND m.membership_type NOT IN ({$excludePlaceholders})";
        $memberParams = $exemptTypes;
        if ($branchId) {
            $memberWhere .= ' AND m.branch_id = ?';
            $memberParams[] = $branchId;
        }

        $members = $db->select("SELECT m.id, m.full_name_ar, m.membership_number, m.activated_at, m.transferred_from_waiver_id FROM members m WHERE {$memberWhere}", $memberParams);

        // FY start date: members/dependents activated on/after this date are first-year — skip them
        $fyStartYear = (int) ($yearParts[0] ?? date('Y'));
        $fyStartDate = $fyStartYear . '-07-01';

        $created = 0;
        $skipped = 0;

        foreach ($members as $member) {
            $memberId = (int) $member['id'];

            // Skip first-year members: their membership fee covers the current FY
            if (!empty($member['activated_at']) && $member['activated_at'] >= $fyStartDate && empty($member['transferred_from_waiver_id'])) {
                $skipped++;
                continue;
            }

            // Check if already generated
            $existing = $db->selectOne(
                "SELECT id FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = 'member'",
                [$memberId, $financialYear]
            );
            if ($existing) { $skipped++; continue; }

            // Member subscription — discount on base only; dev fee is separate non-discountable charge
            $discount = $discountPct ? bcdiv(bcmul($memberRate, $discountPct, 4), '100', 2) : '0.00';
            $total = bcsub($memberRate, $discount, 2);
            try {
                $db->insert('subscriptions', [
                    'member_id'       => $memberId,
                    'financial_year'  => $financialYear,
                    'person_type'     => 'member',
                    'person_id'       => $memberId,
                    'person_name'     => $member['full_name_ar'],
                    'base_amount'     => $memberRate,
                    'development_fee' => $devFee,
                    'discount_amount' => $discount,
                    'total_amount'    => $total,
                    'status'          => 'pending',
                    'created_at'      => $ts,
                    'updated_at'      => $ts,
                    'created_by'      => $empId,
                ]);
                $created++;
            } catch (\Throwable $e) {
                if (str_contains($e->getMessage(), 'Duplicate entry')) { $skipped++; continue; } else { throw $e; }
            }

            // Spouses — NO dev fee; if added on/after FY start, mark as paid (already included in addition fee)
            if ($db->tableExists('spouses')) {
                $spouses = $db->select("SELECT id, full_name_ar, created_at FROM spouses WHERE member_id = ? AND is_archived = 0 AND status = 'active'", [$memberId]);
                foreach ($spouses as $sp) {
                    $existingSp = $db->selectOne(
                        "SELECT id FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = 'spouse' AND person_id = ?",
                        [$memberId, $financialYear, (int) $sp['id']]
                    );
                    if ($existingSp) { $skipped++; continue; }
                    $spDiscount = $discountPct ? bcdiv(bcmul($spouseRate, $discountPct, 4), '100', 2) : '0.00';
                    $spTotal = bcsub($spouseRate, $spDiscount, 2);
                    if ($sp['created_at'] >= $fyStartDate) { $skipped++; continue; }
                    $spStatus = 'pending';
                    try {
                        $db->insert('subscriptions', [
                            'member_id'       => $memberId,
                            'financial_year'  => $financialYear,
                            'person_type'     => 'spouse',
                            'person_id'       => (int) $sp['id'],
                            'person_name'     => $sp['full_name_ar'],
                            'base_amount'     => $spouseRate,
                            'development_fee' => '0.00',
                            'discount_amount' => $spDiscount,
                            'total_amount'    => $spTotal,
                            'status'          => $spStatus,
                            'created_at'      => $ts,
                            'updated_at'      => $ts,
                            'created_by'      => $empId,
                        ]);
                        $created++;
                    } catch (\Throwable $e) {
                        if (str_contains($e->getMessage(), 'Duplicate entry')) { $skipped++; } else { throw $e; }
                    }
                }
            }

            // Children — NO dev fee; if added on/after FY start, mark as paid (already included in addition fee)
            if ($db->tableExists('children')) {
                $children = $db->select("SELECT id, full_name_ar, created_at FROM children WHERE member_id = ? AND is_archived = 0 AND status = 'active'", [$memberId]);
                foreach ($children as $ch) {
                    $existingCh = $db->selectOne(
                        "SELECT id FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = 'child' AND person_id = ?",
                        [$memberId, $financialYear, (int) $ch['id']]
                    );
                    if ($existingCh) { $skipped++; continue; }
                    $chDiscount = $discountPct ? bcdiv(bcmul($childRate, $discountPct, 4), '100', 2) : '0.00';
                    $chTotal = bcsub($childRate, $chDiscount, 2);
                    if ($ch['created_at'] >= $fyStartDate) { $skipped++; continue; }
                    $chStatus = 'pending';
                    try {
                        $db->insert('subscriptions', [
                            'member_id'       => $memberId,
                            'financial_year'  => $financialYear,
                            'person_type'     => 'child',
                            'person_id'       => (int) $ch['id'],
                            'person_name'     => $ch['full_name_ar'],
                            'base_amount'     => $childRate,
                            'development_fee' => '0.00',
                            'discount_amount' => $chDiscount,
                            'total_amount'    => $chTotal,
                            'status'          => $chStatus,
                            'created_at'      => $ts,
                            'updated_at'      => $ts,
                            'created_by'      => $empId,
                        ]);
                        $created++;
                    } catch (\Throwable $e) {
                        if (str_contains($e->getMessage(), 'Duplicate entry')) { $skipped++; } else { throw $e; }
                    }
                }
            }

            // Temporary members — NO dev fee; if added on/after FY start, mark as paid (already included in addition fee)
            if ($db->tableExists('temporary_members')) {
                $temps = $db->select("SELECT id, full_name_ar, created_at FROM temporary_members WHERE member_id = ? AND is_archived = 0 AND status = 'active'", [$memberId]);
                foreach ($temps as $t) {
                    $existingTmp = $db->selectOne(
                        "SELECT id FROM subscriptions WHERE member_id = ? AND financial_year = ? AND person_type = 'temporary' AND person_id = ?",
                        [$memberId, $financialYear, (int) $t['id']]
                    );
                    if ($existingTmp) { $skipped++; continue; }
                    $tmpDiscount = $discountPct ? bcdiv(bcmul($tempRate, $discountPct, 4), '100', 2) : '0.00';
                    $tmpTotal = bcsub($tempRate, $tmpDiscount, 2);
                    if ($t['created_at'] >= $fyStartDate) { $skipped++; continue; }
                    $tmpStatus = 'pending';
                    try {
                        $db->insert('subscriptions', [
                            'member_id'       => $memberId,
                            'financial_year'  => $financialYear,
                            'person_type'     => 'temporary',
                            'person_id'       => (int) $t['id'],
                            'person_name'     => $t['full_name_ar'],
                            'base_amount'     => $tempRate,
                            'development_fee' => '0.00',
                            'discount_amount' => $tmpDiscount,
                            'total_amount'    => $tmpTotal,
                            'status'          => $tmpStatus,
                            'created_at'      => $ts,
                            'updated_at'      => $ts,
                            'created_by'      => $empId,
                        ]);
                        $created++;
                    } catch (\Throwable $e) {
                        if (str_contains($e->getMessage(), 'Duplicate entry')) { $skipped++; } else { throw $e; }
                    }
                }
            }
        }

        Logger::info("Subscription batch generated", ['year' => $financialYear, 'created' => $created, 'skipped' => $skipped]);

        return ['created' => $created, 'skipped' => $skipped, 'members_processed' => count($members)];
    }

    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'];
    }
}