CREATE DATABASE IF NOT EXISTS digital_bazaar CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE digital_bazaar;

CREATE TABLE users (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 username VARCHAR(60) NOT NULL UNIQUE,
 email VARCHAR(150) NOT NULL UNIQUE,
 phone VARCHAR(30) DEFAULT NULL,
 password_hash VARCHAR(255) NOT NULL,
 avatar VARCHAR(255) DEFAULT NULL,
 main_wallet DECIMAL(12,2) NOT NULL DEFAULT 0,
 total_income DECIMAL(12,2) NOT NULL DEFAULT 0,
 total_withdraw DECIMAL(12,2) NOT NULL DEFAULT 0,
 referral_balance DECIMAL(12,2) NOT NULL DEFAULT 0,
 active_referrals INT NOT NULL DEFAULT 0,
 is_active TINYINT(1) NOT NULL DEFAULT 0,
 vip_level VARCHAR(50) NOT NULL DEFAULT 'VIP',
 referral_code VARCHAR(60) NOT NULL UNIQUE,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE admins (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(60) UNIQUE NOT NULL,
 password_hash VARCHAR(255) NOT NULL,
 role VARCHAR(30) NOT NULL DEFAULT 'super_admin',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE services (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 description VARCHAR(255) DEFAULT '',
 icon VARCHAR(100) DEFAULT 'fa-solid fa-grid-2',
 color VARCHAR(20) DEFAULT '#1261e8',
 url VARCHAR(255) DEFAULT '#',
 status TINYINT(1) DEFAULT 1,
 sort_order INT DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE referral_targets (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 target INT NOT NULL,
 bonus DECIMAL(12,2) NOT NULL,
 status TINYINT(1) DEFAULT 1,
 sort_order INT DEFAULT 0
);

CREATE TABLE transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id INT UNSIGNED NOT NULL,
 type VARCHAR(40) NOT NULL,
 amount DECIMAL(12,2) NOT NULL,
 description VARCHAR(255) DEFAULT '',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(user_id)
);

CREATE TABLE withdrawals (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id INT UNSIGNED NOT NULL,
 method VARCHAR(40) NOT NULL,
 account_no VARCHAR(80) NOT NULL,
 amount DECIMAL(12,2) NOT NULL,
 status VARCHAR(30) DEFAULT 'Pending',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE settings (
 setting_key VARCHAR(100) PRIMARY KEY,
 setting_value TEXT
);

INSERT INTO settings(setting_key,setting_value) VALUES
('site_name','DIGITAL BAZAAR SHOP'),
('welcome_text','আপনার ড্যাশবোর্ড দেখুন'),
('telegram_url','#'),
('app_url','#')
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);

INSERT INTO services(name,description,icon,color,sort_order) VALUES
('Facebook Income','', 'fa-brands fa-facebook-f','#1261e8',1),
('অনলাইন শপ','', 'fa-solid fa-bag-shopping','#eab308',2),
('এড প্রমোশন','', 'fa-solid fa-bullhorn','#16a34a',3),
('Instagram job','', 'fa-brands fa-instagram','#1261e8',4),
('প্রমোশনাল সার্ভিস','', 'fa-solid fa-users','#eab308',5),
('Gmail job','', 'fa-solid fa-envelope','#16a34a',6),
('ব্যবহারিক পণ্য বিক্রি','', 'fa-solid fa-tag','#1261e8',7),
('মোবাইল রিচার্জ','', 'fa-solid fa-mobile-screen','#eab308',8),
('রাইড শেয়ারিং','', 'fa-solid fa-car','#16a34a',9),
('রেলওয়ে টিকেট','', 'fa-solid fa-train','#1261e8',10),
('সফটওয়্যার ডিজাইন','', 'fa-solid fa-code','#eab308',11),
('লটারি টিকেট','', 'fa-solid fa-trophy','#16a34a',12);

INSERT INTO referral_targets(target,bonus,sort_order) VALUES
(50,200,1),(100,400,2),(250,1250,3);

-- Generate a secure admin password hash with PHP:
-- php -r "echo password_hash('CHANGE_THIS_PASSWORD', PASSWORD_DEFAULT), PHP_EOL;"
-- Then:
-- INSERT INTO admins(username,password_hash) VALUES('admin','PASTE_HASH_HERE');