<?php

if (!class_exists('CommonPDOConnector')) {
    require_once 'dbPDO.php';
}

/**
 * Get rows from aff_cpc_summary with flexible query options
 * 
 * @param $db_admin Database connection
 * @param string $start_date Start date (Y-m-d format)
 * @param string $end_date End date (Y-m-d format)
 * @param array $options Query options:
 *   - 'conditions' => array of WHERE conditions
 *   - 'joins' => array of JOIN clauses
 *   - 'select' => SELECT clause (default: '*')
 *   - 'order_by' => ORDER BY clause
 *   - 'offset' => pagination offset
 *   - 'limit' => pagination limit
 *   - 'sql_template' => custom SQL template with {table} placeholder
 * @return array Array of records
 */
function getAffCpcSummaryRows($db_admin, $start_date, $end_date, $options = array()) {
    // Calculate archive threshold (12 months ago from current date)
    // $archive_threshold = date('Y-m-d', strtotime('-12 months'));
    $archive_threshold = "2025-06-15";
    
    // Default options
    $defaults = array(
        'conditions' => array(),
        'joins' => array(),
        'select' => '*',
        'order_by' => 'date, time',
        'offset' => 0,
        'limit' => null,
        'sql_template' => null
    );
    $options = array_merge($defaults, $options);
    
    // Build SQL template if not provided
    if ($options['sql_template'] === null) {
        $options['sql_template'] = buildSqlTemplate($options);
    }
    
    // Single table queries (most efficient)
    if ($start_date > $archive_threshold && $end_date > $archive_threshold) {
        return executeQueryOnTable($db_admin, 'aff_cpc_summary_test', $options['sql_template'], $start_date, $end_date, $options);
    }
    
    if ($start_date <= $archive_threshold && $end_date <= $archive_threshold) {
        return executeQueryOnTable($db_admin, 'aff_cpc_summary_archive', $options['sql_template'], $start_date, $end_date, $options);
    }
    
    // Cross-threshold queries (merge results from both tables)
    return executeCrossThresholdQuery($db_admin, $options['sql_template'], $start_date, $end_date, $options, $archive_threshold);
}

/**
 * Get aggregated values from aff_cpc_summary
 * 
 * @param $db_admin Database connection
 * @param string $start_date Start date (Y-m-d format)
 * @param string $end_date End date (Y-m-d format)
 * @param array $options Query options:
 *   - 'aggregates' => array of aggregate functions ['COUNT(*) as total', 'SUM(net_cpc) as revenue']
 *   - 'conditions' => array of WHERE conditions
 *   - 'joins' => array of JOIN clauses
 *   - 'group_by' => GROUP BY clause
 *   - 'sql_template' => custom SQL template with {table} placeholder
 *   - 'merge_strategy' => 'sum', 'max', 'min', 'avg', or custom callback function
 * @return array Aggregated results
 */
function getAffCpcSummaryAggregates($db_admin, $start_date, $end_date, $options = array()) {
    // Calculate archive threshold (12 months ago from current date)
    // $archive_threshold = date('Y-m-d', strtotime('-12 months'));
    $archive_threshold = "2025-06-15";
    
    // Default options
    $defaults = array(
        'aggregates' => array('COUNT(*) as total'),
        'conditions' => array(),
        'joins' => array(),
        'group_by' => null,
        'sql_template' => null,
        'merge_strategy' => 'sum'
    );
    $options = array_merge($defaults, $options);
    
    // Build SQL template if not provided
    if ($options['sql_template'] === null) {
        $options['sql_template'] = buildAggregateSqlTemplate($options);
    }
    
    // Execute query across relevant tables
    $results = array();
    
    if ($end_date > $archive_threshold) {
        $main_result = executeAggregateQueryOnTable($db_admin, 'aff_cpc_summary_test', $options['sql_template'], $start_date, $end_date, $options);
        $results[] = $main_result;
    }
    
    if ($start_date <= $archive_threshold) {
        $archive_result = executeAggregateQueryOnTable($db_admin, 'aff_cpc_summary_archive', $options['sql_template'], $start_date, $end_date, $options);
        $results[] = $archive_result;
    }
    
    // Merge results based on strategy
    return mergeAggregateResults($results, $options['merge_strategy']);
}

// ============================================================================
// HELPER FUNCTIONS
// ============================================================================

/**
 * Build SQL template for row queries
 */
function buildSqlTemplate($options) {
    $sql = "SELECT " . $options['select'] . " FROM {table} a";
    
    // Add joins
    foreach ($options['joins'] as $join) {
        $sql .= " " . $join;
    }
    
    // Add WHERE clause
    $sql .= " WHERE a.date BETWEEN :start_date AND :end_date";
    
    // Add conditions
    foreach ($options['conditions'] as $condition) {
        $sql .= " AND " . $condition['field'] . " " . $condition['operator'] . " :" . $condition['field'];
    }
    
    // Add ORDER BY
    if ($options['order_by']) {
        if (is_array($options['order_by'])) {
            $order_clauses = array();
            foreach ($options['order_by'] as $order_item) {
                $field = $order_item['field'];
                $direction = isset($order_item['dir']) ? strtoupper($order_item['dir']) : 'ASC';
                $order_clauses[] = $field . ' ' . $direction;
            }
            $sql .= " ORDER BY " . implode(', ', $order_clauses);
        } else {
            // Fallback for string order_by (backward compatibility)
            $sql .= " ORDER BY " . $options['order_by'];
        }
    }
    
    return $sql;
}

/**
 * Build SQL template for aggregate queries
 */
function buildAggregateSqlTemplate($options) {
    $sql = "SELECT " . implode(', ', $options['aggregates']) . " FROM {table} a";
    
    // Add joins
    foreach ($options['joins'] as $join) {
        $sql .= " " . $join;
    }
    
    // Add WHERE clause
    $sql .= " WHERE a.date BETWEEN :start_date AND :end_date";
    
    // Add conditions
    foreach ($options['conditions'] as $condition) {
        $sql .= " AND " . $condition['field'] . " " . $condition['operator'] . " :" . $condition['field'];
    }
    
    // Add GROUP BY
    if ($options['group_by']) {
        $sql .= " GROUP BY " . $options['group_by'];
    }
    
    return $sql;
}

/**
 * Execute query on a single table
 */
function executeQueryOnTable($db_admin, $table, $sql_template, $start_date, $end_date, $options) {
    $sql = str_replace('{table}', $table, $sql_template);
    
    // Add LIMIT if specified
    if ($options['limit'] !== null) {
        $sql .= " LIMIT " . $options['offset'] . ", " . $options['limit'];
    }
    
    // Build parameters
    $params = array(':start_date' => $start_date, ':end_date' => $end_date);
    foreach ($options['conditions'] as $condition) {
        $params[':' . $condition['field']] = $condition['value'];
    }
    
    $stmt = $db_admin->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

/**
 * Execute aggregate query on a single table
 */
function executeAggregateQueryOnTable($db_admin, $table, $sql_template, $start_date, $end_date, $options) {
    $sql = str_replace('{table}', $table, $sql_template);
    
    // Build parameters
    $params = array(':start_date' => $start_date, ':end_date' => $end_date);
    foreach ($options['conditions'] as $condition) {
        $params[':' . $condition['field']] = $condition['value'];
    }
    
    $stmt = $db_admin->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

/**
 * Execute cross-threshold query (merge results from multiple tables)
 */
function executeCrossThresholdQuery($db_admin, $sql_template, $start_date, $end_date, $options, $archive_threshold) {
    $all_results = array();
    
    // Get data from main table (recent data)
    if ($end_date > $archive_threshold) {
        $main_results = executeQueryOnTable($db_admin, 'aff_cpc_summary_test', $sql_template, $start_date, $end_date, $options);
        $all_results = array_merge($all_results, $main_results);
    }
    
    // Get data from archive table (old data)
    if ($start_date <= $archive_threshold) {
        $archive_results = executeQueryOnTable($db_admin, 'aff_cpc_summary_archive', $sql_template, $start_date, $end_date, $options);
        $all_results = array_merge($all_results, $archive_results);
    }
    
    // Sort results if ORDER BY was specified
    if ($options['order_by']) {
        usort($all_results, function($a, $b) use ($options) {
            // Handle order_by as array of field/direction pairs
            if (is_array($options['order_by'])) {
                foreach ($options['order_by'] as $order_item) {
                    $field = $order_item['field'];
                    $direction = isset($order_item['dir']) ? strtolower($order_item['dir']) : 'asc';
                    
                    if (isset($a[$field]) && isset($b[$field])) {
                        $a_val = $a[$field];
                        $b_val = $b[$field];
                        
                        // Handle numeric values
                        if (is_numeric($a_val) && is_numeric($b_val)) {
                            if ($a_val < $b_val) {
                                $comparison = -1;
                            } elseif ($a_val > $b_val) {
                                $comparison = 1;
                            } else {
                                $comparison = 0;
                            }
                        } else {
                            // Handle string values
                            $comparison = strcmp($a_val, $b_val);
                        }
                        
                        // Apply direction
                        if ($direction === 'desc') {
                            $comparison = -$comparison;
                        }
                        
                        if ($comparison !== 0) {
                            return $comparison;
                        }
                    }
                }
                return 0; // If all fields are equal
            }
        });
    }
    
    // Apply pagination
    if ($options['limit'] !== null) {
        $all_results = array_slice($all_results, $options['offset'], $options['limit']);
    }
    
    return $all_results;
}

/**
 * Merge aggregate results based on strategy
 */
function mergeAggregateResults($results, $merge_strategy) {
    if (empty($results)) {
        return array();
    }
    
    // If only one result set, return it directly
    if (count($results) === 1) {
        return isset($results[0][0]) ? $results[0][0] : array();
    }
    
    // Handle custom callback
    if (is_callable($merge_strategy)) {
        return call_user_func($merge_strategy, $results);
    }
    
    // Handle predefined strategies
    $merged = array();
    $first_result = isset($results[0][0]) ? $results[0][0] : array();
    
    foreach ($first_result as $key => $value) {
        $merged[$key] = 0;
        
        foreach ($results as $result_set) {
            if (isset($result_set[0][$key])) {
                switch ($merge_strategy) {
                    case 'sum':
                        $merged[$key] += $result_set[0][$key];
                        break;
                    case 'max':
                        $merged[$key] = max($merged[$key], $result_set[0][$key]);
                        break;
                    case 'min':
                        $merged[$key] = min($merged[$key], $result_set[0][$key]);
                        break;
                    case 'avg':
                        // For average, we'll need to track count separately
                        // This is a simplified version
                        $merged[$key] += $result_set[0][$key];
                        break;
                }
            }
        }
    }
    
    return $merged;
}

?>