<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

require_once 'vendor/autoload.php';

use Aws\S3\S3Client;
use Aws\Exception\AwsException;

class DailyHistoricalImporter {
    private $s3Client;
    private $pdo;
    private $bucket;
    
    public function __construct($awsConfig, $mysqlConfig, $bucket) {
        // Initialize S3 client
        $this->s3Client = new S3Client([
            'version' => 'latest',
            'region'  => $awsConfig['region'] ?? 'us-east-1',
            'credentials' => [
                'key'    => $awsConfig['access_key'],
                'secret' => $awsConfig['secret_key'],
            ]
        ]);
        
        // Initialize MySQL PDO connection
        $dsn = "mysql:host={$mysqlConfig['host']};dbname={$mysqlConfig['database']};charset=utf8mb4";
        $this->pdo = new PDO($dsn, $mysqlConfig['username'], $mysqlConfig['password'], [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]);
        
        $this->bucket = $bucket;
        $this->createImportLogTable();
    }
    
    private function createImportLogTable() {
        $sql = "CREATE TABLE IF NOT EXISTS `daily_import_log` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `import_date` DATE NOT NULL,
            `s3_path` VARCHAR(200) NOT NULL,
            `total_files` INT DEFAULT 0,
            `processed_files` INT DEFAULT 0,
            `failed_files` INT DEFAULT 0,
            `total_records` BIGINT DEFAULT 0,
            `import_status` ENUM('in_progress', 'completed', 'failed') DEFAULT 'in_progress',
            `started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            `completed_at` TIMESTAMP NULL,
            `error_summary` TEXT,
            UNIQUE KEY `unique_date` (`import_date`),
            KEY `idx_status` (`import_status`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        $this->pdo->exec($sql);
        
        // Also create a table to track which tables exist and their import timestamps
        $metaSql = "CREATE TABLE IF NOT EXISTS `table_import_metadata` (
            `table_name` VARCHAR(100) PRIMARY KEY,
            `last_import_date` DATE NOT NULL,
            `record_count` BIGINT DEFAULT 0,
            `last_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY `idx_import_date` (`last_import_date`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        $this->pdo->exec($metaSql);
        
        echo "Import tracking tables ready\n";
    }
    
    public function getLatestAvailableDate() {
        // Start with today and work backwards to find the most recent available data
        $today = new DateTime();
        
        for ($i = 0; $i < 7; $i++) { // Check up to 7 days back
            $checkDate = clone $today;
            $checkDate->sub(new DateInterval("P{$i}D"));
            $dateStr = $checkDate->format('Y-m-d');
            $s3Path = $this->getS3PathForDate($dateStr);
            
            echo "Checking for data on {$dateStr} (path: {$s3Path})...\n";
            
            try {
                $files = $this->listParquetFiles($s3Path);
                if (!empty($files)) {
                    echo "? Found " . count($files) . " files for {$dateStr}\n";
                    return $dateStr;
                }
            } catch (Exception $e) {
                echo "No data found for {$dateStr}\n";
                continue;
            }
        }
        
        throw new Exception("No recent data found in the last 7 days");
    }
    
    public function getS3PathForDate($date) {
        $dateObj = new DateTime($date);
        return "nightly_dump_parquet/" . $dateObj->format('Y/n/j');
    }
    
    public function shouldProcessDate($date) {
        // Check if we already processed this date successfully
        $stmt = $this->pdo->prepare("
            SELECT import_status, completed_at 
            FROM daily_import_log 
            WHERE import_date = ? AND import_status = 'completed'
        ");
        $stmt->execute([$date]);
        $result = $stmt->fetch();
        
        if ($result) {
            echo "Date {$date} was already processed successfully at {$result['completed_at']}\n";
            return false;
        }
        
        return true;
    }
    
    public function startImportSession($date, $s3Path, $totalFiles) {
        $stmt = $this->pdo->prepare("
            INSERT INTO daily_import_log 
            (import_date, s3_path, total_files, import_status) 
            VALUES (?, ?, ?, 'in_progress')
            ON DUPLICATE KEY UPDATE 
                s3_path = VALUES(s3_path),
                total_files = VALUES(total_files),
                processed_files = 0,
                failed_files = 0,
                total_records = 0,
                import_status = 'in_progress',
                started_at = CURRENT_TIMESTAMP,
                completed_at = NULL,
                error_summary = NULL
        ");
        
        $stmt->execute([$date, $s3Path, $totalFiles]);
        echo "Started import session for {$date} with {$totalFiles} files\n";
    }
    
    public function updateImportProgress($date, $processedFiles, $failedFiles, $totalRecords) {
        $stmt = $this->pdo->prepare("
            UPDATE daily_import_log 
            SET processed_files = ?, 
                failed_files = ?, 
                total_records = ?
            WHERE import_date = ?
        ");
        
        $stmt->execute([$processedFiles, $failedFiles, $totalRecords, $date]);
    }
    
    public function completeImportSession($date, $success, $errorSummary = null) {
        $status = $success ? 'completed' : 'failed';
        
        $stmt = $this->pdo->prepare("
            UPDATE daily_import_log 
            SET import_status = ?, 
                completed_at = CURRENT_TIMESTAMP,
                error_summary = ?
            WHERE import_date = ?
        ");
        
        $stmt->execute([$status, $errorSummary, $date]);
        
        if ($success) {
            echo "? Import session completed successfully for {$date}!\n";
        } else {
            echo "? Import session failed for {$date}\n";
        }
    }
    
    public function listParquetFiles($s3Path) {
        try {
            $result = $this->s3Client->listObjectsV2([
                'Bucket' => $this->bucket,
                'Prefix' => $s3Path,
            ]);
            
            $parquetFiles = [];
            if (isset($result['Contents'])) {
                foreach ($result['Contents'] as $object) {
                    if (substr($object['Key'], -8) === '.parquet') {
                        $parquetFiles[] = $object['Key'];
                    }
                }
            }
            
            // Sort files for consistent processing order
            sort($parquetFiles);
            return $parquetFiles;
            
        } catch (AwsException $e) {
            throw new Exception("Error listing S3 objects: " . $e->getMessage());
        }
    }
    
    public function getTableNameFromFilename($filename) {
        $basename = basename($filename, '.parquet');
        $tableName = preg_replace('/[^a-zA-Z0-9_]/', '_', $basename);
        $tableName = preg_replace('/_+/', '_', $tableName);
        $tableName = trim($tableName, '_');
        
        if (preg_match('/^[0-9]/', $tableName)) {
            $tableName = 'table_' . $tableName;
        }
        
        return $tableName;
    }
    
    public function downloadParquetFile($s3Key) {
        try {
            $result = $this->s3Client->getObject([
                'Bucket' => $this->bucket,
                'Key'    => $s3Key,
            ]);
            
            $tempFile = tempnam(sys_get_temp_dir(), 'parquet_');
            file_put_contents($tempFile, $result['Body']->getContents());
            
            return $tempFile;
            
        } catch (AwsException $e) {
            throw new Exception("Error downloading {$s3Key}: " . $e->getMessage());
        }
    }
    
    public function convertParquetToCSV($parquetFile) {
        $csvFile = tempnam(sys_get_temp_dir(), 'csv_') . '.csv';
        
        // Try Python pandas first (most reliable)
        $pythonScript = "
import pandas as pd
import sys
try:
    df = pd.read_parquet('{$parquetFile}')
    df.to_csv('{$csvFile}', index=False)
    print('success')
except Exception as e:
    print(f'error: {e}')
    sys.exit(1)
";
        $tempPyFile = tempnam(sys_get_temp_dir(), 'convert_') . '.py';
        file_put_contents($tempPyFile, $pythonScript);
        
        $command = "python3 {$tempPyFile} 2>&1";
        exec($command, $output, $returnCode);
        unlink($tempPyFile);
        
        if ($returnCode === 0 && file_exists($csvFile) && filesize($csvFile) > 0) {
            return $csvFile;
        }
        
        // Fallback to parquet-tools
        $command = "parquet-tools cat --json {$parquetFile} 2>/dev/null";
        exec($command, $jsonOutput, $returnCode2);
        
        if ($returnCode2 === 0 && !empty($jsonOutput)) {
            $this->convertJSONLinesToCSV($jsonOutput, $csvFile);
            return $csvFile;
        }
        
        throw new Exception("Failed to convert parquet file to CSV");
    }
    
    private function convertJSONLinesToCSV($jsonLines, $csvFile) {
        $handle = fopen($csvFile, 'w');
        $headers = [];
        $headerWritten = false;
        
        foreach ($jsonLines as $line) {
            if (empty(trim($line))) continue;
            
            $data = json_decode($line, true);
            if ($data === null) continue;
            
            if (!$headerWritten) {
                $headers = array_keys($data);
                fputcsv($handle, $headers);
                $headerWritten = true;
            }
            
            $row = [];
            foreach ($headers as $header) {
                $row[] = isset($data[$header]) ? $data[$header] : '';
            }
            fputcsv($handle, $row);
        }
        
        fclose($handle);
    }
    
    public function analyzeCSVStructure($csvFile) {
        $handle = fopen($csvFile, 'r');
        if (!$handle) {
            throw new Exception("Cannot open CSV file");
        }
        
        $headers = fgetcsv($handle);
        if (!$headers) {
            fclose($handle);
            throw new Exception("Cannot read CSV headers");
        }
        
        // Clean headers - remove empty ones and reindex
        $cleanHeaders = [];
        foreach ($headers as $header) {
            if (!empty(trim($header))) {
                $cleanHeaders[] = trim($header);
            }
        }
        
        if (empty($cleanHeaders)) {
            fclose($handle);
            throw new Exception("No valid headers found in CSV file");
        }
        
        $headerCount = count($cleanHeaders);
        echo "Found {$headerCount} valid headers\n";
        
        // Sample data for type detection
        $sampleData = [];
        $rowCount = 0;
        $lineNumber = 1; // Track line number for debugging
        
        while (($row = fgetcsv($handle)) !== false && $rowCount < 100) {
            $lineNumber++;
            
            // Skip completely empty rows
            if (empty(array_filter($row, function($v) { return $v !== null && $v !== ''; }))) {
                continue;
            }
            
            // Handle row/header count mismatch
            $rowColumnCount = count($row);
            if ($rowColumnCount !== $headerCount) {
                echo "Warning: Line {$lineNumber} has {$rowColumnCount} columns, expected {$headerCount}. ";
                
                // Pad or trim row to match header count
                if ($rowColumnCount < $headerCount) {
                    $row = array_pad($row, $headerCount, '');
                    echo "Padded with empty values.\n";
                } else {
                    $row = array_slice($row, 0, $headerCount);
                    echo "Trimmed extra columns.\n";
                }
            }
            
            try {
                // Now we can safely combine since counts match
                $combinedRow = array_combine($cleanHeaders, $row);
                if ($combinedRow !== false) {
                    $sampleData[] = $combinedRow;
                    $rowCount++;
                }
            } catch (ValueError $e) {
                echo "Warning: Skipping problematic line {$lineNumber}: " . $e->getMessage() . "\n";
                echo "Headers count: " . count($cleanHeaders) . ", Row count: " . count($row) . "\n";
                continue;
            }
        }
        fclose($handle);
        
        if (empty($sampleData)) {
            throw new Exception("No valid data rows found in CSV file");
        }
        
        echo "Analyzed {$rowCount} sample rows for structure detection\n";
        
        // Determine column types
        $columnTypes = [];
        foreach ($cleanHeaders as $header) {
            $columnTypes[$header] = $this->guessColumnType($sampleData, $header);
        }
        
        return [
            'headers' => $cleanHeaders,
            'types' => $columnTypes,
            'sample_count' => count($sampleData)
        ];
    }
    
    private function guessColumnType($sampleData, $columnName) {
        $values = array_column($sampleData, $columnName);
        $nonNullValues = array_filter($values, function($v) { return $v !== null && $v !== ''; });
        
        if (empty($nonNullValues)) return 'TEXT';
        
        $isInteger = true;
        $isFloat = true;
        $isDate = true;
        
        foreach (array_slice($nonNullValues, 0, 20) as $value) { // Check first 20 values
            if (!preg_match('/^-?\d+$/', $value)) $isInteger = false;
            if (!is_numeric($value)) $isFloat = false;
            if (!strtotime($value)) $isDate = false;
        }
        
        if ($isInteger) return 'BIGINT';
        if ($isFloat) return 'DOUBLE';
        if ($isDate) return 'DATETIME';
        
        $maxLength = max(array_map('strlen', $nonNullValues));
        return $maxLength > 255 ? 'TEXT' : "VARCHAR(" . min($maxLength * 2, 1000) . ")";
    }
    
    public function createOrReplaceTable($tableName, $structure, $importDate) {
        // Since this is a full historical dump, we replace the entire table
        $this->pdo->exec("DROP TABLE IF EXISTS `{$tableName}`");
        
        $columns = ["`sparkle_id` BIGINT AUTO_INCREMENT PRIMARY KEY"];
        
        foreach ($structure['types'] as $column => $type) {
            $columns[] = "`{$column}` {$type}";
        }
        
        $columns[] = "`import_date` DATE NOT NULL";
        $columns[] = "`import_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP";
        $columns[] = "KEY `idx_import_date` (`import_date`)";
        $columns[] = "KEY `idx_import_timestamp` (`import_timestamp`)";
        
        $sql = "CREATE TABLE `{$tableName}` (
            " . implode(",\n            ", $columns) . "
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        $this->pdo->exec($sql);
        echo "Created fresh table: {$tableName}\n";
        
        // Update metadata
        $stmt = $this->pdo->prepare("
            INSERT INTO table_import_metadata (table_name, last_import_date, record_count) 
            VALUES (?, ?, 0)
            ON DUPLICATE KEY UPDATE 
                last_import_date = VALUES(last_import_date),
                record_count = 0
        ");
        $stmt->execute([$tableName, $importDate]);
    }
    
    public function importCSVToTable($csvFile, $tableName, $structure, $importDate) {
        $headers = $structure['headers'];
        $headerCount = count($headers);
        $placeholders = str_repeat('?,', $headerCount + 1); // +1 for import_date
        $placeholders = rtrim($placeholders, ',');
        $columnList = '`' . implode('`, `', $headers) . '`, `import_date`';
        
        $sql = "INSERT INTO `{$tableName}` ({$columnList}) VALUES ({$placeholders})";
        $stmt = $this->pdo->prepare($sql);
        
        $handle = fopen($csvFile, 'r');
        fgetcsv($handle); // Skip header row
        
        $insertCount = 0;
        $skippedCount = 0;
        $batchSize = 1000;
        $batch = [];
        $lineNumber = 1; // Track line numbers
        
        $this->pdo->beginTransaction();
        
        try {
            while (($row = fgetcsv($handle)) !== false) {
                $lineNumber++;
                
                // Skip completely empty rows
                if (empty(array_filter($row, function($v) { return $v !== null && $v !== ''; }))) {
                    continue;
                }
                
                // Handle row/header count mismatch
                $rowColumnCount = count($row);
                if ($rowColumnCount !== $headerCount) {
                    // Pad or trim row to match expected column count
                    if ($rowColumnCount < $headerCount) {
                        $row = array_pad($row, $headerCount, '');
                    } else {
                        $row = array_slice($row, 0, $headerCount);
                    }
                }
                
                // Sanitize row data
                $cleanRow = $this->sanitizeRowData($row, $structure['types'], $headers);
                
                // Skip row if sanitization failed
                if ($cleanRow === false) {
                    $skippedCount++;
                    echo "Skipped problematic row {$lineNumber}\n";
                    continue;
                }
                
                // Add import_date to the row
                $cleanRow[] = $importDate;
                
                $batch[] = $cleanRow;
                
                if (count($batch) >= $batchSize) {
                    $this->processBatch($stmt, $batch, $insertCount);
                    $batch = [];
                }
            }
            
            // Process remaining batch
            if (!empty($batch)) {
                $this->processBatch($stmt, $batch, $insertCount);
            }
            
            $this->pdo->commit();
            fclose($handle);
            
            // Update metadata
            $stmt = $this->pdo->prepare("
                UPDATE table_import_metadata 
                SET record_count = ? 
                WHERE table_name = ?
            ");
            $stmt->execute([$insertCount, $tableName]);
            
            echo "Imported {$insertCount} records into {$tableName}";
            if ($skippedCount > 0) {
                echo " (skipped {$skippedCount} problematic rows)";
            }
            echo "\n";
            
            return $insertCount;
            
        } catch (Exception $e) {
            $this->pdo->rollBack();
            fclose($handle);
            throw $e;
        }
    }
    
    private function sanitizeRowData($row, $columnTypes, $headers) {
        $cleanRow = [];
        
        for ($i = 0; $i < count($headers); $i++) {
            $value = isset($row[$i]) ? $row[$i] : '';
            $header = $headers[$i];
            $type = isset($columnTypes[$header]) ? $columnTypes[$header] : 'TEXT';
            
            // Handle NULL/empty values
            if ($value === '' || $value === null || strtolower($value) === 'null') {
                $cleanRow[] = null;
                continue;
            }
            
            // Sanitize based on column type
            switch (true) {
                case stripos($type, 'INT') !== false || stripos($type, 'BIGINT') !== false:
                    // Clean integer values
                    $value = preg_replace('/[^0-9-]/', '', $value);
                    $cleanRow[] = $value === '' ? null : intval($value);
                    break;
                    
                case stripos($type, 'DOUBLE') !== false || stripos($type, 'FLOAT') !== false:
                    // Clean float values
                    $value = preg_replace('/[^0-9.-]/', '', $value);
                    $cleanRow[] = $value === '' ? null : floatval($value);
                    break;
                    
                case stripos($type, 'DATETIME') !== false || stripos($type, 'TIMESTAMP') !== false:
                    // Handle datetime values
                    $cleanRow[] = $this->sanitizeDateTime($value);
                    break;
                    
                case stripos($type, 'DATE') !== false:
                    // Handle date values
                    $cleanRow[] = $this->sanitizeDate($value);
                    break;
                    
                default:
                    // Handle text values - remove null bytes and control characters
                    $value = str_replace(["\0", "\x00"], '', $value);
                    $value = preg_replace('/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]/', '', $value);
                    $cleanRow[] = $value;
                    break;
            }
        }
        
        return $cleanRow;
    }
    
    private function sanitizeDateTime($value) {
        if (empty($value)) return null;
        
        // Common datetime formats to try
        $formats = [
            'Y-m-d H:i:s',
            'Y-m-d H:i:s.u',
            'Y-m-d\TH:i:s',
            'Y-m-d\TH:i:s\Z',
            'Y-m-d\TH:i:s.u\Z',
            'Y-m-d',
            'd/m/Y H:i:s',
            'm/d/Y H:i:s'
        ];
        
        foreach ($formats as $format) {
            $date = DateTime::createFromFormat($format, $value);
            if ($date !== false) {
                return $date->format('Y-m-d H:i:s');
            }
        }
        
        // Try strtotime as fallback
        $timestamp = strtotime($value);
        if ($timestamp !== false) {
            return date('Y-m-d H:i:s', $timestamp);
        }
        
        // If all else fails, return null
        return null;
    }
    
    private function sanitizeDate($value) {
        if (empty($value)) return null;
        
        $formats = ['Y-m-d', 'd/m/Y', 'm/d/Y', 'Y-m-d H:i:s'];
        
        foreach ($formats as $format) {
            $date = DateTime::createFromFormat($format, $value);
            if ($date !== false) {
                return $date->format('Y-m-d');
            }
        }
        
        $timestamp = strtotime($value);
        if ($timestamp !== false) {
            return date('Y-m-d', $timestamp);
        }
        
        return null;
    }
    
    private function processBatch($stmt, $batch, &$insertCount) {
        foreach ($batch as $row) {
            try {
                $stmt->execute($row);
                $insertCount++;
            } catch (Exception $e) {
                // Log error but continue
                echo "Warning: Failed to insert row: " . $e->getMessage() . "\n";
            }
        }
        
        if ($insertCount % 10000 == 0) {
            echo "Imported {$insertCount} records...\n";
        }
    }
    
    public function processLatestData($forceDate = null) {
        try {
            echo "? DAILY HISTORICAL DATA IMPORT\n";
            echo str_repeat("=", 60) . "\n";
            
            // Determine which date to process
            $targetDate = $forceDate ?: $this->getLatestAvailableDate();
            $s3Path = $this->getS3PathForDate($targetDate);
            
            echo "Processing date: {$targetDate}\n";
            echo "S3 path: {$s3Path}\n";
            
            // Check if already processed (unless forced)
            if (!$forceDate && !$this->shouldProcessDate($targetDate)) {
                echo "? Data for {$targetDate} already processed. Use force option to reprocess.\n";
                return;
            }
            
            // Get all files
            $allFiles = $this->listParquetFiles($s3Path);
            if (empty($allFiles)) {
                echo "? No parquet files found for {$targetDate}\n";
                return;
            }
            
            echo "Found " . count($allFiles) . " files to process\n\n";
            
            // Start import session
            $this->startImportSession($targetDate, $s3Path, count($allFiles));
            
            $processedFiles = 0;
            $failedFiles = 0;
            $totalRecords = 0;
            $errors = [];
            
            foreach ($allFiles as $fileKey) {
                echo "\n" . str_repeat("-", 50) . "\n";
                echo "Processing: " . basename($fileKey) . "\n";
                echo str_repeat("-", 50) . "\n";
                
                try {
                    $tableName = $this->getTableNameFromFilename($fileKey);
                    echo "Table: {$tableName}\n";
                    
                    // Download and convert
                    $parquetFile = $this->downloadParquetFile($fileKey);
                    
                    try {
                        $csvFile = $this->convertParquetToCSV($parquetFile);
                        $structure = $this->analyzeCSVStructure($csvFile);
                        
                        // Create fresh table (replaces old data)
                        $this->createOrReplaceTable($tableName, $structure, $targetDate);
                        
                        // Import data
                        $recordCount = $this->importCSVToTable($csvFile, $tableName, $structure, $targetDate);
                        $totalRecords += $recordCount;
                        
                        echo "? SUCCESS: {$recordCount} records\n";
                        $processedFiles++;
                        
                        unlink($csvFile);
                        
                    } finally {
                        unlink($parquetFile);
                    }
                    
                } catch (Exception $e) {
                    $failedFiles++;
                    $error = "File: " . basename($fileKey) . " - Error: " . $e->getMessage();
                    $errors[] = $error;
                    echo "? FAILED: " . $e->getMessage() . "\n";
                }
                
                // Update progress
                $this->updateImportProgress($targetDate, $processedFiles, $failedFiles, $totalRecords);
                
                // Brief pause to avoid overwhelming the system
                usleep(500000); // 0.5 seconds
            }
            
            // Complete session
            $success = ($failedFiles == 0);
            $errorSummary = empty($errors) ? null : implode("; ", $errors);
            $this->completeImportSession($targetDate, $success, $errorSummary);
            
            echo "\n" . str_repeat("=", 60) . "\n";
            echo "? IMPORT SUMMARY\n";
            echo str_repeat("=", 60) . "\n";
            echo "Date: {$targetDate}\n";
            echo "Files processed: {$processedFiles}/" . count($allFiles) . "\n";
            echo "Files failed: {$failedFiles}\n";
            echo "Total records: " . number_format($totalRecords) . "\n";
            
            if ($success) {
                echo "? ALL DATA IMPORTED SUCCESSFULLY!\n";
            } else {
                echo "??  Some files failed. Check error summary in daily_import_log table.\n";
            }
            
        } catch (Exception $e) {
            echo "? FATAL ERROR: " . $e->getMessage() . "\n";
            throw $e;
        }
    }
    
    public function getImportStatus() {
        echo "? RECENT IMPORT STATUS\n";
        echo str_repeat("=", 40) . "\n";
        
        $stmt = $this->pdo->query("
            SELECT * FROM daily_import_log 
            ORDER BY import_date DESC LIMIT 10
        ");
        $imports = $stmt->fetchAll();
        
        foreach ($imports as $import) {
            echo "Date: {$import['import_date']} | Status: {$import['import_status']}\n";
            echo "  Files: {$import['processed_files']}/{$import['total_files']} | Records: " . number_format($import['total_records']) . "\n";
            if ($import['completed_at']) {
                echo "  Completed: {$import['completed_at']}\n";
            }
            echo "\n";
        }
    }
}

// Configuration
$awsConfig = [
    'access_key' => 'AKIAXRET5SHQB7HVOIKN',
    'secret_key' => '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',
    'region' => 'us-east-1'
];

$mysqlConfig = [
    'host' => 'localhost',
    'database' => 'dataforsparkle_mytime_sync',
    'username' => 'dataforsparkle_syncuser',
    'password' => 'Sync2025!!'
];

$bucket = 'mytime-sparkle';

// Main execution
try {
    $importer = new DailyHistoricalImporter($awsConfig, $mysqlConfig, $bucket);
    
    if (isset($argv[1])) {
        switch($argv[1]) {
            case 'auto':
                $importer->processLatestData();
                break;
                
            case 'force':
                $date = $argv[2] ?? null;
                if (!$date) {
                    echo "Usage: php script.php force YYYY-MM-DD\n";
                    exit(1);
                }
                $importer->processLatestData($date);
                break;
                
            case 'status':
                $importer->getImportStatus();
                break;
                
            default:
                echo "Usage:\n";
                echo "  php script.php auto                 - Process latest available data\n";
                echo "  php script.php force YYYY-MM-DD     - Force process specific date\n";
                echo "  php script.php status               - Show import status\n";
                exit(1);
        }
    } else {
        echo "Processing latest available data...\n\n";
        $importer->processLatestData();
    }
    
} catch (Exception $e) {
    echo "Fatal error: " . $e->getMessage() . "\n";
    exit(1);
}
?>