<?php

/**
 * Analytics Database Setup
 * Creates and manages database tables for tracking visitor analytics
 */

// Exit if accessed directly
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Add the property_id column + index to an existing table if missing.
 *
 * Groundwork for Kantan Hub (many sites → one aggregation dashboard): every
 * event table carries the property it belongs to. dbDelta is unreliable for
 * ALTERs on existing tables, so we check the column explicitly and ALTER by
 * hand — the same pattern used for last_seen/visitor_id below.
 */
function kantan_analytics_add_property_id_column($table_name) {
    global $wpdb;
    $col = $wpdb->get_results("SHOW COLUMNS FROM $table_name LIKE 'property_id'");
    if (empty($col)) {
        $wpdb->query("ALTER TABLE $table_name ADD COLUMN property_id varchar(64) DEFAULT NULL AFTER id");
        $wpdb->query("ALTER TABLE $table_name ADD KEY property_id (property_id)");
    }
}

/* Create analytics database table */
function kantan_analytics_create_tables() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_analytics';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        property_id varchar(64) DEFAULT NULL,
        session_id varchar(255) NOT NULL,
        page_url varchar(500) NOT NULL,
        page_title varchar(255) DEFAULT NULL,
        referrer varchar(500) DEFAULT NULL,
        traffic_source varchar(50) DEFAULT NULL,
        ai_assistant varchar(50) DEFAULT NULL,
        is_bounce tinyint(1) DEFAULT 1,
        visit_duration int(11) DEFAULT 0,
        screen_width int(11) DEFAULT NULL,
        screen_height int(11) DEFAULT NULL,
        user_agent varchar(500) DEFAULT NULL,
        ip_address varchar(45) DEFAULT NULL,
        country varchar(2) DEFAULT NULL,
        region varchar(100) DEFAULT NULL,
        visitor_id varchar(255) DEFAULT NULL,
        visit_date datetime DEFAULT CURRENT_TIMESTAMP,
        last_seen datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        KEY property_id (property_id),
        KEY session_id (session_id),
        KEY visit_date (visit_date),
        KEY last_seen (last_seen),
        KEY traffic_source (traffic_source),
        KEY country (country),
        KEY visitor_id (visitor_id)
    ) $charset_collate;";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);

    // Add property_id column for existing installations (Kantan Hub groundwork)
    kantan_analytics_add_property_id_column($table_name);

    // Add last_seen column if it doesn't exist (for existing installations)
    $column_exists = $wpdb->get_results("SHOW COLUMNS FROM $table_name LIKE 'last_seen'");
    if (empty($column_exists)) {
        $wpdb->query("ALTER TABLE $table_name ADD COLUMN last_seen datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER visit_date");
        $wpdb->query("ALTER TABLE $table_name ADD KEY last_seen (last_seen)");
    }

    // Add visitor_id column if it doesn't exist
    $visitor_col = $wpdb->get_results("SHOW COLUMNS FROM $table_name LIKE 'visitor_id'");
    if (empty($visitor_col)) {
        $wpdb->query("ALTER TABLE $table_name ADD COLUMN visitor_id varchar(255) DEFAULT NULL AFTER ip_address");
        $wpdb->query("ALTER TABLE $table_name ADD KEY visitor_id (visitor_id)");
    }

    // Add country/region columns (v2.27.0 geo). Resolved from the live IP at
    // visit time, then the IP is discarded — see kantan_track_visit / geo.php.
    $country_col = $wpdb->get_results("SHOW COLUMNS FROM $table_name LIKE 'country'");
    if (empty($country_col)) {
        $wpdb->query("ALTER TABLE $table_name ADD COLUMN country varchar(2) DEFAULT NULL AFTER ip_address");
        $wpdb->query("ALTER TABLE $table_name ADD COLUMN region varchar(100) DEFAULT NULL AFTER country");
        $wpdb->query("ALTER TABLE $table_name ADD KEY country (country)");
    }

    // Create clicks and pageviews tables (v2.0.0)
    kantan_analytics_create_clicks_table();
    kantan_analytics_create_pageviews_table();

    // Create UTM and conversions tables (v3.0.0)
    kantan_analytics_create_utm_table();
    kantan_analytics_create_conversions_table();

    // Create AI-crawler hits table (v2.23.0)
    kantan_analytics_create_crawler_hits_table();

    // Create video engagement tables (v2.24.0)
    kantan_analytics_create_video_tables();
}

/**
 * Create the video analytics tables.
 *
 * Three tables mirroring the spec's raw/aggregate/registry split:
 *  - wp_kantan_video_events      raw impressions/plays/progress/completes per
 *                                session; powers journey & conversion
 *                                correlation. Purged on the 90-day raw cron.
 *  - wp_kantan_video_engagement  durable 100-bucket aggregate per video
 *                                (bucket = 1% of duration): reached_count is
 *                                the retention/drop-off curve, plays_count the
 *                                rewatch heatmap. ~100 rows per video, survives
 *                                the raw purge.
 *  - wp_kantan_videos            registry + denormalized counters so the Video
 *                                tab list never scans the events table.
 */
function kantan_analytics_create_video_tables() {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

    $events_table = $wpdb->prefix . 'kantan_video_events';
    $sql = "CREATE TABLE IF NOT EXISTS $events_table (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        property_id varchar(64) DEFAULT NULL,
        session_id varchar(255) NOT NULL,
        visitor_id varchar(255) DEFAULT NULL,
        provider varchar(20) NOT NULL,
        video_key varchar(191) NOT NULL,
        page_url varchar(500) DEFAULT NULL,
        event_type varchar(20) NOT NULL,
        position int(11) DEFAULT 0,
        duration int(11) DEFAULT 0,
        watch_seconds int(11) DEFAULT 0,
        autoplay tinyint(1) DEFAULT 0,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        KEY property_id (property_id),
        KEY session_id (session_id),
        KEY video_key (video_key),
        KEY event_type (event_type),
        KEY created_at (created_at)
    ) $charset_collate;";
    dbDelta($sql);

    $engagement_table = $wpdb->prefix . 'kantan_video_engagement';
    $sql = "CREATE TABLE IF NOT EXISTS $engagement_table (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        property_id varchar(64) DEFAULT NULL,
        video_key varchar(191) NOT NULL,
        bucket tinyint(3) UNSIGNED NOT NULL,
        reached_count int(11) UNSIGNED NOT NULL DEFAULT 0,
        plays_count int(11) UNSIGNED NOT NULL DEFAULT 0,
        updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        UNIQUE KEY video_bucket (video_key, bucket),
        KEY property_id (property_id)
    ) $charset_collate;";
    dbDelta($sql);

    $videos_table = $wpdb->prefix . 'kantan_videos';
    $sql = "CREATE TABLE IF NOT EXISTS $videos_table (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        property_id varchar(64) DEFAULT NULL,
        video_key varchar(191) NOT NULL,
        provider varchar(20) NOT NULL,
        video_title varchar(255) DEFAULT NULL,
        duration int(11) DEFAULT 0,
        total_impressions bigint(20) UNSIGNED NOT NULL DEFAULT 0,
        total_plays bigint(20) UNSIGNED NOT NULL DEFAULT 0,
        total_completes bigint(20) UNSIGNED NOT NULL DEFAULT 0,
        total_watch_seconds bigint(20) UNSIGNED NOT NULL DEFAULT 0,
        first_seen datetime DEFAULT CURRENT_TIMESTAMP,
        last_seen datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        UNIQUE KEY video_key (video_key),
        KEY property_id (property_id),
        KEY provider (provider),
        KEY last_seen (last_seen)
    ) $charset_collate;";
    dbDelta($sql);
}

/**
 * Create the AI-crawler hits table.
 *
 * AI crawlers (GPTBot, ClaudeBot, PerplexityBot, CCBot, …) fetch pages
 * server-side and never run the JS beacon, so they are logged here on page load
 * (see kantan_log_ai_crawler_hit()). Kept SEPARATE from wp_kantan_analytics so
 * crawler traffic is visible without ever polluting human visitor metrics.
 */
function kantan_analytics_create_crawler_hits_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_crawler_hits';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        property_id varchar(64) DEFAULT NULL,
        crawler varchar(50) NOT NULL,
        crawler_type varchar(20) DEFAULT 'crawl',
        page_url varchar(500) NOT NULL,
        user_agent varchar(500) DEFAULT NULL,
        ip_address varchar(45) DEFAULT NULL,
        hit_time datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        KEY property_id (property_id),
        KEY crawler (crawler),
        KEY crawler_type (crawler_type),
        KEY hit_time (hit_time),
        KEY page_url (page_url(191))
    ) $charset_collate;";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
}

/**
 * Create clicks tracking table
 * Stores individual click events with position data
 */
function kantan_analytics_create_clicks_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_clicks';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        property_id varchar(64) DEFAULT NULL,
        session_id varchar(255) NOT NULL,
        page_url varchar(500) NOT NULL,
        click_x_pct tinyint(3) UNSIGNED NOT NULL,
        click_y_pct tinyint(3) UNSIGNED NOT NULL,
        click_region tinyint(1) UNSIGNED NOT NULL,
        element_tag varchar(50) DEFAULT NULL,
        element_text varchar(255) DEFAULT NULL,
        click_time datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        KEY property_id (property_id),
        KEY session_id (session_id),
        KEY page_url (page_url(191)),
        KEY click_time (click_time),
        KEY click_region (click_region)
    ) $charset_collate;";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);

    kantan_analytics_add_property_id_column($table_name);
}

/**
 * Create UTM parameters table
 * Stores UTM data per session before URL cleaning strips them
 */
function kantan_analytics_create_utm_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_utm';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        property_id varchar(64) DEFAULT NULL,
        session_id varchar(255) NOT NULL,
        utm_source varchar(255) DEFAULT NULL,
        utm_medium varchar(255) DEFAULT NULL,
        utm_campaign varchar(255) DEFAULT NULL,
        utm_term varchar(255) DEFAULT NULL,
        utm_content varchar(255) DEFAULT NULL,
        passthrough text DEFAULT NULL,
        landing_url varchar(500) DEFAULT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        UNIQUE KEY session_id (session_id),
        KEY property_id (property_id),
        KEY utm_source (utm_source),
        KEY utm_medium (utm_medium),
        KEY utm_campaign (utm_campaign),
        KEY created_at (created_at)
    ) $charset_collate;";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);

    kantan_analytics_add_property_id_column($table_name);
}

/**
 * Create conversions table
 * Stores conversion events (form submissions, phone clicks, etc.) linked to sessions
 */
function kantan_analytics_create_conversions_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_conversions';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        property_id varchar(64) DEFAULT NULL,
        session_id varchar(255) NOT NULL,
        visitor_id varchar(255) DEFAULT NULL,
        conversion_type varchar(50) NOT NULL,
        conversion_label varchar(255) DEFAULT NULL,
        conversion_value varchar(500) DEFAULT NULL,
        page_url varchar(500) DEFAULT NULL,
        converted_at datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        KEY property_id (property_id),
        KEY session_id (session_id),
        KEY visitor_id (visitor_id),
        KEY conversion_type (conversion_type),
        KEY converted_at (converted_at)
    ) $charset_collate;";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);

    kantan_analytics_add_property_id_column($table_name);

    // Add visitor_id column for existing installations (pre-2.9.4)
    $visitor_col = $wpdb->get_results("SHOW COLUMNS FROM $table_name LIKE 'visitor_id'");
    if (empty($visitor_col)) {
        $wpdb->query("ALTER TABLE $table_name ADD COLUMN visitor_id varchar(255) DEFAULT NULL AFTER session_id");
        $wpdb->query("ALTER TABLE $table_name ADD KEY visitor_id (visitor_id)");
    }
}

/**
 * Create pageviews tracking table
 * Stores individual page views with sequence and timing data for journey tracking
 */
function kantan_analytics_create_pageviews_table() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'kantan_pageviews';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT,
        property_id varchar(64) DEFAULT NULL,
        session_id varchar(255) NOT NULL,
        page_url varchar(500) NOT NULL,
        page_title varchar(255) DEFAULT NULL,
        sequence_num smallint(5) UNSIGNED NOT NULL,
        scroll_depth tinyint(3) UNSIGNED DEFAULT 0,
        time_on_page int(11) DEFAULT 0,
        entered_at datetime DEFAULT CURRENT_TIMESTAMP,
        exited_at datetime DEFAULT NULL,
        PRIMARY KEY  (id),
        KEY property_id (property_id),
        KEY session_id (session_id),
        KEY entered_at (entered_at),
        UNIQUE KEY session_sequence (session_id, sequence_num)
    ) $charset_collate;";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);

    kantan_analytics_add_property_id_column($table_name);

    // Add split test columns if they don't exist (for Kantan Split Testing integration)
    $test_col = $wpdb->get_results("SHOW COLUMNS FROM $table_name LIKE 'test_id'");
    if (empty($test_col)) {
        $wpdb->query("ALTER TABLE $table_name ADD COLUMN test_id bigint(20) DEFAULT NULL");
        $wpdb->query("ALTER TABLE $table_name ADD COLUMN variant_id bigint(20) DEFAULT NULL");
        $wpdb->query("ALTER TABLE $table_name ADD KEY test_id (test_id)");
        $wpdb->query("ALTER TABLE $table_name ADD KEY variant_id (variant_id)");
    }
}
