import { afterEach, beforeEach, expect, test } from "bun:test";
import { eq, sql } from "drizzle-orm";
import { assertTestDb } from "@/lib/db/assert-test-db";
import { account, member, organization, user } from "@/lib/db/auth-schema";
import { db } from "@/lib/db/client";
import { sites } from "@/lib/db/domain-schema";

const RUN_DB = process.env.DB_TESTS === "1";
const SCOPE = "https://www.googleapis.com/auth/webmasters.readonly";
const ORG = "bf-org";
const FOREIGN_ORG = "bf-org-foreign";
const SITE = "bf-site";

// Doit rester byte-identique à la requête de drizzle/0070_workable_tiger_shark.sql.
// Les antislashs sont doublés parce qu'un template literal JS les consomme : la
// chaîne produite à l'exécution est bien celle du fichier de migration.
const BACKFILL = sql`
UPDATE sites s SET gsc_authorized_user_id = (
  SELECT m.user_id FROM member m
  JOIN account a ON a.user_id = m.user_id AND a.provider_id = 'google'
  WHERE m.organization_id = s.org_id
    AND m.role IN ('owner','admin')
    AND a.scope ~ '(^|[[:space:],])https://www\\.googleapis\\.com/auth/webmasters\\.readonly([[:space:],]|$)'
  ORDER BY CASE WHEN m.role = 'owner' THEN 0 ELSE 1 END, m.created_at, m.user_id
  LIMIT 1
) WHERE s.gsc_property IS NOT NULL AND s.gsc_authorized_user_id IS NULL`;

type SeedOpts = {
  ownerScope: string | null;
  adminScope: string | null;
  /** Rôles des deux membres. Surchargés pour vérifier l'exclusion de `member`. */
  ownerRole?: string;
  adminRole?: string;
  /** provider_id du compte de l'owner. Surchargé pour vérifier le filtre google. */
  ownerProvider?: string;
  /** null = site non mappé, donc hors périmètre du backfill. */
  gscProperty?: string | null;
  /** Seede une org étrangère dont l'owner porte le scope (test d'isolation). */
  foreignOrg?: boolean;
};

/** Nettoyage. Appelé aussi EN TÊTE de seed() : un run interrompu laisserait des
 *  lignes derrière lui et ferait échouer tous les runs suivants sur une
 *  violation de PK, avec un message sans rapport avec le vrai problème. */
async function cleanup() {
  await db.delete(sites).where(eq(sites.id, SITE));
  for (const id of ["bf-owner", "bf-admin", "bf-foreign"]) {
    await db.delete(account).where(eq(account.userId, id));
  }
  await db.delete(member).where(eq(member.organizationId, ORG));
  await db.delete(member).where(eq(member.organizationId, FOREIGN_ORG));
  await db.delete(organization).where(eq(organization.id, ORG));
  await db.delete(organization).where(eq(organization.id, FOREIGN_ORG));
  for (const id of ["bf-owner", "bf-admin", "bf-foreign"]) {
    await db.delete(user).where(eq(user.id, id));
  }
}

async function seed(opts: SeedOpts) {
  await cleanup();
  const gscProperty =
    opts.gscProperty === undefined ? "sc-domain:s.test" : opts.gscProperty;
  await db.insert(user).values([
    { id: "bf-owner", name: "o", email: "bf-owner@example.test" },
    { id: "bf-admin", name: "a", email: "bf-admin@example.test" },
  ]);
  await db
    .insert(organization)
    .values({ id: ORG, name: "bf", slug: ORG, createdAt: new Date() });
  await db.insert(member).values([
    {
      id: "bf-m1",
      organizationId: ORG,
      userId: "bf-owner",
      role: opts.ownerRole ?? "owner",
      createdAt: new Date(),
    },
    {
      id: "bf-m2",
      organizationId: ORG,
      userId: "bf-admin",
      role: opts.adminRole ?? "admin",
      createdAt: new Date(),
    },
  ]);
  if (opts.ownerScope !== null) {
    await db.insert(account).values({
      id: "bf-a1",
      accountId: "g1",
      providerId: opts.ownerProvider ?? "google",
      userId: "bf-owner",
      scope: opts.ownerScope,
      createdAt: new Date(),
      updatedAt: new Date(),
    });
  }
  if (opts.adminScope !== null) {
    await db.insert(account).values({
      id: "bf-a2",
      accountId: "g2",
      providerId: "google",
      userId: "bf-admin",
      scope: opts.adminScope,
      createdAt: new Date(),
      updatedAt: new Date(),
    });
  }
  if (opts.foreignOrg) {
    // Owner d'une AUTRE org, porteur du scope : ne doit jamais être retenu.
    await db.insert(user).values({
      id: "bf-foreign",
      name: "f",
      email: "bf-foreign@example.test",
    });
    await db.insert(organization).values({
      id: FOREIGN_ORG,
      name: "bf-f",
      slug: FOREIGN_ORG,
      createdAt: new Date(),
    });
    await db.insert(member).values({
      id: "bf-m3",
      organizationId: FOREIGN_ORG,
      userId: "bf-foreign",
      role: "owner",
      createdAt: new Date(),
    });
    await db.insert(account).values({
      id: "bf-a3",
      accountId: "g3",
      providerId: "google",
      userId: "bf-foreign",
      scope: SCOPE,
      createdAt: new Date(),
      updatedAt: new Date(),
    });
  }
  await db.insert(sites).values({
    id: SITE,
    orgId: ORG,
    name: "s",
    url: "https://s.test",
    gscProperty,
  });
}

async function authority(): Promise<string | null> {
  const [row] = await db
    .select({ a: sites.gscAuthorizedUserId })
    .from(sites)
    .where(eq(sites.id, SITE));
  return row?.a ?? null;
}

beforeEach(() => {
  if (RUN_DB) assertTestDb();
});

afterEach(async () => {
  if (!RUN_DB) return;
  await cleanup();
});

test.skipIf(!RUN_DB)(
  "owner sans Google + admin avec scope → l'admin",
  async () => {
    await seed({ ownerScope: null, adminScope: SCOPE });
    await db.execute(BACKFILL);
    expect(await authority()).toBe("bf-admin");
  },
);

test.skipIf(!RUN_DB)("owner ET admin avec scope → l'owner gagne", async () => {
  await seed({ ownerScope: SCOPE, adminScope: SCOPE });
  await db.execute(BACKFILL);
  expect(await authority()).toBe("bf-owner");
});

test.skipIf(!RUN_DB)("scopes séparés par des espaces → reconnus", async () => {
  await seed({ ownerScope: `openid email ${SCOPE}`, adminScope: null });
  await db.execute(BACKFILL);
  expect(await authority()).toBe("bf-owner");
});

// Le format de PRODUCTION : Better Auth stocke les scopes séparés par des
// virgules (cf. src/lib/gsc/scope.ts). C'est le chemin le plus critique.
test.skipIf(!RUN_DB)(
  "scopes séparés par des virgules (format Better Auth) → reconnus",
  async () => {
    await seed({ ownerScope: `openid,email,${SCOPE}`, adminScope: null });
    await db.execute(BACKFILL);
    expect(await authority()).toBe("bf-owner");
  },
);

test.skipIf(!RUN_DB)(
  "scope webmasters SANS readonly → non reconnu",
  async () => {
    await seed({
      ownerScope: "https://www.googleapis.com/auth/webmasters",
      adminScope: null,
    });
    await db.execute(BACKFILL);
    expect(await authority()).toBeNull();
  },
);

test.skipIf(!RUN_DB)("personne avec le scope → NULL", async () => {
  await seed({ ownerScope: "openid email", adminScope: null });
  await db.execute(BACKFILL);
  expect(await authority()).toBeNull();
});

test.skipIf(!RUN_DB)(
  "owner d'une AUTRE org avec le scope → jamais choisi",
  async () => {
    await seed({ ownerScope: null, adminScope: null, foreignOrg: true });
    await db.execute(BACKFILL);
    expect(await authority()).toBeNull();
  },
);

test.skipIf(!RUN_DB)("rôle member avec le scope → exclu", async () => {
  await seed({
    ownerScope: SCOPE,
    adminScope: SCOPE,
    ownerRole: "member",
    adminRole: "member",
  });
  await db.execute(BACKFILL);
  expect(await authority()).toBeNull();
});

test.skipIf(!RUN_DB)("compte non-google avec le scope → exclu", async () => {
  await seed({ ownerScope: SCOPE, adminScope: null, ownerProvider: "github" });
  await db.execute(BACKFILL);
  expect(await authority()).toBeNull();
});

test.skipIf(!RUN_DB)("site sans gsc_property → reste NULL", async () => {
  await seed({ ownerScope: SCOPE, adminScope: null, gscProperty: null });
  await db.execute(BACKFILL);
  expect(await authority()).toBeNull();
});

// Garde de ré-exécution : le backfill ne doit pas écraser une autorité déjà
// posée (ni la remettre à NULL si le compte a perdu le scope entre-temps).
test.skipIf(!RUN_DB)("autorité déjà définie → non écrasée", async () => {
  await seed({ ownerScope: SCOPE, adminScope: null });
  await db
    .update(sites)
    .set({ gscAuthorizedUserId: "bf-admin" })
    .where(eq(sites.id, SITE));
  await db.execute(BACKFILL);
  expect(await authority()).toBe("bf-admin");
});