<?php
require_once 'config.php';
$db = $pdo;

// Load CSV with BOM fix
$csv = fopen('/home/megaloa1/dj.megaload.co.za/spotify-cron/SpotifyFeatures.csv', 'r');
$rawHeader = fgetcsv($csv);
if (substr($rawHeader[0], 0, 3) === "\xEF\xBB\xBF") {
    $rawHeader[0] = substr($rawHeader[0], 3);
}
$header = array_map(fn($h) => strtolower(trim($h)), $rawHeader);
$col = array_flip($header);

// Camelot key text map (C, F#, etc. + Major/Minor to Camelot)
$camelot_lookup = [
    'C'   => '8B',  'C#'  => '3B',  'D'   => '10B', 'D#' => '5B',
    'E'   => '12B', 'F'   => '7B',  'F#'  => '2B',  'G'  => '9B',
    'G#'  => '4B',  'A'   => '11B', 'A#'  => '6B',  'B'  => '1B',
    'Cm'  => '8A',  'C#m' => '3A',  'Dm'  => '10A','D#m'=> '5A',
    'Em'  => '12A', 'Fm'  => '7A',  'F#m' => '2A', 'Gm' => '9A',
    'G#m' => '4A',  'Am'  => '11A','A#m' => '6A',  'Bm' => '1A'
];

$count = 0;

while (($row = fgetcsv($csv)) !== false) {
    $genre            = $row[$col['genre']];
    $artist           = $row[$col['artist_name']];
    $title            = $row[$col['track_name']];
    $track_id         = $row[$col['track_id']];
    $popularity       = (int)$row[$col['popularity']];
    $acousticness     = (float)$row[$col['acousticness']];
    $danceability     = (float)$row[$col['danceability']];
    $duration_ms      = (int)$row[$col['duration_ms']];
    $energy           = (float)$row[$col['energy']];
    $instrumentalness = (float)$row[$col['instrumentalness']];
    $key_text         = strtoupper(trim($row[$col['key']]));
    $mode_text        = strtolower(trim($row[$col['mode']]));
    $liveness         = (float)$row[$col['liveness']];
    $loudness         = (float)$row[$col['loudness']];
    $speechiness      = (float)$row[$col['speechiness']];
    $tempo            = (float)$row[$col['tempo']];
    $time_signature   = (int)$row[$col['time_signature']];
    $valence          = (float)$row[$col['valence']];

    // Mode numeric for DB (1 = major, 0 = minor)
    $mode = $mode_text === 'minor' ? 0 : 1;
    $key  = $key_text;

    // Construct Camelot code from key+mode
    $key_combo = $mode_text === 'minor' ? $key_text . 'm' : $key_text;
    $camelot = $camelot_lookup[$key_combo] ?? null;

    // Prepare insert
    $stmt = $db->prepare("
        INSERT INTO audio_features (
            genre, artist_name, track_name, track_id, popularity, acousticness, danceability,
            duration_ms, energy, instrumentalness, `key`, liveness, loudness, mode,
            speechiness, tempo, time_signature, valence, camelot, source
        ) VALUES (
            :genre, :artist, :title, :track_id, :popularity, :acousticness, :danceability,
            :duration_ms, :energy, :instrumentalness, :key, :liveness, :loudness, :mode,
            :speechiness, :tempo, :time_signature, :valence, :camelot, 'kaggle'
        )
        ON DUPLICATE KEY UPDATE
            tempo = VALUES(tempo), `key` = VALUES(`key`), camelot = VALUES(camelot)
    ");

    $stmt->execute([
        ':genre'            => $genre,
        ':artist'           => $artist,
        ':title'            => $title,
        ':track_id'         => $track_id,
        ':popularity'       => $popularity,
        ':acousticness'     => $acousticness,
        ':danceability'     => $danceability,
        ':duration_ms'      => $duration_ms,
        ':energy'           => $energy,
        ':instrumentalness' => $instrumentalness,
        ':key'              => $key,
        ':liveness'         => $liveness,
        ':loudness'         => $loudness,
        ':mode'             => $mode,
        ':speechiness'      => $speechiness,
        ':tempo'            => $tempo,
        ':time_signature'   => $time_signature,
        ':valence'          => $valence,
        ':camelot'          => $camelot
    ]);

    $count++;
    if ($count % 1000 === 0) {
        echo "⏳ Imported $count rows...\n";
    }
}

fclose($csv);
echo "✅ Completed import of $count rows from Kaggle CSV.\n";
?>