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

function backupAllowedTables() {
    return [
        'songs',
        'liked_songs',
        'play_history',
        'playlists',
        'playlist_songs',
        'lyrics',
        'track_notes',
        'track_ratings',
        'tags',
        'song_tags',
        'settings',
        'scan_logs',
        'maintenance_logs',
        'admin_logs',
        'app_logs',
    ];
}

function backupChildFirstTables() {
    return [
        'playlist_songs',
        'song_tags',
        'liked_songs',
        'play_history',
        'lyrics',
        'track_notes',
        'track_ratings',
        'playlists',
        'tags',
        'songs',
        'settings',
        'scan_logs',
        'maintenance_logs',
        'admin_logs',
        'app_logs',
    ];
}

function backupExportSections() {
    return [
        'metadata' => ['label' => '곡 메타데이터', 'tables' => ['songs']],
        'playlists' => ['label' => '플레이리스트', 'tables' => ['playlists', 'playlist_songs']],
        'tags' => ['label' => '태그', 'tables' => ['tags', 'song_tags']],
        'lyrics_notes_ratings' => ['label' => '가사 / 메모 / 별점', 'tables' => ['lyrics', 'track_notes', 'track_ratings']],
        'listening' => ['label' => '좋아요 / 청취 기록', 'tables' => ['liked_songs', 'play_history']],
        'settings' => ['label' => '설정', 'tables' => ['settings']],
        'logs' => ['label' => '로그', 'tables' => ['scan_logs', 'maintenance_logs', 'admin_logs', 'app_logs']],
    ];
}

function ensureBackupStorage() {
    $dir = ensureBackupDir();
    if (!is_dir($dir)) {
        throw new RuntimeException('백업 폴더를 만들 수 없습니다.');
    }
    if (!is_writable($dir)) {
        throw new RuntimeException('백업 폴더에 쓸 수 없습니다.');
    }

    $indexFile = $dir . '/index.html';
    if (!is_file($indexFile)) {
        @file_put_contents($indexFile, '');
    }

    $htaccessFile = $dir . '/.htaccess';
    if (!is_file($htaccessFile)) {
        @file_put_contents($htaccessFile, "Require all denied\nDeny from all\n");
    }
    return $dir;
}

function backupTableExists($table) {
    static $cache = [];
    $table = (string)$table;
    if (array_key_exists($table, $cache)) return $cache[$table];
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = :table_name");
    $stmt->execute([':table_name' => $table]);
    $cache[$table] = ((int)$stmt->fetchColumn()) > 0;
    return $cache[$table];
}

function backupTableColumns($table) {
    static $cache = [];
    $table = (string)$table;
    if (array_key_exists($table, $cache)) return $cache[$table];
    if (!in_array($table, backupAllowedTables(), true) || !backupTableExists($table)) {
        $cache[$table] = [];
        return [];
    }
    $rows = getDBConnection()->query("SHOW COLUMNS FROM `$table`")->fetchAll();
    $cache[$table] = array_values(array_map(function ($row) {
        return (string)$row['Field'];
    }, $rows));
    return $cache[$table];
}

function backupSafeFilename($file) {
    $file = basename((string)$file);
    if ($file === '' || strpos($file, '..') !== false) return '';
    $patterns = [
        '/^music_backup_[0-9]{4}-[0-9]{2}-[0-9]{2}_[0-9]{6}(?:_before_restore)?\.json$/',
        '/^music_export_[0-9]{4}-[0-9]{2}-[0-9]{2}_[0-9]{6}\.json$/',
        '/^music_import_[0-9]{4}-[0-9]{2}-[0-9]{2}_[0-9]{6}_[A-Za-z0-9]{6}\.json$/',
    ];
    foreach ($patterns as $pattern) {
        if (preg_match($pattern, $file)) return $file;
    }
    return '';
}

function backupFilePath($file) {
    $file = backupSafeFilename($file);
    if ($file === '') return null;
    $dir = ensureBackupStorage();
    $candidate = $dir . '/' . $file;
    $base = realpath($dir);
    $path = is_file($candidate) ? realpath($candidate) : $candidate;
    if (!$base || !$path) return null;
    $base = strtolower(str_replace('\\', '/', $base));
    $normalized = strtolower(str_replace('\\', '/', $path));
    if (strpos($normalized, rtrim($base, '/') . '/') !== 0 && $normalized !== $base) return null;
    return $path;
}

function backupRedactedRow($table, $row, $includePrivatePaths = false) {
    if ($includePrivatePaths || $table !== 'songs') return $row;
    unset($row['file_path'], $row['folder_path'], $row['cover_cache_path']);
    return $row;
}

function backupSettingRowIsExportable($row) {
    $key = strtolower((string)($row['setting_key'] ?? ''));
    foreach (['password', 'secret', 'token', 'csrf', 'session', 'hash'] as $blocked) {
        if (strpos($key, $blocked) !== false) return false;
    }
    return true;
}

function backupCollectTables($tables, $includePrivatePaths = false) {
    $pdo = getDBConnection();
    $result = [];
    $counts = [];
    $schemas = [];
    foreach ($tables as $table) {
        if (!in_array($table, backupAllowedTables(), true) || !backupTableExists($table)) continue;
        $rows = $pdo->query("SELECT * FROM `$table`")->fetchAll();
        if ($table === 'settings') {
            $rows = array_values(array_filter($rows, 'backupSettingRowIsExportable'));
        }
        if (!$includePrivatePaths) {
            $rows = array_map(function ($row) use ($table) {
                return backupRedactedRow($table, $row, false);
            }, $rows);
        }
        $result[$table] = $rows;
        $counts[$table] = count($rows);
        $schema = $pdo->query("SHOW CREATE TABLE `$table`")->fetch();
        $schemas[$table] = $schema['Create Table'] ?? null;
    }
    return ['data' => $result, 'counts' => $counts, 'schemas' => $schemas];
}

function createJsonBackup($exportType = 'full_db', $sections = null, $note = '', $includePrivatePaths = true) {
    $sectionsMap = backupExportSections();
    $tables = [];
    $sectionNames = [];

    if ($sections === null || $exportType === 'full_db') {
        $tables = backupAllowedTables();
        $sectionNames = ['full_db'];
        $includePrivatePaths = true;
    } else {
        foreach ((array)$sections as $section) {
            if (!isset($sectionsMap[$section])) continue;
            $sectionNames[] = $section;
            $tables = array_merge($tables, $sectionsMap[$section]['tables']);
        }
        $tables = array_values(array_unique($tables));
    }

    $collected = backupCollectTables($tables, $includePrivatePaths);
    $payload = [
        'app_name' => 'MelonLite',
        'export_version' => 2,
        'export_type' => $exportType,
        'generated_at' => date('c'),
        'database' => defined('DB_NAME') ? DB_NAME : null,
        'sections' => $sectionNames,
        'counts' => $collected['counts'],
        'schemas' => $collected['schemas'],
        'data' => $collected['data'],
        'tables' => $collected['data'],
        'note' => trim((string)$note),
        'checksum' => null,
    ];
    $payload['checksum'] = hash('sha256', json_encode($payload['data'], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES));

    $prefix = $exportType === 'full_db' ? 'music_backup_' : 'music_export_';
    $fileName = $prefix . date('Y-m-d_His') . '.json';
    $dir = ensureBackupStorage();
    $tmpPath = $dir . '/' . $fileName . '.tmp';
    $finalPath = $dir . '/' . $fileName;
    $json = json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT);
    if ($json === false) throw new RuntimeException('JSON 백업 데이터를 만들 수 없습니다.');
    if (file_put_contents($tmpPath, $json, LOCK_EX) === false) throw new RuntimeException('임시 백업 파일을 저장할 수 없습니다.');
    if (!rename($tmpPath, $finalPath)) {
        @unlink($tmpPath);
        throw new RuntimeException('백업 파일을 확정할 수 없습니다.');
    }
    return [
        'file_name' => $fileName,
        'path' => $finalPath,
        'size' => filesize($finalPath) ?: 0,
        'checksum' => $payload['checksum'],
        'counts' => $payload['counts'],
    ];
}

function readBackupJson($path) {
    if (!is_file($path) || filesize($path) <= 0) throw new RuntimeException('백업 파일이 비어 있거나 없습니다.');
    $raw = file_get_contents($path);
    if ($raw === false) throw new RuntimeException('백업 파일을 읽을 수 없습니다.');
    $data = json_decode($raw, true);
    if (!is_array($data)) throw new RuntimeException('JSON 형식이 올바르지 않습니다.');
    return $data;
}

function backupDataTables($data) {
    if (isset($data['data']) && is_array($data['data'])) return $data['data'];
    if (isset($data['tables']) && is_array($data['tables'])) return $data['tables'];
    return [];
}

function inspectBackupData($data) {
    $errors = [];
    $warnings = [];
    if (!is_array($data)) $errors[] = 'JSON 데이터가 배열 구조가 아닙니다.';
    if (empty($data['app_name']) && (($data['type'] ?? '') !== 'melonlite_json_backup')) $warnings[] = '앱 식별 정보가 없습니다.';
    if (empty($data['generated_at']) && empty($data['created_at'])) $warnings[] = '생성 시간이 없습니다.';
    if (empty($data['export_version']) && empty($data['version'])) $warnings[] = 'export_version 정보가 없습니다.';
    $tables = backupDataTables($data);
    if (empty($tables)) $errors[] = 'data 또는 tables 섹션이 없습니다.';
    $counts = [];
    foreach ($tables as $table => $rows) {
        if (!is_array($rows)) {
            $warnings[] = $table . ' 섹션이 목록 형태가 아닙니다.';
            continue;
        }
        $counts[$table] = count($rows);
    }
    if (!empty($data['checksum'])) {
        $current = hash('sha256', json_encode($tables, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES));
        if (!hash_equals((string)$data['checksum'], $current)) {
            $errors[] = '체크섬이 일치하지 않습니다.';
        }
    }
    return [
        'valid' => empty($errors),
        'errors' => $errors,
        'warnings' => $warnings,
        'counts' => $counts,
        'export_type' => $data['export_type'] ?? $data['type'] ?? 'unknown',
        'generated_at' => $data['generated_at'] ?? $data['created_at'] ?? null,
        'checksum' => $data['checksum'] ?? null,
    ];
}

function inspectBackupFile($file) {
    $path = backupFilePath($file);
    if (!$path || !is_file($path)) {
        return ['valid' => false, 'errors' => ['백업 파일을 찾을 수 없습니다.'], 'warnings' => [], 'counts' => []];
    }
    try {
        $data = readBackupJson($path);
        $info = inspectBackupData($data);
        $info['file_name'] = basename($path);
        $info['file_size'] = filesize($path) ?: 0;
        $info['modified_at'] = filemtime($path) ?: null;
        return $info;
    } catch (Throwable $e) {
        return ['valid' => false, 'errors' => [$e->getMessage()], 'warnings' => [], 'counts' => [], 'file_name' => basename($path), 'file_size' => is_file($path) ? filesize($path) : 0];
    }
}

function listBackupFiles() {
    $dir = ensureBackupStorage();
    $files = glob($dir . '/music_*.json') ?: [];
    usort($files, function ($a, $b) {
        return filemtime($b) <=> filemtime($a);
    });
    $items = [];
    foreach ($files as $path) {
        $name = basename($path);
        if (backupSafeFilename($name) === '') continue;
        $info = inspectBackupFile($name);
        $info['file_name'] = $name;
        $info['file_size'] = filesize($path) ?: 0;
        $info['modified_at'] = filemtime($path) ?: null;
        $items[] = $info;
    }
    return $items;
}

function deleteBackupFile($file) {
    $path = backupFilePath($file);
    if (!$path || !is_file($path)) throw new RuntimeException('삭제할 백업 파일을 찾을 수 없습니다.');
    $name = basename($path);
    if (!unlink($path)) throw new RuntimeException('백업 파일을 삭제할 수 없습니다.');
    return $name;
}

function backupPreviewImport($data) {
    $inspect = inspectBackupData($data);
    $tables = backupDataTables($data);
    $result = [
        'valid' => $inspect['valid'],
        'errors' => $inspect['errors'],
        'warnings' => $inspect['warnings'],
        'counts' => $inspect['counts'],
        'additions' => 0,
        'changes' => 0,
        'conflicts' => 0,
        'skipped' => 0,
    ];
    if (!$result['valid']) return $result;

    $pdo = getDBConnection();
    foreach ($tables as $table => $rows) {
        if (!in_array($table, backupAllowedTables(), true) || !backupTableExists($table) || !is_array($rows)) {
            $result['skipped'] += is_array($rows) ? count($rows) : 1;
            continue;
        }
        $columns = backupTableColumns($table);
        foreach ($rows as $row) {
            if (!is_array($row)) {
                $result['skipped']++;
                continue;
            }
            if (isset($row['id']) && in_array('id', $columns, true)) {
                $stmt = $pdo->prepare("SELECT COUNT(*) FROM `$table` WHERE id = :id");
                $stmt->execute([':id' => (int)$row['id']]);
                if ((int)$stmt->fetchColumn() > 0) $result['changes']++;
                else $result['additions']++;
                continue;
            }
            if ($table === 'settings' && isset($row['setting_key'])) {
                $stmt = $pdo->prepare("SELECT COUNT(*) FROM settings WHERE setting_key = :setting_key");
                $stmt->execute([':setting_key' => (string)$row['setting_key']]);
                if ((int)$stmt->fetchColumn() > 0) $result['conflicts']++;
                else $result['additions']++;
                continue;
            }
            $result['additions']++;
        }
    }
    return $result;
}

function backupSaveUploadedImport($fieldName = 'import_file') {
    if (!isset($_FILES[$fieldName]) || $_FILES[$fieldName]['error'] !== UPLOAD_ERR_OK) {
        throw new RuntimeException('업로드된 JSON 파일이 없습니다.');
    }
    $original = (string)($_FILES[$fieldName]['name'] ?? '');
    if (!preg_match('/\.json$/i', $original)) throw new RuntimeException('JSON 파일만 가져올 수 있습니다.');
    $data = readBackupJson($_FILES[$fieldName]['tmp_name']);
    $inspect = inspectBackupData($data);
    if (!$inspect['valid']) throw new RuntimeException(implode(' ', $inspect['errors']));
    $fileName = 'music_import_' . date('Y-m-d_His') . '_' . substr(bin2hex(random_bytes(3)), 0, 6) . '.json';
    $path = ensureBackupStorage() . '/' . $fileName;
    $json = json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT);
    if ($json === false || file_put_contents($path, $json, LOCK_EX) === false) {
        throw new RuntimeException('가져오기 미리보기 파일을 저장할 수 없습니다.');
    }
    return [$fileName, $data, $inspect];
}

function backupImportAllowedSections() {
    return ['settings', 'tags', 'song_tags', 'playlists', 'playlist_songs', 'lyrics', 'track_notes', 'track_ratings'];
}

function backupSongExists($songId) {
    $stmt = getDBConnection()->prepare("SELECT COUNT(*) FROM songs WHERE id = :id");
    $stmt->execute([':id' => (int)$songId]);
    return ((int)$stmt->fetchColumn()) > 0;
}

function backupUpsertByUniqueSong($table, $row, $conflictMode) {
    if (!isset($row['song_id']) || !backupSongExists((int)$row['song_id'])) return 'skipped';
    $columns = array_values(array_intersect(array_keys($row), backupTableColumns($table)));
    $columns = array_values(array_filter($columns, function ($col) {
        return $col !== 'id' && preg_match('/^[A-Za-z0-9_]+$/', $col);
    }));
    if (empty($columns)) return 'skipped';
    $check = getDBConnection()->prepare("SELECT COUNT(*) FROM `$table` WHERE song_id = :song_id");
    $check->execute([':song_id' => (int)$row['song_id']]);
    $exists = ((int)$check->fetchColumn()) > 0;
    if ($exists && $conflictMode === 'skip') return 'conflict';

    $insertCols = '`' . implode('`,`', $columns) . '`';
    $placeholders = [];
    $updates = [];
    $params = [];
    foreach ($columns as $col) {
        $placeholders[] = ':' . $col;
        $params[':' . $col] = $row[$col];
        if ($col !== 'id' && $col !== 'song_id' && $col !== 'created_at') $updates[] = "`$col` = VALUES(`$col`)";
    }
    if (in_array('updated_at', backupTableColumns($table), true)) $updates[] = 'updated_at = CURRENT_TIMESTAMP';
    $sql = "INSERT INTO `$table` ($insertCols) VALUES (" . implode(',', $placeholders) . ")";
    if (!empty($updates)) $sql .= " ON DUPLICATE KEY UPDATE " . implode(', ', array_unique($updates));
    $stmt = getDBConnection()->prepare($sql);
    $stmt->execute($params);
    return $exists ? 'changed' : 'added';
}

function applySelectiveImport($file, $sections, $conflictMode = 'skip') {
    $path = backupFilePath($file);
    if (!$path || !is_file($path)) throw new RuntimeException('가져오기 파일을 찾을 수 없습니다.');
    $data = readBackupJson($path);
    $inspect = inspectBackupData($data);
    if (!$inspect['valid']) throw new RuntimeException(implode(' ', $inspect['errors']));
    $tables = backupDataTables($data);
    $requested = array_values(array_intersect((array)$sections, backupImportAllowedSections()));
    if (empty($requested)) throw new RuntimeException('가져올 항목을 선택하세요.');

    $pdo = getDBConnection();
    $result = ['added' => 0, 'changed' => 0, 'conflict' => 0, 'skipped' => 0];
    $playlistIdMap = [];
    $pdo->beginTransaction();
    try {
        foreach ($requested as $table) {
            if (empty($tables[$table]) || !backupTableExists($table)) continue;
            foreach ($tables[$table] as $row) {
                if (!is_array($row)) {
                    $result['skipped']++;
                    continue;
                }
                $status = 'skipped';
                if ($table === 'settings') {
                    if (!backupSettingRowIsExportable($row) || empty($row['setting_key'])) {
                        $status = 'skipped';
                    } else {
                        setSetting((string)$row['setting_key'], (string)($row['setting_value'] ?? ''), (string)($row['setting_group'] ?? 'general'));
                        $status = 'changed';
                    }
                } elseif ($table === 'tags') {
                    require_once __DIR__ . '/functions_tags.php';
                    $tagId = getOrCreateTag((string)($row['name'] ?? ''));
                    $status = $tagId > 0 ? 'added' : 'skipped';
                } elseif ($table === 'song_tags') {
                    if (isset($row['song_id'], $row['tag_id']) && backupSongExists((int)$row['song_id'])) {
                        $tagName = null;
                        foreach (($tables['tags'] ?? []) as $tagRow) {
                            if ((int)($tagRow['id'] ?? 0) === (int)$row['tag_id']) {
                                $tagName = (string)($tagRow['name'] ?? '');
                                break;
                            }
                        }
                        if ($tagName !== null && $tagName !== '') {
                            require_once __DIR__ . '/functions_tags.php';
                            addTagToSong((int)$row['song_id'], $tagName);
                            $status = 'added';
                        }
                    }
                } elseif (in_array($table, ['lyrics', 'track_notes', 'track_ratings'], true)) {
                    $status = backupUpsertByUniqueSong($table, $row, $conflictMode);
                } elseif ($table === 'playlists') {
                    $name = trim((string)($row['name'] ?? ''));
                    if ($name === '') {
                        $status = 'skipped';
                    } else {
                        $stmt = $pdo->prepare("SELECT id FROM playlists WHERE name = :name");
                        $stmt->execute([':name' => $name]);
                        $exists = (int)$stmt->fetchColumn();
                        if ($exists && $conflictMode === 'skip') {
                            if (isset($row['id'])) $playlistIdMap[(int)$row['id']] = $exists;
                            $status = 'conflict';
                        } elseif ($exists) {
                            $update = $pdo->prepare("UPDATE playlists SET description = :description, updated_at = CURRENT_TIMESTAMP WHERE id = :id");
                            $update->execute([':description' => $row['description'] ?? null, ':id' => $exists]);
                            if (isset($row['id'])) $playlistIdMap[(int)$row['id']] = $exists;
                            $status = 'changed';
                        } else {
                            $insert = $pdo->prepare("INSERT INTO playlists (name, description) VALUES (:name, :description)");
                            $insert->execute([':name' => $name, ':description' => $row['description'] ?? null]);
                            if (isset($row['id'])) $playlistIdMap[(int)$row['id']] = (int)$pdo->lastInsertId();
                            $status = 'added';
                        }
                    }
                } elseif ($table === 'playlist_songs') {
                    if (isset($row['playlist_id'], $row['song_id']) && backupSongExists((int)$row['song_id'])) {
                        $playlistId = $playlistIdMap[(int)$row['playlist_id']] ?? (int)$row['playlist_id'];
                        $check = $pdo->prepare("SELECT COUNT(*) FROM playlists WHERE id = :id");
                        $check->execute([':id' => $playlistId]);
                        if ((int)$check->fetchColumn() > 0) {
                            $stmt = $pdo->prepare("INSERT IGNORE INTO playlist_songs (playlist_id, song_id, sort_order) VALUES (:playlist_id, :song_id, :sort_order)");
                            $stmt->execute([':playlist_id' => $playlistId, ':song_id' => (int)$row['song_id'], ':sort_order' => (int)($row['sort_order'] ?? 0)]);
                            $status = $stmt->rowCount() > 0 ? 'added' : 'conflict';
                        }
                    }
                }
                if (!isset($result[$status])) $status = 'skipped';
                $result[$status]++;
            }
        }
        $pdo->commit();
        return $result;
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        throw $e;
    }
}
