<?php

function metadataRequestTypeLabels() {
    return [
        'title' => '제목',
        'artist' => '아티스트',
        'album' => '앨범',
        'genre' => '장르',
        'year' => '발매연도',
        'lyrics' => '가사',
        'cover' => '앨범아트',
        'other' => '기타',
    ];
}

function metadataRequestStatusLabels() {
    return [
        'received' => '접수',
        'reviewing' => '확인 중',
        'applied' => '반영 완료',
        'held' => '보류',
        'rejected' => '거절',
    ];
}

function metadataRequestTypeLabel($type) {
    $labels = metadataRequestTypeLabels();
    return $labels[(string)$type] ?? $labels['other'];
}

function metadataRequestStatusLabel($status) {
    $labels = metadataRequestStatusLabels();
    return $labels[(string)$status] ?? $labels['received'];
}

function ensureMetadataRequestsTable() {
    static $checked = false;
    if ($checked) return;
    $checked = true;

    $pdo = getDBConnection();
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS metadata_correction_requests (
            id INT AUTO_INCREMENT PRIMARY KEY,
            song_id INT NOT NULL,
            request_type VARCHAR(30) NOT NULL DEFAULT 'other',
            status VARCHAR(30) NOT NULL DEFAULT 'received',
            current_title VARCHAR(255) DEFAULT NULL,
            current_artist VARCHAR(255) DEFAULT NULL,
            current_album VARCHAR(255) DEFAULT NULL,
            current_genre VARCHAR(120) DEFAULT NULL,
            current_year VARCHAR(20) DEFAULT NULL,
            suggested_value TEXT DEFAULT NULL,
            reason TEXT DEFAULT NULL,
            requester_admin_id INT DEFAULT NULL,
            requester_ip_hash CHAR(64) DEFAULT NULL,
            user_agent VARCHAR(255) DEFAULT NULL,
            page_url VARCHAR(500) DEFAULT NULL,
            admin_note TEXT DEFAULT NULL,
            handled_by_admin INT DEFAULT NULL,
            handled_at DATETIME DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_meta_req_song (song_id),
            KEY idx_meta_req_status (status),
            KEY idx_meta_req_type (request_type),
            KEY idx_meta_req_created (created_at),
            KEY idx_meta_req_admin (requester_admin_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
}

function metadataRequestClientHash() {
    $ip = (string)($_SERVER['REMOTE_ADDR'] ?? '');
    if ($ip === '') return null;
    return hash('sha256', $ip . '|melonlite-metadata-request');
}

function metadataRequestCleanText($value, $limit) {
    $value = trim(maskInternalPathText(ensureUtf8String($value)));
    $value = trim(strip_tags($value));
    if ($limit > 0 && function_exists('mb_substr')) {
        return mb_substr($value, 0, $limit, 'UTF-8');
    }
    return $limit > 0 ? substr($value, 0, $limit) : $value;
}

function metadataRequestSanitizeUrl($value) {
    $value = trim(maskInternalPathText(ensureUtf8String($value)));
    if ($value === '') return '';

    $parts = @parse_url($value);
    if (!is_array($parts)) return '';

    $host = (string)($_SERVER['HTTP_HOST'] ?? '');
    if (!empty($parts['host']) && $host !== '' && strcasecmp($parts['host'], $host) !== 0) {
        return '';
    }

    $path = (string)($parts['path'] ?? '');
    if ($path === '') $path = function_exists('currentRelativeUrl') ? currentRelativeUrl() : '/';

    $query = '';
    if (!empty($parts['query'])) {
        parse_str((string)$parts['query'], $queryParams);
        foreach (array_keys($queryParams) as $key) {
            if (preg_match('/path|file|dir|folder|root/i', (string)$key)) {
                unset($queryParams[$key]);
            }
        }
        $query = http_build_query($queryParams);
    }

    $safe = str_replace('\\', '/', $path . ($query !== '' ? '?' . $query : ''));
    if (strpos($safe, '..') !== false) return '';
    return metadataRequestCleanText($safe, 500);
}

function metadataRequestCanSubmit($songId, $requestType, $adminUserId, $ipHash) {
    ensureMetadataRequestsTable();

    $params = [
        ':song_id' => (int)$songId,
        ':request_type' => (string)$requestType,
    ];
    $conditions = [];

    if ($ipHash !== null && $ipHash !== '') {
        $conditions[] = 'requester_ip_hash = :requester_ip_hash';
        $params[':requester_ip_hash'] = $ipHash;
    }
    if ((int)$adminUserId > 0) {
        $conditions[] = 'requester_admin_id = :requester_admin_id';
        $params[':requester_admin_id'] = (int)$adminUserId;
    }
    if (empty($conditions)) return true;

    $pdo = getDBConnection();
    $stmt = $pdo->prepare("
        SELECT COUNT(*)
        FROM metadata_correction_requests
        WHERE song_id = :song_id
          AND request_type = :request_type
          AND created_at >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
          AND (" . implode(' OR ', $conditions) . ")
    ");
    $stmt->execute($params);
    return (int)$stmt->fetchColumn() === 0;
}
