<?php

declare(strict_types=1);

require_once dirname(__DIR__, 2) . '/core/auth.php';
require_once dirname(__DIR__, 2) . '/modules/seasons/helpers.php';
require_once dirname(__DIR__, 2) . '/modules/competitions/helpers.php';

function reports_module_boot(): void
{
    require_login();
    require_permission('view_reports');
}

function can_export_reports_module(): bool
{
    return can('export_reports');
}

function report_season_options(): array
{
    return season_list();
}

function report_competition_options(): array
{
    return competition_list();
}

function reports_filters_from_request(array $source): array
{
    return [
        'season_id' => (int) ($source['season_id'] ?? 0),
        'competition_id' => (int) ($source['competition_id'] ?? 0),
    ];
}

function report_filter_labels(array $filters): array
{
    $season = $filters['season_id'] > 0 ? get_season_by_id((int) $filters['season_id']) : null;
    $competition = $filters['competition_id'] > 0 ? get_competition_by_id((int) $filters['competition_id']) : null;

    return [
        'season' => $season['name'] ?? 'All seasons',
        'competition' => $competition['name'] ?? 'All competitions',
    ];
}

function reports_summary_cards(array $filters): array
{
    $params = [];
    $seasonWhere = '';
    $competitionWhere = '';

    if (($filters['season_id'] ?? 0) > 0) {
        $seasonWhere = ' WHERE season_id = :season_id';
        $params['season_id'] = (int) $filters['season_id'];
    }

    if (($filters['competition_id'] ?? 0) > 0) {
        $competitionWhere = ' WHERE competition_id = :competition_id';
        $params['competition_id'] = (int) $filters['competition_id'];
    }

    return [
        'table_rows' => (int) db_fetch_value('SELECT COUNT(*) FROM league_table' . ($competitionWhere !== '' ? $competitionWhere : $seasonWhere), $params, 0),
        'outstanding_balance' => (float) db_fetch_value('SELECT COALESCE(SUM(amount - amount_paid), 0) FROM club_payments' . $seasonWhere, $params, 0),
        'open_cases' => (int) db_fetch_value('SELECT COUNT(*) FROM disciplinary_cases' . ($seasonWhere === '' ? '' : ' WHERE fixture_id IN (SELECT id FROM fixtures WHERE season_id = :season_id)'), $params, 0),
        'open_protests' => (int) db_fetch_value('SELECT COUNT(*) FROM protests' . ($seasonWhere === '' ? ' WHERE status IN ("submitted", "under_review")' : ' WHERE status IN ("submitted", "under_review") AND fixture_id IN (SELECT id FROM fixtures WHERE season_id = :season_id)'), $params, 0),
        'paused_seasons' => (int) db_fetch_value('SELECT COUNT(*) FROM seasons WHERE status = "paused"', [], 0),
        'postponed_fixtures' => (int) db_fetch_value('SELECT COUNT(*) FROM fixtures WHERE status = "postponed"', [], 0),
    ];
}

function paused_season_report_rows(): array
{
    return db_fetch_all(
        'SELECT s.id, s.name, s.start_date, s.end_date, s.paused_at,
                COUNT(c.id) AS competition_count
         FROM seasons s
         LEFT JOIN competitions c ON c.season_id = s.id
         WHERE s.status = "paused"
         GROUP BY s.id
         ORDER BY COALESCE(s.paused_at, s.created_at) DESC, s.id DESC'
    );
}

function postponed_fixture_report_rows(): array
{
    return db_fetch_all(
        'SELECT f.id, f.matchday, f.match_date, f.match_time, f.postponement_reason, f.postponed_at,
                hc.name AS home_club_name, ac.name AS away_club_name,
                c.name AS competition_name, s.name AS season_name
         FROM fixtures f
         INNER JOIN clubs hc ON hc.id = f.home_club_id
         INNER JOIN clubs ac ON ac.id = f.away_club_id
         INNER JOIN competitions c ON c.id = f.competition_id
         INNER JOIN seasons s ON s.id = f.season_id
         WHERE f.status = "postponed"
         ORDER BY COALESCE(f.postponed_at, f.updated_at, f.created_at) DESC, f.id DESC'
    );
}

function standings_report_rows(array $filters): array
{
    $conditions = [];
    $params = [];

    if (($filters['season_id'] ?? 0) > 0) {
        $conditions[] = 'lt.season_id = :season_id';
        $params['season_id'] = (int) $filters['season_id'];
    }

    if (($filters['competition_id'] ?? 0) > 0) {
        $conditions[] = 'lt.competition_id = :competition_id';
        $params['competition_id'] = (int) $filters['competition_id'];
    }

    $sql = 'SELECT lt.position, lt.played, lt.won, lt.drawn, lt.lost, lt.goals_for, lt.goals_against,
                   lt.goal_difference, lt.points, lt.form,
                   cl.name AS club_name,
                   cmp.name AS competition_name,
                   s.name AS season_name
            FROM league_table lt
            INNER JOIN clubs cl ON cl.id = lt.club_id
            INNER JOIN competitions cmp ON cmp.id = lt.competition_id
            INNER JOIN seasons s ON s.id = lt.season_id';

    if ($conditions !== []) {
        $sql .= ' WHERE ' . implode(' AND ', $conditions);
    }

    $sql .= ' ORDER BY s.start_date DESC, cmp.name ASC, lt.position ASC, cl.name ASC';

    return db_fetch_all($sql, $params);
}

function finance_report_rows(array $filters): array
{
    $conditions = [];
    $params = [];

    if (($filters['season_id'] ?? 0) > 0) {
        $conditions[] = 'cp.season_id = :season_id';
        $params['season_id'] = (int) $filters['season_id'];
    }

    $sql = 'SELECT cp.payment_reference, cp.payment_type, cp.amount, cp.amount_paid, cp.payment_status, cp.payment_date,
                   c.name AS club_name, s.name AS season_name,
                   (cp.amount - cp.amount_paid) AS outstanding_amount
            FROM club_payments cp
            INNER JOIN clubs c ON c.id = cp.club_id
            INNER JOIN seasons s ON s.id = cp.season_id';

    if ($conditions !== []) {
        $sql .= ' WHERE ' . implode(' AND ', $conditions);
    }

    $sql .= ' ORDER BY s.start_date DESC, c.name ASC, cp.created_at DESC';

    return db_fetch_all($sql, $params);
}

function discipline_report_rows(array $filters): array
{
    $conditions = [];
    $params = [];

    if (($filters['season_id'] ?? 0) > 0) {
        $conditions[] = 'f.season_id = :season_id';
        $params['season_id'] = (int) $filters['season_id'];
    }

    if (($filters['competition_id'] ?? 0) > 0) {
        $conditions[] = 'f.competition_id = :competition_id';
        $params['competition_id'] = (int) $filters['competition_id'];
    }

    $sql = 'SELECT dc.case_type, dc.status, dc.fine_amount, dc.created_at,
                   cl.name AS club_name,
                   p.first_name, p.middle_name, p.last_name,
                   cmp.name AS competition_name,
                   s.name AS season_name
            FROM disciplinary_cases dc
            LEFT JOIN clubs cl ON cl.id = dc.club_id
            LEFT JOIN players p ON p.id = dc.player_id
            LEFT JOIN fixtures f ON f.id = dc.fixture_id
            LEFT JOIN competitions cmp ON cmp.id = f.competition_id
            LEFT JOIN seasons s ON s.id = f.season_id';

    if ($conditions !== []) {
        $sql .= ' WHERE ' . implode(' AND ', $conditions);
    }

    $sql .= ' ORDER BY dc.created_at DESC, dc.id DESC';

    return db_fetch_all($sql, $params);
}

function protests_report_rows(array $filters): array
{
    $conditions = [];
    $params = [];

    if (($filters['season_id'] ?? 0) > 0) {
        $conditions[] = 'f.season_id = :season_id';
        $params['season_id'] = (int) $filters['season_id'];
    }

    if (($filters['competition_id'] ?? 0) > 0) {
        $conditions[] = 'f.competition_id = :competition_id';
        $params['competition_id'] = (int) $filters['competition_id'];
    }

    $sql = 'SELECT p.title, p.status, p.created_at, p.resolved_at,
                   pc.name AS protesting_club_name,
                   ac.name AS against_club_name,
                   cmp.name AS competition_name,
                   s.name AS season_name
            FROM protests p
            INNER JOIN fixtures f ON f.id = p.fixture_id
            INNER JOIN clubs pc ON pc.id = p.protesting_club_id
            INNER JOIN clubs ac ON ac.id = p.against_club_id
            INNER JOIN competitions cmp ON cmp.id = f.competition_id
            INNER JOIN seasons s ON s.id = f.season_id';

    if ($conditions !== []) {
        $sql .= ' WHERE ' . implode(' AND ', $conditions);
    }

    $sql .= ' ORDER BY p.created_at DESC, p.id DESC';

    return db_fetch_all($sql, $params);
}

function report_export_datasets(array $filters): array
{
    return [
        'standings' => standings_report_rows($filters),
        'finance' => finance_report_rows($filters),
        'discipline' => discipline_report_rows($filters),
        'protests' => protests_report_rows($filters),
        'operations' => array_map(
            static fn(array $row): array => $row + ['report_type' => 'postponed_fixture'],
            postponed_fixture_report_rows()
        ),
    ];
}

function exportable_report_keys(): array
{
    return ['standings', 'finance', 'discipline', 'protests', 'operations'];
}
