<?php
// Handles DB table creation, folder setup, and activation hook.

defined('ABSPATH') || exit;

function spdfed_activate_plugin() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'spdfed_logs';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS $table_name (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        email varchar(100) NOT NULL,
        submitter_name varchar(255) NULL DEFAULT NULL, /* Ensure consistent definition */
        file_name varchar(255) NOT NULL,
        downloaded_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
        post_id mediumint(9) NULL DEFAULT NULL,
        occurred_local_date DATE NULL,
        email_hash VARBINARY(32) NULL,
        referrer varchar(255) NULL,
        ua_hash VARBINARY(32) NULL,
        ip_hash VARBINARY(32) NULL,
        PRIMARY KEY  (id),
        KEY email (email(100)),
        KEY file_name (file_name(191)),
        KEY analytics_lookup (occurred_local_date, post_id),
        KEY post_date (post_id, occurred_local_date)
    ) $charset_collate;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
    
    // Set database version for future upgrades
    update_option('spdfed_db_version', '2.0');

    // Verify table creation
    if ($wpdb->last_error) {
        return false;
    }

    // Verify table exists
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
    $table_check = $wpdb->get_var(
        $wpdb->prepare(
            "SHOW TABLES LIKE %s",
            $wpdb->esc_like($table_name)
        )
    );

    if (!$table_check) {
        return false;
    }

    // Create private uploads directory
    $dir = WP_CONTENT_DIR . '/private-uploads';
    if (!file_exists($dir)) {
        if (!wp_mkdir_p($dir)) {
            return false;
        }
        // Create index.php for security
        file_put_contents($dir . '/index.php', '<?php // Silence is golden');
        // Create .htaccess to prevent direct access
        file_put_contents($dir . '/.htaccess', 'deny from all');
    }

    // Set default options
    add_option('spdfed_enable_gdpr', 1);
    add_option('spdfed_gdpr_message', 'I consent to my email being stored for the purposes of this download.');

    // Call the function to register CPTs and flush rules
    if (function_exists('spdfed_rewrite_flush_on_activation')) {
        spdfed_rewrite_flush_on_activation();
    }

    return true;
}

/**
 * Check if plugin is properly installed
 * @return bool
 */
function spdfed_check_installation() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'spdfed_logs';
    
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
    $table_exists = $wpdb->get_var(
        $wpdb->prepare(
            "SHOW TABLES LIKE %s",
            $wpdb->esc_like($table_name)
        )
    );

    if (!$table_exists) {
        return spdfed_activate_plugin();
    }

    return true;
}

/**
 * Upgrade database schema if needed
 */
function spdfed_maybe_upgrade_database() {
    $current_version = get_option('spdfed_db_version', '1.0');
    
    if (version_compare($current_version, '2.0', '<')) {
        spdfed_upgrade_to_version_2();
    }
}

/**
 * Upgrade to version 2.0 - adds analytics columns
 */
function spdfed_upgrade_to_version_2() {
    global $wpdb;
    $table_name = esc_sql($wpdb->prefix . 'spdfed_logs');
    
    // Add new columns if they don't exist
    $columns_to_add = [
        'post_id' => 'ADD COLUMN post_id mediumint(9) NULL DEFAULT NULL',
        'occurred_local_date' => 'ADD COLUMN occurred_local_date DATE NULL',
        'email_hash' => 'ADD COLUMN email_hash VARBINARY(32) NULL',
        'referrer' => 'ADD COLUMN referrer varchar(255) NULL',
        'ua_hash' => 'ADD COLUMN ua_hash VARBINARY(32) NULL',
        'ip_hash' => 'ADD COLUMN ip_hash VARBINARY(32) NULL'
    ];
    
    foreach ($columns_to_add as $column => $sql_fragment) {
        // Check if column exists (schema upgrade operation)
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $column_exists = $wpdb->get_results($wpdb->prepare(
            "SHOW COLUMNS FROM `{$table_name}` LIKE %s", /* phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared */
            $column
        ));
        
        if (empty($column_exists)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $wpdb->query("ALTER TABLE `{$table_name}` {$sql_fragment}");
        }
    }
    
    // Add indexes (schema upgrade operation)
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    $wpdb->query("ALTER TABLE `{$table_name}` ADD INDEX analytics_lookup (occurred_local_date, post_id)");
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    $wpdb->query("ALTER TABLE `{$table_name}` ADD INDEX post_date (post_id, occurred_local_date)");
    
    // Populate occurred_local_date for existing records (one-time upgrade operation)
    $sql = "UPDATE `{$table_name}` SET occurred_local_date = DATE(CONVERT_TZ(downloaded_at, 'UTC', @@session.time_zone)) WHERE occurred_local_date IS NULL AND downloaded_at IS NOT NULL"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared
    $wpdb->query($sql);
    
    // Clear analytics cache after schema upgrade
    wp_cache_delete('spdfed_has_analytics_columns');
    
    update_option('spdfed_db_version', '2.0');
}

/**
 * Check if the analytics columns exist in the logs table
 */
function spdfed_table_has_analytics_columns() {
    global $wpdb;
    static $has_analytics = null;
    
    if ($has_analytics === null) {
        // Check cache first
        $cache_key = 'spdfed_has_analytics_columns';
        $has_analytics = wp_cache_get($cache_key);
        
        if (false === $has_analytics) {
            $table_name = $wpdb->prefix . 'spdfed_logs';
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $columns = $wpdb->get_results("SHOW COLUMNS FROM `{$table_name}` LIKE 'occurred_local_date'");
            $has_analytics = !empty($columns);
            wp_cache_set($cache_key, $has_analytics, '', 3600); // Cache for 1 hour
        }
    }
    
    return $has_analytics;
}

// Add an admin notice if installation check fails
add_action('admin_init', function() {
    if (!spdfed_check_installation()) {
        add_action('admin_notices', function() {
            ?>
            <div class="notice notice-error">
                <p>Secure PDF Email Download plugin installation appears to be incomplete. Please deactivate and reactivate the plugin.</p>
            </div>
            <?php
        });
    } else {
        // Check for database upgrades
        spdfed_maybe_upgrade_database();
    }
});
