deploy-migration.js

#!/usr/bin/env node
/**
 * Direct Migration Deployment Script
 * Executes migration 0025 SQL directly using Supabase client
 */

const fs = require('fs');
const path = require('path');

// Read environment variables
require('dotenv').config({ path: '.env.local' });

const SUPABASE_URL = process.env.NEXT_PUBLIC_SUPABASE_URL;
const SERVICE_ROLE_KEY = process.env.SUPABASE_SERVICE_ROLE_KEY;

async function deployMigration() {
  console.log('='.repeat(80));
  console.log('MIGRATION 0025 DEPLOYMENT SCRIPT');
  console.log('='.repeat(80));
  console.log('');

  // Verify environment variables
  if (!SUPABASE_URL || !SERVICE_ROLE_KEY) {
    console.error('ERROR: Missing Supabase credentials in .env.local');
    process.exit(1);
  }

  console.log(`Supabase URL: ${SUPABASE_URL}`);
  console.log(`Project Ref: ${SUPABASE_URL.match(/https:\/\/(.+)\.supabase\.co/)[1]}`);
  console.log('');

  // Read migration SQL
  const migrationPath = path.join(__dirname, 'supabase', 'migrations', '0025_performance_optimization_clinical_hours.sql');
  console.log(`Reading migration file: ${migrationPath}`);

  const migrationSQL = fs.readFileSync(migrationPath, 'utf8');
  console.log(`Migration file size: ${(migrationSQL.length / 1024).toFixed(2)} KB`);
  console.log('');

  // Initialize Supabase client
  const { createClient } = require('@supabase/supabase-js');
  const supabase = createClient(SUPABASE_URL, SERVICE_ROLE_KEY, {
    auth: {
      autoRefreshToken: false,
      persistSession: false
    }
  });

  console.log('Executing migration SQL...');
  console.log('This may take 2-5 minutes for index creation (CONCURRENTLY)');
  console.log('');

  try {
    // Split migration into parts: indexes and functions
    // Indexes use CREATE INDEX CONCURRENTLY which can't be in a transaction
    // Functions use BEGIN/COMMIT block

    const indexStatements = migrationSQL.match(/CREATE INDEX CONCURRENTLY.*?;/gs) || [];
    const functionBlock = migrationSQL.match(/BEGIN;.*COMMIT;/s)?.[0] || '';

    // Step 1: Create indexes (one at a time, CONCURRENTLY can't be in transaction)
    console.log('Step 1: Creating indexes...');
    for (let i = 0; i < indexStatements.length; i++) {
      const indexSQL = indexStatements[i];
      const indexName = indexSQL.match(/idx_\w+/)?.[0] || `index ${i + 1}`;

      console.log(`  Creating ${indexName}...`);
      const { error: indexError } = await supabase.rpc('exec_sql', { query: indexSQL }).single();

      // If rpc doesn't exist, try direct query
      if (indexError && indexError.message.includes('Could not find')) {
        // Try using raw SQL query (requires pg extension)
        const response = await fetch(`${SUPABASE_URL}/rest/v1/rpc/query`, {
          method: 'POST',
          headers: {
            'Content-Type': 'application/json',
            'apikey': SERVICE_ROLE_KEY,
            'Authorization': `Bearer ${SERVICE_ROLE_KEY}`,
          },
          body: JSON.stringify({ query: indexSQL })
        });

        if (!response.ok) {
          console.warn(`    Warning: Could not create ${indexName} via API`);
          console.warn(`    You may need to run this manually in Supabase SQL Editor`);
        } else {
          console.log(`    ✓ Created ${indexName}`);
        }
      } else if (indexError) {
        console.error(`    ERROR creating ${indexName}:`, indexError.message);
        throw indexError;
      } else {
        console.log(`    ✓ Created ${indexName}`);
      }
    }

    console.log('');
    console.log('Step 2: Creating RPC functions...');

    // Step 2: Create functions (in transaction block)
    if (functionBlock) {
      const { error: funcError } = await supabase.rpc('exec_sql', { query: functionBlock }).single();

      if (funcError && funcError.message.includes('Could not find')) {
        const response = await fetch(`${SUPABASE_URL}/rest/v1/rpc/query`, {
          method: 'POST',
          headers: {
            'Content-Type': 'application/json',
            'apikey': SERVICE_ROLE_KEY,
            'Authorization': `Bearer ${SERVICE_ROLE_KEY}`,
          },
          body: JSON.stringify({ query: functionBlock })
        });

        if (!response.ok) {
          console.warn('  Warning: Could not create functions via API');
          console.warn('  You may need to run this manually in Supabase SQL Editor');
        } else {
          console.log('  ✓ Created all RPC functions');
        }
      } else if (funcError) {
        console.error('  ERROR creating functions:', funcError.message);
        throw funcError;
      } else {
        console.log('  ✓ Created all RPC functions');
      }
    }

    console.log('');
    console.log('='.repeat(80));
    console.log('DEPLOYMENT STATUS: API method not available');
    console.log('='.repeat(80));
    console.log('');
    console.log('The Supabase REST API does not support direct SQL execution.');
    console.log('Please use one of these alternative methods:');
    console.log('');
    console.log('OPTION 1: Supabase Dashboard (Recommended)');
    console.log('  1. Visit: https://supabase.com/dashboard/project/mdzzslzwaturlmyhnzzw/sql/new');
    console.log('  2. Copy and paste the contents of:');
    console.log('     ' + migrationPath);
    console.log('  3. Click "Run" to execute');
    console.log('');
    console.log('OPTION 2: psql Command Line');
    console.log('  1. Get database password from: https://supabase.com/dashboard/project/mdzzslzwaturlmyhnzzw/settings/database');
    console.log('  2. Run: psql "postgresql://postgres:[PASSWORD]@db.mdzzslzwaturlmyhnzzw.supabase.co:5432/postgres" < supabase/migrations/0025_performance_optimization_clinical_hours.sql');
    console.log('');
    console.log('OPTION 3: Fix Supabase CLI (Advanced)');
    console.log('  1. Run: supabase migration repair --status reverted [migration-names]');
    console.log('  2. Run: supabase db push');
    console.log('');

  } catch (error) {
    console.error('');
    console.error('='.repeat(80));
    console.error('DEPLOYMENT FAILED');
    console.error('='.repeat(80));
    console.error('Error:', error.message);
    console.error('Stack:', error.stack);
    process.exit(1);
  }
}

deployMigration();