CREATE DATABASE IF NOT EXISTS smm_panel;
USE smm_panel;

-- USERS

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,

    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(120) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,

    role ENUM(
        'user',
        'support',
        'moderator',
        'admin'
    ) DEFAULT 'user',

    balance DECIMAL(12,2) DEFAULT 0.00,

    referral_code VARCHAR(50) NULL,
    referred_by INT NULL,

    api_key VARCHAR(255) NULL,

    status TINYINT(1) DEFAULT 1,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- SETTINGS

CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,

    site_name VARCHAR(255) DEFAULT 'SMM Panel',
    currency VARCHAR(20) DEFAULT 'INR',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO settings(site_name,currency)
VALUES ('SMM Panel','INR');

-- CATEGORIES

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100) NOT NULL,

    status TINYINT(1) DEFAULT 1,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PROVIDERS

CREATE TABLE providers (
    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100) NOT NULL,

    api_url VARCHAR(255) NOT NULL,

    api_key VARCHAR(255) NOT NULL,

    api_type VARCHAR(50) DEFAULT 'standard',

    status TINYINT(1) DEFAULT 1,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- SERVICES

CREATE TABLE services (
    id INT AUTO_INCREMENT PRIMARY KEY,

    category_id INT NULL,

    provider_id INT NULL,

    provider_service_id INT NULL,

    category_name VARCHAR(255) NULL,

    name VARCHAR(255) NOT NULL,

    description TEXT NULL,

    service_type ENUM(
        'default',
        'custom_comments',
        'custom_comments_package',
        'mentions',
        'poll'
    ) DEFAULT 'default',

    price DECIMAL(12,4) DEFAULT 0,

    rate DECIMAL(12,4) DEFAULT 0,

    base_rate DECIMAL(12,4) DEFAULT 0,

    min_quantity INT DEFAULT 1,
    max_quantity INT DEFAULT 100000,

    min_order INT DEFAULT 1,
    max_order INT DEFAULT 100000,

    dripfeed TINYINT(1) DEFAULT 0,
    refill TINYINT(1) DEFAULT 0,
    cancel_enabled TINYINT(1) DEFAULT 0,

    status TINYINT(1) DEFAULT 1,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (category_id)
    REFERENCES categories(id),

    FOREIGN KEY (provider_id)
    REFERENCES providers(id)
);

-- ORDERS

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,

    panel_id INT NULL,

    service_id INT NOT NULL,

    provider_order_id VARCHAR(100) NULL,

    link TEXT NOT NULL,

    quantity INT NOT NULL,

    charge DECIMAL(12,4) NOT NULL,

    status ENUM(
        'Pending',
        'Processing',
        'Completed',
        'Partial',
        'Cancelled'
    ) DEFAULT 'Pending',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)
    REFERENCES users(id),

    FOREIGN KEY (service_id)
    REFERENCES services(id)
);

-- TRANSACTIONS

CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,

    amount DECIMAL(12,2) NOT NULL,

    payment_method VARCHAR(50) NOT NULL,

    transaction_id VARCHAR(255) NULL,

    status ENUM(
        'Pending',
        'Approved',
        'Rejected'
    ) DEFAULT 'Pending',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)
    REFERENCES users(id)
);

-- PAYMENT METHODS

CREATE TABLE payment_methods (
    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100) NOT NULL,

    type VARCHAR(50) NOT NULL,

    config TEXT NULL,

    status TINYINT(1) DEFAULT 1,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- TICKETS

CREATE TABLE tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,

    subject VARCHAR(255) NOT NULL,

    status ENUM(
        'Open',
        'Answered',
        'Closed'
    ) DEFAULT 'Open',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)
    REFERENCES users(id)
);

CREATE TABLE ticket_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,

    ticket_id INT NOT NULL,

    sender ENUM(
        'user',
        'admin'
    ) NOT NULL,

    message TEXT NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (ticket_id)
    REFERENCES tickets(id)
);

-- REFERRALS

CREATE TABLE referral_earnings (
    id INT AUTO_INCREMENT PRIMARY KEY,

    referrer_id INT NOT NULL,

    referred_user_id INT NOT NULL,

    order_id INT NULL,

    amount DECIMAL(12,2) NOT NULL,

    status ENUM(
        'Pending',
        'Approved'
    ) DEFAULT 'Pending',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- COUPONS

CREATE TABLE coupons (
    id INT AUTO_INCREMENT PRIMARY KEY,

    code VARCHAR(50) UNIQUE NOT NULL,

    discount_type ENUM(
        'percentage',
        'fixed'
    ) NOT NULL,

    discount_value DECIMAL(12,2) NOT NULL,

    usage_limit INT DEFAULT 0,

    used_count INT DEFAULT 0,

    expires_at DATETIME NULL,

    status TINYINT(1) DEFAULT 1,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ANNOUNCEMENTS

CREATE TABLE announcements (
    id INT AUTO_INCREMENT PRIMARY KEY,

    title VARCHAR(255) NOT NULL,

    message TEXT NOT NULL,

    type ENUM(
        'info',
        'success',
        'warning',
        'danger'
    ) DEFAULT 'info',

    active TINYINT(1) DEFAULT 1,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- SYSTEM SETTINGS

CREATE TABLE system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,

    maintenance_mode TINYINT(1) DEFAULT 0,

    maintenance_message TEXT NULL,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO system_settings(
    maintenance_mode,
    maintenance_message
)
VALUES(
    0,
    'System maintenance in progress.'
);

-- ADMIN LOGS

CREATE TABLE admin_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,

    admin_id INT NOT NULL,

    action VARCHAR(255) NOT NULL,

    details TEXT NULL,

    ip_address VARCHAR(50) NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- LOGIN HISTORY

CREATE TABLE login_history (
    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,

    ip_address VARCHAR(50),

    user_agent TEXT,

    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- CHILD PANELS

CREATE TABLE child_panels (
    id INT AUTO_INCREMENT PRIMARY KEY,

    owner_id INT NOT NULL,

    panel_name VARCHAR(255) NOT NULL,

    domain VARCHAR(255) NOT NULL,

    profit_percentage DECIMAL(5,2) DEFAULT 10.00,

    status ENUM(
        'Active',
        'Suspended'
    ) DEFAULT 'Active',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PANEL SETTINGS

CREATE TABLE panel_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,

    panel_id INT NOT NULL,

    logo VARCHAR(255) NULL,

    favicon VARCHAR(255) NULL,

    primary_color VARCHAR(20) DEFAULT '#0d6efd',

    site_title VARCHAR(255) DEFAULT 'SMM Panel',

    footer_text TEXT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- DOMAINS

CREATE TABLE domains (
    id INT AUTO_INCREMENT PRIMARY KEY,

    panel_id INT NOT NULL,

    domain VARCHAR(255) UNIQUE NOT NULL,

    verified TINYINT(1) DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,

    title VARCHAR(255) NOT NULL,

    message TEXT NOT NULL,

    is_read TINYINT(1) DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)
    REFERENCES users(id)
);
CREATE TABLE smtp_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,

    host VARCHAR(255) NOT NULL,

    port INT NOT NULL,

    username VARCHAR(255) NOT NULL,

    password VARCHAR(255) NOT NULL,

    encryption ENUM(
        'tls',
        'ssl',
        'none'
    ) DEFAULT 'tls',

    from_email VARCHAR(255) NOT NULL,

    from_name VARCHAR(255) NOT NULL,

    status TINYINT(1) DEFAULT 1
);
CREATE TABLE password_resets (
    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,

    token VARCHAR(255) NOT NULL,

    expires_at DATETIME NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);