CREATE DATABASE IF NOT EXISTS cloudsystem;
USE cloudsystem;

CREATE TABLE IF NOT EXISTS templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    type ENUM('LOBBY', 'GAME', 'PROXY') NOT NULL,
    jar_file VARCHAR(255) NOT NULL DEFAULT 'server.jar',
    memory_mb INT NOT NULL DEFAULT 512,
    max_players INT NOT NULL DEFAULT 100,
    auto_scale BOOLEAN DEFAULT FALSE,
    min_servers INT DEFAULT 1,
    max_servers INT DEFAULT 5,
    jvm_flags VARCHAR(500) DEFAULT '',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS servers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    template_id INT NULL,
    type ENUM('LOBBY', 'GAME', 'PROXY') NOT NULL,
    status ENUM('STARTING', 'LOBBY', 'INGAME', 'RESTARTING', 'OFFLINE') DEFAULT 'OFFLINE',
    port INT NOT NULL,
    player_count INT DEFAULT 0,
    max_players INT DEFAULT 100,
    host VARCHAR(255) DEFAULT 'localhost',
    pid INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES templates(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS players (
    uuid VARCHAR(36) PRIMARY KEY,
    name VARCHAR(16) NOT NULL,
    coins INT DEFAULT 0,
    current_server VARCHAR(100) NULL,
    online BOOLEAN DEFAULT FALSE,
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS friends (
    id INT AUTO_INCREMENT PRIMARY KEY,
    player1_uuid VARCHAR(36) NOT NULL,
    player2_uuid VARCHAR(36) NOT NULL,
    status ENUM('PENDING', 'ACCEPTED') DEFAULT 'PENDING',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_friend_pair (player1_uuid, player2_uuid),
    FOREIGN KEY (player1_uuid) REFERENCES players(uuid) ON DELETE CASCADE,
    FOREIGN KEY (player2_uuid) REFERENCES players(uuid) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS shop_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL DEFAULT 'Misc',
    description TEXT NULL,
    price INT NOT NULL DEFAULT 0,
    permission VARCHAR(255) NULL,
    icon_material VARCHAR(50) NOT NULL DEFAULT 'PAPER',
    sort_order INT DEFAULT 0,
    active BOOLEAN DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS sign_locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    world VARCHAR(100) NOT NULL DEFAULT 'world',
    x INT NOT NULL,
    y INT NOT NULL,
    z INT NOT NULL,
    server_id INT NULL,
    FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE SET NULL
);

-- Example templates
INSERT IGNORE INTO templates (name, type, jar_file, memory_mb, max_players, auto_scale, min_servers, max_servers)
VALUES
    ('Lobby-Template', 'LOBBY', 'paper.jar', 512, 100, TRUE, 1, 3),
    ('SkyWars-Template', 'GAME', 'paper.jar', 512, 16, FALSE, 0, 10),
    ('BedWars-Template', 'GAME', 'paper.jar', 512, 16, FALSE, 0, 10),
    ('BungeeCord-Template', 'PROXY', 'BungeeCord.jar', 256, 1000, FALSE, 1, 1);
