<?php
declare(strict_types=1);

namespace App\Modules\SportsActivity\Services;

use App\Core\App;

final class AcademyPricingService
{
    /**
     * Get academy subscription price for a player.
     * Cascading logic: base price → member/nonmember → sibling discount → advance payment discount
     */
    public static function getSubscriptionPrice(
        string $academyCode,
        string $levelNameAr,
        bool $isMember,
        int $months = 1,
        bool $hasSibling = false
    ): array {
        $db = App::getInstance()->db();

        $rule = $db->selectOne(
            "SELECT * FROM sa_academy_pricing
             WHERE academy_code = ? AND level_name_ar = ? AND category = 'subscription'
             AND is_active = 1 AND effective_from <= CURDATE()
             AND (effective_to IS NULL OR effective_to >= CURDATE())
             ORDER BY effective_from DESC LIMIT 1",
            [$academyCode, $levelNameAr]
        );

        if (!$rule) {
            return ['found' => false, 'error' => 'لا يوجد تسعير لهذا المستوى'];
        }

        $basePrice = $isMember ? (float) $rule['price_member'] : (float) $rule['price_nonmember'];
        $totalBeforeDiscount = $basePrice * $months;

        $discountResult = DiscountCalculatorService::calculateDiscounts(
            $totalBeforeDiscount,
            $months,
            $hasSibling,
            $academyCode
        );

        return [
            'found' => true,
            'academy_code' => $academyCode,
            'academy_name_ar' => $rule['academy_name_ar'],
            'level_name_ar' => $levelNameAr,
            'is_member' => $isMember,
            'base_price_monthly' => $basePrice,
            'months' => $months,
            'subtotal' => $totalBeforeDiscount,
            'discounts' => $discountResult['discounts'],
            'total_discount' => $discountResult['total_discount'],
            'final_total' => $discountResult['final_amount'],
            'sessions_per_week' => $rule['sessions_per_week'] ? (int) $rule['sessions_per_week'] : null,
            'session_duration_minutes' => $rule['session_duration_minutes'] ? (int) $rule['session_duration_minutes'] : null,
            'includes_fitness' => (bool) (int) ($rule['includes_fitness'] ?? 0),
        ];
    }

    /**
     * Get pricing options for a player based on birth year and discipline.
     */
    public static function getOptionsForPlayer(
        string $disciplineCode,
        ?int $birthYear,
        bool $isMember
    ): array {
        $db = App::getInstance()->db();

        $sql = "SELECT * FROM sa_academy_pricing
                WHERE discipline_code = ? AND category IN ('subscription','team')
                AND is_active = 1 AND effective_from <= CURDATE()
                AND (effective_to IS NULL OR effective_to >= CURDATE())";
        $params = [$disciplineCode];

        if ($birthYear) {
            $sql .= " AND (birth_year = ? OR birth_year IS NULL)";
            $params[] = $birthYear;
        }

        $sql .= " ORDER BY academy_code, sort_order";

        $rows = $db->select($sql, $params);

        $results = [];
        foreach ($rows as $row) {
            $results[] = [
                'id' => (int) $row['id'],
                'academy_code' => $row['academy_code'],
                'academy_name_ar' => $row['academy_name_ar'],
                'level_name_ar' => $row['level_name_ar'],
                'age_group' => $row['age_group'],
                'birth_year' => $row['birth_year'] ? (int) $row['birth_year'] : null,
                'price' => $isMember ? (float) $row['price_member'] : (float) $row['price_nonmember'],
                'sessions_per_week' => $row['sessions_per_week'] ? (int) $row['sessions_per_week'] : null,
                'session_duration_minutes' => $row['session_duration_minutes'] ? (int) $row['session_duration_minutes'] : null,
                'billing_period' => $row['billing_period'],
            ];
        }

        return $results;
    }

    /**
     * Get session card pricing for swimming.
     */
    public static function getSessionCardPricing(): array
    {
        $db = App::getInstance()->db();

        $rows = $db->select(
            "SELECT * FROM sa_academy_pricing
             WHERE category = 'session_card' AND is_active = 1
             AND effective_from <= CURDATE()
             AND (effective_to IS NULL OR effective_to >= CURDATE())
             ORDER BY total_sessions DESC",
            []
        );

        $results = [];
        foreach ($rows as $row) {
            $results[] = [
                'id' => (int) $row['id'],
                'total_sessions' => (int) $row['total_sessions'],
                'price' => (float) $row['price_member'],
                'price_per_session' => round((float) $row['price_member'] / (int) $row['total_sessions'], 2),
                'level_name_ar' => $row['level_name_ar'],
            ];
        }

        return $results;
    }

    /**
     * Get lane rental pricing.
     */
    public static function getLaneRentalPricing(?string $laneType = null): array
    {
        $db = App::getInstance()->db();

        $sql = "SELECT * FROM sa_academy_pricing
                WHERE category = 'lane_rental' AND is_active = 1
                AND effective_from <= CURDATE()
                AND (effective_to IS NULL OR effective_to >= CURDATE())";
        $params = [];

        if ($laneType) {
            $sql .= " AND lane_type = ?";
            $params[] = $laneType;
        }

        $sql .= " ORDER BY lane_type, sessions_per_month DESC";

        $rows = $db->select($sql, $params);

        $results = [];
        foreach ($rows as $row) {
            $results[] = [
                'id' => (int) $row['id'],
                'lane_type' => $row['lane_type'],
                'sessions_per_month' => (int) $row['sessions_per_month'],
                'price' => (float) $row['price_member'],
                'level_name_ar' => $row['level_name_ar'],
            ];
        }

        return $results;
    }

    /**
     * Get private training pricing for a specific academy/coach level.
     */
    public static function getPrivateTrainingPrice(
        string $academyCode,
        int $numPlayers,
        ?string $coachLevel = null
    ): array {
        $db = App::getInstance()->db();

        $sql = "SELECT * FROM sa_academy_pricing
                WHERE academy_code = ? AND category = 'private_training'
                AND is_active = 1 AND effective_from <= CURDATE()
                AND (effective_to IS NULL OR effective_to >= CURDATE())
                AND (num_players_max >= ? OR num_players_max IS NULL)";
        $params = [$academyCode, $numPlayers];

        if ($coachLevel) {
            $sql .= " AND coach_level = ?";
            $params[] = $coachLevel;
        }

        $sql .= " ORDER BY num_players_max ASC, sort_order LIMIT 1";

        $row = $db->selectOne($sql, $params);

        if (!$row) {
            return ['found' => false, 'error' => 'لا يوجد تسعير تدريب خاص'];
        }

        return [
            'found' => true,
            'academy_code' => $row['academy_code'],
            'academy_name_ar' => $row['academy_name_ar'],
            'level_name_ar' => $row['level_name_ar'],
            'coach_level' => $row['coach_level'],
            'num_players' => $numPlayers,
            'price_member' => (float) $row['price_member'],
            'price_nonmember' => (float) $row['price_nonmember'],
            'total_sessions' => $row['total_sessions'] ? (int) $row['total_sessions'] : null,
            'billing_period' => $row['billing_period'],
        ];
    }

    /**
     * Get facility rental price based on entity type, time of day, and usage.
     */
    public static function getFacilityRentalPrice(
        string $facilityType,
        string $entityType,
        string $timePeriod,
        string $usageType = 'practice'
    ): ?array {
        $db = App::getInstance()->db();

        $row = $db->selectOne(
            "SELECT * FROM sa_rental_entity_pricing
             WHERE facility_type = ? AND entity_type = ? AND time_period = ? AND usage_type = ?
             AND is_active = 1 AND effective_from <= CURDATE()
             AND (effective_to IS NULL OR effective_to >= CURDATE())
             ORDER BY effective_from DESC LIMIT 1",
            [$facilityType, $entityType, $timePeriod, $usageType]
        );

        if (!$row) {
            return null;
        }

        return [
            'facility_type' => $row['facility_type'],
            'facility_name_ar' => $row['facility_name_ar'],
            'entity_type' => $row['entity_type'],
            'usage_type' => $row['usage_type'],
            'time_period' => $row['time_period'],
            'duration_minutes' => (int) $row['duration_minutes'],
            'price_amount' => (float) $row['price_amount'],
            'max_players' => $row['max_players'] ? (int) $row['max_players'] : null,
            'notes_ar' => $row['notes_ar'],
        ];
    }

    /**
     * Check if a member is in a free period (club members get free facility access during specific hours).
     */
    public static function isFreePeriod(string $time, bool $isMember): bool
    {
        if (!$isMember) {
            return false;
        }

        $db = App::getInstance()->db();
        $month = (int) date('m');
        $season = ($month >= 11 || $month <= 3) ? 'winter' : 'summer';

        $rules = $db->select(
            "SELECT condition_value FROM sa_discount_rules
             WHERE condition_type = 'free_period' AND is_active = 1",
            []
        );

        $timeObj = strtotime($time);

        foreach ($rules as $rule) {
            $cond = json_decode($rule['condition_value'], true);
            if (!$cond || ($cond['season'] ?? '') !== $season) {
                continue;
            }
            if (!(bool) ($cond['members_only'] ?? false) || !$isMember) {
                continue;
            }

            $start = strtotime($cond['start'] ?? '00:00');
            $end = strtotime($cond['end'] ?? '00:00');

            if ($timeObj >= $start && $timeObj < $end) {
                return true;
            }
        }

        return false;
    }

    /**
     * Get all active discount rules.
     */
    public static function getActiveDiscounts(?string $academyCode = null): array
    {
        $db = App::getInstance()->db();

        $sql = "SELECT * FROM sa_discount_rules WHERE is_active = 1";
        $params = [];

        if ($academyCode) {
            $sql .= " AND (academy_code = ? OR academy_code IS NULL)";
            $params[] = $academyCode;
        }

        return $db->select($sql, $params);
    }

    /**
     * List all academies with their pricing summaries.
     */
    public static function listAcademies(): array
    {
        $db = App::getInstance()->db();

        return $db->select(
            "SELECT academy_code, academy_name_ar, discipline_code,
                    COUNT(*) as pricing_count,
                    MIN(price_member) as min_price_member,
                    MAX(price_member) as max_price_member
             FROM sa_academy_pricing
             WHERE is_active = 1 AND category IN ('subscription','team')
             AND effective_from <= CURDATE()
             AND (effective_to IS NULL OR effective_to >= CURDATE())
             GROUP BY academy_code, academy_name_ar, discipline_code
             ORDER BY academy_name_ar",
            []
        );
    }

    private static function getDiscountRule(string $ruleCode): ?array
    {
        $db = App::getInstance()->db();
        return $db->selectOne(
            "SELECT * FROM sa_discount_rules WHERE rule_code = ? AND is_active = 1",
            [$ruleCode]
        );
    }
}
