import { createHash } from 'node:crypto'
import { readFile } from 'node:fs/promises'

import { closeDatabase, getSqlClient } from '../../packages/db/src/index'
import {
  evaluateMigrationPreflight,
  EXPECTED_MIGRATION_COUNT,
  type MigrationPreflightSnapshot,
} from '../../packages/db/src/release/migration-preflight'

const sql = getSqlClient()

async function relationExists(name: string): Promise<boolean> {
  const [row] = await sql<{ exists: boolean }[]>`
    select to_regclass(${name}) is not null as exists
  `
  return row?.exists === true
}

async function columnExists(table: string, column: string): Promise<boolean> {
  const [row] = await sql<{ exists: boolean }[]>`
    select exists (
      select 1 from information_schema.columns
      where table_schema = 'public' and table_name = ${table}
        and column_name = ${column}
    ) as exists
  `
  return row?.exists === true
}

async function count(query: string): Promise<number> {
  const [row] = await sql.unsafe<{ count: number }[]>(query)
  return Number(row?.count ?? 0)
}

async function expectedMigrationHashes(): Promise<readonly string[]> {
  const migrationsRoot = new URL(
    '../../packages/db/migrations/',
    import.meta.url,
  )
  const journal = JSON.parse(
    await readFile(new URL('meta/_journal.json', migrationsRoot), 'utf8'),
  ) as {
    entries: readonly { tag: string }[]
  }
  if (journal.entries.length !== EXPECTED_MIGRATION_COUNT) {
    throw new Error(
      `Migration journal has ${journal.entries.length} entries; expected ${EXPECTED_MIGRATION_COUNT}.`,
    )
  }
  return Promise.all(
    journal.entries.map(async ({ tag }) =>
      createHash('sha256')
        .update(await readFile(new URL(`${tag}.sql`, migrationsRoot)))
        .digest('hex'),
    ),
  )
}

async function snapshot(): Promise<MigrationPreflightSnapshot> {
  const migrationTableExists = await relationExists(
    'drizzle.__drizzle_migrations',
  )
  const applied = migrationTableExists
    ? await sql<{ hash: string }[]>`
        select hash from drizzle.__drizzle_migrations order by created_at, id
      `
    : []
  const expectedHashes = await expectedMigrationHashes()
  const migrationHashesMatch = applied.every(
    ({ hash }, index) => expectedHashes[index] === hash,
  )
  const [version] = await sql<{ major: number }[]>`
    select current_setting('server_version_num')::int / 10000 as major
  `

  const generatedRunsExist = await relationExists('public.generated_runs')
  const integrationSecretsExist = await relationExists(
    'public.integration_secrets',
  )
  const versionsExist = await relationExists('public.playbook_versions')
  const draftDigestExists = await columnExists(
    'playbook_versions',
    'draft_digest',
  )
  const evaluationCasesExist = await relationExists('public.evaluation_cases')
  const evaluationTargetDigestExists = await columnExists(
    'evaluation_cases',
    'target_digest',
  )
  const evaluationResultsExist = await relationExists(
    'public.evaluation_results',
  )
  const resultTargetDigestExists = await columnExists(
    'evaluation_results',
    'target_digest',
  )

  return {
    appliedMigrationCount: applied.length,
    migrationTableExists,
    migrationHashesMatch,
    databaseMajorVersion: Number(version?.major ?? 0),
    legacyNullRunIdempotencyKeys: generatedRunsExist
      ? await count(
          'select count(*)::int as count from generated_runs where idempotency_key is null',
        )
      : 0,
    invalidIntegrationSecretEnvelopes: integrationSecretsExist
      ? await count(`select count(*)::int as count from integration_secrets
          where envelope_version <> 1
             or length(btrim(key_version)) not between 1 and 64
             or secret_kind <> 'access_token'
             or octet_length(nonce) <> 12
             or octet_length(auth_tag) <> 16
             or (last_four is not null and length(last_four) <> 4)`)
      : 0,
    publishedDraftDigestMismatches:
      versionsExist && draftDigestExists
        ? await count(`select count(*)::int as count from playbook_versions
            where published_at is not null and draft_digest <> content_digest`)
        : 0,
    invalidEvaluationDigestBindings:
      (evaluationCasesExist && evaluationTargetDigestExists
        ? await count(`select count(*)::int as count from evaluation_cases
            where (target_digest is not null and target_digest !~ '^[0-9a-f]{64}$')
               or (fixture_digest is not null and fixture_digest !~ '^[0-9a-f]{64}$')
               or (environment_digest is not null and environment_digest !~ '^[0-9a-f]{64}$')`)
        : 0) +
      (evaluationResultsExist && resultTargetDigestExists
        ? await count(`select count(*)::int as count from evaluation_results
            where (target_digest is not null and target_digest !~ '^[0-9a-f]{64}$')
               or (fixture_digest is not null and fixture_digest !~ '^[0-9a-f]{64}$')
               or (environment_digest is not null and environment_digest !~ '^[0-9a-f]{64}$')`)
        : 0),
    publishedImmutabilityTriggerPresent: versionsExist
      ? (await count(`select count(*)::int as count from pg_trigger
          where tgrelid = 'public.playbook_versions'::regclass
            and tgname = 'playbook_versions_published_immutable_trg'
            and not tgisinternal`)) === 1
      : false,
  }
}

try {
  const state = await snapshot()
  const findings = evaluateMigrationPreflight(state)
  const blockers = findings.filter(({ severity }) => severity === 'blocker')
  process.stdout.write(
    `${JSON.stringify(
      {
        schemaVersion: 1,
        outcome: blockers.length === 0 ? 'ready' : 'blocked',
        expectedMigrationCount: EXPECTED_MIGRATION_COUNT,
        pendingMigrationCount: Math.max(
          0,
          EXPECTED_MIGRATION_COUNT - state.appliedMigrationCount,
        ),
        snapshot: state,
        findings,
      },
      null,
      2,
    )}\n`,
  )
  process.exitCode = blockers.length === 0 ? 0 : 2
} finally {
  await closeDatabase()
}