import { describe, expect, it } from "@effect/vitest";
import { Effect, Fiber } from "effect";

import { collectTables } from "./executor";
import { createSqliteTestFumaDb, type SqliteTestFumaDb } from "./sqlite-test-db";
import { runSqliteOAuthClientGcMigration } from "./sqlite-oauth-client-gc-migration";

// Integration coverage for the local libSQL GC + backfill migration against a
// real SQLite schema (issue #1120, Part C). Complements the pure decision-matrix
// tests in oauth-gc.test.ts by exercising the actual SQL: seeded rows in, the
// migration runs, surviving rows asserted.

const TENANT = "t1";
const OWNER = "org";
const SUBJECT = "s1";

const withDb = <A>(body: (db: SqliteTestFumaDb) => Promise<A>): Promise<A> =>
  Effect.runPromise(
    Effect.acquireUseRelease(
      Effect.promise(() => createSqliteTestFumaDb({ tables: collectTables() })),
      (db) => Effect.promise(() => body(db)),
      (db) => Effect.promise(() => db.close()),
    ),
  );

const insertOAuthClient = (
  db: SqliteTestFumaDb,
  row: {
    readonly slug: string;
    readonly tokenUrl: string;
    readonly grant?: string;
    readonly resource?: string | null;
    readonly originKind?: string | null;
    readonly originIssuer?: string | null;
  },
): Promise<unknown> =>
  db.client.execute({
    sql: `INSERT INTO oauth_client
      (row_id, tenant, owner, subject, slug, authorization_url, token_url, "grant",
       client_id, resource, origin_kind, origin_issuer, created_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    args: [
      `oc_${row.slug}`,
      TENANT,
      OWNER,
      SUBJECT,
      row.slug,
      "https://as.example/authorize",
      row.tokenUrl,
      row.grant ?? "authorization_code",
      `client-${row.slug}`,
      row.resource ?? null,
      row.originKind ?? null,
      row.originIssuer ?? null,
      Date.now(),
    ],
  });

const insertConnection = (
  db: SqliteTestFumaDb,
  connectionName: string,
  clientSlug: string,
): Promise<unknown> =>
  db.client.execute({
    sql: `INSERT INTO connection
      (row_id, tenant, owner, subject, integration, name, template, provider, item_ids,
       oauth_client, oauth_client_owner, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    args: [
      `cn_${connectionName}`,
      TENANT,
      OWNER,
      SUBJECT,
      "acme",
      connectionName,
      "oauth",
      "memory",
      "{}",
      clientSlug,
      OWNER,
      Date.now(),
      Date.now(),
    ],
  });

const slugs = async (db: SqliteTestFumaDb): Promise<readonly string[]> => {
  const result = await db.client.execute("SELECT slug FROM oauth_client ORDER BY slug");
  return result.rows.map((r) => String(r.slug));
};

const issuerOf = async (db: SqliteTestFumaDb, slug: string): Promise<string | null> => {
  const result = await db.client.execute({
    sql: "SELECT origin_issuer FROM oauth_client WHERE slug = ?",
    args: [slug],
  });
  const value = result.rows[0]?.origin_issuer;
  return value == null ? null : String(value);
};

const originKindOf = async (db: SqliteTestFumaDb, slug: string): Promise<string | null> => {
  const result = await db.client.execute({
    sql: "SELECT origin_kind FROM oauth_client WHERE slug = ?",
    args: [slug],
  });
  const value = result.rows[0]?.origin_kind;
  return value == null ? null : String(value);
};

describe("runSqliteOAuthClientGcMigration", () => {
  it.effect("deletes orphaned DCR rows, keeps manual + referenced rows, backfills survivors", () =>
    Effect.gen(function* () {
      const outcome = yield* Effect.promise(() =>
        withDb(async (db) => {
          // Orphaned explicit DCR → delete.
          await insertOAuthClient(db, {
            slug: "dcr-cloudflare-com",
            tokenUrl: "https://oauth.cloudflare.com/token",
            resource: "https://cloudflare.example/mcp",
            originKind: "dynamic_client_registration",
            originIssuer: "https://cloudflare.com",
          });
          // Orphaned legacy heuristic DCR (null origin) → delete.
          await insertOAuthClient(db, {
            slug: "cloudflare-mcp-2",
            tokenUrl: "https://oauth.cloudflare.com/token",
            resource: "https://cloudflare.example/mcp",
            originKind: null,
          });
          // Referenced legacy DCR (has a connection) → keep + backfill issuer.
          await insertOAuthClient(db, {
            slug: "cloudflare-mcp",
            tokenUrl: "https://oauth.cloudflare.com/token",
            resource: "https://cloudflare.example/mcp",
            originKind: null,
            originIssuer: null,
          });
          await insertConnection(db, "cf-main", "cloudflare-mcp");
          // Orphaned manual app → keep (never GC a hand-registered app).
          await insertOAuthClient(db, {
            slug: "my-github-app",
            tokenUrl: "https://github.com/login/oauth/access_token",
            resource: null,
            originKind: "manual",
          });

          const applied = await Effect.runPromise(runSqliteOAuthClientGcMigration(db.client));
          const surviving = await slugs(db);
          const backfilled = await issuerOf(db, "cloudflare-mcp");
          const survivorKind = await originKindOf(db, "cloudflare-mcp");
          const manualKind = await originKindOf(db, "my-github-app");
          const manualIssuer = await issuerOf(db, "my-github-app");
          return {
            applied,
            surviving,
            backfilled,
            survivorKind,
            manualKind,
            manualIssuer,
          };
        }),
      );

      expect(outcome.applied).toEqual({
        deleted: 2,
        backfilled: 1,
        stampedDcr: 1,
        stampedManual: 0,
      });
      // Both orphaned DCR rows gone; the referenced DCR row and the manual app
      // survive.
      expect(outcome.surviving).toEqual(["cloudflare-mcp", "my-github-app"]);
      // The surviving legacy DCR row got its issuer backfilled from token_url's
      // registrable origin, so the per-AS reuse lookup can now key on it, and it
      // is stamped as an explicit DCR row.
      expect(outcome.backfilled).toBe("https://cloudflare.com");
      expect(outcome.survivorKind).toBe("dynamic_client_registration");
      // The manual app was already explicitly stamped, so its stamp is untouched
      // and it never gets an origin_issuer.
      expect(outcome.manualKind).toBe("manual");
      expect(outcome.manualIssuer).toBeNull();
    }),
  );

  it.effect("stamps a legacy null-origin manual survivor as manual with no issuer", () =>
    Effect.gen(function* () {
      const outcome = yield* Effect.promise(() =>
        withDb(async (db) => {
          // Legacy manual row: null origin_kind, auth-code, but NO resource, so
          // the classifier keeps it manual. It must survive, get an explicit
          // `manual` stamp, and NOT receive an origin_issuer.
          await insertOAuthClient(db, {
            slug: "legacy-manual",
            tokenUrl: "https://github.com/login/oauth/access_token",
            resource: null,
            originKind: null,
            originIssuer: null,
          });

          const applied = await Effect.runPromise(runSqliteOAuthClientGcMigration(db.client));
          const surviving = await slugs(db);
          const kind = await originKindOf(db, "legacy-manual");
          const issuer = await issuerOf(db, "legacy-manual");
          return { applied, surviving, kind, issuer };
        }),
      );

      expect(outcome.applied).toEqual({
        deleted: 0,
        backfilled: 0,
        stampedDcr: 0,
        stampedManual: 1,
      });
      expect(outcome.surviving).toEqual(["legacy-manual"]);
      expect(outcome.kind).toBe("manual");
      expect(outcome.issuer).toBeNull();
    }),
  );

  it.effect("is idempotent: a second run deletes nothing more and re-backfills nothing", () =>
    Effect.gen(function* () {
      const outcome = yield* Effect.promise(() =>
        withDb(async (db) => {
          await insertOAuthClient(db, {
            slug: "cloudflare-mcp-2",
            tokenUrl: "https://oauth.cloudflare.com/token",
            resource: "https://cloudflare.example/mcp",
            originKind: null,
          });
          await insertOAuthClient(db, {
            slug: "cloudflare-mcp",
            tokenUrl: "https://oauth.cloudflare.com/token",
            resource: "https://cloudflare.example/mcp",
            originKind: null,
            originIssuer: null,
          });
          await insertConnection(db, "cf-main", "cloudflare-mcp");

          const first = await Effect.runPromise(runSqliteOAuthClientGcMigration(db.client));
          const afterFirst = await slugs(db);
          const second = await Effect.runPromise(runSqliteOAuthClientGcMigration(db.client));
          const afterSecond = await slugs(db);
          return { first, afterFirst, second, afterSecond };
        }),
      );

      expect(outcome.first).toEqual({
        deleted: 1,
        backfilled: 1,
        stampedDcr: 1,
        stampedManual: 0,
      });
      // Second pass is a no-op: the orphan is already gone, the survivor's issuer
      // is set, and its origin_kind is stamped (so it is no longer a null-origin
      // candidate for any of delete / backfill / stamp).
      expect(outcome.second).toEqual({
        deleted: 0,
        backfilled: 0,
        stampedDcr: 0,
        stampedManual: 0,
      });
      expect(outcome.afterSecond).toEqual(outcome.afterFirst);
      expect(outcome.afterSecond).toEqual(["cloudflare-mcp"]);
    }),
  );

  it.effect("never deletes a manual app even when it is orphaned and MCP-shaped", () =>
    Effect.gen(function* () {
      const surviving = yield* Effect.promise(() =>
        withDb(async (db) => {
          // Explicit manual origin_kind must shield even an MCP-shaped slug from
          // the heuristic.
          await insertOAuthClient(db, {
            slug: "cloudflare-mcp",
            tokenUrl: "https://oauth.cloudflare.com/token",
            resource: "https://cloudflare.example/mcp",
            originKind: "manual",
          });
          await Effect.runPromise(runSqliteOAuthClientGcMigration(db.client));
          return slugs(db);
        }),
      );
      expect(surviving).toEqual(["cloudflare-mcp"]);
    }),
  );

  it.effect("no-op on an empty oauth_client table", () =>
    Effect.gen(function* () {
      const applied = yield* Effect.promise(() =>
        withDb((db) => Effect.runPromise(runSqliteOAuthClientGcMigration(db.client))),
      );
      expect(applied).toEqual({ deleted: 0, backfilled: 0, stampedDcr: 0, stampedManual: 0 });
    }),
  );

  it.effect("rolls back the transaction when the migration fiber is interrupted mid-run", () =>
    Effect.gen(function* () {
      const surviving = yield* Effect.promise(() =>
        withDb(async (db) => {
          // Orphaned DCR row: without rollback-on-interrupt, an interruption
          // landing after its DELETE (but before COMMIT) can still leave the
          // transaction committed by a later statement, or the DELETE alone
          // holding an open transaction — either way the row's fate becomes
          // implementation-dependent instead of "the whole run never happened."
          await insertOAuthClient(db, {
            slug: "dcr-cloudflare-com",
            tokenUrl: "https://oauth.cloudflare.com/token",
            resource: "https://cloudflare.example/mcp",
            originKind: "dynamic_client_registration",
            originIssuer: "https://cloudflare.com",
          });

          // Interrupt the migration's fiber right after its DELETE statement
          // executes (i.e. mid-transaction, well before COMMIT), by wrapping
          // the client so the DELETE's returned promise resolves only after
          // the outer fiber has been asked to interrupt.
          const realExecute = db.client.execute.bind(db.client);
          let releaseInterrupt: (() => void) | null = null;
          const interruptSignal = new Promise<void>((resolve) => {
            releaseInterrupt = resolve;
          });
          const wrappedClient: typeof db.client = new Proxy(db.client, {
            get(target, prop, receiver) {
              if (prop !== "execute") return Reflect.get(target, prop, receiver);
              return async (stmt: unknown) => {
                const sql = typeof stmt === "string" ? stmt : (stmt as { sql: string }).sql;
                const result = await realExecute(stmt as never);
                if (typeof sql === "string" && sql.startsWith("DELETE FROM oauth_client")) {
                  // Let the test's interrupt fire, then hand control back so the
                  // fiber observes the interruption as its very next step.
                  releaseInterrupt?.();
                  await new Promise((r) => setTimeout(r, 20));
                }
                return result;
              };
            },
          });

          const fiber = Effect.runFork(runSqliteOAuthClientGcMigration(wrappedClient));
          await interruptSignal;
          await Effect.runPromise(Fiber.interrupt(fiber));

          return slugs(db);
        }),
      );

      // Rolled back: the orphaned row is exactly as it was before the run, not
      // deleted (proving ROLLBACK ran) and not left half-mutated.
      expect(surviving).toEqual(["dcr-cloudflare-com"]);
    }),
  );
});