<?php
namespace FleekDash\Database\Migrations;

use FleekDash\Database\Core\BaseMigration;

/**
 * Create Analytics Tracking Tables
 *
 * Real analytics system with pageviews, sessions, and daily aggregation
 * Converted from database/Database/Migrations/CreateAnalyticsTables.php
 */
return new class extends BaseMigration {

    public function up(): void
    {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        // Table for page views
        $table_pageviews = $wpdb->prefix . 'fleekdash_pageviews';
        $sql_pageviews = "CREATE TABLE IF NOT EXISTS $table_pageviews (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) DEFAULT NULL,
            session_id varchar(255) NOT NULL,
            page_url varchar(500) NOT NULL,
            page_title varchar(500) DEFAULT NULL,
            post_id bigint(20) DEFAULT NULL,
            post_type varchar(100) DEFAULT NULL,
            referrer varchar(500) DEFAULT NULL,
            user_agent text DEFAULT NULL,
            ip_address varchar(45) DEFAULT NULL,
            country_code varchar(2) DEFAULT NULL,
            country_name varchar(100) DEFAULT NULL,
            city varchar(100) DEFAULT NULL,
            device_type varchar(50) DEFAULT NULL,
            browser varchar(100) DEFAULT NULL,
            os varchar(100) DEFAULT NULL,
            viewed_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            time_on_page int(11) DEFAULT NULL,
            is_bounce tinyint(1) DEFAULT 0,
            PRIMARY KEY (id),
            KEY idx_session_id (session_id),
            KEY idx_page_url (page_url(255)),
            KEY idx_post_id (post_id),
            KEY idx_viewed_at (viewed_at),
            KEY idx_user_id (user_id),
            KEY idx_country_code (country_code),
            KEY idx_device_type (device_type)
        ) $charset_collate;";

        // Table for sessions
        $table_sessions = $wpdb->prefix . 'fleekdash_sessions';
        $sql_sessions = "CREATE TABLE IF NOT EXISTS $table_sessions (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            session_id varchar(255) NOT NULL,
            user_id bigint(20) DEFAULT NULL,
            first_visit datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_visit datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            total_pageviews int(11) DEFAULT 1,
            session_duration int(11) DEFAULT 0,
            is_bounce tinyint(1) DEFAULT 1,
            referrer varchar(500) DEFAULT NULL,
            referrer_domain varchar(255) DEFAULT NULL,
            referrer_type varchar(50) DEFAULT NULL,
            landing_page varchar(500) DEFAULT NULL,
            exit_page varchar(500) DEFAULT NULL,
            country_code varchar(2) DEFAULT NULL,
            country_name varchar(100) DEFAULT NULL,
            city varchar(100) DEFAULT NULL,
            device_type varchar(50) DEFAULT NULL,
            browser varchar(100) DEFAULT NULL,
            os varchar(100) DEFAULT NULL,
            ip_address varchar(45) DEFAULT NULL,
            user_agent text DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY idx_session_id (session_id),
            KEY idx_user_id (user_id),
            KEY idx_first_visit (first_visit),
            KEY idx_country_code (country_code),
            KEY idx_device_type (device_type),
            KEY idx_referrer_domain (referrer_domain)
        ) $charset_collate;";

        // Table for daily analytics summary
        $table_daily = $wpdb->prefix . 'fleekdash_analytics_daily';
        $sql_daily = "CREATE TABLE IF NOT EXISTS $table_daily (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            date date NOT NULL,
            unique_visitors int(11) DEFAULT 0,
            total_pageviews int(11) DEFAULT 0,
            total_sessions int(11) DEFAULT 0,
            bounce_rate decimal(5,2) DEFAULT 0.00,
            avg_session_duration int(11) DEFAULT 0,
            new_visitors int(11) DEFAULT 0,
            returning_visitors int(11) DEFAULT 0,
            new_users int(11) DEFAULT 0,
            top_pages text DEFAULT NULL,
            top_referrers text DEFAULT NULL,
            device_breakdown text DEFAULT NULL,
            country_breakdown text DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_date (date),
            KEY idx_created_at (created_at)
        ) $charset_collate;";

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

        dbDelta($sql_pageviews);
        dbDelta($sql_sessions);
        dbDelta($sql_daily);

        add_option('fleekdash_analytics_tables_version', '1.0.0');
        add_option('fleekdash_analytics_installed_at', current_time('mysql'));
    }

    public function down(): void
    {
        $this->dropTable('fleekdash_analytics_daily');
        $this->dropTable('fleekdash_sessions');
        $this->dropTable('fleekdash_pageviews');

        delete_option('fleekdash_analytics_tables_version');
        delete_option('fleekdash_analytics_installed_at');
    }
};

