File size: 3,065 Bytes
c7f545b |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
CREATE DATABASE IF NOT EXISTS defaultdb;
USE defaultdb;
CREATE TABLE admins (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
full_name VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
full_name VARCHAR(255) NOT NULL,
dob DATE NOT NULL,
address TEXT NOT NULL,
country VARCHAR(100) NOT NULL,
zip_code VARCHAR(20) NOT NULL,
phone_number VARCHAR(20) NOT NULL,
balance DECIMAL(10,2) DEFAULT 0.00,
kyc_status VARCHAR(50) DEFAULT 'not_submitted',
withdrawal_status VARCHAR(50) DEFAULT 'active',
status VARCHAR(50) DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE kyc_documents (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
document_category VARCHAR(50) NOT NULL, -- id, utility, ssn, selfie
document_type VARCHAR(50) NOT NULL, -- state_id, driver_license, passport, utility_bill, paystub, ssn, selfie
file_path VARCHAR(255) NOT NULL,
status VARCHAR(50) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE investment_plans (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
roi DECIMAL(5,2) NOT NULL,
tenure INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE investments (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
plan_id INT,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(50) DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (plan_id) REFERENCES investment_plans(id) ON DELETE SET NULL
);
CREATE TABLE payment_methods (
id INT AUTO_INCREMENT PRIMARY KEY,
crypto_name VARCHAR(255) NOT NULL,
wallet_address VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE transactions (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
type VARCHAR(50) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(50) DEFAULT 'pending',
wallet_address VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE messages (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
message TEXT NOT NULL,
is_read BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE testimonials (
id INT AUTO_INCREMENT PRIMARY KEY,
author VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE withdrawals (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
); |