scripts/run-migration.js

const { createClient } = require('@supabase/supabase-js');
const fs = require('fs');
const path = require('path');

const supabaseUrl = process.env.SUPABASE_URL;
const supabaseKey = process.env.SUPABASE_SERVICE_ROLE_KEY || process.env.SUPABASE_KEY;

if (!supabaseUrl || !supabaseKey) {
  console.error('Error: SUPABASE_URL and SUPABASE_KEY environment variables are required.');
  process.exit(1);
}

const supabase = createClient(supabaseUrl, supabaseKey);

async function runMigration(migrationFile) {
  const filePath = path.join(__dirname, 'migrations', migrationFile);

  if (!fs.existsSync(filePath)) {
    console.error(`Error: Migration file not found: ${filePath}`);
    return false;
  }

  const sql = fs.readFileSync(filePath, 'utf8');

  console.log(`Running migration: ${migrationFile}`);

  // Execute SQL via Supabase RPC or direct query
  const { data, error } = await supabase.rpc('exec_sql', { sql });

  if (error) {
    // Some migrations may need to be run differently
    console.log(`Note: RPC exec_sql not available, trying direct execution...`);
    console.log(`SQL to execute:\n${sql}`);
    return false;
  }

  console.log(`Migration ${migrationFile} completed successfully.`);
  return true;
}

async function main() {
  const migrations = process.argv.slice(2);

  if (migrations.length === 0) {
    console.log('Available migrations:');
    const files = fs.readdirSync(path.join(__dirname, 'migrations'));
    files.filter(f => f.endsWith('.sql')).forEach(f => console.log(`  - ${f}`));
    console.log('\nUsage: node run-migration.js <migration_file.sql> [migration_file2.sql ...]');
    process.exit(0);
  }

  for (const migration of migrations) {
    try {
      const success = await runMigration(migration);
      if (!success) {
        console.log(`Skipping migration: ${migration} (manual execution required)`);
      }
    } catch (err) {
      console.error(`Error running migration ${migration}:`, err.message);
    }
  }
}

main();