<?php
// Enable error reporting
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Include configuration
require_once $_SERVER['DOCUMENT_ROOT'] . '/config.php';

// Connect to database
try {
    $db = new PDO("mysql:host=$host_gscroll;dbname=$database_gscroll", $username_gscroll, $password_gscroll);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}

// Directory containing the .gz files
$source_directory = __DIR__ . '/'; // Current directory
$files_processed = [];
$files_skipped = [];
$files_failed = [];
$team_id_cache = [];

// Function to get ESPN team ID from database
function getEspnTeamId($league, $team_abbrev, $db, &$cache) {
    // Create cache key
    $cache_key = $league . '_' . $team_abbrev;
    
    // Check cache first
    if (isset($cache[$cache_key])) {
        return $cache[$cache_key];
    }
    
    // IMPORTANT: Map CFB to NCAAF for database lookup since that's how it's stored
    $db_league = ($league === 'CFB') ? 'NCAAF' : $league;
    
    try {
        $query = "SELECT espn_team_id FROM team_rgb_correct WHERE league = :league AND abbreviation = :abbrev LIMIT 1";
        $stmt = $db->prepare($query);
        $stmt->execute([':league' => $db_league, ':abbrev' => $team_abbrev]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($result && !empty($result['espn_team_id'])) {
            $cache[$cache_key] = $result['espn_team_id'];
            return $result['espn_team_id'];
        }
    } catch (PDOException $e) {
        error_log("Database query error: " . $e->getMessage());
    }
    
    return null;
}

// Start HTML output
echo "<!DOCTYPE html>";
echo "<html><head><title>File Renaming Report</title>";
echo "<style>";
echo "table { border-collapse: collapse; width: 100%; margin: 20px 0; }";
echo "th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }";
echo "th { background-color: #4CAF50; color: white; }";
echo "tr:nth-child(even) { background-color: #f2f2f2; }";
echo ".success { background-color: #d4edda; }";
echo ".warning { background-color: #fff3cd; }";
echo ".error { background-color: #f8d7da; }";
echo ".summary { margin: 20px 0; padding: 15px; background-color: #e7f3ff; border-radius: 5px; }";
echo "</style></head><body>";

echo "<h1>Scoring Animation File Renaming Report</h1>";
echo "<div class='summary'>";
echo "<h2>Process Summary</h2>";
echo "<p>Directory: $source_directory</p>";
echo "<p>Start Time: " . date('Y-m-d H:i:s') . "</p>";
echo "<p style='color: red; font-weight: bold;'>Note: CFB files will look up NCAAF in database</p>";
echo "</div>";

// Get all .gz files
$gz_files = glob($source_directory . "*.gz");
$total_files = count($gz_files);

echo "<div class='summary'>";
echo "<p>Total .gz files found: $total_files</p>";
echo "</div>";

// Process each file
echo "<h2>File Processing Details</h2>";
echo "<table>";
echo "<thead>";
echo "<tr>";
echo "<th>#</th>";
echo "<th>Original Filename</th>";
echo "<th>League (File)</th>";
echo "<th>League (DB)</th>";
echo "<th>Conference</th>";
echo "<th>Team</th>";
echo "<th>ESPN Team ID</th>";
echo "<th>New Filename</th>";
echo "<th>Status</th>";
echo "<th>Message</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";

$count = 0;
foreach ($gz_files as $filepath) {
    $count++;
    $filename = basename($filepath);
    
    // Parse filename: LEAGUE_CONFERENCE_TEAM_SCORETYPE_NUM_SIZE.gz
    // Example: CFB_AAC_ARMY_2PT_1_6.gz
    $parts = explode('_', pathinfo($filename, PATHINFO_FILENAME));
    
    if (count($parts) < 4) {
        echo "<tr class='error'>";
        echo "<td>$count</td>";
        echo "<td>$filename</td>";
        echo "<td colspan='7'>Invalid filename format</td>";
        echo "<td>Skipped</td>";
        echo "</tr>";
        $files_skipped[] = $filename;
        continue;
    }
    
    $league = $parts[0];
    $conference = $parts[1];
    $team_abbrev = $parts[2];
    
    // Map CFB to NCAAF for database lookup
    $db_league = ($league === 'CFB') ? 'NCAAF' : $league;
    
    // Reconstruct the rest of the filename after team abbreviation
    $remaining_parts = array_slice($parts, 3);
    $suffix = implode('_', $remaining_parts);
    
    // Get ESPN team ID
    $espn_team_id = getEspnTeamId($league, $team_abbrev, $db, $team_id_cache);
    
    if (!$espn_team_id) {
        echo "<tr class='warning'>";
        echo "<td>$count</td>";
        echo "<td>$filename</td>";
        echo "<td>$league</td>";
        echo "<td>$db_league</td>";
        echo "<td>$conference</td>";
        echo "<td>$team_abbrev</td>";
        echo "<td>NOT FOUND</td>";
        echo "<td>-</td>";
        echo "<td>Failed</td>";
        echo "<td>ESPN Team ID not found in database for $db_league / $team_abbrev</td>";
        echo "</tr>";
        $files_failed[] = $filename;
        continue;
    }
    
    // Construct new filename - keep the original league from the file (CFB)
    $new_filename = $league . '_' . $espn_team_id . '_' . $team_abbrev . '_' . $suffix . '.gz';
    $new_filepath = $source_directory . $new_filename;
    
    // Check if new file already exists
    if (file_exists($new_filepath)) {
        echo "<tr class='warning'>";
        echo "<td>$count</td>";
        echo "<td>$filename</td>";
        echo "<td>$league</td>";
        echo "<td>$db_league</td>";
        echo "<td>$conference</td>";
        echo "<td>$team_abbrev</td>";
        echo "<td>$espn_team_id</td>";
        echo "<td>$new_filename</td>";
        echo "<td>Skipped</td>";
        echo "<td>File already exists</td>";
        echo "</tr>";
        $files_skipped[] = $filename;
        continue;
    }
    
    // Copy the file with new name
    if (copy($filepath, $new_filepath)) {
        echo "<tr class='success'>";
        echo "<td>$count</td>";
        echo "<td>$filename</td>";
        echo "<td>$league</td>";
        echo "<td>$db_league</td>";
        echo "<td>$conference</td>";
        echo "<td>$team_abbrev</td>";
        echo "<td>$espn_team_id</td>";
        echo "<td>$new_filename</td>";
        echo "<td>Success</td>";
        echo "<td>File copied successfully</td>";
        echo "</tr>";
        $files_processed[] = [
            'original' => $filename,
            'new' => $new_filename,
            'team_id' => $espn_team_id
        ];
    } else {
        echo "<tr class='error'>";
        echo "<td>$count</td>";
        echo "<td>$filename</td>";
        echo "<td>$league</td>";
        echo "<td>$db_league</td>";
        echo "<td>$conference</td>";
        echo "<td>$team_abbrev</td>";
        echo "<td>$espn_team_id</td>";
        echo "<td>$new_filename</td>";
        echo "<td>Failed</td>";
        echo "<td>Could not copy file</td>";
        echo "</tr>";
        $files_failed[] = $filename;
    }
}

echo "</tbody></table>";

// Summary statistics
echo "<div class='summary'>";
echo "<h2>Final Summary</h2>";
echo "<table style='width: 50%;'>";
echo "<tr><th>Metric</th><th>Count</th><th>Percentage</th></tr>";
$success_count = count($files_processed);
$skip_count = count($files_skipped);
$fail_count = count($files_failed);
echo "<tr class='success'><td>Successfully Processed</td><td>$success_count</td><td>" . ($total_files > 0 ? round($success_count / $total_files * 100, 2) : 0) . "%</td></tr>";
echo "<tr class='warning'><td>Skipped</td><td>$skip_count</td><td>" . ($total_files > 0 ? round($skip_count / $total_files * 100, 2) : 0) . "%</td></tr>";
echo "<tr class='error'><td>Failed</td><td>$fail_count</td><td>" . ($total_files > 0 ? round($fail_count / $total_files * 100, 2) : 0) . "%</td></tr>";
echo "<tr><th>Total Files</th><th>$total_files</th><th>100%</th></tr>";
echo "</table>";
echo "</div>";

// Team ID lookup cache summary
echo "<div class='summary'>";
echo "<h2>Team ID Cache</h2>";
echo "<p>Unique team IDs retrieved: " . count($team_id_cache) . "</p>";
echo "<table style='width: 50%;'>";
echo "<tr><th>League (File)</th><th>League (DB)</th><th>Team</th><th>ESPN Team ID</th></tr>";
foreach ($team_id_cache as $key => $id) {
    list($cache_league, $cache_team) = explode('_', $key);
    $cache_db_league = ($cache_league === 'CFB') ? 'NCAAF' : $cache_league;
    echo "<tr><td>$cache_league</td><td>$cache_db_league</td><td>$cache_team</td><td>$id</td></tr>";
}
echo "</table>";
echo "</div>";

// Failed files detail
if (count($files_failed) > 0) {
    echo "<div class='summary' style='background-color: #f8d7da;'>";
    echo "<h2>Failed Files Detail</h2>";
    echo "<ul>";
    foreach ($files_failed as $failed_file) {
        echo "<li>$failed_file</li>";
    }
    echo "</ul>";
    echo "</div>";
}

// Show a sample SQL query for debugging
echo "<div class='summary'>";
echo "<h2>Debug Info</h2>";
echo "<p>Sample SQL query being used:</p>";
echo "<code>SELECT espn_team_id FROM team_rgb_correct WHERE league = 'NCAAF' AND abbreviation = 'ARMY' LIMIT 1</code>";
echo "</div>";

echo "<div class='summary'>";
echo "<p>End Time: " . date('Y-m-d H:i:s') . "</p>";
echo "</div>";

echo "</body></html>";

// Close database connection
$db = null;
?>