<?php

/**
 * Migration 0026 — UTM attribution columns on pageviews
 *
 * Referrer-host alone can't tell campaign channels apart (and app/email
 * clicks strip the referrer entirely). These three columns let the Stats
 * page attribute a visit to a tagged campaign link
 * (?utm_source=instagram&utm_medium=social&utm_campaign=launch) regardless
 * of referrer. Captured server-side in AnalyticsService::record() from the
 * request query string — still no JS, no cookies.
 *
 * idx_utm_day backs the "Top sources / campaigns" aggregate.
 *
 * Idempotent via INFORMATION_SCHEMA column guard.
 *
 * @since 2.1.1004
 */

if ( ! defined( 'ABSPATH' ) ) exit;

return [
    'description' => 'Add UTM attribution columns to pageviews',

    'up' => [],

    'php' => function () {
        $views = \Instinctor\Core\DB::prefix() . 'instinctor_pageviews';

        $columnExists = function ($column) use ($views) {
            return (int) \Instinctor\Core\DB::get_var(
                \Instinctor\Core\DB::prepare(
                    "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                    $views,
                    $column
                )
            ) > 0;
        };

        // Table may not exist yet on a fresh install where 0025 runs in the
        // same batch — skip silently; 0025 already created it, and a brand
        // new table would need these columns added here regardless.
        $tableExists = (int) \Instinctor\Core\DB::get_var(
            \Instinctor\Core\DB::prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s",
                $views
            )
        ) > 0;
        if (!$tableExists) return;

        $adds = [];
        if (!$columnExists('utm_source'))   $adds[] = "ADD COLUMN utm_source   VARCHAR(100) NOT NULL DEFAULT ''";
        if (!$columnExists('utm_medium'))   $adds[] = "ADD COLUMN utm_medium   VARCHAR(100) NOT NULL DEFAULT ''";
        if (!$columnExists('utm_campaign')) $adds[] = "ADD COLUMN utm_campaign VARCHAR(150) NOT NULL DEFAULT ''";

        if ($adds) {
            \Instinctor\Core\DB::query("ALTER TABLE {$views} " . implode(', ', $adds));
            if (\Instinctor\Core\DB::last_error() !== '') {
                throw new \RuntimeException(
                    esc_html('pageviews UTM column add failed: ' . \Instinctor\Core\DB::last_error())
                );
            }
        }

        // Index for the "Top sources" aggregate. Wrapped in try — a re-run
        // after the column add (but before this index) must still succeed,
        // and "duplicate key name" on a second run is non-fatal.
        $idxExists = (int) \Instinctor\Core\DB::get_var(
            \Instinctor\Core\DB::prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = %s",
                $views,
                'idx_utm_day'
            )
        ) > 0;
        if (!$idxExists) {
            \Instinctor\Core\DB::query("ALTER TABLE {$views} ADD KEY idx_utm_day (utm_source, day)");
        }
    },

    'down' => [
        "ALTER TABLE {prefix}instinctor_pageviews
            DROP COLUMN utm_source, DROP COLUMN utm_medium, DROP COLUMN utm_campaign",
    ],
];
