-- =====================================================
-- MLM SYSTEM - COMPLETE DATABASE SCHEMA (MySQL)
-- =====================================================
-- Notes:
-- 1. Every table includes:
--    - status
--    - sub_status
--    - metadata (JSON for extensibility)
-- 2. ENUM values can be modified as needed
-- =====================================================

-- =========================
-- USERS
-- =========================
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150),
    email VARCHAR(150) UNIQUE,
    password VARCHAR(255),

    sponsor_id BIGINT NULL,

    status ENUM('active','inactive','blocked') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    roi_enabled BOOLEAN DEFAULT TRUE,
    is_working_id BOOLEAN DEFAULT FALSE,

    team_business_enabled BOOLEAN DEFAULT TRUE,
    team_business_levels INT NULL,

    direct_count INT DEFAULT 0,

    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,

    INDEX(sponsor_id)
);

-- =========================
-- USER RELATIONS
-- =========================
CREATE TABLE user_relations (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    ancestor_id BIGINT,
    level INT,

    status ENUM('active','inactive') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    UNIQUE KEY unique_relation (user_id, ancestor_id),
    INDEX(user_id),
    INDEX(ancestor_id)
);

-- =========================
-- DEPOSITS
-- =========================
CREATE TABLE deposits (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,

    amount DECIMAL(18,6),

    channel_type ENUM('web3','crypto_manual','other_manual','admin'),

    tx_hash VARCHAR(255) UNIQUE NULL,
    payment_reference VARCHAR(255) NULL,
    proof_path VARCHAR(255) NULL,
    remarks TEXT NULL,

    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    created_by BIGINT NULL,

    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,

    INDEX(user_id),
    INDEX(status)
);

-- =========================
-- TRANSACTIONS (LEDGER)
-- =========================
CREATE TABLE transactions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,

    wallet_type ENUM('deposit','withdrawal','transfer'),
    direction ENUM('credit','debit'),

    amount DECIMAL(18,6),

    source_type ENUM('deposit','admin','transfer','withdrawal','adjustment'),
    source_id BIGINT NULL,

    status ENUM('active','reversed') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    created_at TIMESTAMP NULL,

    INDEX(user_id),
    INDEX(source_type)
);

-- =========================
-- WITHDRAWALS
-- =========================
CREATE TABLE withdrawals (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,

    amount DECIMAL(18,6),
    fee DECIMAL(18,6),
    net_amount DECIMAL(18,6),

    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,

    INDEX(user_id),
    INDEX(status)
);

-- =========================
-- PACKAGES
-- =========================
CREATE TABLE packages (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),

    min_amount DECIMAL(18,6),
    max_amount DECIMAL(18,6),

    status ENUM('active','inactive') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

-- =========================
-- INVESTMENTS
-- =========================
CREATE TABLE investments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    package_id BIGINT,

    amount DECIMAL(18,6),

    start_date DATETIME,

    status ENUM('active','completed') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    created_at TIMESTAMP NULL,

    INDEX(user_id),
    INDEX(package_id)
);

-- =========================
-- ROI CONFIGS
-- =========================
CREATE TABLE roi_configs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    package_id BIGINT,
    daily_percentage DECIMAL(5,4),

    effective_from DATETIME,
    effective_to DATETIME NULL,

    status ENUM('active','inactive') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    created_at TIMESTAMP NULL,

    INDEX(package_id, effective_from)
);

-- =========================
-- LEVEL CONFIGS
-- =========================
CREATE TABLE level_configs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    level INT UNIQUE,
    percentage DECIMAL(5,2),

    status ENUM('active','inactive') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL
);

-- =========================
-- DIRECT INCOME
-- =========================
CREATE TABLE direct_incomes (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    from_user_id BIGINT,
    investment_id BIGINT,

    amount DECIMAL(18,6),

    status ENUM('active','reversed') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    created_at TIMESTAMP NULL,

    INDEX(user_id)
);

-- =========================
-- RANK CONFIGS
-- =========================
CREATE TABLE rank_configs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),

    target_business DECIMAL(18,6),
    reward DECIMAL(18,6),

    duration_days INT DEFAULT 100,

    status ENUM('active','inactive') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL
);

-- =========================
-- USER RANKS
-- =========================
CREATE TABLE user_ranks (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    rank_id BIGINT,

    achieved_at DATETIME,

    status ENUM('active','inactive') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    INDEX(user_id)
);

-- =========================
-- REWARD CONFIGS
-- =========================
CREATE TABLE reward_configs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    level_order INT UNIQUE,

    matching_required DECIMAL(18,6),
    reward_amount DECIMAL(18,6),

    status ENUM('active','inactive') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL
);

-- =========================
-- USER REWARDS
-- =========================
CREATE TABLE user_rewards (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    reward_config_id BIGINT,

    amount DECIMAL(18,6),
    achieved_at DATETIME,

    status ENUM('active','partial','capped') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    UNIQUE KEY unique_reward (user_id, reward_config_id),
    INDEX(user_id)
);

-- =========================
-- USER LEGS
-- =========================
CREATE TABLE user_legs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,

    leg_type ENUM('real','virtual'),

    ref_user_id BIGINT NULL,
    name VARCHAR(100) NULL,

    status ENUM('active','inactive') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    created_at TIMESTAMP NULL,

    INDEX(user_id)
);

-- =========================
-- USER LEG STATS
-- =========================
CREATE TABLE user_leg_stats (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    leg_id BIGINT,

    total_business DECIMAL(18,6) DEFAULT 0,

    status ENUM('active','inactive') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    UNIQUE KEY unique_leg (user_id, leg_id),
    INDEX(user_id)
);

-- =========================
-- VIRTUAL LEGS
-- =========================
CREATE TABLE virtual_legs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    leg_id BIGINT,

    amount DECIMAL(18,6),

    expires_at DATETIME NULL,
    created_by BIGINT,

    status ENUM('active','expired') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    created_at TIMESTAMP NULL,

    INDEX(user_id)
);

-- =========================
-- USER STATS
-- =========================
CREATE TABLE user_stats (
    user_id BIGINT PRIMARY KEY,

    total_investment DECIMAL(18,6) DEFAULT 0,
    total_withdrawn DECIMAL(18,6) DEFAULT 0,

    total_direct_income DECIMAL(18,6) DEFAULT 0,
    total_reward_income DECIMAL(18,6) DEFAULT 0,

    status ENUM('active','inactive') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL,

    updated_at TIMESTAMP NULL
);

-- =========================
-- ELIGIBILITY CONFIGS
-- =========================
CREATE TABLE eligibility_configs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    income_type ENUM('roi','level','direct','reward','rank'),

    condition_type VARCHAR(50),
    condition_value VARCHAR(50),

    status ENUM('active','inactive') DEFAULT 'active',
    sub_status VARCHAR(50) NULL,
    metadata JSON NULL
);

-- =====================================================
-- END OF SCHEMA
-- =====================================================