<?php

declare(strict_types=1);

namespace FecaMailshots\Infrastructure;

use FecaMailshots\Application\SourceMetadataProvider;

final class DatabaseSourceMetadataProvider implements SourceMetadataProvider
{
    private DatabaseRouter $router;

    /** @var list<string> */
    private array $builtInSources;
    /** @var array<string, string> */
    private array $builtInTables;
    /** @var array<string, list<string>> */
    private array $sourceFieldsCache = [];

    /** @var array<string, array{left:string,right:string}> */
    private array $joinMap;

    public function __construct(DatabaseRouter $router)
    {
        $this->router = $router;
        $fen = $router->fenDbName();
        $this->builtInSources = ['contacts', 'accounts', 'renewals', 'grants', 'advertisers', 'ads', 'pages', 'articles', 'issues', 'invoices'];

        $this->builtInTables = [
            'contacts' => 'contacts',
            'accounts' => 'accounts',
            'renewals' => 'renewals',
            'grants' => 'grants',
            'advertisers' => $fen . '.advertisers',
            'ads' => $fen . '.ads',
            'pages' => $fen . '.Pages',
            'articles' => $fen . '.Articles',
            'issues' => $fen . '.Issues',
            'invoices' => $fen . '.invoices',
        ];

        $this->joinMap = [
            'contacts|accounts' => ['left' => 'contacts.account_id', 'right' => 'accounts.id'],
            'accounts|contacts' => ['left' => 'accounts.id', 'right' => 'contacts.account_id'],
            'renewals|accounts' => ['left' => 'renewals.account_id', 'right' => 'accounts.id'],
            'accounts|renewals' => ['left' => 'accounts.id', 'right' => 'renewals.account_id'],
            'renewals|contacts' => ['left' => 'renewals.account_id', 'right' => 'contacts.account_id'],
            'contacts|renewals' => ['left' => 'contacts.account_id', 'right' => 'renewals.account_id'],
            'grants|accounts' => ['left' => 'grants.account_id', 'right' => 'accounts.id'],
            'accounts|grants' => ['left' => 'accounts.id', 'right' => 'grants.account_id'],
            'articles|pages' => ['left' => 'articles.PageID', 'right' => 'pages.ID'],
            'pages|articles' => ['left' => 'pages.ID', 'right' => 'articles.PageID'],
            'pages|issues' => ['left' => 'pages.Issue', 'right' => 'issues.ID'],
            'issues|pages' => ['left' => 'issues.ID', 'right' => 'pages.Issue'],
            'ads|pages' => ['left' => 'ads.PageID', 'right' => 'pages.ID'],
            'pages|ads' => ['left' => 'pages.ID', 'right' => 'ads.PageID'],
            'ads|advertisers' => ['left' => 'ads.Advertiser', 'right' => 'advertisers.AdvertiserName'],
            'advertisers|ads' => ['left' => 'advertisers.AdvertiserName', 'right' => 'ads.Advertiser'],
            'invoices|ads' => ['left' => 'invoices.ad_id', 'right' => 'ads.ID'],
            'ads|invoices' => ['left' => 'ads.ID', 'right' => 'invoices.ad_id'],
            'invoices|issues' => ['left' => 'invoices.issue_id', 'right' => 'issues.ID'],
            'issues|invoices' => ['left' => 'issues.ID', 'right' => 'invoices.issue_id'],
            'invoices|pages' => ['left' => 'invoices.issue_id', 'right' => 'pages.Issue'],
            'pages|invoices' => ['left' => 'pages.Issue', 'right' => 'invoices.issue_id'],
        ];
    }

    public function sourceExists(string $source): bool
    {
        if (in_array($source, $this->builtInSources, true)) {
            return true;
        }

        if (!str_contains($source, '.')) {
            return false;
        }

        [$schema, $table] = explode('.', $source, 2);
        $sql = 'SHOW FULL TABLES FROM `' . str_replace('`', '``', $schema) . '`';
        $rows = $this->router->membersPdo()->query($sql)->fetchAll(\PDO::FETCH_NUM);
        foreach ($rows as $row) {
            if (isset($row[0]) && strcasecmp((string) $row[0], $table) === 0) {
                return true;
            }
        }
        return false;
    }

    public function sourceFields(string $source): array
    {
        if (in_array($source, $this->builtInSources, true)) {
            return $this->loadFieldsFromTable($this->builtInTables[$source] ?? $source);
        }

        if (!str_contains($source, '.')) {
            return [];
        }

        [$schema, $table] = explode('.', $source, 2);
        $sql = 'SHOW COLUMNS FROM `' . str_replace('`', '``', $schema) . '`.`' . str_replace('`', '``', $table) . '`';
        $rows = $this->router->membersPdo()->query($sql)->fetchAll(\PDO::FETCH_ASSOC);
        return array_values(array_filter(array_map(static fn(array $row): string => (string) ($row['Field'] ?? ''), $rows), static fn(string $v): bool => $v !== ''));
    }

    public function hasEmailField(string $source): bool
    {
        $fields = $this->sourceFields($source);
        $lower = array_map('strtolower', $fields);
        return in_array('email', $lower, true);
    }

    public function joinPath(string $left, string $right): ?array
    {
        return $this->joinMap[$left . '|' . $right] ?? null;
    }

    public function allKnownSources(): array
    {
        return $this->builtInSources;
    }

    public function sourceTable(string $source): ?string
    {
        return $this->builtInTables[$source] ?? (str_contains($source, '.') ? $source : null);
    }

    public function fieldSql(string $source, string $field, string $alias): ?string
    {
        $source = trim($source);
        if (in_array($source, $this->builtInSources, true)) {
            $column = $this->physicalColumnName($source, $field);
            if ($column === null) {
                return null;
            }
            return $alias . '.`' . str_replace('`', '``', $column) . '`';
        }
        return $alias . '.`' . str_replace('`', '``', $field) . '`';
    }

    private function physicalColumnName(string $source, string $field): ?string
    {
        $table = $this->builtInTables[$source] ?? null;
        if ($table === null) {
            return null;
        }
        foreach ($this->loadFieldsFromTable($table) as $physicalField) {
            if (strcasecmp($physicalField, $field) === 0) {
                return $physicalField;
            }
        }
        return null;
    }

    /** @return list<string> */
    private function loadFieldsFromTable(string $tableSql): array
    {
        $tableSql = trim($tableSql);
        if ($tableSql === '') {
            return [];
        }
        if (isset($this->sourceFieldsCache[$tableSql])) {
            return $this->sourceFieldsCache[$tableSql];
        }

        $sql = 'SHOW COLUMNS FROM ' . $this->quoteSource($tableSql);
        $rows = $this->router->membersPdo()->query($sql)->fetchAll(\PDO::FETCH_ASSOC);
        $fields = array_values(array_filter(
            array_map(static fn(array $row): string => trim((string) ($row['Field'] ?? '')), $rows),
            static fn(string $v): bool => $v !== ''
        ));
        if ($fields === []) {
            throw new \RuntimeException('Source "' . $tableSql . '" has no readable columns.');
        }
        $this->sourceFieldsCache[$tableSql] = $fields;
        return $fields;
    }

    private function quoteSource(string $source): string
    {
        if (str_contains($source, '.')) {
            [$schema, $table] = explode('.', $source, 2);
            return '`' . str_replace('`', '``', $schema) . '`.`' . str_replace('`', '``', $table) . '`';
        }
        return '`' . str_replace('`', '``', $source) . '`';
    }
}
