<?php
declare(strict_types=1);

namespace App\Modules\Members\Services;

use App\Core\App;

final class MemberNumberGenerator
{
    /**
     * Assign the next membership number to a member.
     * Called ONLY after membership value payment is confirmed.
     * Number is based on payment priority — first to pay gets next number.
     */
    public static function assign(int $memberId): ?string
    {
        $db = App::getInstance()->db();

        $member = $db->selectOne(
            "SELECT id, membership_number FROM members WHERE id = ? AND is_archived = 0",
            [$memberId]
        );
        if (!$member) return null;
        if (!empty($member['membership_number'])) return $member['membership_number'];

        for ($attempt = 1; $attempt <= 3; $attempt++) {
            $nextNumber = self::getNextMembershipNumber();
            if ($nextNumber === null) return null;

            $numberStr = (string) $nextNumber;

            try {
                $db->update(
                    'members',
                    ['membership_number' => $numberStr, 'updated_at' => date('Y-m-d H:i:s')],
                    '`id` = ?',
                    [$memberId]
                );
                return $numberStr;
            } catch (\PDOException $e) {
                if ($e->getCode() === '23000' || str_contains($e->getMessage(), '1062')) {
                    \App\Core\Logger::warning("MemberNumberGenerator: race on attempt {$attempt}", [
                        'member_id' => $memberId, 'number' => $numberStr,
                    ]);
                    if ($attempt === 3) {
                        \App\Core\Logger::error("MemberNumberGenerator: exhausted retries for member #{$memberId}");
                        return null;
                    }
                    continue;
                }
                throw $e;
            }
        }

        return null;
    }

    /**
     * Get next available membership number.
     * Membership numbers are separate from form numbers.
     * Format: sequential integers starting from system config.
     */
    public static function getNextMembershipNumber(): ?int
    {
        $db = App::getInstance()->db();

        // Check existing membership numbers
        $last = $db->selectOne(
            "SELECT MAX(CAST(membership_number AS UNSIGNED)) as last_num 
             FROM members 
             WHERE membership_number IS NOT NULL 
             AND membership_number REGEXP '^[0-9]+$'"
        );
        $lastNum = (int) ($last['last_num'] ?? 0);

        if ($lastNum > 0) {
            return $lastNum + 1;
        }

        // Check system config for starting membership number
        $config = $db->selectOne(
            "SELECT config_value FROM system_config WHERE config_key = ?",
            ['membership.number_start']
        );

        if ($config && is_numeric($config['config_value']) && (int) $config['config_value'] > 0) {
            return (int) $config['config_value'];
        }

        // Default starting number per spec: 1001/2 format = 1001
        return 1001;
    }

    /**
     * Set the starting membership number (separate from form number).
     */
    public static function setMembershipNumberStart(int $number): void
    {
        $db = App::getInstance()->db();
        $existing = $db->selectOne(
            "SELECT id FROM system_config WHERE config_key = ?",
            ['membership.number_start']
        );

        if ($existing) {
            $db->update('system_config', [
                'config_value' => (string) $number,
                'updated_at'   => date('Y-m-d H:i:s'),
            ], '`id` = ?', [(int) $existing['id']]);
        } else {
            $db->insert('system_config', [
                'config_key'     => 'membership.number_start',
                'config_value'   => (string) $number,
                'config_type'    => 'integer',
                'group_name'     => 'membership',
                'description_ar' => 'رقم بداية العضويات (رقم العضوية وليس الاستمارة)',
                'description_en' => 'Starting Membership Number',
                'is_editable'    => 1,
                'created_at'     => date('Y-m-d H:i:s'),
                'updated_at'     => date('Y-m-d H:i:s'),
            ]);
        }
    }

    // ── Form Number Methods (unchanged) ──

    public static function next(): ?int
    {
        $db = App::getInstance()->db();
        $last = $db->selectOne(
            "SELECT MAX(CAST(form_number AS UNSIGNED)) as last_num 
             FROM members 
             WHERE form_number IS NOT NULL 
             AND form_number REGEXP '^[0-9]+$'"
        );
        $lastNum = (int) ($last['last_num'] ?? 0);

        if ($lastNum > 0) {
            return $lastNum + 1;
        }

        $config = $db->selectOne(
            "SELECT config_value FROM system_config WHERE config_key = ?",
            ['membership.form_start_number']
        );

        if ($config && is_numeric($config['config_value']) && (int) $config['config_value'] > 0) {
            return (int) $config['config_value'];
        }

        return null;
    }

    public static function setStartNumber(int $number): void
    {
        $db = App::getInstance()->db();
        $existing = $db->selectOne(
            "SELECT id FROM system_config WHERE config_key = ?",
            ['membership.form_start_number']
        );

        if ($existing) {
            $db->update('system_config', [
                'config_value' => (string) $number,
                'updated_at'   => date('Y-m-d H:i:s'),
            ], '`id` = ?', [(int) $existing['id']]);
        } else {
            $db->insert('system_config', [
                'config_key'     => 'membership.form_start_number',
                'config_value'   => (string) $number,
                'config_type'    => 'integer',
                'group_name'     => 'membership',
                'description_ar' => 'رقم بداية الاستمارات',
                'description_en' => 'Starting Form Number',
                'is_editable'    => 1,
                'created_at'     => date('Y-m-d H:i:s'),
                'updated_at'     => date('Y-m-d H:i:s'),
            ]);
        }
    }

    public static function isConfigured(): bool
    {
        return self::next() !== null;
    }

    public static function getFormFee(): string
    {
        try {
            $db = App::getInstance()->db();
            $fee = $db->selectOne(
                "SELECT base_amount FROM service_catalog WHERE service_code IN ('SVC_NEW_FORM','FORM_NEW_MEMBERSHIP') AND is_active = 1 ORDER BY service_code ASC LIMIT 1"
            );
            if ($fee && $fee['base_amount']) {
                return $fee['base_amount'];
            }
        } catch (\Throwable $e) {}
        return '505.00';
    }

    /**
     * Get the membership value based on branch and qualification.
     * Per spec: High qual = 150,000, Medium = 225,000, No qual = 300,000
     */
    public static function getMembershipValue(int $branchId, ?int $qualificationId): string
    {
        if (!$qualificationId) return '300000.00'; // No qualification default

        try {
            $db = App::getInstance()->db();
            $pricing = $db->selectOne(
                "SELECT price FROM pricing_configs 
                 WHERE branch_id = ? AND qualification_id = ? AND membership_type = 'working' 
                 AND is_active = 1 AND effective_from <= CURDATE() 
                 AND (effective_to IS NULL OR effective_to >= CURDATE()) 
                 ORDER BY effective_from DESC LIMIT 1",
                [$branchId, $qualificationId]
            );
            if ($pricing) return $pricing['price'];
        } catch (\Throwable $e) {}

        // Fallback based on qualification code
        try {
            $db = App::getInstance()->db();
            $qual = $db->selectOne("SELECT code FROM qualifications WHERE id = ?", [$qualificationId]);
            if ($qual) {
                return match ($qual['code'] ?? '') {
                    'high'   => '150000.00',
                    'medium' => '225000.00',
                    'none'   => '300000.00',
                    default  => '150000.00',
                };
            }
        } catch (\Throwable $e) {}

        return '150000.00';
    }
}