Gecko [ fastearnlimited.com ]

Name	Size	Permission	Date	Action
database.sql	5.68 KB	-rw-r--r--	2025-10-18 00:31

Rename

-- Share & Earn Database Structure

-- Create database
CREATE DATABASE IF NOT EXISTS shareearn;
USE shareearn;

-- Plans table
CREATE TABLE plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    duration INT NOT NULL, -- in days
    daily_ads INT NOT NULL,
    per_ad_earning DECIMAL(10, 2) NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active'
);

-- Users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fullname VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    password VARCHAR(255) NOT NULL,
    referral_code VARCHAR(50) UNIQUE NOT NULL,
    referred_by INT DEFAULT NULL,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    balance DECIMAL(10, 2) DEFAULT 0.00,
    current_plan INT DEFAULT NULL,
    FOREIGN KEY (referred_by) REFERENCES users(id),
    FOREIGN KEY (current_plan) REFERENCES plans(id)
);

-- Deposits table
CREATE TABLE deposits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    method VARCHAR(50) NOT NULL,
    receipt VARCHAR(255),
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Withdraws table
CREATE TABLE withdraws (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    method VARCHAR(50) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- D-Pins table
CREATE TABLE dpins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    pin_code VARCHAR(50) UNIQUE NOT NULL,
    status ENUM('active', 'used', 'expired') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    used_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Ads table
CREATE TABLE ads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    link TEXT NOT NULL,
    description TEXT,
    reward DECIMAL(10, 2) NOT NULL,
    duration INT NOT NULL, -- in seconds
    status ENUM('active', 'inactive') DEFAULT 'active'
);

-- Transactions table
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('deposit', 'withdraw', 'commission', 'ad') NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Referrals table
CREATE TABLE referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL, -- The referrer
    referred_user_id INT NOT NULL, -- The referred user
    level INT NOT NULL, -- Referral level (1, 2, 3)
    commission_amount DECIMAL(10, 2) NOT NULL,
    transaction_id INT, -- ID of the commission transaction
    source_transaction_id INT, -- ID of the original transaction that generated this commission
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (referred_user_id) REFERENCES users(id),
    FOREIGN KEY (transaction_id) REFERENCES transactions(id),
    FOREIGN KEY (source_transaction_id) REFERENCES transactions(id)
);

-- Methods table (Deposit/Withdraw methods)
CREATE TABLE methods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type ENUM('deposit', 'withdraw') NOT NULL,
    name VARCHAR(100) NOT NULL,
    account_number VARCHAR(100),
    holder_name VARCHAR(100),
    instructions TEXT
);

-- Referral Commission Levels
CREATE TABLE referral_commissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    level INT NOT NULL UNIQUE,
    commission_percentage DECIMAL(5, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert sample data for testing

-- Sample plans
INSERT INTO plans (name, price, duration, daily_ads, per_ad_earning) VALUES
('Basic Plan', 500.00, 30, 10, 2.00),
('Premium Plan', 1000.00, 30, 20, 3.00),
('VIP Plan', 2000.00, 30, 30, 5.00);

-- Sample deposit/withdraw methods
INSERT INTO methods (type, name, account_number, holder_name, instructions) VALUES
('deposit', 'Easypaisa', '03001234567', 'Share & Earn', 'Send money to this Easypaisa account and upload screenshot'),
('deposit', 'JazzCash', '03211234567', 'Share & Earn', 'Send money to this JazzCash account and upload screenshot'),
('deposit', 'Bank Transfer', '1234567890123456', 'Share & Earn', 'Transfer to this bank account and upload receipt'),
('withdraw', 'Easypaisa', '03001234567', 'Enter your Easypaisa number', 'Enter your Easypaisa number where you want to receive money'),
('withdraw', 'JazzCash', '03211234567', 'Enter your JazzCash number', 'Enter your JazzCash number where you want to receive money'),
('withdraw', 'Bank Transfer', 'Provide your bank details', 'Provide your bank account details', 'Provide your complete bank account details');

-- Sample ads
INSERT INTO ads (title, link, description, reward, duration) VALUES
('Watch Our Product Video', 'https://www.youtube.com/watch?v=dQw4w9WgXcQ', 'Watch this 30-second video to earn rewards', 2.00, 30),
('Visit Our Website', 'https://www.example.com', 'Visit our website and spend 45 seconds browsing', 3.00, 45),
('Try Our New Feature', 'https://www.example.com/new-feature', 'Try our new feature for 60 seconds', 5.00, 60);

-- Sample referral commission levels
INSERT INTO referral_commissions (level, commission_percentage) VALUES
(1, 10.00),
(2, 5.00),
(3, 2.00);