<?php

/**
 * Migration 0025 — built-in analytics tables
 *
 * Two tables back the no-GA analytics that ship with Instinctor 2.1.954+:
 *
 *   inst_instinctor_pageviews         one row per public page view
 *   inst_instinctor_analytics_salt    daily-rotating salt feeding the
 *                                     anonymous visitor_hash
 *
 * The pageviews table is append-only. visitor_hash is sha256(UA + IP/24 +
 * daily salt); the salt rotates at midnight UTC so the hash is non-
 * correlatable across days. No cookies, no JS — recording happens at the
 * top of impulse-page.php server-side. No PII stored, no consent banner
 * required in the EU.
 *
 * Indexes are sized for the queries the Stats admin page issues:
 *   - `idx_day`         site-wide views-by-day aggregate
 *   - `idx_page_day`    per-page views-by-day
 *   - `idx_ref_day`     top-referrer aggregate
 *   - `idx_country_day` country breakdown
 *   - `idx_device_day`  device breakdown
 *
 * Idempotent via INFORMATION_SCHEMA guard.
 *
 * @since 2.1.954
 */

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

return [
    'description' => 'Create analytics tables (pageviews + daily salt)',

    'up' => [],

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

        $tableExists = function ($name) {
            return (int) \Instinctor\Core\DB::get_var(
                \Instinctor\Core\DB::prepare(
                    "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
                     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s",
                    $name
                )
            ) > 0;
        };

        if (!$tableExists($views)) {
            \Instinctor\Core\DB::query(
                "CREATE TABLE {$views} (
                    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                    page_id         INT UNSIGNED NULL,
                    path            VARCHAR(512) NOT NULL DEFAULT '',
                    ts              DATETIME NOT NULL,
                    day             DATE NOT NULL,
                    visitor_hash    CHAR(64) NOT NULL DEFAULT '',
                    referrer_host   VARCHAR(255) NOT NULL DEFAULT '',
                    country         CHAR(2) NOT NULL DEFAULT '',
                    device          VARCHAR(10) NOT NULL DEFAULT '',
                    is_bot          TINYINT(1) NOT NULL DEFAULT 0,
                    PRIMARY KEY (id),
                    KEY idx_day (day),
                    KEY idx_page_day (page_id, day),
                    KEY idx_ref_day (referrer_host, day),
                    KEY idx_country_day (country, day),
                    KEY idx_device_day (device, day),
                    KEY idx_visitor_day (visitor_hash, day)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
            );
            if (\Instinctor\Core\DB::last_error() !== '') {
                throw new \RuntimeException(
                    esc_html('pageviews table create failed: ' . \Instinctor\Core\DB::last_error())
                );
            }
        }

        if (!$tableExists($salt)) {
            \Instinctor\Core\DB::query(
                "CREATE TABLE {$salt} (
                    day  DATE NOT NULL,
                    salt CHAR(32) NOT NULL,
                    PRIMARY KEY (day)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
            );
            if (\Instinctor\Core\DB::last_error() !== '') {
                throw new \RuntimeException(
                    esc_html('analytics_salt table create failed: ' . \Instinctor\Core\DB::last_error())
                );
            }
        }
    },

    'down' => [
        "DROP TABLE IF EXISTS {prefix}instinctor_pageviews",
        "DROP TABLE IF EXISTS {prefix}instinctor_analytics_salt",
    ],
];
