<?php
/**
 * 데이터베이스 연결 및 초기 스키마 생성 처리
 */

require_once dirname(__DIR__) . '/config/config.php';

try {
    // 1. 우선 DB 서버에 연결 (DB가 없는 상황을 대비하여 데이터베이스 선택 없이 접속)
    $dsnWithoutDb = sprintf("mysql:host=%s;charset=utf8mb4", DB_HOST);
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
    ];
    $pdo = new PDO($dsnWithoutDb, DB_USER, DB_PASS, $options);

    // 2. 데이터베이스 자동 생성
    $dbName = DB_NAME;
    $pdo->exec("CREATE DATABASE IF NOT EXISTS `$dbName` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");

    // 3. 데이터베이스 선택하여 재접속
    $dsnWithDb = sprintf("mysql:host=%s;dbname=%s;charset=utf8mb4", DB_HOST, DB_NAME);
    $pdo = new PDO($dsnWithDb, DB_USER, DB_PASS, $options);
    $pdo->exec("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");

    // 4. songs 테이블 생성 (is_missing 컬럼 추가하여 유실 파일 처리)
    $createSongsTable = "
        CREATE TABLE IF NOT EXISTS `songs` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `title` VARCHAR(255) NOT NULL,
            `artist` VARCHAR(255) DEFAULT NULL,
            `album` VARCHAR(255) DEFAULT NULL,
            `genre` VARCHAR(100) DEFAULT NULL,
            `track_no` INT DEFAULT NULL,
            `duration` INT DEFAULT NULL,
            `file_path` TEXT NOT NULL,
            `file_hash` VARCHAR(64) NOT NULL,
            `file_size` BIGINT DEFAULT 0,
            `file_mtime` INT DEFAULT 0,
            `extension` VARCHAR(20) DEFAULT NULL,
            `play_count` INT DEFAULT 0,
            `is_missing` TINYINT DEFAULT 0,
            `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY `uniq_file_hash` (`file_hash`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    $pdo->exec($createSongsTable);

    // songs 테이블에 3차 추가 컬럼 (year, cover_hash) 존재 유무 확인 후 동적 추가
    $checkYearCol = $pdo->query("SHOW COLUMNS FROM `songs` LIKE 'year'")->fetch();
    if (!$checkYearCol) {
        $pdo->exec("ALTER TABLE `songs` ADD COLUMN `year` VARCHAR(10) DEFAULT NULL AFTER `extension`");
    }
    $checkCoverCol = $pdo->query("SHOW COLUMNS FROM `songs` LIKE 'cover_hash'")->fetch();
    if (!$checkCoverCol) {
        $pdo->exec("ALTER TABLE `songs` ADD COLUMN `cover_hash` VARCHAR(64) DEFAULT NULL AFTER `year`");
    }

    $songExtraColumns = [
        'album_artist' => "ALTER TABLE `songs` ADD COLUMN `album_artist` VARCHAR(255) DEFAULT NULL AFTER `album`",
        'disc_no' => "ALTER TABLE `songs` ADD COLUMN `disc_no` INT DEFAULT NULL AFTER `track_no`",
        'composer' => "ALTER TABLE `songs` ADD COLUMN `composer` VARCHAR(255) DEFAULT NULL AFTER `genre`",
        'comment' => "ALTER TABLE `songs` ADD COLUMN `comment` TEXT DEFAULT NULL AFTER `composer`",
        'bitrate' => "ALTER TABLE `songs` ADD COLUMN `bitrate` INT DEFAULT NULL AFTER `duration`",
        'sample_rate' => "ALTER TABLE `songs` ADD COLUMN `sample_rate` INT DEFAULT NULL AFTER `bitrate`",
        'channels' => "ALTER TABLE `songs` ADD COLUMN `channels` VARCHAR(50) DEFAULT NULL AFTER `sample_rate`",
        'metadata_source' => "ALTER TABLE `songs` ADD COLUMN `metadata_source` VARCHAR(50) DEFAULT NULL AFTER `cover_hash`",
        'metadata_updated_at' => "ALTER TABLE `songs` ADD COLUMN `metadata_updated_at` DATETIME DEFAULT NULL AFTER `metadata_source`",
        'metadata_error' => "ALTER TABLE `songs` ADD COLUMN `metadata_error` TEXT DEFAULT NULL AFTER `metadata_updated_at`",
        'is_manual_edited' => "ALTER TABLE `songs` ADD COLUMN `is_manual_edited` TINYINT(1) DEFAULT 0 AFTER `metadata_error`",
        'cover_cache_path' => "ALTER TABLE `songs` ADD COLUMN `cover_cache_path` VARCHAR(500) DEFAULT NULL AFTER `is_manual_edited`",
        'has_cover' => "ALTER TABLE `songs` ADD COLUMN `has_cover` TINYINT(1) DEFAULT 0 AFTER `cover_cache_path`",
        'cover_mime' => "ALTER TABLE `songs` ADD COLUMN `cover_mime` VARCHAR(100) DEFAULT NULL AFTER `has_cover`",
        'last_metadata_scan_at' => "ALTER TABLE `songs` ADD COLUMN `last_metadata_scan_at` DATETIME DEFAULT NULL AFTER `metadata_updated_at`",
        'folder_path' => "ALTER TABLE `songs` ADD COLUMN `folder_path` TEXT DEFAULT NULL AFTER `file_path`",
        'folder_name' => "ALTER TABLE `songs` ADD COLUMN `folder_name` VARCHAR(255) DEFAULT NULL AFTER `folder_path`",
        'relative_path' => "ALTER TABLE `songs` ADD COLUMN `relative_path` TEXT DEFAULT NULL AFTER `folder_name`",
        'is_hidden' => "ALTER TABLE `songs` ADD COLUMN `is_hidden` TINYINT(1) DEFAULT 0 AFTER `is_missing`",
        'missing_checked_at' => "ALTER TABLE `songs` ADD COLUMN `missing_checked_at` DATETIME DEFAULT NULL AFTER `is_hidden`"
    ];
    foreach ($songExtraColumns as $column => $sql) {
        $exists = $pdo->query("SHOW COLUMNS FROM `songs` LIKE " . $pdo->quote($column))->fetch();
        if (!$exists) {
            $pdo->exec($sql);
        }
    }

    // 5. scan_logs 테이블 생성
    $createScanLogsTable = "
        CREATE TABLE IF NOT EXISTS `scan_logs` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `scan_started_at` DATETIME NOT NULL,
            `scan_finished_at` DATETIME DEFAULT NULL,
            `total_found` INT DEFAULT 0,
            `new_count` INT DEFAULT 0,
            `updated_count` INT DEFAULT 0,
            `deleted_count` INT DEFAULT 0,
            `message` TEXT DEFAULT NULL,
            `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    $pdo->exec($createScanLogsTable);
    $scanLogExtraColumns = [
        'skipped_count' => "ALTER TABLE `scan_logs` ADD COLUMN `skipped_count` INT DEFAULT 0 AFTER `deleted_count`",
        'tag_count' => "ALTER TABLE `scan_logs` ADD COLUMN `tag_count` INT DEFAULT 0 AFTER `skipped_count`",
        'filename_count' => "ALTER TABLE `scan_logs` ADD COLUMN `filename_count` INT DEFAULT 0 AFTER `tag_count`",
        'manual_protected_count' => "ALTER TABLE `scan_logs` ADD COLUMN `manual_protected_count` INT DEFAULT 0 AFTER `filename_count`",
        'cover_success_count' => "ALTER TABLE `scan_logs` ADD COLUMN `cover_success_count` INT DEFAULT 0 AFTER `manual_protected_count`",
        'cover_missing_count' => "ALTER TABLE `scan_logs` ADD COLUMN `cover_missing_count` INT DEFAULT 0 AFTER `cover_success_count`",
        'metadata_error_count' => "ALTER TABLE `scan_logs` ADD COLUMN `metadata_error_count` INT DEFAULT 0 AFTER `cover_missing_count`",
        'missing_count' => "ALTER TABLE `scan_logs` ADD COLUMN `missing_count` INT DEFAULT 0 AFTER `metadata_error_count`"
    ];
    foreach ($scanLogExtraColumns as $column => $sql) {
        $exists = $pdo->query("SHOW COLUMNS FROM `scan_logs` LIKE " . $pdo->quote($column))->fetch();
        if (!$exists) {
            $pdo->exec($sql);
        }
    }

    // 6. liked_songs 테이블 생성
    $createLikedSongsTable = "
        CREATE TABLE IF NOT EXISTS `liked_songs` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `song_id` INT NOT NULL,
            `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY `uniq_song_id` (`song_id`),
            INDEX `idx_song_id` (`song_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    $pdo->exec($createLikedSongsTable);

    // 7. play_history 테이블 생성
    $createPlayHistoryTable = "
        CREATE TABLE IF NOT EXISTS `play_history` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `song_id` INT NOT NULL,
            `played_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_song_id` (`song_id`),
            INDEX `idx_played_at` (`played_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    $pdo->exec($createPlayHistoryTable);

    // 8. playlists 테이블 생성
    $createPlaylistsTable = "
        CREATE TABLE IF NOT EXISTS `playlists` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `name` VARCHAR(255) NOT NULL,
            `description` TEXT DEFAULT NULL,
            `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    $pdo->exec($createPlaylistsTable);

    // 9. playlist_songs 테이블 생성
    $createPlaylistSongsTable = "
        CREATE TABLE IF NOT EXISTS `playlist_songs` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `playlist_id` INT NOT NULL,
            `song_id` INT NOT NULL,
            `sort_order` INT DEFAULT 0,
            `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY `uniq_playlist_song` (`playlist_id`, `song_id`),
            INDEX `idx_playlist_id` (`playlist_id`),
            INDEX `idx_song_id` (`song_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    $pdo->exec($createPlaylistSongsTable);

    // 10. lyrics 테이블 생성
    $createLyricsTable = "
        CREATE TABLE IF NOT EXISTS `lyrics` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `song_id` INT NOT NULL,
            `lyrics_text` LONGTEXT DEFAULT NULL,
            `source` VARCHAR(100) DEFAULT 'manual',
            `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`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    $pdo->exec($createLyricsTable);

    $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;
    ");

    $createSettingsTable = "
        CREATE TABLE IF NOT EXISTS `settings` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `setting_key` VARCHAR(100) NOT NULL,
            `setting_value` TEXT DEFAULT NULL,
            `setting_group` VARCHAR(50) DEFAULT 'general',
            `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY `uniq_setting_key` (`setting_key`),
            INDEX `idx_setting_group` (`setting_group`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    $pdo->exec($createSettingsTable);

    $createMaintenanceLogsTable = "
        CREATE TABLE IF NOT EXISTS `maintenance_logs` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `action_type` VARCHAR(100) NOT NULL,
            `message` TEXT DEFAULT NULL,
            `status` VARCHAR(20) DEFAULT 'success',
            `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_action_type` (`action_type`),
            INDEX `idx_created_at` (`created_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    $pdo->exec($createMaintenanceLogsTable);

    $createAdminUsersTable = "
        CREATE TABLE IF NOT EXISTS `admin_users` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `username` VARCHAR(100) NOT NULL,
            `password_hash` VARCHAR(255) NOT NULL,
            `display_name` VARCHAR(100) DEFAULT NULL,
            `role` VARCHAR(50) DEFAULT 'admin',
            `is_active` TINYINT(1) DEFAULT 1,
            `last_login_at` DATETIME DEFAULT NULL,
            `login_fail_count` INT DEFAULT 0,
            `locked_until` DATETIME DEFAULT NULL,
            `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY `uniq_username` (`username`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    $pdo->exec($createAdminUsersTable);

    $createAdminLogsTable = "
        CREATE TABLE IF NOT EXISTS `admin_logs` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `admin_user_id` INT DEFAULT NULL,
            `action_type` VARCHAR(100) NOT NULL,
            `message` TEXT DEFAULT NULL,
            `ip_address` VARCHAR(100) DEFAULT NULL,
            `user_agent` TEXT DEFAULT NULL,
            `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_action_type` (`action_type`),
            INDEX `idx_created_at` (`created_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    $pdo->exec($createAdminLogsTable);

    $createAppLogsTable = "
        CREATE TABLE IF NOT EXISTS `app_logs` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `log_level` VARCHAR(20) NOT NULL DEFAULT 'info',
            `log_type` VARCHAR(100) NOT NULL,
            `message` TEXT DEFAULT NULL,
            `context` TEXT DEFAULT NULL,
            `file_path` TEXT DEFAULT NULL,
            `user_id` INT DEFAULT NULL,
            `ip_address` VARCHAR(100) DEFAULT NULL,
            `user_agent` TEXT DEFAULT NULL,
            `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_log_level` (`log_level`),
            INDEX `idx_log_type` (`log_type`),
            INDEX `idx_created_at` (`created_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    $pdo->exec($createAppLogsTable);

    $addIndexIfMissing = function ($table, $indexName, $columnsSql) use ($pdo) {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.statistics
            WHERE table_schema = DATABASE()
              AND table_name = :table_name
              AND index_name = :index_name
        ");
        $stmt->execute([
            ':table_name' => $table,
            ':index_name' => $indexName,
        ]);
        if ((int)$stmt->fetchColumn() === 0) {
            $pdo->exec("ALTER TABLE `$table` ADD INDEX `$indexName` ($columnsSql)");
        }
    };

    $searchIndexes = [
        ['songs', 'idx_search_title', '`title`(191)'],
        ['songs', 'idx_search_artist', '`artist`(191)'],
        ['songs', 'idx_search_album', '`album`(191)'],
        ['songs', 'idx_search_genre', '`genre`'],
        ['songs', 'idx_search_created_at', '`created_at`'],
        ['songs', 'idx_search_updated_at', '`updated_at`'],
        ['songs', 'idx_search_play_count', '`play_count`'],
        ['playlists', 'idx_search_name', '`name`(191)'],
    ];
    foreach ($searchIndexes as $index) {
        try {
            $addIndexIfMissing($index[0], $index[1], $index[2]);
        } catch (Throwable $ignored) {
        }
    }

} catch (PDOException $e) {
    // 디버그 용이성을 위해 예외 메시지를 그대로 출력하되, 보안상 민감할 수 있으므로 
    // 실무 배포 시에는 적절한 에러 뷰를 처리하는 것이 좋으나, 1차 구축 목적에 맞게 친절한 에러를 보여줌
    die("데이터베이스 연결 및 초기화에 실패했습니다: " . htmlspecialchars($e->getMessage()));
}

// 전역에서 사용할 PDO 인스턴스 반환 함수
function getDBConnection() {
    global $pdo;
    return $pdo;
}
