import { logError, logInfo } from "@/lib/logger";
import { promises as fs } from "node:fs";
import type {
  Migration,
  MigrationProvider
} from "kysely";
import {
  Kysely,
  Migrator,
  PostgresDialect
} from "kysely";
import path, { dirname } from "node:path";
import type { PoolConfig } from "pg";
import pg from "pg";
import { fileURLToPath, pathToFileURL } from "node:url";
import type { DB } from "./db";

const { Pool } = pg;

const __dirname = dirname(fileURLToPath(import.meta.url));

// Custom migration provider that handles Windows ESM paths properly
class WindowsEsmMigrationProvider implements MigrationProvider {
  constructor(private migrationFolder: string, private db?: Kysely<any>) {}

  async getMigrations(): Promise<Record<string, Migration>> {
    const files = await fs.readdir(this.migrationFolder);
    const migrations: Record<string, Migration> = {};

    for (const file of files) {
      if (!file.endsWith(".ts") && !file.endsWith(".js"))
        continue;

      const filePath = path.join(this.migrationFolder, file);
      const fileUrl = pathToFileURL(filePath).href;
      // Get migration name without extension
      const migrationName = file.replace(/\.(ts|js)$/, "");

      try {
        const module = await import(fileUrl);
        migrations[migrationName] = module.default || module;
      } catch (error) {
        logError(`Failed to load migration file ${file}: ${error}`);
        throw error;
      }
    }

    if (this.db) {
      try {
        const executed = await this.db
          .selectFrom("kysely_migration" as any)
          .select("name")
          .execute();
        
        for (const row of executed) {
          const name = row.name as string;
          if (!migrations[name]) {
            migrations[name] = {
              up: async () => {},
              down: async () => {},
            };
          }
        }
      } catch (err) {
        // Table might not exist yet on fresh setup, ignore
      }
    }

    return migrations;
  }
}

export const createDBPool = (config: PoolConfig) => {
  try {
    const pool = new Pool({
      ...config,
    });

    // Handle unexpected errors on idle clients
    pool.on("error", (err) => {
      logError(`Unexpected error on idle client: ${err}`);
    });

    const db = new Kysely<DB>({
      dialect: new PostgresDialect({
        pool,
      }),
    });
    logInfo("Connected to Database successfully");
    return db;
  } catch (err) {
    throw err;
  }
};

export const migratePSQL = async (config: PoolConfig) => {
  const db = new Kysely<DB>({
    dialect: new PostgresDialect({
      pool: new Pool({
        ...config,
      }),
    }),
  });

  try {
    const migrator = new Migrator({
      db,
      provider: new WindowsEsmMigrationProvider(
        path.join(__dirname, "migrations"),
        db
      ),
    });
    const { error, results } = await migrator.migrateToLatest();
    if (error) {
      console.error("Migration Error Details:", error);
      // logError(error, "Migration failed");
      process.exit(1);
    }

    results?.forEach((r) => {
      console.log(`[PSQL] ${r.status}: ${r.migrationName}`);
    });
    console.log("[PSQL] Migration complete");
  } catch (err) {
    throw err;
  } finally {
    await db.destroy();
  }
};