import fs from "fs";
import os from "os";
import path from "path";
import { spawnSync } from "child_process";
import { afterAll, beforeAll, describe, expect, it } from "vitest";

const { Pool } = require("pg");

const livePostgresUrl = process.env.C6_LIVE_POSTGRES_URL || "postgres://rtm@127.0.0.1:5432/postgres";
const runLivePostgres = process.env.C6_LIVE_POSTGRES === "1" || !!process.env.C6_LIVE_POSTGRES_URL;
const repoRoot = process.cwd();
const generatorScriptPath = path.resolve(repoRoot, "scripts/generateRestBindings.cjs");
const databaseName = "carbonnode_pg_generator_smoke";

const writeJson = (filePath: string, payload: Record<string, any>) => {
    fs.writeFileSync(filePath, `${JSON.stringify(payload, null, 2)}\n`);
};

describe.runIf(runLivePostgres)("generateRestBindings PostgreSQL live smoke", () => {
    let adminPool: any;
    let databasePool: any;

    beforeAll(async () => {
        adminPool = new Pool({
            connectionString: livePostgresUrl,
        });

        await adminPool.query(`DROP DATABASE IF EXISTS ${databaseName}`);
        await adminPool.query(`CREATE DATABASE ${databaseName}`);

        databasePool = new Pool({
            connectionString: livePostgresUrl.replace(/\/[^/]+$/, `/${databaseName}`),
        });
        await databasePool.query(`
            CREATE TABLE actor (
                actor_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                first_name character varying(45) NOT NULL,
                last_name text,
                profile jsonb
            )
        `);
        await databasePool.query(`
            CREATE TABLE film_actor (
                actor_id integer NOT NULL REFERENCES actor(actor_id),
                film_id integer NOT NULL,
                PRIMARY KEY (actor_id, film_id)
            )
        `);
    });

    afterAll(async () => {
        await databasePool?.end();
        await adminPool?.query(`
            SELECT pg_terminate_backend(pid)
            FROM pg_stat_activity
            WHERE datname = '${databaseName}'
              AND pid <> pg_backend_pid()
        `);
        await adminPool?.query(`DROP DATABASE IF EXISTS ${databaseName}`);
        await adminPool?.end();
    });

    it("generates bindings from a live PostgreSQL database through pg_dump and psql", () => {
        const tempDir = fs.mkdtempSync(path.join(os.tmpdir(), "carbonnode-live-pg-generator-"));
        try {
            const configPath = path.join(tempDir, "C6.config.json");
            const outputDir = path.join(tempDir, "generated");
            writeJson(configPath, {
                databases: [
                    {
                        alias: "app",
                        dialect: "postgresql",
                        host: "127.0.0.1",
                        port: 5432,
                        user: "rtm",
                        pass: "unused",
                        dbnames: [databaseName],
                    },
                ],
            });

            const result = spawnSync(
                "node",
                [generatorScriptPath, "--config", configPath, "--output", outputDir],
                {
                    cwd: repoRoot,
                    encoding: "utf-8",
                    env: {
                        ...process.env,
                        FORCE_COLOR: "0",
                        C6_SKIP_GENERATED_TSC: "1",
                    },
                },
            );

            expect(`${result.stdout}\n${result.stderr}`).toMatch(/Successfully created CarbonORM bindings/i);
            expect(result.status).toBe(0);

            const generatedActorPath = path.join(outputDir, "C6.generated", "tables", "Actor.ts");
            expect(fs.existsSync(path.join(outputDir, "C6.pg_dump.sql"))).toBe(true);
            expect(fs.existsSync(generatedActorPath)).toBe(true);

            const actorSource = fs.readFileSync(generatedActorPath, "utf-8");
            expect(actorSource).toMatch(/TABLE_NAME: 'actor'/);
            expect(actorSource).toMatch(/'actor_id'\?: number/);
            expect(actorSource).toMatch(/MYSQL_TYPE: 'int'/);
            expect(actorSource).toMatch(/AUTO_INCREMENT: true/);
            expect(actorSource).toMatch(/MYSQL_TYPE: 'jsonb'/);
        } finally {
            fs.rmSync(tempDir, { recursive: true, force: true });
        }
    });
});