-- Standalone User Dashboard Auth Database Backup
-- Database: `smitht8_users`
-- Created: 2026-05-06 00:36:05

START TRANSACTION;


-- --------------------------------------------------------
-- Table structure for `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`

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`

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`

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`

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`

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`

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`

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`

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`

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`

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`

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`

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`

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;
