<?php

namespace PageVICO\Database;

use PageVICO\Geo;
defined( 'ABSPATH' ) || exit;
class Installer {
    public static function install() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();
        // Use constants for table names
        $table_sessions = PageVICO_TABLE_SESSIONS;
        $table_pageviews = PageVICO_TABLE_PAGEVIEWS;
        Geo\GeoService::ensureDatabase();
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        // Check if the database supports partitioning
        $mysql_version = $wpdb->get_var( "SELECT VERSION()" );
        $partition_support = version_compare( $mysql_version, '5.7.0', '>=' );
        // Create sessions table (no partitioning needed)
        dbDelta( "\n            CREATE TABLE {$table_sessions} (\n                session_id VARCHAR(64) NOT NULL,\n                ip_hash CHAR(64),\n                country VARCHAR(64),\n                region VARCHAR(64),\n                city VARCHAR(64),\n                user_agent TEXT,\n                device_type VARCHAR(32),\n                browser VARCHAR(64),\n                os VARCHAR(64),\n                screen_width SMALLINT,\n                screen_height SMALLINT,\n                utm_source VARCHAR(128),\n                utm_medium VARCHAR(128),\n                utm_campaign VARCHAR(128),\n                utm_term VARCHAR(128),\n                utm_content VARCHAR(128),\n                first_seen DATETIME DEFAULT CURRENT_TIMESTAMP,\n                last_seen DATETIME DEFAULT CURRENT_TIMESTAMP,\n                is_bot TINYINT(1) DEFAULT 0,\n                is_active TINYINT(1) DEFAULT 1,\n                PRIMARY KEY (session_id),\n                KEY idx_first_seen (first_seen),\n                KEY idx_last_seen (last_seen),\n                KEY idx_device_type (device_type),\n                KEY idx_browser (browser),\n                KEY idx_country (country),\n                KEY idx_city (city),\n                KEY idx_region (region)\n            ) {$charset_collate};\n        " );
        // Create pageviews table with or without partitioning based on MySQL version
        if ( $partition_support ) {
            // Calculate actual date values instead of using expressions
            $tomorrow = date( 'Y-m-d', strtotime( '+1 day' ) );
            $max_date = '9999-12-31';
            // Far future date for MAXVALUE
            // Try to create with partitioning
            $result = $wpdb->query( "CREATE TABLE IF NOT EXISTS {$table_pageviews} (\n\t\t\t\t\tid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,\n\t\t\t\t\tsession_id VARCHAR(64) NOT NULL,\n\t\t\t\t\tpage_url TEXT NOT NULL,\n\t\t\t\t\tpost_type VARCHAR(20) NOT NULL,\n\t\t\t\t\tpost_id BIGINT UNSIGNED,\n\t\t\t\t\treferrer TEXT,\n\t\t\t\t\tcreated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n\t\t\t\t\tPRIMARY KEY (id, created_at),\n\t\t\t\t\tKEY idx_session_id (session_id),\n\t\t\t\t\tKEY idx_post_type (post_type),\n\t\t\t\t\tKEY idx_post_id (post_id),\n\t\t\t\t\tKEY idx_created_at (created_at)\n\t\t\t\t) {$charset_collate}\n\t\t\t\tPARTITION BY RANGE (TO_DAYS(created_at)) (\n\t\t\t\t\tPARTITION p_current VALUES LESS THAN (TO_DAYS('{$tomorrow}')),\n\t\t\t\t\tPARTITION p_future VALUES LESS THAN (TO_DAYS('{$max_date}'))\n\t\t\t\t)" );
            if ( $result === false ) {
                // Fallback to non-partitioned table
                $wpdb->query( "CREATE TABLE IF NOT EXISTS {$table_pageviews} (\n\t\t\t\t\t\tid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,\n\t\t\t\t\t\tsession_id VARCHAR(64) NOT NULL,\n\t\t\t\t\t\tpage_url TEXT NOT NULL,\n\t\t\t\t\t\tpost_type VARCHAR(20) NOT NULL,\n\t\t\t\t\t\tpost_id BIGINT UNSIGNED,\n\t\t\t\t\t\treferrer TEXT,\n\t\t\t\t\t\tcreated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n\t\t\t\t\t\tPRIMARY KEY (id),\n\t\t\t\t\t\tKEY idx_session_id (session_id),\n\t\t\t\t\t\tKEY idx_post_type (post_type),\n\t\t\t\t\t\tKEY idx_post_id (post_id),\n\t\t\t\t\t\tKEY idx_created_at (created_at)\n\t\t\t\t\t) {$charset_collate}" );
            }
        } else {
            // Create without partitioning for older MySQL versions
            dbDelta( "\n\t\t\t\tCREATE TABLE {$table_pageviews} (\n\t\t\t\t\tid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,\n\t\t\t\t\tsession_id VARCHAR(64) NOT NULL,\n\t\t\t\t\tpage_url TEXT NOT NULL,\n\t\t\t\t\tpost_type VARCHAR(20) NOT NULL,\n\t\t\t\t\tpost_id BIGINT UNSIGNED,\n\t\t\t\t\treferrer TEXT,\n\t\t\t\t\tcreated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n\t\t\t\t\tPRIMARY KEY (id),\n\t\t\t\t\tKEY idx_session_id (session_id),\n\t\t\t\t\tKEY idx_post_type (post_type),\n\t\t\t\t\tKEY idx_post_id (post_id),\n\t\t\t\t\tKEY idx_created_at (created_at)\n\t\t\t\t) {$charset_collate};\n\t\t\t\t" );
        }
        // Only try to set up partitions if MySQL version supports it
        if ( $partition_support ) {
            // Set up the monthly partition management in a try-catch to avoid fatal errors
            try {
                self::initialize_partitions();
            } catch ( \Exception $e ) {
                // Error initializing partitions silently
            }
        }
    }

    /**
     * Initialize partitions for all partitioned tables
     */
    public static function initialize_partitions() {
        // Schedule the partition management event if not already scheduled
        if ( !wp_next_scheduled( 'pagevico_manage_partitions' ) ) {
            wp_schedule_event( time(), 'daily', 'pagevico_manage_partitions' );
        }
        // Run initial partition setup
        try {
            self::manage_partitions();
        } catch ( \Exception $e ) {
            return false;
        }
        return true;
    }

    /**
     * Create and manage partitions for the analytics tables
     * This runs daily to ensure we have partitions for the next 3 months
     */
    public static function manage_partitions() {
        global $wpdb;
    }

    /**
     * Create monthly partitions for a table
     */
    private static function create_monthly_partitions( $table ) {
        global $wpdb;
        // Get the date column name for this table
        $date_column = self::get_date_column_for_table( $table );
        if ( !$date_column ) {
            return false;
        }
        try {
            // Check if the table already has partitions
            $has_partitions = false;
            $partitions = $wpdb->get_results( "SELECT PARTITION_NAME \n\t                                       FROM INFORMATION_SCHEMA.PARTITIONS \n\t                                       WHERE TABLE_SCHEMA = DATABASE() \n\t                                       AND TABLE_NAME = '{$table}'\n\t                                       AND PARTITION_NAME IS NOT NULL" );
            $has_partitions = !empty( $partitions );
            if ( $has_partitions ) {
                // If it has partitions but only the default ones, reorganize them
                if ( count( $partitions ) <= 2 ) {
                    // Start with a clean slate but keep the data
                    // We'll keep the current partitioning structure but reorganize it
                } else {
                    // Already has multiple partitions, don't recreate
                    return true;
                }
            } else {
                // Try to add partitioning if the table exists but is not partitioned
                // First, ensure the primary key includes the date column
                $table_structure = $wpdb->get_row( "SHOW CREATE TABLE {$table}", ARRAY_N );
                if ( !empty( $table_structure[1] ) ) {
                    if ( strpos( $table_structure[1], "PRIMARY KEY (`id`, `{$date_column}`)" ) === false ) {
                        // Need to modify the primary key
                        $result = $wpdb->query( "ALTER TABLE {$table} DROP PRIMARY KEY, ADD PRIMARY KEY(id, {$date_column})" );
                        if ( $result === false ) {
                            return false;
                        }
                    }
                }
            }
            // Create partition definition with explicit dates
            $partition_sql = "ALTER TABLE {$table} PARTITION BY RANGE (TO_DAYS({$date_column})) (";
            // Add partition for current month
            $current_date = new \DateTime('first day of this month');
            $partition_name = 'p_' . $current_date->format( 'Y_m' );
            $next_month = clone $current_date;
            $next_month->modify( '+1 month' );
            $next_month_date = $next_month->format( 'Y-m-d' );
            $partition_sql .= "PARTITION {$partition_name} VALUES LESS THAN (TO_DAYS('{$next_month_date}')),";
            // Add partitions for next 3 months
            for ($i = 1; $i <= 3; $i++) {
                $month_start = clone $next_month;
                $partition_name = 'p_' . $month_start->format( 'Y_m' );
                $next_month->modify( '+1 month' );
                $next_month_date = $next_month->format( 'Y-m-d' );
                $partition_sql .= "PARTITION {$partition_name} VALUES LESS THAN (TO_DAYS('{$next_month_date}')),";
            }
            // Add catch-all partition for future dates with explicit max date
            $max_date = '9999-12-31';
            $partition_sql .= "PARTITION p_future VALUES LESS THAN (TO_DAYS('{$max_date}')))";
            // Execute the partition SQL
            $result = $wpdb->query( $partition_sql );
            if ( $result === false ) {
                return false;
            }
            return true;
        } catch ( \Exception $e ) {
            return false;
        }
    }

    /**
     * Add new partitions for future months
     */
    private static function add_future_partitions( $table, $partitions ) {
        global $wpdb;
        // Get the date column name for this table
        $date_column = self::get_date_column_for_table( $table );
        if ( !$date_column ) {
            return false;
        }
        // Find the latest month partition
        $latest_month = null;
        foreach ( $partitions as $partition ) {
            if ( $partition->PARTITION_NAME == 'p_future' ) {
                continue;
            }
            if ( preg_match( '/^p_(\\d{4})_(\\d{2})$/', $partition->PARTITION_NAME, $matches ) ) {
                $year = $matches[1];
                $month = $matches[2];
                $date = new \DateTime("{$year}-{$month}-01");
                if ( $latest_month === null || $date > $latest_month ) {
                    $latest_month = $date;
                }
            }
        }
        // If no monthly partitions found, return
        if ( $latest_month === null ) {
            return false;
        }
        // Calculate what the next three months should be
        $next_month = clone $latest_month;
        $next_month->modify( '+1 month' );
        // Check if we need to add more partitions to cover the next 3 months
        $three_months_future = new \DateTime('first day of +3 months');
        // Add monthly partitions until we have coverage for the next 3 months
        while ( $next_month < $three_months_future ) {
            $partition_name = 'p_' . $next_month->format( 'Y_m' );
            $month_after = clone $next_month;
            $month_after->modify( '+1 month' );
            $month_after_date = $month_after->format( 'Y-m-d' );
            // Check if this partition already exists
            $exists = false;
            foreach ( $partitions as $partition ) {
                if ( $partition->PARTITION_NAME == $partition_name ) {
                    $exists = true;
                    break;
                }
            }
            // Add the partition if it doesn't exist
            if ( !$exists ) {
                // Use specific date value instead of a dynamic expression
                $sql = "ALTER TABLE {$table} REORGANIZE PARTITION p_future INTO (\n\t                    PARTITION {$partition_name} VALUES LESS THAN (TO_DAYS('{$month_after_date}')),\n\t                    PARTITION p_future VALUES LESS THAN (TO_DAYS('9999-12-31'))\n\t                )";
                $result = $wpdb->query( $sql );
                if ( $result === false ) {
                    return false;
                }
            }
            // Move to the next month
            $next_month = $month_after;
        }
        return true;
    }

    /**
     * Archive partitions older than the retention period
     */
    private static function archive_old_partitions( $table, $partitions ) {
        global $wpdb;
        // Get retention period from settings (default to 12 months)
        $retention_months = intval( get_option( 'data_retention_months', 12 ) );
        // Allow developers to override via filter
        $retention_months = apply_filters( 'PageVICO_partition_retention_months', $retention_months );
        // Calculate the cutoff date
        $cutoff_date = new \DateTime('first day of -' . $retention_months . ' months');
        // Go through each partition and drop if older than retention period
        foreach ( $partitions as $partition ) {
            if ( $partition->PARTITION_NAME == 'p_future' ) {
                continue;
            }
            if ( preg_match( '/^p_(\\d{4})_(\\d{2})$/', $partition->PARTITION_NAME, $matches ) ) {
                $year = $matches[1];
                $month = $matches[2];
                $partition_date = new \DateTime("{$year}-{$month}-01");
                // If partition is older than cutoff, archive it
                if ( $partition_date < $cutoff_date ) {
                    // Option 1: Drop the partition (delete data)
                    // $sql = "ALTER TABLE $table DROP PARTITION " . $partition->PARTITION_NAME;
                    // Option 2: Archive the data to an archive table instead of deleting
                    $archive_table = $table . '_archive';
                    // Create archive table if it doesn't exist
                    if ( $wpdb->get_var( "SHOW TABLES LIKE '{$archive_table}'" ) != $archive_table ) {
                        // Get table structure
                        $table_structure = $wpdb->get_row( "SHOW CREATE TABLE {$table}", ARRAY_N );
                        if ( !empty( $table_structure[1] ) ) {
                            // Create archive table with the same structure but without partitioning
                            $create_sql = str_replace( "CREATE TABLE `{$table}`", "CREATE TABLE `{$archive_table}`", $table_structure[1] );
                            // Remove partitioning clauses
                            $create_sql = preg_replace( '/PARTITION BY.*$/s', '', $create_sql );
                            $wpdb->query( $create_sql );
                        }
                    }
                    // Archive data before dropping partition
                    $date_column = self::get_date_column_for_table( $table );
                    if ( $date_column && $wpdb->get_var( "SHOW TABLES LIKE '{$archive_table}'" ) == $archive_table ) {
                        // Copy data from the partition to the archive table
                        $wpdb->query( "INSERT INTO {$archive_table} SELECT * FROM {$table} PARTITION(" . $partition->PARTITION_NAME . ")" );
                        // Now drop the partition
                        $wpdb->query( "ALTER TABLE {$table} DROP PARTITION " . $partition->PARTITION_NAME );
                    }
                }
            }
        }
        return true;
    }

    /**
     * Get the date column used for partitioning for each table
     */
    private static function get_date_column_for_table( $table ) {
        global $wpdb;
        if ( strpos( $table, 'pageviews' ) !== false ) {
            return 'created_at';
        } else {
            if ( strpos( $table, 'goal_conversions' ) !== false ) {
                return 'triggered_at';
            } else {
                if ( strpos( $table, 'heatmap_clicks' ) !== false ) {
                    return 'created_at';
                }
            }
        }
        return false;
    }

}
