USE klkapp_db;

-- 1. Diamond Packages
CREATE TABLE IF NOT EXISTS diamond_packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    diamonds INT NOT NULL,
    bonus INT DEFAULT 0,
    price DECIMAL(10, 2) NOT NULL,
    tag ENUM('none', 'popular', 'best_value') DEFAULT 'none',
    image_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Wallet Transactions (Ensure it supports all types)
CREATE TABLE IF NOT EXISTS wallet_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('purchase', 'send', 'receive', 'gift', 'withdraw', 'deposit', 'bonus') NOT NULL,
    amount INT NOT NULL, /* Diamonds */
    money_amount DECIMAL(10, 2) DEFAULT 0.00, /* Real money for withdrawals/purchases */
    related_user_id INT DEFAULT NULL,
    status ENUM('pending', 'completed', 'failed', 'rejected') DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES i_users(iuid) ON DELETE CASCADE
);

-- 3. User Payment Methods
CREATE TABLE IF NOT EXISTS user_payment_methods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('bank_transfer', 'paypal', 'crypto', 'binance') NOT NULL,
    details JSON NOT NULL, -- Stores account number, email, wallet address, etc.
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES i_users(iuid) ON DELETE CASCADE
);

-- 4. Withdrawals
CREATE TABLE IF NOT EXISTS withdrawals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount_diamonds INT NOT NULL,
    amount_money DECIMAL(10, 2) NOT NULL,
    payment_method_id INT NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    admin_note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES i_users(iuid) ON DELETE CASCADE,
    FOREIGN KEY (payment_method_id) REFERENCES user_payment_methods(id)
);
