-- Create categories table
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE
);

-- Add categories if not exist
INSERT IGNORE INTO categories (name, slug) VALUES ('Games', 'games'), ('Apps', 'apps'), ('Tools', 'tools');

-- Update apks table
ALTER TABLE apks ADD COLUMN icon_path VARCHAR(255) DEFAULT NULL;
ALTER TABLE apks ADD COLUMN display_order INT DEFAULT 0;

-- Update users table
ALTER TABLE users ADD COLUMN balance DECIMAL(15, 2) DEFAULT 0.00;

-- Create withdrawals table
CREATE TABLE IF NOT EXISTS withdrawals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    method VARCHAR(50) NOT NULL,
    account_details TEXT NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Create referral_downloads table to track earnings per download
CREATE TABLE IF NOT EXISTS referral_downloads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer_id INT NOT NULL,
    apk_id INT NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    amount DECIMAL(15, 2) DEFAULT 500.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (referrer_id) REFERENCES users(id),
    FOREIGN KEY (apk_id) REFERENCES apks(id)
);