// ---------------------------------------------------------------------------
// purgeOrganizationData — org deletion cascade
// ---------------------------------------------------------------------------
//
// Runs inside the Cloudflare Workers runtime against a real PGlite Postgres
// (scripts/test-globalsetup.ts), the same path api.ts uses per request. Seeds
// two orgs across every tenant table + blob namespace, purges one, and asserts:
//   - every executor tenant table row for the target org is gone
//   - org- and user-scoped secret blobs for the target org are gone
//   - the identity row is gone and its memberships cascade with it
//   - a second org's data is completely untouched
//   - the blob prefix match escapes LIKE wildcards (a `_` in the org id must
//     not widen the match to a look-alike namespace)
//
// Plus a SCHEMA-DRIFT guard (see "purge coverage" below): the purge list is
// hand-written, so a table added to the schema without being added to it would
// leave that org's rows behind forever, silently. The guard derives the set of
// org-owned tables from the schema itself rather than restating it.

import { describe, it, expect } from "@effect/vitest";
import { Effect } from "effect";
import { eq, getTableColumns, getTableName, inArray } from "drizzle-orm";

import { DbService } from "./db";
import type { DrizzleDb } from "./db";
import { makeUserStore } from "../auth/user-store";
import * as cloudSchema from "./schema";
import * as executorSchema from "./executor-schema";
import { memberships, accounts } from "./schema";
import {
  artifact,
  blob,
  connection,
  definition,
  integration,
  oauth_client,
  oauth_session,
  plugin_storage,
  subject,
  tool,
  tool_policy,
} from "./executor-schema";

const program = <A, E>(body: Effect.Effect<A, E, DbService>) =>
  Effect.runPromise(
    body.pipe(Effect.provide(DbService.Live), Effect.scoped) as Effect.Effect<A, E, never>,
  );

// Insert one row into every tenant table, plus an org- and a user-scoped blob,
// all owned by `tenant`. `tag` keeps unique indexes from colliding across orgs.
const seedTenant = async (db: DrizzleDb, tenant: string, tag: string) => {
  const now = new Date();
  await db.insert(integration).values({
    slug: `int-${tag}`,
    plugin_id: "p",
    created_at: now,
    updated_at: now,
    tenant,
  });
  await db.insert(connection).values({
    integration: "int",
    name: `conn-${tag}`,
    template: "t",
    provider: "pr",
    item_ids: [],
    created_at: now,
    updated_at: now,
    tenant,
    owner: "o",
    subject: "s",
  });
  await db.insert(oauth_client).values({
    slug: `oc-${tag}`,
    authorization_url: "u",
    token_url: "u",
    grant: "g",
    client_id: "c",
    created_at: now,
    tenant,
    owner: "o",
    subject: "s",
  });
  await db.insert(oauth_session).values({
    state: `state-${tag}`,
    client_slug: "cs",
    integration: "int",
    name: "n",
    template: "t",
    redirect_url: "r",
    payload: {},
    expires_at: 0n,
    created_at: now,
    tenant,
    owner: "o",
    subject: "s",
  });
  await db.insert(tool).values({
    integration: "int",
    connection: "conn",
    plugin_id: "p",
    name: `tool-${tag}`,
    description: "d",
    created_at: now,
    updated_at: now,
    tenant,
    owner: "o",
    subject: "s",
  });
  await db.insert(definition).values({
    integration: "int",
    connection: "conn",
    plugin_id: "p",
    name: `def-${tag}`,
    schema: {},
    created_at: now,
    tenant,
    owner: "o",
    subject: "s",
  });
  await db.insert(tool_policy).values({
    id: `tp-${tag}`,
    pattern: "*",
    action: "allow",
    position: "1",
    created_at: now,
    updated_at: now,
    tenant,
    owner: "o",
    subject: "s",
  });
  await db.insert(plugin_storage).values({
    plugin_id: "p",
    collection: "col",
    key: `k-${tag}`,
    data: {},
    created_at: now,
    updated_at: now,
    tenant,
    owner: "o",
    subject: "s",
  });

  await db.insert(subject).values({
    external_id: `acct-${tag}`,
    created_at: now,
    last_seen_at: BigInt(now.getTime()),
    tenant,
  });

  await db.insert(artifact).values({
    id: `art-${tag}`,
    title: "Dashboard",
    code: "export default function App() { return null; }",
    created_at: now,
    updated_at: now,
    tenant,
    owner: "o",
    subject: "s",
  });

  const orgNs = `o:${tenant}/plugin`;
  const userNs = `u:${tenant}:subject/plugin`;
  await db.insert(blob).values({
    namespace: orgNs,
    key: "k",
    value: "v",
    id: JSON.stringify([orgNs, "k"]),
  });
  await db.insert(blob).values({
    namespace: userNs,
    key: "k",
    value: "v",
    id: JSON.stringify([userNs, "k"]),
  });
};

const TENANT_TABLES = [
  integration,
  connection,
  oauth_client,
  oauth_session,
  tool,
  definition,
  tool_policy,
  plugin_storage,
  subject,
  artifact,
] as const;

// Tables that are NOT purged by org id, each with the reason it is exempt. Any
// schema table not carrying a `tenant` column must be listed here — that is
// what makes the drift guard below total rather than a spot check.
const NOT_ORG_OWNED: Record<string, string> = {
  // Purged, but by NAMESPACE PREFIX (`o:<org>/…` / `u:<org>:…`) rather than a
  // tenant column, so it cannot be checked by the column rule.
  blob: "org-scoped by namespace prefix, purged via the LIKE match",
  // Instance-wide, not owned by any org.
  private_executor_cloud_settings: "singleton instance settings, not org-scoped",
  // Identity mirror. `organizations` is deleted directly and `memberships`
  // cascades from its FK; `accounts` deliberately outlives the org.
  organizations: "the identity row itself, deleted directly",
  memberships: "cascades from the organizations FK",
  accounts: "shared across orgs — deliberately survives",
};

const countTenantRows = async (db: DrizzleDb, tenant: string): Promise<number> => {
  let total = 0;
  for (const table of TENANT_TABLES) {
    const rows = await db.select().from(table).where(eq(table.tenant, tenant));
    total += rows.length;
  }
  // Count by the exact namespaces `seedTenant` inserts. Matching on a `%tenant%`
  // LIKE here would reintroduce the very wildcard hazard under test.
  const blobs = await db
    .select()
    .from(blob)
    .where(inArray(blob.namespace, [`o:${tenant}/plugin`, `u:${tenant}:subject/plugin`]));
  return total + blobs.length;
};

describe("purgeOrganizationData", () => {
  it("removes all of one org's data and leaves other orgs untouched", async () => {
    // Underscore in the id is deliberate: an unescaped `_` in a LIKE pattern is
    // a single-char wildcard, so the escaping has to hold for this to be safe.
    const orgA = `org_del_${crypto.randomUUID().slice(0, 8)}`;
    const orgB = `org_keep_${crypto.randomUUID().slice(0, 8)}`;
    const accountId = `user_${crypto.randomUUID().slice(0, 8)}`;

    // A look-alike blob that only an UNescaped `_` wildcard would match:
    // `o:<orgA>/…` with the underscore replaced by another char.
    const trapNs = `o:${orgA.replace("_", "X")}/plugin`;

    await program(
      Effect.gen(function* () {
        const { db } = yield* DbService;
        yield* Effect.promise(async () => {
          const store = makeUserStore(db);
          await store.upsertOrganization({ id: orgA, name: "Delete Me" });
          await store.upsertOrganization({ id: orgB, name: "Keep Me" });
          await store.ensureAccount(accountId);
          await db.insert(memberships).values({ accountId, organizationId: orgA });
          await db.insert(memberships).values({ accountId, organizationId: orgB });
          await seedTenant(db, orgA, "a");
          await seedTenant(db, orgB, "b");
          await db.insert(blob).values({
            namespace: trapNs,
            key: "k",
            value: "v",
            id: JSON.stringify([trapNs, "k"]),
          });
        });
      }),
    );

    await program(
      Effect.gen(function* () {
        const { db } = yield* DbService;
        yield* Effect.promise(() => makeUserStore(db).deleteOrganizationCascade(orgA));
      }),
    );

    await program(
      Effect.gen(function* () {
        const { db } = yield* DbService;
        yield* Effect.promise(async () => {
          const store = makeUserStore(db);

          // Target org: every tenant row + blob gone, identity gone, membership
          // cascaded, but the shared account survives (it may join other orgs).
          expect(await countTenantRows(db, orgA)).toBe(0);
          expect(await store.getOrganization(orgA)).toBeNull();
          const orgAMemberships = await db
            .select()
            .from(memberships)
            .where(eq(memberships.organizationId, orgA));
          expect(orgAMemberships).toHaveLength(0);
          const account = await db.select().from(accounts).where(eq(accounts.id, accountId));
          expect(account, "the account outlives the org").toHaveLength(1);

          // The look-alike blob must survive: escaping keeps `_` literal.
          const trap = await db.select().from(blob).where(eq(blob.namespace, trapNs));
          expect(trap, "escaped LIKE must not match a look-alike namespace").toHaveLength(1);

          // Second org: fully intact.
          expect(await countTenantRows(db, orgB)).toBeGreaterThan(0);
          expect(await store.getOrganization(orgB)).not.toBeNull();
          const orgBMemberships = await db
            .select()
            .from(memberships)
            .where(eq(memberships.organizationId, orgB));
          expect(orgBMemberships).toHaveLength(1);
        });
      }),
    );
  }, 20_000);
});

// ---------------------------------------------------------------------------
// Purge coverage vs the schema
// ---------------------------------------------------------------------------
//
// The purge list in `org-deletion.ts` is hand-written, one `tx.delete(...)` per
// table. Adding a tenant table to the schema and forgetting that line leaves
// every deleted org's rows in it forever — no error, no failing test, and the
// data is exactly what a deletion was supposed to remove.
//
// So the expected set is DERIVED: a `tenant` column is what makes a table
// org-owned, and every table without one must be named in `NOT_ORG_OWNED` with
// its reason. A new table lands in one of the two buckets automatically, and
// both buckets fail loudly until it is handled.
describe("purge coverage", () => {
  // Every drizzle table the cloud DB is built from (the same two modules
  // `combinedSchema` spreads), by table name.
  const allTables = Object.values({ ...cloudSchema, ...executorSchema });

  const orgOwnedTables = allTables.filter((table) => "tenant" in getTableColumns(table));
  const orgOwnedNames = orgOwnedTables.map(getTableName).sort();
  const seededNames = TENANT_TABLES.map(getTableName).sort();

  it("classifies every schema table as org-owned or explicitly exempt", () => {
    const unclassified = allTables
      .map(getTableName)
      .filter((name) => !orgOwnedNames.includes(name) && !(name in NOT_ORG_OWNED));
    expect(
      unclassified,
      "a new table must either carry `tenant` (and be purged) or be listed in NOT_ORG_OWNED with a reason",
    ).toEqual([]);
  });

  it("purges every org-owned table in the schema", () => {
    // `TENANT_TABLES` is what this suite seeds and then asserts is empty after
    // the purge, so keeping it equal to the schema-derived set is what makes
    // the cascade test above cover the WHOLE schema rather than a snapshot of
    // it. A tenant table added tomorrow fails here first — the fix is to seed
    // it, which then fails the cascade test until `purgeOrganizationData`
    // deletes it too.
    expect(seededNames, "every org-owned table is seeded and checked by the cascade test").toEqual(
      orgOwnedNames,
    );
  });

  it("seeds a row in every org-owned table, so the cascade assertions are not vacuous", async () => {
    // Guards the other half: a table listed in TENANT_TABLES but never seeded
    // would count zero rows before AND after the purge, passing while proving
    // nothing. Assert the seed actually populates each one.
    const tenant = `org_seed_${crypto.randomUUID().slice(0, 8)}`;
    const empty = await program(
      Effect.gen(function* () {
        const { db } = yield* DbService;
        return yield* Effect.promise(async () => {
          await seedTenant(db, tenant, `seed${crypto.randomUUID().slice(0, 6)}`);
          const missing: Array<string> = [];
          for (const table of TENANT_TABLES) {
            const rows = await db.select().from(table).where(eq(table.tenant, tenant));
            if (rows.length === 0) missing.push(getTableName(table));
          }
          return missing;
        });
      }),
    );
    expect(empty, "seedTenant must insert a row into every table the cascade checks").toEqual([]);
  }, 20_000);
});