/**
 * Database test helper - creates isolated database instances for testing
 */

import { database } from "../../src/storage/database.js";
import { readFileSync } from "fs";
import { join } from "path";
import type { TestContext } from "./test-utils.js";

/**
 * Setup test database with isolated database connection
 * Uses MCP_TEST_DATABASE_URL if provided, otherwise uses DATABASE_URL with a test suffix
 */
export async function setupTestDatabase(testContext: TestContext): Promise<() => Promise<void>> {
  // Use test database URL if provided, otherwise use main database URL
  const testDbUrl = process.env.MCP_TEST_DATABASE_URL || process.env.DATABASE_URL;
  
  if (!testDbUrl) {
    throw new Error(
      "MCP_TEST_DATABASE_URL or DATABASE_URL environment variable is required for tests"
    );
  }

  // Set test database URL
  const originalDbUrl = process.env.DATABASE_URL;
  process.env.DATABASE_URL = testDbUrl;

  // Initialize database connection
  await database.initialize();

  // Create tables if they don't exist (using schema.sql)
  try {
    const schemaPath = join(process.cwd(), "src", "storage", "schema.sql");
    const schema = readFileSync(schemaPath, "utf-8");
    
    // Execute schema (split by semicolons and execute each statement)
    const statements = schema
      .split(";")
      .map((s) => s.trim())
      .filter((s) => s.length > 0 && !s.startsWith("--"));

    // We'll use the database connection directly
    // Note: This is a simplified approach. In production, you might want to use a migration tool
    const sql = (database as any).getSql();
    for (const statement of statements) {
      if (statement) {
        try {
          // Use postgres.unsafe() to execute raw SQL
          await (sql as any).unsafe(statement);
        } catch (error) {
          // Ignore errors for IF NOT EXISTS statements
          const errorMsg = (error as Error).message;
          if (!errorMsg.includes("already exists") && !errorMsg.includes("duplicate")) {
            console.warn(`Schema statement warning: ${errorMsg}`);
          }
        }
      }
    }
  } catch (error) {
    console.warn("Could not execute schema.sql:", error);
    // Continue anyway - tables might already exist
  }

  // Clean up all tables before each test
  await cleanupTestData();

  // Return cleanup function
  return async () => {
    await cleanupTestData();
    await database.close();
    if (originalDbUrl) {
      process.env.DATABASE_URL = originalDbUrl;
    } else {
      delete process.env.DATABASE_URL;
    }
  };
}

/**
 * Clean up test data from all tables
 */
async function cleanupTestData(): Promise<void> {
  try {
    const sql = (database as any).getSql();
    await sql`TRUNCATE TABLE code_snippets, notes, tasks, baby_milestones, math_resources, game_wishlist RESTART IDENTITY CASCADE`;
  } catch (error) {
    // Tables might not exist yet, ignore
    console.warn("Could not truncate test tables:", error);
  }
}