<?php
declare(strict_types=1);

namespace App\Core;

use App\Config\Config;
use PDO;
use PDOException;

class DB
{
    private static ?PDO $pdo = null;

    public static function init(): void
    {
        if (self::$pdo) {
            return;
        }
        $cfg = Config::get('db');
        if ($cfg['driver'] === 'mysql') {
            $dsn = 'mysql:host=' . $cfg['host'] . ';port=' . $cfg['port'] . ';dbname=' . $cfg['database'] . ';charset=' . $cfg['charset'];
        } else {
            $dbPath = Config::get('storage_path') . '/app.sqlite';
            $dsn = 'sqlite:' . $dbPath;
        }
        try {
            self::$pdo = new PDO($dsn, $cfg['username'] ?? null, $cfg['password'] ?? null, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]);
        } catch (PDOException $e) {
            if (($cfg['driver'] ?? '') === 'mysql') {
                try {
                    $dsnCreate = 'mysql:host=' . $cfg['host'] . ';port=' . $cfg['port'] . ';charset=' . $cfg['charset'];
                    $pdoCreate = new PDO($dsnCreate, $cfg['username'] ?? null, $cfg['password'] ?? null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
                    $pdoCreate->exec('CREATE DATABASE IF NOT EXISTS `' . $cfg['database'] . '` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
                    $dsn2 = 'mysql:host=' . $cfg['host'] . ';port=' . $cfg['port'] . ';dbname=' . $cfg['database'] . ';charset=' . $cfg['charset'];
                    self::$pdo = new PDO($dsn2, $cfg['username'] ?? null, $cfg['password'] ?? null, [
                        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    ]);
                } catch (PDOException $e2) {
                    $dbPath = Config::get('storage_path') . '/app.sqlite';
                    self::$pdo = new PDO('sqlite:' . $dbPath);
                }
            } else {
            $dbPath = Config::get('storage_path') . '/app.sqlite';
            self::$pdo = new PDO('sqlite:' . $dbPath);
            }
        }
        self::migrate();
    }

    public static function pdo(): PDO
    {
        return self::$pdo;
    }

    private static function migrate(): void
    {
        $sql = [
            "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTO_INCREMENT, tenant_id INTEGER, name VARCHAR(255), email VARCHAR(255) UNIQUE, password VARCHAR(255), role VARCHAR(50), created_at DATETIME, updated_at DATETIME)",
            "CREATE TABLE IF NOT EXISTS tenants (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), razao_social VARCHAR(255), cnpj VARCHAR(20) UNIQUE, uf VARCHAR(2), cert_path VARCHAR(255), cert_password VARCHAR(255), cert_password_enc TEXT, nsu VARCHAR(50), ultimo_nsu VARCHAR(50), ambiente VARCHAR(20), plan_id INTEGER, status VARCHAR(20), created_at DATETIME, updated_at DATETIME)",
            "CREATE TABLE IF NOT EXISTS plans (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), limit_notas INTEGER, price DECIMAL(10,2), features TEXT, created_at DATETIME, updated_at DATETIME)",
            "CREATE TABLE IF NOT EXISTS invoices (id INTEGER PRIMARY KEY AUTO_INCREMENT, tenant_id INTEGER, chave VARCHAR(50), xml_path VARCHAR(255), emitente VARCHAR(255), destinatario VARCHAR(255), valor_total DECIMAL(10,2), data_emissao DATETIME, status VARCHAR(50), created_at DATETIME, updated_at DATETIME)",
            "CREATE TABLE IF NOT EXISTS payments (id INTEGER PRIMARY KEY AUTO_INCREMENT, tenant_id INTEGER, plan_id INTEGER, mp_payment_id VARCHAR(100), status VARCHAR(50), amount DECIMAL(10,2), due_date DATETIME, paid_at DATETIME, created_at DATETIME, updated_at DATETIME)",
            "CREATE TABLE IF NOT EXISTS sefaz_logs (id INTEGER PRIMARY KEY AUTO_INCREMENT, tenant_id INTEGER, operation VARCHAR(50), request TEXT, response TEXT, status_code INTEGER, created_at DATETIME)",
            "CREATE TABLE IF NOT EXISTS sefaz_failed (id INTEGER PRIMARY KEY AUTO_INCREMENT, tenant_id INTEGER, operation VARCHAR(50), request TEXT, status_code INTEGER, error TEXT, next_retry_at DATETIME, retries INTEGER DEFAULT 0, created_at DATETIME)",
            "CREATE TABLE IF NOT EXISTS notas (id INTEGER PRIMARY KEY AUTO_INCREMENT, tenant_id INTEGER, numero_nota VARCHAR(20), chave_acesso VARCHAR(50), data_emissao DATETIME, valor DECIMAL(10,2), caminho_xml VARCHAR(255), created_at DATETIME)",
            "CREATE TABLE IF NOT EXISTS assinaturas (id INTEGER PRIMARY KEY AUTO_INCREMENT, empresa_id INTEGER, plano_id INTEGER, status VARCHAR(20), data_inicio DATETIME, data_fim DATETIME, created_at DATETIME, updated_at DATETIME)",
            "CREATE TABLE IF NOT EXISTS manifestacoes (id INTEGER PRIMARY KEY AUTO_INCREMENT, tenant_id INTEGER, chave VARCHAR(50), tipo VARCHAR(30), protocolo VARCHAR(50), status VARCHAR(20), created_at DATETIME)",
            "CREATE TABLE IF NOT EXISTS login_logs (id INTEGER PRIMARY KEY AUTO_INCREMENT, user_id INTEGER, email VARCHAR(255), success TINYINT(1), ip VARCHAR(50), created_at DATETIME)",
            "CREATE TABLE IF NOT EXISTS admin_logs (id INTEGER PRIMARY KEY AUTO_INCREMENT, admin_user_id INTEGER, action VARCHAR(100), target_tenant_id INTEGER, details TEXT, created_at DATETIME)",
            "CREATE TABLE IF NOT EXISTS webhook_logs (id INTEGER PRIMARY KEY AUTO_INCREMENT, provider VARCHAR(50), payload TEXT, status_code INTEGER, created_at DATETIME)",
            "CREATE TABLE IF NOT EXISTS settings (id INTEGER PRIMARY KEY AUTO_INCREMENT, skey VARCHAR(100) UNIQUE, svalue TEXT, updated_at DATETIME)"
        ];
        foreach ($sql as $s) {
            try {
                self::$pdo->exec($s);
            } catch (\Throwable $e) {
            }
        }
        // Ajustes incrementais para ambientes já existentes
        $alter = [
            "ALTER TABLE tenants ADD COLUMN uf VARCHAR(2)",
            "ALTER TABLE tenants ADD COLUMN razao_social VARCHAR(255)",
            "ALTER TABLE tenants ADD COLUMN cert_password_enc TEXT",
            "ALTER TABLE tenants ADD COLUMN ambiente VARCHAR(20)",
            "ALTER TABLE tenants ADD COLUMN ultimo_nsu VARCHAR(50)",
            "ALTER TABLE notas ADD COLUMN emitente_cnpj VARCHAR(20)",
            "ALTER TABLE notas ADD COLUMN destinatario_cnpj VARCHAR(20)",
            "ALTER TABLE notas ADD COLUMN cfop VARCHAR(10)",
            "ALTER TABLE notas ADD COLUMN cst VARCHAR(10)",
        ];
        foreach ($alter as $a) {
            try {
                self::$pdo->exec($a);
            } catch (\Throwable $e) {
            }
        }
        // Criar views de compatibilidade (MySQL)
        try {
            $cfg = Config::get('db');
            if (($cfg['driver'] ?? 'mysql') === 'mysql') {
                self::$pdo->exec("CREATE OR REPLACE VIEW xml_notas AS
                    SELECT
                        n.id,
                        n.tenant_id AS empresa_id,
                        n.chave_acesso,
                        n.numero_nota,
                        n.data_emissao,
                        n.valor,
                        CASE WHEN t.cnpj = n.emitente_cnpj THEN 'saida' ELSE 'entrada' END AS tipo,
                        n.caminho_xml,
                        CASE WHEN m.status IS NULL THEN 'nao' ELSE 'sim' END AS manifestado,
                        COALESCE(m.status, 'nao') AS status_manifestacao,
                        COALESCE(m.protocolo, '') AS protocolo_manifestacao,
                        COALESCE(m.tipo, '') AS tipo_manifestacao
                    FROM notas n
                    LEFT JOIN tenants t ON t.id = n.tenant_id
                    LEFT JOIN (
                        SELECT mm.tenant_id, mm.chave, mm.status, mm.protocolo, mm.tipo, mm.created_at
                        FROM manifestacoes mm
                        INNER JOIN (
                            SELECT tenant_id, chave, MAX(created_at) AS max_created
                            FROM manifestacoes
                            GROUP BY tenant_id, chave
                        ) last ON last.tenant_id = mm.tenant_id AND last.chave = mm.chave AND last.max_created = mm.created_at
                    ) m ON m.tenant_id = n.tenant_id AND m.chave = n.chave_acesso");
                self::$pdo->exec("CREATE OR REPLACE VIEW pagamentos AS SELECT id, tenant_id AS empresa_id, plan_id AS plano_id, status, amount AS valor, created_at, updated_at FROM payments");
                self::$pdo->exec("CREATE OR REPLACE VIEW logs_sefaz AS SELECT * FROM sefaz_logs");
            }
        } catch (\Throwable $e) {
        }
        $count = self::$pdo->query("SELECT COUNT(*) AS c FROM plans")->fetch()['c'] ?? 0;
        if ((int)$count === 0) {
            $now = date('Y-m-d H:i:s');
            $stmt = self::$pdo->prepare("INSERT INTO plans (name, limit_notas, price, features, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)");
            $stmt->execute(['Básico', 1000, 99.90, 'consulta,download', $now, $now]);
            $stmt->execute(['Pro', 5000, 299.90, 'consulta,download,manifestacao', $now, $now]);
            $stmt->execute(['Premium', 20000, 799.90, 'consulta,download,manifestacao,relatorios', $now, $now]);
        }
    }
}
