import type { Database } from 'bun:sqlite';
import { createHash } from 'node:crypto';
import { MAX_ARTICLE_LINK_SIZE } from '../utils/constants';
import { parseHttpUrl } from '../utils/validation';
import { ARTICLE_IDENTITY_CONSTRAINTS_MIGRATION_TAG, type MigrationAsset } from './migration-assets';

const LEGACY_BASELINE_MIGRATION_TAG = '0000_natural_the_hood';
let migrationSavepointSequence = 0;

/**
 * Run migration repair work atomically without Bun's `Database.transaction()`
 * wrapper. That wrapper retains internal BEGIN/COMMIT statements after returning,
 * which prevents the dedicated startup connection from closing synchronously.
 */
function runImmediateTransaction<T>(sqliteDb: Database, operation: () => T): T {
  const nested = sqliteDb.inTransaction;
  const savepoint = `rss_ai_migration_repair_${migrationSavepointSequence++}`;
  sqliteDb.run(nested ? `SAVEPOINT ${savepoint}` : 'BEGIN IMMEDIATE');

  try {
    const result = operation();
    sqliteDb.run(nested ? `RELEASE SAVEPOINT ${savepoint}` : 'COMMIT');
    return result;
  } catch (error) {
    const rollbackErrors: unknown[] = [];
    try {
      sqliteDb.run(nested ? `ROLLBACK TO SAVEPOINT ${savepoint}` : 'ROLLBACK');
    } catch (rollbackError) {
      rollbackErrors.push(rollbackError);
    }
    if (nested) {
      try {
        sqliteDb.run(`RELEASE SAVEPOINT ${savepoint}`);
      } catch (releaseError) {
        rollbackErrors.push(releaseError);
      }
    }
    if (rollbackErrors.length > 0) {
      throw new AggregateError([error, ...rollbackErrors], 'Migration repair failed and could not be rolled back');
    }
    throw error;
  }
}

interface LegacyColumn {
  readonly name: string;
  readonly notNull?: boolean;
  readonly primaryKey?: boolean;
  readonly hasDefault?: boolean;
}

const LEGACY_SCHEMA_COLUMNS: Readonly<Record<string, readonly LegacyColumn[]>> = {
  feeds: [
    { name: 'id', primaryKey: true },
    { name: 'title', notNull: true },
    { name: 'xml_url', notNull: true },
    { name: 'html_url' },
    { name: 'description' },
    { name: 'category' },
    { name: 'last_fetched_at' },
    { name: 'last_modified' },
    { name: 'etag' },
    { name: 'fetch_error' },
    { name: 'error_count', hasDefault: true },
    { name: 'is_active', hasDefault: true },
    { name: 'created_at' },
    { name: 'updated_at' },
  ],
  articles: [
    { name: 'id', primaryKey: true },
    { name: 'feed_id', notNull: true },
    { name: 'guid' },
    { name: 'title' },
    { name: 'link', notNull: true },
    { name: 'description' },
    { name: 'content' },
    { name: 'author' },
    { name: 'categories' },
    { name: 'published_at' },
    { name: 'is_read', hasDefault: true },
    { name: 'is_starred', hasDefault: true },
    { name: 'is_broken', hasDefault: true },
    { name: 'broken_reason' },
    { name: 'link_fixed' },
    { name: 'content_fetched_at' },
    { name: 'created_at' },
    { name: 'updated_at' },
  ],
  fetch_cache: [
    { name: 'id', primaryKey: true },
    { name: 'url', notNull: true },
    { name: 'etag' },
    { name: 'last_modified' },
    { name: 'content_hash' },
    { name: 'status_code' },
    { name: 'fetched_at' },
  ],
  opml_imports: [
    { name: 'id', primaryKey: true },
    { name: 'file_path', notNull: true },
    { name: 'file_name' },
    { name: 'feeds_imported', hasDefault: true },
    { name: 'feeds_skipped', hasDefault: true },
    { name: 'imported_at' },
  ],
};

function assertRecognizedLegacySchema(sqliteDb: Database): void {
  const missing: string[] = [];

  for (const [table, expectedColumns] of Object.entries(LEGACY_SCHEMA_COLUMNS)) {
    const exists = sqliteDb.query("SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?").get(table);
    if (!exists) {
      missing.push(`table ${table}`);
      continue;
    }

    const columns = new Map(
      (
        sqliteDb
          .query('SELECT name, "notnull" AS not_null, pk, dflt_value FROM pragma_table_info(?)')
          .all(table) as Array<{
          name: string;
          not_null: number;
          pk: number;
          dflt_value: string | number | null;
        }>
      ).map((column) => [column.name, column]),
    );
    for (const expected of expectedColumns) {
      const column = columns.get(expected.name);
      if (!column) {
        missing.push(`column ${table}.${expected.name}`);
        continue;
      }
      if (expected.primaryKey && column.pk === 0) missing.push(`primary key ${table}.${expected.name}`);
      if (expected.notNull && column.not_null === 0) missing.push(`NOT NULL ${table}.${expected.name}`);
      if (expected.hasDefault && column.dflt_value === null) missing.push(`default ${table}.${expected.name}`);
    }
  }

  for (const [table, column] of [
    ['feeds', 'xml_url'],
    ['fetch_cache', 'url'],
  ] as const) {
    const uniqueIndexes = sqliteDb
      .query('SELECT name FROM pragma_index_list(?) WHERE "unique" = 1')
      .all(table) as Array<{ name: string }>;
    const hasUniqueConstraint = uniqueIndexes.some((index) => {
      const indexedColumns = sqliteDb
        .query('SELECT name FROM pragma_index_info(?) ORDER BY seqno')
        .all(index.name) as Array<{ name: string }>;
      return indexedColumns.length === 1 && indexedColumns[0]?.name === column;
    });
    if (!hasUniqueConstraint) missing.push(`UNIQUE ${table}.${column}`);
  }

  const feedForeignKey = (
    sqliteDb.query('SELECT * FROM pragma_foreign_key_list(?)').all('articles') as Array<{
      table: string;
      from: string;
      to: string;
      on_delete: string;
    }>
  ).some(
    (foreignKey) =>
      foreignKey.table === 'feeds' &&
      foreignKey.from === 'feed_id' &&
      foreignKey.to === 'id' &&
      foreignKey.on_delete.toUpperCase() === 'CASCADE',
  );
  if (!feedForeignKey) missing.push('foreign key articles.feed_id -> feeds.id ON DELETE CASCADE');

  if (missing.length > 0) {
    throw new Error(`Database is not a recognized rss-ai legacy database (missing ${missing.join(', ')})`);
  }
}

/**
 * Seed the Drizzle migrations table for databases created by the old hand-written
 * migration system. Marks only the known legacy baseline as applied so that Drizzle
 * executes every later migration normally.
 */
export function seedDrizzleMigrationsForLegacyDb(sqliteDb: Database, migrations: readonly MigrationAsset[]): void {
  runImmediateTransaction(sqliteDb, () => {
    // BEGIN IMMEDIATE is acquired before detection, so concurrent initializers cannot
    // both observe an empty migration table and then seed the same baseline.
    const hasFeedsTable = sqliteDb.query("SELECT name FROM sqlite_master WHERE type='table' AND name='feeds'").get();
    const hasDrizzleTable = sqliteDb
      .query("SELECT name FROM sqlite_master WHERE type='table' AND name='__drizzle_migrations'")
      .get();
    let hasMigrationRecords = false;
    if (hasDrizzleTable) {
      const migrationCount = sqliteDb.query('SELECT COUNT(*) as cnt FROM __drizzle_migrations').get() as {
        cnt: number;
      };
      hasMigrationRecords = migrationCount.cnt > 0;
    }

    if (!hasFeedsTable && !hasMigrationRecords) {
      return; // Fresh DB, Drizzle will create everything.
    }

    // Migration records are metadata, not proof that the schema survived. Validate the
    // complete baseline before either trusting them or writing replacement metadata so a
    // partial database remains untouched rather than being silently restamped.
    assertRecognizedLegacySchema(sqliteDb);

    if (hasMigrationRecords) {
      return; // Already migrated to Drizzle and confirmed structurally complete.
    }

    const baseline = migrations.find((entry) => entry.tag === LEGACY_BASELINE_MIGRATION_TAG);
    if (!baseline) {
      throw new Error(`Embedded migrations do not contain legacy baseline ${LEGACY_BASELINE_MIGRATION_TAG}`);
    }
    const appliedMigrations = [baseline];

    // Some databases were created after 0001 but before Drizzle metadata was
    // introduced. Its ALTER TABLE is not idempotent, so recognize the durable
    // column it added, normalize its other effects, and record it as applied.
    const hasTitleOverridden =
      (
        sqliteDb
          .query('SELECT name FROM pragma_table_info(?) WHERE name = ?')
          .all('feeds', 'title_overridden') as Array<{
          name: string;
        }>
      ).length > 0;
    const titleOverrideMigration = hasTitleOverridden
      ? migrations.find((entry) => entry.tag === '0001_tranquil_polaris')
      : undefined;
    if (hasTitleOverridden) {
      if (!titleOverrideMigration) {
        throw new Error('Embedded migrations do not contain legacy title-override migration');
      }
      appliedMigrations.push(titleOverrideMigration);
    }

    sqliteDb.run(`
			CREATE TABLE IF NOT EXISTS __drizzle_migrations (
				id SERIAL PRIMARY KEY,
				hash text NOT NULL,
				created_at numeric
			)
		`);

    repairMigrationEffects(sqliteDb, baseline.tag);
    if (titleOverrideMigration) {
      repairMigrationEffects(sqliteDb, titleOverrideMigration.tag);
    }
    for (const migration of appliedMigrations) {
      const hash = createHash('sha256').update(migration.sql).digest('hex');
      sqliteDb.run('INSERT INTO __drizzle_migrations (hash, created_at) VALUES (?, ?)', [hash, migration.when]);
    }

    // Drop the old schema_version table if it exists.
    sqliteDb.run('DROP TABLE IF EXISTS schema_version');
  });
}

/**
 * Bring stale entries in `__drizzle_migrations` up to date with the current migration files.
 *
 * `seedDrizzleMigrationsForLegacyDb` records the file-content hash at seeding time. If a
 * migration file's content drifts after the user's DB was seeded — either because the
 * file itself was edited or because the user installed an earlier published version
 * with different content — Drizzle's `migrate()` sees a hash mismatch and tries to
 * re-run the migration. That fails when the migration's effects are already applied
 * (DROP an index that's gone, ALTER a column that exists). Per-migration repair logic
 * lives in `repairMigrationEffects` so the schema is brought up to a consistent state
 * before the hash is rewritten.
 */
export function repairStaleMigrationHashes(sqliteDb: Database, migrations: readonly MigrationAsset[]): void {
  runImmediateTransaction(sqliteDb, () => {
    const hasDrizzleTable = sqliteDb
      .query("SELECT name FROM sqlite_master WHERE type='table' AND name='__drizzle_migrations'")
      .get();
    if (!hasDrizzleTable) return;

    // Use rowid because the legacy seed function created the table with `id SERIAL PRIMARY
    // KEY`, which SQLite does not honor as auto-increment — existing rows have NULL ids.
    const records = sqliteDb
      .query('SELECT rowid AS rid, hash, created_at FROM __drizzle_migrations ORDER BY rid')
      .all() as Array<{
      rid: number;
      hash: string;
      created_at: number;
    }>;
    if (records.length === 0) return;

    const expectedMigrations = migrations.map((entry) => ({
      entry,
      expectedHash: createHash('sha256').update(entry.sql).digest('hex'),
    }));

    // Rows sharing created_at claim the same migration identity. Collapse them before
    // matching so a duplicate baseline row can never be interpreted as migration 0001.
    const recordsByCreatedAt = new Map<number, typeof records>();
    for (const record of records) {
      const createdAt = Number(record.created_at);
      const group = recordsByCreatedAt.get(createdAt) ?? [];
      group.push(record);
      recordsByCreatedAt.set(createdAt, group);
    }

    const normalizedRecords: typeof records = [];
    for (const [createdAt, group] of recordsByCreatedAt) {
      const expected = expectedMigrations.find((migration) => migration.entry.when === createdAt);
      const canonical = group.find((record) => record.hash === expected?.expectedHash) ?? group[0];
      if (!canonical) continue;
      normalizedRecords.push(canonical);
      for (const duplicate of group) {
        if (duplicate.rid !== canonical.rid) {
          sqliteDb.run('DELETE FROM __drizzle_migrations WHERE rowid = ?', [duplicate.rid]);
        }
      }
    }

    const matches = new Map<number, (typeof records)[number]>();
    const claimedRecords = new Set<number>();
    const assignMatches = (
      predicate: (migration: (typeof expectedMigrations)[number], record: (typeof records)[number]) => boolean,
    ): void => {
      for (let i = 0; i < expectedMigrations.length; i++) {
        if (matches.has(i)) continue;
        const migration = expectedMigrations[i];
        if (!migration) continue;
        const record = normalizedRecords.find(
          (candidate) => !claimedRecords.has(candidate.rid) && predicate(migration, candidate),
        );
        if (!record) continue;
        matches.set(i, record);
        claimedRecords.add(record.rid);
      }
    };

    // Prefer an exact identity, then the stable journal timestamp, then the SQL hash.
    // A final singleton fallback preserves repairability for an unambiguous migration
    // whose historical timestamp and SQL both drifted.
    assignMatches(
      (migration, record) =>
        Number(record.created_at) === migration.entry.when && record.hash === migration.expectedHash,
    );
    assignMatches((migration, record) => Number(record.created_at) === migration.entry.when);
    assignMatches((migration, record) => record.hash === migration.expectedHash);

    const unmatchedMigrationIndexes = expectedMigrations
      .map((_, index) => index)
      .filter((index) => !matches.has(index));
    const unmatchedRecords = normalizedRecords.filter((record) => !claimedRecords.has(record.rid));
    if (unmatchedMigrationIndexes.length === 1 && unmatchedRecords.length === 1) {
      const migrationIndex = unmatchedMigrationIndexes[0];
      const record = unmatchedRecords[0];
      if (migrationIndex !== undefined && record) matches.set(migrationIndex, record);
    }

    const appliedTags = new Set(
      [...matches.keys()]
        .map((migrationIndex) => expectedMigrations[migrationIndex]?.entry.tag)
        .filter((tag): tag is string => tag !== undefined),
    );

    for (const [migrationIndex, record] of matches) {
      const migration = expectedMigrations[migrationIndex];
      if (!migration) continue;
      const { entry, expectedHash } = migration;
      if (record.hash === expectedHash && record.created_at === entry.when) continue;

      // 0003's effects must be repaired only after 0002 has rebuilt Articles and
      // duplicate rows have been removed. initDb performs that deferred repair
      // in the correct order before it trusts the rewritten migration hash.
      if (record.hash !== expectedHash && entry.tag !== ARTICLE_IDENTITY_CONSTRAINTS_MIGRATION_TAG) {
        repairMigrationEffects(sqliteDb, entry.tag, appliedTags);
      }
      // Drizzle's migrate() decides what to run by comparing `created_at` against the
      // journal's `when` — not by hash. Update both so future boots stay in sync.
      sqliteDb.run('UPDATE __drizzle_migrations SET hash = ?, created_at = ? WHERE rowid = ?', [
        expectedHash,
        entry.when,
        record.rid,
      ]);
    }
  });
}

/**
 * Idempotently apply the effects of a migration. Used when a stale hash signals the
 * migration content drifted after the user's DB was seeded. Each branch must mirror
 * the SQL in the corresponding migration file but be safe to re-run on any state.
 */
function repairMigrationEffects(sqliteDb: Database, tag: string, appliedTags: ReadonlySet<string> = new Set()): void {
  if (tag === '0000_natural_the_hood') {
    // Restore the generated baseline indexes that old hand-written databases may lack
    // before declaring 0000 applied. In particular, 0001 expects to drop the named
    // fetch-cache index and is deliberately allowed to run through Drizzle.
    sqliteDb.run('CREATE INDEX IF NOT EXISTS feeds_category_idx ON feeds(category)');
    sqliteDb.run('CREATE INDEX IF NOT EXISTS feeds_active_idx ON feeds(is_active)');
    sqliteDb.run('CREATE INDEX IF NOT EXISTS articles_feed_idx ON articles(feed_id)');
    sqliteDb.run('CREATE INDEX IF NOT EXISTS articles_guid_idx ON articles(guid)');
    sqliteDb.run('CREATE INDEX IF NOT EXISTS articles_link_idx ON articles(link)');
    sqliteDb.run('CREATE INDEX IF NOT EXISTS articles_published_idx ON articles(published_at)');
    sqliteDb.run('CREATE INDEX IF NOT EXISTS articles_read_idx ON articles(is_read)');
    sqliteDb.run('CREATE INDEX IF NOT EXISTS articles_broken_idx ON articles(is_broken)');
    sqliteDb.run('CREATE INDEX IF NOT EXISTS articles_feed_read_idx ON articles(feed_id, is_read)');
    sqliteDb.run('CREATE INDEX IF NOT EXISTS articles_feed_pub_idx ON articles(feed_id, published_at)');
    sqliteDb.run('CREATE INDEX IF NOT EXISTS articles_author_idx ON articles(author)');
    sqliteDb.run('CREATE INDEX IF NOT EXISTS articles_starred_idx ON articles(is_starred)');
    // 0001 deliberately removes this redundant index. Repairing an older 0000
    // hash on a fully migrated database must not resurrect an effect that a
    // later applied migration already superseded.
    if (!appliedTags.has('0001_tranquil_polaris')) {
      sqliteDb.run('CREATE INDEX IF NOT EXISTS fetch_cache_url_idx ON fetch_cache(url)');
    }
  }
  if (tag === '0001_tranquil_polaris') {
    sqliteDb.run('DROP INDEX IF EXISTS fetch_cache_url_idx');
    const cols = sqliteDb.query('PRAGMA table_info(feeds)').all() as Array<{ name: string }>;
    if (!cols.some((c) => c.name === 'title_overridden')) {
      sqliteDb.run('ALTER TABLE feeds ADD COLUMN title_overridden integer DEFAULT 0');
    }
    sqliteDb.run('CREATE INDEX IF NOT EXISTS articles_content_fetched_at_idx ON articles(content_fetched_at)');
  }
  if (tag === '0004_elite_the_spike') {
    const cols = sqliteDb.query('PRAGMA table_info(feeds)').all() as Array<{ name: string }>;
    if (!cols.some((column) => column.name === 'error_category')) {
      sqliteDb.run('ALTER TABLE feeds ADD COLUMN error_category text');
    }
  }
  if (tag === '0005_lean_magneto') {
    sqliteDb.run(`CREATE TABLE IF NOT EXISTS maintenance_cursors (
      key text PRIMARY KEY NOT NULL,
      last_id integer DEFAULT 0 NOT NULL,
      updated_at integer NOT NULL
    )`);
  }
}

/**
 * The raw, durable audit table used by the article identity preparation phase.
 * It deliberately has no foreign keys: the row is a recovery record and must
 * survive a later deletion of either the original Feed or its survivor Article.
 */
export const ARTICLE_IDENTITY_REPAIR_BACKUPS_TABLE = 'article_identity_repair_backups';

type DuplicateReason = 'guid' | 'link';

interface StoredArticle {
  id: number;
  feed_id: number;
  guid: string | null;
  title: string | null;
  link: string;
  description: string | null;
  content: string | null;
  author: string | null;
  categories: string | null;
  published_at: number | null;
  is_read: number;
  is_starred: number;
  is_broken: number;
  broken_reason: string | null;
  link_fixed: string | null;
  content_fetched_at: number | null;
  created_at: number | null;
  updated_at: number | null;
}

interface StoredArticleIdentity {
  id: number;
  feed_id: number;
  guid: string | null;
  link: string;
}

export interface ArticleIdentityRepairResult {
  removed: number;
}

export interface ArticleIdentityRepairOptions {
  /** Maximum number of identity-only rows materialized by one keyset page. */
  readonly batchSize?: number;
  /** Optional diagnostic hook invoked after each nonempty identity page is read. */
  readonly onPageRead?: (rowCount: number) => void;
}

export const ARTICLE_IDENTITY_REPAIR_BATCH_SIZE = 256;

function createArticleIdentityRepairBackupsTable(sqliteDb: Database): void {
  sqliteDb.run(`
    CREATE TABLE IF NOT EXISTS ${ARTICLE_IDENTITY_REPAIR_BACKUPS_TABLE} (
      id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
      article_id integer NOT NULL,
      survivor_article_id integer NOT NULL,
      reason text NOT NULL,
      removed_at integer NOT NULL,
      feed_id integer NOT NULL,
      guid text,
      title text,
      link text NOT NULL,
      description text,
      content text,
      author text,
      categories text,
      published_at integer,
      is_read integer NOT NULL,
      is_starred integer NOT NULL,
      is_broken integer NOT NULL,
      broken_reason text,
      link_fixed text,
      content_fetched_at integer,
      created_at integer,
      updated_at integer
    )
  `);
  sqliteDb.run(
    'CREATE UNIQUE INDEX IF NOT EXISTS article_identity_repair_backups_article_id_unique ON article_identity_repair_backups (article_id)',
  );
  sqliteDb.run(
    'CREATE INDEX IF NOT EXISTS article_identity_repair_backups_survivor_idx ON article_identity_repair_backups (survivor_article_id)',
  );
}

interface IndexListRow {
  name: string;
  is_unique: number;
  partial: number;
}

function hasUniqueIndex(
  sqliteDb: Database,
  table: string,
  name: string,
  columns: readonly string[],
  partial = false,
): boolean {
  const index = (
    sqliteDb.query('SELECT name, "unique" AS is_unique, partial FROM pragma_index_list(?)').all(table) as IndexListRow[]
  ).find((candidate) => candidate.name === name);
  if (index?.is_unique !== 1 || Boolean(index.partial) !== partial) return false;
  const actualColumns = sqliteDb.query('SELECT name FROM pragma_index_info(?) ORDER BY seqno').all(name) as Array<{
    name: string;
  }>;
  return (
    actualColumns.length === columns.length && actualColumns.every((column, index) => column.name === columns[index])
  );
}

function hasIndex(
  sqliteDb: Database,
  table: string,
  name: string,
  columns: readonly string[],
  unique: boolean,
): boolean {
  const index = (
    sqliteDb.query('SELECT name, "unique" AS is_unique, partial FROM pragma_index_list(?)').all(table) as IndexListRow[]
  ).find((candidate) => candidate.name === name);
  if (!index || Boolean(index.is_unique) !== unique) return false;
  const actualColumns = sqliteDb.query('SELECT name FROM pragma_index_info(?) ORDER BY seqno').all(name) as Array<{
    name: string;
  }>;
  return (
    actualColumns.length === columns.length && actualColumns.every((column, index) => column.name === columns[index])
  );
}

/**
 * A cheap sqlite_master/pragma guard used on every startup. Once 0003 has run,
 * this avoids replaying or scanning the full Articles table again.
 */
export function articleIdentityConstraintsInstalled(sqliteDb: Database): boolean {
  if (!hasUniqueIndex(sqliteDb, 'articles', 'articles_feed_guid_unique', ['feed_id', 'guid'], true)) return false;
  if (!hasUniqueIndex(sqliteDb, 'articles', 'articles_feed_link_unique', ['feed_id', 'link'])) return false;
  const guidIndexSql = sqliteDb
    .query("SELECT sql FROM sqlite_master WHERE type = 'index' AND name = 'articles_feed_guid_unique'")
    .get() as { sql: string | null } | null;
  const normalized = guidIndexSql?.sql?.toLowerCase().replace(/\s|`|"|\[|\]/g, '') ?? '';
  return /where(?:articles\.)?guidisnotnulland(?:articles\.)?guid<>''/.test(normalized);
}

const ARTICLE_IDENTITY_REPAIR_BACKUP_COLUMNS = [
  'id',
  'article_id',
  'survivor_article_id',
  'reason',
  'removed_at',
  'feed_id',
  'guid',
  'title',
  'link',
  'description',
  'content',
  'author',
  'categories',
  'published_at',
  'is_read',
  'is_starred',
  'is_broken',
  'broken_reason',
  'link_fixed',
  'content_fetched_at',
  'created_at',
  'updated_at',
] as const;

export function articleIdentityRepairAuditStorageInstalled(sqliteDb: Database): boolean {
  const columns = sqliteDb
    .query('SELECT name FROM pragma_table_info(?) ORDER BY cid')
    .all(ARTICLE_IDENTITY_REPAIR_BACKUPS_TABLE) as Array<{ name: string }>;
  return (
    columns.length === ARTICLE_IDENTITY_REPAIR_BACKUP_COLUMNS.length &&
    columns.every((column, index) => column.name === ARTICLE_IDENTITY_REPAIR_BACKUP_COLUMNS[index]) &&
    hasIndex(
      sqliteDb,
      ARTICLE_IDENTITY_REPAIR_BACKUPS_TABLE,
      'article_identity_repair_backups_article_id_unique',
      ['article_id'],
      true,
    ) &&
    hasIndex(
      sqliteDb,
      ARTICLE_IDENTITY_REPAIR_BACKUPS_TABLE,
      'article_identity_repair_backups_survivor_idx',
      ['survivor_article_id'],
      false,
    )
  );
}

/** Ensure the raw audit schema remains aligned with 0003 without scanning Articles. */
export function ensureArticleIdentityRepairAuditStorage(sqliteDb: Database): void {
  createArticleIdentityRepairBackupsTable(sqliteDb);
  if (!articleIdentityRepairAuditStorageInstalled(sqliteDb)) {
    throw new Error(`${ARTICLE_IDENTITY_REPAIR_BACKUPS_TABLE} does not match the 0003 audit schema`);
  }
}

/**
 * Last-resort repair for a stale 0003 journal row whose unique-index effects
 * are absent. Call only after duplicate cleanup; it deliberately replaces only
 * the two migration-owned index names with their intended unique definitions.
 */
export function installArticleIdentityConstraintIndexes(sqliteDb: Database): void {
  ensureArticleIdentityRepairAuditStorage(sqliteDb);
  sqliteDb.run('DROP INDEX IF EXISTS articles_feed_guid_unique');
  sqliteDb.run('DROP INDEX IF EXISTS articles_feed_link_unique');
  sqliteDb.run(
    "CREATE UNIQUE INDEX articles_feed_guid_unique ON articles (feed_id, guid) WHERE guid IS NOT NULL AND guid <> ''",
  );
  sqliteDb.run('CREATE UNIQUE INDEX articles_feed_link_unique ON articles (feed_id, link)');
  if (!articleIdentityConstraintsInstalled(sqliteDb)) {
    throw new Error('Unable to install article identity unique indexes');
  }
}

function hasGuidIdentity(guid: string | null): guid is string {
  return guid !== null && guid !== '';
}

function canonicalLegacyArticleLink(link: string): string {
  if (link.length > MAX_ARTICLE_LINK_SIZE) return link;
  const parsed = parseHttpUrl(link);
  return parsed.valid && parsed.value.length <= MAX_ARTICLE_LINK_SIZE ? parsed.value : link;
}

function textLength(value: string | null): number {
  return value?.trim().length ?? 0;
}

function isNewer(incoming: StoredArticle, current: StoredArticle): boolean {
  const incomingTimestamp = incoming.updated_at ?? incoming.created_at ?? Number.NEGATIVE_INFINITY;
  const currentTimestamp = current.updated_at ?? current.created_at ?? Number.NEGATIVE_INFINITY;
  return incomingTimestamp > currentTimestamp;
}

function newestUsefulText(
  current: string | null,
  incoming: string | null,
  currentRow: StoredArticle,
  incomingRow: StoredArticle,
): string | null {
  const currentLength = textLength(current);
  const incomingLength = textLength(incoming);
  if (currentLength === 0) return incomingLength === 0 ? current : incoming;
  if (incomingLength === 0) return current;
  return isNewer(incomingRow, currentRow) ? incoming : current;
}

function newestUsefulTimestamp(
  current: number | null,
  incoming: number | null,
  currentRow: StoredArticle,
  incomingRow: StoredArticle,
): number | null {
  if (current === null) return incoming;
  if (incoming === null) return current;
  return isNewer(incomingRow, currentRow) ? incoming : current;
}

function earliestTimestamp(current: number | null, incoming: number | null): number | null {
  if (current === null) return incoming;
  if (incoming === null) return current;
  return Math.min(current, incoming);
}

function latestTimestamp(current: number | null, incoming: number | null): number | null {
  if (current === null) return incoming;
  if (incoming === null) return current;
  return Math.max(current, incoming);
}

function mergedContent(
  current: StoredArticle,
  incoming: StoredArticle,
): Pick<StoredArticle, 'content' | 'content_fetched_at'> {
  const currentLength = textLength(current.content);
  const incomingLength = textLength(incoming.content);
  if (incomingLength > currentLength) {
    return { content: incoming.content, content_fetched_at: incoming.content_fetched_at };
  }
  if (incomingLength < currentLength) {
    return { content: current.content, content_fetched_at: current.content_fetched_at };
  }

  // Equal non-empty bodies prefer the most recently fetched copy. Empty bodies
  // keep the stable survivor's text, but retain the latest known fetch attempt.
  if (
    currentLength > 0 &&
    incoming.content_fetched_at !== null &&
    (current.content_fetched_at === null || incoming.content_fetched_at > current.content_fetched_at)
  ) {
    return { content: incoming.content, content_fetched_at: incoming.content_fetched_at };
  }
  return {
    content: current.content,
    content_fetched_at:
      currentLength === 0
        ? latestTimestamp(current.content_fetched_at, incoming.content_fetched_at)
        : current.content_fetched_at,
  };
}

/**
 * Merge only non-identity state into the first retained row. Keeping its GUID
 * and link is essential: a deleted duplicate's alternate identity must never
 * become a new map entry or collide with a later retained Article.
 */
function mergeDuplicateIntoSurvivor(current: StoredArticle, incoming: StoredArticle): StoredArticle {
  const content = mergedContent(current, incoming);
  const hasSuccessfulCopy = !current.is_broken || !incoming.is_broken;

  return {
    ...current,
    title: newestUsefulText(current.title, incoming.title, current, incoming),
    description: newestUsefulText(current.description, incoming.description, current, incoming),
    content: content.content,
    author: newestUsefulText(current.author, incoming.author, current, incoming),
    categories: newestUsefulText(current.categories, incoming.categories, current, incoming),
    published_at: newestUsefulTimestamp(current.published_at, incoming.published_at, current, incoming),
    is_read: current.is_read || incoming.is_read ? 1 : 0,
    is_starred: current.is_starred || incoming.is_starred ? 1 : 0,
    is_broken: hasSuccessfulCopy ? 0 : 1,
    broken_reason: hasSuccessfulCopy
      ? null
      : newestUsefulText(current.broken_reason, incoming.broken_reason, current, incoming),
    // `link` remains the retained identity and is never rewritten. `link_fixed`
    // is a separate, already-resolved content target, so preserving its newest
    // useful value is coherent with an unbroken survivor and clears any stale
    // broken reason rather than claiming the stable original URL changed.
    link_fixed: newestUsefulText(current.link_fixed, incoming.link_fixed, current, incoming),
    content_fetched_at: content.content_fetched_at,
    created_at: earliestTimestamp(current.created_at, incoming.created_at),
    updated_at: latestTimestamp(current.updated_at, incoming.updated_at),
  };
}

function backupRemovedArticle(
  sqliteDb: Database,
  article: StoredArticle,
  survivorArticleId: number,
  reason: DuplicateReason,
  removedAt: number,
): void {
  sqliteDb.run(
    `INSERT OR IGNORE INTO ${ARTICLE_IDENTITY_REPAIR_BACKUPS_TABLE} (
      article_id, survivor_article_id, reason, removed_at,
      feed_id, guid, title, link, description, content, author, categories,
      published_at, is_read, is_starred, is_broken, broken_reason, link_fixed,
      content_fetched_at, created_at, updated_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      article.id,
      survivorArticleId,
      reason,
      removedAt,
      article.feed_id,
      article.guid,
      article.title,
      article.link,
      article.description,
      article.content,
      article.author,
      article.categories,
      article.published_at,
      article.is_read,
      article.is_starred,
      article.is_broken,
      article.broken_reason,
      article.link_fixed,
      article.content_fetched_at,
      article.created_at,
      article.updated_at,
    ],
  );
}

function persistMergedSurvivor(sqliteDb: Database, article: StoredArticle): void {
  sqliteDb.run(
    `UPDATE articles SET
      title = ?, description = ?, content = ?, author = ?, categories = ?, published_at = ?,
      is_read = ?, is_starred = ?, is_broken = ?, broken_reason = ?, link_fixed = ?,
      content_fetched_at = ?, created_at = ?, updated_at = ?
    WHERE id = ?`,
    [
      article.title,
      article.description,
      article.content,
      article.author,
      article.categories,
      article.published_at,
      article.is_read,
      article.is_starred,
      article.is_broken,
      article.broken_reason,
      article.link_fixed,
      article.content_fetched_at,
      article.created_at,
      article.updated_at,
      article.id,
    ],
  );
}

/**
 * Prepare legacy Article rows for 0003's feed-scoped GUID/link unique indexes.
 *
 * Rows are replayed strictly in `(feed_id, id)` order. A retained row is the
 * only row allowed into the indexed temporary identity set; each later duplicate
 * resolves GUID first and canonical HTTP link second, is backed up in full,
 * merged into that retained row, and deleted. This deliberately does not
 * collapse connected components.
 */
export function repairArticleIdentitiesForUniqueConstraints(
  sqliteDb: Database,
  options: ArticleIdentityRepairOptions = {},
): ArticleIdentityRepairResult {
  const batchSize = articleIdentityRepairBatchSize(options.batchSize);
  return runImmediateTransaction(sqliteDb, () => {
    const restoreLinkConstraint = sqliteDb
      .query("SELECT 1 FROM sqlite_master WHERE type = 'index' AND name = 'articles_feed_link_unique'")
      .get();
    if (restoreLinkConstraint) sqliteDb.run('DROP INDEX articles_feed_link_unique');
    const result = repairArticleIdentitiesInTransaction(sqliteDb, batchSize, options);
    if (restoreLinkConstraint) {
      sqliteDb.run('CREATE UNIQUE INDEX articles_feed_link_unique ON articles (feed_id, link)');
    }
    return result;
  });
}

/**
 * Repair legacy rows and publish the migration-owned uniqueness guarantees in
 * one IMMEDIATE transaction. This closes the otherwise observable gap between
 * deduplication and 0003's index DDL, during which an already-running writer
 * could add a new duplicate row.
 *
 * Drizzle still records 0003 afterwards: all of its schema statements use
 * `IF NOT EXISTS`, so a crash after this commit is safely recovered by the
 * next startup without replaying the ordered Article scan.
 */
export function repairAndInstallArticleIdentityConstraints(
  sqliteDb: Database,
  options: ArticleIdentityRepairOptions = {},
): ArticleIdentityRepairResult {
  const batchSize = articleIdentityRepairBatchSize(options.batchSize);
  return runImmediateTransaction(sqliteDb, () => {
    sqliteDb.run('DROP INDEX IF EXISTS articles_feed_link_unique');
    const result = repairArticleIdentitiesInTransaction(sqliteDb, batchSize, options);
    installArticleIdentityConstraintIndexes(sqliteDb);
    return result;
  });
}

function articleIdentityRepairBatchSize(requested: number | undefined): number {
  const batchSize = requested ?? ARTICLE_IDENTITY_REPAIR_BATCH_SIZE;
  if (!Number.isSafeInteger(batchSize) || batchSize <= 0) {
    throw new RangeError('Article identity repair batch size must be a positive safe integer');
  }
  return batchSize;
}

function repairArticleIdentitiesInTransaction(
  sqliteDb: Database,
  batchSize: number,
  options: ArticleIdentityRepairOptions,
): ArticleIdentityRepairResult {
  ensureArticleIdentityRepairAuditStorage(sqliteDb);
  // Keep retained identities in SQLite rather than in unbounded JavaScript
  // Maps. A single legacy Feed may contain millions of unique identities;
  // this indexed temp table keeps heap use bounded by the keyset page size.
  sqliteDb.run('DROP TABLE IF EXISTS temp.article_identity_repair_seen');
  sqliteDb.run(`
    CREATE TEMP TABLE article_identity_repair_seen (
      kind integer NOT NULL,
      identity text NOT NULL,
      survivor_id integer NOT NULL,
      PRIMARY KEY (kind, identity)
    ) WITHOUT ROWID
  `);
  const firstPageStatement = sqliteDb.prepare(`
    SELECT id, feed_id, guid, link
    FROM articles
    ORDER BY feed_id ASC, id ASC
    LIMIT ?
  `);
  let activeFeedId: number | undefined;
  let removed = 0;
  const removedAt = Date.now();

  try {
    const nextPageStatement = sqliteDb.prepare(`
      SELECT id, feed_id, guid, link
      FROM articles
      WHERE (feed_id, id) > (?, ?)
      ORDER BY feed_id ASC, id ASC
      LIMIT ?
    `);
    try {
      const clearSeenStatement = sqliteDb.prepare('DELETE FROM temp.article_identity_repair_seen');
      try {
        const findSeenStatement = sqliteDb.prepare(
          'SELECT survivor_id FROM temp.article_identity_repair_seen WHERE kind = ? AND identity = ?',
        );
        try {
          const rememberSeenStatement = sqliteDb.prepare(
            'INSERT INTO temp.article_identity_repair_seen (kind, identity, survivor_id) VALUES (?, ?, ?)',
          );
          try {
            const completeArticleStatement = sqliteDb.prepare(`
              SELECT id, feed_id, guid, title, link, description, content, author, categories, published_at,
                is_read, is_starred, is_broken, broken_reason, link_fixed, content_fetched_at, created_at, updated_at
              FROM articles
              WHERE id = ?
            `);
            try {
              let cursor: StoredArticleIdentity | undefined;
              while (true) {
                const rows = (
                  cursor
                    ? nextPageStatement.all(cursor.feed_id, cursor.id, batchSize)
                    : firstPageStatement.all(batchSize)
                ) as StoredArticleIdentity[];
                if (rows.length === 0) break;
                options.onPageRead?.(rows.length);

                for (const identity of rows) {
                  if (identity.feed_id !== activeFeedId) {
                    activeFeedId = identity.feed_id;
                    clearSeenStatement.run();
                  }

                  const guidMatch = hasGuidIdentity(identity.guid)
                    ? (findSeenStatement.get(0, identity.guid) as { survivor_id: number } | null)
                    : null;
                  const canonicalLink = canonicalLegacyArticleLink(identity.link);
                  const linkMatch = findSeenStatement.get(1, canonicalLink) as { survivor_id: number } | null;
                  const survivorId = guidMatch?.survivor_id ?? linkMatch?.survivor_id;
                  if (survivorId === undefined) {
                    if (canonicalLink !== identity.link) {
                      sqliteDb.run('UPDATE articles SET link = ? WHERE id = ?', [canonicalLink, identity.id]);
                    }
                    if (hasGuidIdentity(identity.guid)) rememberSeenStatement.run(0, identity.guid, identity.id);
                    rememberSeenStatement.run(1, canonicalLink, identity.id);
                    continue;
                  }

                  const article = completeArticleStatement.get(identity.id) as StoredArticle | null;
                  const survivor = completeArticleStatement.get(survivorId) as StoredArticle | null;
                  if (!article || !survivor) {
                    throw new Error('Article identity repair lost a row during its immediate transaction');
                  }
                  const reason: DuplicateReason = guidMatch !== null ? 'guid' : 'link';
                  backupRemovedArticle(sqliteDb, article, survivor.id, reason, removedAt);
                  persistMergedSurvivor(sqliteDb, mergeDuplicateIntoSurvivor(survivor, article));
                  sqliteDb.run('DELETE FROM articles WHERE id = ?', [article.id]);
                  removed++;
                }

                cursor = rows[rows.length - 1];
              }
            } finally {
              completeArticleStatement.finalize();
            }
          } finally {
            rememberSeenStatement.finalize();
          }
        } finally {
          findSeenStatement.finalize();
        }
      } finally {
        clearSeenStatement.finalize();
      }
    } finally {
      nextPageStatement.finalize();
    }
  } finally {
    try {
      firstPageStatement.finalize();
    } finally {
      sqliteDb.run('DROP TABLE IF EXISTS temp.article_identity_repair_seen');
    }
  }

  return { removed };
}