-- MCP Server ROI Transactional Functions
-- These functions ensure atomic operations for complex data operations

-- Function: create_project_with_details
-- Purpose: Atomically create a project with all its use cases and generate initial projection
-- Returns: JSON object with project_id, projection_id, and success status
CREATE OR REPLACE FUNCTION create_project_with_details(
  p_project JSONB,
  p_use_cases JSONB,
  p_implementation_costs JSONB,
  p_timeline_months INTEGER,
  p_confidence_level NUMERIC DEFAULT 0.95
) RETURNS JSONB AS $$
DECLARE
  v_project_id UUID;
  v_projection_id UUID;
  v_use_case_ids UUID[];
  v_use_case JSONB;
  v_total_investment NUMERIC;
  v_result JSONB;
BEGIN
  -- Start transaction block
  -- Validate inputs
  IF p_project IS NULL OR p_use_cases IS NULL OR p_implementation_costs IS NULL THEN
    RAISE EXCEPTION 'Missing required parameters';
  END IF;
  
  IF p_timeline_months < 1 OR p_timeline_months > 120 THEN
    RAISE EXCEPTION 'Timeline must be between 1 and 120 months';
  END IF;
  
  IF p_confidence_level < 0 OR p_confidence_level > 1 THEN
    RAISE EXCEPTION 'Confidence level must be between 0 and 1';
  END IF;

  -- Step 1: Create project
  INSERT INTO projects (
    client_name, 
    project_name, 
    industry, 
    description, 
    status
  )
  VALUES (
    p_project->>'client_name',
    p_project->>'project_name',
    p_project->>'industry',
    p_project->>'description',
    COALESCE(p_project->>'status', 'active')
  )
  RETURNING id INTO v_project_id;

  -- Step 2: Create use cases
  FOR v_use_case IN SELECT * FROM jsonb_array_elements(p_use_cases)
  LOOP
    INSERT INTO use_cases (
      project_id,
      name,
      category,
      current_state,
      future_state,
      implementation
    )
    VALUES (
      v_project_id,
      v_use_case->>'name',
      v_use_case->>'category',
      v_use_case->'current_state',
      v_use_case->'future_state',
      v_use_case->'implementation'
    )
    RETURNING id INTO v_use_case_ids[array_length(v_use_case_ids, 1) + 1];
  END LOOP;

  -- Step 3: Calculate total investment
  v_total_investment := 
    COALESCE((p_implementation_costs->>'software_licenses')::NUMERIC, 0) +
    COALESCE((p_implementation_costs->>'development_hours')::NUMERIC, 0) * 150 + -- $150/hour default rate
    COALESCE((p_implementation_costs->>'training_costs')::NUMERIC, 0) +
    COALESCE((p_implementation_costs->>'infrastructure')::NUMERIC, 0);

  -- Step 4: Create projection with placeholder calculations
  -- Note: Actual calculations should be done by the ROI engine
  INSERT INTO projections (
    project_id,
    scenario_name,
    metadata,
    implementation_costs,
    timeline_months,
    financial_metrics,
    calculations
  )
  VALUES (
    v_project_id,
    'Base Case',
    jsonb_build_object(
      'confidence_level', p_confidence_level,
      'assumptions', jsonb_build_array(
        'Standard implementation timeline',
        'Average industry adoption rates',
        'No major technical obstacles'
      ),
      'created_via', 'transactional_function'
    ),
    p_implementation_costs,
    p_timeline_months,
    jsonb_build_object(
      'conservative', jsonb_build_object('roi', 0, 'npv', 0),
      'expected', jsonb_build_object('roi', 0, 'npv', 0),
      'optimistic', jsonb_build_object('roi', 0, 'npv', 0)
    ),
    jsonb_build_object(
      'total_investment', v_total_investment,
      'net_present_value', 0,
      'internal_rate_of_return', 0,
      'payback_period_months', 0,
      'five_year_roi', 0,
      'break_even_date', NULL,
      'requires_calculation', true
    )
  )
  RETURNING id INTO v_projection_id;

  -- Build success response
  v_result := jsonb_build_object(
    'success', true,
    'project_id', v_project_id,
    'projection_id', v_projection_id,
    'use_case_count', array_length(v_use_case_ids, 1),
    'total_investment', v_total_investment,
    'message', 'Project created successfully. ROI calculations pending.'
  );

  RETURN v_result;

EXCEPTION
  WHEN OTHERS THEN
    -- Rollback is automatic in case of exception
    RETURN jsonb_build_object(
      'success', false,
      'error', SQLERRM,
      'error_detail', SQLSTATE
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function: update_project_with_use_cases
-- Purpose: Atomically update project and its use cases, regenerate projections
-- Returns: JSON object with update status
CREATE OR REPLACE FUNCTION update_project_with_use_cases(
  p_project_id UUID,
  p_project_updates JSONB DEFAULT NULL,
  p_use_cases_to_add JSONB DEFAULT NULL,
  p_use_cases_to_update JSONB DEFAULT NULL,
  p_use_cases_to_delete UUID[] DEFAULT NULL,
  p_regenerate_projection BOOLEAN DEFAULT false
) RETURNS JSONB AS $$
DECLARE
  v_result JSONB;
  v_use_case JSONB;
  v_update_count INTEGER := 0;
  v_add_count INTEGER := 0;
  v_delete_count INTEGER := 0;
  v_project_exists BOOLEAN;
BEGIN
  -- Check if project exists
  SELECT EXISTS(SELECT 1 FROM projects WHERE id = p_project_id) INTO v_project_exists;
  
  IF NOT v_project_exists THEN
    RAISE EXCEPTION 'Project % not found', p_project_id;
  END IF;

  -- Update project if updates provided
  IF p_project_updates IS NOT NULL THEN
    UPDATE projects
    SET 
      client_name = COALESCE(p_project_updates->>'client_name', client_name),
      project_name = COALESCE(p_project_updates->>'project_name', project_name),
      industry = COALESCE(p_project_updates->>'industry', industry),
      description = COALESCE(p_project_updates->>'description', description),
      status = COALESCE(p_project_updates->>'status', status)
    WHERE id = p_project_id;
  END IF;

  -- Add new use cases
  IF p_use_cases_to_add IS NOT NULL THEN
    FOR v_use_case IN SELECT * FROM jsonb_array_elements(p_use_cases_to_add)
    LOOP
      INSERT INTO use_cases (
        project_id,
        name,
        category,
        current_state,
        future_state,
        implementation
      )
      VALUES (
        p_project_id,
        v_use_case->>'name',
        v_use_case->>'category',
        v_use_case->'current_state',
        v_use_case->'future_state',
        v_use_case->'implementation'
      );
      v_add_count := v_add_count + 1;
    END LOOP;
  END IF;

  -- Update existing use cases
  IF p_use_cases_to_update IS NOT NULL THEN
    FOR v_use_case IN SELECT * FROM jsonb_array_elements(p_use_cases_to_update)
    LOOP
      UPDATE use_cases
      SET
        name = COALESCE(v_use_case->>'name', name),
        category = COALESCE(v_use_case->>'category', category),
        current_state = COALESCE(v_use_case->'current_state', current_state),
        future_state = COALESCE(v_use_case->'future_state', future_state),
        implementation = COALESCE(v_use_case->'implementation', implementation)
      WHERE id = (v_use_case->>'id')::UUID
        AND project_id = p_project_id;
      
      v_update_count := v_update_count + 1;
    END LOOP;
  END IF;

  -- Delete use cases
  IF p_use_cases_to_delete IS NOT NULL THEN
    DELETE FROM use_cases 
    WHERE id = ANY(p_use_cases_to_delete)
      AND project_id = p_project_id;
    
    GET DIAGNOSTICS v_delete_count = ROW_COUNT;
  END IF;

  -- Mark existing projections for recalculation if requested
  IF p_regenerate_projection THEN
    UPDATE projections
    SET calculations = jsonb_set(
      calculations,
      '{requires_recalculation}',
      'true'::jsonb
    )
    WHERE project_id = p_project_id;
  END IF;

  -- Build result
  v_result := jsonb_build_object(
    'success', true,
    'project_id', p_project_id,
    'use_cases_added', v_add_count,
    'use_cases_updated', v_update_count,
    'use_cases_deleted', v_delete_count,
    'projection_marked_for_update', p_regenerate_projection
  );

  RETURN v_result;

EXCEPTION
  WHEN OTHERS THEN
    -- Rollback is automatic
    RETURN jsonb_build_object(
      'success', false,
      'error', SQLERRM,
      'error_detail', SQLSTATE
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function: create_projection_with_simulation
-- Purpose: Create a projection and optionally run simulation in one transaction
-- Returns: JSON object with projection details
CREATE OR REPLACE FUNCTION create_projection_with_simulation(
  p_project_id UUID,
  p_projection_data JSONB,
  p_run_simulation BOOLEAN DEFAULT false,
  p_simulation_iterations INTEGER DEFAULT 10000
) RETURNS JSONB AS $$
DECLARE
  v_projection_id UUID;
  v_simulation_id UUID;
  v_result JSONB;
BEGIN
  -- Validate project exists
  IF NOT EXISTS(SELECT 1 FROM projects WHERE id = p_project_id) THEN
    RAISE EXCEPTION 'Project % not found', p_project_id;
  END IF;

  -- Create projection
  INSERT INTO projections (
    project_id,
    scenario_name,
    metadata,
    implementation_costs,
    timeline_months,
    financial_metrics,
    calculations
  )
  VALUES (
    p_project_id,
    COALESCE(p_projection_data->>'scenario_name', 'Base Case'),
    p_projection_data->'metadata',
    p_projection_data->'implementation_costs',
    (p_projection_data->>'timeline_months')::INTEGER,
    p_projection_data->'financial_metrics',
    p_projection_data->'calculations'
  )
  RETURNING id INTO v_projection_id;

  -- Create simulation result placeholder if requested
  IF p_run_simulation THEN
    INSERT INTO simulation_results (
      projection_id,
      simulation_type,
      results
    )
    VALUES (
      v_projection_id,
      'monte_carlo',
      jsonb_build_object(
        'status', 'pending',
        'iterations', p_simulation_iterations,
        'created_at', NOW()
      )
    )
    RETURNING id INTO v_simulation_id;
  END IF;

  -- Build result
  v_result := jsonb_build_object(
    'success', true,
    'projection_id', v_projection_id,
    'simulation_id', v_simulation_id,
    'simulation_pending', p_run_simulation
  );

  RETURN v_result;

EXCEPTION
  WHEN OTHERS THEN
    RETURN jsonb_build_object(
      'success', false,
      'error', SQLERRM,
      'error_detail', SQLSTATE
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function: delete_project_cascade
-- Purpose: Safely delete a project and all related data
-- Returns: JSON object with deletion summary
CREATE OR REPLACE FUNCTION delete_project_cascade(
  p_project_id UUID,
  p_confirm_delete BOOLEAN DEFAULT false
) RETURNS JSONB AS $$
DECLARE
  v_use_case_count INTEGER;
  v_projection_count INTEGER;
  v_simulation_count INTEGER;
  v_metrics_count INTEGER;
  v_result JSONB;
BEGIN
  -- Get counts before deletion
  SELECT COUNT(*) INTO v_use_case_count FROM use_cases WHERE project_id = p_project_id;
  SELECT COUNT(*) INTO v_projection_count FROM projections WHERE project_id = p_project_id;
  SELECT COUNT(*) INTO v_simulation_count 
  FROM simulation_results sr 
  JOIN projections p ON sr.projection_id = p.id 
  WHERE p.project_id = p_project_id;
  SELECT COUNT(*) INTO v_metrics_count 
  FROM actual_metrics am 
  JOIN projections p ON am.projection_id = p.id 
  WHERE p.project_id = p_project_id;

  IF NOT p_confirm_delete THEN
    -- Return what would be deleted without actually deleting
    RETURN jsonb_build_object(
      'success', false,
      'project_id', p_project_id,
      'would_delete', jsonb_build_object(
        'use_cases', v_use_case_count,
        'projections', v_projection_count,
        'simulations', v_simulation_count,
        'metrics', v_metrics_count
      ),
      'message', 'Set confirm_delete to true to proceed with deletion'
    );
  END IF;

  -- Perform cascade delete (relies on ON DELETE CASCADE)
  DELETE FROM projects WHERE id = p_project_id;

  v_result := jsonb_build_object(
    'success', true,
    'project_id', p_project_id,
    'deleted', jsonb_build_object(
      'use_cases', v_use_case_count,
      'projections', v_projection_count,
      'simulations', v_simulation_count,
      'metrics', v_metrics_count
    )
  );

  RETURN v_result;

EXCEPTION
  WHEN OTHERS THEN
    RETURN jsonb_build_object(
      'success', false,
      'error', SQLERRM,
      'error_detail', SQLSTATE
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function: validate_project_data
-- Purpose: Validate project data before creation/update
-- Returns: JSON object with validation results
CREATE OR REPLACE FUNCTION validate_project_data(
  p_project JSONB,
  p_use_cases JSONB
) RETURNS JSONB AS $$
DECLARE
  v_errors JSONB[] := '{}';
  v_warnings JSONB[] := '{}';
  v_use_case JSONB;
  v_idx INTEGER := 0;
BEGIN
  -- Validate project data
  IF p_project->>'client_name' IS NULL OR length(p_project->>'client_name') < 1 THEN
    v_errors := array_append(v_errors, jsonb_build_object(
      'field', 'client_name',
      'message', 'Client name is required'
    ));
  END IF;

  IF p_project->>'project_name' IS NULL OR length(p_project->>'project_name') < 1 THEN
    v_errors := array_append(v_errors, jsonb_build_object(
      'field', 'project_name',
      'message', 'Project name is required'
    ));
  END IF;

  IF p_project->>'industry' NOT IN (
    'financial_services', 'healthcare', 'retail', 'manufacturing',
    'technology', 'education', 'government', 'other'
  ) THEN
    v_errors := array_append(v_errors, jsonb_build_object(
      'field', 'industry',
      'message', 'Invalid industry value'
    ));
  END IF;

  -- Validate use cases
  IF jsonb_array_length(p_use_cases) = 0 THEN
    v_errors := array_append(v_errors, jsonb_build_object(
      'field', 'use_cases',
      'message', 'At least one use case is required'
    ));
  END IF;

  FOR v_use_case IN SELECT * FROM jsonb_array_elements(p_use_cases)
  LOOP
    v_idx := v_idx + 1;
    
    -- Check required fields
    IF v_use_case->>'name' IS NULL THEN
      v_errors := array_append(v_errors, jsonb_build_object(
        'field', format('use_cases[%s].name', v_idx),
        'message', 'Use case name is required'
      ));
    END IF;

    -- Validate current state
    IF NOT (v_use_case->'current_state' ? 'volume_per_month') THEN
      v_errors := array_append(v_errors, jsonb_build_object(
        'field', format('use_cases[%s].current_state.volume_per_month', v_idx),
        'message', 'Volume per month is required'
      ));
    ELSIF (v_use_case->'current_state'->>'volume_per_month')::NUMERIC <= 0 THEN
      v_warnings := array_append(v_warnings, jsonb_build_object(
        'field', format('use_cases[%s].current_state.volume_per_month', v_idx),
        'message', 'Volume per month should be greater than 0'
      ));
    END IF;

    -- Validate future state percentages
    IF (v_use_case->'future_state'->>'automation_percentage')::NUMERIC > 1 THEN
      v_errors := array_append(v_errors, jsonb_build_object(
        'field', format('use_cases[%s].future_state.automation_percentage', v_idx),
        'message', 'Automation percentage cannot exceed 100%'
      ));
    END IF;
  END LOOP;

  RETURN jsonb_build_object(
    'valid', array_length(v_errors, 1) = 0,
    'errors', to_jsonb(v_errors),
    'warnings', to_jsonb(v_warnings)
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant execute permissions on functions
GRANT EXECUTE ON FUNCTION create_project_with_details TO authenticated;
GRANT EXECUTE ON FUNCTION update_project_with_use_cases TO authenticated;
GRANT EXECUTE ON FUNCTION create_projection_with_simulation TO authenticated;
GRANT EXECUTE ON FUNCTION delete_project_cascade TO authenticated;
GRANT EXECUTE ON FUNCTION validate_project_data TO authenticated;

-- Add helpful comments
COMMENT ON FUNCTION create_project_with_details IS 'Atomically creates a project with use cases and initial projection';
COMMENT ON FUNCTION update_project_with_use_cases IS 'Atomically updates project and related use cases';
COMMENT ON FUNCTION create_projection_with_simulation IS 'Creates projection with optional simulation placeholder';
COMMENT ON FUNCTION delete_project_cascade IS 'Safely deletes project and all related data with confirmation';
COMMENT ON FUNCTION validate_project_data IS 'Validates project and use case data before operations';