<?php
declare(strict_types=1);

if (!function_exists('reclama_safe_div')) {
    function reclama_safe_div(int|float $a, int|float $b, int $precision = 4): float
    {
        $a = (float)$a;
        $b = (float)$b;
        if ($b == 0.0) {
            return 0.0;
        }
        return round($a / $b, $precision);
    }
}

if (!function_exists('reclama_calc_ctr')) {
    function reclama_calc_ctr(int $impressions, int $clicks, int $precision = 2): float
    {
        if ($impressions <= 0 || $clicks <= 0) {
            return 0.0;
        }
        return round(($clicks / $impressions) * 100, $precision);
    }
}

if (!function_exists('reclama_normalize_date_range')) {
    function reclama_normalize_date_range(?string $startDate, ?string $endDate, int $defaultDays = 30): array
    {
        $defaultDays = max(1, min(366, $defaultDays));
        $startDate = trim((string)$startDate);
        $endDate   = trim((string)$endDate);

        $start = null;
        $end   = null;

        try {
            if ($startDate !== '') {
                $start = new DateTimeImmutable($startDate . ' 00:00:00');
            }
        } catch (Throwable $e) {
            $start = null;
        }

        try {
            if ($endDate !== '') {
                $end = new DateTimeImmutable($endDate . ' 00:00:00');
            }
        } catch (Throwable $e) {
            $end = null;
        }

        if (!$start && !$end) {
            $end = new DateTimeImmutable(date('Y-m-d') . ' 00:00:00');
            $start = $end->modify('-' . max(0, $defaultDays - 1) . ' day');
        } elseif ($start && !$end) {
            $end = $start->modify('+' . max(0, $defaultDays - 1) . ' day');
        } elseif (!$start && $end) {
            $start = $end->modify('-' . max(0, $defaultDays - 1) . ' day');
        }

        if (!$start) {
            $start = new DateTimeImmutable(date('Y-m-d') . ' 00:00:00');
        }
        if (!$end) {
            $end = $start;
        }

        if ($start > $end) {
            [$start, $end] = [$end, $start];
        }

        $daysCount = (int)$start->diff($end)->days + 1;

        return [
            'start_date' => $start->format('Y-m-d'),
            'end_date'   => $end->format('Y-m-d'),
            'days_count' => $daysCount,
        ];
    }
}

if (!function_exists('reclama_date_span')) {
    function reclama_date_span(string $startDate, string $endDate): array
    {
        $out = [];

        try {
            $start = new DateTimeImmutable($startDate . ' 00:00:00');
            $end   = new DateTimeImmutable($endDate . ' 00:00:00');
        } catch (Throwable $e) {
            return $out;
        }

        if ($start > $end) {
            return $out;
        }

        $limit = 0;
        for ($d = $start; $d <= $end; $d = $d->modify('+1 day')) {
            $out[] = $d->format('Y-m-d');
            $limit++;
            if ($limit >= 731) {
                break;
            }
        }

        return $out;
    }
}

if (!function_exists('reclama_empty_series_map')) {
    function reclama_empty_series_map(string $startDate, string $endDate): array
    {
        $map = [];
        foreach (reclama_date_span($startDate, $endDate) as $day) {
            $map[$day] = [
                'date'                 => $day,
                'impressions_total'    => 0,
                'clicks_total'         => 0,
                'unique_viewers_total' => 0,
                'ctr'                  => 0.0,
            ];
        }
        return $map;
    }
}

if (!function_exists('reclama_campaign_stats_source_sql')) {
    function reclama_campaign_stats_source_sql(PDO $pdo): ?string
    {
        if (!reclama_table_exists($pdo, 'reclama_campaigns')) {
            return null;
        }

        if (reclama_table_exists($pdo, 'reclama_campaign_daily_stats')) {
            $cols   = reclama_daily_stats_columns($pdo);
            $impCol = $cols['impressions'] ?? null;
            $clkCol = $cols['clicks'] ?? null;
            $uniCol = $cols['unique'] ?? null;

            if ($impCol !== null || $clkCol !== null || $uniCol !== null) {
                $impExpr = $impCol !== null ? 'COALESCE(SUM(`' . $impCol . '`), 0)' : '0';
                $clkExpr = $clkCol !== null ? 'COALESCE(SUM(`' . $clkCol . '`), 0)' : '0';
                $uniExpr = $uniCol !== null ? 'COALESCE(SUM(`' . $uniCol . '`), 0)' : '0';

                return '
                    SELECT
                        campaign_id,
                        ' . $impExpr . ' AS impressions_total,
                        ' . $clkExpr . ' AS clicks_total,
                        ' . $uniExpr . ' AS unique_viewers_total
                    FROM reclama_campaign_daily_stats
                    GROUP BY campaign_id
                ';
            }
        }

        $hasImp = reclama_table_exists($pdo, 'reclama_campaign_impressions');
        $hasClk = reclama_table_exists($pdo, 'reclama_campaign_clicks');

        if (!$hasImp && !$hasClk) {
            return null;
        }

        $selectImp  = $hasImp ? 'COALESCE(i.impressions_total, 0)' : '0';
        $selectClk  = $hasClk ? 'COALESCE(k.clicks_total, 0)' : '0';
        $selectUniq = $hasImp ? 'COALESCE(i.unique_viewers_total, 0)' : '0';

        $joinImp = '';
        if ($hasImp) {
            $joinImp = '
                LEFT JOIN (
                    SELECT
                        campaign_id,
                        COUNT(*) AS impressions_total,
                        COUNT(DISTINCT CASE
                            WHEN viewer_user_id IS NOT NULL THEN CONCAT("u:", viewer_user_id)
                            WHEN viewer_session_key IS NOT NULL AND viewer_session_key <> "" THEN CONCAT("s:", viewer_session_key)
                            ELSE NULL
                        END) AS unique_viewers_total
                    FROM reclama_campaign_impressions
                    GROUP BY campaign_id
                ) i ON i.campaign_id = c.id
            ';
        }

        $joinClk = '';
        if ($hasClk) {
            $joinClk = '
                LEFT JOIN (
                    SELECT
                        campaign_id,
                        COUNT(*) AS clicks_total
                    FROM reclama_campaign_clicks
                    GROUP BY campaign_id
                ) k ON k.campaign_id = c.id
            ';
        }

        return '
            SELECT
                c.id AS campaign_id,
                ' . $selectImp . ' AS impressions_total,
                ' . $selectClk . ' AS clicks_total,
                ' . $selectUniq . ' AS unique_viewers_total
            FROM reclama_campaigns c
            ' . $joinImp . '
            ' . $joinClk . '
        ';
    }
}

if (!function_exists('reclama_campaign_daily_series')) {
    function reclama_campaign_daily_series(PDO $pdo, int $campaignId, ?string $startDate = null, ?string $endDate = null, int $defaultDays = 30): array
    {
        $range = reclama_normalize_date_range($startDate, $endDate, $defaultDays);
        $map   = reclama_empty_series_map($range['start_date'], $range['end_date']);

        if ($campaignId <= 0) {
            return [
                'start_date' => $range['start_date'],
                'end_date'   => $range['end_date'],
                'days_count' => $range['days_count'],
                'days'       => array_values($map),
                'totals'     => [
                    'impressions_total'    => 0,
                    'clicks_total'         => 0,
                    'unique_viewers_total' => 0,
                    'ctr'                  => 0.0,
                ],
            ];
        }

        $needImpFallback = true;
        $needClkFallback = true;
        $needUniFallback = true;

        if (reclama_table_exists($pdo, 'reclama_campaign_daily_stats')) {
            $cols   = reclama_daily_stats_columns($pdo);
            $impCol = $cols['impressions'] ?? null;
            $clkCol = $cols['clicks'] ?? null;
            $uniCol = $cols['unique'] ?? null;

            if ($impCol !== null || $clkCol !== null || $uniCol !== null) {
                $impExpr = $impCol !== null ? 'COALESCE(SUM(`' . $impCol . '`), 0)' : '0';
                $clkExpr = $clkCol !== null ? 'COALESCE(SUM(`' . $clkCol . '`), 0)' : '0';
                $uniExpr = $uniCol !== null ? 'COALESCE(SUM(`' . $uniCol . '`), 0)' : '0';

                try {
                    $st = $pdo->prepare('
                        SELECT
                            DATE(stat_date) AS d,
                            ' . $impExpr . ' AS impressions_total,
                            ' . $clkExpr . ' AS clicks_total,
                            ' . $uniExpr . ' AS unique_viewers_total
                        FROM reclama_campaign_daily_stats
                        WHERE campaign_id = ?
                          AND DATE(stat_date) BETWEEN ? AND ?
                        GROUP BY DATE(stat_date)
                        ORDER BY DATE(stat_date) ASC
                    ');
                    $st->execute([$campaignId, $range['start_date'], $range['end_date']]);
                    $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];

                    foreach ($rows as $row) {
                        $day = (string)($row['d'] ?? '');
                        if ($day !== '' && isset($map[$day])) {
                            $map[$day]['impressions_total']    = (int)($row['impressions_total'] ?? 0);
                            $map[$day]['clicks_total']         = (int)($row['clicks_total'] ?? 0);
                            $map[$day]['unique_viewers_total'] = (int)($row['unique_viewers_total'] ?? 0);
                        }
                    }

                    $needImpFallback = ($impCol === null);
                    $needClkFallback = ($clkCol === null);
                    $needUniFallback = ($uniCol === null);
                } catch (Throwable $e) {
                    $needImpFallback = true;
                    $needClkFallback = true;
                    $needUniFallback = true;
                }
            }
        }

        if (($needImpFallback || $needUniFallback) && reclama_table_exists($pdo, 'reclama_campaign_impressions')) {
            try {
                $st = $pdo->prepare('
                    SELECT
                        DATE(shown_at) AS d,
                        COUNT(*) AS impressions_total,
                        COUNT(DISTINCT CASE
                            WHEN viewer_user_id IS NOT NULL THEN CONCAT("u:", viewer_user_id)
                            WHEN viewer_session_key IS NOT NULL AND viewer_session_key <> "" THEN CONCAT("s:", viewer_session_key)
                            ELSE NULL
                        END) AS unique_viewers_total
                    FROM reclama_campaign_impressions
                    WHERE campaign_id = ?
                      AND DATE(shown_at) BETWEEN ? AND ?
                    GROUP BY DATE(shown_at)
                    ORDER BY DATE(shown_at) ASC
                ');
                $st->execute([$campaignId, $range['start_date'], $range['end_date']]);
                $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];

                foreach ($rows as $row) {
                    $day = (string)($row['d'] ?? '');
                    if ($day !== '' && isset($map[$day])) {
                        if ($needImpFallback) {
                            $map[$day]['impressions_total'] = (int)($row['impressions_total'] ?? 0);
                        }
                        if ($needUniFallback) {
                            $map[$day]['unique_viewers_total'] = (int)($row['unique_viewers_total'] ?? 0);
                        }
                    }
                }
            } catch (Throwable $e) {
            }
        }

        if ($needClkFallback && reclama_table_exists($pdo, 'reclama_campaign_clicks')) {
            try {
                $st = $pdo->prepare('
                    SELECT
                        DATE(clicked_at) AS d,
                        COUNT(*) AS clicks_total
                    FROM reclama_campaign_clicks
                    WHERE campaign_id = ?
                      AND DATE(clicked_at) BETWEEN ? AND ?
                    GROUP BY DATE(clicked_at)
                    ORDER BY DATE(clicked_at) ASC
                ');
                $st->execute([$campaignId, $range['start_date'], $range['end_date']]);
                $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];

                foreach ($rows as $row) {
                    $day = (string)($row['d'] ?? '');
                    if ($day !== '' && isset($map[$day])) {
                        $map[$day]['clicks_total'] = (int)($row['clicks_total'] ?? 0);
                    }
                }
            } catch (Throwable $e) {
            }
        }

        $totalImp = 0;
        $totalClk = 0;
        $totalUni = 0;

        foreach ($map as &$day) {
            $day['ctr'] = reclama_calc_ctr((int)$day['impressions_total'], (int)$day['clicks_total']);
            $totalImp += (int)$day['impressions_total'];
            $totalClk += (int)$day['clicks_total'];
            $totalUni += (int)$day['unique_viewers_total'];
        }
        unset($day);

        return [
            'start_date' => $range['start_date'],
            'end_date'   => $range['end_date'],
            'days_count' => $range['days_count'],
            'days'       => array_values($map),
            'totals'     => [
                'impressions_total'    => $totalImp,
                'clicks_total'         => $totalClk,
                'unique_viewers_total' => $totalUni,
                'ctr'                  => reclama_calc_ctr($totalImp, $totalClk),
            ],
        ];
    }
}

if (!function_exists('reclama_user_spend_stats')) {
    function reclama_user_spend_stats(PDO $pdo, int $userId): array
    {
        $stats = [
            'coins_gross_spent' => 0,
            'coins_refunded'    => 0,
            'coins_net_spent'   => 0,
        ];

        if ($userId <= 0 || !reclama_table_exists($pdo, 'vm_coin_transactions')) {
            return $stats;
        }

        try {
            $st = $pdo->prepare('
                SELECT
                    COALESCE(SUM(CASE
                        WHEN event_key IN ("reclama_charge", "reclama_reserve") AND delta_amount < 0
                        THEN ABS(delta_amount)
                        ELSE 0
                    END), 0) AS gross_spent,
                    COALESCE(SUM(CASE
                        WHEN event_key = "reclama_refund" AND delta_amount > 0
                        THEN delta_amount
                        ELSE 0
                    END), 0) AS refunded
                FROM vm_coin_transactions
                WHERE user_id = ?
                  AND (entity_type = "reclama_campaign" OR event_key IN ("reclama_charge", "reclama_reserve", "reclama_refund"))
            ');
            $st->execute([$userId]);
            $row = $st->fetch(PDO::FETCH_ASSOC) ?: [];

            $stats['coins_gross_spent'] = (int)($row['gross_spent'] ?? 0);
            $stats['coins_refunded']    = (int)($row['refunded'] ?? 0);
            $stats['coins_net_spent']   = max(0, $stats['coins_gross_spent'] - $stats['coins_refunded']);
        } catch (Throwable $e) {
        }

        return $stats;
    }
}

if (!function_exists('reclama_user_dashboard_stats')) {
    function reclama_user_dashboard_stats(PDO $pdo, int $userId): array
    {
        $stats = [
            'total'                   => 0,
            'draft'                   => 0,
            'pending'                 => 0,
            'approved'                => 0,
            'active'                  => 0,
            'paused'                  => 0,
            'finished'                => 0,
            'rejected'                => 0,
            'canceled'                => 0,
            'budget_total'            => 0,
            'budget_paid'             => 0,
            'impressions_total'       => 0,
            'clicks_total'            => 0,
            'unique_viewers_total'    => 0,
            'campaigns_with_imps'     => 0,
            'campaigns_with_clicks'   => 0,
            'ctr'                     => 0.0,
            'coins_gross_spent'       => 0,
            'coins_refunded'          => 0,
            'coins_net_spent'         => 0,
        ];

        if ($userId <= 0 || !reclama_table_exists($pdo, 'reclama_campaigns')) {
            return $stats;
        }

        try {
            $st = $pdo->prepare('
                SELECT
                    COUNT(*) AS total,
                    SUM(CASE WHEN status = "draft" THEN 1 ELSE 0 END) AS draft_cnt,
                    SUM(CASE WHEN status = "pending" THEN 1 ELSE 0 END) AS pending_cnt,
                    SUM(CASE WHEN status = "approved" THEN 1 ELSE 0 END) AS approved_cnt,
                    SUM(CASE WHEN status = "active" THEN 1 ELSE 0 END) AS active_cnt,
                    SUM(CASE WHEN status = "paused" THEN 1 ELSE 0 END) AS paused_cnt,
                    SUM(CASE WHEN status = "finished" THEN 1 ELSE 0 END) AS finished_cnt,
                    SUM(CASE WHEN status = "rejected" THEN 1 ELSE 0 END) AS rejected_cnt,
                    SUM(CASE WHEN status = "canceled" THEN 1 ELSE 0 END) AS canceled_cnt,
                    COALESCE(SUM(total_price), 0) AS budget_total,
                    COALESCE(SUM(CASE WHEN payment_status IN ("paid","reserved","partial_refund","refunded") THEN total_price ELSE 0 END), 0) AS budget_paid
                FROM reclama_campaigns
                WHERE user_id = ?
            ');
            $st->execute([$userId]);
            $row = $st->fetch(PDO::FETCH_ASSOC) ?: [];

            $stats['total']        = (int)($row['total'] ?? 0);
            $stats['draft']        = (int)($row['draft_cnt'] ?? 0);
            $stats['pending']      = (int)($row['pending_cnt'] ?? 0);
            $stats['approved']     = (int)($row['approved_cnt'] ?? 0);
            $stats['active']       = (int)($row['active_cnt'] ?? 0);
            $stats['paused']       = (int)($row['paused_cnt'] ?? 0);
            $stats['finished']     = (int)($row['finished_cnt'] ?? 0);
            $stats['rejected']     = (int)($row['rejected_cnt'] ?? 0);
            $stats['canceled']     = (int)($row['canceled_cnt'] ?? 0);
            $stats['budget_total'] = (int)($row['budget_total'] ?? 0);
            $stats['budget_paid']  = (int)($row['budget_paid'] ?? 0);
        } catch (Throwable $e) {
        }

        $statsSql = reclama_campaign_stats_source_sql($pdo);
        if ($statsSql !== null) {
            try {
                $st = $pdo->prepare('
                    SELECT
                        COALESCE(SUM(ds.impressions_total), 0) AS impressions_total,
                        COALESCE(SUM(ds.clicks_total), 0) AS clicks_total,
                        COALESCE(SUM(ds.unique_viewers_total), 0) AS unique_viewers_total,
                        SUM(CASE WHEN COALESCE(ds.impressions_total, 0) > 0 THEN 1 ELSE 0 END) AS campaigns_with_imps,
                        SUM(CASE WHEN COALESCE(ds.clicks_total, 0) > 0 THEN 1 ELSE 0 END) AS campaigns_with_clicks
                    FROM reclama_campaigns c
                    LEFT JOIN (' . $statsSql . ') ds ON ds.campaign_id = c.id
                    WHERE c.user_id = ?
                ');
                $st->execute([$userId]);
                $row = $st->fetch(PDO::FETCH_ASSOC) ?: [];

                $stats['impressions_total']     = (int)($row['impressions_total'] ?? 0);
                $stats['clicks_total']          = (int)($row['clicks_total'] ?? 0);
                $stats['unique_viewers_total']  = (int)($row['unique_viewers_total'] ?? 0);
                $stats['campaigns_with_imps']   = (int)($row['campaigns_with_imps'] ?? 0);
                $stats['campaigns_with_clicks'] = (int)($row['campaigns_with_clicks'] ?? 0);
            } catch (Throwable $e) {
            }
        }

        $spend = reclama_user_spend_stats($pdo, $userId);
        $stats = array_merge($stats, $spend);
        $stats['ctr'] = reclama_calc_ctr($stats['impressions_total'], $stats['clicks_total']);

        return $stats;
    }
}

if (!function_exists('reclama_user_top_campaigns')) {
    function reclama_user_top_campaigns(PDO $pdo, int $userId, int $limit = 10, string $sort = 'impressions'): array
    {
        if ($userId <= 0 || !reclama_table_exists($pdo, 'reclama_campaigns')) {
            return [];
        }

        $limit = max(1, min(100, $limit));
        $statsSql = reclama_campaign_stats_source_sql($pdo);

        $orderBy = match ($sort) {
            'clicks' => 'clicks_total DESC, impressions_total DESC, c.id DESC',
            'ctr'    => 'ctr DESC, clicks_total DESC, impressions_total DESC, c.id DESC',
            'budget' => 'c.total_price DESC, impressions_total DESC, c.id DESC',
            'recent' => 'c.id DESC',
            default  => 'impressions_total DESC, clicks_total DESC, c.id DESC',
        };

        $hasPosts = reclama_table_exists($pdo, 'posts');

        $sql = '
            SELECT
                c.id,
                c.campaign_type,
                c.source_post_id,
                c.title,
                c.target_url,
                c.status,
                c.moderation_status,
                c.payment_status,
                c.start_date,
                c.end_date,
                c.days_count,
                c.price_per_day,
                c.total_price,
                ' . ($hasPosts ? 'p.body AS source_post_body,' : 'NULL AS source_post_body,') . '
                ' . ($statsSql !== null ? '
                    COALESCE(ds.impressions_total, 0) AS impressions_total,
                    COALESCE(ds.clicks_total, 0) AS clicks_total,
                    COALESCE(ds.unique_viewers_total, 0) AS unique_viewers_total,
                ' : '
                    0 AS impressions_total,
                    0 AS clicks_total,
                    0 AS unique_viewers_total,
                ') . '
                CASE
                    WHEN ' . ($statsSql !== null ? 'COALESCE(ds.impressions_total, 0)' : '0') . ' > 0
                    THEN ROUND((' . ($statsSql !== null ? 'COALESCE(ds.clicks_total, 0)' : '0') . ' / ' . ($statsSql !== null ? 'COALESCE(ds.impressions_total, 0)' : '1') . ') * 100, 2)
                    ELSE 0
                END AS ctr
            FROM reclama_campaigns c
            ' . ($hasPosts ? 'LEFT JOIN posts p ON p.id = c.source_post_id' : '') . '
            ' . ($statsSql !== null ? 'LEFT JOIN (' . $statsSql . ') ds ON ds.campaign_id = c.id' : '') . '
            WHERE c.user_id = ?
            ORDER BY ' . $orderBy . '
            LIMIT ' . (int)$limit;

        try {
            $st = $pdo->prepare($sql);
            $st->execute([$userId]);
            return $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable $e) {
            return [];
        }
    }
}

if (!function_exists('reclama_admin_overview_stats')) {
    function reclama_admin_overview_stats(PDO $pdo): array
    {
        $stats = reclama_campaigns_admin_stats($pdo) + [
            'unique_viewers_total'   => 0,
            'ctr_total'              => 0.0,
            'advertisers_total'      => 0,
            'active_advertisers'     => 0,
            'campaigns_with_imps'    => 0,
            'campaigns_with_clicks'  => 0,
            'suspicious_campaigns'   => 0,
        ];

        if (!reclama_table_exists($pdo, 'reclama_campaigns')) {
            return $stats;
        }

        $statsSql = reclama_campaign_stats_source_sql($pdo);
        if ($statsSql !== null) {
            try {
                $st = $pdo->query('
                    SELECT
                        COALESCE(SUM(ds.impressions_total), 0) AS impressions_total,
                        COALESCE(SUM(ds.clicks_total), 0) AS clicks_total,
                        COALESCE(SUM(ds.unique_viewers_total), 0) AS unique_viewers_total,
                        SUM(CASE WHEN COALESCE(ds.impressions_total, 0) > 0 THEN 1 ELSE 0 END) AS campaigns_with_imps,
                        SUM(CASE WHEN COALESCE(ds.clicks_total, 0) > 0 THEN 1 ELSE 0 END) AS campaigns_with_clicks,
                        COUNT(DISTINCT c.user_id) AS advertisers_total,
                        COUNT(DISTINCT CASE WHEN c.status = "active" THEN c.user_id ELSE NULL END) AS active_advertisers
                    FROM reclama_campaigns c
                    LEFT JOIN (' . $statsSql . ') ds ON ds.campaign_id = c.id
                ');
                $row = $st->fetch(PDO::FETCH_ASSOC) ?: [];

                $stats['impressions_total']    = (int)($row['impressions_total'] ?? $stats['impressions_total'] ?? 0);
                $stats['clicks_total']         = (int)($row['clicks_total'] ?? $stats['clicks_total'] ?? 0);
                $stats['unique_viewers_total'] = (int)($row['unique_viewers_total'] ?? 0);
                $stats['campaigns_with_imps']  = (int)($row['campaigns_with_imps'] ?? 0);
                $stats['campaigns_with_clicks']= (int)($row['campaigns_with_clicks'] ?? 0);
                $stats['advertisers_total']    = (int)($row['advertisers_total'] ?? 0);
                $stats['active_advertisers']   = (int)($row['active_advertisers'] ?? 0);
                $stats['ctr_total']            = reclama_calc_ctr($stats['impressions_total'], $stats['clicks_total']);
            } catch (Throwable $e) {
            }
        } else {
            $stats['ctr_total'] = reclama_calc_ctr((int)$stats['impressions_total'], (int)$stats['clicks_total']);
        }

        try {
            $st = $pdo->query('
                SELECT COUNT(*) FROM (
                    SELECT
                        c.id,
                        CASE
                            WHEN COALESCE(ds.impressions_total, 0) > 0
                            THEN (COALESCE(ds.clicks_total, 0) / COALESCE(ds.impressions_total, 0)) * 100
                            ELSE 0
                        END AS ctr_val
                    FROM reclama_campaigns c
                    LEFT JOIN (' . ($statsSql ?? 'SELECT NULL AS campaign_id, 0 AS impressions_total, 0 AS clicks_total, 0 AS unique_viewers_total') . ') ds ON ds.campaign_id = c.id
                    WHERE COALESCE(ds.impressions_total, 0) >= 50
                ) z
                WHERE z.ctr_val >= 20
            ');
            $stats['suspicious_campaigns'] = (int)$st->fetchColumn();
        } catch (Throwable $e) {
        }

        return $stats;
    }
}

if (!function_exists('reclama_admin_daily_overview')) {
    function reclama_admin_daily_overview(PDO $pdo, ?string $startDate = null, ?string $endDate = null, int $defaultDays = 14): array
    {
        $range = reclama_normalize_date_range($startDate, $endDate, $defaultDays);
        $map   = reclama_empty_series_map($range['start_date'], $range['end_date']);

        $needImpFallback = true;
        $needClkFallback = true;
        $needUniFallback = true;

        if (reclama_table_exists($pdo, 'reclama_campaign_daily_stats')) {
            $cols   = reclama_daily_stats_columns($pdo);
            $impCol = $cols['impressions'] ?? null;
            $clkCol = $cols['clicks'] ?? null;
            $uniCol = $cols['unique'] ?? null;

            if ($impCol !== null || $clkCol !== null || $uniCol !== null) {
                $impExpr = $impCol !== null ? 'COALESCE(SUM(`' . $impCol . '`), 0)' : '0';
                $clkExpr = $clkCol !== null ? 'COALESCE(SUM(`' . $clkCol . '`), 0)' : '0';
                $uniExpr = $uniCol !== null ? 'COALESCE(SUM(`' . $uniCol . '`), 0)' : '0';

                try {
                    $st = $pdo->prepare('
                        SELECT
                            DATE(stat_date) AS d,
                            ' . $impExpr . ' AS impressions_total,
                            ' . $clkExpr . ' AS clicks_total,
                            ' . $uniExpr . ' AS unique_viewers_total
                        FROM reclama_campaign_daily_stats
                        WHERE DATE(stat_date) BETWEEN ? AND ?
                        GROUP BY DATE(stat_date)
                        ORDER BY DATE(stat_date) ASC
                    ');
                    $st->execute([$range['start_date'], $range['end_date']]);
                    $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];

                    foreach ($rows as $row) {
                        $day = (string)($row['d'] ?? '');
                        if ($day !== '' && isset($map[$day])) {
                            $map[$day]['impressions_total']    = (int)($row['impressions_total'] ?? 0);
                            $map[$day]['clicks_total']         = (int)($row['clicks_total'] ?? 0);
                            $map[$day]['unique_viewers_total'] = (int)($row['unique_viewers_total'] ?? 0);
                        }
                    }

                    $needImpFallback = ($impCol === null);
                    $needClkFallback = ($clkCol === null);
                    $needUniFallback = ($uniCol === null);
                } catch (Throwable $e) {
                }
            }
        }

        if (($needImpFallback || $needUniFallback) && reclama_table_exists($pdo, 'reclama_campaign_impressions')) {
            try {
                $st = $pdo->prepare('
                    SELECT
                        DATE(shown_at) AS d,
                        COUNT(*) AS impressions_total,
                        COUNT(DISTINCT CASE
                            WHEN viewer_user_id IS NOT NULL THEN CONCAT("u:", viewer_user_id)
                            WHEN viewer_session_key IS NOT NULL AND viewer_session_key <> "" THEN CONCAT("s:", viewer_session_key)
                            ELSE NULL
                        END) AS unique_viewers_total
                    FROM reclama_campaign_impressions
                    WHERE DATE(shown_at) BETWEEN ? AND ?
                    GROUP BY DATE(shown_at)
                    ORDER BY DATE(shown_at) ASC
                ');
                $st->execute([$range['start_date'], $range['end_date']]);
                $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];

                foreach ($rows as $row) {
                    $day = (string)($row['d'] ?? '');
                    if ($day !== '' && isset($map[$day])) {
                        if ($needImpFallback) {
                            $map[$day]['impressions_total'] = (int)($row['impressions_total'] ?? 0);
                        }
                        if ($needUniFallback) {
                            $map[$day]['unique_viewers_total'] = (int)($row['unique_viewers_total'] ?? 0);
                        }
                    }
                }
            } catch (Throwable $e) {
            }
        }

        if ($needClkFallback && reclama_table_exists($pdo, 'reclama_campaign_clicks')) {
            try {
                $st = $pdo->prepare('
                    SELECT
                        DATE(clicked_at) AS d,
                        COUNT(*) AS clicks_total
                    FROM reclama_campaign_clicks
                    WHERE DATE(clicked_at) BETWEEN ? AND ?
                    GROUP BY DATE(clicked_at)
                    ORDER BY DATE(clicked_at) ASC
                ');
                $st->execute([$range['start_date'], $range['end_date']]);
                $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];

                foreach ($rows as $row) {
                    $day = (string)($row['d'] ?? '');
                    if ($day !== '' && isset($map[$day])) {
                        $map[$day]['clicks_total'] = (int)($row['clicks_total'] ?? 0);
                    }
                }
            } catch (Throwable $e) {
            }
        }

        $totalImp = 0;
        $totalClk = 0;
        $totalUni = 0;

        foreach ($map as &$day) {
            $day['ctr'] = reclama_calc_ctr((int)$day['impressions_total'], (int)$day['clicks_total']);
            $totalImp += (int)$day['impressions_total'];
            $totalClk += (int)$day['clicks_total'];
            $totalUni += (int)$day['unique_viewers_total'];
        }
        unset($day);

        return [
            'start_date' => $range['start_date'],
            'end_date'   => $range['end_date'],
            'days_count' => $range['days_count'],
            'days'       => array_values($map),
            'totals'     => [
                'impressions_total'    => $totalImp,
                'clicks_total'         => $totalClk,
                'unique_viewers_total' => $totalUni,
                'ctr'                  => reclama_calc_ctr($totalImp, $totalClk),
            ],
        ];
    }
}

if (!function_exists('reclama_admin_top_advertisers')) {
    function reclama_admin_top_advertisers(PDO $pdo, int $limit = 10): array
    {
        if (!reclama_table_exists($pdo, 'reclama_campaigns')) {
            return [];
        }

        $limit    = max(1, min(100, $limit));
        $statsSql = reclama_campaign_stats_source_sql($pdo);
        $hasUsers = reclama_table_exists($pdo, 'users');

        $sql = '
            SELECT
                c.user_id,
                ' . ($hasUsers ? 'u.username,' : '"" AS username,') . '
                ' . ($hasUsers && reclama_column_exists($pdo, 'users', 'name') ? 'u.name,' : '"" AS name,') . '
                COUNT(*) AS campaigns_total,
                SUM(CASE WHEN c.status = "active" THEN 1 ELSE 0 END) AS active_campaigns,
                COALESCE(SUM(c.total_price), 0) AS budget_total,
                COALESCE(SUM(CASE WHEN c.payment_status IN ("paid","reserved","partial_refund","refunded") THEN c.total_price ELSE 0 END), 0) AS budget_paid,
                ' . ($statsSql !== null ? '
                    COALESCE(SUM(ds.impressions_total), 0) AS impressions_total,
                    COALESCE(SUM(ds.clicks_total), 0) AS clicks_total,
                    COALESCE(SUM(ds.unique_viewers_total), 0) AS unique_viewers_total,
                ' : '
                    0 AS impressions_total,
                    0 AS clicks_total,
                    0 AS unique_viewers_total,
                ') . '
                MAX(c.created_at) AS last_campaign_at
            FROM reclama_campaigns c
            ' . ($hasUsers ? 'LEFT JOIN users u ON u.id = c.user_id' : '') . '
            ' . ($statsSql !== null ? 'LEFT JOIN (' . $statsSql . ') ds ON ds.campaign_id = c.id' : '') . '
            GROUP BY c.user_id' . ($hasUsers ? ', u.username' . (reclama_column_exists($pdo, 'users', 'name') ? ', u.name' : '') : '') . '
            ORDER BY impressions_total DESC, clicks_total DESC, budget_paid DESC, campaigns_total DESC
            LIMIT ' . (int)$limit;

        try {
            $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC) ?: [];
            foreach ($rows as &$row) {
                $row['ctr'] = reclama_calc_ctr((int)($row['impressions_total'] ?? 0), (int)($row['clicks_total'] ?? 0));
            }
            unset($row);
            return $rows;
        } catch (Throwable $e) {
            return [];
        }
    }
}

if (!function_exists('reclama_admin_top_campaigns')) {
    function reclama_admin_top_campaigns(PDO $pdo, int $limit = 10, string $sort = 'impressions'): array
    {
        if (!reclama_table_exists($pdo, 'reclama_campaigns')) {
            return [];
        }

        $limit    = max(1, min(100, $limit));
        $statsSql = reclama_campaign_stats_source_sql($pdo);
        $hasUsers = reclama_table_exists($pdo, 'users');

        $orderBy = match ($sort) {
            'clicks' => 'clicks_total DESC, impressions_total DESC, c.id DESC',
            'ctr'    => 'ctr DESC, clicks_total DESC, impressions_total DESC, c.id DESC',
            'budget' => 'c.total_price DESC, impressions_total DESC, c.id DESC',
            'recent' => 'c.id DESC',
            default  => 'impressions_total DESC, clicks_total DESC, c.id DESC',
        };

        $sql = '
            SELECT
                c.id,
                c.user_id,
                ' . ($hasUsers ? 'u.username,' : '"" AS username,') . '
                ' . ($hasUsers && reclama_column_exists($pdo, 'users', 'name') ? 'u.name,' : '"" AS name,') . '
                c.campaign_type,
                c.title,
                c.target_url,
                c.status,
                c.moderation_status,
                c.payment_status,
                c.start_date,
                c.end_date,
                c.days_count,
                c.price_per_day,
                c.total_price,
                ' . ($statsSql !== null ? '
                    COALESCE(ds.impressions_total, 0) AS impressions_total,
                    COALESCE(ds.clicks_total, 0) AS clicks_total,
                    COALESCE(ds.unique_viewers_total, 0) AS unique_viewers_total,
                ' : '
                    0 AS impressions_total,
                    0 AS clicks_total,
                    0 AS unique_viewers_total,
                ') . '
                CASE
                    WHEN ' . ($statsSql !== null ? 'COALESCE(ds.impressions_total, 0)' : '0') . ' > 0
                    THEN ROUND((' . ($statsSql !== null ? 'COALESCE(ds.clicks_total, 0)' : '0') . ' / ' . ($statsSql !== null ? 'COALESCE(ds.impressions_total, 0)' : '1') . ') * 100, 2)
                    ELSE 0
                END AS ctr
            FROM reclama_campaigns c
            ' . ($hasUsers ? 'LEFT JOIN users u ON u.id = c.user_id' : '') . '
            ' . ($statsSql !== null ? 'LEFT JOIN (' . $statsSql . ') ds ON ds.campaign_id = c.id' : '') . '
            ORDER BY ' . $orderBy . '
            LIMIT ' . (int)$limit;

        try {
            return $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable $e) {
            return [];
        }
    }
}

if (!function_exists('reclama_admin_suspicious_campaigns')) {
    function reclama_admin_suspicious_campaigns(PDO $pdo, int $limit = 20, int $minImpressions = 50, float $ctrThreshold = 20.0): array
    {
        if (!reclama_table_exists($pdo, 'reclama_campaigns')) {
            return [];
        }

        $limit         = max(1, min(100, $limit));
        $minImpressions = max(1, $minImpressions);
        $ctrThreshold   = max(0.0, $ctrThreshold);

        $statsSql = reclama_campaign_stats_source_sql($pdo);
        if ($statsSql === null) {
            return [];
        }

        $hasUsers = reclama_table_exists($pdo, 'users');
        $ctrExpr  = 'CASE
            WHEN COALESCE(ds.impressions_total, 0) > 0
            THEN ROUND((COALESCE(ds.clicks_total, 0) / COALESCE(ds.impressions_total, 0)) * 100, 2)
            ELSE 0
        END';

        $sql = '
            SELECT
                c.id,
                c.user_id,
                ' . ($hasUsers ? 'u.username,' : '"" AS username,') . '
                ' . ($hasUsers && reclama_column_exists($pdo, 'users', 'name') ? 'u.name,' : '"" AS name,') . '
                c.campaign_type,
                c.title,
                c.target_url,
                c.status,
                c.moderation_status,
                c.payment_status,
                c.start_date,
                c.end_date,
                c.total_price,
                COALESCE(ds.impressions_total, 0) AS impressions_total,
                COALESCE(ds.clicks_total, 0) AS clicks_total,
                COALESCE(ds.unique_viewers_total, 0) AS unique_viewers_total,
                ' . $ctrExpr . ' AS ctr
            FROM reclama_campaigns c
            ' . ($hasUsers ? 'LEFT JOIN users u ON u.id = c.user_id' : '') . '
            LEFT JOIN (' . $statsSql . ') ds ON ds.campaign_id = c.id
            WHERE COALESCE(ds.impressions_total, 0) >= ?
              AND ' . $ctrExpr . ' >= ?
            ORDER BY ctr DESC, impressions_total DESC, clicks_total DESC, c.id DESC
            LIMIT ' . (int)$limit;

        try {
            $st = $pdo->prepare($sql);
            $st->execute([$minImpressions, $ctrThreshold]);
            return $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
        } catch (Throwable $e) {
            return [];
        }
    }
}