import type { SQL } from 'bun'
import {
	createRecordedMigrationRunner,
	type MigrationLedgerStore,
	type MigrationLockCleanupFailure,
	type RecordedMigrationRunner,
	sanitizeMigrationStartupError,
	withReservedMigrationAdvisoryLock,
} from './migration-runner'

// Stable 64-bit namespace: "WISPMIGR". This serializes migrations across all
// main-app instances that share the primary database.
const MIGRATION_ADVISORY_LOCK_KEY = 0x574953504d494752n
const reportMigrationLockCleanupFailure = (kind: MigrationLockCleanupFailure): void => {
	// Keep this intentionally static: driver errors can contain a database URL.
	console.error(`[DB Migration] Advisory lock cleanup failed: ${kind}`)
}

type LocalMigrationRun = (name: string, fn: () => Promise<unknown>) => Promise<void>

interface SchemaMigrationExecutor {
	run(name: string, fn: (transaction: SQL) => Promise<unknown>): Promise<void>
}

/**
 * The ledger is the only DDL intentionally revisited on startup. Every other
 * schema/data-transition step is claimed, applied, and recorded in one primary
 * transaction, so a restart skips recorded work and retries interrupted work.
 */
const createSchemaMigrationExecutor = async (primaryDb: SQL): Promise<SchemaMigrationExecutor> => {
	await primaryDb`
		CREATE TABLE IF NOT EXISTS schema_migrations (
			name TEXT PRIMARY KEY,
			applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
		)
	`

	const store: MigrationLedgerStore<SQL> = {
		async loadAppliedNames(): Promise<ReadonlySet<string>> {
			const rows = await primaryDb<Array<{ name: string }>>`SELECT name FROM schema_migrations`
			return new Set(rows.map((row) => row.name))
		},
		async transaction(operation): Promise<void> {
			await primaryDb.begin(async (transaction) => {
				await operation({
					connection: transaction,
					async isMigrationApplied(name: string): Promise<boolean> {
						const rows = await transaction<Array<{ exists: boolean }>>`
							SELECT EXISTS(SELECT 1 FROM schema_migrations WHERE name = ${name}) AS exists
						`
						return rows[0]?.exists === true
					},
					async recordMigration(name: string): Promise<void> {
						await transaction`INSERT INTO schema_migrations (name) VALUES (${name})`
					},
				})
			})
		},
	}

	const runner: RecordedMigrationRunner<SQL> = await createRecordedMigrationRunner(store)
	return {
		async run(name, fn): Promise<void> {
			await runner.run({ name, run: fn })
		},
	}
}

const bootstrapSchema = async (runMigration: LocalMigrationRun, getDb: () => SQL): Promise<void> => {
	await runMigration('create oauth_states', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS oauth_states (
				key TEXT PRIMARY KEY,
				data TEXT NOT NULL,
				created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
			)
		`
	})

	await runMigration('create oauth_sessions', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS oauth_sessions (
				sub TEXT PRIMARY KEY,
				data TEXT NOT NULL,
				updated_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()),
				expires_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW()) + 2592000
			)
		`
	})

	await runMigration('create oauth_keys', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS oauth_keys (
				kid TEXT PRIMARY KEY,
				jwk TEXT NOT NULL,
				created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
			)
		`
	})

	await runMigration('create cookie_secrets', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS cookie_secrets (
				id TEXT PRIMARY KEY DEFAULT 'default',
				secret TEXT NOT NULL,
				created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
			)
		`
	})

	await runMigration('create service_identity_keys', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS service_identity_keys (
				id TEXT PRIMARY KEY DEFAULT 'default',
				public_key_multibase TEXT NOT NULL,
				private_key_multibase TEXT,
				created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()),
				updated_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
			)
		`
	})

	await runMigration('create domains', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS domains (
				domain TEXT PRIMARY KEY,
				did TEXT NOT NULL,
				rkey TEXT,
				created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
			)
		`
	})

	await runMigration('create custom_domains', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS custom_domains (
				id TEXT PRIMARY KEY,
				domain TEXT UNIQUE NOT NULL,
				did TEXT NOT NULL,
				rkey TEXT,
				verified BOOLEAN DEFAULT false,
				last_verified_at BIGINT,
				created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
			)
		`
	})

	await runMigration('create site_cache', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS site_cache (
				did TEXT NOT NULL,
				rkey TEXT NOT NULL,
				record_cid TEXT NOT NULL,
				file_cids JSONB NOT NULL DEFAULT '{}',
				cached_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()),
				updated_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()),
				cold_synced BOOLEAN NOT NULL DEFAULT true,
				PRIMARY KEY (did, rkey)
			)
		`
	})

	await runMigration('create site_settings_cache', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS site_settings_cache (
				did TEXT NOT NULL,
				rkey TEXT NOT NULL,
				record_cid TEXT NOT NULL,
				directory_listing BOOLEAN NOT NULL DEFAULT false,
				spa_mode TEXT,
				custom_404 TEXT,
				index_files JSONB,
				clean_urls BOOLEAN NOT NULL DEFAULT true,
				headers JSONB,
				cached_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()),
				updated_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()),
				PRIMARY KEY (did, rkey)
			)
		`
	})

	await runMigration('create supporter', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS supporter (
				did TEXT PRIMARY KEY,
				created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())
			)
		`
	})

	await runMigration('create webhook_secrets', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS webhook_secrets (
				did TEXT NOT NULL,
				name TEXT NOT NULL,
				token TEXT NOT NULL,
				created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
				last_rotated_at TIMESTAMPTZ,
				PRIMARY KEY (did, name)
			)
		`
	})

	await runMigration('create webhook_mutation_rate_limits', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS webhook_mutation_rate_limits (
				did TEXT NOT NULL,
				action TEXT NOT NULL CHECK (action IN ('create', 'delete')),
				window_started_at BIGINT NOT NULL,
				mutation_count INTEGER NOT NULL CHECK (mutation_count >= 0),
				PRIMARY KEY (did, action)
			)
		`
	})

	await runMigration('create private_sites', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS private_sites (
				site_id TEXT PRIMARY KEY,
				owner_did TEXT NOT NULL,
				name TEXT NOT NULL,
				file_count INTEGER NOT NULL DEFAULT 0 CONSTRAINT private_sites_file_count_nonnegative_check CHECK (file_count >= 0) CONSTRAINT private_sites_file_count_limit_check CHECK (file_count <= 500),
				total_bytes BIGINT NOT NULL DEFAULT 0 CONSTRAINT private_sites_total_bytes_nonnegative_check CHECK (total_bytes >= 0) CONSTRAINT private_sites_total_bytes_limit_check CHECK (total_bytes <= 104857600),
				state TEXT NOT NULL DEFAULT 'ready' CONSTRAINT private_sites_state_check CHECK (state IN ('staging', 'ready', 'deleting')),
				staging_lease_token_hash TEXT,
				staging_lease_expires_at TIMESTAMPTZ,
				expires_at TIMESTAMPTZ,
				created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
				updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
				CONSTRAINT private_sites_staging_lease_check CHECK (
					(state = 'staging' AND staging_lease_token_hash IS NOT NULL AND staging_lease_expires_at IS NOT NULL)
					OR (state <> 'staging' AND staging_lease_token_hash IS NULL AND staging_lease_expires_at IS NULL)
				)
			)
		`
	})

	await runMigration('create private_site_files', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS private_site_files (
				site_id TEXT NOT NULL REFERENCES private_sites(site_id) ON DELETE CASCADE,
				path TEXT NOT NULL,
				size BIGINT NOT NULL CONSTRAINT private_site_files_size_nonnegative_check CHECK (size >= 0) CONSTRAINT private_site_files_size_limit_check CHECK (size <= 104857600),
				mime_type TEXT,
				sha256 TEXT NOT NULL,
				created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
				PRIMARY KEY (site_id, path)
			)
		`
	})

	await runMigration('create private_site_shares', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS private_site_shares (
				share_id TEXT PRIMARY KEY,
				site_id TEXT NOT NULL REFERENCES private_sites(site_id) ON DELETE CASCADE,
				token_hash TEXT NOT NULL,
				token_prefix TEXT NOT NULL,
				label TEXT,
				expires_at TIMESTAMPTZ,
				revoked_at TIMESTAMPTZ,
				created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
				last_used_at TIMESTAMPTZ,
				audience_did TEXT
			)
		`
	})

	await runMigration('create private_site_sessions', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS private_site_sessions (
				session_id TEXT PRIMARY KEY,
				secret_hash TEXT NOT NULL,
				site_id TEXT NOT NULL REFERENCES private_sites(site_id) ON DELETE CASCADE,
				kind TEXT NOT NULL,
				owner_did TEXT,
				share_id TEXT REFERENCES private_site_shares(share_id) ON DELETE CASCADE,
				expires_at TIMESTAMPTZ NOT NULL,
				created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
			)
		`
	})

	await runMigration('create private_site_handoffs', async () => {
		await getDb()`
			CREATE TABLE IF NOT EXISTS private_site_handoffs (
				handoff_id TEXT PRIMARY KEY,
				secret_hash TEXT NOT NULL,
				site_id TEXT NOT NULL REFERENCES private_sites(site_id) ON DELETE CASCADE,
				owner_did TEXT,
				share_id TEXT REFERENCES private_site_shares(share_id) ON DELETE CASCADE,
				expires_at TIMESTAMPTZ NOT NULL,
				consumed_at TIMESTAMPTZ,
				created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
			)
		`
	})
}

const runWithPrimaryMigrationLock = async <T>(primaryDb: SQL, fn: (reservedDb: SQL) => Promise<T>): Promise<T> => {
	try {
		const reserved = await primaryDb.reserve()
		return await withReservedMigrationAdvisoryLock(
			{
				async setLockTimeout(): Promise<void> {
					await reserved`SET lock_timeout = '30s'`
				},
				async acquireLock(): Promise<void> {
					await reserved`SELECT pg_advisory_lock(${MIGRATION_ADVISORY_LOCK_KEY})`
				},
				async resetLockTimeout(): Promise<void> {
					await reserved`SET lock_timeout = DEFAULT`
				},
				async unlock(): Promise<void> {
					const rows = await reserved<Array<{ unlocked: boolean }>>`
						SELECT pg_advisory_unlock(${MIGRATION_ADVISORY_LOCK_KEY}) AS unlocked
					`
					if (!rows[0]?.unlocked) {
						throw new Error('Migration advisory lock was not held')
					}
				},
				release(): void {
					reserved.release()
				},
				close(): Promise<void> {
					return reserved.close({ timeout: 0 })
				},
			},
			async () => await fn(reserved),
			reportMigrationLockCleanupFailure,
		)
	} catch (error) {
		throw sanitizeMigrationStartupError('primary connection or advisory lock', error)
	}
}

export const runDatabaseMigrations = async (primaryDb: SQL): Promise<void> => {
	await runWithPrimaryMigrationLock(primaryDb, async (reservedDb) => {
		// The immutable migration bodies below are serial. Bind each existing tagged
		// SQL closure to its transaction while that one body is executing.
		let db = reservedDb
		const executor = await createSchemaMigrationExecutor(reservedDb)
		const runMigration: LocalMigrationRun = async (name, fn) =>
			await executor.run(name, async (transaction) => {
				const previousDb = db
				db = transaction
				try {
					return await fn()
				} finally {
					db = previousDb
				}
			})

		await bootstrapSchema(runMigration, () => db)

		// The restricted replica role cannot inspect pg_stat_wal_receiver directly.
		// This narrow definer function exposes only the non-sensitive fields the
		// health probe needs. Operations grants pg_read_all_stats to this trusted
		// migration-role owner and execute to the dedicated reader role after rollout.
		await runMigration('create replica receiver probe function', async () => {
			await db`
				CREATE OR REPLACE FUNCTION public.wisp_replica_receiver_status()
				RETURNS TABLE(receiver_streaming BOOLEAN, last_message_receipt_at TIMESTAMPTZ)
				LANGUAGE sql
				SECURITY DEFINER
				SET search_path = pg_catalog
				AS $function$
					SELECT status = 'streaming', last_msg_receipt_time
					FROM pg_catalog.pg_stat_wal_receiver
					LIMIT 1
				$function$
			`
			await db`REVOKE ALL ON FUNCTION public.wisp_replica_receiver_status() FROM PUBLIC`
		})

		// Add columns if they don't exist (for existing databases)
		await runMigration('add domains.rkey', async () => {
			await db`ALTER TABLE domains ADD COLUMN IF NOT EXISTS rkey TEXT`
		})

		await runMigration('add oauth_sessions.expires_at', async () => {
			await db`ALTER TABLE oauth_sessions ADD COLUMN IF NOT EXISTS expires_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW()) + 2592000`
		})

		await runMigration('add oauth_keys.created_at', async () => {
			await db`ALTER TABLE oauth_keys ADD COLUMN IF NOT EXISTS created_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())`
		})

		await runMigration('add oauth_states.expires_at', async () => {
			await db`ALTER TABLE oauth_states ADD COLUMN IF NOT EXISTS expires_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW()) + 3600`
		})

		await runMigration('add service_identity_keys.updated_at', async () => {
			await db`ALTER TABLE service_identity_keys ADD COLUMN IF NOT EXISTS updated_at BIGINT DEFAULT EXTRACT(EPOCH FROM NOW())`
		})

		await runMigration('add service_identity_keys.private_key_multibase', async () => {
			await db`ALTER TABLE service_identity_keys ADD COLUMN IF NOT EXISTS private_key_multibase TEXT`
		})

		// Existing rows are assumed already synced to S3 (firehose wrote them), so the
		// column defaults to true to avoid a thundering-herd re-download on rollout.
		// The on-demand path explicitly inserts cold_synced=false going forward.
		await runMigration('add site_cache.cold_synced', async () => {
			await db`ALTER TABLE site_cache ADD COLUMN IF NOT EXISTS cold_synced BOOLEAN NOT NULL DEFAULT true`
		})

		// Remove the unique constraint on domains.did to allow multiple domains per user
		await runMigration('drop legacy domains_did_key', async () => {
			await db`ALTER TABLE domains DROP CONSTRAINT IF EXISTS domains_did_key`
		})

		// Make custom_domains.rkey nullable and remove default
		await runMigration('custom_domains.rkey drop not null', async () => {
			await db`ALTER TABLE custom_domains ALTER COLUMN rkey DROP NOT NULL`
		})

		await runMigration('custom_domains.rkey drop default', async () => {
			await db`ALTER TABLE custom_domains ALTER COLUMN rkey DROP DEFAULT`
		})

		// Ensure existing domain mappings only point to owned cached sites before adding FK constraints.
		await runMigration('normalize invalid domains.rkey mappings', async () => {
			await db`
		            UPDATE domains d
		            SET rkey = NULL
		            WHERE rkey IS NOT NULL
		              AND NOT EXISTS (
		                  SELECT 1
		                  FROM site_cache s
		                  WHERE s.did = d.did
		                    AND s.rkey = d.rkey
		              )
		        `
		})

		await runMigration('normalize invalid custom_domains.rkey mappings', async () => {
			await db`
		            UPDATE custom_domains d
		            SET rkey = NULL
		            WHERE rkey IS NOT NULL
		              AND NOT EXISTS (
		                  SELECT 1
		                  FROM site_cache s
		                  WHERE s.did = d.did
		                    AND s.rkey = d.rkey
		              )
		        `
		})

		await runMigration('ensure unique custom_domains.domain', async () => {
			await db`CREATE UNIQUE INDEX IF NOT EXISTS idx_custom_domains_domain_unique ON custom_domains(domain)`
		})

		// Mapped site rkeys must refer to an existing cached site owned by the same DID.
		await runMigration('drop legacy fk_domains_site_owner', async () => {
			await db`ALTER TABLE domains DROP CONSTRAINT IF EXISTS fk_domains_site_owner`
		})

		await runMigration('drop legacy fk_custom_domains_site_owner', async () => {
			await db`ALTER TABLE custom_domains DROP CONSTRAINT IF EXISTS fk_custom_domains_site_owner`
		})

		await runMigration('add fk_domains_site_owner', async () => {
			await db`
				DO $$
				BEGIN
					IF NOT EXISTS (
						SELECT 1 FROM pg_constraint
						WHERE conname = 'fk_domains_site_owner' AND conrelid = 'domains'::regclass
					) THEN
						ALTER TABLE domains
							ADD CONSTRAINT fk_domains_site_owner
							FOREIGN KEY (did, rkey)
							REFERENCES site_cache(did, rkey)
							ON UPDATE CASCADE
							ON DELETE SET NULL;
					END IF;
				END $$;
			`
		})

		await runMigration('add fk_custom_domains_site_owner', async () => {
			await db`
				DO $$
				BEGIN
					IF NOT EXISTS (
						SELECT 1 FROM pg_constraint
						WHERE conname = 'fk_custom_domains_site_owner' AND conrelid = 'custom_domains'::regclass
					) THEN
						ALTER TABLE custom_domains
							ADD CONSTRAINT fk_custom_domains_site_owner
							FOREIGN KEY (did, rkey)
							REFERENCES site_cache(did, rkey)
							ON UPDATE CASCADE
							ON DELETE SET NULL;
					END IF;
				END $$;
			`
		})

		// Owner-visible analytics are stored as aggregate counters only. The site
		// cache foreign key makes site deletion the lifecycle boundary for all rows.
		await runMigration('create site_analytics_hourly', async () => {
			await db`
					CREATE TABLE IF NOT EXISTS site_analytics_hourly (
						owner_did TEXT NOT NULL,
						site_rkey TEXT NOT NULL,
						bucket_start TIMESTAMPTZ NOT NULL,
						requests BIGINT NOT NULL DEFAULT 0 CHECK (requests >= 0),
						html_responses BIGINT NOT NULL DEFAULT 0 CHECK (html_responses >= 0),
						status_2xx BIGINT NOT NULL DEFAULT 0 CHECK (status_2xx >= 0),
						status_3xx BIGINT NOT NULL DEFAULT 0 CHECK (status_3xx >= 0),
						status_4xx BIGINT NOT NULL DEFAULT 0 CHECK (status_4xx >= 0),
						status_5xx BIGINT NOT NULL DEFAULT 0 CHECK (status_5xx >= 0),
						updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
						PRIMARY KEY (owner_did, site_rkey, bucket_start),
						CONSTRAINT fk_site_analytics_owner
							FOREIGN KEY (owner_did, site_rkey)
							REFERENCES site_cache(did, rkey)
							ON UPDATE CASCADE
							ON DELETE CASCADE
					)
				`
		})

		await runMigration('create analytics_ingest_batches', async () => {
			await db`
					CREATE TABLE IF NOT EXISTS analytics_ingest_batches (
						batch_id UUID PRIMARY KEY,
						instance_id TEXT NOT NULL,
						received_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
					)
				`
		})

		// Private-site lifecycle rows from older deployments become ready before
		// the state constraint is introduced. These steps are ordered so every
		// existing row satisfies the final NOT NULL/CHECK invariants.
		await runMigration('add private_sites.state', async () => {
			await db`ALTER TABLE private_sites ADD COLUMN IF NOT EXISTS state TEXT NOT NULL DEFAULT 'ready'`
		})

		await runMigration('normalize private_sites.state', async () => {
			await db`
					UPDATE private_sites
					SET state = 'ready'
					WHERE state IS NULL OR state NOT IN ('staging', 'ready', 'deleting')
				`
		})

		await runMigration('set private_sites.state default', async () => {
			await db`ALTER TABLE private_sites ALTER COLUMN state SET DEFAULT 'ready'`
		})

		await runMigration('set private_sites.state not null', async () => {
			await db`ALTER TABLE private_sites ALTER COLUMN state SET NOT NULL`
		})

		await runMigration('add private_sites staging lease columns', async () => {
			await db`ALTER TABLE private_sites ADD COLUMN IF NOT EXISTS staging_lease_token_hash TEXT`
			await db`ALTER TABLE private_sites ADD COLUMN IF NOT EXISTS staging_lease_expires_at TIMESTAMPTZ`
		})

		// An older binary cannot renew these leases. Hide any such in-flight upload
		// before enforcing the invariant; the reaper will clean it safely.
		await runMigration('retire unleased private_sites staging rows', async () => {
			await db`
				UPDATE private_sites
				SET
					state = 'deleting',
					staging_lease_token_hash = NULL,
					staging_lease_expires_at = NULL,
					updated_at = NOW()
				WHERE state = 'staging'
			`
		})

		await runMigration('add private_sites_staging_lease_check', async () => {
			await db`
				DO $$
				BEGIN
					IF NOT EXISTS (
						SELECT 1 FROM pg_constraint
						WHERE conname = 'private_sites_staging_lease_check' AND conrelid = 'private_sites'::regclass
					) THEN
						ALTER TABLE private_sites
							ADD CONSTRAINT private_sites_staging_lease_check CHECK (
								(state = 'staging' AND staging_lease_token_hash IS NOT NULL AND staging_lease_expires_at IS NOT NULL)
								OR (state <> 'staging' AND staging_lease_token_hash IS NULL AND staging_lease_expires_at IS NULL)
							) NOT VALID;
					END IF;
				END $$;
			`
		})

		await runMigration('add private_sites_state_check', async () => {
			await db`
				DO $$
				BEGIN
					IF NOT EXISTS (
						SELECT 1 FROM pg_constraint
						WHERE conname = 'private_sites_state_check' AND conrelid = 'private_sites'::regclass
					) THEN
						ALTER TABLE private_sites
							ADD CONSTRAINT private_sites_state_check CHECK (state IN ('staging', 'ready', 'deleting'));
					END IF;
				END $$;
			`
		})

		await runMigration('normalize private_sites nonnegative counters', async () => {
			await db`
				UPDATE private_sites
				SET
					file_count = GREATEST(file_count, 0),
					total_bytes = GREATEST(total_bytes, 0)
				WHERE file_count < 0 OR total_bytes < 0
			`
		})

		await runMigration('normalize private_site_files.size', async () => {
			await db`UPDATE private_site_files SET size = 0 WHERE size < 0`
		})

		await runMigration('add private_sites_file_count_nonnegative_check', async () => {
			await db`
				DO $$
				BEGIN
					IF NOT EXISTS (
						SELECT 1 FROM pg_constraint
						WHERE conname = 'private_sites_file_count_nonnegative_check' AND conrelid = 'private_sites'::regclass
					) THEN
						ALTER TABLE private_sites
							ADD CONSTRAINT private_sites_file_count_nonnegative_check CHECK (file_count >= 0);
					END IF;
				END $$;
			`
		})

		await runMigration('add private_sites_total_bytes_nonnegative_check', async () => {
			await db`
				DO $$
				BEGIN
					IF NOT EXISTS (
						SELECT 1 FROM pg_constraint
						WHERE conname = 'private_sites_total_bytes_nonnegative_check' AND conrelid = 'private_sites'::regclass
					) THEN
						ALTER TABLE private_sites
							ADD CONSTRAINT private_sites_total_bytes_nonnegative_check CHECK (total_bytes >= 0);
					END IF;
				END $$;
			`
		})

		await runMigration('add private_site_files_size_nonnegative_check', async () => {
			await db`
				DO $$
				BEGIN
					IF NOT EXISTS (
						SELECT 1 FROM pg_constraint
						WHERE conname = 'private_site_files_size_nonnegative_check' AND conrelid = 'private_site_files'::regclass
					) THEN
						ALTER TABLE private_site_files
							ADD CONSTRAINT private_site_files_size_nonnegative_check CHECK (size >= 0);
					END IF;
				END $$;
			`
		})

		// Existing oversized rows remain readable. PostgreSQL enforces NOT VALID
		// checks for every new or changed row without scanning legacy data.
		await runMigration('add private_sites_file_count_limit_check', async () => {
			await db`
				DO $$
				BEGIN
					IF NOT EXISTS (
						SELECT 1 FROM pg_constraint
						WHERE conname = 'private_sites_file_count_limit_check' AND conrelid = 'private_sites'::regclass
					) THEN
						ALTER TABLE private_sites
							ADD CONSTRAINT private_sites_file_count_limit_check CHECK (file_count <= 500) NOT VALID;
					END IF;
				END $$;
			`
		})

		await runMigration('add private_sites_total_bytes_limit_check', async () => {
			await db`
				DO $$
				BEGIN
					IF NOT EXISTS (
						SELECT 1 FROM pg_constraint
						WHERE conname = 'private_sites_total_bytes_limit_check' AND conrelid = 'private_sites'::regclass
					) THEN
						ALTER TABLE private_sites
							ADD CONSTRAINT private_sites_total_bytes_limit_check CHECK (total_bytes <= 104857600) NOT VALID;
					END IF;
				END $$;
			`
		})

		await runMigration('add private_site_files_size_limit_check', async () => {
			await db`
				DO $$
				BEGIN
					IF NOT EXISTS (
						SELECT 1 FROM pg_constraint
						WHERE conname = 'private_site_files_size_limit_check' AND conrelid = 'private_site_files'::regclass
					) THEN
						ALTER TABLE private_site_files
							ADD CONSTRAINT private_site_files_size_limit_check CHECK (size <= 104857600) NOT VALID;
					END IF;
				END $$;
			`
		})

		// Seed initial supporter DID
		await runMigration('seed initial supporter', async () => {
			await db`
		            INSERT INTO supporter (did)
		            VALUES ('did:plc:ttdrpj45ibqunmfhdsb4zdwq')
		            ON CONFLICT (did) DO NOTHING
		        `
		})

		const migrations: Array<[string, () => Promise<unknown>]> = [
			[
				'idx_oauth_states_expires_at',
				async () => db`CREATE INDEX IF NOT EXISTS idx_oauth_states_expires_at ON oauth_states(expires_at)`,
			],
			[
				'idx_oauth_sessions_expires_at',
				async () => db`CREATE INDEX IF NOT EXISTS idx_oauth_sessions_expires_at ON oauth_sessions(expires_at)`,
			],
			[
				'idx_oauth_keys_created_at',
				async () => db`CREATE INDEX IF NOT EXISTS idx_oauth_keys_created_at ON oauth_keys(created_at)`,
			],
			['idx_domains_did_rkey', async () => db`CREATE INDEX IF NOT EXISTS idx_domains_did_rkey ON domains(did, rkey)`],
			[
				'idx_custom_domains_did',
				async () => db`CREATE INDEX IF NOT EXISTS idx_custom_domains_did ON custom_domains(did)`,
			],
			[
				'idx_custom_domains_did_rkey',
				async () => db`CREATE INDEX IF NOT EXISTS idx_custom_domains_did_rkey ON custom_domains(did, rkey)`,
			],
			[
				'idx_custom_domains_verified',
				async () => db`CREATE INDEX IF NOT EXISTS idx_custom_domains_verified ON custom_domains(verified)`,
			],
			['idx_site_cache_did', async () => db`CREATE INDEX IF NOT EXISTS idx_site_cache_did ON site_cache(did)`],
			[
				'idx_site_cache_updated',
				async () => db`CREATE INDEX IF NOT EXISTS idx_site_cache_updated ON site_cache(updated_at)`,
			],
			[
				'idx_site_analytics_hourly_bucket_start',
				async () =>
					db`CREATE INDEX IF NOT EXISTS idx_site_analytics_hourly_bucket_start ON site_analytics_hourly(bucket_start)`,
			],
			[
				'idx_analytics_ingest_batches_received_at',
				async () =>
					db`CREATE INDEX IF NOT EXISTS idx_analytics_ingest_batches_received_at ON analytics_ingest_batches(received_at)`,
			],
			[
				'idx_private_sites_owner',
				async () => db`CREATE INDEX IF NOT EXISTS idx_private_sites_owner ON private_sites(owner_did)`,
			],
			[
				'idx_private_sites_expires_at',
				async () => db`CREATE INDEX IF NOT EXISTS idx_private_sites_expires_at ON private_sites(expires_at)`,
			],
			[
				'idx_private_sites_state_updated_at',
				async () =>
					db`CREATE INDEX IF NOT EXISTS idx_private_sites_state_updated_at ON private_sites(state, updated_at)`,
			],
			[
				'idx_private_sites_staging_lease_expires_at',
				async () =>
					db`CREATE INDEX IF NOT EXISTS idx_private_sites_staging_lease_expires_at ON private_sites(staging_lease_expires_at) WHERE state = 'staging'`,
			],
			[
				'private_site_shares.audience_did',
				async () => db`ALTER TABLE private_site_shares ADD COLUMN IF NOT EXISTS audience_did TEXT`,
			],
			[
				'idx_private_site_shares_site',
				async () => db`CREATE INDEX IF NOT EXISTS idx_private_site_shares_site ON private_site_shares(site_id)`,
			],
			[
				'idx_private_site_shares_token_hash',
				async () =>
					db`CREATE INDEX IF NOT EXISTS idx_private_site_shares_token_hash ON private_site_shares(token_hash)`,
			],
			[
				'idx_private_site_shares_token_hash_unique',
				async () =>
					db`CREATE UNIQUE INDEX IF NOT EXISTS idx_private_site_shares_token_hash_unique ON private_site_shares(token_hash)`,
			],
			[
				'idx_private_sessions_secret',
				async () => db`CREATE INDEX IF NOT EXISTS idx_private_sessions_secret ON private_site_sessions(secret_hash)`,
			],
			[
				'idx_private_sessions_expires',
				async () => db`CREATE INDEX IF NOT EXISTS idx_private_sessions_expires ON private_site_sessions(expires_at)`,
			],
			[
				'private_site_handoffs.owner_did nullable',
				async () => db`ALTER TABLE private_site_handoffs ALTER COLUMN owner_did DROP NOT NULL`,
			],
			[
				'private_site_handoffs.share_id',
				async () =>
					db`ALTER TABLE private_site_handoffs ADD COLUMN IF NOT EXISTS share_id TEXT REFERENCES private_site_shares(share_id) ON DELETE CASCADE`,
			],
			[
				'idx_private_handoffs_secret',
				async () => db`CREATE INDEX IF NOT EXISTS idx_private_handoffs_secret ON private_site_handoffs(secret_hash)`,
			],
		]
		for (const [name, run] of migrations) {
			await runMigration(name, run)
		}
	})
}