<?php
namespace App\Core;

use PDO;
use PDOException;

class Orm
{
    private static $conexaoORM = null;

    protected $tabela;
    protected $dados = array();
    protected $chave_primaria;
    protected $join = array();
    protected $tipo_join = 'LEFT';
    protected $distinct = false;
    protected $order_by;
    protected $group_by = null;
    protected $limite;
    protected $and = array();
    protected $andRaw = array();
    protected $bindings = array();
    protected $extra_sql;
    protected $colunas = array();
    protected $debug_sql = false;
    public $last_id;
    protected $log_metrics = false;

    // --- Timestamps ---
    protected $timestamps = true;
    protected $created_at_column = 'created_at';
    protected $updated_at_column = 'updated_at';

    public function __construct()
    {
        $this->connect();
    }



    // --- Métodos Mágicos ---
    public function __set($nome, $valor)
    {
        $this->dados[$nome] = $valor;
    }
    public function __get($nome)
    {
        return $this->dados[$nome] ?? null;
    }
    public function __isset($nome)
    {
        return isset($this->dados[$nome]);
    }
    public function __unset($nome)
    {
        unset($this->dados[$nome]);
    }

    /**
     * Popula os dados do modelo a partir de um array.
     * @param array $data
     * @return $this
     */
    public function dados(array $data)
    {
        $this->dados = array_merge($this->dados, $data);
        return $this;
    }

    // --- Configurações de Query (Fluent Interface) ---
    public function columns($columns)
    {
        $this->colunas = is_array($columns) ? $columns : [$columns];
        return $this;
    }
    public function debug($bool = true)
    {
        $this->debug_sql = $bool;
        return $this;
    }
    public function limit($limit)
    {
        $this->limite = $limit;
        return $this;
    }
    public function orderBy($order)
    {
        $this->order_by = $order;
        return $this;
    }

    public function groupBy($group)
    {
        $this->group_by = $group;
        return $this;
    }

    public function logMetrics($log = true)
    {
        $this->log_metrics = $log;
        return $this;
    }

    // --- Aliases ---
    public function colunas($c)
    {
        return $this->columns($c);
    }
    public function limite($l)
    {
        return $this->limit($l);
    }

    private function connect()
    {
        if (is_null(self::$conexaoORM)) {
            $envPath = __DIR__ . '/../../.env';
            if (file_exists($envPath)) {
                $lines = file($envPath, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
                foreach ($lines as $line) {
                    if (strpos(trim($line), '#') === 0)
                        continue;
                    list($name, $value) = explode('=', $line, 2);
                    $name = trim($name);
                    $value = trim($value);
                    if (!array_key_exists($name, $_SERVER) && !array_key_exists($name, $_ENV)) {
                        putenv(sprintf('%s=%s', $name, $value));
                        $_ENV[$name] = $value;
                        $_SERVER[$name] = $value;
                    }
                }
            }

            $driver = getenv('DB_DRIVER') ?: 'mysql';
            $host = getenv('DB_HOST') ?: 'localhost';
            $db_name = getenv('DB_NAME') ?: 'meu_banco';
            $username = getenv('DB_USER') ?: 'root';
            $password = getenv('DB_PASS') ?: '';
            $dsn = "{$driver}:host={$host};dbname={$db_name};charset=utf8mb4";

            try {
                self::$conexaoORM = new PDO($dsn, $username, $password, [
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4",
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
                ]);
            } catch (PDOException $e) {
                die("Erro de Conexão: " . $e->getMessage());
            }
        }
    }

    protected function queryOrm($sql, $params, $fetchSingle)
    {
        if ($this->log_metrics)
            $start = microtime(true);

        if ($this->debug_sql)
            $this->dump($sql, $params);
        try {
            $stmt = self::$conexaoORM->prepare($sql);
            $stmt->execute($params);

            if ($fetchSingle) {
                $result = $stmt->fetch();
            } else {
                $result = $stmt->fetchAll();
            }

            if ($this->log_metrics) {
                \App\Shared\Helpers\MetricCollector::add('db_query_time', microtime(true) - $start);
            }
            return $result;

        } catch (PDOException $e) {
            $originalMessage = $e->getMessage();
            $parsedMessage = \App\Shared\Helpers\SqlErrorParser::parse($originalMessage);
            throw new \Exception($parsedMessage . '||' . $originalMessage);
        }
    }

    protected function execute($sql, $params)
    {
        if ($this->log_metrics)
            $start = microtime(true);

        if ($this->debug_sql)
            $this->dump($sql, $params);
        try {
            $result = self::$conexaoORM->prepare($sql)->execute($params);

            if ($this->log_metrics) {
                \App\Shared\Helpers\MetricCollector::add('db_query_time', microtime(true) - $start);
            }
            return $result;
        } catch (PDOException $e) {
            $originalMessage = $e->getMessage();
            $parsedMessage = \App\Shared\Helpers\SqlErrorParser::parse($originalMessage);
            throw new \Exception($parsedMessage . '||' . $originalMessage);
        }
    }

    protected function executeAndGetRowCount($sql, $params)
    {
        if ($this->log_metrics)
            $start = microtime(true);

        if ($this->debug_sql)
            $this->dump($sql, $params);
        try {
            $stmt = self::$conexaoORM->prepare($sql);
            $stmt->execute($params);
            $rowCount = $stmt->rowCount();

            if ($this->log_metrics) {
                \App\Shared\Helpers\MetricCollector::add('db_query_time', microtime(true) - $start);
            }
            return $rowCount;
        } catch (PDOException $e) {
            $originalMessage = $e->getMessage();
            $parsedMessage = \App\Shared\Helpers\SqlErrorParser::parse($originalMessage);
            throw new \Exception($parsedMessage . '||' . $originalMessage);
        }
    }

    public function create($data = null)
    {
        if ($data)
            $this->dados = array_merge($this->dados, $data);

        if ($this->timestamps) {
            if ($this->created_at_column)
                $this->dados[$this->created_at_column] = date('Y-m-d H:i:s');
            if ($this->updated_at_column)
                $this->dados[$this->updated_at_column] = date('Y-m-d H:i:s');
        }

        $cols = implode(", ", array_keys($this->dados));
        $placeholders = ":" . implode(", :", array_keys($this->dados));
        $sql = "INSERT INTO {$this->tabela} ({$cols}) VALUES ({$placeholders})";
        $res = $this->execute($sql, $this->dados);
        if ($res) {
            $this->last_id = self::$conexaoORM->lastInsertId();
        }
        return $res;
    }

    public function update($data = null)
    {
        if ($data)
            $this->dados = array_merge($this->dados, $data);

        if ($this->timestamps && $this->updated_at_column) {
            $this->dados[$this->updated_at_column] = date('Y-m-d H:i:s');
        }

        $pk_value = $this->dados[$this->chave_primaria] ?? null;
        if (!$pk_value)
            return false;

        $temp_data = $this->dados;
        unset($temp_data[$this->chave_primaria]);

        $sets = [];
        foreach ($temp_data as $col => $val) {
            $sets[] = "$col = :$col";
        }

        $sql = "UPDATE {$this->tabela} SET " . implode(", ", $sets);
        $sql .= " WHERE {$this->chave_primaria} = :{$this->chave_primaria}";

        return $this->executeAndGetRowCount($sql, $this->dados);
    }

    public function save()
    {
        $pk_value = $this->dados[$this->chave_primaria] ?? null;
        if (!empty($pk_value)) {
            $found = $this->pk($pk_value)->find();
            if ($found) {
                return $this->update();
            }
        }
        return $this->create();
    }

    public function delete($pk = null)
    {
        if ($pk)
            $this->pk($pk);
        if (!isset($this->dados[$this->chave_primaria]))
            return false;

        $sql = "DELETE FROM {$this->tabela} WHERE {$this->chave_primaria} = :pk";
        return $this->executeAndGetRowCount($sql, ['pk' => $this->dados[$this->chave_primaria]]);
    }

    public function list()
    {
        $sql = $this->gerarSelect();
        $params = $this->gerarWhere($sql);
        if ($this->group_by)
            $sql .= " GROUP BY $this->group_by ";
        if ($this->order_by)
            $sql .= " ORDER BY " . $this->order_by;
        if ($this->limite)
            $sql .= " LIMIT {$this->limite} ";
        return $this->queryOrm($sql, $params, false);
    }

    public function find()
    {
        $this->limit(1);
        $sql = $this->gerarSelect();
        $params = $this->gerarWhere($sql);
        if ($this->order_by)
            $sql .= " ORDER BY " . $this->order_by;
        return $this->queryOrm($sql, $params, true);
    }

    public function count()
    {
        $sql = "SELECT COUNT(*) AS total FROM {$this->tabela} ";
        $params = $this->gerarWhere($sql);

        $result = $this->queryOrm($sql, $params, true);

        return $result ? (int) $result->total : 0;
    }

    public function pagination($page = 1, $per_page = 10)
    {
        // Clona o objeto ORM atual para executar uma query de contagem separada
        // sem afetar o estado da query principal (cláusulas where, etc.).
        $count_orm = clone $this;
        $total_records = $count_orm->count();

        // Calcula os detalhes da paginação
        $total_pages = ceil($total_records / $per_page);
        $offset = ($page > 0) ? ($page - 1) * $per_page : 0;

        // Define o limite e o offset para a query de dados principal
        $this->limit("{$offset}, {$per_page}");

        // Obtém os dados para a página atual
        $data = $this->list();

        // Retorna o objeto de paginação final
        return (object) [
            'data' => $data,
            'total_records' => (int) $total_records,
            'total_pages' => (int) $total_pages,
            'current_page' => (int) $page,
            'per_page' => (int) $per_page,
        ];
    }

    // --- WRAPPERS (Português) ---
    public function cadastrar($d = null)
    {
        return $this->create($d);
    }
    public function alterar($d = null)
    {
        return $this->update($d);
    }
    public function deletar($pk = null)
    {
        return $this->delete($pk);
    }
    public function listar()
    {
        return $this->list();
    }
    public function consultar()
    {
        return $this->find();
    }
    public function salvar()
    {
        return $this->save();
    }

    public function contar()
    {
        return $this->count();
    }

    public function paginacao($page = 1, $per_page = 10)
    {
        return $this->pagination($page, $per_page);
    }

    private function gerarSelect()
    {
        $colunas = (array) $this->colunas;
        $sql = $this->distinct ? "SELECT DISTINCT " : "SELECT ";
        $sql .= count($colunas) > 0 ? implode(', ', $colunas) : "{$this->tabela}.*";
        $sql .= " FROM {$this->tabela} ";
        $sql .= $this->gerarJoin();
        return $sql;
    }

    private function gerarJoin()
    {
        $sql = "";
        foreach ($this->join as $join) {
            $apelido = $join["apelido"] ?? $join["tabela"];
            $base = $join["tabelafk"] ?? $this->tabela;
            $sql .= "{$this->tipo_join} JOIN {$join["tabela"]} AS {$apelido} ON $base.{$join["fk"]} = $apelido.{$join["pk"]} ";
        }
        return $sql;
    }

    private function gerarWhere(&$sql)
    {
        $all_clauses = array_merge($this->and, $this->andRaw);

        if (count($all_clauses) > 0 || !is_null($this->extra_sql)) {
            $sql .= " WHERE ";
            $sql .= implode(" AND ", $all_clauses);
            if (!is_null($this->extra_sql)) {
                $sql .= (count($all_clauses) > 0 ? " AND " : "") . $this->extra_sql;
            }
        }

        $params = $this->bindings;
        // Reseta o estado da query para chamadas futuras na mesma instância
        $this->bindings = [];
        $this->and = [];
        $this->andRaw = [];

        return $params;
    }
    public function pk($pk = null)
    {
        if (is_null($pk))
            return $this->dados[$this->chave_primaria] ?? null;
        $this->dados[$this->chave_primaria] = $pk;
        $this->and($this->tabela . "." . $this->chave_primaria, $pk);
        return $this;
    }

    public function and($coluna, $valor, $operador = "=")
    {
        $param = str_replace(['.', ' '], '_', $coluna) . count($this->bindings);
        $this->and[] = "$coluna $operador :$param";
        $this->bindings[$param] = $valor;
        return $this;
    }

    public function whereRaw($sql_part)
    {
        $this->andRaw[] = $sql_part;
        return $this;
    }

    private function dump($sql, $params)
    {
        // Implement SqlFormatter if available and needed
        echo "<pre style='border:1px solid #ccc;padding:10px;background:#f9f9f9;font-family:monospace;'>";
        echo "<strong>SQL:</strong> $sql <br><strong>Params:</strong> " . json_encode($params);
        echo "</pre>";
    }


}
