import { supabase } from '../db/supabase.js';
import { z } from 'zod';
import { logger } from '../utils/logger.js';
// Transaction result schemas
const TransactionResultSchema = z.object({
success: z.boolean(),
error: z.string().optional(),
error_detail: z.string().optional()
});
const CreateProjectResultSchema = TransactionResultSchema.extend({
project_id: z.string().uuid().optional(),
projection_id: z.string().uuid().optional(),
use_case_count: z.number().optional(),
total_investment: z.number().optional(),
message: z.string().optional()
});
const UpdateProjectResultSchema = TransactionResultSchema.extend({
project_id: z.string().uuid().optional(),
use_cases_added: z.number().optional(),
use_cases_updated: z.number().optional(),
use_cases_deleted: z.number().optional(),
projection_marked_for_update: z.boolean().optional()
});
const DeleteProjectResultSchema = TransactionResultSchema.extend({
project_id: z.string().uuid().optional(),
would_delete: z.object({
use_cases: z.number(),
projections: z.number(),
simulations: z.number(),
metrics: z.number()
}).optional(),
deleted: z.object({
use_cases: z.number(),
projections: z.number(),
simulations: z.number(),
metrics: z.number()
}).optional(),
message: z.string().optional()
});
const ValidationResultSchema = z.object({
valid: z.boolean(),
errors: z.array(z.object({
field: z.string(),
message: z.string()
})),
warnings: z.array(z.object({
field: z.string(),
message: z.string()
}))
});
export class TransactionService {
/**
* Creates a project with all its use cases atomically
* @param projectData Project details
* @param useCases Array of use cases
* @param implementationCosts Implementation cost breakdown
* @param timelineMonths Project timeline in months
* @param confidenceLevel Confidence level (0-1)
* @returns Transaction result with project and projection IDs
*/
static async createProjectWithDetails(
projectData: any,
useCases: any[],
implementationCosts: any,
timelineMonths: number,
confidenceLevel: number = 0.95
) {
try {
const { data, error } = await supabase.rpc('create_project_with_details', {
p_project: projectData,
p_use_cases: useCases,
p_implementation_costs: implementationCosts,
p_timeline_months: timelineMonths,
p_confidence_level: confidenceLevel
});
if (error) {
throw new Error(`Database transaction failed: ${error.message}`);
}
const result = CreateProjectResultSchema.parse(data);
if (!result.success) {
throw new Error(result.error || 'Transaction failed');
}
return result;
} catch (error) {
logger.error('Transaction error in createProjectWithDetails', error as Error);
throw error;
}
}
/**
* Updates a project and its use cases atomically
* @param projectId UUID of the project
* @param projectUpdates Optional project field updates
* @param useCasesToAdd Optional new use cases to add
* @param useCasesToUpdate Optional use cases to update (must include id)
* @param useCasesToDelete Optional array of use case IDs to delete
* @param regenerateProjection Whether to mark projections for recalculation
* @returns Transaction result with operation counts
*/
static async updateProjectWithUseCases(
projectId: string,
projectUpdates?: any,
useCasesToAdd?: any[],
useCasesToUpdate?: any[],
useCasesToDelete?: string[],
regenerateProjection: boolean = false
) {
try {
const { data, error } = await supabase.rpc('update_project_with_use_cases', {
p_project_id: projectId,
p_project_updates: projectUpdates || null,
p_use_cases_to_add: useCasesToAdd || null,
p_use_cases_to_update: useCasesToUpdate || null,
p_use_cases_to_delete: useCasesToDelete || null,
p_regenerate_projection: regenerateProjection
});
if (error) {
throw new Error(`Database transaction failed: ${error.message}`);
}
const result = UpdateProjectResultSchema.parse(data);
if (!result.success) {
throw new Error(result.error || 'Transaction failed');
}
return result;
} catch (error) {
logger.error('Transaction error in updateProjectWithUseCases', error as Error);
throw error;
}
}
/**
* Deletes a project and all related data atomically
* @param projectId UUID of the project to delete
* @param confirmDelete Must be true to actually delete (false returns what would be deleted)
* @returns Transaction result with deletion summary
*/
static async deleteProjectCascade(projectId: string, confirmDelete: boolean = false) {
try {
const { data, error } = await supabase.rpc('delete_project_cascade', {
p_project_id: projectId,
p_confirm_delete: confirmDelete
});
if (error) {
throw new Error(`Database transaction failed: ${error.message}`);
}
const result = DeleteProjectResultSchema.parse(data);
if (!result.success && confirmDelete) {
throw new Error(result.error || 'Deletion failed');
}
return result;
} catch (error) {
logger.error('Transaction error in deleteProjectCascade', error as Error);
throw error;
}
}
/**
* Validates project and use case data before creation/update
* @param projectData Project details to validate
* @param useCases Array of use cases to validate
* @returns Validation result with errors and warnings
*/
static async validateProjectData(projectData: any, useCases: any[]) {
try {
const { data, error } = await supabase.rpc('validate_project_data', {
p_project: projectData,
p_use_cases: useCases
});
if (error) {
throw new Error(`Validation failed: ${error.message}`);
}
return ValidationResultSchema.parse(data);
} catch (error) {
logger.error('Validation error in validateProjectData', error as Error);
throw error;
}
}
/**
* Creates a projection with optional simulation setup
* @param projectId UUID of the project
* @param projectionData Projection details
* @param runSimulation Whether to create a simulation placeholder
* @param simulationIterations Number of Monte Carlo iterations
* @returns Transaction result with projection ID
*/
static async createProjectionWithSimulation(
projectId: string,
projectionData: any,
runSimulation: boolean = false,
simulationIterations: number = 10000
) {
try {
const { data, error } = await supabase.rpc('create_projection_with_simulation', {
p_project_id: projectId,
p_projection_data: projectionData,
p_run_simulation: runSimulation,
p_simulation_iterations: simulationIterations
});
if (error) {
throw new Error(`Database transaction failed: ${error.message}`);
}
const result = TransactionResultSchema.parse(data);
if (!result.success) {
throw new Error(result.error || 'Transaction failed');
}
return data; // Return full data for projection_id and simulation_id
} catch (error) {
logger.error('Transaction error in createProjectionWithSimulation', error as Error);
throw error;
}
}
/**
* Performs a batch operation with automatic rollback on failure
* @param operations Array of operations to perform
* @returns Results of all operations
*/
static async batchOperation(operations: Array<() => Promise<any>>) {
const results: any[] = [];
const rollbacks: Array<() => Promise<void>> = [];
try {
for (const operation of operations) {
const result = await operation();
results.push(result);
}
return {
success: true,
results
};
} catch (error) {
// In a real implementation, you'd execute rollback operations here
// For now, we just throw the error
logger.error('Batch operation failed', error as Error);
throw error;
}
}
}
// Export convenience functions for common operations
export async function createProjectTransaction(
projectData: any,
useCases: any[],
implementationCosts: any,
timelineMonths: number,
confidenceLevel?: number
) {
// First validate the data
const validation = await TransactionService.validateProjectData(projectData, useCases);
if (!validation.valid) {
throw new Error(`Validation failed: ${JSON.stringify(validation.errors)}`);
}
// Log warnings if any
if (validation.warnings.length > 0) {
logger.warn('Validation warnings', { warnings: validation.warnings });
}
// Create the project
return TransactionService.createProjectWithDetails(
projectData,
useCases,
implementationCosts,
timelineMonths,
confidenceLevel
);
}
export async function updateProjectTransaction(
projectId: string,
updates: {
project?: any;
addUseCases?: any[];
updateUseCases?: any[];
deleteUseCaseIds?: string[];
regenerateProjection?: boolean;
}
) {
return TransactionService.updateProjectWithUseCases(
projectId,
updates.project,
updates.addUseCases,
updates.updateUseCases,
updates.deleteUseCaseIds,
updates.regenerateProjection
);
}
export async function safeDeleteProject(projectId: string) {
// First check what would be deleted
const preview = await TransactionService.deleteProjectCascade(projectId, false);
// Log what will be deleted
logger.info('Project deletion preview', { preview });
// Actually delete
return TransactionService.deleteProjectCascade(projectId, true);
}
