-- Standalone User Dashboard Auth Database Backup -- Database: `smitht8_users` -- Created: 2026-05-06 00:36:05 SET FOREIGN_KEY_CHECKS=0; SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; START TRANSACTION; -- -------------------------------------------------------- -- Table structure for `audit_logs` DROP TABLE IF EXISTS `audit_logs`; CREATE TABLE `audit_logs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned DEFAULT NULL, `action` varchar(100) NOT NULL, `module` varchar(100) DEFAULT NULL, `description` text DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `user_agent` varchar(255) DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_audit_user` (`user_id`), KEY `idx_audit_created` (`created_at`) ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Data for `audit_logs` INSERT INTO `audit_logs` (`id`,`user_id`,`action`,`module`,`description`,`ip_address`,`user_agent`,`created_at`) VALUES ('1','1','mfa_start_failed','auth','Google OAuth is not connected.','103.187.211.236','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:03:46'), ('2','1','mfa_start_failed','auth','Google OAuth is not connected.','103.187.211.236','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:06:41'), ('3','1','mfa_start_failed','auth','Google OAuth is not connected.','103.187.211.236','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:11:51'), ('4','1','mfa_start_failed','auth','Google OAuth is not connected.','103.187.211.236','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:14:12'), ('5','1','mfa_start_failed','auth','Google OAuth is not connected. Open Security > Email OTP Settings and connect Gmail account.','103.187.211.236','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:18:27'), ('6','1','mfa_start_failed','auth','Google OAuth is not connected. Open Security > Email OTP Settings and connect Gmail account.','103.187.211.236','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:19:16'), ('7','1','mfa_start_failed','auth','Google OAuth is not connected. Open Security > Email OTP Settings and connect Gmail account.','103.187.211.236','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:28:02'), ('8','1','login_success','auth','User logged in.','117.96.148.232','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:28:55'), ('9','1','password_changed','auth','Password changed.','117.96.148.232','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:29:21'), ('10','1','logout','auth','User logged out.','117.96.148.232','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:35:49'), ('11','1','login_success','auth','User logged in.','117.96.148.232','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:36:00'), ('12','1','logout','auth','User logged out.','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-06 00:04:02'), ('13','1','login_success','auth','User logged in.','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-06 00:04:08'), ('14','1','google_oauth_connected','security','Google OAuth connected for OTP email: security@smiththerm.in','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-06 00:10:04'), ('15','1','logout','auth','User logged out.','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-06 00:15:19'), ('16','1','mfa_otp_sent','auth','MFA OTP sent through Google OAuth Gmail API.','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-06 00:15:27'), ('17','1','login_success','auth','User logged in.','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-06 00:16:06'), ('18','1','logout','auth','User logged out.','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-06 00:16:47'), ('19','1','login_success','auth','User logged in.','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-06 00:16:54'), ('20','1','logout','auth','User logged out.','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-06 00:17:09'), ('21','1','mfa_otp_sent','auth','MFA OTP sent through Google OAuth Gmail API.','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-06 00:17:15'), ('22','1','mfa_success','auth','MFA OTP verified.','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-06 00:17:31'); -- -------------------------------------------------------- -- Table structure for `email_logs` DROP TABLE IF EXISTS `email_logs`; CREATE TABLE `email_logs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned DEFAULT NULL, `to_email` varchar(190) DEFAULT NULL, `subject` varchar(255) DEFAULT NULL, `status` varchar(50) DEFAULT NULL, `error_message` text DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Data for `email_logs` -- -------------------------------------------------------- -- Table structure for `email_oauth_tokens` DROP TABLE IF EXISTS `email_oauth_tokens`; CREATE TABLE `email_oauth_tokens` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `provider` varchar(50) NOT NULL DEFAULT 'google', `email` varchar(190) DEFAULT NULL, `access_token` text DEFAULT NULL, `refresh_token` text DEFAULT NULL, `expires_at` datetime DEFAULT NULL, `scopes` text DEFAULT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Data for `email_oauth_tokens` INSERT INTO `email_oauth_tokens` (`id`,`provider`,`email`,`access_token`,`refresh_token`,`expires_at`,`scopes`,`is_active`,`created_at`,`updated_at`) VALUES ('1','google','security@smiththerm.in','lg2QNNR5rTh3rWpeXT5WjrqTT880GSw7NhG5kiuHR6exbR/gru+QQ5S9LzLS08kbKeOQ/krU3PUhJjbbh3F6fOh4uEDeZqY3JJ9dr92KNa0e+EDCIUnfAujfMX4TViJF/+EsrxbT8kQDN/xAmOhaPnAfU9mQZPhFr2G7PSWxAPjmu0K2Mjp8EwTdZ+nJQ+DtdHzAbsrxzfbcOy2gPkayam5m/9EpDKP0V/j0mSsQBuSsefFmj65ABGO5NAdzJks5XjQG6t2CHOWls5Hof+Pk6HvKNbKJ77iiLCEslqO4iqyoX4IRcY8TPI/+qup/m7az4V6b2fInhTjxk75yC1nxmi9ALGJys64byiCgO6LL+IE=','glU7S4vumNQPQaby4ZQ68RVxe7B1JWzB/WoAupuBFsC/PZRX16fq0MA5mWkCyZL5gS68pfpFgACqEv2swuYhOJaXt2reRgCwMpsRfjJvhCSbCvrCpnbZFOWA3DbhyS6bk0JBo5d+SKrOx+VdKDe2Y3/dkDeq2t02ASsXfeYvwQQ=','2026-05-06 01:10:03','https://www.googleapis.com/auth/gmail.send','1','2026-05-06 00:10:04','2026-05-06 00:10:04'); -- -------------------------------------------------------- -- Table structure for `login_attempts` DROP TABLE IF EXISTS `login_attempts`; CREATE TABLE `login_attempts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(150) DEFAULT NULL, `user_id` bigint(20) unsigned DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `user_agent` varchar(255) DEFAULT NULL, `status` enum('success','failed','locked') NOT NULL, `reason` varchar(255) DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_login_username` (`username`), KEY `idx_login_created` (`created_at`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Data for `login_attempts` INSERT INTO `login_attempts` (`id`,`username`,`user_id`,`ip_address`,`user_agent`,`status`,`reason`,`created_at`) VALUES ('1','admin','1','117.96.148.232','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','success','Login successful.','2026-05-05 23:28:55'), ('2','admin','1','117.96.148.232','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','success','Login successful.','2026-05-05 23:36:00'), ('3','admin','1','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','success','Login successful.','2026-05-06 00:04:08'), ('4','admin','1','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','success','Login successful.','2026-05-06 00:16:06'), ('5','admin','1','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','success','Login successful.','2026-05-06 00:16:54'), ('6','admin','1','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','success','MFA login successful.','2026-05-06 00:17:31'); -- -------------------------------------------------------- -- Table structure for `mfa_challenges` DROP TABLE IF EXISTS `mfa_challenges`; CREATE TABLE `mfa_challenges` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL, `otp_hash` varchar(255) NOT NULL, `purpose` varchar(50) NOT NULL DEFAULT 'login', `attempts` int(11) NOT NULL DEFAULT 0, `expires_at` datetime NOT NULL, `verified_at` datetime DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `user_agent` varchar(255) DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_mfa_user` (`user_id`), KEY `idx_mfa_expires` (`expires_at`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Data for `mfa_challenges` INSERT INTO `mfa_challenges` (`id`,`user_id`,`otp_hash`,`purpose`,`attempts`,`expires_at`,`verified_at`,`ip_address`,`user_agent`,`created_at`) VALUES ('1','1','$2y$10$HE75HlCuhZqs6Bkx8joDZO9iTUhPDtZbwy4c3H.JTDhZ6bqpmvDPK','login','0','2026-05-05 23:13:46','2026-05-05 10:57:26','103.187.211.236','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:03:46'), ('2','1','$2y$10$4wD.vf.6Ih5HlePwetCNp.5EGkhyXLpdo0TOK54WGkRUDVNyQovui','login','0','2026-05-05 23:16:41','2026-05-05 10:57:26','103.187.211.236','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:06:41'), ('3','1','$2y$10$b4IlIBd2.sgMu4uAkXDbnuOCl/eL/dYFisGnjf9CfLFTi0uWtcw9O','login','0','2026-05-05 23:21:51','2026-05-05 10:57:26','103.187.211.236','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:11:51'), ('4','1','$2y$10$itAR3v34xZJcEDd1xzBpRe9mhnW8zbdKN76oXMEk0JBYGlH3GqYqu','login','0','2026-05-05 23:24:12','2026-05-05 10:57:26','103.187.211.236','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:14:12'), ('5','1','$2y$10$6Anf9h4hPeO2Tp3Q1ralc.QdDxOrJG6dayHeE4Iq39omOIzusIZzq','login','0','2026-05-05 23:28:27','2026-05-05 10:57:26','103.187.211.236','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:18:27'), ('6','1','$2y$10$Aqz6U6IFXP6AUKd.HeofCuPCQ2EK42PIaz/9U4D7/.WUXxO9mtuVy','login','0','2026-05-05 23:29:16','2026-05-05 10:57:26','103.187.211.236','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:19:16'), ('7','1','$2y$10$N6BMBt/8vqUv2R7VQd6uUeIgPVdozCpS9BosWaLprLmXSWIbphdbS','login','0','2026-05-05 23:38:02',NULL,'103.187.211.236','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-05 23:28:02'), ('8','1','$2y$10$/HqziANWx558WBeM9WlDOuI6PrCrV/lFe7udj.DjzLbUlAPfr6mU2','login','0','2026-05-06 00:25:26',NULL,'223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-06 00:15:26'), ('9','1','$2y$10$2w7O8y3anSRfJlLSpMHoCe7shN8Is/Z3SzTqCg4ltRfnRQAmZ.1vW','login','1','2026-05-06 00:27:14','2026-05-06 00:17:31','223.228.250.66','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36','2026-05-06 00:17:14'); -- -------------------------------------------------------- -- Table structure for `permissions` DROP TABLE IF EXISTS `permissions`; CREATE TABLE `permissions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `program_id` bigint(20) unsigned NOT NULL, `permission_key` varchar(150) NOT NULL, `permission_name` varchar(190) NOT NULL, `permission_group` varchar(120) DEFAULT NULL, `sort_order` int(11) NOT NULL DEFAULT 0, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_program_permission` (`program_id`,`permission_key`), CONSTRAINT `fk_permissions_program` FOREIGN KEY (`program_id`) REFERENCES `programs` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Data for `permissions` INSERT INTO `permissions` (`id`,`program_id`,`permission_key`,`permission_name`,`permission_group`,`sort_order`,`is_active`,`created_at`,`updated_at`) VALUES ('1','1','dashboard_view','View Dashboard','Dashboard','10','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('2','1','users_view','View Users','Users','100','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('3','1','users_create','Create Users','Users','110','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('4','1','users_edit','Edit Users','Users','120','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('5','1','users_manage_permissions','Manage User Program Access','Users','130','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('6','1','roles_view','View Roles','Roles','200','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('7','1','roles_create','Create Roles','Roles','210','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('8','1','roles_edit','Edit Roles','Roles','220','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('9','1','roles_permissions_manage','Manage Role Permissions','Roles','230','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('10','1','programs_view','View Programs','Programs','300','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('11','1','programs_create','Create Programs','Programs','310','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('12','1','programs_edit','Edit Programs','Programs','320','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('13','1','security_manage','Manage Security','Security','400','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('14','1','gmail_oauth_manage','Manage Google OTP OAuth','Security','410','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('15','1','audit_logs_view','View Audit Logs','Audit Logs','500','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('16','2','dashboard_view','View Dashboard','Dashboard','10','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('17','2','work_order_view','View Work Order','Work Order','11','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('18','2','work_order_create','Create Work Order','Work Order','12','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('19','2','work_order_edit','Edit Work Order','Work Order','13','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('20','2','work_order_delete','Delete Work Order','Work Order','14','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('21','2','production_view','View Production','Production','15','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('22','2','production_update','Update Production','Production','16','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('23','2','production_approve','Approve Production','Production','17','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('24','2','stock_report_view','View Stock Report','Reports','18','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('25','2','production_report_view','View Production Report','Reports','19','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('26','3','dashboard_view','View Dashboard','Dashboard','10','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('27','3','employee_view','View Employee','Employees','11','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('28','3','employee_create','Create Employee','Employees','12','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('29','3','employee_edit','Edit Employee','Employees','13','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('30','3','attendance_view','View Attendance','Attendance','14','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('31','3','attendance_update','Update Attendance','Attendance','15','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('32','3','salary_view','View Salary','Salary','16','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('33','3','salary_process','Process Salary','Salary','17','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('34','3','salary_approve','Approve Salary','Salary','18','1','2026-05-05 23:31:55','2026-05-06 00:12:24'), ('54','1','trusted_devices_manage','Manage Trusted Devices','Security','420','1','2026-05-05 11:54:27','2026-05-05 11:54:27'); -- -------------------------------------------------------- -- Table structure for `program_user_access` DROP TABLE IF EXISTS `program_user_access`; CREATE TABLE `program_user_access` ( `user_id` bigint(20) unsigned NOT NULL, `program_id` bigint(20) unsigned NOT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`user_id`,`program_id`), KEY `fk_program_user_access_program` (`program_id`), CONSTRAINT `fk_program_user_access_program` FOREIGN KEY (`program_id`) REFERENCES `programs` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_program_user_access_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Data for `program_user_access` INSERT INTO `program_user_access` (`user_id`,`program_id`,`is_active`,`created_at`) VALUES ('1','1','1','2026-05-05 10:29:21'); -- -------------------------------------------------------- -- Table structure for `programs` DROP TABLE IF EXISTS `programs`; CREATE TABLE `programs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `program_code` varchar(80) NOT NULL, `program_name` varchar(150) NOT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `program_code` (`program_code`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Data for `programs` INSERT INTO `programs` (`id`,`program_code`,`program_name`,`is_active`,`created_at`,`updated_at`) VALUES ('1','ACCESS_DASHBOARD','Access Dashboard','1','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('2','MRP','MRP / Production','1','2026-05-05 23:31:55','2026-05-06 00:12:31'), ('3','PAYROLL','Payroll','1','2026-05-05 23:31:55','2026-05-06 00:12:24'); -- -------------------------------------------------------- -- Table structure for `role_permissions` DROP TABLE IF EXISTS `role_permissions`; CREATE TABLE `role_permissions` ( `role_id` bigint(20) unsigned NOT NULL, `permission_id` bigint(20) unsigned NOT NULL, PRIMARY KEY (`role_id`,`permission_id`), KEY `fk_role_permissions_permission` (`permission_id`), CONSTRAINT `fk_role_permissions_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_role_permissions_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Data for `role_permissions` INSERT INTO `role_permissions` (`role_id`,`permission_id`) VALUES ('1','1'), ('1','2'), ('1','3'), ('1','4'), ('1','5'), ('1','6'), ('1','7'), ('1','8'), ('1','9'), ('1','10'), ('1','11'), ('1','12'), ('1','13'), ('1','14'), ('1','15'), ('1','54'); -- -------------------------------------------------------- -- Table structure for `roles` DROP TABLE IF EXISTS `roles`; CREATE TABLE `roles` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `role_key` varchar(80) NOT NULL, `role_name` varchar(150) NOT NULL, `status` enum('active','inactive') NOT NULL DEFAULT 'active', `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `role_key` (`role_key`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Data for `roles` INSERT INTO `roles` (`id`,`role_key`,`role_name`,`status`,`created_at`,`updated_at`) VALUES ('1','super_admin','Super Admin','active','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('2','admin','Admin','active','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('3','staff','Staff','active','2026-05-05 10:29:21','2026-05-05 10:29:21'), ('4','viewer','Viewer','active','2026-05-05 10:29:21','2026-05-05 10:29:21'); -- -------------------------------------------------------- -- Table structure for `user_permissions` DROP TABLE IF EXISTS `user_permissions`; CREATE TABLE `user_permissions` ( `user_id` bigint(20) unsigned NOT NULL, `permission_id` bigint(20) unsigned NOT NULL, `effect` enum('allow','deny') NOT NULL DEFAULT 'allow', PRIMARY KEY (`user_id`,`permission_id`), KEY `fk_user_permissions_permission` (`permission_id`), CONSTRAINT `fk_user_permissions_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_user_permissions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Data for `user_permissions` -- -------------------------------------------------------- -- Table structure for `user_recovery_codes` DROP TABLE IF EXISTS `user_recovery_codes`; CREATE TABLE `user_recovery_codes` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL, `code_hash` varchar(255) NOT NULL, `used_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_recovery_user` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Data for `user_recovery_codes` -- -------------------------------------------------------- -- Table structure for `user_trusted_devices` DROP TABLE IF EXISTS `user_trusted_devices`; CREATE TABLE `user_trusted_devices` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL, `selector` varchar(64) NOT NULL, `validator_hash` varchar(255) NOT NULL, `device_name` varchar(150) DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `user_agent_hash` varchar(255) DEFAULT NULL, `expires_at` datetime NOT NULL, `last_used_at` datetime DEFAULT NULL, `revoked_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `selector` (`selector`), KEY `idx_trusted_user` (`user_id`), KEY `idx_trusted_expires` (`expires_at`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Data for `user_trusted_devices` INSERT INTO `user_trusted_devices` (`id`,`user_id`,`selector`,`validator_hash`,`device_name`,`ip_address`,`user_agent_hash`,`expires_at`,`last_used_at`,`revoked_at`,`created_at`) VALUES ('1','1','8c8503a0690015ba7ec807c024e5ab58','20358c6899542fd8dbf1e6c8c22c4cad6567bbd4e53cacc937835cde2f2ba976','Trusted Device','223.228.250.66','4cbc8039bc8d8badefe54056014551dfd22b5e4a800a9e6305203a5a073e6b48','2026-06-05 00:17:31',NULL,NULL,'2026-05-06 00:17:31'); -- -------------------------------------------------------- -- Table structure for `users` DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(150) NOT NULL, `username` varchar(100) NOT NULL, `email` varchar(190) DEFAULT NULL, `password_hash` varchar(255) NOT NULL, `role_id` bigint(20) unsigned DEFAULT NULL, `status` enum('active','inactive') NOT NULL DEFAULT 'active', `mfa_enabled` tinyint(1) NOT NULL DEFAULT 1, `mfa_email` varchar(190) DEFAULT NULL, `force_password_change` tinyint(1) NOT NULL DEFAULT 0, `password_changed_at` datetime DEFAULT NULL, `last_login_at` datetime DEFAULT NULL, `last_mfa_at` datetime DEFAULT NULL, `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), KEY `idx_users_role` (`role_id`), CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Data for `users` INSERT INTO `users` (`id`,`name`,`username`,`email`,`password_hash`,`role_id`,`status`,`mfa_enabled`,`mfa_email`,`force_password_change`,`password_changed_at`,`last_login_at`,`last_mfa_at`,`created_at`,`updated_at`) VALUES ('1','Super Admin','admin','klvishwa@smiththerm.com','$2y$10$QxDxTc0E1wdJVW1CGm.0Ae4lrsIbUWtbxWMDZdWPNgEFxF1vDRD0W','1','active','1','klvishwa@smiththerm.com','0','2026-05-05 23:29:21','2026-05-06 00:17:31','2026-05-06 00:17:31','2026-05-05 10:29:21','2026-05-06 00:16:44'); COMMIT; SET FOREIGN_KEY_CHECKS=1;