<?php
require_once __DIR__ . '/db.php';
require_once __DIR__ . '/functions.php';

function ensureListeningNoteTables() {
    static $done = false;
    if ($done) return;
    $pdo = getDBConnection();
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS track_notes (
            id INT AUTO_INCREMENT PRIMARY KEY,
            song_id INT NOT NULL,
            note_text LONGTEXT DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_song_id (song_id),
            INDEX idx_song_id (song_id),
            INDEX idx_updated_at (updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS track_ratings (
            id INT AUTO_INCREMENT PRIMARY KEY,
            song_id INT NOT NULL,
            rating TINYINT DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_song_id (song_id),
            INDEX idx_song_id (song_id),
            INDEX idx_rating (rating),
            INDEX idx_updated_at (updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    $done = true;
}

function getSongNote($songId) {
    ensureListeningNoteTables();
    $stmt = getDBConnection()->prepare("SELECT * FROM track_notes WHERE song_id = :song_id");
    $stmt->execute([':song_id' => (int)$songId]);
    return $stmt->fetch() ?: null;
}

function getSongRating($songId) {
    ensureListeningNoteTables();
    $stmt = getDBConnection()->prepare("SELECT rating FROM track_ratings WHERE song_id = :song_id");
    $stmt->execute([':song_id' => (int)$songId]);
    $value = $stmt->fetchColumn();
    return $value === false || $value === null ? 0 : (int)$value;
}

function saveSongNote($songId, $noteText) {
    ensureListeningNoteTables();
    $noteText = trim((string)$noteText);
    $stmt = getDBConnection()->prepare("
        INSERT INTO track_notes (song_id, note_text)
        VALUES (:song_id, :note_text)
        ON DUPLICATE KEY UPDATE note_text = :note_text_update, updated_at = CURRENT_TIMESTAMP
    ");
    $stmt->execute([
        ':song_id' => (int)$songId,
        ':note_text' => $noteText !== '' ? $noteText : null,
        ':note_text_update' => $noteText !== '' ? $noteText : null,
    ]);
}

function deleteSongNote($songId) {
    ensureListeningNoteTables();
    $stmt = getDBConnection()->prepare("DELETE FROM track_notes WHERE song_id = :song_id");
    $stmt->execute([':song_id' => (int)$songId]);
}

function saveSongRating($songId, $rating) {
    ensureListeningNoteTables();
    $rating = max(0, min(5, (int)$rating));
    if ($rating === 0) {
        $stmt = getDBConnection()->prepare("DELETE FROM track_ratings WHERE song_id = :song_id");
        $stmt->execute([':song_id' => (int)$songId]);
        return;
    }
    $stmt = getDBConnection()->prepare("
        INSERT INTO track_ratings (song_id, rating)
        VALUES (:song_id, :rating)
        ON DUPLICATE KEY UPDATE rating = :rating_update, updated_at = CURRENT_TIMESTAMP
    ");
    $stmt->execute([
        ':song_id' => (int)$songId,
        ':rating' => $rating,
        ':rating_update' => $rating,
    ]);
}

function renderRatingStars($rating, $songId = null, $editable = false) {
    $rating = max(0, min(5, (int)$rating));
    $class = $editable ? 'rating-stars rating-stars-editable' : 'rating-stars';
    echo '<div class="' . h($class) . '" data-song-id="' . h($songId ?? '') . '" data-rating="' . h($rating) . '">';
    for ($i = 1; $i <= 5; $i++) {
        $active = $i <= $rating ? 'active' : '';
        if ($editable) {
            echo '<button type="button" class="rating-star ' . $active . '" data-rating="' . $i . '" aria-label="' . $i . '점"><i class="' . ($i <= $rating ? 'fas' : 'far') . ' fa-star"></i></button>';
        } else {
            echo '<span class="rating-star ' . $active . '"><i class="' . ($i <= $rating ? 'fas' : 'far') . ' fa-star"></i></span>';
        }
    }
    if ($editable) echo '<button type="button" class="rating-clear-btn" data-rating="0">미평가</button>';
    echo '</div>';
}

function getListeningNoteSongs($filter = 'recent_notes', $limit = 50) {
    ensureListeningNoteTables();
    $pdo = getDBConnection();
    $limit = max(1, min(200, (int)$limit));
    $where = "s.is_missing = 0";
    $order = "s.id DESC";
    if ($filter === 'top_rated') {
        $where .= " AND COALESCE(r.rating, 0) >= 4";
        $order = "r.rating DESC, s.play_count DESC, s.title ASC";
    } elseif ($filter === 'unrated') {
        $where .= " AND r.id IS NULL";
    } elseif ($filter === 'has_lyrics') {
        $where .= " AND ly.lyrics_text IS NOT NULL AND TRIM(ly.lyrics_text) != ''";
    } elseif ($filter === 'no_lyrics') {
        $where .= " AND (ly.lyrics_text IS NULL OR TRIM(ly.lyrics_text) = '')";
    } elseif ($filter === 'has_notes') {
        $where .= " AND n.note_text IS NOT NULL AND TRIM(n.note_text) != ''";
        $order = "n.updated_at DESC";
    } elseif ($filter === 'liked_unrated') {
        $where .= " AND l.id IS NOT NULL AND r.id IS NULL";
        $order = "l.created_at DESC";
    } elseif ($filter === 'popular_no_notes') {
        $where .= " AND s.play_count > 0 AND (n.note_text IS NULL OR TRIM(n.note_text) = '')";
        $order = "s.play_count DESC";
    } else {
        $where .= " AND n.note_text IS NOT NULL AND TRIM(n.note_text) != ''";
        $order = "n.updated_at DESC";
    }
    $sql = baseSongSelectSql() . "
        LEFT JOIN lyrics ly ON ly.song_id = s.id
        WHERE $where
        ORDER BY $order
        LIMIT $limit
    ";
    return $pdo->query($sql)->fetchAll();
}

function getListeningNoteStats() {
    ensureListeningNoteTables();
    $pdo = getDBConnection();
    return [
        'rated' => (int)$pdo->query("SELECT COUNT(*) FROM track_ratings WHERE rating > 0")->fetchColumn(),
        'five_star' => (int)$pdo->query("SELECT COUNT(*) FROM track_ratings WHERE rating = 5")->fetchColumn(),
        'notes' => (int)$pdo->query("SELECT COUNT(*) FROM track_notes WHERE note_text IS NOT NULL AND TRIM(note_text) != ''")->fetchColumn(),
        'lyrics' => (int)$pdo->query("SELECT COUNT(*) FROM lyrics WHERE lyrics_text IS NOT NULL AND TRIM(lyrics_text) != ''")->fetchColumn(),
        'unrated' => (int)$pdo->query("SELECT COUNT(*) FROM songs s LEFT JOIN track_ratings r ON r.song_id = s.id WHERE s.is_missing = 0 AND r.id IS NULL")->fetchColumn(),
    ];
}
