<?php
declare(strict_types=1);

namespace App\Modules\SportsActivity\Services;

use App\Core\App;

final class SaFinanceReportService
{
    public static function generate(array $filters): array
    {
        $db = App::getInstance()->db();

        [$dateFrom, $dateTo] = self::resolveDateRange($filters);

        $disciplineId = !empty($filters['discipline_id']) ? (int) $filters['discipline_id'] : null;
        $branchId = !empty($filters['branch_id']) ? (int) $filters['branch_id'] : null;

        $subscriptionRevenue = self::getSubscriptionRevenue($db, $dateFrom, $dateTo, $disciplineId, $branchId);
        $bookingRevenue = self::getBookingRevenue($db, $dateFrom, $dateTo, $disciplineId, $branchId);
        $registrationRevenue = self::getRegistrationRevenue($db, $dateFrom, $dateTo, $branchId);
        $coachCosts = self::getCoachCosts($db, $dateFrom, $dateTo, $disciplineId, $branchId);

        $totalRevenue = bcadd(bcadd($subscriptionRevenue['total'], $bookingRevenue['total'], 2), $registrationRevenue['total'], 2);
        $totalCosts = $coachCosts['total'];
        $netProfit = bcsub($totalRevenue, $totalCosts, 2);

        $monthlyTrend = self::getMonthlyTrend($db, $dateFrom, $dateTo, $disciplineId, $branchId);

        $columns = [
            'category'    => 'البند',
            'description' => 'التفاصيل',
            'amount'      => 'المبلغ',
        ];

        $rows = [];
        $rows[] = ['category' => 'إيرادات', 'description' => 'اشتراكات اللاعبين', 'amount' => $subscriptionRevenue['total']];
        $rows[] = ['category' => 'إيرادات', 'description' => 'حجوزات المرافق', 'amount' => $bookingRevenue['total']];
        $rows[] = ['category' => 'إيرادات', 'description' => 'رسوم تسجيل', 'amount' => $registrationRevenue['total']];
        $rows[] = ['category' => 'تكاليف', 'description' => 'أجور المدربين', 'amount' => $coachCosts['total']];
        $rows[] = ['category' => 'صافي', 'description' => 'صافي الربح', 'amount' => $netProfit];

        return [
            'summary' => [
                'total_revenue' => $totalRevenue,
                'total_costs'   => $totalCosts,
                'net_profit'    => $netProfit,
            ],
            'revenue_breakdown' => [
                ['label' => 'اشتراكات اللاعبين', 'amount' => $subscriptionRevenue['total'], 'count' => $subscriptionRevenue['count']],
                ['label' => 'حجوزات المرافق',     'amount' => $bookingRevenue['total'],      'count' => $bookingRevenue['count']],
                ['label' => 'رسوم تسجيل',         'amount' => $registrationRevenue['total'],  'count' => $registrationRevenue['count']],
            ],
            'cost_breakdown' => [
                ['label' => 'أجور المدربين', 'amount' => $coachCosts['total'], 'detail' => $coachCosts['detail']],
            ],
            'monthly_trend' => $monthlyTrend,
            'date_from'     => $dateFrom,
            'date_to'       => $dateTo,
            'columns'       => $columns,
            'rows'          => $rows,
        ];
    }

    private static function resolveDateRange(array $filters): array
    {
        $preset = $filters['period_preset'] ?? 'monthly';

        if ($preset === 'custom' && !empty($filters['date_from']) && !empty($filters['date_to'])) {
            return [$filters['date_from'], $filters['date_to']];
        }

        $today = date('Y-m-d');
        $year = (int) date('Y');
        $month = (int) date('m');

        return match ($preset) {
            'daily'   => [$today, $today],
            'weekly'  => [date('Y-m-d', strtotime('monday this week')), date('Y-m-d', strtotime('sunday this week'))],
            'monthly' => [date('Y-m-01'), date('Y-m-t')],
            'yearly'  => $month >= 7
                ? [$year . '-07-01', ($year + 1) . '-06-30']
                : [($year - 1) . '-07-01', $year . '-06-30'],
            '3yr'     => [date('Y-m-d', strtotime('-3 years')), $today],
            '5yr'     => [date('Y-m-d', strtotime('-5 years')), $today],
            default   => [date('Y-m-01'), date('Y-m-t')],
        };
    }

    private static function getSubscriptionRevenue(\App\Core\Database $db, string $from, string $to, ?int $disciplineId, ?int $branchId): array
    {
        $where = "sub.payment_status = 'paid' AND sub.period_start <= ? AND sub.period_end >= ?";
        $params = [$to, $from];

        $joins = '';
        if ($disciplineId !== null) {
            $joins .= " INNER JOIN sa_groups g ON g.id = sub.group_id INNER JOIN sa_programs p ON p.id = g.program_id";
            $where .= " AND p.discipline_id = ?";
            $params[] = $disciplineId;
        }
        if ($branchId !== null) {
            $joins .= " INNER JOIN sa_players sp ON sp.id = sub.player_id";
            $where .= " AND sp.branch_id = ?";
            $params[] = $branchId;
        }

        $row = $db->selectOne("SELECT COALESCE(SUM(sub.final_amount), 0) AS total, COUNT(*) AS cnt FROM sa_subscriptions sub {$joins} WHERE {$where}", $params);

        return ['total' => $row['total'] ?? '0.00', 'count' => (int) ($row['cnt'] ?? 0)];
    }

    private static function getBookingRevenue(\App\Core\Database $db, string $from, string $to, ?int $disciplineId, ?int $branchId): array
    {
        $where = "b.payment_status = 'paid' AND b.booking_date BETWEEN ? AND ? AND b.status NOT IN ('cancelled')";
        $params = [$from, $to];

        $joins = '';
        if ($disciplineId !== null) {
            $joins .= " INNER JOIN sa_groups g ON g.id = b.group_id INNER JOIN sa_programs p ON p.id = g.program_id";
            $where .= " AND p.discipline_id = ?";
            $params[] = $disciplineId;
        }
        if ($branchId !== null) {
            $where .= " AND b.branch_id = ?";
            $params[] = $branchId;
        }

        $row = $db->selectOne("SELECT COALESCE(SUM(b.total_amount), 0) AS total, COUNT(*) AS cnt FROM sa_bookings b {$joins} WHERE {$where}", $params);

        return ['total' => $row['total'] ?? '0.00', 'count' => (int) ($row['cnt'] ?? 0)];
    }

    private static function getRegistrationRevenue(\App\Core\Database $db, string $from, string $to, ?int $branchId): array
    {
        $where = "sp.registration_fee_paid = 1 AND sp.is_archived = 0 AND sp.created_at BETWEEN ? AND ?";
        $params = [$from . ' 00:00:00', $to . ' 23:59:59'];

        if ($branchId !== null) {
            $where .= " AND sp.branch_id = ?";
            $params[] = $branchId;
        }

        $row = $db->selectOne("SELECT COUNT(*) AS cnt FROM sa_players sp WHERE {$where}", $params);
        $count = (int) ($row['cnt'] ?? 0);

        $feeRow = $db->selectOne("SELECT base_price FROM sa_pricing_rules WHERE activity_type = 'registration' AND is_active = 1 ORDER BY id DESC LIMIT 1");
        $feePerPlayer = $feeRow['base_price'] ?? '0.00';

        $total = bcmul((string) $count, (string) $feePerPlayer, 2);

        return ['total' => $total, 'count' => $count];
    }

    private static function getCoachCosts(\App\Core\Database $db, string $from, string $to, ?int $disciplineId, ?int $branchId): array
    {
        $where = "c.is_active = 1 AND c.is_archived = 0";
        $params = [];
        $joins = '';

        if ($disciplineId !== null) {
            $joins .= " INNER JOIN sa_coach_disciplines cd ON cd.coach_id = c.id";
            $where .= " AND cd.discipline_id = ?";
            $params[] = $disciplineId;
        }
        if ($branchId !== null) {
            $where .= " AND c.branch_id = ?";
            $params[] = $branchId;
        }

        $coaches = $db->select("SELECT c.id, c.full_name_ar, c.payment_model, c.monthly_rate, c.session_rate, c.hourly_rate FROM sa_coaches c {$joins} WHERE {$where}", $params);

        $totalCost = '0.00';
        $detail = [];

        $monthsInRange = max(1, self::monthsBetween($from, $to));

        foreach ($coaches as $coach) {
            $coachCost = '0.00';
            switch ($coach['payment_model']) {
                case 'monthly':
                    $coachCost = bcmul((string) ($coach['monthly_rate'] ?? '0'), (string) $monthsInRange, 2);
                    break;
                case 'per_session':
                    $sessionCount = $db->selectOne(
                        "SELECT COUNT(DISTINCT a.booking_id, a.attendance_date) AS cnt
                         FROM sa_attendance a
                         INNER JOIN sa_bookings bk ON bk.id = a.booking_id
                         WHERE bk.coach_id = ? AND a.attendance_date BETWEEN ? AND ?",
                        [(int) $coach['id'], $from, $to]
                    );
                    $cnt = (int) ($sessionCount['cnt'] ?? 0);
                    $coachCost = bcmul((string) ($coach['session_rate'] ?? '0'), (string) $cnt, 2);
                    break;
                case 'hourly':
                    $sessionCount = $db->selectOne(
                        "SELECT COUNT(DISTINCT a.booking_id, a.attendance_date) AS cnt
                         FROM sa_attendance a
                         INNER JOIN sa_bookings bk ON bk.id = a.booking_id
                         WHERE bk.coach_id = ? AND a.attendance_date BETWEEN ? AND ?",
                        [(int) $coach['id'], $from, $to]
                    );
                    $cnt = (int) ($sessionCount['cnt'] ?? 0);
                    $coachCost = bcmul((string) ($coach['hourly_rate'] ?? '0'), (string) $cnt, 2);
                    break;
            }

            if (bccomp($coachCost, '0', 2) > 0) {
                $detail[] = ['name' => $coach['full_name_ar'], 'model' => $coach['payment_model'], 'amount' => $coachCost];
            }
            $totalCost = bcadd($totalCost, $coachCost, 2);
        }

        return ['total' => $totalCost, 'detail' => $detail];
    }

    private static function getMonthlyTrend(\App\Core\Database $db, string $from, string $to, ?int $disciplineId, ?int $branchId): array
    {
        $subJoins = '';
        $subWhere = "sub.payment_status = 'paid'";
        $subParams = [$from, $to];

        if ($disciplineId !== null) {
            $subJoins .= " INNER JOIN sa_groups g ON g.id = sub.group_id INNER JOIN sa_programs p ON p.id = g.program_id";
            $subWhere .= " AND p.discipline_id = ?";
            $subParams[] = $disciplineId;
        }
        if ($branchId !== null) {
            $subJoins .= " INNER JOIN sa_players sp ON sp.id = sub.player_id";
            $subWhere .= " AND sp.branch_id = ?";
            $subParams[] = $branchId;
        }

        $subRows = $db->select(
            "SELECT DATE_FORMAT(sub.period_start, '%Y-%m') AS month_key, COALESCE(SUM(sub.final_amount), 0) AS revenue
             FROM sa_subscriptions sub {$subJoins}
             WHERE {$subWhere} AND sub.period_start BETWEEN ? AND ?
             GROUP BY month_key ORDER BY month_key",
            $subParams
        );

        $bkWhere = "b.payment_status = 'paid' AND b.status != 'cancelled'";
        $bkParams = [$from, $to];
        $bkJoins = '';

        if ($disciplineId !== null) {
            $bkJoins .= " INNER JOIN sa_groups g2 ON g2.id = b.group_id INNER JOIN sa_programs p2 ON p2.id = g2.program_id";
            $bkWhere .= " AND p2.discipline_id = ?";
            $bkParams[] = $disciplineId;
        }
        if ($branchId !== null) {
            $bkWhere .= " AND b.branch_id = ?";
            $bkParams[] = $branchId;
        }

        $bkRows = $db->select(
            "SELECT DATE_FORMAT(b.booking_date, '%Y-%m') AS month_key, COALESCE(SUM(b.total_amount), 0) AS revenue
             FROM sa_bookings b {$bkJoins}
             WHERE {$bkWhere} AND b.booking_date BETWEEN ? AND ?
             GROUP BY month_key ORDER BY month_key",
            $bkParams
        );

        $monthMap = [];
        foreach ($subRows as $r) {
            $monthMap[$r['month_key']] = bcadd($monthMap[$r['month_key']] ?? '0.00', $r['revenue'], 2);
        }
        foreach ($bkRows as $r) {
            $monthMap[$r['month_key']] = bcadd($monthMap[$r['month_key']] ?? '0.00', $r['revenue'], 2);
        }

        ksort($monthMap);

        $trend = [];
        foreach ($monthMap as $month => $revenue) {
            $trend[] = ['month' => $month, 'revenue' => $revenue];
        }

        return $trend;
    }

    private static function monthsBetween(string $from, string $to): int
    {
        $d1 = new \DateTime($from);
        $d2 = new \DateTime($to);
        $diff = $d1->diff($d2);
        return max(1, $diff->y * 12 + $diff->m + ($diff->d > 0 ? 1 : 0));
    }
}
