<?php
declare(strict_types=1);

namespace App\Modules\AcademyContracts\Services;

use App\Core\App;
use App\Core\EventBus;
use App\Modules\AcademyContracts\Models\AcademyContract;
use App\Modules\AcademyContracts\Models\AcademySettlement;

final class SettlementService
{
    /**
     * Calculate and create a settlement for a contract for a given month.
     *
     * @param int    $contractId
     * @param string $month  YYYY-MM format
     * @return array The settlement data
     */
    public static function calculateSettlement(int $contractId, string $month): array
    {
        $db = App::getInstance()->db();
        $contract = AcademyContract::findOrFail($contractId);

        $academyId = (int) $contract->academy_id;

        // Query activity_subscriptions where the enrollment links to this academy and the month matches
        $subscriptionRevenue = $db->selectOne(
            "SELECT COALESCE(SUM(asub.total_amount), 0) as total
             FROM activity_subscriptions asub
             INNER JOIN academy_enrollments ae ON ae.id = asub.enrollment_id
             WHERE ae.academy_id = ?
               AND asub.subscription_month = ?
               AND asub.status IN ('paid', 'pending')
               AND asub.is_archived = 0",
            [$academyId, $month]
        );

        $enrollmentRevenue = (float) ($subscriptionRevenue['total'] ?? 0);

        // Also include revenue from SA groups led by academy-linked coaches
        $coachGroupRevenue = $db->selectOne(
            "SELECT COALESCE(SUM(pr.amount), 0) as total
             FROM payment_requests pr
             INNER JOIN sa_group_players sgp ON sgp.id = pr.related_entity_id AND pr.related_entity_type = 'sa_group_players'
             INNER JOIN sa_groups sg ON sg.id = sgp.group_id
             INNER JOIN sa_coaches sc ON sc.id = sg.coach_id
             WHERE sc.academy_id = ?
               AND sc.coach_type = 'academy'
               AND pr.status IN ('paid', 'completed')
               AND DATE_FORMAT(pr.paid_at, '%Y-%m') = ?",
            [$academyId, $month]
        );

        $coachRevenue = (float) ($coachGroupRevenue['total'] ?? 0);
        $totalRevenue = $enrollmentRevenue + $coachRevenue;
        $minimumGuarantee = (float) $contract->minimum_revenue_guarantee;
        $clubCommissionPct = (float) $contract->club_commission_pct;
        $academySharePct = (float) $contract->academy_share_pct;
        $penaltyRatePct = (float) $contract->penalty_rate_pct;

        $revenueSurplus = 0.0;
        $revenueShortfall = 0.0;
        $clubCommission = 0.0;
        $academyShare = 0.0;
        $netAmount = 0.0;
        $penaltyAmount = 0.0;
        $direction = 'balanced';

        if ($totalRevenue >= $minimumGuarantee) {
            // Revenue meets or exceeds minimum guarantee
            $revenueSurplus = $totalRevenue - $minimumGuarantee;
            $academyShare = $revenueSurplus * ($academySharePct / 100);
            $clubCommission = $revenueSurplus * ($clubCommissionPct / 100);
            $direction = 'club_pays_academy';
            $netAmount = $academyShare;
        } else {
            // Revenue below minimum guarantee
            $revenueShortfall = $minimumGuarantee - $totalRevenue;
            $direction = 'academy_pays_club';
            $netAmount = $revenueShortfall;
            // Apply penalty if configured
            if ($penaltyRatePct > 0) {
                $penaltyAmount = $revenueShortfall * ($penaltyRatePct / 100);
            }
        }

        // If revenue equals minimum exactly and no surplus
        if ($totalRevenue == $minimumGuarantee) {
            $direction = 'balanced';
            $netAmount = 0.0;
        }

        // Count enrolled and active players for the revenue record
        $playerCounts = $db->selectOne(
            "SELECT
                COUNT(*) as enrolled_count,
                SUM(CASE WHEN ae.status = 'active' THEN 1 ELSE 0 END) as active_count
             FROM academy_enrollments ae
             WHERE ae.academy_id = ?",
            [$academyId]
        );

        // Create or update academy_revenue_records
        $existingRecord = $db->selectOne(
            "SELECT id FROM academy_revenue_records
             WHERE academy_id = ? AND contract_id = ? AND period_month = ?",
            [$academyId, $contractId, $month]
        );

        $revenueData = [
            'academy_id'            => $academyId,
            'contract_id'           => $contractId,
            'period_month'          => $month,
            'subscription_revenue'  => $enrollmentRevenue,
            'registration_revenue'  => 0.00,
            'other_revenue'         => $coachRevenue,
            'total_revenue'         => $totalRevenue,
            'enrolled_count'        => (int) ($playerCounts['enrolled_count'] ?? 0),
            'active_players_count'  => (int) ($playerCounts['active_count'] ?? 0),
            'is_finalized'          => 1,
            'calculated_at'         => date('Y-m-d H:i:s'),
        ];

        if ($existingRecord) {
            $db->update('academy_revenue_records', $revenueData, 'id = ?', [(int) $existingRecord['id']]);
        } else {
            $db->insert('academy_revenue_records', $revenueData);
        }

        // Generate settlement number
        $settlementNumber = self::generateSettlementNumber();

        // Create academy_settlements record
        $settlementData = [
            'settlement_number' => $settlementNumber,
            'academy_id'        => $academyId,
            'contract_id'       => $contractId,
            'period_month'      => $month,
            'total_revenue'     => $totalRevenue,
            'minimum_guarantee' => $minimumGuarantee,
            'revenue_surplus'   => $revenueSurplus,
            'revenue_shortfall' => $revenueShortfall,
            'club_commission'   => $clubCommission,
            'academy_share'     => $academyShare,
            'net_amount'        => $netAmount,
            'penalty_amount'    => $penaltyAmount,
            'direction'         => $direction,
            'status'            => 'pending_approval',
        ];

        $settlement = AcademySettlement::create($settlementData);

        // Dispatch event
        EventBus::dispatch('academy.settlement.created', [
            'settlement_id' => $settlement->id,
            'contract_id'   => $contractId,
            'academy_id'    => $academyId,
            'month'         => $month,
            'direction'     => $direction,
            'net_amount'    => $netAmount,
        ]);

        return array_merge($settlementData, ['id' => $settlement->id]);
    }

    /**
     * Generate monthly settlements for all active contracts.
     *
     * @param string $month YYYY-MM format
     * @return int Number of settlements generated
     */
    public static function generateMonthlySettlements(string $month): int
    {
        $db = App::getInstance()->db();

        // Get all active contracts
        $contracts = $db->select(
            "SELECT id FROM academy_contracts WHERE status = 'active' AND is_archived = 0"
        );

        $count = 0;
        foreach ($contracts as $contract) {
            // Check if settlement already exists for this contract and month
            $existing = $db->selectOne(
                "SELECT id FROM academy_settlements WHERE contract_id = ? AND period_month = ?",
                [(int) $contract['id'], $month]
            );

            if (!$existing) {
                self::calculateSettlement((int) $contract['id'], $month);
                $count++;
            }
        }

        return $count;
    }

    /**
     * Approve a settlement.
     */
    public static function approveSettlement(int $settlementId, int $approvedBy): void
    {
        $settlement = AcademySettlement::findOrFail($settlementId);

        $settlement->update([
            'status'      => 'approved',
            'approved_by' => $approvedBy,
            'approved_at' => date('Y-m-d H:i:s'),
        ]);

        EventBus::dispatch('academy.settlement.approved', [
            'settlement_id' => $settlementId,
            'approved_by'   => $approvedBy,
            'contract_id'   => (int) $settlement->contract_id,
            'academy_id'    => (int) $settlement->academy_id,
            'direction'     => $settlement->direction,
            'net_amount'    => (float) $settlement->net_amount,
        ]);
    }

    /**
     * Generate a unique settlement number in the format STL-YYYY-XXXX.
     */
    private static function generateSettlementNumber(): string
    {
        $db = App::getInstance()->db();
        $year = date('Y');
        $prefix = 'STL-' . $year . '-';

        $lastRow = $db->selectOne(
            "SELECT settlement_number FROM academy_settlements
             WHERE settlement_number LIKE ?
             ORDER BY settlement_number DESC LIMIT 1",
            [$prefix . '%']
        );

        if ($lastRow) {
            $lastSeq = (int) substr($lastRow['settlement_number'], strlen($prefix));
            $nextSeq = $lastSeq + 1;
        } else {
            $nextSeq = 1;
        }

        return $prefix . str_pad((string) $nextSeq, 4, '0', STR_PAD_LEFT);
    }
}
