import { eq, or, like } from "drizzle-orm";
import { createDb, runSqliteMigrations } from "@atbb/db";
import { forums, posts, users, categories, memberships, boards, roles, modActions, backfillProgress, backfillErrors } from "@atbb/db";
import { createLogger } from "@atbb/logger";
import path from "path";
import { fileURLToPath } from "url";
import type { AppConfig } from "../config.js";
import type { AppContext } from "../app-context.js";

const __dirname = fileURLToPath(new URL(".", import.meta.url));

export interface TestContext extends AppContext {
  cleanup: () => Promise<void>;
  cleanDatabase: () => Promise<void>;
}

export interface TestContextOptions {
  emptyDb?: boolean;
}

/**
 * Create test context with database and sample data.
 * Call cleanup() after tests to remove test data.
 * Supports both Postgres (DATABASE_URL=postgres://...) and SQLite (DATABASE_URL=file::memory:).
 *
 * SQLite note: Uses file::memory:?cache=shared so that @libsql/client's transaction()
 * handoff (which sets #db = null and lazily recreates the connection) reconnects to the
 * same shared in-memory database rather than creating a new empty one. Without
 * cache=shared, migrations are lost after the first transaction.
 */
export async function createTestContext(
  options: TestContextOptions = {}
): Promise<TestContext> {
  const rawDatabaseUrl = process.env.DATABASE_URL ?? "";
  const isPostgres = rawDatabaseUrl.startsWith("postgres");

  // For SQLite in-memory databases: upgrade to cache=shared so that @libsql/client's
  // transaction() pattern (which sets #db=null and lazily recreates the connection)
  // reconnects to the same database rather than creating a new empty in-memory DB.
  const databaseUrl =
    rawDatabaseUrl === "file::memory:" || rawDatabaseUrl === ":memory:"
      ? "file::memory:?cache=shared"
      : rawDatabaseUrl;

  const config: AppConfig = {
    port: 3000,
    forumDid: "did:plc:test-forum",
    pdsUrl: "https://test.pds",
    databaseUrl,
    jetstreamUrl: "wss://test.jetstream",
    logLevel: "warn",
    oauthPublicUrl: "http://localhost:3000",
    sessionSecret: "test-secret-at-least-32-characters-long",
    sessionTtlDays: 7,
    backfillRateLimit: 10,
    backfillConcurrency: 10,
    backfillCursorMaxAgeHours: 48,
  };

  const db = createDb(config.databaseUrl);
  const isSqlite = !isPostgres;

  // For SQLite: run migrations programmatically before any tests.
  // Uses runSqliteMigrations from @atbb/db to ensure the same drizzle-orm instance
  // is used for both database creation and migration (avoids cross-package module issues).
  if (isSqlite) {
    const migrationsFolder = path.resolve(__dirname, "../../../drizzle-sqlite");
    await runSqliteMigrations(db, migrationsFolder);
  }

  // Create stub OAuth dependencies (unused in read-path tests)
  const stubFirehose = {
    start: () => Promise.resolve(),
    stop: () => Promise.resolve(),
  } as any;

  const stubOAuthClient = {} as any;
  const stubOAuthStateStore = { destroy: () => {} } as any;
  const stubOAuthSessionStore = { destroy: () => {} } as any;
  const stubCookieSessionStore = { destroy: () => {} } as any;
  const stubForumAgent = null; // Mock ForumAgent is null by default (can be overridden in tests)

  const cleanDatabase = async () => {
    if (isSqlite) {
      // SQLite in-memory: delete all rows in FK order (role_permissions cascade from roles)
      await db.delete(posts).catch(() => {});
      await db.delete(memberships).catch(() => {});
      await db.delete(users).catch(() => {});
      await db.delete(boards).catch(() => {});
      await db.delete(categories).catch(() => {});
      await db.delete(roles).catch(() => {}); // cascades to role_permissions
      await db.delete(modActions).catch(() => {});
      await db.delete(backfillErrors).catch(() => {});
      await db.delete(backfillProgress).catch(() => {});
      await db.delete(forums).catch(() => {});
      return;
    }

    // Postgres: delete by test DID patterns
    await db.delete(posts).where(eq(posts.did, config.forumDid)).catch(() => {});
    await db.delete(posts).where(like(posts.did, "did:plc:test-%")).catch(() => {});
    await db.delete(memberships).where(like(memberships.did, "did:plc:test-%")).catch(() => {});
    await db.delete(users).where(like(users.did, "did:plc:test-%")).catch(() => {});
    await db.delete(users).where(like(users.did, "did:plc:mod-%")).catch(() => {});
    await db.delete(users).where(like(users.did, "did:plc:subject-%")).catch(() => {});
    await db.delete(boards).where(eq(boards.did, config.forumDid)).catch(() => {});
    await db.delete(categories).where(eq(categories.did, config.forumDid)).catch(() => {});
    await db.delete(roles).where(eq(roles.did, config.forumDid)).catch(() => {}); // cascades to role_permissions
    await db.delete(modActions).where(eq(modActions.did, config.forumDid)).catch(() => {});
    await db.delete(backfillErrors).catch(() => {});
    await db.delete(backfillProgress).catch(() => {});
    await db.delete(forums).where(eq(forums.did, config.forumDid)).catch(() => {});
  };

  // Clean database before creating test data to ensure clean state
  await cleanDatabase();

  // Insert test forum unless emptyDb is true
  // No need for onConflictDoNothing since cleanDatabase ensures clean state
  if (!options.emptyDb) {
    await db.insert(forums).values({
      did: config.forumDid,
      rkey: "self",
      cid: "bafytest",
      name: "Test Forum",
      description: "A test forum",
      indexedAt: new Date(),
    });
  }

  const logger = createLogger({
    service: "atbb-appview-test",
    level: "warn",
  });

  return {
    db,
    config,
    logger,
    firehose: stubFirehose,
    oauthClient: stubOAuthClient,
    oauthStateStore: stubOAuthStateStore,
    oauthSessionStore: stubOAuthSessionStore,
    cookieSessionStore: stubCookieSessionStore,
    forumAgent: stubForumAgent,
    backfillManager: null,
    cleanDatabase,
    cleanup: async () => {
      // Clean up test data (order matters due to FKs: posts -> memberships -> users -> boards -> categories -> forums)
      // Delete all test-specific DIDs (including dynamically generated ones)
      const testDidPattern = or(
        eq(posts.did, "did:plc:test-user"),
        eq(posts.did, "did:plc:topicsuser"),
        like(posts.did, "did:plc:test-%"),
        like(posts.did, "did:plc:duptest-%"),
        like(posts.did, "did:plc:create-%"),
        like(posts.did, "did:plc:pds-fail-%")
      );
      await db.delete(posts).where(testDidPattern);

      const testMembershipPattern = or(
        eq(memberships.did, "did:plc:test-user"),
        eq(memberships.did, "did:plc:topicsuser"),
        like(memberships.did, "did:plc:test-%"),
        like(memberships.did, "did:plc:duptest-%"),
        like(memberships.did, "did:plc:create-%"),
        like(memberships.did, "did:plc:pds-fail-%")
      );
      await db.delete(memberships).where(testMembershipPattern);

      const testUserPattern = or(
        eq(users.did, "did:plc:test-user"),
        eq(users.did, "did:plc:topicsuser"),
        like(users.did, "did:plc:test-%"),
        like(users.did, "did:plc:duptest-%"),
        like(users.did, "did:plc:create-%"),
        like(users.did, "did:plc:pds-fail-%")
      );
      await db.delete(users).where(testUserPattern);

      await db.delete(boards).where(eq(boards.did, config.forumDid));
      await db.delete(categories).where(eq(categories.did, config.forumDid));
      await db.delete(roles).where(eq(roles.did, config.forumDid)); // cascades to role_permissions
      await db.delete(modActions).where(eq(modActions.did, config.forumDid));
      await db.delete(backfillErrors).catch(() => {});
      await db.delete(backfillProgress).catch(() => {});
      await db.delete(forums).where(eq(forums.did, config.forumDid));
      // Close the postgres.js connection pool to prevent connection exhaustion.
      // With many tests each calling createTestContext(), every call opens a new
      // pool. Without end(), the pool stays open and PostgreSQL hits max_connections.
      if (isPostgres) {
        await (db as any).$client?.end?.();
      }
    },
  } as TestContext;
}