<?php

namespace App\Services\Reports;

use App\Services\Reports\Exceptions\InvalidIrException;
use App\Support\FiscalYear;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;

class ReportCompiler
{
    private const ALLOWED_AGGREGATES = ['SUM', 'COUNT', 'AVG', 'MIN', 'MAX'];

    public function __construct(protected SchemaIntrospector $schema) {}

    /** @throws InvalidIrException */
    public function compile(array $ir): Builder
    {
        $this->validate($ir);

        $first = $ir['tables'][0];
        $query = DB::table("{$first['name']} as {$first['alias']}");

        foreach ($ir['joins'] ?? [] as $j) {
            $rightTable = $this->resolveTableByAlias($ir, $j['right']['table']);
            $method = $j['type'] === 'LEFT' ? 'leftJoin' : 'join';
            $query->{$method}("{$rightTable['name']} as {$rightTable['alias']}", function ($join) use ($j) {
                $join->on(
                    "{$j['left']['table']}.{$j['left']['column']}",
                    '=',
                    "{$j['right']['table']}.{$j['right']['column']}"
                );
            });
        }

        $grammar = $query->getGrammar();
        $selects = [];
        foreach ($ir['columns'] as $c) {
            $ref = "{$c['table']}.{$c['column']}";
            $alias = preg_replace('/[^a-zA-Z0-9_]/', '_', $c['label'] ?? "{$c['table']}_{$c['column']}");
            if (! empty($c['aggregate'])) {
                $agg = strtoupper($c['aggregate']);
                $selects[] = DB::raw($agg.'('.$grammar->wrap($ref).') as '.$grammar->wrap($alias));
            } else {
                $selects[] = "{$ref} as {$alias}";
            }
        }
        $query->select($selects);

        foreach ($ir['filters'] ?? [] as $f) {
            $col = "{$f['table']}.{$f['column']}";
            $boolean = (($f['logic'] ?? 'AND') === 'OR') ? 'or' : 'and';
            $this->applyFilter($query, $col, $f, $boolean);
        }

        foreach ($ir['group_by'] ?? [] as $gb) {
            $query->groupBy("{$gb['table']}.{$gb['column']}");
        }

        foreach ($ir['sort'] ?? [] as $s) {
            $col = "{$s['table']}.{$s['column']}";
            $direction = ($s['direction'] ?? 'desc') === 'asc' ? 'asc' : 'desc';
            if (! empty($s['aggregate'])) {
                $agg = strtoupper($s['aggregate']);
                $query->orderByRaw("{$agg}({$col}) {$direction}");
            } else {
                $query->orderBy($col, $direction);
            }
        }

        $limit = min((int) ($ir['limit'] ?? config('reports.max_result_rows')), config('reports.max_result_rows'));
        $query->limit($limit);

        return $query;
    }

    protected function applyFilter(Builder $q, string $col, array $f, string $boolean): void
    {
        $op = strtoupper($f['op']);
        $val = $f['value'] ?? null;

        match (true) {
            $op === '='     => $q->where($col, '=', $val, $boolean),
            $op === '!='    => $q->where($col, '!=', $val, $boolean),
            $op === '<'     => $q->where($col, '<', $val, $boolean),
            $op === '<='    => $q->where($col, '<=', $val, $boolean),
            $op === '>'     => $q->where($col, '>', $val, $boolean),
            $op === '>='    => $q->where($col, '>=', $val, $boolean),
            $op === 'LIKE'  => $q->where($col, 'like', "%{$val}%", $boolean),
            $op === 'NOT_LIKE' => $q->where($col, 'not like', "%{$val}%", $boolean),
            $op === 'IN'    => $q->whereIn($col, (array) $val, $boolean),
            $op === 'NOT_IN' => $q->whereNotIn($col, (array) $val, $boolean),
            $op === 'BETWEEN' => $q->whereBetween($col, [$val[0], $val[1]], $boolean),
            $op === 'IS_NULL' => $q->whereNull($col, $boolean),
            $op === 'IS_NOT_NULL' => $q->whereNotNull($col, $boolean),
            $op === 'LAST_N_DAYS' => $q->where($col, '>=', now()->subDays((int) $val), $boolean),
            $op === 'THIS_MONTH'  => $q->whereBetween($col, [now()->startOfMonth(), now()->endOfMonth()], $boolean),
            $op === 'THIS_YEAR'   => $q->whereBetween($col, [now()->startOfYear(), now()->endOfYear()], $boolean),
            // PRC-825 — mali yıl presetleri (THIS_YEAR takvim yılı olarak DEĞİŞMEZ).
            $op === 'THIS_FISCAL_YEAR' => $q->whereBetween($col, [FiscalYear::current()['start'], FiscalYear::current()['end']], $boolean),
            $op === 'LAST_FISCAL_YEAR' => $q->whereBetween($col, [FiscalYear::previous()['start'], FiscalYear::previous()['end']], $boolean),
            $op === 'BETWEEN_DATES' => $q->whereBetween($col, [$val[0], $val[1]], $boolean),
            default => throw new InvalidIrException("Unknown filter op: {$op}"),
        };
    }

    protected function validate(array $ir): void
    {
        if (empty($ir['tables']))   throw new InvalidIrException('At least one table required');
        if (empty($ir['columns']))  throw new InvalidIrException('At least one column required');
        if (count($ir['joins'] ?? []) > config('reports.max_joins'))
            throw new InvalidIrException('Too many joins (max '.config('reports.max_joins').')');
        if (count($ir['columns']) > config('reports.max_columns'))
            throw new InvalidIrException('Too many columns (max '.config('reports.max_columns').')');

        $aliasToName = [];
        foreach ($ir['tables'] as $t) {
            if (! preg_match('/^[a-zA-Z_][a-zA-Z0-9_]{0,31}$/', $t['alias'] ?? '')) {
                throw new InvalidIrException("Invalid table alias: '{$t['alias']}'. Must match /^[a-zA-Z_][a-zA-Z0-9_]{0,31}\$/");
            }
            if (! $this->schema->tableExists($t['name'])) {
                throw new InvalidIrException("Table '{$t['name']}' is not available (blacklisted or doesn't exist)");
            }
            $aliasToName[$t['alias']] = $t['name'];
        }

        foreach ($ir['columns'] as $c) {
            if (! empty($c['aggregate']) && ! in_array(strtoupper($c['aggregate']), self::ALLOWED_AGGREGATES, true)) {
                throw new InvalidIrException("Invalid aggregate function: '{$c['aggregate']}'. Allowed: " . implode(', ', self::ALLOWED_AGGREGATES));
            }
        }
        foreach (($ir['sort'] ?? []) as $s) {
            if (! empty($s['aggregate']) && ! in_array(strtoupper($s['aggregate']), self::ALLOWED_AGGREGATES, true)) {
                throw new InvalidIrException("Invalid sort aggregate: '{$s['aggregate']}'. Allowed: " . implode(', ', self::ALLOWED_AGGREGATES));
            }
        }

        $refs = [];
        foreach ($ir['columns']        as $c) $refs[] = [$c['table'], $c['column']];
        foreach (($ir['filters'] ?? [])  as $f) $refs[] = [$f['table'], $f['column']];
        foreach (($ir['sort'] ?? [])     as $s) $refs[] = [$s['table'], $s['column']];
        foreach (($ir['group_by'] ?? []) as $g) $refs[] = [$g['table'], $g['column']];
        foreach (($ir['joins'] ?? [])    as $j) {
            $refs[] = [$j['left']['table'], $j['left']['column']];
            $refs[] = [$j['right']['table'], $j['right']['column']];
        }
        foreach ($refs as [$alias, $col]) {
            if (! isset($aliasToName[$alias])) {
                throw new InvalidIrException("Unknown table alias: {$alias}");
            }
            if (! $this->schema->columnExists($aliasToName[$alias], $col)) {
                throw new InvalidIrException("Column '{$alias}.{$col}' is not available (blacklisted or removed)");
            }
        }

        $hasAggregate = collect($ir['columns'])->contains(fn ($c) => ! empty($c['aggregate']));
        if ($hasAggregate) {
            $groupedKeys = collect($ir['group_by'] ?? [])->map(fn ($g) => "{$g['table']}.{$g['column']}")->all();
            foreach ($ir['columns'] as $c) {
                if (empty($c['aggregate'])) {
                    $key = "{$c['table']}.{$c['column']}";
                    if (! in_array($key, $groupedKeys, true)) {
                        throw new InvalidIrException("Non-aggregated column '{$key}' must be in GROUP BY");
                    }
                }
            }
        }

        if (! empty($ir['display']) && in_array($ir['display']['type'], ['bar', 'line', 'pie'])) {
            if (empty($ir['display']['category_column']) || empty($ir['display']['value_column'])) {
                throw new InvalidIrException('Chart requires category_column and value_column');
            }
        }
    }

    protected function resolveTableByAlias(array $ir, string $alias): array
    {
        foreach ($ir['tables'] as $t) {
            if ($t['alias'] === $alias) return $t;
        }
        throw new InvalidIrException("Unknown table alias in JOIN: {$alias}");
    }
}
