#!/usr/bin/env node

/**
 * Apply organization migration to Supabase
 *
 * This script applies the organization tables migration directly to Supabase.
 */

import { createClient } from '@supabase/supabase-js';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';
import dotenv from 'dotenv';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// Load environment variables
dotenv.config({ path: path.resolve(__dirname, '../.env') });

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL;
const supabaseKey = process.env.SUPABASE_SERVICE_ROLE_KEY;

if (!supabaseUrl || !supabaseKey) {
  console.error('❌ Missing Supabase credentials in .env file');
  process.exit(1);
}

const supabase = createClient(supabaseUrl, supabaseKey);

async function applyMigration() {
  console.log('🚀 Starting Organization Migration to Supabase');
  console.log('==========================================\n');

  try {
    // Read the migration file
    const migrationPath = path.resolve(
      __dirname,
      '../prisma/migrations/20251205000000_add_organizations/migration.sql'
    );

    if (!fs.existsSync(migrationPath)) {
      console.error('❌ Migration file not found:', migrationPath);
      process.exit(1);
    }

    const migrationSQL = fs.readFileSync(migrationPath, 'utf-8');

    console.log('📄 Migration file loaded successfully');
    console.log('📝 Executing SQL migration...\n');

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

    if (error) {
      console.error('❌ Migration failed:', error);
      console.log('\n⚠️  Attempting alternative approach...\n');

      // Try splitting by statement and executing individually
      const statements = migrationSQL
        .split(';')
        .map(s => s.trim())
        .filter(s => s.length > 0 && !s.startsWith('--'));

      let successCount = 0;
      let errorCount = 0;

      for (const statement of statements) {
        try {
          const { error: stmtError } = await supabase.rpc('exec_sql', {
            sql: statement + ';',
          });

          if (stmtError) {
            console.error(`❌ Statement failed: ${statement.substring(0, 50)}...`);
            console.error(`   Error: ${stmtError.message}`);
            errorCount++;
          } else {
            successCount++;
          }
        } catch (err) {
          console.error(`❌ Statement error: ${err.message}`);
          errorCount++;
        }
      }

      console.log(`\n✅ Executed: ${successCount} statements`);
      console.log(`❌ Failed: ${errorCount} statements`);

      if (errorCount > 0) {
        console.log('\n⚠️  Some statements failed. Manual intervention may be required.');
        console.log('You can apply the migration manually using Supabase SQL Editor:');
        console.log(`   ${migrationPath}`);
      }
    } else {
      console.log('✅ Migration applied successfully!');
    }

    console.log('\n==========================================');
    console.log('📊 Verifying new tables...\n');

    // Verify tables were created
    const tables = ['organizations', 'organization_members', 'organization_invitations'];

    for (const table of tables) {
      const { count, error: countError } = await supabase
        .from(table)
        .select('*', { count: 'exact', head: true });

      if (countError) {
        console.log(`❌ ${table}: Not accessible (${countError.message})`);
      } else {
        console.log(`✅ ${table}: Accessible (${count || 0} rows)`);
      }
    }

    console.log('\n==========================================');
    console.log('✨ Migration process completed!');
    console.log('==========================================\n');
  } catch (error) {
    console.error('❌ Unexpected error:', error);
    process.exit(1);
  }
}

applyMigration();
