process_opportunity.php

process_opportunity2.php

<?php
// process_opportunity.php (or your chosen script name for this specific workflow)

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL); // Use E_ALL for comprehensive error reporting

// Adjust paths as necessary based on your project structure
require_once __DIR__ . '/../vendor/autoload.php';
require_once './ApiClient/BaseApiClient.php';
require_once './ApiClient/DynamicsApiClient.php';
require_once './ApiClient/NfceApiClient.php'; // Assuming this is the NCFE client
require_once './Handlers/LoggingHandler.php';
require_once './Handlers/DatabaseHandler.php';
require_once './Handlers/WorkflowLogger.php'; // NEW: Include the WorkflowLogger

use Dotenv\Dotenv;
use Handlers\LoggingHandler;
use Handlers\DatabaseHandler;
use Handlers\WorkflowLogger; // Use the WorkflowLogger class
use ApiClient\DynamicsApiClient;
use ApiClient\NfceApiClient;
use Exception; // Ensure Exception class is imported

// Set content type for JSON response
header('Content-Type: application/json');

// --- Helper Functions for Request Processing and Filtering ---

/**
 * Extracts all HTTP headers from the $_SERVER superglobal.
 *
 * @return array
 */
function get_all_request_headers(): array
{
    $headers = [];
    foreach ($_SERVER as $name => $value) {
        if (str_starts_with($name, 'HTTP_')) {
            $headers[str_replace(' ', '-', ucwords(strtolower(str_replace('_', ' ', substr($name, 5)))))] = $value;
        }
    }
    return $headers;
}

/**
 * Reads the raw request body and attempts to decode it as JSON.
 *
 * @return array|null
 */
function get_request_body_json(): ?array
{
    $rawBody = file_get_contents('php://input');
    if (empty($rawBody)) {
        return null;
    }
    $jsonBody = json_decode($rawBody, true);
    return json_last_error() === JSON_ERROR_NONE ? $jsonBody : null;
}

/**
 * Evaluates a JSONPath-like string against a data array/object.
 * Supports dot notation and [*] for array iteration.
 * Returns an array of all matching values.
 *
 * @param array|object $data The data to search within.
 * @param string $path The JSONPath-like string (e.g., "user.address.street", "items[*].id").
 * @return array An array of all values found at the specified path.
 */
function evaluate_path($data, string $path): array
{
    if (!is_array($data) && !is_object($data)) {
        return [];
    }

    $segments = explode('.', $path);
    $currentValues = [$data];
    $results = [];

    foreach ($segments as $segment) {
        $nextValues = [];
        foreach ($currentValues as $currentValue) {
            if (!is_array($currentValue) && !is_object($currentValue)) {
                continue;
            }

            if ($segment === '[*]') {
                if (is_array($currentValue)) {
                    foreach ($currentValue as $item) {
                        $nextValues[] = $item;
                    }
                }
            } elseif (is_array($currentValue) && isset($currentValue[$segment])) {
                $nextValues[] = $currentValue[$segment];
            } elseif (is_object($currentValue) && isset($currentValue->$segment)) {
                $nextValues[] = $currentValue->$segment;
            }
        }
        $currentValues = $nextValues;
    }

    // Filter out non-scalar values if the path leads to a final value
    foreach ($currentValues as $value) {
        if (is_scalar($value) || is_null($value)) {
            $results[] = $value;
        }
    }
    return $results;
}

/**
 * Performs wildcard matching using fnmatch.
 *
 * @param string $pattern The pattern with wildcards (*, ?).
 * @param string $subject The string to match against.
 * @return bool
 */
function match_wildcard(string $pattern, string $subject): bool
{
    return fnmatch($pattern, $subject);
}

/**
 * Evaluates a single condition based on operator and target value.
 *
 * @param mixed $value The actual value from the request data.
 * @param string $operator The comparison operator (e.g., 'equals', 'contains', 'starts_with', 'ends_with', 'matches_wildcard').
 * @param mixed $targetValue The value to compare against.
 * @return bool
 */
function evaluate_condition($value, string $operator, $targetValue): bool
{
    // Ensure values are strings for string operations
    $value = (string)$value;
    $targetValue = (string)$targetValue;

    switch ($operator) {
        case 'equals':
            return $value === $targetValue;
        case 'contains':
            return str_contains($value, $targetValue);
        case 'starts_with':
            return str_starts_with($value, $targetValue);
        case 'ends_with':
            return str_ends_with($value, $targetValue);
        case 'matches_wildcard':
            return match_wildcard($targetValue, $value);
        default:
            return false; // Unknown operator
    }
}

/**
 * Evaluates a set of conditions against request data.
 *
 * @param array $requestData Contains 'query_params', 'headers', 'body'.
 * @param array $rulesConfig The configuration array for filtering rules.
 *   Example:
 *   [
 *       "conditions" => [
 *           ["source" => "query_param", "key" => "action", "operator" => "equals", "value" => "processUser"],
 *           ["source" => "header", "key" => "User-Agent", "operator" => "matches_wildcard", "value" => "Mozilla*"],
 *           ["source" => "body", "path" => "user.status", "operator" => "equals", "value" => "active"]
 *       ],
 *       "logic" => "AND" // or "OR"
 *   ]
 * @return bool True if all (AND) or any (OR) conditions are met.
 */
function evaluate_request_conditions(array $requestData, array $rulesConfig): bool
{
    $conditions = $rulesConfig['conditions'] ?? [];
    $logic = strtoupper($rulesConfig['logic'] ?? 'AND'); // Default to AND

    if (empty($conditions)) {
        return true; // No conditions means always pass
    }

    $overallResult = ($logic === 'AND'); // Start with true for AND, false for OR

    foreach ($conditions as $condition) {
        $source = $condition['source'] ?? null;
        $key = $condition['key'] ?? null; // For query_param, header
        $path = $condition['path'] ?? null; // For body
        $operator = $condition['operator'] ?? null;
        $targetValue = $condition['value'] ?? null;

        $valuesToEvaluate = [];

        switch ($source) {
            case 'query_param':
                if (isset($requestData['query_params'][$key])) {
                    $valuesToEvaluate[] = $requestData['query_params'][$key];
                }
                break;
            case 'header':
                // Headers are case-insensitive, but $_SERVER keys are normalized.
                // Use the normalized key from get_all_request_headers()
                $normalizedKey = str_replace(' ', '-', ucwords(strtolower(str_replace('_', ' ', $key))));
                if (isset($requestData['headers'][$normalizedKey])) {
                    $valuesToEvaluate[] = $requestData['headers'][$normalizedKey];
                }
                break;
            case 'body':
                if ($requestData['body_json'] && ($path !== null)) { // Use body_json here
                    $valuesToEvaluate = evaluate_path($requestData['body_json'], $path);
                }
                break;
            default:
                // Invalid source, this condition fails
                $conditionMet = false;
                break;
        }

        $conditionMet = false;
        foreach ($valuesToEvaluate as $value) {
            if (evaluate_condition($value, $operator, $targetValue)) {
                $conditionMet = true;
                break; // Found at least one match for this condition
            }
        }

        if ($logic === 'AND') {
            $overallResult = $overallResult && $conditionMet;
            if (!$overallResult) {
                return false; // Short-circuit for AND if any condition fails
            }
        } elseif ($logic === 'OR') {
            $overallResult = $overallResult || $conditionMet;
            if ($overallResult) {
                return true; // Short-circuit for OR if any condition passes
            }
        }
    }

    return $overallResult;
}

/**
 * Validates an HMAC signature.
 *
 * @param string $payload The raw request body.
 * @param string $signature The signature provided in the header.
 * @param string $secret The HMAC secret key.
 * @param string $algo The hashing algorithm (e.g., 'sha256', 'sha1').
 * @param string $prefix Optional prefix to remove from the signature (e.g., 'sha256=').
 * @return bool True if the signature is valid, false otherwise.
 */
function validate_hmac_signature(string $payload, string $signature, string $secret, string $algo, string $prefix = ''): bool
{
    if ($prefix && str_starts_with($signature, $prefix)) {
        $signature = substr($signature, strlen($prefix));
    }

    $expectedSignature = hash_hmac($algo, $payload, $secret, false); // raw_output = false for hex string

    // Use hash_equals for timing attack safe comparison
    return hash_equals($expectedSignature, $signature);
}

/**
 * Performs webhook HMAC validation based on configured rules.
 *
 * @param array $requestData Contains 'raw_body', 'headers'.
 * @param array $hmacConfigs Array of HMAC configurations.
 *   Example:
 *   [
 *       "hubspot" => [
 *           "enabled" => true,
 *           "header" => "X-HubSpot-Signature",
 *           "secret" => "YOUR_HUBSPOT_SECRET", // Direct secret value from DB config
 *           "algo" => "sha256",
 *           "prefix" => "sha256="
 *       ],
 *       "twilio" => [
 *           "enabled" => false,
 *           "header" => "X-Twilio-Signature",
 *           "secret" => "YOUR_TWILIO_SECRET", // Direct secret value from DB config
 *           "algo" => "sha1"
 *       ]
 *   ]
 * @param LoggingHandler $logger For logging validation attempts and failures.
 * @param string $workflowId For logging context.
 * @return bool True if validation is not enabled or passes for an enabled type, false otherwise.
 */
function validate_webhook_request(array $requestData, array $hmacConfigs, LoggingHandler $logger, string $workflowId): bool
{
    $rawBody = $requestData['raw_body'] ?? '';
    $headers = $requestData['headers'] ?? [];

    foreach ($hmacConfigs as $type => $config) {
        if (!($config['enabled'] ?? false)) {
            continue; // Skip if not enabled
        }

        $headerName = $config['header'] ?? null;
        $secret = $config['secret'] ?? null; // Now directly from DB config
        $algo = $config['algo'] ?? null;
        $prefix = $config['prefix'] ?? '';

        if (!$headerName || !$secret || !$algo) {
            $logger->logError("Workflow {$workflowId}: HMAC config for '{$type}' is incomplete (missing header, secret, or algo). Skipping validation.");
            continue;
        }

        $signature = $headers[$headerName] ?? null;
        if (!$signature) {
            $logger->logError("Workflow {$workflowId}: HMAC signature header '{$headerName}' missing for '{$type}' validation.");
            return false; // Signature required but not found
        }

        if (validate_hmac_signature($rawBody, $signature, $secret, $algo, $prefix)) {
            $logger->log("Workflow {$workflowId}: HMAC validation successful for '{$type}'.");
            return true; // Validation passed for this type
        } else {
            $logger->logError("Workflow {$workflowId}: HMAC validation FAILED for '{$type}'. Signature: '{$signature}', Payload: '{$rawBody}'.");
            return false; // Validation failed for this type
        }
    }

    // If no HMAC validation types are enabled, or loop completes without returning false,
    // it means validation is either not required or passed.
    $logger->log("Workflow {$workflowId}: No enabled HMAC validation types or all passed.");
    return true;
}

/**
 * Sets up all necessary database tables for logging and configuration.
 * This function is called early to ensure tables exist before logging/config operations.
 *
 * @param PDO $pdo The PDO connection (from WorkflowLogger's own connection).
 * @param LoggingHandler $logger The logger instance.
 */
function setup_database_tables(PDO $pdo, LoggingHandler $logger): void
{
    $tables = [
        "workflow_logs" => "
            CREATE TABLE IF NOT EXISTS workflow_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                workflow_id VARCHAR(255) NOT NULL,
                status VARCHAR(50) NOT NULL,
                message TEXT NOT NULL,
                context JSON,
                logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ",
        "api_logs" => "
            CREATE TABLE IF NOT EXISTS api_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                operation TEXT NOT NULL,
                request_data JSON,
                response_data JSON,
                logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ",
        "error_logs" => "
            CREATE TABLE IF NOT EXISTS error_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                message TEXT NOT NULL,
                context JSON,
                logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ",
        "webhook_logs" => "
            CREATE TABLE IF NOT EXISTS webhook_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                event_type VARCHAR(255) NOT NULL,
                event_id VARCHAR(255) NOT NULL,
                signature VARCHAR(255) NOT NULL,
                timestamp TIMESTAMP NOT NULL,
                body TEXT NOT NULL
            )
        ",
        "webhook_events" => "
            CREATE TABLE IF NOT EXISTS webhook_events (
                id INT AUTO_INCREMENT PRIMARY KEY,
                object_id INT NOT NULL,
                property_name VARCHAR(255),
                property_value VARCHAR(255),
                change_source VARCHAR(255),
                event_id INT NOT NULL,
                subscription_id INT NOT NULL,
                portal_id INT NOT NULL,
                app_id INT NOT NULL,
                occurred_at TIMESTAMP NOT NULL,
                event_type VARCHAR(255) NOT NULL,
                attempt_number INT NOT NULL
            )
        ",
        "data_triggers" => "
            CREATE TABLE IF NOT EXISTS data_triggers (
                trigger_id INT AUTO_INCREMENT PRIMARY KEY,
                trigger_category VARCHAR(255),
                trigger_propname VARCHAR(255) NOT NULL,
                trigger_propvalue VARCHAR(255) NOT NULL,
                trigger_process TINYINT(1) NOT NULL,
                trigger_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                trigger_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        ",
        "data_trigger_logs" => "
            CREATE TABLE IF NOT EXISTS data_trigger_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                trigger_id INT,
                trigger_category VARCHAR(255),
                trigger_propname VARCHAR(255),
                trigger_propvalue VARCHAR(255),
                trigger_wasprocessed BOOLEAN,
                trigger_webhook_event_id VARCHAR(255),
                trigger_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (trigger_id) REFERENCES data_triggers(trigger_id)
            )
        ",
        "execution_times" => "
            CREATE TABLE IF NOT EXISTS execution_times (
                id INT AUTO_INCREMENT PRIMARY KEY,
                operation VARCHAR(255) NOT NULL,
                round_time_ms INT NOT NULL
            )
        ",
        "warning_logs" => "
            CREATE TABLE IF NOT EXISTS warning_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                message TEXT NOT NULL,
                context TEXT,
                log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ",
        "tokens" => "
            CREATE TABLE IF NOT EXISTS tokens (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(255) NOT NULL UNIQUE,
                token TEXT NOT NULL,
                expiry INT NOT NULL
            )
        ",
        "email_templates" => "
            CREATE TABLE IF NOT EXISTS email_templates (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL UNIQUE,
                subject VARCHAR(255) NOT NULL,
                body TEXT NOT NULL
            )
        ",
        "app_configurations" => "
            CREATE TABLE IF NOT EXISTS app_configurations (
                id INT AUTO_INCREMENT PRIMARY KEY,
                config_name VARCHAR(255) NOT NULL UNIQUE,
                config_value JSON NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        "
    ];

    foreach ($tables as $tableName => $createQuery) {
        try {
            $pdo->exec($createQuery);
            $logger->log("Table '{$tableName}' ensured to exist.");
        } catch (PDOException $e) {
            $logger->logError("Failed to create table '{$tableName}': " . $e->getMessage());
            // Critical error, but don't die, just log and try next table
        }
    }
}


// --- Main Script Execution ---

// Generate a unique workflow ID for this request at the very beginning
$workflowId = uniqid('workflow_');
$oppRef = $_GET['opp'] ?? 'N/A'; // Initialize early for consistent logging

// Initialize logger, databaseHandler, and workflowLogger
$logger = null;
$databaseHandler = null;
$workflowLogger = null;

try {
    // Load environment variables
    $dotenv = Dotenv::createImmutable(__DIR__ . '/../'); // Adjust path if .env is elsewhere
    $dotenv->load();

    // Configure file-based logging
    $logConfig = [
        'file' => $_ENV['LOG_FILE'] ?? __DIR__ . '/../logs/app.log',
        'json_file' => $_ENV['LOG_JSON_FILE'] ?? __DIR__ . '/../logs/app.json',
        'json_logging_enabled' => filter_var($_ENV['JSON_LOGGING_ENABLED'] ?? 'false', FILTER_VALIDATE_BOOLEAN),
    ];
    $logger = new LoggingHandler($logConfig);

    // Configure database connection details
    $dbConfig = [
        'host' => $_ENV['DB_HOST'] ?? 'localhost',
        'dbname' => $_ENV['DB_NAME'] ?? 'api_integration',
        'user' => $_ENV['DB_USER'] ?? 'root',
        'password' => $_ENV['DB_PASSWORD'] ?? '',
    ];

    // Instantiate DatabaseHandler (your original, unchanged one)
    // Note: Your DatabaseHandler's constructor does not accept a logger.
    // If its connection fails, it will `die()`.
    $databaseHandler = null;
    if (filter_var($_ENV['DB_LOGGING_ENABLED'] ?? 'false', FILTER_VALIDATE_BOOLEAN)) {
        try {
            $databaseHandler = new DatabaseHandler($dbConfig);
        } catch (Exception $e) {
            // This catch block might not be reached if DatabaseHandler uses die()
            $logger->logError("Workflow {$workflowId}: Failed to initialize DatabaseHandler: " . $e->getMessage());
            $databaseHandler = null; // Ensure it's null if construction failed
        }
    }

    // Instantiate WorkflowLogger, passing the DatabaseHandler AND dbConfig to it.
    // WorkflowLogger will now create its OWN PDO connection and manage config fetching.
    if ($databaseHandler) { // Only instantiate workflow logger if DatabaseHandler was successfully initialized
        $workflowLogger = new WorkflowLogger($databaseHandler, $dbConfig); // Pass dbConfig for its own PDO
        // Ensure all tables are set up using WorkflowLogger's PDO
        if ($workflowLogger->pdo) { // Check if WorkflowLogger's PDO connected successfully
            setup_database_tables($workflowLogger->pdo, $logger);
        } else {
            $logger->logError("Workflow {$workflowId}: WorkflowLogger's PDO connection failed, cannot setup tables.");
        }
    }

    // --- Capture full incoming request details ---
    $requestHeaders = get_all_request_headers();
    $rawRequestBody = file_get_contents('php://input');
    $requestBodyJson = json_decode($rawRequestBody, true);
    $requestQueryParams = $_GET;

    $fullRequestData = [
        'method' => $_SERVER['REQUEST_METHOD'],
        'uri' => $_SERVER['REQUEST_URI'],
        'query_params' => $requestQueryParams,
        'headers' => $requestHeaders,
        'body_json' => $requestBodyJson, // Parsed JSON body
        'raw_body' => $rawRequestBody // Raw body string
    ];

    // Log incoming request details using WorkflowLogger
    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'incoming', 'Received HTTP request for opportunity processing.', $fullRequestData);
    }
    $logger->log("Workflow {$workflowId}: Incoming request received.", ['query_params' => $requestQueryParams, 'headers_count' => count($requestHeaders), 'body_present' => ($requestBodyJson !== null)]);

    // --- Fetch Configurations from Database or use Defaults ---
    $filteringRules = [];
    $hmacValidationConfigs = [];
    $ncfeAssignmentRules = [];

    if ($workflowLogger) {
        $dbFilteringRules = $workflowLogger->getConfiguration('filtering_rules');
        if ($dbFilteringRules) {
            $filteringRules = $dbFilteringRules;
            $logger->log("Workflow {$workflowId}: Loaded filtering rules from DB.");
        } else {
            $logger->logWarning("Workflow {$workflowId}: Filtering rules not found in DB. Using default/example rules.");
            // Default/Example Filtering Rules (if not found in DB)
            $filteringRules = [
                "conditions" => [
                    [
                        "source" => "query_param",
                        "key" => "action",
                        "operator" => "equals",
                        "value" => "process_opportunity"
                    ],
                    [
                        "source" => "header",
                        "key" => "Content-Type",
                        "operator" => "contains",
                        "value" => "application/json"
                    ]
                ],
                "logic" => "AND"
            ];
            // Optionally, store this default to DB for future use
             $workflowLogger->setConfiguration('filtering_rules', $filteringRules);
        }

        $dbHmacConfigs = $workflowLogger->getConfiguration('hmac_configs');
        if ($dbHmacConfigs) {
            $hmacValidationConfigs = $dbHmacConfigs;
            $logger->log("Workflow {$workflowId}: Loaded HMAC configurations from DB.");
        } else {
            $logger->logWarning("Workflow {$workflowId}: HMAC configurations not found in DB. Using default/example configs.");
            // Default/Example HMAC Configurations (if not found in DB)
            // IMPORTANT: Replace 'YOUR_HUBSPOT_SECRET' and 'YOUR_TWILIO_SECRET' with actual secrets
            // These should ideally be managed securely, not hardcoded even as defaults.
            $hmacValidationConfigs = [
                "hubspot" => [
                    "enabled" => false, // Set to true to enable
                    "header" => "X-HubSpot-Signature",
                    "secret" => $_ENV['HUBSPOT_WEBHOOK_SECRET'] ?? 'YOUR_HUBSPOT_SECRET', // Fallback to .env or hardcoded
                    "algo" => "sha256",
                    "prefix" => "sha256="
                ],
                "twilio" => [
                    "enabled" => false, // Set to true to enable
                    "header" => "X-Twilio-Signature",
                    "secret" => $_ENV['TWILIO_AUTH_TOKEN'] ?? 'YOUR_TWILIO_SECRET', // Fallback to .env or hardcoded
                    "algo" => "sha1"
                ]
            ];
            // Optionally, store this default to DB for future use
         $workflowLogger->setConfiguration('hmac_configs', $hmacValidationConfigs);
        }

        $dbNcfeAssignmentRules = $workflowLogger->getConfiguration('ncfe_assignment_rules');
        if ($dbNcfeAssignmentRules) {
            $ncfeAssignmentRules = $dbNcfeAssignmentRules;
            $logger->log("Workflow {$workflowId}: Loaded NCFE assignment rules from DB.");
        } else {
            $logger->logWarning("Workflow {$workflowId}: NCFE assignment rules not found in DB. Using default/example rules.");
            // Default/Example NCFE Assignment Rules (if not found in DB)
            $ncfeAssignmentRules = [
                "default_assignments" => [
                    "units" => [],
                    "modules" => [],
                    "diags" => []
                ],
                "funding_stream_rules" => [
                    "Adult Education Budget" => [
                        "units" => [
                            ["id" => 971, "diags" => [["diag" => 185, "progress" => 1], ["diag" => 184, "progress" => 1], ["diag" => 183, "progress" => 1]]]
                        ],
                        "modules" => [
                            ["id" => 568],
                            ["id" => 570]
                        ],
                        "diags" => []
                    ],
                    "Apprenticeship Levy" => [
                        "units" => [
                            ["id" => 211, "diags" => [["diag" => 199, "progress" => 1]]]
                        ],
                        "modules" => [
                            ["id" => 586]
                        ],
                        "diags" => []
                    ]
                ],
                "known_ncfe_items" => [
                    "units" => [
                        {"id": 568, "name": "Reform English Resources (FS)"},
                        {"id": 570, "name": "Reform Maths Resources (FS)"},
                        {"id": 971, "name": "Reform Assessments"},
                        {"id": 211, "name": "Digital Functional Skills"},
                        {"id": 975, "name": "Essential Digital Skills (Unit)"}
                    ],
                    "modules" => [
                        {"id": 183, "name": "Reform maths"},
                        {"id": 184, "name": "Reform english"},
                        {"id": 199, "name": "Essential Digital Skills Assessment"},
                        {"id": 586, "name": "Essential Digital Skills Resources"}
                    ],
                    "diagnostics" => [
                        {"id": 15, "name": "FS English Diagnostic"},
                        {"id": 16, "name": "FS Maths Diagnostic"},
                        {"id": 18, "name": "FS ICT Diagnostic"},
                        {"id": 23, "name": "Learning Styles Assessment"},
                        {"id": 60, "name": "GCSE English Diagnostic"},
                        {"id": 61, "name": "GCSE Maths Diagnostic"},
                        {"id": 70, "name": "Digital Skills Diagnostic"},
                        {"id": 183, "name": "Reform Maths Diag"},
                        {"id": 184, "name": "Reform English Diag"},
                        {"id": 185, "name": "Reform ICT Diag"},
                        {"id": 199, "name": "Essential Digital Skills Diag"}
                    ]
                ]
            ];
            // Optionally, store this default to DB for future use
         $workflowLogger->setConfiguration('ncfe_assignment_rules', $ncfeAssignmentRules);
        }
    } else {
        $logger->logError("Workflow {$workflowId}: WorkflowLogger not initialized. Cannot fetch DB configurations. Using hardcoded defaults.");
        // Fallback to hardcoded defaults if WorkflowLogger itself failed to initialize
        $filteringRules = [
            "conditions" => [
                ["source" => "query_param", "key" => "action", "operator" => "equals", "value" => "process_opportunity"]
            ],
            "logic" => "AND"
        ];
        $hmacValidationConfigs = [
            "hubspot" => ["enabled" => false, "header" => "X-HubSpot-Signature", "secret" => 'FALLBACK_HUBSPOT_SECRET', "algo" => "sha256", "prefix" => "sha256="],
            "twilio" => ["enabled" => false, "header" => "X-Twilio-Signature", "secret" => 'FALLBACK_TWILIO_SECRET', "algo" => "sha1"]
        ];
        $ncfeAssignmentRules = [
            "default_assignments" => ["units" => [], "modules" => [], "diags" => []],
            "funding_stream_rules" => [],
            "known_ncfe_items" => []
        ];
    }
    // --- End Fetch Configurations ---


    // --- HMAC Validation ---
    $isHmacValid = validate_webhook_request($fullRequestData, $hmacValidationConfigs, $logger, $workflowId);

    if (!$isHmacValid) {
        $errorMessage = "HMAC signature validation failed. Processing halted.";
        if ($workflowLogger) {
            $workflowLogger->logActivity($workflowId, 'failed', $errorMessage, ['reason' => 'hmac_validation_failed']);
        }
        $logger->logError("Workflow {$workflowId}: " . $errorMessage);
        http_response_code(401); // Unauthorized
        echo json_encode(['status' => 'error', 'message' => $errorMessage]);
        exit;
    }
    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'processing', "HMAC signature validated successfully.");
    }
    $logger->log("Workflow {$workflowId}: HMAC signature validated successfully.");
    // --- End HMAC Validation ---


    // --- Conditional Filtering based on Request Data ---
    $requestMeetsConditions = evaluate_request_conditions($fullRequestData, $filteringRules);

    if (!$requestMeetsConditions) {
        $errorMessage = "Request did not meet defined filtering conditions. Processing halted.";
        if ($workflowLogger) {
            $workflowLogger->logActivity($workflowId, 'failed', $errorMessage, ['filtering_rules' => $filteringRules]);
        }
        $logger->logError("Workflow {$workflowId}: " . $errorMessage);
        http_response_code(403); // Forbidden
        echo json_encode(['status' => 'error', 'message' => $errorMessage]);
        exit;
    }
    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'processing', "Request met filtering conditions. Proceeding.");
    }
    $logger->log("Workflow {$workflowId}: Request met filtering conditions. Proceeding.");
    // --- End Conditional Filtering ---


    // Initialize API Clients (pass the original DatabaseHandler to them)
    $dynamicsClientApi = new DynamicsApiClient($logger, $databaseHandler);
    $nfceApiClient = new NfceApiClient($logger, $databaseHandler);

    // 1. Receive HTTP Request with a query parameter &opp=$_GET["opp"]
    $oppRef = $_GET['opp'] ?? null;

    if (!$oppRef) {
        $errorMessage = 'Missing opportunity reference (opp) parameter in request.';
        if ($workflowLogger) {
            $workflowLogger->logActivity($workflowId, 'failed', $errorMessage, ['reason' => 'missing_parameter']);
        }
        $logger->logError("Workflow {$workflowId}: " . $errorMessage);
        echo json_encode(['status' => 'error', 'message' => $errorMessage]);
        exit;
    }

    // 2. Lookup Dynamics Opportunity by the Opportunity reference (active_opportunityref)
    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'processing', "Attempting to get Dynamics Opportunity ID.", ['opportunity_reference' => $oppRef]);
    }
    $logger->log("Workflow {$workflowId}: Attempting to get Dynamics Opportunity ID for OppRef: {$oppRef}");
    $opportunityId = $dynamicsClientApi->getOpportunityIdByOppRef($oppRef);

    if (!$opportunityId) {
        $errorMessage = "Dynamics Opportunity not found for OppRef: {$oppRef}.";
        if ($workflowLogger) {
            $workflowLogger->logActivity($workflowId, 'failed', $errorMessage, ['opportunity_reference' => $oppRef]);
        }
        $logger->logError("Workflow {$workflowId}: " . $errorMessage);
        echo json_encode(['status' => 'error', 'message' => $errorMessage]);
        exit;
    }
    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'processing', "Found Dynamics Opportunity ID.", ['opportunity_id' => $opportunityId]);
    }
    $logger->log("Workflow {$workflowId}: Found Dynamics Opportunity ID: {$opportunityId} for OppRef: {$oppRef}");

    // 3. Select all interesting columns from the opportunity
    $selectColumns = '$select=active_ncfeuser,active_ncfeassessments,active_ncfemodules,active_ncferoles,active_ncfegroups,active_courseinfo,active_opportunityref,active_skillsforwardaccountcreated,active_iduserinstitution,active_iduser,active_leadreferenceid,firstname,lastname,emailaddress1,active_fundingstream,address1_postalcode,mobilephone,telephone1,birthdate';
    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'processing', "Retrieving Dynamics Opportunity details.", ['opportunity_id' => $opportunityId]);
    }
    $logger->log("Workflow {$workflowId}: Attempting to get Dynamics Opportunity details for ID: {$opportunityId}");
    $opportunityDetails = $dynamicsClientApi->getOpportunityById($opportunityId, $selectColumns);

    if (!$opportunityDetails) {
        $errorMessage = "Failed to retrieve Dynamics Opportunity details for ID: {$opportunityId}.";
        if ($workflowLogger) {
            $workflowLogger->logActivity($workflowId, 'failed', $errorMessage, ['opportunity_id' => $opportunityId]);
        }
        $logger->logError("Workflow {$workflowId}: " . $errorMessage);
        echo json_encode(['status' => 'error', 'message' => $errorMessage]);
        exit;
    }
    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'processing', "Successfully retrieved Dynamics Opportunity details.", ['details_retrieved' => true, 'opportunity_ref' => $opportunityDetails['active_opportunityref'] ?? 'N/A', 'lead_ref' => $opportunityDetails['active_leadreferenceid'] ?? 'N/A']);
    }
    $logger->log("Workflow {$workflowId}: Successfully retrieved Dynamics Opportunity details for ID: {$opportunityId}");

    // Extract relevant data for NCFE user registration
    $firstName = $opportunityDetails['firstname'] ?? 'N/A';
    $lastName = $opportunityDetails['lastname'] ?? 'N/A';
    $email = $opportunityDetails['emailaddress1'] ?? null;
    $studentRef = $opportunityDetails['active_leadreferenceid'] ?? $oppRef; // Use lead ref or opp ref as student ref
    $phone1 = $opportunityDetails['mobilephone'] ?? $opportunityDetails['telephone1'] ?? null;
    $dob = isset($opportunityDetails['birthdate']) ? date('Y-m-d', strtotime($opportunityDetails['birthdate'])) : null;

    if (!$email) {
        $errorMessage = "Email address not found in Dynamics Opportunity for ID: {$opportunityId}. Cannot register NCFE user.";
        if ($workflowLogger) {
            $workflowLogger->logActivity($workflowId, 'failed', $errorMessage, ['opportunity_id' => $opportunityId]);
        }
        $logger->logError("Workflow {$workflowId}: " . $errorMessage);
        echo json_encode(['status' => 'error', 'message' => $errorMessage]);
        exit;
    }

    // Generate a simple username and password
    $username = strtolower(str_replace(' ', '', $firstName)) . '.' . strtolower(str_replace(' ', '', $lastName)) . '_' . uniqid();
    $password = bin2hex(random_bytes(8)); // 16 character hex string

    $registerUserData = [
        'firstName' => $firstName,
        'lastName' => $lastName,
        'email' => $email,
        'username' => $username,
        'password' => [
            'password' => $password,
            'format' => 'plain'
        ],
        'studentRef' => $studentRef,
        'sendWelcomeEmail' => false, // Set to true if you want NCFE to send welcome email
        'phone1' => $phone1,
        'dob' => $dob,
    ];

    // 4. Initialize NCFE API Client (already done at the top)
    // 5. Register a new user in NCFE
    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'processing', "Attempting NCFE user registration.", ['email' => $email, 'registration_data_preview' => ['firstName' => $firstName, 'lastName' => $lastName, 'username' => $username]]);
    }
    $logger->log("Workflow {$workflowId}: Attempting to register NCFE user with email: {$email}");
    $registerUserResponse = $nfceApiClient->registerUser($registerUserData);

    if (!$registerUserResponse || ($registerUserResponse['code'] !== 200 && !isset($registerUserResponse['messages']))) {
        $errorMessage = "Failed to register NCFE user for email: {$email}.";
        if ($workflowLogger) {
            $workflowLogger->logActivity($workflowId, 'failed', $errorMessage, ['email' => $email, 'ncfe_response' => $registerUserResponse]);
        }
        $logger->logError("Workflow {$workflowId}: " . $errorMessage . " Response: " . json_encode($registerUserResponse));
        echo json_encode(['status' => 'error', 'message' => $errorMessage]);
        exit;
    }
    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'processing', "NCFE user registration response received.", ['ncfe_response_code' => $registerUserResponse['code'], 'ncfe_messages' => $registerUserResponse['messages'] ?? 'N/A']);
    }
    $logger->log("Workflow {$workflowId}: NCFE user registration response: " . json_encode($registerUserResponse));

    // 6. Extract auto-assigned units/diagnostics
    $autoAssignedUnits = [];
    $autoAssignedDiags = [];
    if (isset($registerUserResponse['messages']) && is_array($registerUserResponse['messages'])) {
        foreach ($registerUserResponse['messages'] as $message) {
            if (str_starts_with($message, 'User assigned to unit:')) {
                if (preg_match('/unit: (\d+)/', $message, $matches)) {
                    $autoAssignedUnits[] = (int)$matches[1];
                }
                if (preg_match('/diagnostics: ([\d, ]+)/', $message, $matches)) {
                    $diags = array_map('intval', explode(',', $matches[1]));
                    $autoAssignedDiags = array_merge($autoAssignedDiags, $diags);
                }
            }
        }
    }
    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'processing', "Extracted auto-assigned NCFE items.", ['units' => $autoAssignedUnits, 'diagnostics' => $autoAssignedDiags]);
    }
    $logger->log("Workflow {$workflowId}: Auto-assigned NCFE units: " . json_encode($autoAssignedUnits) . ", diagnostics: " . json_encode($autoAssignedDiags));

    // 7. Lookup NCFE user details (idUser, idUserInstitution)
    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'processing', "Retrieving NCFE user details (idUser, idUserInstitution).", ['email' => $email]);
    }
    $logger->log("Workflow {$workflowId}: Attempting to get NCFE user details for email: {$email}");
    $ncfeUserDetailsResponse = $nfceApiClient->getUserDetail(['email' => $email]);

    $idUser = null;
    $idUserInstitution = null;
    $ncfeUserGroups = []; // Placeholder for groups if retrieved
    $ncfeUserRoles = ''; // Placeholder for role if retrieved

    if ($ncfeUserDetailsResponse && isset($ncfeUserDetailsResponse['data'][0])) {
        $idUser = $ncfeUserDetailsResponse['data'][0]['idUser'] ?? null;
        $idUserInstitution = $ncfeUserDetailsResponse['data'][0]['idUserInstitution'] ?? null;
        $ncfeUserGroups = $ncfeUserDetailsResponse['data'][0]['groups'] ?? [];
        $ncfeUserRoles = $ncfeUserDetailsResponse['data'][0]['roleTitle'] ?? '';
    }

    if (!$idUser || !$idUserInstitution) {
        $errorMessage = "Failed to retrieve idUser or idUserInstitution for NCFE user with email: {$email}.";
        if ($workflowLogger) {
            $workflowLogger->logActivity($workflowId, 'failed', $errorMessage, ['email' => $email, 'ncfe_response' => $ncfeUserDetailsResponse]);
        }
        $logger->logError("Workflow {$workflowId}: " . $errorMessage . " Response: " . json_encode($ncfeUserDetailsResponse));
        echo json_encode(['status' => 'error', 'message' => $errorMessage]);
        exit;
    }
    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'processing', "Retrieved NCFE idUser and idUserInstitution.", ['idUser' => $idUser, 'idUserInstitution' => $idUserInstitution, 'groups' => $ncfeUserGroups, 'role' => $ncfeUserRoles]);
    }
    $logger->log("Workflow {$workflowId}: Retrieved NCFE idUser: {$idUser}, idUserInstitution: {$idUserInstitution}");

    // 8. Conditionally assign additional NCFE modules/units/diagnostics based on DB config
    $fundingStream = $opportunityDetails['active_fundingstream'] ?? null;
    $unitsToAssign = $ncfeAssignmentRules['default_assignments']['units'] ?? [];
    $modulesToAssign = $ncfeAssignmentRules['default_assignments']['modules'] ?? [];
    $diagsToAssign = $ncfeAssignmentRules['default_assignments']['diags'] ?? [];

    if ($fundingStream && isset($ncfeAssignmentRules['funding_stream_rules'][$fundingStream])) {
        if ($workflowLogger) {
            $workflowLogger->logActivity($workflowId, 'processing', "Applying funding stream specific assignments for '{$fundingStream}'.");
        }
        $logger->log("Workflow {$workflowId}: Funding stream '{$fundingStream}' detected. Applying specific NCFE assignments.");
        $streamRules = $ncfeAssignmentRules['funding_stream_rules'][$fundingStream];
        $unitsToAssign = array_merge($unitsToAssign, $streamRules['units'] ?? []);
        $modulesToAssign = array_merge($modulesToAssign, $streamRules['modules'] ?? []);
        $diagsToAssign = array_merge($diagsToAssign, $streamRules['diags'] ?? []);
    } else {
        if ($workflowLogger) {
            $workflowLogger->logActivity($workflowId, 'processing', "No specific funding stream rules found for '{$fundingStream}'. Using default assignments.");
        }
        $logger->log("Workflow {$workflowId}: No specific funding stream rules found for '{$fundingStream}'. Using default assignments.");
    }

    $ncfeAssignedModules = [];
    $ncfeAssignedUnits = [];
    $ncfeAssignedDiags = [];

    if (!empty($unitsToAssign) || !empty($modulesToAssign) || !empty($diagsToAssign)) {
        $assignUnitsModulesData = [];
        if (!empty($unitsToAssign)) {
            $assignUnitsModulesData['units'] = $unitsToAssign;
        }
        if (!empty($modulesToAssign)) {
            $assignUnitsModulesData['modules'] = $modulesToAssign;
        }
        // Note: NCFE API's assignUnitsModules expects diags *within* units.
        // If $diagsToAssign contains standalone diags, you might need to map them to a generic unit or handle separately.
        // For now, assuming diags are either auto-assigned or part of units.
        // If $diagsToAssign are standalone, you might need a separate API call or a different structure.
        // For this example, I'll merge standalone diags into the first unit if available, or log a warning.
        if (!empty($diagsToAssign)) {
            if (!empty($assignUnitsModulesData['units'])) {
                // Merge standalone diags into the first unit's diags
                $existingDiags = $assignUnitsModulesData['units'][0]['diags'] ?? [];
                foreach ($diagsToAssign as $diag) {
                    $existingDiags[] = $diag;
                }
                $assignUnitsModulesData['units'][0]['diags'] = $existingDiags;
            } else {
                $logger->logWarning("Workflow {$workflowId}: Standalone diagnostics found but no units to attach them to for assignment. Skipping standalone diags assignment.");
            }
        }


        if ($workflowLogger) {
            $workflowLogger->logActivity($workflowId, 'processing', "Attempting to assign additional NCFE units/modules.", ['idUser' => $idUser, 'assignment_data' => $assignUnitsModulesData]);
        }
        $logger->log("Workflow {$workflowId}: Attempting to assign additional NCFE units/modules for idUser: {$idUser}. Data: " . json_encode($assignUnitsModulesData));
        $assignResponse = $nfceApiClient->assignUnitsModules(['idUser' => $idUser], $assignUnitsModulesData);

        if ($assignResponse && ($assignResponse['code'] === 1 || $assignResponse['code'] === 200)) {
            if ($workflowLogger) {
                $workflowLogger->logActivity($workflowId, 'processing', "Successfully assigned additional NCFE units/modules.", ['ncfe_response' => $assignResponse]);
            }
            $logger->log("Workflow {$workflowId}: Successfully assigned additional NCFE units/modules. Response: " . json_encode($assignResponse));

            if (isset($assignResponse['messages']['unit']['Success'])) {
                preg_match('/\[([\d,]+)\]/', $assignResponse['messages']['unit']['Success'], $matches);
                if (isset($matches[1])) {
                    $ncfeAssignedUnits = array_merge($ncfeAssignedUnits, array_map('intval', explode(',', $matches[1])));
                }
            }
            if (isset($assignResponse['messages']['unitdiags']['Success'])) {
                preg_match('/\[([\d,]+)\]/', $assignResponse['messages']['unitdiags']['Success'], $matches);
                if (isset($matches[1])) {
                    $ncfeAssignedDiags = array_merge($ncfeAssignedDiags, array_map('intval', explode(',', $matches[1])));
                }
            }
            if (isset($assignResponse['messages']['modules']['Success'])) {
                preg_match('/\[([\d,]+)\]/', $assignResponse['messages']['modules']['Success'], $matches);
                if (isset($matches[1])) {
                    $ncfeAssignedModules = array_merge($ncfeAssignedModules, array_map('intval', explode(',', $matches[1])));
                }
            }
        } else {
            $errorMessage = "Failed to assign additional NCFE units/modules for idUser: {$idUser}.";
            if ($workflowLogger) {
                $workflowLogger->logActivity($workflowId, 'processing_error', $errorMessage, ['idUser' => $idUser, 'ncfe_response' => $assignResponse]);
            }
            $logger->logError("Workflow {$workflowId}: " . $errorMessage . " Response: " . json_encode($assignResponse));
        }
    } else {
        if ($workflowLogger) {
            $workflowLogger->logActivity($workflowId, 'processing', "No additional NCFE units/modules to assign based on funding stream.", ['funding_stream' => $fundingStream]);
        }
        $logger->log("Workflow {$workflowId}: No additional NCFE units/modules to assign based on funding stream: {$fundingStream}");
    }

    // Combine auto-assigned and conditionally assigned items for Dynamics update
    $finalAssignedUnits = array_unique(array_merge($autoAssignedUnits, $ncfeAssignedUnits));
    $finalAssignedDiags = array_unique(array_merge($autoAssignedDiags, $ncfeAssignedDiags));
    $finalAssignedModules = array_unique($ncfeAssignedModules);

    // 9. Update the Dynamics about success
    $dynamicsUpdateData = [
        'active_ncfeuser' => true,
        'active_iduser' => (string)$idUser,
        'active_iduserinstitution' => (string)$idUserInstitution,
        'active_skillsforwardaccountcreated' => true,
        'active_ncfegroups' => json_encode($ncfeUserGroups), // Store actual groups if retrieved/assigned
        'active_ncferoles' => $ncfeUserRoles, // Store role if retrieved/assigned
        'active_ncfeunits' => json_encode($finalAssignedUnits), // Assuming a field for units
        'active_ncfeassessments' => json_encode($finalAssignedDiags), // Using 'active_ncfeassessments' for diagnostics
        'active_ncfemodules' => json_encode($finalAssignedModules),
        'active_ncfeusername' => $username // Assuming a field for NCFE username
    ];

    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'processing', "Attempting to update Dynamics Opportunity with NCFE details.", ['opportunity_id' => $opportunityId, 'update_data_keys' => array_keys($dynamicsUpdateData)]);
    }
    $logger->log("Workflow {$workflowId}: Attempting to patch Dynamics Opportunity ID: {$opportunityId} with NCFE details. Data: " . json_encode($dynamicsUpdateData));
    $patchSuccess = $dynamicsClientApi->patchOpportunityById($opportunityId, $dynamicsUpdateData);

    if (!$patchSuccess) {
        $errorMessage = "Failed to update Dynamics Opportunity ID: {$opportunityId} with NCFE details.";
        if ($workflowLogger) {
            $workflowLogger->logActivity($workflowId, 'failed', $errorMessage, ['opportunity_id' => $opportunityId, 'update_data' => $dynamicsUpdateData]);
        }
        $logger->logError("Workflow {$workflowId}: " . $errorMessage);
        echo json_encode(['status' => 'error', 'message' => $errorMessage]);
        exit;
    }
    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'success', "Successfully updated Dynamics Opportunity with NCFE details.", ['opportunity_id' => $opportunityId]);
    }
    $logger->log("Workflow {$workflowId}: Successfully updated Dynamics Opportunity ID: {$opportunityId} with NCFE details.");

    // Final success response for the entire workflow
    $successResponse = [
        'status' => 'success',
        'message' => 'Workflow completed successfully.',
        'workflow_id' => $workflowId,
        'opportunity_id' => $opportunityId,
        'dynamics_opp_ref' => $opportunityDetails['active_opportunityref'] ?? 'N/A',
        'dynamics_lead_ref' => $opportunityDetails['active_leadreferenceid'] ?? 'N/A',
        'ncfe_user_id' => $idUser,
        'ncfe_user_institution_id' => $idUserInstitution,
        'ncfe_username' => $username,
        'assigned_units' => $finalAssignedUnits,
        'assigned_diagnostics' => $finalAssignedDiags,
        'assigned_modules' => $finalAssignedModules,
        'ncfe_groups' => $ncfeUserGroups,
        'ncfe_role' => $ncfeUserRoles
    ];
    echo json_encode($successResponse);

} catch (Exception $e) {
    // Catch any unexpected exceptions during the workflow
    $errorMessage = 'An unexpected error occurred during workflow execution: ' . $e->getMessage();
    if ($workflowLogger) {
        $workflowLogger->logActivity($workflowId, 'failed', $errorMessage, ['exception' => $e->getMessage(), 'trace' => $e->getTraceAsString()]);
    }
    // Log to file as well
    if ($logger) {
        $logger->logError("Workflow {$workflowId}: " . $errorMessage . " Stack: " . $e->getTraceAsString());
    } else {
        // Fallback if logger itself failed to initialize
        error_log("CRITICAL ERROR: Workflow {$workflowId}: " . $errorMessage . " Stack: " . $e->getTraceAsString());
    }
    http_response_code(500); // Internal Server Error
    echo json_encode(['status' => 'error', 'message' => $errorMessage]);
    exit;
}