<?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 rates from service catalog
        $memberRate = self::getRate('SVC_ANNUAL_MEMBER');
        $spouseRate = self::getRate('SVC_ANNUAL_SPOUSE');
        $childRate = self::getRate('SVC_ANNUAL_CHILD');
        $tempRate = self::getRate('SVC_ANNUAL_TEMP');
        $devFeeData = RuleEngine::get('DEVELOPMENT_FEE');
        $devFee = $devFeeData['amount'] ?? '35.00';

        // Get active members
        $memberWhere = "m.status = 'active' AND m.is_archived = 0 AND m.membership_type NOT IN ('honorary')";
        $memberParams = [];
        if ($branchId) {
            $memberWhere .= ' AND m.branch_id = ?';
            $memberParams[] = $branchId;
        }

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

        $created = 0;
        $skipped = 0;

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

            // 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
            $total = bcadd($memberRate, $devFee, 2);
            $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,
                'total_amount'    => $total,
                'status'          => 'pending',
                'created_at'      => $ts,
                'updated_at'      => $ts,
                'created_by'      => $empId,
            ]);
            $created++;

            // Spouses
            if ($db->tableExists('spouses')) {
                $spouses = $db->select("SELECT id, full_name_ar FROM spouses WHERE member_id = ? AND is_archived = 0 AND status = 'active'", [$memberId]);
                foreach ($spouses as $sp) {
                    $spTotal = bcadd($spouseRate, $devFee, 2);
                    $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' => $devFee,
                        'total_amount'    => $spTotal,
                        'status'          => 'pending',
                        'created_at'      => $ts,
                        'updated_at'      => $ts,
                        'created_by'      => $empId,
                    ]);
                    $created++;
                }
            }

            // Children
            if ($db->tableExists('children')) {
                $children = $db->select("SELECT id, full_name_ar FROM children WHERE member_id = ? AND is_archived = 0 AND status = 'active'", [$memberId]);
                foreach ($children as $ch) {
                    $chTotal = bcadd($childRate, $devFee, 2);
                    $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' => $devFee,
                        'total_amount'    => $chTotal,
                        'status'          => 'pending',
                        'created_at'      => $ts,
                        'updated_at'      => $ts,
                        'created_by'      => $empId,
                    ]);
                    $created++;
                }
            }

            // Temporary members
            if ($db->tableExists('temporary_members')) {
                $temps = $db->select("SELECT id, full_name_ar FROM temporary_members WHERE member_id = ? AND is_archived = 0 AND status = 'active'", [$memberId]);
                foreach ($temps as $t) {
                    $tTotal = bcadd($tempRate, $devFee, 2);
                    $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' => $devFee,
                        'total_amount'    => $tTotal,
                        'status'          => 'pending',
                        'created_at'      => $ts,
                        'updated_at'      => $ts,
                        'created_by'      => $empId,
                    ]);
                    $created++;
                }
            }
        }

        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();
        $row = $db->selectOne(
            "SELECT base_amount FROM service_catalog WHERE service_code = ? AND is_active = 1 AND branch_id IS NULL ORDER BY effective_from DESC LIMIT 1",
            [$serviceCode]
        );
        return $row['base_amount'] ?? '0.00';
    }
}