-- Disable foreign key checks to prevent issues during creation
SET FOREIGN_KEY_CHECKS = 0;

-- Drop tables if they exist to ensure a clean slate
DROP TABLE IF EXISTS `apps`;
DROP TABLE IF EXISTS `consoles`;
DROP TABLE IF EXISTS `email_accounts`;
DROP TABLE IF EXISTS `browsers`;
DROP TABLE IF EXISTS `rdps`;
DROP TABLE IF EXISTS `admins`;

-- Create admins table
CREATE TABLE `admins` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) Not NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create rdps table
CREATE TABLE `rdps` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `ip_address` VARCHAR(255) NOT NULL,
    `username` VARCHAR(255) NOT NULL,
    `password` TEXT NOT NULL, -- Storing as plain text per requirements
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create browsers table
CREATE TABLE `browsers` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `rdp_id` INT NOT NULL,
    `browser_name` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`rdp_id`) REFERENCES `rdps`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create email_accounts table
CREATE TABLE `email_accounts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `browser_id` INT NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `email_password` TEXT NOT NULL, -- Storing as plain text per requirements
    `backup_codes` TEXT, -- Can be plain text or JSON
    `phone_number` VARCHAR(50),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`browser_id`) REFERENCES `browsers`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create consoles table
CREATE TABLE `consoles` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `email_id` INT NOT NULL UNIQUE, -- One console per email account
    `status` ENUM('review', 'ready', 'sold', 'terminated') DEFAULT 'review',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`email_id`) REFERENCES `email_accounts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create apps table
CREATE TABLE `apps` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `console_id` INT NOT NULL,
    `app_name` VARCHAR(255) NOT NULL,
    `app_link` VARCHAR(255),
    `app_code_zip_path` VARCHAR(255),
    `jks_file_path` VARCHAR(255),
    `jks_alias` VARCHAR(255),
    `jks_password` TEXT, -- Storing as plain text per requirements
    `status` ENUM('uploaded', 'in_review', 'published', 'rejected') DEFAULT 'uploaded',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`console_id`) REFERENCES `consoles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;