File size: 3,755 Bytes
0dff816 |
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 |
CREATE TABLE `users` (
`id` int NOT NULL AUTO_INCREMENT,
`user_type` enum('marketer','agent','admin') DEFAULT 'marketer',
`referred_by` int DEFAULT NULL,
`username` varchar(50) NOT NULL,
`email` varchar(100) NOT NULL,
`country` varchar(2) NOT NULL,
`phone_number` varchar(20) NOT NULL,
`tier` enum('Basic','Premium') DEFAULT 'Basic',
`current_package_id` int DEFAULT NULL,
`package_start_date` datetime DEFAULT NULL,
`package_end_date` datetime DEFAULT NULL,
`package` enum('None','NOVA','SUPERIOR','GOLD') DEFAULT 'None',
`balance` decimal(10,2) DEFAULT '0.00',
`verification_token` varchar(100) DEFAULT NULL,
`reset_token` varchar(100) DEFAULT NULL,
`reset_token_expiry` datetime DEFAULT NULL,
`total_invested` decimal(15,2) DEFAULT '0.00',
`total_earnings` decimal(15,2) DEFAULT '0.00',
`total_deposits` decimal(10,2) DEFAULT '0.00',
`total_withdrawals` decimal(10,2) DEFAULT '0.00',
`rewards` decimal(10,2) DEFAULT '0.00',
`meta_earnings` decimal(10,2) DEFAULT '0.00',
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_login` timestamp NULL DEFAULT NULL,
`pin_hash` varchar(255) DEFAULT '',
`password_hash` varchar(255) NOT NULL,
`currency` varchar(3) DEFAULT 'KES',
`exchange_rate` decimal(10,4) DEFAULT '1.0000',
`referral_code` varchar(20) DEFAULT NULL,
`is_active` tinyint(1) DEFAULT '1',
`login_attempts` int DEFAULT '0',
`last_login_attempt` timestamp NULL DEFAULT NULL,
`account_status` enum('active','suspended','pending') DEFAULT 'active',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `referral_code` (`referral_code`),
KEY `idx_users_email` (`email`),
KEY `idx_users_username` (`username`),
KEY `idx_account_status` (`account_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `user_sessions` (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`session_id` varchar(128) NOT NULL,
`ip_address` varchar(45) DEFAULT NULL,
`user_agent` text,
`login_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`last_activity` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`expires_at` datetime NOT NULL,
`is_active` tinyint(1) DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `session_id` (`session_id`),
KEY `user_id` (`user_id`),
KEY `idx_expires_at` (`expires_at`),
KEY `idx_is_active` (`is_active`),
CONSTRAINT `user_sessions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `user_activity_log` (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`activity_type` varchar(50) NOT NULL,
`description` text,
`ip_address` varchar(45) DEFAULT NULL,
`user_agent` text,
`metadata` json DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `idx_activity_type` (`activity_type`),
KEY `idx_created_at` (`created_at`),
CONSTRAINT `user_activity_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `users` (
`username`, `email`, `country`, `phone_number`, `password_hash`, `referral_code`, `tier`, `package`
) VALUES
('admin', 'admin@jmotors.com', 'KE', '+254700000000', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'ADMIN001', 'Premium', 'GOLD'),
('testuser', 'test@jmotors.com', 'KE', '+254711111111', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'TEST002', 'Basic', 'NOVA'); |