<?php

declare(strict_types=1);

namespace App\Controllers;

use App\Core\Auth;
use App\Core\Database;
use App\Core\Request;
use App\Core\Response;
use App\Core\UrlHelper;
use PDO;

final class CompatController
{
    private array $tables = [
        'profiles','user_roles','municipios','usuario_municipios','branding_municipio','auditoria','sessoes_login','tentativas_login',
        'grupos_trabalho','secretarias','departamentos','setores','modulos','municipio_modulos','permissoes','role_permissoes',
        'perfis_customizados','perfil_customizado_permissoes','perfil_membros','ctm_lotes',
        'modules','permissions','roles','groups','group_permissions','memberships','municipalities','branding_settings','system_settings','refresh_sessions'
    ];

    private array $publicRead = ['municipios','branding_municipio','modulos'];
    private array $publicWrite = ['tentativas_login','sessoes_login'];

    public function select(Request $request, array $params): void
    {
        $table = $this->table($params['table'] ?? '');
        if (!$table) return;

        if (!in_array($table, $this->publicRead, true)) {
            Auth::requireUser($request);
        }

        $select = $request->header('x-compat-select', '*') ?: '*';
        $filters = json_decode($request->header('x-compat-filters', '[]') ?: '[]', true) ?: [];
        $order = json_decode($request->header('x-compat-order', 'null') ?: 'null', true) ?: [];
        $limit = $request->header('x-compat-limit');
        $range = $request->header('x-compat-range');
        $head = strtolower($request->header('x-compat-head', 'false') ?: 'false') === 'true';
        $countMode = strtolower((string)($request->header('x-compat-count', '') ?: ''));

        [$where, $bind] = $this->where($table, $filters);
        $total = null;
        if ($head || in_array($countMode, ['exact', 'planned', 'estimated'], true)) {
            $countSql = 'SELECT count(*) AS total FROM ' . $this->qi($table) . $where;
            $total = (int)(Database::fetchOne($countSql, $bind)['total'] ?? 0);
        }

        $columns = $this->selectColumns($table, $select);
        $sql = 'SELECT ' . $columns . ' FROM ' . $this->qi($table) . $where;
        $sql .= $this->orderBy($table, $order);

        if ($range && preg_match('/^(\d+)-(\d+)$/', $range, $m)) {
            $from = (int)$m[1];
            $to = (int)$m[2];
            $sql .= ' LIMIT ' . max(0, $to - $from + 1) . ' OFFSET ' . max(0, $from);
        } elseif ($limit !== null && $limit !== '') {
            $sql .= ' LIMIT ' . max(0, (int)$limit);
        }

        if ($head) {
            Response::json(['data' => null, 'count' => $total ?? 0]);
            return;
        }

        $rows = Database::fetchAll($sql, $this->normalizeParams($bind));
        $rows = $this->normalizeRows($rows);
        $rows = $this->hydrateRelations($table, $select, $rows);
        Response::json(['data' => $rows, 'count' => $total]);
    }

    public function insert(Request $request, array $params): void
    {
        $table = $this->table($params['table'] ?? '');
        if (!$table) return;
        if (!in_array($table, $this->publicWrite, true)) {
            Auth::requireUser($request);
        }

        $payload = $request->body['payload'] ?? [];
        $items = array_is_list($payload) ? $payload : [$payload];
        $inserted = [];
        foreach ($items as $item) {
            if (!is_array($item)) continue;
            $data = $this->clean($table, $item);
            if (!isset($data['id'])) $data['id'] = $this->uuid();
            if ($this->hasColumn($table, 'created_at') && !isset($data['created_at'])) $data['created_at'] = date('Y-m-d H:i:sP');
            if ($this->hasColumn($table, 'updated_at') && !isset($data['updated_at'])) $data['updated_at'] = date('Y-m-d H:i:sP');
            $cols = array_keys($data);
            $ph = array_map(fn($c) => ':' . $c, $cols);
            $sql = 'INSERT INTO ' . $this->qi($table) . ' (' . implode(',', array_map([$this,'qi'], $cols)) . ') VALUES (' . implode(',', $ph) . ') RETURNING *';
            $stmt = Database::pdo()->prepare($sql);
            $stmt->execute($this->normalizeParams($data));
            $inserted[] = $stmt->fetch(PDO::FETCH_ASSOC);
        }
        Response::json(['data' => $this->normalizeRows($inserted)]);
    }

    public function update(Request $request, array $params): void
    {
        $table = $this->table($params['table'] ?? '');
        if (!$table) return;
        Auth::requireUser($request);

        $payload = $request->body['payload'] ?? [];
        $filters = $request->body['filters'] ?? [];
        $data = $this->clean($table, is_array($payload) ? $payload : []);
        unset($data['id'], $data['created_at']);
        if ($this->hasColumn($table, 'updated_at')) $data['updated_at'] = date('Y-m-d H:i:sP');
        if (!$data) {
            Response::json(['data' => []]); return;
        }
        [$where, $bind] = $this->where($table, is_array($filters) ? $filters : []);
        if (!$where) {
            Response::error('Atualização sem filtro não permitida', 400); return;
        }
        $sets = [];
        foreach ($data as $col => $val) {
            $sets[] = $this->qi($col) . ' = :set_' . $col;
            $bind['set_' . $col] = $val;
        }
        $sql = 'UPDATE ' . $this->qi($table) . ' SET ' . implode(', ', $sets) . $where . ' RETURNING *';
        $stmt = Database::pdo()->prepare($sql);
        $stmt->execute($this->normalizeParams($bind));
        Response::json(['data' => $this->normalizeRows($stmt->fetchAll(PDO::FETCH_ASSOC))]);
    }

    public function delete(Request $request, array $params): void
    {
        $table = $this->table($params['table'] ?? '');
        if (!$table) return;
        Auth::requireUser($request);
        $filters = $request->body['filters'] ?? [];
        [$where, $bind] = $this->where($table, is_array($filters) ? $filters : []);
        if (!$where) {
            Response::error('Exclusão sem filtro não permitida', 400); return;
        }
        $sql = 'DELETE FROM ' . $this->qi($table) . $where . ' RETURNING *';
        $stmt = Database::pdo()->prepare($sql);
        $stmt->execute($this->normalizeParams($bind));
        Response::json(['data' => $this->normalizeRows($stmt->fetchAll(PDO::FETCH_ASSOC))]);
    }

    private function table(string $table): ?string
    {
        if (!in_array($table, $this->tables, true)) {
            Response::error('Tabela não permitida: ' . $table, 404);
            return null;
        }
        return $table;
    }

    private function where(string $table, array $filters): array
    {
        $clauses = [];
        $bind = [];
        $i = 0;
        foreach ($filters as $filter) {
            if (!is_array($filter)) continue;
            $field = (string)($filter['field'] ?? '');
            $op = (string)($filter['op'] ?? 'eq');
            $value = $filter['value'] ?? null;
            if (!$this->hasColumn($table, $field)) continue;
            $param = 'w_' . preg_replace('/\W+/', '_', $field) . '_' . $i++;
            $col = $this->qi($field);
            switch ($op) {
                case 'neq': $clauses[] = "$col <> :$param"; $bind[$param] = $value; break;
                case 'gt': $clauses[] = "$col > :$param"; $bind[$param] = $value; break;
                case 'gte': $clauses[] = "$col >= :$param"; $bind[$param] = $value; break;
                case 'lt': $clauses[] = "$col < :$param"; $bind[$param] = $value; break;
                case 'lte': $clauses[] = "$col <= :$param"; $bind[$param] = $value; break;
                case 'like': $clauses[] = "$col LIKE :$param"; $bind[$param] = $value; break;
                case 'not.is':
                    if ($value === null || strtolower((string)$value) === 'null') $clauses[] = "$col IS NOT NULL";
                    break;
                case 'ilike': $clauses[] = "$col ILIKE :$param"; $bind[$param] = $value; break;
                case 'is':
                    if ($value === null || strtolower((string)$value) === 'null') $clauses[] = "$col IS NULL";
                    elseif ($value === true || $value === 'true') $clauses[] = "$col IS TRUE";
                    elseif ($value === false || $value === 'false') $clauses[] = "$col IS FALSE";
                    break;
                case 'in':
                    $vals = is_array($value) ? $value : [];
                    if (!$vals) break;
                    $in = [];
                    foreach ($vals as $k => $v) { $p = $param . '_' . $k; $in[] = ':' . $p; $bind[$p] = $v; }
                    $clauses[] = "$col IN (" . implode(',', $in) . ')';
                    break;
                case 'eq':
                default: $clauses[] = "$col = :$param"; $bind[$param] = $value; break;
            }
        }
        return [$clauses ? ' WHERE ' . implode(' AND ', $clauses) : '', $bind];
    }

    private function orderBy(string $table, array $order): string
    {
        $parts = [];
        foreach ($order as $item) {
            $field = $item['field'] ?? '';
            if (!$this->hasColumn($table, (string)$field)) continue;
            $parts[] = $this->qi((string)$field) . ((($item['ascending'] ?? true) === false) ? ' DESC' : ' ASC');
        }
        return $parts ? ' ORDER BY ' . implode(', ', $parts) : '';
    }

    private function selectColumns(string $table, string $select): string
    {
        if ($select === '*' || trim($select) === '') return '*';
        // Relações Supabase, ex: municipios(*), são ignoradas nesta primeira base. Mantém somente colunas reais.
        $cols = [];
        foreach ($this->splitSelect($select) as $raw) {
            $col = trim(preg_replace('/\(.+\)/', '', $raw));
            $col = trim(explode(':', $col)[0] ?? $col);
            if ($this->hasColumn($table, $col)) $cols[] = $this->qi($col);
        }
        return $cols ? implode(', ', array_unique($cols)) : '*';
    }

    private function clean(string $table, array $data): array
    {
        $cols = $this->columns($table);
        $clean = array_intersect_key($data, array_flip($cols));

        // Identidade Visual: grava apenas o caminho relativo limpo no banco.
        // Ex.: uploads/identidade_visual/{municipio_id}/logo.png
        if ($table === 'branding_municipio') {
            foreach (['logo_url','brasao_url','favicon_url','login_bg_url','login_background_url'] as $assetCol) {
                if (array_key_exists($assetCol, $clean)) {
                    $clean[$assetCol] = $this->cleanBrandingAssetPath(
                        is_string($clean[$assetCol]) ? $clean[$assetCol] : null
                    );
                }
            }
        }

        return $clean;
    }

    private function cleanBrandingAssetPath(?string $value): ?string
    {
        if ($value === null) return null;
        $raw = trim($value);
        if ($raw === '') return null;
        if (str_starts_with($raw, 'data:') || str_starts_with($raw, 'blob:')) return $raw;

        if (preg_match('#^https?://#i', $raw)) {
            $parts = parse_url($raw);
            $raw = (string)($parts['path'] ?? '');
        }

        $raw = preg_replace('/([?&])(v|t|cache)=\d+(&)?/i', '$1', $raw) ?? $raw;
        $raw = rtrim($raw, '?&');
        $raw = ltrim(str_replace('\\', '/', $raw), '/');

        foreach (['api/v1/storage/public/', 'storage/uploads/'] as $prefix) {
            if (str_starts_with($raw, $prefix)) {
                $raw = substr($raw, strlen($prefix));
            }
        }

        if (str_starts_with($raw, 'branding-assets/')) {
            $raw = 'uploads/identidade_visual/' . substr($raw, strlen('branding-assets/'));
        }
        if (str_starts_with($raw, 'identidade_visual/')) {
            $raw = 'uploads/' . $raw;
        }

        if ($raw !== '' && !str_starts_with($raw, 'uploads/identidade_visual/')) {
            if (preg_match('#^[a-zA-Z0-9._-]+/(logo|brasao|favicon|login-bg)[^/]*\.[a-z0-9]+$#i', $raw)) {
                $raw = 'uploads/identidade_visual/' . $raw;
            }
        }

        return $raw !== '' ? $raw : null;
    }

    private function columns(string $table): array
    {
        return Database::tableColumns($table);
    }

    private function hasColumn(string $table, string $column): bool
    {
        return $column !== '' && in_array($column, $this->columns($table), true);
    }

    private function normalizeRows(array $rows): array
    {
        return array_map(function ($row) {
            foreach ($row as $k => $v) {
                if (is_string($v) && in_array(strtolower($v), ['t','f'], true)) {
                    $row[$k] = strtolower($v) === 't';
                }
                if (in_array($k, ['logo_url','brasao_url','favicon_url','login_bg_url','login_background_url'], true)) {
                    $row[$k] = UrlHelper::normalizeAssetUrl(is_string($v) ? $v : null);
                }
            }
            return $row;
        }, $rows);
    }

    /**
     * Suporte básico ao select relacional do Supabase/PostgREST, usado pelo frontend:
     * municipio_id, ativo, municipios:municipios(id, nome, uf, bloqueado)
     */
    private function hydrateRelations(string $table, string $select, array $rows): array
    {
        if (!$rows) return $rows;
        $relations = $this->parseRelations($select);
        if (!$relations) return $rows;

        foreach ($relations as $rel) {
            $target = $rel['table'];
            if (!$this->hasAllowedTable($target)) continue;
            if (!$this->hasTable($target)) continue;

            $fk = $this->relationFk($table, $target);
            if (!$fk || !$this->hasColumn($table, $fk)) continue;

            $ids = [];
            foreach ($rows as $row) {
                if (!empty($row[$fk])) $ids[] = (string)$row[$fk];
            }
            $ids = array_values(array_unique($ids));
            if (!$ids) continue;

            $params = [];
            $placeholders = [];
            foreach ($ids as $i => $id) {
                $key = 'rel_' . $i;
                $params[$key] = $id;
                $placeholders[] = ':' . $key;
            }
            $cols = $this->relationColumns($target, $rel['columns']);
            if (!in_array('id', $cols, true) && $this->hasColumn($target, 'id')) $cols[] = 'id';
            $sql = 'SELECT ' . implode(', ', array_map([$this, 'qi'], $cols)) . ' FROM ' . $this->qi($target) . ' WHERE id IN (' . implode(',', $placeholders) . ')';
            $related = Database::fetchAll($sql, $params);
            $map = [];
            foreach ($this->normalizeRows($related) as $item) {
                if (isset($item['id'])) $map[(string)$item['id']] = $item;
            }
            foreach ($rows as &$row) {
                $row[$rel['alias']] = isset($row[$fk]) ? ($map[(string)$row[$fk]] ?? null) : null;
            }
            unset($row);
        }
        return $rows;
    }

    private function parseRelations(string $select): array
    {
        $out = [];
        foreach ($this->splitSelect($select) as $part) {
            $part = trim($part);
            if (!str_contains($part, '(') || !str_contains($part, ')')) continue;
            if (!preg_match('/^(?:(?<alias>[a-zA-Z_][\w]*)\s*:\s*)?(?<table>[a-zA-Z_][\w]*)\s*\((?<cols>.*)\)$/', $part, $m)) continue;
            $table = $m['table'];
            $alias = $m['alias'] ?: $table;
            $cols = array_values(array_filter(array_map('trim', $this->splitSelect($m['cols'] ?? '*'))));
            $out[] = ['alias' => $alias, 'table' => $table, 'columns' => $cols ?: ['*']];
        }
        return $out;
    }

    private function splitSelect(string $select): array
    {
        $parts = [];
        $current = '';
        $depth = 0;
        $len = strlen($select);
        for ($i = 0; $i < $len; $i++) {
            $ch = $select[$i];
            if ($ch === '(') $depth++;
            if ($ch === ')') $depth = max(0, $depth - 1);
            if ($ch === ',' && $depth === 0) {
                $parts[] = $current;
                $current = '';
                continue;
            }
            $current .= $ch;
        }
        if (trim($current) !== '') $parts[] = $current;
        return $parts;
    }

    private function relationColumns(string $table, array $requested): array
    {
        if ($requested === ['*']) return $this->columns($table);
        $cols = [];
        foreach ($requested as $raw) {
            $col = trim((string)$raw);
            if ($col === '*') return $this->columns($table);
            $col = trim(preg_replace('/\(.+\)/', '', $col));
            $col = trim(explode(':', $col)[0] ?? $col);
            if ($this->hasColumn($table, $col)) $cols[] = $col;
        }
        return $cols ?: $this->columns($table);
    }

    private function relationFk(string $sourceTable, string $targetTable): ?string
    {
        $map = [
            'usuario_municipios:municipios' => 'municipio_id',
            'profiles:municipios' => 'municipio_id',
            'departamentos:secretarias' => 'secretaria_id',
            'setores:departamentos' => 'departamento_id',
            'grupos_trabalho:setores' => 'setor_id',
            'municipio_modulos:municipios' => 'municipio_id',
            'municipio_modulos:modulos' => 'modulo_id',
            'permissoes:modulos' => 'modulo_id',
            'role_permissoes:permissoes' => 'permissao_id',
            'perfil_customizado_permissoes:permissoes' => 'permissao_id',
            'perfil_customizado_permissoes:perfis_customizados' => 'perfil_id',
        ];
        $key = $sourceTable . ':' . $targetTable;
        if (isset($map[$key])) return $map[$key];
        $guess = preg_replace('/s$/', '', $targetTable) . '_id';
        return $this->hasColumn($sourceTable, $guess) ? $guess : null;
    }

    private function hasTable(string $table): bool
    {
        return Database::tableExists($table);
    }

    private function hasAllowedTable(string $table): bool
    {
        return in_array($table, $this->tables, true);
    }

    private function normalizeParams(array $params): array
    {
        foreach ($params as $key => $value) {
            if (is_bool($value)) {
                $params[$key] = $value ? 'true' : 'false';
            } elseif ($value === '') {
                // Mantém string vazia em campos texto, mas evita conversão acidental para booleano vazio em filtros/payloads.
                $params[$key] = '';
            }
        }
        return $params;
    }

    private function qi(string $id): string
    {
        return '"' . str_replace('"', '""', $id) . '"';
    }

    private function uuid(): string
    {
        $data = random_bytes(16);
        $data[6] = chr((ord($data[6]) & 0x0f) | 0x40);
        $data[8] = chr((ord($data[8]) & 0x3f) | 0x80);
        return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4));
    }
}
