#!/usr/bin/env node
/**
* Script to apply database functions to Supabase
* Usage: npm run apply-db-functions
*/
import { createClient } from '@supabase/supabase-js';
import * as fs from 'fs';
import * as path from 'path';
import * as dotenv from 'dotenv';
// Load environment variables
dotenv.config();
const SUPABASE_URL = process.env.SUPABASE_URL;
const SUPABASE_SERVICE_KEY = process.env.SUPABASE_SERVICE_KEY;
if (!SUPABASE_URL || !SUPABASE_SERVICE_KEY) {
console.error('❌ Error: SUPABASE_URL and SUPABASE_SERVICE_KEY must be set in .env file');
console.error(' The service key is required for admin operations like creating functions.');
process.exit(1);
}
// Create Supabase admin client
const supabase = createClient(SUPABASE_URL, SUPABASE_SERVICE_KEY, {
auth: {
autoRefreshToken: false,
persistSession: false
}
});
async function applyDatabaseFunctions() {
console.log('🚀 Applying database functions to Supabase...\n');
try {
// Read the SQL file
const sqlPath = path.join(__dirname, '..', 'database', '002_transactional_functions.sql');
const sqlContent = fs.readFileSync(sqlPath, 'utf-8');
console.log('📄 Read SQL file:', sqlPath);
console.log(`📏 SQL content length: ${sqlContent.length} characters\n`);
// Execute the SQL
console.log('⚡ Executing SQL functions...');
const { data, error } = await supabase.rpc('query', { query: sqlContent });
if (error) {
// If the RPC doesn't exist, try using the SQL editor approach
console.log('ℹ️ Standard RPC not available, trying direct SQL execution...');
// Split the SQL into individual function definitions
const functions = sqlContent.split(/^-- Function:/gm).filter(f => f.trim());
console.log(`📦 Found ${functions.length} functions to create\n`);
for (let i = 0; i < functions.length; i++) {
const funcSql = functions[i];
const funcName = funcSql.match(/CREATE OR REPLACE FUNCTION (\w+)/)?.[1] || `Function ${i + 1}`;
console.log(`⚙️ Creating function: ${funcName}`);
// Note: Direct SQL execution requires using Supabase dashboard or CLI
// This is a limitation of the JS client
}
console.log('\n⚠️ Note: The Supabase JS client cannot directly execute DDL statements.');
console.log(' Please use one of these methods to apply the functions:\n');
console.log(' 1. Supabase Dashboard:');
console.log(' - Go to your project at https://app.supabase.com');
console.log(' - Navigate to SQL Editor');
console.log(' - Paste the contents of database/002_transactional_functions.sql');
console.log(' - Click "Run"\n');
console.log(' 2. Supabase CLI:');
console.log(' - Install: npm install -g supabase');
console.log(' - Run: supabase db push --db-url "postgresql://postgres:[YOUR-PASSWORD]@db.[PROJECT-REF].supabase.co:5432/postgres"');
console.log(' - Or: supabase db diff --file database/002_transactional_functions.sql\n');
console.log(' 3. psql:');
console.log(' - psql "postgresql://postgres:[YOUR-PASSWORD]@db.[PROJECT-REF].supabase.co:5432/postgres" < database/002_transactional_functions.sql\n');
// Create a temporary file with just the function names for reference
const functionList = [
'create_project_with_details',
'update_project_with_use_cases',
'create_projection_with_simulation',
'delete_project_cascade',
'validate_project_data'
];
console.log('📋 Functions to be created:');
functionList.forEach(fn => console.log(` ✓ ${fn}`));
return;
}
console.log('✅ Database functions applied successfully!\n');
// Test that the functions exist
console.log('🧪 Testing functions...');
const testResult = await supabase.rpc('validate_project_data', {
p_project: { client_name: 'Test', project_name: 'Test', industry: 'technology' },
p_use_cases: []
});
if (testResult.error) {
console.log('⚠️ Functions may not be applied yet. Error:', testResult.error.message);
} else {
console.log('✅ Functions are working correctly!');
console.log(' Test result:', testResult.data);
}
} catch (error) {
console.error('❌ Error applying database functions:', error);
process.exit(1);
}
}
// Run the script
applyDatabaseFunctions().then(() => {
console.log('\n✨ Script completed');
}).catch(error => {
console.error('❌ Script failed:', error);
process.exit(1);
});
