-- ==========================================
-- USERS
-- ==========================================

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL UNIQUE,
    username VARCHAR(255) NULL,
    first_name VARCHAR(255) NULL,
    last_name VARCHAR(255) NULL,
    user_state VARCHAR(100) NOT NULL DEFAULT 'none',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);


-- ==========================================
-- LOCATIONS
-- ==========================================

CREATE TABLE locations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    flag VARCHAR(20) NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- ==========================================
-- PLANS
-- ==========================================

CREATE TABLE plans (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    cpu INT NOT NULL,
    ram INT NOT NULL,
    disk INT NOT NULL,
    location_id INT UNSIGNED NOT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (location_id)
        REFERENCES locations(id)
        ON DELETE CASCADE
);


-- ==========================================
-- PLAN PRICES
-- ==========================================

CREATE TABLE plan_prices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plan_id INT UNSIGNED NOT NULL,
    duration_type ENUM('daily', 'weekly', 'monthly') NOT NULL,
    price BIGINT UNSIGNED NOT NULL,

    UNIQUE KEY unique_plan_duration (
        plan_id,
        duration_type
    ),

    FOREIGN KEY (plan_id)
        REFERENCES plans(id)
        ON DELETE CASCADE
);


-- ==========================================
-- OPERATING SYSTEMS
-- ==========================================

CREATE TABLE operating_systems (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- ==========================================
-- ORDERS
-- ==========================================

CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id INT UNSIGNED NOT NULL,

    order_type VARCHAR(30) NOT NULL,

    plan_id INT UNSIGNED NULL,
    location_id INT UNSIGNED NULL,
    operating_system_id INT UNSIGNED NULL,

    duration_type VARCHAR(20) NULL,

    amount BIGINT UNSIGNED NOT NULL,

    server_ip VARCHAR(45) NULL,
    root_password VARCHAR(255) NULL,

    status VARCHAR(50) NOT NULL DEFAULT 'pending',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE,

    FOREIGN KEY (plan_id)
        REFERENCES plans(id)
        ON DELETE SET NULL,

    FOREIGN KEY (location_id)
        REFERENCES locations(id)
        ON DELETE SET NULL,

    FOREIGN KEY (operating_system_id)
        REFERENCES operating_systems(id)
        ON DELETE SET NULL
);


-- ==========================================
-- PAYMENTS
-- ==========================================

CREATE TABLE payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    order_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,

    file_id VARCHAR(255) NOT NULL,

    status ENUM(
        'pending',
        'approved',
        'rejected'
    ) NOT NULL DEFAULT 'pending',

    admin_id BIGINT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (order_id)
        REFERENCES orders(id)
        ON DELETE CASCADE,

    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
);


-- ==========================================
-- SERVERS
-- ==========================================

CREATE TABLE servers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id INT UNSIGNED NOT NULL,

    order_id INT UNSIGNED NULL,

    plan_id INT UNSIGNED NULL,
    location_id INT UNSIGNED NULL,
    operating_system_id INT UNSIGNED NULL,

    ip_address VARCHAR(45) NOT NULL,

    username VARCHAR(100) NOT NULL DEFAULT 'root',

    password TEXT NOT NULL,

    started_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,

    status ENUM(
        'active',
        'expired',
        'suspended'
    ) NOT NULL DEFAULT 'active',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE,

    FOREIGN KEY (order_id)
        REFERENCES orders(id)
        ON DELETE SET NULL,

    FOREIGN KEY (plan_id)
        REFERENCES plans(id)
        ON DELETE SET NULL,

    FOREIGN KEY (location_id)
        REFERENCES locations(id)
        ON DELETE SET NULL,

    FOREIGN KEY (operating_system_id)
        REFERENCES operating_systems(id)
        ON DELETE SET NULL
);


-- ==========================================
-- SETTINGS
-- ==========================================

CREATE TABLE settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    setting_key VARCHAR(100) NOT NULL UNIQUE,

    setting_value TEXT NULL,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);


-- ==========================================
-- DEFAULT SETTINGS
-- ==========================================

INSERT INTO settings (
    setting_key,
    setting_value
) VALUES
(
    'card_number',
    '6037XXXXXXXXXXXX'
),
(
    'card_owner',
    'نام صاحب کارت'
),
(
    'support_username',
    '@YourSupport'
);


-- ==========================================
-- DEFAULT LOCATION
-- ==========================================

INSERT INTO locations (
    name,
    flag,
    status,
    sort_order
) VALUES (
    'Germany',
    '🇩🇪',
    1,
    1
);


-- ==========================================
-- DEFAULT OPERATING SYSTEMS
-- ==========================================

INSERT INTO operating_systems (
    name,
    status,
    sort_order
) VALUES
(
    'Ubuntu 24.04',
    1,
    1
),
(
    'Ubuntu 22.04',
    1,
    2
),
(
    'Debian 13',
    1,
    3
),
(
    'Debian 12',
    1,
    4
),
(
    'Windows Server 2025',
    1,
    5
),
(
    'Windows Server 2022',
    1,
    6
),
(
    'Windows Server 2019',
    1,
    7
),
(
    'AlmaLinux 9',
    1,
    8
),
(
    'Rocky Linux 9',
    1,
    9
);


-- ==========================================
-- USER PURCHASE SESSIONS
-- ==========================================

CREATE TABLE purchase_sessions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id INT UNSIGNED NOT NULL,

    location_id INT UNSIGNED NULL,
    plan_id INT UNSIGNED NULL,

    duration_type ENUM(
        'daily',
        'weekly',
        'monthly'
    ) NULL,

    operating_system_id INT UNSIGNED NULL,

    current_step VARCHAR(50) NOT NULL DEFAULT 'start',

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY unique_user_session (
        user_id
    ),

    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE,

    FOREIGN KEY (location_id)
        REFERENCES locations(id)
        ON DELETE SET NULL,

    FOREIGN KEY (plan_id)
        REFERENCES plans(id)
        ON DELETE SET NULL,

    FOREIGN KEY (operating_system_id)
        REFERENCES operating_systems(id)
        ON DELETE SET NULL
);