<?php

/**
 * Fix tracking tables foreign keys for sites upgraded from old versions.
 *
 * Issues fixed:
 * 1. email_tracking: contact_id FK not removed by allow_anonymous_tracking migration
 *    (dbDelta created the FK with an auto-generated name, so dropForeign couldn't find it)
 * 2. email_tracking: duplicate batch_id FKs (one auto-generated by dbDelta, one added by migration)
 * 3. click_tracking: same contact_id FK issue
 */

return function (\MailerPress\Core\Migrations\SchemaBuilder $schema) {
    global $wpdb;

    $emailTrackingTable = $wpdb->prefix . 'mailerpress_email_tracking';
    $clickTrackingTable = $wpdb->prefix . 'mailerpress_click_tracking';

    // ========================================
    // FIX 1: email_tracking — remove orphan contact_id FK
    // ========================================
    $tableExists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $wpdb->esc_like($emailTrackingTable)));
    if ($tableExists) {
        // Find all FKs on contact_id column (regardless of constraint name)
        $contactFKs = $wpdb->get_col(
            "SELECT CONSTRAINT_NAME
             FROM information_schema.KEY_COLUMN_USAGE
             WHERE TABLE_NAME = '{$emailTrackingTable}'
               AND CONSTRAINT_SCHEMA = DATABASE()
               AND COLUMN_NAME = 'contact_id'
               AND REFERENCED_TABLE_NAME IS NOT NULL"
        );

        foreach ($contactFKs as $fkName) {
            $wpdb->query("ALTER TABLE `{$emailTrackingTable}` DROP FOREIGN KEY `{$fkName}`");
        }

        // Fix duplicate batch_id FKs — keep only one
        $batchFKs = $wpdb->get_col(
            "SELECT CONSTRAINT_NAME
             FROM information_schema.KEY_COLUMN_USAGE
             WHERE TABLE_NAME = '{$emailTrackingTable}'
               AND CONSTRAINT_SCHEMA = DATABASE()
               AND COLUMN_NAME = 'batch_id'
               AND REFERENCED_TABLE_NAME IS NOT NULL"
        );

        if (count($batchFKs) > 1) {
            // Keep the first one, drop the rest
            $duplicates = array_slice($batchFKs, 1);
            foreach ($duplicates as $fkName) {
                $wpdb->query("ALTER TABLE `{$emailTrackingTable}` DROP FOREIGN KEY `{$fkName}`");
            }
        }
    }

    // ========================================
    // FIX 2: click_tracking — remove orphan contact_id FK
    // ========================================
    $clickExists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $wpdb->esc_like($clickTrackingTable)));
    if ($clickExists) {
        $contactFKs = $wpdb->get_col(
            "SELECT CONSTRAINT_NAME
             FROM information_schema.KEY_COLUMN_USAGE
             WHERE TABLE_NAME = '{$clickTrackingTable}'
               AND CONSTRAINT_SCHEMA = DATABASE()
               AND COLUMN_NAME = 'contact_id'
               AND REFERENCED_TABLE_NAME IS NOT NULL"
        );

        foreach ($contactFKs as $fkName) {
            $wpdb->query("ALTER TABLE `{$clickTrackingTable}` DROP FOREIGN KEY `{$fkName}`");
        }
    }
};
