<?php
namespace MetForm_Pro\Core\Analytics\Helpers;
defined( 'ABSPATH' ) || exit;

class Database {
    private static $LOGS_TABLE             = 'mf_analytics_logs';
	private static $COUNTRIES_TABLE        = 'mf_analytics_countries';
	private static $REFERRERS_TABLE        = 'mf_analytics_referrers';
	private static $REFERRERS_LOG_TABLE    = 'mf_analytics_reffrearrs_log';

	public static $DATABASE_KEY	  = 'mf_analytics_db_version';
	public static $DATABASE_VERSION  = '1.0.7';

	/*
	 * This class is the data-access layer for MetForm's custom analytics tables.
	 * Direct $wpdb queries are required (no WP API exists for these tables). All
	 * table/column identifiers come from $wpdb->prefix and the trusted get_schema()
	 * allowlist (validated by is_safe_identifier()), and every user-supplied value
	 * is bound with $wpdb->prepare(). Results are short-lived analytics aggregates
	 * that are intentionally not cached. The DB sniffs below are therefore disabled
	 * for the whole class with this justification rather than per query.
	 */
	// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL

    /**
     * Resolve retention cutoff date from a configured range.
     *
     * @param string $range Retention range (6_months, 1_year, 2_years).
     * @return string|false Cutoff date in Y-m-d, or false when no cleanup should run.
     */
    private static function get_retention_cutoff_date( $range ) {
        $range = sanitize_text_field( (string) $range );

        switch ( $range ) {
            case '6_months':
                return gmdate( 'Y-m-d', strtotime( '-6 months' ) );
            case '1_year':
                return gmdate( 'Y-m-d', strtotime( '-1 year' ) );
            case '2_years':
                return gmdate( 'Y-m-d', strtotime( '-2 years' ) );
            default:
                return false;
        }
    }

    /**
     * Standard cleanup response for no-op/error outcomes.
     *
     * @param bool $success Whether cleanup outcome should be marked successful.
     * @return array<string,int|bool>
     */
    private static function get_cleanup_empty_response( $success = true ) {
        return [
            'success'               => (bool) $success,
            'deleted_logs'          => 0,
            'deleted_countries'     => 0,
            'deleted_referrers_log' => 0,
            'deleted_referrers'     => 0,
        ];
    }

    /**
     * Check and update analytics database.
     * - If tables don't exist: Create them (new installation)
     * - If tables exist: Only update when plugin version changes AND database version differs
     */
    public static function mf_analytics_update_db() {
        global $wpdb;

        $logs_table   = $wpdb->prefix . self::$LOGS_TABLE;
        $table_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $logs_table ) )
        ) === $logs_table;

        if ( ! $table_exists ) {
            // Fresh install — no existing tables, nothing to migrate
            self::mfAnalyticCreateDb( false );
        } elseif ( get_option( self::$DATABASE_KEY ) !== self::$DATABASE_VERSION ) {
            // Existing install, schema changed — run migrations then sync tables
            self::mfAnalyticCreateDb( true );
        }
    }

    /**
     * Single source of truth for all table schemas.
     * Returns an array keyed by table name (without prefix). Each entry has:
     * When you add or remove a column, edit ONLY this method then bump DATABASE_VERSION.
     */
    private static function get_schema() {
        return [
            self::$LOGS_TABLE => [
                'columns' => [
                    'id'               => 'BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT',
                    'date'             => 'DATE NOT NULL',
                    'form_id'          => 'BIGINT(20) UNSIGNED NOT NULL',
                    'views'            => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'conversions'      => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'session_duration' => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'mobile'           => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'tablet'           => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'desktop'          => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'mozilla_firefox'  => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'chrome'           => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'internet_explore' => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'safari'           => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'opera_mini'       => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'microsoft_edge'   => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'others_browsers'  => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'organic'          => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'direct'           => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'referral'         => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                ],
                'keys' => [
                    'PRIMARY KEY  (id)',
                    'KEY date_form (date, form_id)',
                ],
            ],
            self::$COUNTRIES_TABLE => [
                'columns' => [
                    'id'           => 'BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT',
                    'log_id'       => 'BIGINT(20) UNSIGNED NOT NULL',
                    'country_code' => 'VARCHAR(10) NOT NULL',
                    'views'        => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                    'conversions'  => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                ],
                'keys' => [
                    'PRIMARY KEY  (id)',
                    'KEY log_country (log_id, country_code)',
                ],
            ],
            self::$REFERRERS_TABLE => [
                'columns' => [
                    'id'            => 'INT UNSIGNED NOT NULL AUTO_INCREMENT',
                    'referrer_name' => 'TEXT NOT NULL',
                ],
                'keys' => [
                    'PRIMARY KEY  (id)',
                ],
            ],
            self::$REFERRERS_LOG_TABLE => [
                'columns' => [
                    'id'          => 'BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT',
                    'referrer_id' => 'INT UNSIGNED NOT NULL',
                    'log_id'      => 'BIGINT(20) UNSIGNED NOT NULL',
                    'conversions' => 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
                ],
                'keys' => [
                    'PRIMARY KEY  (id)',
                    'KEY log_referrer (log_id, referrer_id)',
                ],
            ],
        ];
    }

    /**
     * Whether a string is a safe SQL identifier (column/table name) for interpolation.
     *
     * @param string $identifier
     * @return bool
     */
    private static function is_safe_identifier( $identifier ) {
        return is_string( $identifier ) && (bool) preg_match( '/^[A-Za-z0-9_]+$/', $identifier );
    }

    /**
     * Run version-specific database migrations.
     *
     * Uses get_schema() as the single source of truth to:
     *   - ADD COLUMN  for columns present in the schema but missing from the DB
     *   - DROP COLUMN for columns present in the DB but removed from the schema
     */
    private static function run_migrations() {
        global $wpdb;

        // Columns that must never be dropped regardless of schema differences
        $protected_columns = ['id', 'log_id', 'referrer_id', 'form_id'];

        foreach ( self::get_schema() as $table_name => $table_def ) {
            $full_table_name  = $wpdb->prefix . $table_name;
            $schema_columns   = $table_def['columns'];
            $required_columns = array_keys( $schema_columns );

            // Skip tables that haven't been created yet
            if ( $wpdb->get_var(
                $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $full_table_name ) )
            ) !== $full_table_name ) {
                continue;
            }

            $existing_columns = $wpdb->get_col( "SHOW COLUMNS FROM {$full_table_name}" );

            // ADD missing columns. Column names come from get_schema() keys (trusted),
            // but guard the identifier format before interpolating it into DDL.
            foreach ( array_diff( $required_columns, $existing_columns ) as $column ) {
                if ( ! self::is_safe_identifier( $column ) ) {
                    continue;
                }
                $wpdb->query( "ALTER TABLE {$full_table_name} ADD COLUMN `{$column}` {$schema_columns[$column]}" );
            }

            // DROP extra columns. Column names come from the live DB schema; guard them too.
            foreach ( array_diff( $existing_columns, $required_columns ) as $column ) {
                if ( in_array( $column, $protected_columns ) ) {
                    continue; // Never remove key/relational columns
                }
                if ( ! self::is_safe_identifier( $column ) ) {
                    continue;
                }
                $wpdb->query( "ALTER TABLE {$full_table_name} DROP COLUMN `{$column}`" );
            }
        }
    }

    public static function mfAnalyticCreateDb( $migrate = true ) {
        global $wpdb;

        $logs_table          = $wpdb->prefix . self::$LOGS_TABLE;
        $countries_table     = $wpdb->prefix . self::$COUNTRIES_TABLE;
        $referrers_table     = $wpdb->prefix . self::$REFERRERS_TABLE;
        $referrers_log_table = $wpdb->prefix . self::$REFERRERS_LOG_TABLE;
        $charset_collate     = $wpdb->get_charset_collate();

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

        if ( $migrate ) {
            // Existing install: ALTER tables to add/remove columns before dbDelta.
            // run_migrations() skips tables that don't exist yet, so
            // any brand-new tables are safely handled by dbDelta() below.
            self::run_migrations();
        }

        // Build and run CREATE TABLE for each table from the single schema source
        foreach ( self::get_schema() as $table_name => $table_def ) {
            $full_table_name = $wpdb->prefix . $table_name;

            $column_defs = [];
            foreach ( $table_def['columns'] as $col_name => $col_def ) {
                $column_defs[] = "`{$col_name}` {$col_def}";
            }
            foreach ( $table_def['keys'] as $key_def ) {
                $column_defs[] = $key_def;
            }

            $sql = "CREATE TABLE {$full_table_name} (\n    "
                 . implode( ",\n    ", $column_defs )
                 . "\n) {$charset_collate};";

            dbDelta( $sql );
        }

        // Add FOREIGN KEY constraints (compatible with all MySQL/MariaDB versions)
        self::add_foreign_key( $countries_table,     'fk_countries_log', 'log_id',      $logs_table,      'id' );
        self::add_foreign_key( $referrers_log_table, 'fk_reflog_log',    'log_id',      $logs_table,      'id' );
        self::add_foreign_key( $referrers_log_table, 'fk_reflog_ref',    'referrer_id', $referrers_table, 'id' );

        // Save the database version
        update_option( self::$DATABASE_KEY, self::$DATABASE_VERSION );
    }

    /**
     * Add a FOREIGN KEY constraint only if it doesn't already exist.
     * Uses information_schema for compatibility with all MySQL/MariaDB versions
     * (avoids ADD CONSTRAINT IF NOT EXISTS which requires MySQL 8.0.19+).
     */
    private static function add_foreign_key( $table, $constraint_name, $column, $ref_table, $ref_column ) {
        global $wpdb;

        $exists = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
             WHERE CONSTRAINT_SCHEMA = DATABASE()
               AND TABLE_NAME        = %s
               AND CONSTRAINT_NAME   = %s
               AND CONSTRAINT_TYPE   = 'FOREIGN KEY'",
            $table,
            $constraint_name
        ) );

        if ( ! $exists ) {
            $wpdb->query(
                "ALTER TABLE {$table}
                 ADD CONSTRAINT `{$constraint_name}`
                 FOREIGN KEY (`{$column}`) REFERENCES {$ref_table}(`{$ref_column}`) ON DELETE CASCADE"
            );
        }
    }
    
    /**
     * Track form view - increments views count
     * @param int $form_id Form ID
     * @param array $data Tracking data (device, browser, source, country, referrer, session_duration)
     * @return int Log ID
     */
    public static function log_view($form_id, $data) {
        global $wpdb;
        
        $logs_table = $wpdb->prefix . self::$LOGS_TABLE;
        $date = current_time('Y-m-d');
        $session_duration = intval($data['session_duration'] ?? 0);
        
        // true  → duration update (incremental): only update session_duration
        // false → initial view: increment views + device/browser/source counters
        $is_update = !empty($data['is_update']);
        
        // Find or create today's log
        $log_id = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT id FROM {$logs_table} WHERE date = %s AND form_id = %d LIMIT 1",
                $date,
                $form_id
            )
        );
        
        // Map device, browser, source to column names
        $device_column = self::get_device_column($data['device']);
        $browser_column = self::get_browser_column($data['browser']);
        $source_column = self::get_source_column($data['source']);
        
        if ($log_id) {
            // Update existing log
            if (!$is_update) {
                // Initial view: increment view counters
                $wpdb->query(
                    $wpdb->prepare(
                        "UPDATE {$logs_table}
                        SET views = views + 1,
                            session_duration = session_duration + %d,
                            {$device_column} = {$device_column} + 1,
                            {$browser_column} = {$browser_column} + 1,
                            {$source_column} = {$source_column} + 1
                        WHERE id = %d",
                        $session_duration,
                        $log_id
                    )
                );
            } else {
                // Duration update: only update session_duration, don't increment views
                $wpdb->query(
                    $wpdb->prepare(
                        "UPDATE {$logs_table}
                        SET session_duration = session_duration + %d
                        WHERE id = %d",
                        $session_duration,
                        $log_id
                    )
                );
            }
        } else {
            // Create new log (always counts as initial view)
            $wpdb->insert(
                $logs_table,
                [
                    'date' => $date,
                    'form_id' => $form_id,
                    'views' => 1,
                    'session_duration' => $session_duration,
                    $device_column => 1,
                    $browser_column => 1,
                    $source_column => 1,
                ],
                ['%s', '%d', '%d', '%d', '%d', '%d', '%d']
            );
            $log_id = $wpdb->insert_id;
        }
        
        // Only update country stats on initial view (not on incremental update).
        // Referrers are intentionally NOT recorded at view time: referrer stats are
        // conversion-based (see log_referrer_conversion), and creating a referrer row
        // here only produced orphaned entries that cleanup later removed.
        if (!$is_update) {
            // Update countries table (track views by country)
            if (!empty($data['country'])) {
                self::log_country_view($log_id, $data['country']);
            }
        }

        return $log_id;
    }

    /**
     * Track form conversion - increments conversions count only
     * Called from metform_after_store_form_data hook
     * @param int $form_id Form ID
     * @param array $data Tracking data (country, referrer)
     * @return void
     */
    public static function log_conversion($form_id, $data) {
        global $wpdb;
        
        $logs_table = $wpdb->prefix . self::$LOGS_TABLE;
        $date = current_time('Y-m-d');
        
        // Find or create today's log
        $log_id = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT id FROM {$logs_table} WHERE date = %s AND form_id = %d LIMIT 1",
                $date,
                $form_id
            )
        );
        
        if ($log_id) {
            // Update existing log - increment both views and conversions on every call
            $wpdb->query(
                $wpdb->prepare(
                    "UPDATE {$logs_table}
                    SET views = views + 1, conversions = conversions + 1
                    WHERE id = %d",
                    $log_id
                )
            );
        } else {
            // Create new log with 1 conversion and 1 view
            $wpdb->insert(
                $logs_table,
                [
                    'date' => $date,
                    'form_id' => $form_id,
                    'views' => 1,
                    'conversions' => 1,
                ],
                ['%s', '%d', '%d', '%d']
            );
            $log_id = $wpdb->insert_id;
        }
        
        // Update countries table (increment conversion count by country)
        if (!empty($data['country'])) {
            self::log_country_conversion($log_id, $data['country']);
        }
        
        // Update referrers log table (increment conversion count by referrer)
        if (!empty($data['referrer'])) {
            self::log_referrer_conversion($log_id, $data['referrer']);
        }
    }

    /**
     * Log country view
     */
    private static function log_country_view($log_id, $country) {
        self::update_country_stat($log_id, $country, 'views');
    }

    /**
     * Log country conversion
     */
    private static function log_country_conversion($log_id, $country) {
        self::update_country_stat($log_id, $country, 'conversions');
    }

    /**
     * Generic method to update country statistics
     * 
     * @param int $log_id Log ID
     * @param string $country Country code
     * @param string $stat_type 'views' or 'conversions'
     */
    private static function update_country_stat($log_id, $country, $stat_type = 'views') {
        global $wpdb;
        $countries_table = $wpdb->prefix . self::$COUNTRIES_TABLE;

        // $stat_type is interpolated as a column name — restrict to a known allowlist.
        if ( ! in_array( $stat_type, [ 'views', 'conversions' ], true ) ) {
            return;
        }

        $existing = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT id FROM {$countries_table} WHERE log_id = %d AND country_code = %s",
                $log_id,
                $country
            )
        );
        
        if ($existing) {
            $wpdb->query(
                $wpdb->prepare(
                    "UPDATE {$countries_table} SET {$stat_type} = {$stat_type} + 1 WHERE id = %d",
                    $existing
                )
            );
        } else {
            $wpdb->insert(
                $countries_table,
                [
                    'log_id' => $log_id,
                    'country_code' => $country,
                    $stat_type => 1,
                ],
                ['%d', '%s', '%d']
            );
        }
    }

    /**
     * Log referrer conversion
     */
    private static function log_referrer_conversion($log_id, $referrer) {
        global $wpdb;
        $referrers_log_table = $wpdb->prefix . self::$REFERRERS_LOG_TABLE;
        
        // Get or create referrer ID
        $referrer_id = self::get_or_create_referrer_id($referrer);
        
        // Update referrers_log
        $existing = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT id FROM {$referrers_log_table} WHERE log_id = %d AND referrer_id = %d",
                $log_id,
                $referrer_id
            )
        );
        
        if ($existing) {
            $wpdb->query(
                $wpdb->prepare(
                    "UPDATE {$referrers_log_table} SET conversions = conversions + 1 WHERE id = %d",
                    $existing
                )
            );
        } else {
            $wpdb->insert(
                $referrers_log_table,
                [
                    'log_id' => $log_id,
                    'referrer_id' => $referrer_id,
                    'conversions' => 1,
                ],
                ['%d', '%d', '%d']
            );
        }
    }

    /**
     * Get or create referrer ID
     * 
     * @param string $referrer Referrer URL
     * @return int Referrer ID
     */
    private static function get_or_create_referrer_id($referrer) {
        global $wpdb;
        $referrers_table = $wpdb->prefix . self::$REFERRERS_TABLE;
        
        // Find existing referrer
        $referrer_id = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT id FROM {$referrers_table} WHERE referrer_name = %s",
                $referrer
            )
        );
        
        // Create if not exists
        if (!$referrer_id) {
            $wpdb->insert(
                $referrers_table,
                ['referrer_name' => $referrer],
                ['%s']
            );
            $referrer_id = $wpdb->insert_id;
        }
        
        return (int) $referrer_id;
    }

    // Helper methods to map values to column names
    private static function get_device_column($device) {
        $device = strtolower($device);
        if ($device === 'mobile') return 'mobile';
        if ($device === 'tablet') return 'tablet';
        return 'desktop';
    }

    private static function get_browser_column($browser) {
        $browser = strtolower($browser);
        if ($browser === 'firefox') return 'mozilla_firefox';
        if ($browser === 'chrome') return 'chrome';
        if ($browser === 'ie') return 'internet_explore';
        if ($browser === 'safari') return 'safari';
        if ($browser === 'opera') return 'opera_mini';
        if ($browser === 'edge') return 'microsoft_edge';
        return 'others_browsers';
    }

    private static function get_source_column($source) {
        $source = strtolower($source);
        if ($source === 'organic') return 'organic';
        if ($source === 'referral') return 'referral';
        return 'direct';
    }

    /**
     * Get analytics logs data with filtering
     * @param int $form_id Form ID (0 for all forms)
     * @param string $start_date Start date (Y-m-d format)
     * @param string $end_date End date (Y-m-d format)
     * @param string $type Data type: conversion, devices, browsers, countries, referrers, sources
     * @param string $form_type 'general', 'quiz', or 'all' (both combined)
     * @return array Analytics data
     */
    public static function get_logs($form_id, $start_date, $end_date, $type = 'conversion', $form_type = 'general') {
        global $wpdb;
        $logs_table = $wpdb->prefix . self::$LOGS_TABLE;
        $countries_table = $wpdb->prefix . self::$COUNTRIES_TABLE;
        $referrers_table = $wpdb->prefix . self::$REFERRERS_TABLE;
        $referrers_log_table = $wpdb->prefix . self::$REFERRERS_LOG_TABLE;

        $form_type = self::normalize_form_type( $form_type );
        $allowed_form_ids = self::get_filtered_form_ids( $form_type, (int) $form_id );
        if ( empty( $allowed_form_ids ) ) {
            return self::get_empty_logs_response_by_type( $type );
        }
        
        // Build WHERE clause — date filter is optional
        $where  = 'WHERE form_id IN (' . implode( ',', array_fill( 0, count( $allowed_form_ids ), '%d' ) ) . ')';
        $params = [];

        if (!empty($start_date) && !empty($end_date)) {
            $where   .= " AND date BETWEEN %s AND %s";
            $params[] = $start_date;
            $params[] = $end_date;
        }

        $params = array_merge( $allowed_form_ids, $params );
        
        switch ($type) {
            case 'conversion':
                $sql = "SELECT date, SUM(views) as views, SUM(conversions) as conversions, SUM(session_duration) as session_duration 
                        FROM {$logs_table} {$where} GROUP BY date ORDER BY date ASC";
                $results = $wpdb->get_results($wpdb->prepare($sql, $params), ARRAY_A);
                
                $total_views = array_sum(array_column($results, 'views'));
                $total_conversions = array_sum(array_column($results, 'conversions'));
                $total_session_duration = array_sum(array_column($results, 'session_duration'));
                $conversion_rate = $total_views > 0 ? round(($total_conversions / $total_views) * 100, 2) : 0;
                $abandonment_rate = max(0, round(100 - $conversion_rate, 2));
                $average_session = $total_views > 0 ? round($total_session_duration / $total_views, 2) : 0;
                
                return [
                    'group' => $results,
                    'total' => [
                        'views' => (int) $total_views,
                        'conversions' => (int) $total_conversions,
                        'conversion_rate' => $conversion_rate,
                        'abandonment_rate' => $abandonment_rate,
                        'average_session' => $average_session
                    ]
                ];
                
            case 'devices':
                $sql = "SELECT SUM(desktop) as desktop, SUM(tablet) as tablet, SUM(mobile) as mobile 
                        FROM {$logs_table} {$where}";
                $result = $wpdb->get_row($wpdb->prepare($sql, $params), ARRAY_A);
                return $result;
                
            case 'browsers':
                $sql = "SELECT 
                        SUM(chrome) as Chrome,
                        SUM(mozilla_firefox) as Firefox,
                        SUM(safari) as Safari,
                        SUM(microsoft_edge) as Edge,
                        SUM(opera_mini) as Opera,
                        SUM(internet_explore) as IE,
                        SUM(others_browsers) as Others
                        FROM {$logs_table} {$where}";
                $result = $wpdb->get_row($wpdb->prepare($sql, $params), ARRAY_A);
                
                // Convert to array format
                $browsers = [];
                foreach ($result as $browser => $count) {
                    if ($count > 0) {
                        $browsers[] = ['browser_name' => $browser, 'count' => (int) $count];
                    }
                }
                return $browsers;
                
            case 'countries':
                $sql = "SELECT c.country_code as country_name, SUM(c.views) as views, SUM(c.conversions) as conversions
                        FROM {$countries_table} c
                        INNER JOIN {$logs_table} l ON c.log_id = l.id
                        {$where}
                        GROUP BY c.country_code
                        ORDER BY views DESC
                        LIMIT 20";
                $results = $wpdb->get_results($wpdb->prepare($sql, $params), ARRAY_A);

                foreach ($results as &$row) {
                    $views       = (int) $row['views'];
                    $conversions = (int) $row['conversions'];
                    $row['conversion_rate'] = $views > 0 ? round(($conversions / $views) * 100, 2) : 0;
                }
                unset($row);

                return $results;
                
            case 'referrers':
                $sql = "SELECT r.referrer_name, COUNT(*) as count
                        FROM {$referrers_table} r
                        INNER JOIN {$referrers_log_table} rl ON r.id = rl.referrer_id
                        INNER JOIN {$logs_table} l ON rl.log_id = l.id
                        {$where}
                        GROUP BY r.referrer_name
                        ORDER BY count DESC
                        LIMIT 20";
                return $wpdb->get_results($wpdb->prepare($sql, $params), ARRAY_A);
                
            case 'sources':
                $sql = "SELECT SUM(organic) as organic, SUM(direct) as direct, SUM(referral) as referral 
                        FROM {$logs_table} {$where}";
                $result = $wpdb->get_row($wpdb->prepare($sql, $params), ARRAY_A);
                return $result;
                
            default:
                return [];
        }
    }

    /**
     * Get date range for analytics data
     * @param int $form_id Form ID (0 for all forms)
     * @return array First and last log dates
     */
    public static function get_date_range($form_id = 0) {
        global $wpdb;
        $logs_table = $wpdb->prefix . self::$LOGS_TABLE;
        $form_id    = (int) $form_id;

        if ( $form_id > 0 ) {
            return $wpdb->get_row(
                $wpdb->prepare(
                    "SELECT MIN(date) as first_log_date, MAX(date) as last_log_date
                     FROM {$logs_table} WHERE form_id = %d",
                    $form_id
                ),
                ARRAY_A
            );
        }

        return $wpdb->get_row(
            "SELECT MIN(date) as first_log_date, MAX(date) as last_log_date FROM {$logs_table}",
            ARRAY_A
        );
    }

    /**
     * Delete analytics log rows within a date range (optionally for one form).
     *
     * Keeps raw SQL out of the REST layer (called by Api::delete_logs()).
     * Child rows in countries/referrers_log are removed via ON DELETE CASCADE.
     *
     * @param int    $form_id    Form ID (0 = all forms).
     * @param string $start_date Start date (Y-m-d).
     * @param string $end_date   End date (Y-m-d).
     * @return int|false Number of rows deleted, or false on failure.
     */
    public static function delete_logs_range( $form_id, $start_date, $end_date ) {
        global $wpdb;

        $logs_table = $wpdb->prefix . self::$LOGS_TABLE;
        $form_id    = (int) $form_id;

        if ( $form_id > 0 ) {
            return $wpdb->query(
                $wpdb->prepare(
                    "DELETE FROM {$logs_table} WHERE date BETWEEN %s AND %s AND form_id = %d",
                    $start_date,
                    $end_date,
                    $form_id
                )
            );
        }

        return $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$logs_table} WHERE date BETWEEN %s AND %s",
                $start_date,
                $end_date
            )
        );
    }

    /**
     * Permanently delete all analytics data for a given form.
     *
     * Deleting from the logs table is sufficient because the foreign keys on
     * mf_analytics_countries and mf_analytics_reffrearrs_log are defined with
     * ON DELETE CASCADE, so child rows are removed automatically.
     * After that, any referrer entries that are no longer referenced by any
     * log are also removed to avoid orphaned rows.
     *
     * @param int $form_id The ID of the form whose analytics data should be removed.
     * @return int|false Number of log rows deleted, or false on failure.
     */
    public static function delete_form_data( $form_id ) {
        global $wpdb;

        $form_id = (int) $form_id;
        if ( $form_id <= 0 ) {
            return false;
        }

        $logs_table          = $wpdb->prefix . self::$LOGS_TABLE;
        $referrers_table     = $wpdb->prefix . self::$REFERRERS_TABLE;
        $referrers_log_table = $wpdb->prefix . self::$REFERRERS_LOG_TABLE;

        // Delete all log rows for this form.
        // ON DELETE CASCADE automatically removes related rows from:
        //   - mf_analytics_countries      (via fk_countries_log)
        //   - mf_analytics_reffrearrs_log (via fk_reflog_log)
        $deleted = $wpdb->delete(
            $logs_table,
            [ 'form_id' => $form_id ],
            [ '%d' ]
        );

        // Clean up orphaned referrers — entries in the referrers table that
        // are no longer linked to any row in the referrers_log table.
        $wpdb->query(
            "DELETE r FROM {$referrers_table} r
             LEFT JOIN {$referrers_log_table} rl ON r.id = rl.referrer_id
             WHERE rl.id IS NULL"
        );

        return $deleted;
    }

    /**
     * Delete expired analytics rows based on configured retention policy.
     *
     * Deletes from all analytics tables:
     * - mf_analytics_logs
     * - mf_analytics_countries
     * - mf_analytics_referrers
     * - mf_analytics_reffrearrs_log
     *
     * @param string $range Optional retention range override.
     * @return array<string,int|bool> Cleanup stats.
     */
    public static function cleanup_expired_data( $range = '' ) {
        global $wpdb;

        $settings = get_option( 'metform_option__settings', [] );

        if ( empty( $settings['mf_enable_form_analytics'] ) ) {
            return [];
        }

        $selected_range = ! empty( $range )
            ? sanitize_text_field( $range )
            : ( isset( $settings['mf_analytics_delete_range'] ) ? sanitize_text_field( $settings['mf_analytics_delete_range'] ) : '6_months' );

        if ( $selected_range === 'keep_forever' ) {
            return self::get_cleanup_empty_response( true );
        }

        $logs_table          = $wpdb->prefix . self::$LOGS_TABLE;
        $countries_table     = $wpdb->prefix . self::$COUNTRIES_TABLE;
        $referrers_table     = $wpdb->prefix . self::$REFERRERS_TABLE;
        $referrers_log_table = $wpdb->prefix . self::$REFERRERS_LOG_TABLE;

        $cutoff_date = self::get_retention_cutoff_date( $selected_range );

        if ( ! $cutoff_date ) {
            return self::get_cleanup_empty_response( false );
        }

        // Delete child rows first so cleanup also works if foreign keys are missing.
        $deleted_countries = $wpdb->query(
            $wpdb->prepare(
                "DELETE c FROM {$countries_table} c
                 INNER JOIN {$logs_table} l ON c.log_id = l.id
                 WHERE l.date < %s",
                $cutoff_date
            )
        );

        $deleted_referrers_log = $wpdb->query(
            $wpdb->prepare(
                "DELETE rl FROM {$referrers_log_table} rl
                 INNER JOIN {$logs_table} l ON rl.log_id = l.id
                 WHERE l.date < %s",
                $cutoff_date
            )
        );

        $deleted_logs = $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$logs_table} WHERE date < %s",
                $cutoff_date
            )
        );

        $deleted_referrers = $wpdb->query(
            "DELETE r FROM {$referrers_table} r
             LEFT JOIN {$referrers_log_table} rl ON r.id = rl.referrer_id
             WHERE rl.id IS NULL"
        );

        return [
            'success'        => true,
            'deleted_logs'   => max( 0, (int) $deleted_logs ),
            'deleted_countries' => max( 0, (int) $deleted_countries ),
            'deleted_referrers_log' => max( 0, (int) $deleted_referrers_log ),
            'deleted_referrers' => max( 0, (int) $deleted_referrers ),
        ];
    }

    /**
     * Get all forms with id and title
     *
     * @param int    $form_id Optional specific form ID (0 for all forms)
     *
     * @return array Total forms count and list of form_id + form_title
     */
    public static function get_forms($form_id = 0, $form_type = 'general') {
        $form_type = self::normalize_form_type( $form_type );
        $form_ids = self::get_filtered_form_ids( $form_type, (int) $form_id );

        if ( empty( $form_ids ) ) {
            return [
                'total_forms' => 0,
                'forms'       => [],
            ];
        }

        $forms = get_posts([
            'post_type'   => 'metform-form',
            'post_status' => 'publish',
            'numberposts' => -1,
            'post__in'    => $form_ids,
            'orderby'     => 'date',
            'order'       => 'DESC',
        ]);

        $forms_data = array_map(function($form) {
            return [
                'form_id'    => $form->ID,
                'form_title' => $form->post_title,
            ];
        }, $forms);

        return [
            'total_forms' => count($forms_data),
            'forms'       => $forms_data,
        ];
    }

    /**
     * Normalize requested form type.
     *
     * @param string $form_type 'general', 'quiz', or 'all' (both combined).
     * @return string
     */
    private static function normalize_form_type( $form_type ) {
        if ( $form_type === 'quiz' || $form_type === 'all' ) {
            return $form_type;
        }
        return 'general';
    }

    /**
     * Return form IDs filtered by form type and optional specific form ID.
     *
     * @param string $form_type 'general', 'quiz', or 'all'.
     * @param int    $form_id   Optional specific form ID.
     * @return array<int>
     */
    private static function get_filtered_form_ids( $form_type, $form_id = 0 ) {
        $form_id = (int) $form_id;

        if ( $form_id > 0 ) {
            $form = get_post( $form_id );
            if ( ! $form || $form->post_type !== 'metform-form' || $form->post_status !== 'publish' ) {
                return [];
            }

            return [ $form_id ];
        }

        $all_form_ids = get_posts([
            'post_type'   => 'metform-form',
            'post_status' => 'publish',
            'numberposts' => -1,
            'fields'      => 'ids',
        ]);

        $filtered = [];
        foreach ( $all_form_ids as $fid ) {
            $fid = (int) $fid;
            if ( self::matches_form_type( $fid, $form_type ) ) {
                $filtered[] = $fid;
            }
        }

        return $filtered;
    }

    /**
     * Check whether a form matches requested type.
     *
     * @param int    $form_id
     * @param string $form_type 'general', 'quiz', or 'all' (matches everything).
     * @return bool
     */
    private static function matches_form_type( $form_id, $form_type ) {
        if ( $form_type === 'all' ) {
            return true;
        }

        $is_quiz = self::is_quiz_form( $form_id );
        return $form_type === 'quiz' ? $is_quiz : ! $is_quiz;
    }

    /**
     * Determine if a form is a quiz form.
     *
     * @param int $form_id
     * @return bool
     */
    private static function is_quiz_form( $form_id ) {
        static $quiz_type_cache = [];

        $form_id = (int) $form_id;
        if ( isset( $quiz_type_cache[ $form_id ] ) ) {
            return $quiz_type_cache[ $form_id ];
        }

        $forms_action = \MetForm\Core\Forms\Action::instance();
        if ( ! is_object( $forms_action ) || ! method_exists( $forms_action, 'get_all_data' ) ) {
            $quiz_type_cache[ $form_id ] = false;
            return false;
        }

        $form_settings = $forms_action->get_all_data( $form_id );
        $quiz_type_cache[ $form_id ] = is_array( $form_settings )
            && isset( $form_settings['form_type'] )
            && $form_settings['form_type'] === 'quiz-form';

        return $quiz_type_cache[ $form_id ];
    }

    /**
     * Empty response shape for each log type.
     *
     * @param string $type
     * @return array
     */
    private static function get_empty_logs_response_by_type( $type ) {
        switch ( $type ) {
            case 'conversion':
                return [
                    'group' => [],
                    'total' => [
                        'views' => 0,
                        'conversions' => 0,
                        'conversion_rate' => 0,
                        'average_session' => 0,
                    ],
                ];
            case 'devices':
                return [
                    'desktop' => 0,
                    'tablet'  => 0,
                    'mobile'  => 0,
                ];
            case 'browsers':
            case 'countries':
            case 'referrers':
                return [];
            case 'sources':
                return [
                    'organic'  => 0,
                    'direct'   => 0,
                    'referral' => 0,
                ];
            default:
                return [];
        }
    }

	// phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL
}
