CREATE TABLE IF NOT EXISTS `developers` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(255) NOT NULL,
    `companyname` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `avatar` VARCHAR(255) DEFAULT NULL,
    `roles` VARCHAR(255) NOT NULL DEFAULT 'developer',
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `users` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `email` VARCHAR(200) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `avatar` VARCHAR(255) DEFAULT NULL,
    `library` JSON DEFAULT NULL,
    `verification_code` VARCHAR(255) DEFAULT NULL,
    `last_login_ip` VARCHAR(45) DEFAULT NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `games` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `developer_id` INT NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `game_avatar` VARCHAR(255) DEFAULT NULL,
    `game_banner` VARCHAR(255) DEFAULT NULL,
    `executable_file` VARCHAR(255) NOT NULL,
    `isEditorChoice` TINYINT(1) NOT NULL DEFAULT 0,
    `version` VARCHAR(50) NOT NULL,
    `upload_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `rating` FLOAT DEFAULT NULL,
    `tags` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`developer_id`) REFERENCES `developers`(`id`)
);

CREATE TABLE IF NOT EXISTS `game_of_the_day` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `game_id` INT NOT NULL,
    `end_date` DATE NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`game_id`) REFERENCES `games`(`id`) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `game_screenshots` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `game_id` INT NOT NULL,
    `screenshot_url` VARCHAR(255) NOT NULL,
    `orientation` ENUM('vertical', 'horizontal') NOT NULL,
    `upload_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`game_id`) REFERENCES `games`(`id`)
);

CREATE TABLE IF NOT EXISTS `news` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `image` VARCHAR(255) DEFAULT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT NOT NULL,
    `link` VARCHAR(255) DEFAULT NULL,
    `developer_id` INT NOT NULL,
    `date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`developer_id`) REFERENCES `developers`(`id`)
);

CREATE TABLE IF NOT EXISTS `app_updates` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `version` VARCHAR(10) NOT NULL,
    `installer_url` VARCHAR(255) NOT NULL,
    `upload_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `release_notes` TEXT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS `reviews` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `game_id` INT NOT NULL,
    `rating` INT NOT NULL,
    `review` TEXT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `username` VARCHAR(255) NOT NULL,
    FOREIGN KEY (`game_id`) REFERENCES `games`(`id`)
);

CREATE TABLE IF NOT EXISTS `maintenance` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `is_active` BOOLEAN DEFAULT FALSE,
    `end_time` DATETIME,
    `last_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);