<?php

/**
 * Migration 0027 — engagement columns on pageviews (behavior tracker)
 *
 * Phase-1 behavior tracking layers on top of the existing pageview row, no
 * new table and no cookies. The frontend renderer stamps a random
 * `view_token` onto the row and into the page; a ~1KB first-party script
 * measures active engagement time + scroll depth + exit screen and fires one
 * navigator.sendBeacon on exit. AnalyticsService::ingestBeacon() updates the
 * row by token. The token lives only in page memory (regenerated each load),
 * so this stays cookieless / consent-banner-free.
 *
 *   view_token       random correlation id (page ↔ row), '' when unset
 *   engaged_seconds  active (visible + interacting) seconds on the page
 *   max_scroll       deepest scroll reached, 0..100 %
 *   exit_screen      screen index active at exit (screen-mode); -1 = n/a
 *   beaconed         1 once the tracker reported (separates real bounces
 *                    from JS-off / instant-leave when averaging)
 *
 * idx_token backs the beacon UPDATE. Idempotent column guard.
 *
 * @since 2.1.1008
 */

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

return [
    'description' => 'Add engagement columns to pageviews (behavior tracker)',

    'up' => [],

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

        $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;

        $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;
        };

        $adds = [];
        if (!$columnExists('view_token'))      $adds[] = "ADD COLUMN view_token      CHAR(16) NOT NULL DEFAULT ''";
        if (!$columnExists('engaged_seconds')) $adds[] = "ADD COLUMN engaged_seconds SMALLINT UNSIGNED NOT NULL DEFAULT 0";
        if (!$columnExists('max_scroll'))      $adds[] = "ADD COLUMN max_scroll      TINYINT UNSIGNED NOT NULL DEFAULT 0";
        if (!$columnExists('exit_screen'))     $adds[] = "ADD COLUMN exit_screen     SMALLINT NOT NULL DEFAULT -1";
        if (!$columnExists('beaconed'))        $adds[] = "ADD COLUMN beaconed        TINYINT(1) NOT NULL DEFAULT 0";

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

        $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_token'
            )
        ) > 0;
        if (!$idxExists) {
            \Instinctor\Core\DB::query("ALTER TABLE {$views} ADD KEY idx_token (view_token)");
        }
    },

    'down' => [
        "ALTER TABLE {prefix}instinctor_pageviews
            DROP COLUMN view_token, DROP COLUMN engaged_seconds,
            DROP COLUMN max_scroll, DROP COLUMN exit_screen, DROP COLUMN beaconed",
    ],
];
