<?php

function migrate_036($pdo) {
    echo "Starting migration 036 (Process Versioning)...
";

    // 1. Process Definitions
    $stmt = $pdo->query("SHOW INDEX FROM process_definitions WHERE Key_name = 'code'");
    if ($stmt->fetch()) {
        $pdo->exec("ALTER TABLE process_definitions DROP INDEX `code`");
        echo "Dropped unique index on process_definitions.code.
";
    }

    $stmt = $pdo->query("SHOW COLUMNS FROM process_definitions LIKE 'supersedes_definition_id'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE process_definitions ADD COLUMN supersedes_definition_id INT(11) UNSIGNED NULL AFTER version");
        echo "Added supersedes_definition_id to process_definitions.
";
    }

    $stmt = $pdo->query("SHOW COLUMNS FROM process_definitions LIKE 'is_latest'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE process_definitions ADD COLUMN is_latest TINYINT(1) NOT NULL DEFAULT 1 AFTER supersedes_definition_id");
        echo "Added is_latest to process_definitions.
";
    }

    $stmt = $pdo->query("SHOW INDEX FROM process_definitions WHERE Key_name = 'idx_code_latest_active'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE process_definitions ADD INDEX idx_code_latest_active (code, is_latest, is_active)");
        echo "Added index idx_code_latest_active on process_definitions.
";
    }

    // Ensure existing rows are marked as latest properly (all existing should be latest)
    // Nothing needed, default is 1.

    // 2. Process Instances
    $stmt = $pdo->query("SHOW INDEX FROM process_instances WHERE Key_name = 'person_process'");
    if ($stmt->fetch()) {
        $pdo->exec("ALTER TABLE process_instances DROP INDEX `person_process`");
        echo "Dropped unique index on process_instances.person_process.
";
    }

    $stmt = $pdo->query("SHOW INDEX FROM process_instances WHERE Key_name = 'idx_person_definition'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE process_instances ADD INDEX idx_person_definition (person_id, process_definition_id)");
        echo "Added index idx_person_definition on process_instances.
";
    }

    echo "Migration 036 completed.
";
}