<?php

declare(strict_types=1);

namespace FecaMailshots\Application;

use FecaMailshots\Domain\AppError;

final class DslCompiler
{
    private SourceMetadataProvider $metadata;

    public function __construct(SourceMetadataProvider $metadata)
    {
        $this->metadata = $metadata;
    }

    /**
     * @param array{sources:list<string>, where:array<int, mixed>} $ast
     * @return array{sql:string,params:list<mixed>}
     */
    public function compile(array $ast): array
    {
        $sources = $ast['sources'];
        if ($sources === []) {
            throw new AppError('dsl_compile', 'No sources provided');
        }

        $from = $this->quoteSource($sources[0]) . ' AS ' . $this->alias($sources[0]);
        $joins = [];
        $hasIssueContextFilter = $this->hasConstrainingIssueFilter($ast['where']);

        for ($i = 1; $i < count($sources); $i++) {
            $right = $sources[$i];
            $join = $this->findPriorJoinPath(array_slice($sources, 0, $i), $right);
            $path = $join['path'];
            if ($path === null) {
                if ($this->canAppendIssueContext($right, $hasIssueContextFilter)) {
                    $joins[] = 'CROSS JOIN ' . $this->quoteSource($right) . ' AS ' . $this->alias($right);
                    continue;
                }
                throw new AppError('dsl_compile', sprintf('No join path to %s', $right));
            }

            [$leftRef, $rightRef] = $this->rewriteJoinRef($path['left'], $path['right']);
            if ($this->isNormalizedJoin($join['left'], $right)) {
                $leftRef = $this->normalizedTextSql($leftRef);
                $rightRef = $this->normalizedTextSql($rightRef);
            }
            $joins[] = 'INNER JOIN ' . $this->quoteSource($right) . ' AS ' . $this->alias($right)
                . ' ON ' . $leftRef . ' = ' . $rightRef;
        }

        $params = [];
        $whereParts = [];
        foreach ($this->compileAutomaticSourcePredicates($sources) as $predicateSql) {
            $whereParts[] = $predicateSql;
        }
        foreach ($ast['where'] as $predicate) {
            $whereParts[] = $this->compilePredicate($predicate, $params, $sources);
        }

        $whereSql = $whereParts === [] ? '' : ' WHERE ' . implode(' AND ', $whereParts);

        // Always emit source-qualified projection keys so token names remain stable
        // without any post-query alias fallback.
        $selectSql = $this->buildUniqueSelectProjection($sources);
        $sql = 'SELECT ' . $selectSql . ' FROM ' . $from;
        if ($joins !== []) {
            $sql .= ' ' . implode(' ', $joins);
        }
        $sql .= $whereSql;

        return ['sql' => $sql, 'params' => $params];
    }

    /** @param list<mixed> &$params */
    private function compilePredicate(array $predicate, array &$params, array $sources): string
    {
        if ($predicate['type'] === 'group') {
            $parts = [];
            foreach ($predicate['items'] as $item) {
                $parts[] = $this->compilePredicate($item, $params, $sources);
            }
            $sql = '(' . implode(' AND ', $parts) . ')';
            return $predicate['not'] ? 'NOT ' . $sql : $sql;
        }

        if ($predicate['type'] === 'filter') {
            $sql = $this->compileFilter($predicate['name'], $predicate['args'] ?? [], $params, $sources);
            return $predicate['not'] ? 'NOT (' . $sql . ')' : $sql;
        }

        if ($predicate['type'] === 'comparison_value') {
            $lhs = $this->fieldRefSql($predicate['lhs']);
            $op = $predicate['op'];
            $value = $predicate['rhs'];
            if (is_array($value) && ($value['type'] ?? '') === 'blank_literal') {
                if (!in_array($op, ['=', '!='], true)) {
                    throw new AppError('dsl_compile', 'Blank comparison supports only = and !=');
                }
                $sql = $this->compileBlankSql($lhs, $op === '!=');
                return $predicate['not'] ? 'NOT (' . $sql . ')' : $sql;
            }
            if ($op === 'contains') {
                $op = 'LIKE';
                $value = '%' . $value . '%';
            } elseif ($op === 'starts-with') {
                $op = 'LIKE';
                $value = $value . '%';
            } elseif ($op === 'ends-with') {
                $op = 'LIKE';
                $value = '%' . $value;
            }
            $params[] = $value;
            $sql = sprintf('%s %s ?', $lhs, $op);
            return $predicate['not'] ? 'NOT (' . $sql . ')' : $sql;
        }

        if ($predicate['type'] === 'blank') {
            $lhs = $this->fieldRefSql($predicate['lhs']);
            $sql = $this->compileBlankSql($lhs, false);
            return $predicate['not'] ? 'NOT (' . $sql . ')' : $sql;
        }

        if ($predicate['type'] === 'comparison_field') {
            $lhs = $this->fieldRefSql($predicate['lhs']);
            $rhs = $this->fieldRefSql($predicate['rhs']);
            $sql = sprintf('%s %s %s', $lhs, $predicate['op'], $rhs);
            return $predicate['not'] ? 'NOT (' . $sql . ')' : $sql;
        }

        if ($predicate['type'] === 'set_values') {
            $lhs = $this->fieldRefSql($predicate['lhs']);
            $placeholders = [];
            foreach ($predicate['values'] as $value) {
                $placeholders[] = '?';
                $params[] = $value;
            }
            $op = $predicate['op'] === 'not in' ? 'NOT IN' : 'IN';
            $sql = sprintf('%s %s (%s)', $lhs, $op, implode(',', $placeholders));
            return $predicate['not'] ? 'NOT (' . $sql . ')' : $sql;
        }

        if ($predicate['type'] === 'set_ref') {
            $lhs = $this->fieldRefSql($predicate['lhs']);
            $rhs = $this->fieldRefSql($predicate['rhs']);
            $op = $predicate['op'] === 'not in' ? 'NOT IN' : 'IN';
            $sql = sprintf('%s %s (SELECT %s)', $lhs, $op, $rhs);
            return $predicate['not'] ? 'NOT (' . $sql . ')' : $sql;
        }

        throw new AppError('dsl_compile', 'Unknown predicate type', ['type' => $predicate['type']]);
    }

    private function compileBlankSql(string $fieldSql, bool $notBlank): string
    {
        if ($notBlank) {
            return '(' . $fieldSql . ' IS NOT NULL AND ' . $fieldSql . " <> '')";
        }
        return '(' . $fieldSql . ' IS NULL OR ' . $fieldSql . " = '')";
    }

    /** @param list<mixed> $args @param list<mixed> &$params @param list<string> $sources */
    private function compileFilter(string $name, array $args, array &$params, array $sources): string
    {
        if ($name === 'selected-renewal') {
            return $this->alias('renewals') . '.`selected` = 1';
        }
        if ($name === 'pending-renewal') {
            return $this->alias('renewals') . '.`status` = \'pending\'';
        }
        if ($name === 'fen1-contact') {
            return $this->alias('contacts') . '.`is_fen_1` = 1';
        }
        if ($name === 'primary-contact') {
            return $this->alias('contacts') . '.`is_contact_1` = 1';
        }
        if ($name === 'selected') {
            return 'COALESCE(' . $this->alias('advertisers') . '.`Selected`, 0) <> 0';
        }
        if ($name === 'pending-invoice') {
            return $this->alias('invoices') . '.`status` = \'pending\'';
        }
        if ($name === 'selected-invoice') {
            return $this->alias('invoices') . '.`id` IN (SELECT id FROM ' . $this->quoteSource('invoices') . ' WHERE 0=1)';
        }
        if ($name === 'invoice-ids') {
            $ids = $this->numericFilterArgs($name, $args);
            foreach ($ids as $id) {
                $params[] = $id;
            }
            return $this->alias('invoices') . '.`id` IN (' . implode(',', array_fill(0, count($ids), '?')) . ')';
        }
        if ($name === 'issue') {
            return $this->compileIssueFilter($this->singleNumericFilterArg($name, $args), $params, $sources);
        }
        if ($name === 'member-or-affiliate-or-parish-council') {
            $acc = $this->alias('accounts');
            return sprintf(
                "(%s.`account_type_id` IN (SELECT id FROM `picklist_account_type` WHERE value IN ('Member','Affiliate')) OR %s.`name` LIKE '%%Parish Council%%')",
                $acc,
                $acc
            );
        }
        if ($name === 'account-has-article-in-issue') {
            return $this->compileAccountHasArticleInIssueFilter($this->singleNumericFilterArg($name, $args), $params);
        }

        throw new AppError('dsl_compile', 'Unknown filter', ['filter' => $name]);
    }

    /** @return array{string,string} */
    private function rewriteJoinRef(string $left, string $right): array
    {
        $leftParts = explode('.', $left, 2);
        $rightParts = explode('.', $right, 2);

        return [
            $this->alias($leftParts[0]) . '.`' . str_replace('`', '``', $leftParts[1]) . '`',
            $this->alias($rightParts[0]) . '.`' . str_replace('`', '``', $rightParts[1]) . '`',
        ];
    }

    /** @param array{source:string,field:string} $fieldRef */
    private function fieldRefSql(array $fieldRef): string
    {
        $sql = $this->metadata->fieldSql($fieldRef['source'], $fieldRef['field'], $this->alias($fieldRef['source']));
        if ($sql === null) {
            throw new AppError('dsl_compile', 'Unknown field', ['source' => $fieldRef['source'], 'field' => $fieldRef['field']]);
        }
        return $sql;
    }

    private function alias(string $source): string
    {
        return 's_' . preg_replace('/[^a-z0-9_]/', '_', strtolower($source));
    }

    private function quoteSource(string $source): string
    {
        $table = $this->metadata->sourceTable($source) ?? $source;
        if (str_contains($table, '.')) {
            [$schema, $table] = explode('.', $table, 2);
            return '`' . $schema . '`.`' . $table . '`';
        }
        return '`' . $table . '`';
    }

    /** @param list<string> $sources */
    private function buildUniqueSelectProjection(array $sources): string
    {
        $parts = [];
        foreach ($sources as $source) {
            $alias = $this->alias($source);
            foreach ($this->metadata->sourceFields($source) as $field) {
                $fieldSql = $this->metadata->fieldSql($source, $field, $alias);
                if ($fieldSql === null) {
                    throw new AppError('dsl_compile', 'Unknown field', ['source' => $source, 'field' => $field]);
                }
                $parts[] = $fieldSql . ' AS `' . $source . '.' . $field . '`';
            }
        }
        if ($parts === []) {
            throw new AppError('dsl_compile', 'No fields available for selected sources');
        }
        return implode(', ', $parts);
    }

    /** @param list<string> $priorSources @return array{left:string,path:?array{left:string,right:string}} */
    private function findPriorJoinPath(array $priorSources, string $right): array
    {
        for ($i = count($priorSources) - 1; $i >= 0; $i--) {
            $left = $priorSources[$i];
            $path = $this->metadata->joinPath($left, $right);
            if ($path !== null) {
                return ['left' => $left, 'path' => $path];
            }
        }
        return ['left' => '', 'path' => null];
    }

    private function isNormalizedJoin(string $left, string $right): bool
    {
        return ($left === 'ads' && $right === 'advertisers') || ($left === 'advertisers' && $right === 'ads');
    }

    /** @param list<string> $sources @return list<string> */
    private function compileAutomaticSourcePredicates(array $sources): array
    {
        $predicates = [];
        foreach ($sources as $source) {
            if (!in_array($source, ['accounts', 'contacts'], true)) {
                continue;
            }
            $deletedField = $this->findSourceField($source, 'is_deleted');
            if ($deletedField === null) {
                continue;
            }
            $deletedSql = $this->metadata->fieldSql($source, $deletedField, $this->alias($source));
            if ($deletedSql === null) {
                continue;
            }
            $predicates[] = '(' . $deletedSql . ' IS NULL OR ' . $deletedSql . ' + 0 = 0)';
        }
        return $predicates;
    }

    private function findSourceField(string $source, string $needle): ?string
    {
        foreach ($this->metadata->sourceFields($source) as $field) {
            if (strcasecmp($field, $needle) === 0) {
                return $field;
            }
        }
        return null;
    }

    private function canAppendIssueContext(string $rightSource, bool $hasIssueContextFilter): bool
    {
        return $rightSource === 'issues' && $hasIssueContextFilter;
    }

    /** @param list<array<string,mixed>> $predicates */
    private function hasConstrainingIssueFilter(array $predicates, bool $negated = false): bool
    {
        foreach ($predicates as $predicate) {
            $predicateNegated = $negated || !empty($predicate['not']);
            if (($predicate['type'] ?? '') === 'filter'
                && ($predicate['name'] ?? '') === 'issue'
                && !$predicateNegated
            ) {
                return true;
            }
            if (($predicate['type'] ?? '') === 'group'
                && $this->hasConstrainingIssueFilter(is_array($predicate['items'] ?? null) ? $predicate['items'] : [], $predicateNegated)
            ) {
                return true;
            }
        }
        return false;
    }

    /** @param list<mixed> $args */
    private function singleNumericFilterArg(string $name, array $args): int
    {
        $values = $this->numericFilterArgs($name, $args);
        if (count($values) !== 1) {
            throw new AppError('dsl_compile', sprintf('Filter %s requires exactly one numeric issue ID', $name));
        }
        return $values[0];
    }

    /** @param list<mixed> $args @return list<int> */
    private function numericFilterArgs(string $name, array $args): array
    {
        if ($args === []) {
            throw new AppError('dsl_compile', sprintf('Filter %s requires numeric arguments', $name));
        }
        $out = [];
        foreach ($args as $arg) {
            if (!is_int($arg) && !(is_string($arg) && ctype_digit($arg))) {
                throw new AppError('dsl_compile', sprintf('Filter %s accepts numeric arguments only', $name));
            }
            $out[] = (int) $arg;
        }
        return $out;
    }

    /** @param list<mixed> &$params @param list<string> $sources */
    private function compileIssueFilter(int $issueId, array &$params, array $sources): string
    {
        $fenAds = $this->quoteSource('ads');
        $fenPages = $this->quoteSource('pages');
        $params[] = $issueId;
        if (in_array('pages', $sources, true)) {
            return $this->alias('pages') . '.`Issue` = ?';
        }
        if (in_array('issues', $sources, true)) {
            return $this->alias('issues') . '.`ID` = ?';
        }
        if (in_array('articles', $sources, true)) {
            return 'EXISTS (SELECT 1 FROM ' . $fenPages . ' AS p_article_issue WHERE p_article_issue.`ID` = ' . $this->alias('articles') . '.`PageID` AND p_article_issue.`Issue` = ?)';
        }
        if (in_array('ads', $sources, true)) {
            return 'EXISTS (SELECT 1 FROM ' . $fenPages . ' AS p_ad_issue WHERE p_ad_issue.`ID` = ' . $this->alias('ads') . '.`PageID` AND p_ad_issue.`Issue` = ?)';
        }
        if (in_array('advertisers', $sources, true)) {
            return 'EXISTS (SELECT 1 FROM ' . $fenAds . ' AS ad_adv_issue INNER JOIN ' . $fenPages . ' AS p_adv_issue ON p_adv_issue.`ID` = ad_adv_issue.`PageID` WHERE ' . $this->normalizedTextSql('ad_adv_issue.`Advertiser`') . ' = ' . $this->normalizedTextSql($this->alias('advertisers') . '.`AdvertiserName`') . ' AND p_adv_issue.`Issue` = ?)';
        }
        return $this->alias('invoices') . '.`issue_id` = ?';
    }

    /** @param list<mixed> &$params */
    private function compileAccountHasArticleInIssueFilter(int $issueId, array &$params): string
    {
        $accountNameField = $this->findSourceField('accounts', 'name');
        $accountNameSql = $accountNameField === null ? null : $this->metadata->fieldSql('accounts', $accountNameField, $this->alias('accounts'));
        if ($accountNameSql === null) {
            throw new AppError('dsl_compile', 'Unknown field', ['source' => 'accounts', 'field' => 'name']);
        }

        $params[] = $issueId;
        return 'EXISTS (SELECT 1 FROM ' . $this->quoteSource('articles') . ' AS article_account_issue'
            . ' INNER JOIN ' . $this->quoteSource('pages') . ' AS page_account_issue ON page_account_issue.`ID` = article_account_issue.`PageID`'
            . ' WHERE ' . $this->normalizedTextSql('article_account_issue.`MemberName`') . ' = ' . $this->normalizedTextSql($accountNameSql)
            . ' AND page_account_issue.`Issue` = ?)';
    }

    private function normalizedTextSql(string $sql): string
    {
        return 'LOWER(TRIM(CONVERT(COALESCE(' . $sql . ', \'\') USING utf8mb4) COLLATE utf8mb4_unicode_ci))';
    }
}
