/**
 * Migration Service for PGlite database upgrades
 *
 * Handles detection and guidance for migrating from PGlite 0.2.x (PostgreSQL 16)
 * to 0.3.x (PostgreSQL 17).
 *
 * Note: Automatic migration is not possible due to WASM compatibility issues
 * between PGlite versions. This service provides detection and guidance.
 */

import { Effect, Data } from "effect";
import { PGlite } from "@electric-sql/pglite";
import { vector } from "@electric-sql/pglite/vector";
import { existsSync, readdirSync } from "fs";
import { dirname, join } from "path";

/** Migration error with reason */
export class MigrationError extends Data.TaggedError("MigrationError")<{
  readonly reason: string;
}> {}

/** Migration result */
export interface MigrationResult {
  readonly success: boolean;
  readonly memoriesCount: number;
  readonly embeddingsCount: number;
  readonly backupPath?: string;
}

/**
 * Check if a data directory exists and looks like a PGlite database
 */
const isPGliteDataDir = (dataDir: string): boolean => {
  if (!existsSync(dataDir)) return false;
  try {
    const files = readdirSync(dataDir);
    return files.includes("base") && files.includes("global");
  } catch {
    return false;
  }
};

/**
 * Check if migration is needed by attempting to open with current PGlite
 */
export const needsMigration = (
  dataDir: string,
): Effect.Effect<boolean, MigrationError> =>
  Effect.gen(function* () {
    if (!isPGliteDataDir(dataDir)) {
      return false;
    }

    // Try to open with current PGlite version
    const result = yield* Effect.tryPromise({
      try: async () => {
        const db = new PGlite(dataDir, { extensions: { vector } });
        try {
          await db.waitReady;
          await db.close();
          return false; // Opens fine, no migration needed
        } catch {
          return true; // Failed to open, needs migration
        }
      },
      catch: () => new MigrationError({ reason: "Failed to check database" }),
    });

    return result;
  });

/**
 * Attempt migration - will fail with helpful instructions since automatic
 * migration is not possible due to WASM compatibility issues.
 */
export const migrate = (
  dataDir: string,
  _options?: { keepBackup?: boolean },
): Effect.Effect<MigrationResult, MigrationError> =>
  Effect.fail(
    new MigrationError({
      reason: `PGlite 0.2.x database cannot be automatically migrated.

The old database format (PostgreSQL 16) is incompatible with the new version (PostgreSQL 17),
and the PGlite 0.2.x WASM runtime has compatibility issues with current JavaScript runtimes.

Options:

1. Start fresh (recommended if data isn't critical):
   rm -r ${dataDir}
   semantic-memory stats

2. If you have critical data, the old database files are preserved at:
   ${dataDir}
   
   You may be able to recover data using an older version of Node.js (v18)
   with PGlite 0.2.x, or by using native PostgreSQL tools on the data files.

The database uses standard PostgreSQL 16 format in: ${dataDir}`,
    }),
  );

/**
 * Import a migration dump into a new 0.3.x database
 */
export const importMigrationDump = (
  dataDir: string,
  dumpPath: string,
): Effect.Effect<MigrationResult, MigrationError> =>
  Effect.tryPromise({
    try: async () => {
      const dumpSql = await Bun.file(dumpPath).text();

      // Ensure data directory doesn't exist
      if (existsSync(dataDir)) {
        throw new Error(`Data directory already exists: ${dataDir}`);
      }

      // Create new database
      const db = new PGlite(dataDir, { extensions: { vector } });
      await db.waitReady;

      // Enable vector extension
      await db.exec("CREATE EXTENSION IF NOT EXISTS vector;");

      // Import dump
      await db.exec(dumpSql);

      // Get counts
      const memResult = await db.query<{ count: string }>(
        "SELECT COUNT(*) as count FROM memories",
      );
      const embResult = await db.query<{ count: string }>(
        "SELECT COUNT(*) as count FROM memory_embeddings",
      );

      const memoriesCount = parseInt(memResult.rows[0]?.count ?? "0");
      const embeddingsCount = parseInt(embResult.rows[0]?.count ?? "0");

      await db.close();

      return {
        success: true,
        memoriesCount,
        embeddingsCount,
      };
    },
    catch: (e) => new MigrationError({ reason: `Import failed: ${e}` }),
  });

/**
 * Generate a migration helper script for manual migration
 * This script is designed to run with an older Node.js version where
 * PGlite 0.2.x WASM works correctly.
 */
export const generateMigrationScript = (dataDir: string): string => {
  const parentDir = dirname(dataDir);
  const dumpPath = join(parentDir, "migration-dump.sql");

  return `#!/usr/bin/env node
/**
 * PGlite Migration Script
 * 
 * This script exports your semantic-memory database from PGlite 0.2.x format.
 * 
 * IMPORTANT: This requires an older Node.js version (v18) where PGlite 0.2.x works.
 * 
 * Setup:
 *   mkdir pglite-migrate && cd pglite-migrate
 *   npm init -y
 *   npm install @electric-sql/pglite@0.2.17 @electric-sql/pglite-tools --legacy-peer-deps
 *   node migrate-helper.mjs
 */

import { PGlite } from "@electric-sql/pglite";
import { vector } from "@electric-sql/pglite/vector";
import { pgDump } from "@electric-sql/pglite-tools";
import { writeFileSync } from "fs";

const dataDir = "${dataDir}";
const dumpPath = "${dumpPath}";

async function main() {
  console.log("Opening old database at:", dataDir);
  
  const oldDb = await PGlite.create({
    dataDir,
    extensions: { vector },
  });
  
  const version = await oldDb.query("SELECT version()");
  console.log("Database version:", version.rows[0].version);
  
  console.log("Creating SQL dump...");
  const dump = await pgDump({ pg: oldDb });
  const dumpText = await dump.text();
  
  writeFileSync(dumpPath, dumpText);
  console.log("Dump saved to:", dumpPath);
  console.log("Dump size:", (dumpText.length / 1024).toFixed(1), "KB");
  
  await oldDb.close();
  
  console.log("\\n✓ Export complete!");
  console.log("\\nNext steps:");
  console.log("1. Backup old data: mv ${dataDir} ${dataDir}.old");
  console.log("2. Import into new format: semantic-memory migrate --import ${dumpPath}");
  console.log("3. Verify: semantic-memory stats");
}

main().catch((e) => {
  console.error("Migration failed:", e.message);
  process.exit(1);
});
`;
};