CREATE TABLE admins(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(100) UNIQUE NOT NULL,
 password_hash VARCHAR(255) NOT NULL,status ENUM('active','inactive') DEFAULT 'active',
 created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE users(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,telegram_id BIGINT UNIQUE NOT NULL,username VARCHAR(255),
 first_name VARCHAR(255),last_name VARCHAR(255),language ENUM('fa','en','ar') DEFAULT 'en',
 status ENUM('pending','active','inactive') DEFAULT 'pending',expire_date DATETIME NULL,
 created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,INDEX(status,expire_date)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE subscription_plans(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,price DECIMAL(20,8) DEFAULT 0,
 currency VARCHAR(10) DEFAULT 'USD',duration_days INT UNSIGNED NOT NULL,status ENUM('active','inactive') DEFAULT 'active',
 created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE subscriptions(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,plan_id INT UNSIGNED NULL,
 plan_name VARCHAR(100) NOT NULL,price DECIMAL(20,8) NOT NULL,start_date DATETIME NOT NULL,expire_date DATETIME NOT NULL,
 status ENUM('active','expired','cancelled') DEFAULT 'active',created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,
 INDEX(user_id,status,expire_date),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(plan_id) REFERENCES subscription_plans(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE payments(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,subscription_id BIGINT UNSIGNED NULL,
 amount DECIMAL(20,8) NOT NULL,currency VARCHAR(10) DEFAULT 'USD',method ENUM('crypto','card') NOT NULL,
 crypto_network VARCHAR(50),wallet_address VARCHAR(255),proof_image VARCHAR(500),transaction_id VARCHAR(255),
 status ENUM('pending','paid','approved','rejected','failed') DEFAULT 'pending',approved_by INT UNSIGNED NULL,
 approved_at DATETIME NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,
 INDEX(user_id),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL,
 FOREIGN KEY(approved_by) REFERENCES admins(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE trades(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,external_id VARCHAR(191) UNIQUE NOT NULL,ticket VARCHAR(100) NOT NULL,
 symbol VARCHAR(50) NOT NULL,direction VARCHAR(20) NOT NULL,lot DECIMAL(20,8) NOT NULL,entry DECIMAL(30,10) NOT NULL,
 sl DECIMAL(30,10),tp DECIMAL(30,10),current_price DECIMAL(30,10),profit DECIMAL(30,10) DEFAULT 0,
 trade_status VARCHAR(30) DEFAULT 'open',screenshot_path VARCHAR(500),created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,
 INDEX(ticket),INDEX(created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE trade_updates(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,trade_id BIGINT UNSIGNED NOT NULL,event_type VARCHAR(40) NOT NULL,
 old_json JSON,new_json JSON,created_at DATETIME NOT NULL,FOREIGN KEY(trade_id) REFERENCES trades(id) ON DELETE CASCADE);
CREATE TABLE trade_targets(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,trade_id BIGINT UNSIGNED NOT NULL,user_id INT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL,UNIQUE KEY uq_target(trade_id,user_id),
 FOREIGN KEY(trade_id) REFERENCES trades(id) ON DELETE CASCADE,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE message_queue(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,trade_id BIGINT UNSIGNED NULL,user_id INT UNSIGNED NOT NULL,
 message_type VARCHAR(40) NOT NULL,payload_json JSON NOT NULL,status ENUM('pending','processing','sent','failed') DEFAULT 'pending',
 attempts INT UNSIGNED DEFAULT 0,last_error TEXT,dedupe_key VARCHAR(191) UNIQUE,next_attempt_at DATETIME NULL,sent_at DATETIME NULL,
 created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,INDEX(status,next_attempt_at),
 FOREIGN KEY(trade_id) REFERENCES trades(id) ON DELETE SET NULL,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE translations(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,key_name VARCHAR(191) UNIQUE NOT NULL,fa TEXT,en TEXT,ar TEXT,updated_at DATETIME NOT NULL);
CREATE TABLE settings(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,setting_key VARCHAR(100) UNIQUE NOT NULL,setting_value TEXT,updated_at DATETIME NOT NULL);
CREATE TABLE system_logs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,level VARCHAR(20),module VARCHAR(80),message TEXT,context_json JSON,created_at DATETIME NOT NULL,INDEX(created_at));
CREATE TABLE backup_logs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,file_name VARCHAR(500),file_size BIGINT,status ENUM('success','failed'),created_at DATETIME NOT NULL);
CREATE TABLE api_keys(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100),key_hash CHAR(64) UNIQUE,status ENUM('active','inactive') DEFAULT 'active',last_used_at DATETIME,created_at DATETIME NOT NULL);