<?php

function garantir_estrutura_bases(mysqli $conn): void
{
    $conn->query("CREATE TABLE IF NOT EXISTS bases (
        idbase INT AUTO_INCREMENT PRIMARY KEY,
        nome_base VARCHAR(100) NOT NULL,
        ativa CHAR(1) NOT NULL DEFAULT 'S',
        data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uk_nome_base (nome_base)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $dbResult = $conn->query("SELECT DATABASE() AS db_name");
    $dbRow = $dbResult ? $dbResult->fetch_assoc() : null;
    $dbName = $dbRow['db_name'] ?? '';

    if ($dbName !== '') {
        $stmt = $conn->prepare("SELECT COUNT(*) AS total FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'empresas' AND COLUMN_NAME = 'idbase'");
        if ($stmt) {
            $stmt->bind_param('s', $dbName);
            $stmt->execute();
            $total = (int)($stmt->get_result()->fetch_assoc()['total'] ?? 0);
            $stmt->close();

            if ($total === 0) {
                $conn->query("ALTER TABLE empresas ADD COLUMN idbase INT NULL AFTER BASE");
            }
        }

        $stmtIdx = $conn->prepare("SELECT COUNT(*) AS total FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'empresas' AND INDEX_NAME = 'idx_empresas_idbase'");
        if ($stmtIdx) {
            $stmtIdx->bind_param('s', $dbName);
            $stmtIdx->execute();
            $idxTotal = (int)($stmtIdx->get_result()->fetch_assoc()['total'] ?? 0);
            $stmtIdx->close();

            if ($idxTotal === 0) {
                $conn->query("CREATE INDEX idx_empresas_idbase ON empresas(idbase)");
            }
        }
    }

    // Migrar bases legadas (texto em empresas.BASE) para tabela bases
    $conn->query("INSERT IGNORE INTO bases (nome_base) SELECT DISTINCT TRIM(BASE) FROM empresas WHERE BASE IS NOT NULL AND TRIM(BASE) <> ''");

    // Preencher idbase nas empresas que ainda não possuem idbase
    $conn->query("UPDATE empresas e INNER JOIN bases b ON b.nome_base = TRIM(e.BASE) SET e.idbase = b.idbase WHERE (e.idbase IS NULL OR e.idbase = 0) AND e.BASE IS NOT NULL AND TRIM(e.BASE) <> ''");
}

function listar_bases(mysqli $conn, bool $somenteAtivas = true): array
{
    $sql = "SELECT idbase, nome_base FROM bases";
    if ($somenteAtivas) {
        $sql .= " WHERE ativa = 'S'";
    }
    $sql .= " ORDER BY nome_base ASC";

    $bases = [];
    $res = $conn->query($sql);
    if ($res) {
        while ($row = $res->fetch_assoc()) {
            $bases[] = [
                'idbase' => (int)$row['idbase'],
                'nome_base' => $row['nome_base']
            ];
        }
    }

    return $bases;
}

function obter_nome_base_por_id(mysqli $conn, int $idbase): string
{
    if ($idbase <= 0) {
        return '';
    }

    $stmt = $conn->prepare("SELECT nome_base FROM bases WHERE idbase = ? LIMIT 1");
    if (!$stmt) {
        return '';
    }

    $stmt->bind_param('i', $idbase);
    $stmt->execute();
    $nome = (string)($stmt->get_result()->fetch_assoc()['nome_base'] ?? '');
    $stmt->close();

    return $nome;
}

function obter_ou_criar_base(mysqli $conn, string $nomeBase): int
{
    $nomeBase = trim($nomeBase);
    if ($nomeBase === '') {
        return 0;
    }

    $stmtBusca = $conn->prepare("SELECT idbase FROM bases WHERE nome_base = ? LIMIT 1");
    if ($stmtBusca) {
        $stmtBusca->bind_param('s', $nomeBase);
        $stmtBusca->execute();
        $idExistente = (int)($stmtBusca->get_result()->fetch_assoc()['idbase'] ?? 0);
        $stmtBusca->close();
        if ($idExistente > 0) {
            return $idExistente;
        }
    }

    $stmtIns = $conn->prepare("INSERT INTO bases (nome_base, ativa) VALUES (?, 'S')");
    if (!$stmtIns) {
        return 0;
    }

    $stmtIns->bind_param('s', $nomeBase);
    if (!$stmtIns->execute()) {
        $stmtIns->close();
        return 0;
    }

    $novoId = (int)$stmtIns->insert_id;
    $stmtIns->close();

    return $novoId;
}
