<?php

namespace App\Campaigns;

use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Throwable;

class WhaleAdopsTables
{
    /**
     * @return list<string>
     */
    public function candidates(string $dataset): array
    {
        $prefix = (string) config('campaign-performance.adops_table_prefix', 'arb_adops_');

        return match ($dataset) {
            'campaigns' => [$prefix.'campaigns', 'adops_campaigns'],
            'ads' => [$prefix.'ads', 'adops_ads'],
            'adsets' => [$prefix.'adsets', 'adops_adsets'],
            'campaign_daily' => [$prefix.'stats_campaign_daily', 'adops_stats_campaign_daily'],
            'campaign_hourly' => [$prefix.'stats_campaign_hourly', 'adops_stats_campaign_hourly'],
            'stats_daily' => [$prefix.'stats_daily', 'adops_stats_daily'],
            'stats_hourly' => [$prefix.'stats_hourly', 'adops_stats_hourly'],
            'sync_status' => [$prefix.'sync_status', 'adops_sync_status'],
            'monthly_tables' => [$prefix.'monthly_tables', 'adops_monthly_tables'],
            default => [$prefix.$dataset],
        };
    }

    public function name(string $dataset): ?string
    {
        foreach ($this->candidates($dataset) as $table) {
            if ($this->hasTable($table)) {
                return $table;
            }
        }

        return null;
    }

    public function has(string $dataset): bool
    {
        return $this->name($dataset) !== null || $this->monthlyTables($dataset, '2000-01-01', '2100-01-01') !== [];
    }

    /**
     * @return list<string>
     */
    public function tablesFor(string $dataset, string $from, string $to): array
    {
        $single = $this->name($dataset);

        if ($single !== null) {
            return [$single];
        }

        return $this->monthlyTables($dataset, $from, $to);
    }

    /**
     * Every arb_adops_* table except templates and registry/meta tables.
     *
     * @return list<string>
     */
    public function allAdopsTables(): array
    {
        $prefix = (string) config('campaign-performance.adops_table_prefix', 'arb_adops_');
        $skip = [
            $prefix.'monthly_tables',
            $prefix.'table_sizes',
            $prefix.'fx_rates',
            'adops_monthly_tables',
            'adops_table_sizes',
            'adops_fx_rates',
        ];
        $found = [];

        foreach ($this->tablesLike($prefix) as $table) {
            if (in_array($table, $skip, true) || str_ends_with($table, '_template')) {
                continue;
            }

            $found[] = $table;
        }

        sort($found);

        return $found;
    }

    public function hasTable(string $table): bool
    {
        try {
            return Schema::connection('whale')->hasTable($table);
        } catch (Throwable) {
            return false;
        }
    }

    /**
     * @return list<string>
     */
    public function columns(string $table): array
    {
        try {
            return Schema::connection('whale')->getColumnListing($table);
        } catch (Throwable) {
            return [];
        }
    }

    /**
     * @return list<string>
     */
    private function monthlyTables(string $dataset, string $from, string $to): array
    {
        $prefix = (string) config('campaign-performance.adops_table_prefix', 'arb_adops_');
        $base = $this->monthlyPrefix($dataset);

        if ($base === null) {
            return [];
        }

        $fromYm = Carbon::parse($from)->format('Ym');
        $toYm = Carbon::parse($to)->format('Ym');
        $registry = $this->name('monthly_tables');

        if ($registry !== null) {
            try {
                $datasetKeys = array_values(array_unique(array_filter([
                    $dataset,
                    $prefix.$dataset,
                    rtrim($base, '_'),
                ])));

                $names = DB::connection('whale')
                    ->table($registry)
                    ->whereIn('dataset', $datasetKeys)
                    ->whereBetween('yyyymm', [$fromYm, $toYm])
                    ->orderBy('yyyymm')
                    ->pluck('table_name')
                    ->filter()
                    ->values()
                    ->all();

                $found = array_values(array_filter(
                    $names,
                    fn (mixed $name): bool => is_string($name) && $this->hasTable($name),
                ));

                if ($found !== []) {
                    return $found;
                }
            } catch (Throwable) {
                // Fall through to listing tables by name.
            }
        }

        $found = [];

        foreach ($this->tablesLike($base) as $table) {
            $suffix = substr($table, strlen($base));

            if (! preg_match('/^\d{6}$/', $suffix)) {
                continue;
            }

            if ($suffix < $fromYm || $suffix > $toYm) {
                continue;
            }

            $found[] = $table;
        }

        sort($found);

        return $found;
    }

    private function monthlyPrefix(string $dataset): ?string
    {
        $prefix = (string) config('campaign-performance.adops_table_prefix', 'arb_adops_');

        return match ($dataset) {
            'stats_daily' => $prefix.'stats_daily_',
            'stats_hourly' => $prefix.'stats_hourly_',
            'stats_geo_daily' => $prefix.'stats_geo_daily_',
            'stats_breakdown_daily' => $prefix.'stats_breakdown_daily_',
            'conversions_daily' => $prefix.'conversions_daily_',
            'quality_daily' => $prefix.'quality_daily_',
            default => null,
        };
    }

    /**
     * @return list<string>
     */
    private function tablesLike(string $prefix): array
    {
        try {
            $connection = DB::connection('whale');
            $driver = $connection->getDriverName();

            if ($driver === 'sqlite') {
                $rows = $connection->select(
                    "SELECT name FROM sqlite_master WHERE type = 'table' AND name LIKE ?",
                    [$prefix.'%'],
                );

                return array_values(array_filter(array_map(
                    fn (object $row): string => (string) ($row->name ?? ''),
                    $rows,
                )));
            }

            $database = $connection->getDatabaseName();
            $rows = $connection->select(
                'SELECT TABLE_NAME as name FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME LIKE ?',
                [$database, $prefix.'%'],
            );

            return array_values(array_filter(array_map(
                fn (object $row): string => (string) ($row->name ?? ''),
                $rows,
            )));
        } catch (Throwable) {
            return [];
        }
    }
}