-- User Packages table
CREATE TABLE IF NOT EXISTS user_packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    topup_commission DECIMAL(5,2) DEFAULT 0.00,
    intl_commission DECIMAL(5,2) DEFAULT 0.00,
    pin_commission DECIMAL(5,2) DEFAULT 0.00,
    gift_commission DECIMAL(5,2) DEFAULT 0.00,
    card_commission DECIMAL(5,2) DEFAULT 0.00,
    drive_commission DECIMAL(5,2) DEFAULT 0.00,
    is_active TINYINT(1) DEFAULT 1,
    is_default TINYINT(1) DEFAULT 0,
    color VARCHAR(20) DEFAULT '#1a3c8f',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default packages
INSERT INTO user_packages (name, description, topup_commission, intl_commission, pin_commission, gift_commission, card_commission, drive_commission, is_active, is_default, color) VALUES
('Basic', 'Default package for all users', 0, 0, 0, 0, 0, 0, 1, 1, '#1a3c8f'),
('Silver', 'Silver member benefits', 2.00, 1.50, 1.50, 1.50, 1.50, 1.00, 1, 0, '#718096'),
('Gold', 'Gold member benefits', 3.00, 2.50, 2.50, 2.50, 2.50, 2.00, 1, 0, '#d69e2e'),
('Platinum', 'Premium package', 5.00, 4.00, 4.00, 4.00, 4.00, 3.00, 1, 0, '#805ad5');

-- Add package_id to users
ALTER TABLE users ADD COLUMN IF NOT EXISTS package_id INT DEFAULT 1;
ALTER TABLE users ADD FOREIGN KEY IF NOT EXISTS (package_id) REFERENCES user_packages(id) ON DELETE SET NULL;
