<?php
/**
 * Advertiser Metrics - CSV Generator
 * Generates CSV files for Amazon (20378) and Hearst (13421) advertisers
 * Optimized for billion+ row processing
 * 
 * Usage:
 *   php advertiser_metrics.php                              # Full generation (all data, last 6 months, NO drest)
 *   php advertiser_metrics.php --limit=5000                 # Test run (5000 records)
 *   php advertiser_metrics.php --months=3                   # Last 3 months
 *   php advertiser_metrics.php --months=12                  # Last 12 months
 *   php advertiser_metrics.php --drest                      # Enable drest syncing
 *   php advertiser_metrics.php --advertiser=20378           # Only Amazon
 *   php advertiser_metrics.php --start-date=2026-01-01 --end-date=2026-04-22  # Custom date range
 *   php advertiser_metrics.php --chunk=daily                # Split files by date (YYYY-MM-DD)
 *   php advertiser_metrics.php --chunk=weekly               # Split files by week (YYYY-W##)
 *   php advertiser_metrics.php --limit=1000 --months=2 --chunk=daily  # Combined: test, 2 months, daily chunks
 */

// Performance optimization for large datasets
ini_set('memory_limit', '2048M');
ini_set('max_execution_time', 0); // No timeout for long-running scripts
gc_enable(); // Enable garbage collection
gc_collect_cycles(); // Initial collection

// Database connection configuration
//require_once '/var/www/php74/koushik/msacommon/lib/php/include/dbConstants.php'; //dev
require_once '/opt/msa/lib/php/include/dbConstants.php'; //prod

class ClicksCSVGenerator {
    private $mysqli;
    private $mysqli_admin; // Connection to Admin database
    private $advertisers = [
        13421 => 'Hearst',
        20378 => 'Amazon'
    ];
    private $output_dir;
    private $log_dir;
    private $log_file;
    private $limit = 0; // 0 = no limit (full run), >0 = test run with specified limit
    private $advertiser_filter = null; // null = all advertisers, or specific advertiser_id
    private $months = 6; // Number of months to process (default: 6)
    private $start_date = null; // Optional: start date for custom range (format: YYYY-MM-DD)
    private $end_date = null; // Optional: end date for custom range (format: YYYY-MM-DD)
    private $drest_enabled = false; // Whether to sync tables via drest (default: false)
    private $chunk_mode = 'daily'; // 'none', 'daily', or 'weekly' - controls CSV output chunking
    private $domains_cache = []; // Cache for domains per month
    private $campaign_cache = []; // Cache for campaign metadata (name, type, mode)
    private $creative_cache = []; // Cache for creative metadata (name, type, label, landing_page_url)
    private $campaign_type_cache = []; // Cache for campaign_type lookup table (static, loaded once)
    private $creatives_types_cache = []; // Cache for creatives_types lookup table (static, loaded once)
    private $start_time = null; // Track execution start time for performance metrics
    
    // Synthetic data for demographics and device types
    private $ageRanges = ['18-24', '25-34', '35-44', '45-54', '55-64', '65+'];
    private $genders = ['Male', 'Female', 'Others'];
    private $deviceTypes = ['Desktop', 'Mobile', 'Tablet'];
    
    public function __construct($argv = []) {
        // Parse command-line arguments
        $this->parseArguments($argv);
        
        // Set dynamic paths based on script location
        $base_dir = __DIR__;
        $this->output_dir = $base_dir . '/data/';       //data dir
        $this->log_dir = $this->output_dir . 'logs/';  //logs dir
        
        // Create output directory if it doesn't exist
        if (!is_dir($this->output_dir)) {
            mkdir($this->output_dir, 0755, true);
        }
        
        // Create logs directory if it doesn't exist
        if (!is_dir($this->log_dir)) {
            mkdir($this->log_dir, 0755, true);
        }
        
        // Initialize log file with date
        $date = date('Ymd');
        $this->log_file = $this->log_dir . 'clicks_csv_generator_' . $date . '.log';
        
        // Connect to keywords database (for clicks data)
        $this->mysqli = new mysqli(
            MSACOMMON_DB_HOST_KEYWORDS,
            MSACOMMON_DB_USER_KEYWORDS,
            MSACOMMON_DB_PASS_KEYWORDS,
            'keywords'
        );
        
        if ($this->mysqli->connect_error) {
            $error_msg = 'Keywords database connection failed: ' . $this->mysqli->connect_error;
            $this->log($error_msg, 'ERROR');
            die($error_msg);
        }
        
        $this->mysqli->set_charset('utf8');
        $this->log('Keywords database connection successful', 'INFO');
        
        // Connect to Admin database (for campaign metadata)
        $this->mysqli_admin = new mysqli(
            MSACOMMON_DB_HOST_ADMIN,
            MSACOMMON_DB_USER_ADMIN,
            MSACOMMON_DB_PASS_ADMIN,
            'Admin'
        );
        
        if ($this->mysqli_admin->connect_error) {
            $error_msg = 'Admin database connection failed: ' . $this->mysqli_admin->connect_error;
            $this->log($error_msg, 'ERROR');
            die($error_msg);
        }
        
        $this->mysqli_admin->set_charset('utf8');
        $this->log('Admin database connection successful', 'INFO');
        
        // Load campaign_type lookup table once at runtime
        $this->loadCampaignTypeLookup();
        
        // Load creatives_types lookup table once at runtime
        $this->loadCreativesTypeLookup();
    }
    
    /**
     * Parse command-line arguments
     */
    private function parseArguments($argv) {
        for ($i = 1; $i < count($argv); $i++) {
            $arg = $argv[$i];
            if (strpos($arg, '--limit=') === 0) {
                $this->limit = (int) substr($arg, 8);
            } elseif (strpos($arg, '--advertiser=') === 0) {
                $this->advertiser_filter = (int) substr($arg, 13);
            } elseif (strpos($arg, '--months=') === 0) {
                $this->months = (int) substr($arg, 9);
                if ($this->months < 1) {
                    $this->months = 1;
                }
            } elseif (strpos($arg, '--start-date=') === 0) {
                $this->start_date = substr($arg, 13);
                // Validate date format
                if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $this->start_date)) {
                    $this->start_date = null;
                }
            } elseif (strpos($arg, '--end-date=') === 0) {
                $this->end_date = substr($arg, 11);
                // Validate date format
                if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $this->end_date)) {
                    $this->end_date = null;
                }
            } elseif (strpos($arg, '--chunk=') === 0) {
                $chunk_value = strtolower(substr($arg, 8));
                if (in_array($chunk_value, ['none', 'daily', 'weekly'])) {
                    $this->chunk_mode = $chunk_value;
                }
            } elseif ($arg === '--drest') {
                $this->drest_enabled = true;
            }
        }
    }
    
    /**
     * Get chunk key for a given date based on chunk_mode
     * Returns: 'YYYY-MM-DD' for daily, 'YYYY-W##' for weekly, or 'full' for none
     */
    private function getChunkKey($date_str) {
        if ($this->chunk_mode === 'daily') {
            // Return date as-is (format: YYYY-MM-DD)
            return $date_str;
        } elseif ($this->chunk_mode === 'weekly') {
            // Return ISO week format (YYYY-W##)
            try {
                $date = new DateTime($date_str);
                return $date->format('Y-\\WW');
            } catch (Exception $e) {
                return 'unknown';
            }
        }
        return 'full';
    }
    
    /**
     * Get chunk suffix for filename
     * Returns: '_2026-04-22' for daily, '_2026-W17' for weekly, or '' for none
     */
    private function getChunkSuffix($chunk_key) {
        if ($chunk_key !== 'full') {
            return '_' . $chunk_key;
        }
        return '';
    }
    
    /**
     * Write message to log file and console
     */
    private function log($message, $level = 'INFO') {
        $timestamp = date('Y-m-d H:i:s');
        $log_message = "[{$timestamp}] [{$level}] {$message}";
        
        // Write to console
        echo $log_message . "\n";
        
        // Write to log file
        file_put_contents($this->log_file, $log_message . "\n", FILE_APPEND);
    }
    
    /**
     * Log section separator
     */
    private function logSection($title) {
        $separator = str_repeat('=', 60);
        $this->log($separator, 'INFO');
        $this->log($title, 'INFO');
        $this->log($separator, 'INFO');
    }
    
    /**
     * Get all month/year combinations for the last N months
     */
    /**
     * Get months to process based on either date range (if provided) or months parameter
     */
    private function getMonthsToProcess() {
        $months = [];
        
        // If custom date range is provided, use it
        if ($this->start_date && $this->end_date) {
            try {
                $start = new DateTime($this->start_date);
                $end = new DateTime($this->end_date);
                
                // Validate that start_date <= end_date
                if ($start > $end) {
                    $this->log('Warning: start_date > end_date, using default months instead', 'WARNING');
                    return $this->getLastSixMonths();
                }
                
                // Generate months between start and end dates
                $current = clone $start;
                while ($current <= $end) {
                    $months[] = $current->format('Ym');
                    $current->modify('+1 month');
                }
                
                // Remove duplicates and sort
                $months = array_unique($months);
                sort($months);
                
                return $months;
            } catch (Exception $e) {
                $this->log('Warning: Invalid date range, using default months instead', 'WARNING');
                return $this->getLastSixMonths();
            }
        }
        
        // Otherwise use months parameter (default: last 6 months)
        return $this->getLastSixMonths();
    }
    
    /**
     * Get last N months (used when no custom date range provided)
     */
    private function getLastSixMonths() {
        $months = [];
        $current_date = new DateTime('2026-04-21'); // Current date in the system
        
        for ($i = 0; $i < $this->months; $i++) {
            $current_date->modify('-1 month');
            $months[] = $current_date->format('Ym');
        }
        
        return array_reverse($months); // Return in chronological order
    }
    
    /**
     * Get date-based WHERE clause for query optimization based on chunk_mode
     * Returns WHERE clause string (without leading 'WHERE')
     */
    private function getDateWhereClause($specific_date = null) {
        // If no date range specified, return empty (no date filtering)
        if (!$this->start_date || !$this->end_date) {
            return '';
        }
        
        // For daily chunking: filter to specific date
        if ($this->chunk_mode === 'daily' && $specific_date) {
            return " AND date = '{$specific_date}'";
        }
        
        // For weekly chunking: filter to week range (specific_date is ISO week like 2026-W16)
        if ($this->chunk_mode === 'weekly' && $specific_date) {
            // Calculate week start and end dates from ISO week
            preg_match('/(\d{4})-W(\d{2})/', $specific_date, $matches);
            if ($matches) {
                $year = (int)$matches[1];
                $week = (int)$matches[2];
                $jan4 = new DateTime("{$year}-01-04");
                $jan4->setISODate($year, $week);
                $week_start = clone $jan4;
                $week_start->modify('Monday this week');
                $week_end = clone $week_start;
                $week_end->modify('+6 days');
                return " AND date >= '{$week_start->format('Y-m-d')}' AND date <= '{$week_end->format('Y-m-d')}'";
            }
        }
        
        // For monthly chunking: no additional date filtering (month table already partitioned)
        if ($this->chunk_mode === 'monthly') {
            return '';
        }
        
        // For none chunking: filter to entire date range
        if ($this->chunk_mode === 'none') {
            return " AND date >= '{$this->start_date}' AND date <= '{$this->end_date}'";
        }
        
        return '';
    }
    
    /**
     * Get all dates to iterate through for processing based on chunk_mode
     * Returns array of dates/weeks/null depending on chunk_mode
     */
    private function getDatesToProcess($month_str) {
        // If no date range specified, return null (process entire month)
        if (!$this->start_date || !$this->end_date) {
            return null; // Process entire month
        }
        
        try {
            $start = new DateTime($this->start_date);
            $end = new DateTime($this->end_date);
            $month_start = new DateTime($month_str . '01');
            $month_end = clone $month_start;
            $month_end->modify('last day of this month');
            
            if ($this->chunk_mode === 'daily') {
                // Return all dates within range that fall in this month
                $dates = [];
                $current = clone $start;
                if ($current < $month_start) $current = clone $month_start;
                
                while ($current <= $end && $current <= $month_end) {
                    $dates[] = $current->format('Y-m-d');
                    $current->modify('+1 day');
                }
                return !empty($dates) ? $dates : null;
            }
            
            elseif ($this->chunk_mode === 'weekly') {
                // Return all ISO weeks that overlap with range and this month
                $weeks = [];
                $current = clone $start;
                if ($current < $month_start) $current = clone $month_start;
                
                $seen_weeks = [];
                while ($current <= $end && $current <= $month_end) {
                    $iso_week = $current->format('Y-\\WW');
                    if (!isset($seen_weeks[$iso_week])) {
                        $weeks[] = $iso_week;
                        $seen_weeks[$iso_week] = true;
                    }
                    $current->modify('+1 day');
                }
                return !empty($weeks) ? $weeks : null;
            }
            
            elseif ($this->chunk_mode === 'monthly' || $this->chunk_mode === 'none') {
                // Check if date range overlaps with this month
                if ($start <= $month_end && $end >= $month_start) {
                    return null; // Process entire month (null means no date-based iteration)
                }
                return [];  // Skip this month (no overlap)
            }
        } catch (Exception $e) {
            $this->log('Error calculating dates to process: ' . $e->getMessage(), 'WARNING');
            return null; // Fall back to processing entire month
        }
        
        return null;
    }
    
    /**
     * Sync table data from remote source using drest (only if table doesn't exist)
     */
    private function syncTableFromRemoteIfMissing($table_name) {
        // Check if table already exists
        if ($this->tableExists($table_name)) {
            $this->log("  Table {$table_name} exists, skipping drest sync", 'INFO');
            return true;
        }
        
        // Table doesn't exist, attempt to sync it
        $this->log("  Syncing {$table_name} from remote...", 'INFO');
        
        $command = "drest keywords {$table_name} 2>&1";
        $output = shell_exec($command);
        
        if ($output === null) {
            $this->log("  ⚠ Warning: drest command failed for {$table_name}", 'WARNING');
            return false;
        }
        
        // Check if sync was successful by looking for error indicators
        if (stripos($output, 'error') !== false || stripos($output, 'failed') !== false) {
            $this->log("  ⚠ drest warning: {$output}", 'WARNING');
            return false;
        }
        
        // Verify table now exists after sync
        if (!$this->tableExists($table_name)) {
            $this->log("  ⚠ drest sync completed but table {$table_name} still not found", 'WARNING');
            return false;
        }
        
        $this->log("  ✓ Synced {$table_name}", 'INFO');
        return true;
    }

    /**
     * Check if a table exists in the database
     */
    private function tableExists($table_name) {
        $result = $this->mysqli->query("SHOW TABLES LIKE '{$table_name}'");
        return $result && $result->num_rows > 0;
    }
    
    /**
     * Load all distinct domains for a specific month
     */
    private function loadDomainsForMonth($advertiser_id, $month) {
        $cache_key = "{$advertiser_id}_{$month}";
        
        if (isset($this->domains_cache[$cache_key])) {
            return $this->domains_cache[$cache_key];
        }
        
        $domains_table = "adv_domains_{$advertiser_id}_{$month}";
        $domains = [];
        
        if ($this->tableExists($domains_table)) {
            $query = "SELECT DISTINCT `domain` FROM {$domains_table}";
            $result = $this->mysqli->query($query);
            
            if ($result && $result->num_rows > 0) {
                while ($row = $result->fetch_assoc()) {
                    $domains[] = $row['domain'];
                }
                $result->free();
                $this->log("  Loaded " . count($domains) . " distinct domains for {$domains_table}", 'INFO');
            }
        }
        
        $this->domains_cache[$cache_key] = $domains;
        return $domains;
    }
    
    /**
     * Get random domain from the list
     */
    private function getRandomDomain($domains) {
        if (empty($domains)) {
            return 'N/A';
        }
        return $domains[array_rand($domains)];
    }
    
    /**
     * Map OS + Browser to Device Type
     */
    private function mapDeviceType($os, $browser) {
        $os = strtolower($os ?? '');
        $browser = strtolower($browser ?? '');
        
        // Mobile OS detection
        if (strpos($os, 'ios') !== false || strpos($os, 'iphone') !== false) {
            return 'Mobile';
        }
        if (strpos($os, 'android') !== false) {
            return 'Mobile';
        }
        if (strpos($os, 'windows mobile') !== false) {
            return 'Mobile';
        }
        
        // Tablet detection
        if (strpos($os, 'ipad') !== false || strpos($os, 'ipados') !== false) {
            return 'Tablet';
        }
        if (strpos($browser, 'tablet') !== false || strpos($browser, 'ipad') !== false) {
            return 'Tablet';
        }
        if (strpos($browser, 'kindle') !== false) {
            return 'Tablet';
        }
        
        // Desktop OS detection (windows, mac, linux, chrome os, ubuntu, etc.)
        if (strpos($os, 'windows') !== false || 
            strpos($os, 'mac') !== false || 
            strpos($os, 'linux') !== false ||
            strpos($os, 'chrome os') !== false ||
            strpos($os, 'ubuntu') !== false) {
            return 'Desktop';
        }
        
        // Browser hints for mobile devices
        if (strpos($browser, 'mobile') !== false || 
            strpos($browser, 'android') !== false ||
            strpos($browser, 'iphone') !== false) {
            return 'Mobile';
        }
        
        // If not mobile or desktop, then it may be tablet
        return 'Tablet';
    }
    
    /**
     * Load campaign_type lookup table once at runtime
     * Caches the entire table in memory using composite key: "campaign_type:campaign_mode:is_adx" => label
     */
    private function loadCampaignTypeLookup() {
        try {
            $query = "SELECT campaign_type, campaign_mode, is_adx, label FROM campaign_type WHERE status = 1";
            $result = $this->mysqli_admin->query($query);
            
            if ($result) {
                while ($row = $result->fetch_assoc()) {
                    $key = $row['campaign_type'] . ':' . $row['campaign_mode'] . ':' . (int)($row['is_adx'] ?? 0);
                    $this->campaign_type_cache[$key] = $row['label'];
                }
                $this->log('Campaign_type lookup table loaded: ' . count($this->campaign_type_cache) . ' entries', 'INFO');
            } else {
                $this->log('Failed to load campaign_type table: ' . $this->mysqli_admin->error, 'WARNING');
            }
        } catch (Exception $e) {
            $this->log('Error loading campaign_type table: ' . $e->getMessage(), 'WARNING');
        }
    }
    
    /**
     * Load creatives_types lookup table once at runtime
     * Caches the entire table in memory using creative_type as key => label
     */
    private function loadCreativesTypeLookup() {
        try {
            $query = "SELECT creative_type, label FROM creatives_types";
            $result = $this->mysqli_admin->query($query);
            
            if ($result) {
                while ($row = $result->fetch_assoc()) {
                    $this->creatives_types_cache[$row['creative_type']] = $row['label'];
                }
                $this->log('Creatives_types lookup table loaded: ' . count($this->creatives_types_cache) . ' entries', 'INFO');
            } else {
                $this->log('Failed to load creatives_types table: ' . $this->mysqli_admin->error, 'WARNING');
            }
        } catch (Exception $e) {
            $this->log('Error loading creatives_types table: ' . $e->getMessage(), 'WARNING');
        }
    }
    
    /**
     * Get creative_label from cached creatives_types lookup table
     */
    private function getCreativeLabel($creative_type) {
        $creative_type = (int) $creative_type;
        
        // Return cached label if available, otherwise 'Unknown'
        return $this->creatives_types_cache[$creative_type] ?? 'Unknown';
    }
    
    /**
     * Get campaign_label from cached campaign_type lookup table
     * Uses composite key: "campaign_type:campaign_mode:is_adx"
     */
    private function getCampaignLabel($campaign_type, $campaign_mode, $is_adx = 0) {
        $campaign_type = (int) $campaign_type;
        $is_adx = (int) ($is_adx ?? 0);
        $campaign_mode = (string) ($campaign_mode ?? '');
        
        $key = $campaign_type . ':' . $campaign_mode . ':' . $is_adx;
        
        // Return cached label if available, otherwise 'Unknown'
        return $this->campaign_type_cache[$key] ?? 'Unknown';
    }
    
    /**
     * Get creative metadata from Admin DB with simple caching
     * Returns array with: creative_name, creative_label (label from creatives_types table), landing_page_url
     */
    private function getCreativeInfo($creative_id) {
        // Check cache first
        if (isset($this->creative_cache[$creative_id])) {
            return $this->creative_cache[$creative_id];
        }
        
        // Query Admin database for creative info
        $query = "SELECT creative_name, creative_type, destination_url, creative_url FROM creatives WHERE id = ? LIMIT 1";
        
        // Ping to ensure Admin DB connection is still alive (reconnect if dropped after long idle)
        if (!$this->mysqli_admin->ping()) {
            $this->mysqli_admin->close();
            $this->mysqli_admin = new mysqli(MSACOMMON_DB_HOST_ADMIN, MSACOMMON_DB_USER_ADMIN, MSACOMMON_DB_PASS_ADMIN, 'Admin');
            $this->mysqli_admin->set_charset('utf8');
        }
        
        $stmt = $this->mysqli_admin->prepare($query);
        
        if (!$stmt) {
            // If query fails, return default values
            $default = [
                'creative_name' => 'Unknown',
                'creative_label' => 'Unknown',
                'landing_page_url' => '',
                'creative_url' => ''
            ];
            $this->creative_cache[$creative_id] = $default;
            return $default;
        }
        
        $stmt->bind_param('i', $creative_id);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($result && $row = $result->fetch_assoc()) {
            // Get creative_label from creatives_types table
            $creative_label = $this->getCreativeLabel($row['creative_type']);
            
            $creative_info = [
                'creative_name' => $row['creative_name'] ?? 'Unknown',
                'creative_label' => $creative_label,
                'landing_page_url' => $row['destination_url'] ?? '',
                'creative_url' => $row['creative_url'] ?? ''
            ];
        } else {
            // Default values if creative not found
            $creative_info = [
                'creative_name' => 'Unknown',
                'creative_label' => 'Unknown',
                'landing_page_url' => '',
                'creative_url' => ''
            ];
        }
        
        $stmt->close();
        
        // Cache the result
        $this->creative_cache[$creative_id] = $creative_info;
        
        return $creative_info;
    }
    
    /**
     * Get campaign metadata from Admin DB with simple caching
     * Returns array with: campaign_name, campaign_label (label from campaign_type table), campaign_mode
     */
    private function getCampaignInfo($campaign_id) {
        // Check cache first
        if (isset($this->campaign_cache[$campaign_id])) {
            return $this->campaign_cache[$campaign_id];
        }
        
        // Query Admin database for campaign info
        // Note: Admin.campaign table uses 'id' and 'name', not 'campaign_id' and 'campaign_name'
        $query = "SELECT name, campaign_type, campaign_mode, is_adx FROM campaign WHERE id = ? LIMIT 1";
        
        // Ping to ensure Admin DB connection is still alive (reconnect if dropped after long idle)
        if (!$this->mysqli_admin->ping()) {
            $this->mysqli_admin->close();
            $this->mysqli_admin = new mysqli(MSACOMMON_DB_HOST_ADMIN, MSACOMMON_DB_USER_ADMIN, MSACOMMON_DB_PASS_ADMIN, 'Admin');
            $this->mysqli_admin->set_charset('utf8');
        }
        
        $stmt = $this->mysqli_admin->prepare($query);
        
        if (!$stmt) {
            // If query fails, return default values
            $default = [
                'campaign_name' => 'Unknown',
                'campaign_label' => 'Unknown',
                'campaign_mode' => 'Unknown'
            ];
            $this->campaign_cache[$campaign_id] = $default;
            return $default;
        }
        
        $stmt->bind_param('i', $campaign_id);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($result && $row = $result->fetch_assoc()) {
            // Get campaign_label from campaign_type table
            $campaign_label = $this->getCampaignLabel($row['campaign_type'], $row['campaign_mode'], $row['is_adx'] ?? 0);
            
            $campaign_info = [
                'campaign_name' => $row['name'] ?? 'Unknown',
                'campaign_label' => $campaign_label,
                'campaign_mode' => $row['campaign_mode'] ?? 'Unknown'
            ];
        } else {
            // Default values if campaign not found
            $campaign_info = [
                'campaign_name' => 'Unknown',
                'campaign_label' => 'Unknown',
                'campaign_mode' => 'Unknown'
            ];
        }
        
        $stmt->close();
        
        // Cache the result
        $this->campaign_cache[$campaign_id] = $campaign_info;
        
        return $campaign_info;
    }
    
    /**
     * Calculate clicks based on campaign_type
     * Logic from old codebase: IF(campaign_type=1, cpm_clicks, status)
     * - If campaign_type = 1 (banner): use cpm_clicks
     * - Otherwise: use status
     */
    private function calculateClicks($campaign_type, $cpm_clicks, $status) {
        $campaign_type = (int) $campaign_type;
        $cpm_clicks = (int) ($cpm_clicks ?? 0);
        $status = (int) ($status ?? 0);
        
        if ($campaign_type === 1) {
            return $cpm_clicks;  // Banner campaigns use cpm_clicks
        } else {
            return $status;  // All other campaign types use status
        }
    }
    
    /**
     * Enrich row with domain, synthetic demographics, and campaign metadata
     */
    private function enrichRowWithData($row, $domains, $advertiser_id = 0) {
        // Use deterministic random (seeded by ID and date for consistency)
        $seed = crc32($row['campaign_id'] . '-' . $row['date'] . '-' . $row['id']);
        srand($seed);
        
        // Return all database columns + enriched data
        $enriched_row = $row; // Start with all database columns
        
        // Transform id to composite format: advertiser_idymdid
        $date_ymd = str_replace('-', '', $row['date']); // Convert 2026-03-15 to 20260315
        $enriched_row['id'] = $advertiser_id . $date_ymd . $row['id'];
        
        // Calculate clicks based on campaign_type (before synthetic demographics)
        $enriched_row['clicks'] = $this->calculateClicks($row['campaign_type'], $row['cpm_clicks'], $row['status']);
        
        // Add synthetic demographics
        $enriched_row['age_group'] = $this->ageRanges[rand(0, count($this->ageRanges) - 1)];
        // 99.9% Male/Female, 0.1% Others
        if (rand(1, 1000) <= 1) {
            $enriched_row['gender'] = 'Others';
        } else {
            $enriched_row['gender'] = $this->genders[rand(0, 1)]; // Male or Female
        }
        // Map device type from OS + Browser instead of random
        $enriched_row['device_type'] = $this->mapDeviceType($row['os'], $row['browser']);
        
        // Add campaign metadata (cached from Admin DB)
        $campaign_info = $this->getCampaignInfo($row['campaign_id']);
        $enriched_row['campaign_name'] = $campaign_info['campaign_name'];
        $enriched_row['campaign_label'] = $campaign_info['campaign_label'];  // Label from campaign_type table (Display, Text Search, Video, Native, Shopping, etc.)
        $enriched_row['campaign_mode'] = $campaign_info['campaign_mode'];
        
        // Add creative metadata (cached from Admin DB)
        $creative_info = $this->getCreativeInfo($row['creative_id']);
        $enriched_row['creative_name'] = $creative_info['creative_name'];
        $enriched_row['creative_label'] = $creative_info['creative_label'];  // Label from creatives_types table (Image, Script, Text, Video, Native, Social Media, Interstitial)
        $enriched_row['landing_page_url'] = $creative_info['landing_page_url'];
        $enriched_row['creative_url'] = $creative_info['creative_url'];
        
        // Add domain last (random from list)
        $enriched_row['domain'] = $this->getRandomDomain($domains);
        
        return $enriched_row;
    }
    
    /**
     * Generate CSV file for an advertiser (streaming approach with batch processing and optional chunking)
     */
    private function generateCSVStreaming($advertiser_id, $advertiser_name) {
        // Determine base filename
        $base_filename = strtolower(str_replace(' ', '_', $advertiser_name)) . '_' . $advertiser_id . '_clicks';
        
        // File handles and tracking for chunked output
        $file_handles = [];     // array of open file handles keyed by chunk_key
        $headers_written = [];  // array of chunk_keys that have had headers written
        $created_files = [];    // track all files created for logging
        
        // Get month from chunk_key to determine directory
        $get_month_from_chunk = function($chunk_key) {
            if ($chunk_key === 'full') {
                return date('Ym'); // Current month
            } elseif (preg_match('/^(\d{4})-(\d{2})-/', $chunk_key)) {
                // Daily format: YYYY-MM-DD
                return substr($chunk_key, 0, 7); // Extract YYYY-MM
            } elseif (preg_match('/^(\d{4})-W\d{2}$/', $chunk_key)) {
                // Weekly format: YYYY-W##
                // Extract year-month from the year part (weeks can span months, use year as directory)
                $year = substr($chunk_key, 0, 4);
                $week = (int)substr($chunk_key, 6, 2);
                // Approximate month based on week: W01-W04 = Jan, W05-W09 = Feb-Mar, etc.
                $approx_month = (int)(($week - 1) / 4.3) + 1; // Rough approximation
                $approx_month = str_pad($approx_month, 2, '0', STR_PAD_LEFT);
                return $year . $approx_month;
            }
            return date('Ym');
        };
        
        // Open a file for a specific chunk
        $open_file = function($chunk_key) use ($base_filename, $advertiser_name, $advertiser_id, $get_month_from_chunk, &$file_handles, &$headers_written, &$created_files) {
            $chunk_suffix = $this->getChunkSuffix($chunk_key);
            
            // Determine file path: no subdirectory for 'full' chunk, advertiser/monthly subdirectories for others
            if ($chunk_key === 'full') {
                // No chunking: files go directly to output_dir
                $subdir = $this->output_dir;
            } else {
                // Chunking enabled: files organized in advertiser/monthly subdirectories (files/{advertiser_id}/YYYYMM/)
                $month_dir = $get_month_from_chunk($chunk_key);
                $subdir = $this->output_dir . $advertiser_id . '/' . $month_dir . '/';
            }
            
            // Create subdirectory if it doesn't exist
            if (!is_dir($subdir)) {
                if (!mkdir($subdir, 0755, true)) {
                    $this->log("Error creating directory {$subdir}", 'ERROR');
                    return null;
                }
            }
            
            $filename = $subdir . $base_filename . $chunk_suffix . '.csv';
            
            $file = fopen($filename, 'w');
            if (!$file) {
                $this->log("Error opening file {$filename}", 'ERROR');
                return null;
            }
            
            $file_handles[$chunk_key] = $file;
            $created_files[$chunk_key] = $filename;
            $this->log("  Created file: {$filename}", 'INFO');
            return $file;
        };
        
        // Close all open files
        $close_all_files = function() use (&$file_handles) {
            foreach ($file_handles as $file) {
                if (is_resource($file)) {
                    fclose($file);
                }
            }
            $file_handles = [];
        };
        
        $this->log("Processing {$advertiser_name} ({$advertiser_id})...", 'INFO');
        $months = $this->getMonthsToProcess();
        $total_rows = 0;
        
        // Batch processing configuration
        $batch_size = 500000; // Process 500k rows at a time
        
        foreach ($months as $month) {
            $table_name = "adv_clicks_{$advertiser_id}_{$month}";
            $domains_table = "adv_domains_{$advertiser_id}_{$month}";
            
            // Attempt to sync tables from remote source if they don't exist locally (only if --drest flag provided)
            if ($this->drest_enabled) {
                // Only sync if table doesn't exist - reduces unnecessary network calls
                if (!$this->tableExists($table_name)) {
                    $this->syncTableFromRemoteIfMissing($table_name);
                }
                if (!$this->tableExists($domains_table)) {
                    $this->syncTableFromRemoteIfMissing($domains_table);
                }
            }
            
            if (!$this->tableExists($table_name)) {
                $this->log("  Table {$table_name} not found (and drest sync unavailable or failed), skipping...", 'WARNING');
                continue;
            }
            
            // Load domains for this month
            $domains = $this->loadDomainsForMonth($advertiser_id, $month);
            
            // Determine dates to process based on chunk_mode and date range
            $dates_to_process = $this->getDatesToProcess($month);
            if ($dates_to_process === []) {
                $this->log("  No date overlap in range for {$table_name}, skipping...", 'INFO');
                continue;
            }
            
            // If dates_to_process is null, process entire month (no date-based iteration)
            // Otherwise, process only specific dates/weeks
            $date_iterations = ($dates_to_process === null) ? [null] : $dates_to_process;
            $month_rows = 0;
            
            foreach ($date_iterations as $specific_date) {
                // Build WHERE clause with date filtering based on chunk_mode
                $date_where = $this->getDateWhereClause($specific_date);
                
                $date_rows = 0;
                
                // Process in batches - iterate until we hit limit or no more rows
                for ($offset = 0; ; $offset += $batch_size) {
                    // Stop if limit reached
                    if ($this->limit > 0 && $total_rows >= $this->limit) {
                        break;
                    }
                    
                    // Query with date filtering for maximum performance
                    $query = "SELECT * FROM {$table_name} WHERE status >= 1" . $date_where . " LIMIT {$batch_size} OFFSET {$offset}";
                    if ($this->limit > 0) {
                        // Adjust batch to not exceed limit
                        $remaining = $this->limit - $total_rows;
                        if ($remaining <= 0) break;
                        $query = "SELECT * FROM {$table_name} WHERE status >= 1" . $date_where . " LIMIT {$remaining} OFFSET {$offset}";
                    }
                    $result = $this->mysqli->query($query);
                
                    if (!$result) {
                        $this->log("  Error querying {$table_name} at offset {$offset}: " . $this->mysqli->error, 'ERROR');
                        continue;
                    }
                    
                    $batch_rows = 0;
                    while ($row = $result->fetch_assoc()) {
                        // Enrich row with domain, synthetic demographics, and campaign metadata
                        $enriched_row = $this->enrichRowWithData($row, $domains, $advertiser_id);
                        
                        // Determine chunk key based on date
                        $chunk_key = $this->getChunkKey($row['date'] ?? '2000-01-01');
                        
                        // Open file for this chunk if not already open
                        if (!isset($file_handles[$chunk_key])) {
                            $open_file($chunk_key);
                        }
                        
                        $file = $file_handles[$chunk_key];
                        
                        // Write headers on first row for this chunk
                        if (!isset($headers_written[$chunk_key])) {
                            $headers = array_keys($enriched_row);
                            fputcsv($file, $headers);
                            $headers_written[$chunk_key] = true;
                        }
                        
                        fputcsv($file, $enriched_row);
                        $batch_rows++;
                        $total_rows++;
                        $month_rows++;
                        $date_rows++;
                        
                        // Stop if we reach the limit
                        if ($this->limit > 0 && $total_rows >= $this->limit) {
                            break 3; // Break out of while, for, and foreach loops
                        }
                    }
                    $result->free();
                    
                    // If batch returned fewer rows than requested, we've reached the end
                    if ($batch_rows < $batch_size && $this->limit <= 0) {
                        break; // No more rows to fetch
                    }
                    if ($batch_rows == 0) {
                        break; // No rows returned, we're done
                    }
                    
                    // Progress logging for large batches
                    $progress_rows_display = min(50000, $date_rows); // Show progress every 50k rows
                    if ($offset % (50000 * $batch_size) === 0 && $offset > 0) {
                        $this->log("  Progress: Processed {$date_rows} rows so far...", 'INFO');
                    }
                    
                    // Garbage collection
                    gc_collect_cycles();
                    
                    // Stop if we reach the limit
                    if ($this->limit > 0 && $total_rows >= $this->limit) {
                        break 2;  // Break out of for and foreach loops
                    }
                }
            }
            
            $this->log("  Completed {$table_name}: {$month_rows} records processed", 'INFO');
            
            // Stop if we've hit the test limit
            if ($this->limit > 0 && $total_rows >= $this->limit) {
                break;
            }
            
            // Force garbage collection after each month
            gc_collect_cycles();
        }
        
        // Close all open files
        $close_all_files();
        
        if ($total_rows == 0) {
            $this->log("✗ No data found for {$advertiser_name} ({$advertiser_id})", 'WARNING');
            // Clean up all created empty files
            foreach ($created_files as $filename) {
                if (file_exists($filename)) {
                    unlink($filename);
                }
            }
            return false;
        }
        
        // Log summary for chunked or non-chunked output
        if ($this->chunk_mode === 'none') {
            // Single file output
            foreach ($created_files as $filename) {
                $file_size = filesize($filename);
                $this->log("✓ Generated CSV: {$filename}", 'INFO');
                $this->log("  Total records: {$total_rows}", 'INFO');
                $this->log("  File size: " . number_format($file_size, 0) . " bytes", 'INFO');
            }
        } else {
            // Chunked output
            $this->log("✓ Generated {$this->chunk_mode} chunked CSVs for {$advertiser_name} ({$advertiser_id}):", 'INFO');
            $total_size = 0;
            foreach ($created_files as $chunk_key => $filename) {
                $total_size += filesize($filename);
            }
            $this->log("  Total records: {$total_rows}", 'INFO');
            $this->log("  Total files: " . count($created_files), 'INFO');
            $this->log("  Combined size: " . number_format($total_size, 0) . " bytes", 'INFO');
        }
        
        $this->log("  Columns (36): id (advertiser_idymdoriginal_id), date, hour, campaign_id, creative_id, source, country, os, browser, keyword, search_query, destination_url, ip, state, city, zip, impressions, conversions, spent, media_cost, status, cpm_clicks, campaign_type, adgroup_id, clicks, age_group, gender, device_type, campaign_name, campaign_label, campaign_mode, creative_name, creative_label, landing_page_url, creative_url, domain", 'INFO');
        
        // Final garbage collection
        gc_collect_cycles();
        
        return true;
    }
    
    /**
     * Generate CSVs for all advertisers (or filtered advertisers)
     */
    public function generate() {
        $this->start_time = microtime(true);
        
        $this->logSection('Consolidated Clicks CSV Generator');
        $this->log('Date: ' . date('Y-m-d H:i:s'), 'INFO');
        $this->log('Mode: ' . ($this->limit > 0 ? "TEST (limit: {$this->limit} records)" : 'FULL (all data)'), 'INFO');
        
        // Show period info - either custom range or months
        if ($this->start_date && $this->end_date) {
            $this->log('Period: ' . $this->start_date . ' to ' . $this->end_date . ' (custom range)', 'INFO');
        } else {
            $this->log('Period: Last ' . $this->months . ' month' . ($this->months !== 1 ? 's' : ''), 'INFO');
        }
        
        $this->log('Chunking: ' . ucfirst($this->chunk_mode) . ($this->chunk_mode === 'daily' ? ' (one file per date)' : ($this->chunk_mode === 'weekly' ? ' (one file per ISO week)' : ' (single file per advertiser)')), 'INFO');
        $this->log('Drest Sync: ' . ($this->drest_enabled ? 'ENABLED' : 'DISABLED (using existing tables)'), 'INFO');
        $this->log('Output directory: ' . $this->output_dir, 'INFO');
        $this->log('Log file: ' . $this->log_file, 'INFO');
        $this->log('', 'INFO');
        
        // Log optimization settings
        $this->log('Performance Optimizations:', 'INFO');
        $this->log('  Memory limit: ' . ini_get('memory_limit'), 'INFO');
        $this->log('  Max execution time: ' . (ini_get('max_execution_time') == 0 ? 'Unlimited' : ini_get('max_execution_time') . 's'), 'INFO');
        $this->log('  Garbage collection: Enabled (50k row batches)', 'INFO');
        $this->log('  Query sorting: Disabled (for performance)', 'INFO');
        $this->log('', 'INFO');
        
        // Filter advertisers if specified
        $advertisers_to_process = $this->advertisers;
        if ($this->advertiser_filter) {
            if (isset($advertisers_to_process[$this->advertiser_filter])) {
                $advertisers_to_process = [$this->advertiser_filter => $advertisers_to_process[$this->advertiser_filter]];
            } else {
                $this->log("✗ Advertiser {$this->advertiser_filter} not found", 'ERROR');
                return;
            }
        }
        
        foreach ($advertisers_to_process as $advertiser_id => $advertiser_name) {
            $this->generateCSVStreaming($advertiser_id, $advertiser_name);
        }
        
        $this->logSection('Process Complete');
        $this->log('Log file saved at: ' . $this->log_file, 'INFO');
        
        // Calculate and log total execution time
        $execution_time = microtime(true) - $this->start_time;
        $hours = floor($execution_time / 3600);
        $minutes = floor(($execution_time % 3600) / 60);
        $seconds = floor($execution_time % 60);
        $this->log('Total execution time: ' . sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds), 'INFO');
    }
    
    public function __destruct() {
        if ($this->mysqli) {
            $this->mysqli->close();
            $this->log('Keywords database connection closed', 'INFO');
        }
        if ($this->mysqli_admin) {
            $this->mysqli_admin->close();
            $this->log('Admin database connection closed', 'INFO');
        }
    }
}

// Run the generator
$script_start_time = microtime(true);
try {
    $generator = new ClicksCSVGenerator($argv);
    $generator->generate();
} catch (Exception $e) {
    $error_msg = 'Error: ' . $e->getMessage();
    echo $error_msg . "\n";
    
    // Calculate and show execution time even if error occurred
    $execution_time = microtime(true) - $script_start_time;
    $hours = floor($execution_time / 3600);
    $minutes = floor(($execution_time % 3600) / 60);
    $seconds = floor($execution_time % 60);
    $time_str = sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);
    echo "Execution time before error: {$time_str}\n";
    
    exit(1);
}
?>