<?php

function migrate_037($pdo) {
    echo "Starting migration 037 (Process Subjects)...
";

    // 1. process_instances: add subject_type, subject_id, cycle_key, bni_group_id
    
    $stmt = $pdo->query("SHOW COLUMNS FROM process_instances LIKE 'subject_type'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE process_instances ADD COLUMN subject_type VARCHAR(20) NOT NULL DEFAULT 'person' AFTER person_id");
        echo "Added subject_type to process_instances.
";
    }

    $stmt = $pdo->query("SHOW COLUMNS FROM process_instances LIKE 'subject_id'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE process_instances ADD COLUMN subject_id INT NOT NULL DEFAULT 0 AFTER subject_type");
        echo "Added subject_id to process_instances.
";
    }

    $stmt = $pdo->query("SHOW COLUMNS FROM process_instances LIKE 'cycle_key'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE process_instances ADD COLUMN cycle_key VARCHAR(64) NULL AFTER subject_id");
        echo "Added cycle_key to process_instances.
";
    }

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

    // 2. Data migration
    echo "Migrating existing data...
";
    $pdo->exec("UPDATE process_instances SET subject_type = 'person', subject_id = person_id WHERE subject_id = 0");

    // 3. Add indexes
    $stmt = $pdo->query("SHOW INDEX FROM process_instances WHERE Key_name = 'idx_subject_active'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE process_instances ADD INDEX idx_subject_active (subject_type, subject_id, current_status)");
        echo "Added index idx_subject_active.
";
    }

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

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

    echo "Migration 037 completed.
";
}