knowledge/tests/apply-media-production-migration.ts

import 'dotenv/config';
import { Pool } from 'pg';

async function main() {
  const pool = new Pool({ connectionString: process.env.DATABASE_URL });

  try {
    await pool.query(`
      ALTER TABLE media
      ADD COLUMN IF NOT EXISTS resource_id uuid
    `);

    await pool.query(`
      ALTER TABLE media
      ADD COLUMN IF NOT EXISTS error_message text
    `);

    await pool.query(`
      DO $$
      BEGIN
        IF NOT EXISTS (
          SELECT 1
          FROM pg_constraint
          WHERE conname = 'media_resource_id_resources_id_fk'
        ) THEN
          ALTER TABLE media
          ADD CONSTRAINT media_resource_id_resources_id_fk
          FOREIGN KEY (resource_id)
          REFERENCES resources(id)
          ON DELETE SET NULL;
        END IF;
      END $$;
    `);

    console.log('✅ Media production migration applied.');
  } finally {
    await pool.end();
  }
}

main().catch((error) => {
  console.error('❌ Migration failed:', error);
  process.exit(1);
});