deploy-migration.mjs

import { createClient } from '@supabase/supabase-js';
import { readFileSync } from 'fs';

const SUPABASE_URL = 'https://mdzzslzwaturlmyhnzzw.supabase.co';
const SERVICE_ROLE_KEY = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6Im1kenpzbHp3YXR1cmxteWhuenp3Iiwicm9sZSI6InNlcnZpY2Vfcm9sZSIsImlhdCI6MTc1OTEwMDg3NCwiZXhwIjoyMDc0Njc2ODc0fQ.2N2l51pfjChvRdMZyl3Lzl0FAPkYLWuynLmfK0zHapE';

const supabase = createClient(SUPABASE_URL, SERVICE_ROLE_KEY, {
  auth: { persistSession: false }
});

async function executeMigration() {
  try {
    console.log('Starting migration deployment...\n');

    // Read the migration file
    const migrationSQL = readFileSync('./supabase/migrations/0025_performance_optimization_clinical_hours.sql', 'utf8');

    // Execute the entire migration
    const { data, error } = await supabase.rpc('exec', { sql: migrationSQL });

    if (error) {
      console.error('Error executing migration:', error);

      // Try alternative method - execute via raw query
      console.log('\nTrying alternative execution method...');
      const response = await fetch(`${SUPABASE_URL}/rest/v1/`, {
        method: 'POST',
        headers: {
          'apikey': SERVICE_ROLE_KEY,
          'Authorization': `Bearer ${SERVICE_ROLE_KEY}`,
          'Content-Type': 'application/json',
          'Prefer': 'return=representation'
        },
        body: JSON.stringify({ query: migrationSQL })
      });

      const result = await response.json();
      console.log('Alternative method result:', result);
    } else {
      console.log('Migration executed successfully!');
      console.log('Result:', data);
    }

    // Verify deployment
    console.log('\n=== VERIFYING DEPLOYMENT ===\n');

    // Check for the RPC functions
    console.log('Checking RPC functions...');
    const functions = [
      'get_weekly_hours_aggregation',
      'get_rotation_analytics_aggregated',
      'export_hours_filtered'
    ];

    for (const funcName of functions) {
      const { data, error } = await supabase.rpc(funcName, {
        p_student_id: '00000000-0000-0000-0000-000000000000',
        ...(funcName === 'get_weekly_hours_aggregation' ? { p_weeks_back: 1 } : {})
      }).limit(1);

      if (error && !error.message.includes('Student not found') && !error.message.includes('Unauthorized')) {
        console.log(`  ❌ ${funcName}: NOT FOUND`);
      } else {
        console.log(`  ✓ ${funcName}: EXISTS`);
      }
    }

  } catch (err) {
    console.error('Fatal error:', err);
    process.exit(1);
  }
}

executeMigration();