-- Missing Database Tables for Australian Agro MLM Platform
-- These tables are referenced in the functions but missing from the main schema

-- Level Income Distributions Table
CREATE TABLE IF NOT EXISTS level_income_distributions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    from_user_id INT NOT NULL,
    level INT NOT NULL,
    package_amount DECIMAL(10,2) NOT NULL,
    total_commission DECIMAL(10,2) NOT NULL,
    daily_amount DECIMAL(10,2) NOT NULL,
    days_remaining INT NOT NULL DEFAULT 100,
    status ENUM('active', 'completed', 'cancelled') 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 (from_user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_level (user_id, level),
    INDEX idx_from_user (from_user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Wallet Transactions Table
CREATE TABLE IF NOT EXISTS wallet_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    admin_id INT NULL,
    transaction_type ENUM('deposit', 'withdrawal', 'package_purchase', 'add_funds', 'deduct_funds', 'transfer', 'commission', 'daily_income', 'level_income', 'direct_commission', 'team_reward', 'refund') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    balance_before DECIMAL(10,2) DEFAULT 0.00,
    balance_after DECIMAL(10,2) DEFAULT 0.00,
    description TEXT,
    reference_id VARCHAR(100),
    status ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (admin_id) REFERENCES admin_users(id) ON DELETE SET NULL,
    INDEX idx_user_transaction (user_id, transaction_type),
    INDEX idx_reference_id (reference_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Withdrawals Table (for USDT withdrawals)
CREATE TABLE IF NOT EXISTS withdrawals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    crypto_address VARCHAR(100) NOT NULL,
    network VARCHAR(10) DEFAULT 'BEP20',
    status ENUM('pending', 'processing', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    transaction_hash VARCHAR(100),
    admin_notes TEXT,
    processed_by INT,
    processed_at TIMESTAMP NULL,
    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 (processed_by) REFERENCES admin_users(id) ON DELETE SET NULL,
    INDEX idx_user_withdrawal (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Password Reset Tokens Table
CREATE TABLE IF NOT EXISTS password_reset_tokens (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    token VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    used BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token (token),
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at)
);

-- Add missing columns to users table if they don't exist
-- Note: These ALTER TABLE statements will only add columns if they don't exist
-- You may need to run these manually if your MySQL version doesn't support IF NOT EXISTS

-- Add referral_id column to users table
ALTER TABLE users ADD COLUMN IF NOT EXISTS referral_id VARCHAR(20) UNIQUE;

-- Add income_wallet_balance column to users table  
ALTER TABLE users ADD COLUMN IF NOT EXISTS income_wallet_balance DECIMAL(10,2) DEFAULT 0.00;

-- Add crypto_address column to users table
ALTER TABLE users ADD COLUMN IF NOT EXISTS crypto_address VARCHAR(100);

-- Add indexes for better performance
CREATE INDEX IF NOT EXISTS idx_users_referral_id ON users(referral_id);
CREATE INDEX IF NOT EXISTS idx_users_income_wallet ON users(income_wallet_balance);
CREATE INDEX IF NOT EXISTS idx_users_crypto_address ON users(crypto_address);

-- Insert some sample data for testing (optional)
-- You can uncomment these if you want to test with sample data

-- Sample users for testing team structure
-- INSERT INTO users (username, email, password, first_name, last_name, sponsor_id, referral_id, package_amount, total_investment) VALUES
-- ('testuser1', 'test1@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Test', 'User1', NULL, 'REF001', 100.00, 100.00),
-- ('testuser2', 'test2@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Test', 'User2', 1, 'REF002', 250.00, 250.00),
-- ('testuser3', 'test3@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Test', 'User3', 1, 'REF003', 500.00, 500.00),
-- ('testuser4', 'test4@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Test', 'User4', 2, 'REF004', 100.00, 100.00),
-- ('testuser5', 'test5@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Test', 'User5', 2, 'REF005', 250.00, 250.00),
-- ('testuser6', 'test6@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Test', 'User6', 3, 'REF006', 500.00, 500.00),
-- ('testuser7', 'test7@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Test', 'User7', 4, 'REF007', 100.00, 100.00),
-- ('testuser8', 'test8@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Test', 'User8', 5, 'REF008', 250.00, 250.00),
-- ('testuser9', 'test9@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Test', 'User9', 6, 'REF009', 500.00, 500.00),
-- ('testuser10', 'test10@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Test', 'User10', 7, 'REF010', 100.00, 100.00);