/**
 * Neon parity for pre-existing bug #10 (see db/oracle/pre-existing-bugs.txt) — the
 * post-upgrade verification flow. The same fixes were applied to the Oracle ADB
 * during the query migration and mirrored in db/oracle/schema.sql; this migration
 * brings Neon/Postgres to the same shape so the feature works pre-cutover too.
 *
 * db/upgrade-verifications.js insertPostUpgradeValidationRun() writes into
 * installer_validation_runs with:
 *   - probe_8_status  — but only probe_1..7 columns existed (10a)
 *   - os = 'post-upgrade', topology = 'post-upgrade-v3-to-v6',
 *     trigger_source = 'post-upgrade-agent' — sentinel values that all violated
 *     the OS / TOPOLOGY / TRIGGER_SOURCE CHECK constraints. The missing-column
 *     error (a parse error) had masked these until 10a was fixed.
 * and markConnectionUpgradeDegraded() sets oracle_connections.status =
 * 'upgrade-degraded', which the status CHECK forbade (10b). (10c —
 * clearUpgradeDegradedStatus resetting to 'active' not NULL — is a code-only fix,
 * already committed, no schema change.)
 *
 * Idempotent: ADD COLUMN IF NOT EXISTS, DROP CONSTRAINT IF EXISTS then ADD. All
 * legacy values are preserved on every widened constraint.
 */
module.exports = {
  name: 'upgrade_verification_schema_fixes',
  up: async (client) => {
    // 10a: the 8th probe the agent sends (mirror probe_7's text/integer/text shape)
    await client.query(`
      ALTER TABLE installer_validation_runs ADD COLUMN IF NOT EXISTS probe_8_status text;
      ALTER TABLE installer_validation_runs ADD COLUMN IF NOT EXISTS probe_8_ms integer;
      ALTER TABLE installer_validation_runs ADD COLUMN IF NOT EXISTS probe_8_error text;
    `);

    await client.query(`
      ALTER TABLE installer_validation_runs DROP CONSTRAINT IF EXISTS installer_validation_runs_probe_8_status_check;
      ALTER TABLE installer_validation_runs ADD CONSTRAINT installer_validation_runs_probe_8_status_check
        CHECK (probe_8_status = ANY (ARRAY['pass','fail','skip']::text[]));
    `);

    // 10a (masked): widen the reused-table domains for the post-upgrade sentinel rows
    await client.query(`
      ALTER TABLE installer_validation_runs DROP CONSTRAINT IF EXISTS installer_validation_runs_os_check;
      ALTER TABLE installer_validation_runs ADD CONSTRAINT installer_validation_runs_os_check
        CHECK (os = ANY (ARRAY['ol7','ol8','ebs12210','post-upgrade']::text[]));
    `);

    await client.query(`
      ALTER TABLE installer_validation_runs DROP CONSTRAINT IF EXISTS installer_validation_runs_topology_check;
      ALTER TABLE installer_validation_runs ADD CONSTRAINT installer_validation_runs_topology_check
        CHECK (topology = ANY (ARRAY['greenfield-OL7','greenfield-OL8','live-EBS-12.2.10-db-dev','post-upgrade-v3-to-v6']::text[]));
    `);

    await client.query(`
      ALTER TABLE installer_validation_runs DROP CONSTRAINT IF EXISTS installer_validation_runs_trigger_source_check;
      ALTER TABLE installer_validation_runs ADD CONSTRAINT installer_validation_runs_trigger_source_check
        CHECK (trigger_source = ANY (ARRAY['cron','deploy_webhook','manual','post-upgrade-agent']::text[]));
    `);

    // 10b: allow the upgrade-degraded status on oracle_connections
    await client.query(`
      ALTER TABLE oracle_connections DROP CONSTRAINT IF EXISTS oracle_connections_status_check;
      ALTER TABLE oracle_connections ADD CONSTRAINT oracle_connections_status_check
        CHECK (status = ANY (ARRAY['pending_registration','active','stale','upgrade-degraded']::text[]));
    `);
  },
};
