import { Database } from 'bun:sqlite';
import { afterEach, beforeEach, describe, expect, it } from 'bun:test';
import { mkdtempSync, readFileSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';
import { closeDb, findMigrationsFolder } from '../../db/client';
import { resetTestDbPath } from '../../test-utils/db-path';
import { handleSystemMigrate } from './system-migrate';

/**
 * The newest migration, read from the journal rather than written down here.
 *
 * These assertions used to name `0019_backup_pid` literally, so every
 * subsequent migration broke a test that has nothing to do with it. What is
 * under test is that `--status` REPORTS the head and any gap below it, not
 * which migration happens to be head today.
 */
function latestMigrationTag(): string {
  const journal = JSON.parse(
    readFileSync(join(findMigrationsFolder(), 'meta', '_journal.json'), 'utf8'),
  ) as { entries: { tag: string }[] };
  const tag = journal.entries.at(-1)?.tag;
  if (!tag) throw new Error('Migration journal is empty');
  return tag;
}

describe('handleSystemMigrate', () => {
  let dir: string;

  beforeEach(() => {
    dir = mkdtempSync(join(tmpdir(), 'sysmig-'));
    process.env.CELILO_DB_PATH = join(dir, 'celilo.db');
  });
  afterEach(() => {
    closeDb();
    resetTestDbPath();
    try {
      rmSync(dir, { recursive: true, force: true });
    } catch {
      /* ignore */
    }
  });

  it('reports a fresh/current DB as up to date with the full schema', async () => {
    const result = await handleSystemMigrate();
    expect(result.success).toBe(true);
    if (result.success) {
      expect(result.message).toContain('up to date');
      expect(result.message).toContain('tables');
    }
  });

  it('is idempotent — a second run is also clean', async () => {
    (await handleSystemMigrate()).success;
    closeDb();
    const second = await handleSystemMigrate();
    expect(second.success).toBe(true);
  });

  // celilo#604: the runbook asserts "applied 19 -> 20, backups.pid present".
  // Before this, the only answer was a table COUNT, which cannot see a column.
  describe('--status', () => {
    it('names the applied count and the latest applied migration', async () => {
      await handleSystemMigrate();
      closeDb();

      const result = await handleSystemMigrate([], { status: true });

      expect(result.success).toBe(true);
      if (result.success) {
        expect(result.message).toMatch(/Applied migrations: \d+/);
        expect(result.message).toContain(latestMigrationTag());
        expect(result.message).toContain('Pending:            none');
        expect(result.message).toContain('columns');
      }
    });

    it('reports a pending migration WITHOUT applying it', async () => {
      await handleSystemMigrate();
      closeDb();
      // Rewind one migration, the way an upgrade that never ran would look.
      const head = latestMigrationTag();
      const raw = new Database(process.env.CELILO_DB_PATH as string);
      raw.run(
        'DELETE FROM `__drizzle_migrations` WHERE created_at = (SELECT MAX(created_at) FROM `__drizzle_migrations`)',
      );
      raw.run('ALTER TABLE backups DROP COLUMN pid');
      const countBefore = raw
        .query<{ c: number }, []>('SELECT COUNT(*) AS c FROM `__drizzle_migrations`')
        .get()?.c;
      raw.close();

      const result = await handleSystemMigrate([], { status: true });

      expect(result.success).toBe(false);
      if (!result.success) {
        // The rewound migration is named as pending…
        expect(result.error).toContain(head);
        // …and the dropped COLUMN is reported independently, which is the
        // thing a table count cannot see (celilo#604).
        expect(result.error).toContain('backups.pid');
      }

      // A status that repaired what it reports would always read clean — the
      // exact placebo this command exists to replace.
      const after = new Database(process.env.CELILO_DB_PATH as string);
      const countAfter = after
        .query<{ c: number }, []>('SELECT COUNT(*) AS c FROM `__drizzle_migrations`')
        .get()?.c;
      const cols = after
        .query<{ name: string }, []>("SELECT name FROM pragma_table_info('backups')")
        .all();
      after.close();
      expect(countAfter).toBe(countBefore as number);
      expect(cols.map((c) => c.name)).not.toContain('pid');
    });
  });
});