-- Australian Agro MLM Platform Database Schema

CREATE DATABASE IF NOT EXISTS australian_agro;
USE australian_agro;

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(50),
    state VARCHAR(50),
    country VARCHAR(50) DEFAULT 'Australia',
    postal_code VARCHAR(10),
    sponsor_id INT,
    position ENUM('left', 'right') DEFAULT 'left',
    package_id INT,
    package_amount DECIMAL(10,2) DEFAULT 0.00,
    total_investment DECIMAL(10,2) DEFAULT 0.00,
    total_income DECIMAL(10,2) DEFAULT 0.00,
    wallet_balance DECIMAL(10,2) DEFAULT 0.00,
    withdrawal_balance DECIMAL(10,2) DEFAULT 0.00,
    kyc_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    kyc_documents TEXT,
    bank_name VARCHAR(100),
    bank_account VARCHAR(50),
    bank_ifsc VARCHAR(20),
    account_holder_name VARCHAR(100),
    status ENUM('active', 'inactive', 'blocked') DEFAULT 'active',
    email_verified BOOLEAN DEFAULT FALSE,
    phone_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (sponsor_id) REFERENCES users(id),
    INDEX idx_sponsor (sponsor_id),
    INDEX idx_email (email),
    INDEX idx_username (username)
);

-- Packages table
CREATE TABLE packages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    daily_percentage DECIMAL(5,2) NOT NULL,
    direct_commission DECIMAL(5,2) NOT NULL,
    level2_percentage DECIMAL(5,2) DEFAULT 50.00,
    level3_percentage DECIMAL(5,2) DEFAULT 25.00,
    level4_percentage DECIMAL(5,2) DEFAULT 10.00,
    level5_percentage DECIMAL(5,2) DEFAULT 5.00,
    level_duration INT DEFAULT 100,
    description TEXT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User packages (investment history)
CREATE TABLE user_packages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    package_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    daily_percentage DECIMAL(5,2) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    status ENUM('active', 'completed', 'cancelled') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (package_id) REFERENCES packages(id),
    INDEX idx_user_package (user_id, package_id)
);

-- Income transactions
CREATE TABLE income_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    transaction_type ENUM('daily_income', 'direct_commission', 'level_income', 'team_reward', 'withdrawal', 'topup') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description TEXT,
    reference_id INT,
    level INT,
    from_user_id INT,
    status ENUM('pending', 'completed', 'cancelled') DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (from_user_id) REFERENCES users(id),
    INDEX idx_user_income (user_id, transaction_type),
    INDEX idx_created_at (created_at)
);

-- Withdrawal requests
CREATE TABLE withdrawal_requests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    bank_name VARCHAR(100),
    bank_account VARCHAR(50),
    bank_ifsc VARCHAR(20),
    account_holder_name VARCHAR(100),
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    admin_notes TEXT,
    processed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_withdrawal (user_id),
    INDEX idx_status (status)
);

-- Top-up requests
CREATE TABLE topup_requests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method ENUM('bank_transfer', 'upi', 'wallet') NOT NULL,
    transaction_id VARCHAR(100),
    screenshot VARCHAR(255),
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    admin_notes TEXT,
    processed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_topup (user_id),
    INDEX idx_status (status)
);

-- Products table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    category ENUM('seeds', 'fertilizers', 'tools', 'equipment', 'package') NOT NULL,
    image VARCHAR(255),
    stock_quantity INT DEFAULT 0,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'confirmed', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    shipping_address TEXT,
    payment_status ENUM('pending', 'paid', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_order (user_id)
);

-- Order items
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Team rewards
CREATE TABLE team_rewards (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    reward_type ENUM('team_size', 'business_volume', 'achievement') NOT NULL,
    reward_amount DECIMAL(10,2) NOT NULL,
    criteria_met VARCHAR(100),
    status ENUM('pending', 'credited') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_reward (user_id)
);

-- System settings
CREATE TABLE system_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Admin users
CREATE TABLE admin_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('super_admin', 'admin', 'moderator') DEFAULT 'admin',
    permissions TEXT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Activity logs
CREATE TABLE activity_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    admin_id INT,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (admin_id) REFERENCES admin_users(id),
    INDEX idx_user_activity (user_id),
    INDEX idx_admin_activity (admin_id),
    INDEX idx_created_at (created_at)
);

-- Insert default packages
INSERT INTO packages (name, amount, daily_percentage, direct_commission, description) VALUES
('Starter Package', 50.00, 1.00, 5.00, 'Perfect for beginners - Organic seeds and basic tools'),
('Growth Package', 100.00, 1.00, 5.00, 'Ideal for growing farmers - Seeds, fertilizers, and tools'),
('Professional Package', 250.00, 1.00, 5.00, 'For serious farmers - Complete farming kit'),
('Premium Package', 500.00, 1.00, 5.00, 'Advanced farming equipment and premium products'),
('Elite Package', 1000.00, 1.00, 5.00, 'Complete farming solution with all premium products');

-- Insert default system settings
INSERT INTO system_settings (setting_key, setting_value, description) VALUES
('site_name', 'Australian Agro', 'Website name'),
('site_email', 'info@australianagro.com', 'Contact email'),
('min_withdrawal', '50.00', 'Minimum withdrawal amount'),
('max_withdrawal', '10000.00', 'Maximum withdrawal amount'),
('withdrawal_fee', '2.00', 'Withdrawal processing fee'),
('team_reward_25', '50.00', 'Reward for 25 team members'),
('team_reward_50', '100.00', 'Reward for 50 team members'),
('team_reward_100', '250.00', 'Reward for 100 team members'),
('maintenance_mode', '0', 'Maintenance mode (0=off, 1=on)');

-- Insert default admin user
INSERT INTO admin_users (username, email, password, full_name, role) VALUES
('admin', 'admin@australianagro.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', 'super_admin');

-- Insert sample products
INSERT INTO products (name, description, price, category, stock_quantity) VALUES
('Organic Tomato Seeds', 'High-quality organic tomato seeds for home and commercial farming', 25.00, 'seeds', 1000),
('Natural Compost Fertilizer', '100% organic compost fertilizer for healthy plant growth', 45.00, 'fertilizers', 500),
('Professional Garden Tools Set', 'Complete set of professional gardening tools', 120.00, 'tools', 200),
('Automatic Irrigation System', 'Smart irrigation system for efficient water management', 350.00, 'equipment', 50),
('Premium Farming Package', 'Complete farming package with seeds, fertilizers, and tools', 500.00, 'package', 100);

-- Hero Slides table
CREATE TABLE hero_slides (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    subtitle TEXT,
    description TEXT,
    image_path VARCHAR(255) NOT NULL,
    button_text VARCHAR(100),
    button_link VARCHAR(255),
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default hero slides
INSERT INTO hero_slides (title, subtitle, description, image_path, button_text, button_link, sort_order) VALUES
('Grow with Agro, Earn with Agro', 'Join Australia\'s premier agro farming MLM platform', 'Invest in sustainable agriculture products and build a profitable network while supporting eco-friendly farming practices.', 'assets/images/hero-slide-1.jpg', 'Start Earning', 'register.php', 1),
('Sustainable Farming Solutions', 'Premium organic products for modern agriculture', 'Discover our range of organic seeds, natural fertilizers, and professional farming equipment designed for sustainable growth.', 'assets/images/hero-slide-2.jpg', 'View Products', 'products.php', 2),
('Build Your Team, Grow Your Income', 'Multi-level commission structure with daily returns', 'Earn through direct referrals, level commissions, and team rewards. Start with as little as $50 and watch your income grow.', 'assets/images/hero-slide-3.jpg', 'Learn More', '#opportunity', 3);