adaptador-planilhas/tests/unit/migration-admin.test.ts

import { readFileSync } from "node:fs";
import path from "node:path";
import { describe, expect, it } from "vitest";
import { assertDeployedEnv, assertMigrationAdminEnv, collectMigrationAdminIssues } from "@/config/env";
import { DatabaseGuardError, buildPostgresOptions } from "@/infra/db/connection-policy";
import { SUPABASE_ROOT_CA_2021 } from "@/infra/db/supabase-root-ca";
import { evaluateProjectIdentity } from "@/infra/db/target-guard";
import { AUTHORIZED_HML_PROJECT } from "@/infra/db/connection-policy";
import {
  MigrationLedgerError,
  pendingMigrations,
  readMigrationLedger,
} from "@/infra/db/migration-manifest";
import { createMemoryDb } from "@/infra/db/pglite";
import { migrate } from "@/infra/db/migrate";
import type { Db, Query } from "@/infra/db/types";

const REF = "abcd1234abcd1234wxyz";
const OUTRO = "zzzzzzzzzzzzzzzzzzz1";
const HOST = "aws-1-us-west-2.pooler.supabase.com";

function adminUrl(user = `postgres.${REF}`, database = "postgres", host = HOST, sslmode = "verify-full"): string {
  return `postgres://${user}:segredo@${host}:5432/${database}?sslmode=${sslmode}`;
}

const adminEnv = {
  ADAPTER_ENV: "staging",
  ADAPTER_DB_PROJECT_REF: REF,
  ADAPTER_DB_POOLER_HOST: HOST,
  ADAPTER_MIGRATION_DATABASE_URL: adminUrl(),
  ADAPTER_APPLY_IDENTITY_SHIM: "1",
};

function banco(query: Query["query"]): Db {
  return {
    exec: async () => undefined,
    withUser: async (_userId, fn) => fn({ query }),
    withService: async (fn) => fn({ query }),
    withTransaction: async (fn) => fn({ exec: async () => undefined, query }),
    close: async () => undefined,
  };
}

describe("migrate administrativo", () => {
  it("aceita staging só com a configuração administrativa", () => {
    expect(collectMigrationAdminIssues(adminEnv)).toEqual([]);
    expect(() => assertMigrationAdminEnv(adminEnv)).not.toThrow();
    const options = buildPostgresOptions(adminEnv.ADAPTER_MIGRATION_DATABASE_URL, "admin", adminEnv);
    expect(options.ssl?.rejectUnauthorized).toBe(true);
    expect(options.ssl?.ca).toBe(SUPABASE_ROOT_CA_2021);
  });

  it("não exige web, Redis, Storage, login nem TTL", () => {
    const env = { ...adminEnv };
    expect(env).not.toHaveProperty("DATABASE_URL");
    expect(env).not.toHaveProperty("REDIS_URL");
    expect(env).not.toHaveProperty("SUPABASE_STORAGE_BUCKET");
    expect(env).not.toHaveProperty("ADAPTER_STAGING_AUTH");
    expect(env).not.toHaveProperty("SPREADSHEET_TEMP_TTL_SECONDS");
    expect(() => assertMigrationAdminEnv(env)).not.toThrow();
  });

  it("recusa Reference ID ausente e divergente", () => {
    expect(collectMigrationAdminIssues({ ...adminEnv, ADAPTER_DB_PROJECT_REF: "" }).map((issue) => issue.variable)).toContain(
      "ADAPTER_DB_PROJECT_REF",
    );
    expect(() => buildPostgresOptions(adminUrl(`postgres.${OUTRO}`), "admin", adminEnv)).toThrow(DatabaseGuardError);
    expect(() =>
      evaluateProjectIdentity({ projectName: AUTHORIZED_HML_PROJECT, projectRef: OUTRO }, adminEnv),
    ).toThrow(/marcador_diverge/);
  });

  it("recusa host, banco, usuário e TLS fora do destino", () => {
    expect(collectMigrationAdminIssues({ ...adminEnv, ADAPTER_DB_POOLER_HOST: "db.exemplo.supabase.co" }).map((item) => item.variable)).toContain(
      "ADAPTER_DB_POOLER_HOST",
    );
    expect(() => buildPostgresOptions(adminUrl(`postgres.${REF}`, "postgres", "aws-9-us-west-2.pooler.supabase.com"), "admin", adminEnv)).toThrow(
      /destino_incompativel/,
    );
    expect(() => buildPostgresOptions(adminUrl(`postgres.${REF}`, "template1"), "admin", adminEnv)).toThrow(/destino_incompativel/);
    expect(() => buildPostgresOptions(adminUrl(`adaptador_web.${REF}`), "admin", adminEnv)).toThrow(/usuario_url_diverge/);
    expect(() => buildPostgresOptions(adminUrl(`postgres.${REF}`, "postgres", HOST, "require"), "admin", adminEnv)).toThrow(/tls_inseguro/);
    expect(() =>
      buildPostgresOptions(
        `postgres://postgres.${REF}:segredo@supabase.elopro.app:5432/postgres?sslmode=verify-full`,
        "admin",
        { ...adminEnv, ADAPTER_DB_POOLER_HOST: "supabase.elopro.app" },
      ),
    ).toThrow(/host_elopro_preservado|pooler/);
  });

  it("recusa shim ausente e ambiente que não é staging", () => {
    const semShim = { ...adminEnv } as Record<string, string | undefined>;
    delete semShim.ADAPTER_APPLY_IDENTITY_SHIM;
    expect(collectMigrationAdminIssues(semShim).map((issue) => issue.variable)).toContain("ADAPTER_APPLY_IDENTITY_SHIM");
    expect(collectMigrationAdminIssues({ ...adminEnv, ADAPTER_APPLY_IDENTITY_SHIM: "true" }).map((issue) => issue.variable)).toContain(
      "ADAPTER_APPLY_IDENTITY_SHIM",
    );
    expect(collectMigrationAdminIssues({ ...adminEnv, ADAPTER_ENV: "production" }).map((issue) => issue.variable)).toContain("ADAPTER_ENV");
    expect(collectMigrationAdminIssues({ ...adminEnv, ADAPTER_ENV: "development" }).map((issue) => issue.variable)).toContain("ADAPTER_ENV");
  });

  it("mantém a aplicação e o worker sujeitos à configuração operacional", () => {
    expect(() => assertDeployedEnv({ ADAPTER_ENV: "staging", ADAPTER_APPLY_IDENTITY_SHIM: "1" })).toThrow(/DATABASE_URL/);
    expect(() => assertDeployedEnv({ ADAPTER_ENV: "staging", ADAPTER_APPLY_IDENTITY_SHIM: "1" })).toThrow(/REDIS_URL/);
    expect(() => assertDeployedEnv({ ADAPTER_ENV: "staging", ADAPTER_APPLY_IDENTITY_SHIM: "1", DATABASE_URL: adminUrl(), REDIS_URL: "redis://host" })).toThrow(
      /SUPABASE_STORAGE_BUCKET/,
    );
    expect(() =>
      assertDeployedEnv({
        ADAPTER_ENV: "staging",
        ADAPTER_APPLY_IDENTITY_SHIM: "1",
        DATABASE_URL: adminUrl(),
        REDIS_URL: "redis://host",
        NEXT_PUBLIC_SUPABASE_URL: "https://exemplo.supabase.co",
        NEXT_PUBLIC_SUPABASE_ANON_KEY: "anon",
        SUPABASE_SERVICE_ROLE_KEY: "service",
        SUPABASE_STORAGE_BUCKET: "privado",
        SPREADSHEET_TEMP_TTL_SECONDS: "3600",
      }),
    ).toThrow(/ADAPTER_STAGING_AUTH/);
    const worker = readFileSync(path.join(process.cwd(), "scripts", "worker.ts"), "utf8");
    const migrateScript = readFileSync(path.join(process.cwd(), "scripts", "migrate.ts"), "utf8");
    expect(worker).toContain('bootstrapScript("worker")');
    expect(worker).not.toContain("exitOnInvalidMigrationConfig");
    expect(migrateScript).toContain("exitOnInvalidMigrationConfig");
    expect(migrateScript).toContain('process.argv.includes("--plan")');
    expect(migrateScript).toContain('process.argv.includes("--dry-run")');
  });
});

describe("histórico do dry-run", () => {
  it("não transforma falha de leitura em histórico vazio", async () => {
    const db = banco(async (sql) => {
      if (sql.includes("to_regclass")) return [{ name: "schema_migrations" }];
      throw Object.assign(new Error("permission denied"), { code: "42501" });
    });
    await expect(readMigrationLedger(db)).rejects.toBeInstanceOf(MigrationLedgerError);
    await expect(pendingMigrations(db, { applyIdentityShim: true })).rejects.toBeInstanceOf(MigrationLedgerError);
  });

  it("identifica histórico ausente sem criar a tabela", async () => {
    const db = await createMemoryDb();
    await expect(readMigrationLedger(db)).resolves.toEqual({ state: "ausente" });
    const ainda = await db.withService((query) =>
      query.query<{ name: string | null }>("select to_regclass('public.schema_migrations')::text as name"),
    );
    expect(ainda[0]?.name ?? null).toBeNull();
    await expect(pendingMigrations(db, { applyIdentityShim: true })).resolves.toHaveLength(5);
    await db.close();
  });

  it("lê o histórico íntegro depois da aplicação local", async () => {
    const db = await createMemoryDb();
    await migrate(db, { applyIdentityShim: true });
    const ledger = await readMigrationLedger(db);
    expect(ledger).toEqual({
      state: "presente",
      ids: [
        "0001_platform_shim.sql",
        "0002_spreadsheet_adapter.sql",
        "0003_rls.sql",
        "0004_audit_append_only.sql",
        "0005_maintenance_roles.sql",
      ],
    });
    await expect(pendingMigrations(db, { applyIdentityShim: true })).resolves.toEqual([]);
    await db.close();
  });
});