import { BUILTIN_ROLES, builtinPolicies } from "@premium-cms/auth";
import type { Kysely } from "kysely";
import { sql } from "kysely";

import { columnExists, currentTimestamp } from "../dialect-helpers.js";

/**
 * Roles → policies → routes.
 *
 * Adds the authorization model on top of the fixed role levels:
 *
 *   _emdash_roles          one per role; `level` is the legacy tier the role
 *                          most resembles (kept so code that has not been
 *                          taught about policies still has a floor)
 *   _emdash_policies       named bundles of rules (routes, permissions,
 *                          admin pages, …) stored as JSON
 *   _emdash_role_policies  which policies a role holds
 *
 * and links users, invites, signup domains and API tokens to it:
 *
 *   users.role_id, auth_tokens.role_id, allowed_domains.default_role_id
 *   _emdash_api_tokens.policies   JSON array of policy slugs; NULL means the
 *                                 token predates policies and carries scopes
 *
 * The five built-in roles are seeded with a policy each that reproduces the
 * old level exactly, and every existing row is backfilled from its level, so
 * upgrading changes nothing for anyone until an admin edits a policy.
 *
 * Built-in ids are deterministic (`role:admin`, `policy:core-admin`) so they
 * are identical across every environment and safe to reference from seeds.
 *
 * Every step is guarded so a retry after a partial run (D1 subrequest limit,
 * isolate cancellation — see #954) picks up where it left off.
 */

export const BUILTIN_ROLE_ID_PREFIX = "role:";
export const BUILTIN_POLICY_ID_PREFIX = "policy:";

export function builtinRoleId(slug: string): string {
	return `${BUILTIN_ROLE_ID_PREFIX}${slug}`;
}

export function builtinPolicyId(slug: string): string {
	return `${BUILTIN_POLICY_ID_PREFIX}${slug}`;
}

async function addColumnIfMissing(
	db: Kysely<unknown>,
	table: string,
	column: string,
	definition: string,
): Promise<void> {
	if (await columnExists(db, table, column)) return;
	await sql.raw(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`).execute(db);
}

export async function up(db: Kysely<unknown>): Promise<void> {
	// ── Tables ────────────────────────────────────────────────────────
	await db.schema
		.createTable("_emdash_roles")
		.ifNotExists()
		.addColumn("id", "text", (col) => col.primaryKey())
		.addColumn("slug", "text", (col) => col.notNull().unique())
		.addColumn("name", "text", (col) => col.notNull())
		.addColumn("description", "text")
		.addColumn("level", "integer", (col) => col.notNull().defaultTo(10))
		.addColumn("builtin", "integer", (col) => col.notNull().defaultTo(0))
		.addColumn("created_at", "text", (col) => col.defaultTo(currentTimestamp(db)))
		.addColumn("updated_at", "text", (col) => col.defaultTo(currentTimestamp(db)))
		.execute();

	await db.schema
		.createTable("_emdash_policies")
		.ifNotExists()
		.addColumn("id", "text", (col) => col.primaryKey())
		.addColumn("slug", "text", (col) => col.notNull().unique())
		.addColumn("name", "text", (col) => col.notNull())
		.addColumn("description", "text")
		.addColumn("builtin", "integer", (col) => col.notNull().defaultTo(0))
		.addColumn("rules", "text", (col) => col.notNull().defaultTo("{}"))
		.addColumn("created_at", "text", (col) => col.defaultTo(currentTimestamp(db)))
		.addColumn("updated_at", "text", (col) => col.defaultTo(currentTimestamp(db)))
		.execute();

	await db.schema
		.createTable("_emdash_role_policies")
		.ifNotExists()
		.addColumn("role_id", "text", (col) => col.notNull())
		.addColumn("policy_id", "text", (col) => col.notNull())
		.addColumn("sort_order", "integer", (col) => col.notNull().defaultTo(0))
		.addPrimaryKeyConstraint("role_policies_pk", ["role_id", "policy_id"])
		.addForeignKeyConstraint("role_policies_role_fk", ["role_id"], "_emdash_roles", ["id"], (cb) =>
			cb.onDelete("cascade"),
		)
		.addForeignKeyConstraint(
			"role_policies_policy_fk",
			["policy_id"],
			"_emdash_policies",
			["id"],
			(cb) => cb.onDelete("cascade"),
		)
		.execute();

	await db.schema
		.createIndex("idx_role_policies_policy")
		.ifNotExists()
		.on("_emdash_role_policies")
		.column("policy_id")
		.execute();

	// ── Links from existing tables ────────────────────────────────────
	// No FK constraints on the added columns: SQLite cannot add one to an
	// existing table without a rebuild, and D1 is SQLite. Referential
	// integrity is enforced in the repository (a role in use cannot be
	// deleted) and the resolver treats a dangling id as "no role".
	await addColumnIfMissing(db, "users", "role_id", "TEXT");
	await addColumnIfMissing(db, "auth_tokens", "role_id", "TEXT");
	await addColumnIfMissing(db, "allowed_domains", "default_role_id", "TEXT");
	await addColumnIfMissing(db, "_emdash_api_tokens", "policies", "TEXT");

	await db.schema
		.createIndex("idx_users_role_id")
		.ifNotExists()
		.on("users")
		.column("role_id")
		.execute();

	// ── Seed built-ins ────────────────────────────────────────────────
	const now = new Date().toISOString();

	for (const role of BUILTIN_ROLES) {
		await sql`
			INSERT INTO _emdash_roles (id, slug, name, description, level, builtin, created_at, updated_at)
			VALUES (${builtinRoleId(role.slug)}, ${role.slug}, ${role.name}, ${role.description}, ${role.level}, 1, ${now}, ${now})
			ON CONFLICT (id) DO NOTHING
		`.execute(db);
	}

	for (const policy of builtinPolicies()) {
		await sql`
			INSERT INTO _emdash_policies (id, slug, name, description, builtin, rules, created_at, updated_at)
			VALUES (${builtinPolicyId(policy.slug)}, ${policy.slug}, ${policy.name}, ${policy.description}, 1, ${JSON.stringify(policy.rules)}, ${now}, ${now})
			ON CONFLICT (id) DO NOTHING
		`.execute(db);
	}

	for (const role of BUILTIN_ROLES) {
		await sql`
			INSERT INTO _emdash_role_policies (role_id, policy_id, sort_order)
			VALUES (${builtinRoleId(role.slug)}, ${builtinPolicyId(role.policy)}, 0)
			ON CONFLICT (role_id, policy_id) DO NOTHING
		`.execute(db);
	}

	// ── Backfill from legacy levels ───────────────────────────────────
	// Highest matching tier wins, mirroring builtinRoleForLevel().
	const tiers = BUILTIN_ROLES.toSorted((a, b) => b.level - a.level);
	const caseExpr = (column: string) =>
		`CASE ${tiers
			.map((r) => `WHEN ${column} >= ${r.level} THEN '${builtinRoleId(r.slug)}'`)
			.join(" ")} ELSE '${builtinRoleId(tiers.at(-1)!.slug)}' END`;

	await sql.raw(`UPDATE users SET role_id = ${caseExpr("role")} WHERE role_id IS NULL`).execute(db);
	await sql
		.raw(
			`UPDATE auth_tokens SET role_id = ${caseExpr("role")} WHERE role_id IS NULL AND role IS NOT NULL`,
		)
		.execute(db);
	await sql
		.raw(
			`UPDATE allowed_domains SET default_role_id = ${caseExpr("default_role")} WHERE default_role_id IS NULL`,
		)
		.execute(db);
}

export async function down(db: Kysely<unknown>): Promise<void> {
	await db.schema.dropTable("_emdash_role_policies").ifExists().execute();
	await db.schema.dropTable("_emdash_policies").ifExists().execute();
	await db.schema.dropTable("_emdash_roles").ifExists().execute();
	// Added columns are left in place: SQLite needs a table rebuild to drop
	// them, and a NULL role_id is harmless to the pre-policy code paths.
}