schema.sql

CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT,username TEXT UNIQUE NOT NULL,email TEXT UNIQUE NOT NULL,phone TEXT UNIQUE NOT NULL,full_name TEXT NOT NULL,password_hash TEXT NOT NULL,reset_phrase_hash TEXT NOT NULL,balance REAL DEFAULT 0,total_deposit REAL DEFAULT 0,total_profit REAL DEFAULT 0,investment_plan TEXT DEFAULT 'Stage 1',is_active INTEGER DEFAULT 1,is_admin INTEGER DEFAULT 0,login_attempts INTEGER DEFAULT 0,locked_until TEXT,last_login TEXT,created_at TEXT DEFAULT (datetime('now')),updated_at TEXT DEFAULT (datetime('now')));
CREATE TABLE IF NOT EXISTS transactions (id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER NOT NULL,type TEXT NOT NULL,amount REAL NOT NULL,status TEXT DEFAULT 'pending',payment_method TEXT,wallet_address TEXT,description TEXT,reference TEXT,linked_type TEXT,linked_id INTEGER,created_at TEXT DEFAULT (datetime('now')),updated_at TEXT DEFAULT (datetime('now')));
CREATE TABLE IF NOT EXISTS payment_settings (id INTEGER PRIMARY KEY AUTOINCREMENT,method TEXT UNIQUE NOT NULL,display_name TEXT NOT NULL,is_active INTEGER DEFAULT 1,logo TEXT,details TEXT DEFAULT '[]',created_at TEXT DEFAULT (datetime('now')));
CREATE TABLE IF NOT EXISTS support_tickets (id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER NOT NULL,subject TEXT NOT NULL,message TEXT NOT NULL,status TEXT DEFAULT 'open',priority TEXT DEFAULT 'medium',created_at TEXT DEFAULT (datetime('now')),updated_at TEXT DEFAULT (datetime('now')));
CREATE TABLE IF NOT EXISTS announcements (id INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT NOT NULL,message TEXT NOT NULL,type TEXT DEFAULT 'info',is_active INTEGER DEFAULT 1,created_at TEXT DEFAULT (datetime('now')));
CREATE TABLE IF NOT EXISTS admin_settings (key TEXT PRIMARY KEY,value TEXT,updated_at TEXT DEFAULT (datetime('now')));
CREATE TABLE IF NOT EXISTS product_orders (id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER NOT NULL,product_name TEXT NOT NULL,product_price REAL NOT NULL,quantity INTEGER DEFAULT 1,payment_method TEXT,shipping_address TEXT,status TEXT DEFAULT 'pending',reference TEXT,created_at TEXT DEFAULT (datetime('now')),updated_at TEXT DEFAULT (datetime('now')));
CREATE TABLE IF NOT EXISTS admin_logs (id INTEGER PRIMARY KEY AUTOINCREMENT,admin_id INTEGER,action TEXT NOT NULL,target TEXT,details TEXT,created_at TEXT DEFAULT (datetime('now')));
CREATE TABLE IF NOT EXISTS user_wallets (user_id INTEGER PRIMARY KEY,deposit_balance REAL DEFAULT 0,roi_balance REAL DEFAULT 0,bonus_balance REAL DEFAULT 0,total_roi_earned REAL DEFAULT 0,last_roi_at TEXT);
CREATE TABLE IF NOT EXISTS roi_cycles (id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER NOT NULL,stage TEXT NOT NULL,roi_percent REAL NOT NULL,deposit_base REAL NOT NULL,roi_amount REAL NOT NULL,status TEXT DEFAULT 'completed',cycle_date TEXT DEFAULT (datetime('now')));
CREATE TABLE IF NOT EXISTS purchase_requests (id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER NOT NULL,product_name TEXT NOT NULL,product_price REAL NOT NULL,quantity INTEGER DEFAULT 1,payment_method TEXT,shipping_address TEXT,status TEXT DEFAULT 'pending',transaction_id INTEGER,created_at TEXT DEFAULT (datetime('now')),updated_at TEXT DEFAULT (datetime('now')));
CREATE TABLE IF NOT EXISTS stock_positions (id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER NOT NULL,symbol TEXT NOT NULL,shares REAL NOT NULL,avg_price REAL NOT NULL,current_price REAL,created_at TEXT DEFAULT (datetime('now')),updated_at TEXT DEFAULT (datetime('now')));
CREATE TABLE IF NOT EXISTS stock_trades (id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER NOT NULL,symbol TEXT NOT NULL,type TEXT NOT NULL,shares REAL NOT NULL,price REAL NOT NULL,total REAL NOT NULL,status TEXT DEFAULT 'pending',transaction_id INTEGER,created_at TEXT DEFAULT (datetime('now')),updated_at TEXT DEFAULT (datetime('now')));
CREATE TABLE IF NOT EXISTS user_messages (id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER NOT NULL,title TEXT NOT NULL,body TEXT NOT NULL,type TEXT DEFAULT 'info',is_read INTEGER DEFAULT 0,from_admin INTEGER DEFAULT 1,reference TEXT,sender_id INTEGER,parent_id INTEGER,created_at TEXT DEFAULT (datetime('now')));