-- phpMyAdmin SQL Dump -- version 5.2.1 -- https://www.phpmyadmin.net/ -- -- Host: localhost:3306 -- Generation Time: Jun 20, 2026 at 03:15 PM -- Server version: 10.6.19-MariaDB-log -- PHP Version: 8.1.34 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `techpale_babicha` -- -- -------------------------------------------------------- -- -- Table structure for table `actions` -- CREATE TABLE `actions` ( `id` bigint(20) UNSIGNED NOT NULL, `action_name` varchar(100) NOT NULL, `action_code` varchar(50) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `login_audit_logs` -- CREATE TABLE `login_audit_logs` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `login_time` datetime NOT NULL, `logout_time` datetime DEFAULT NULL, `total_active_seconds` int(11) DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `user_agent` varchar(500) DEFAULT NULL, `status` enum('success','failed') NOT NULL DEFAULT 'success' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `modules` -- CREATE TABLE `modules` ( `id` bigint(20) UNSIGNED NOT NULL, `module_name` varchar(100) NOT NULL, `module_code` varchar(50) NOT NULL, `created_at` datetime DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `otp_logins` -- CREATE TABLE `otp_logins` ( `id` bigint(20) UNSIGNED NOT NULL, `phone` varchar(15) NOT NULL, `otp_hash` varchar(255) NOT NULL, `attempt_count` int(11) NOT NULL DEFAULT 0, `is_verified` tinyint(1) NOT NULL DEFAULT 0, `expires_at` datetime NOT NULL, `created_at` datetime DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `refresh_tokens` -- CREATE TABLE `refresh_tokens` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `token_hash` varchar(255) NOT NULL, `active_role_id` bigint(20) UNSIGNED DEFAULT NULL, `device_info` varchar(300) DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `is_revoked` tinyint(1) NOT NULL DEFAULT 0, `expires_at` datetime NOT NULL, `created_at` datetime DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `roles` -- CREATE TABLE `roles` ( `id` bigint(20) UNSIGNED NOT NULL, `role_name` varchar(150) NOT NULL, `role_code` varchar(50) NOT NULL, `is_system_role` tinyint(1) NOT NULL DEFAULT 0, `created_at` datetime DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `roles` -- INSERT INTO `roles` (`id`, `role_name`, `role_code`, `is_system_role`, `created_at`) VALUES (1, '', 'admin', 0, '2026-06-20 15:05:18'), (2, '', 'customer', 0, '2026-06-20 15:05:18'), (3, '', 'manager', 0, '2026-06-20 15:05:18'); -- -------------------------------------------------------- -- -- Table structure for table `role_module_permissions` -- CREATE TABLE `role_module_permissions` ( `id` bigint(20) UNSIGNED NOT NULL, `role_id` bigint(20) UNSIGNED NOT NULL, `module_id` bigint(20) UNSIGNED NOT NULL, `action_id` bigint(20) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `social_logins` -- CREATE TABLE `social_logins` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `provider` enum('google','facebook') NOT NULL, `provider_user_id` varchar(150) NOT NULL, `provider_email` varchar(200) DEFAULT NULL, `access_token` text DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `id` bigint(20) UNSIGNED NOT NULL, `name` varchar(150) NOT NULL, `email` varchar(200) DEFAULT NULL, `phone` varchar(15) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, `profile_pic` varchar(500) DEFAULT NULL, `is_super_admin` tinyint(1) NOT NULL DEFAULT 0, `status` enum('active','inactive','blocked') NOT NULL DEFAULT 'active', `last_login_at` datetime DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `users` -- INSERT INTO `users` (`id`, `name`, `email`, `phone`, `password`, `profile_pic`, `is_super_admin`, `status`, `last_login_at`, `created_at`, `updated_at`) VALUES (1, 'Super Admin', 'admin@example.com', '9999999999', '$2a$10$vI8aWBnW3fID.ZQ4/zo1G.q1lRps.9cGLcZEiGDMVr5yUP1KUOYTa', NULL, 0, 'active', NULL, '2026-06-20 15:05:18', '2026-06-20 15:05:18'), (2, 'Test Customer', 'customer@example.com', '9876543210', NULL, NULL, 0, 'active', NULL, '2026-06-20 15:05:18', '2026-06-20 15:05:18'); -- -------------------------------------------------------- -- -- Table structure for table `user_roles` -- CREATE TABLE `user_roles` ( `id` bigint(20) UNSIGNED NOT NULL, `user_id` bigint(20) UNSIGNED NOT NULL, `role_id` bigint(20) UNSIGNED NOT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `assigned_at` datetime DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- Dumping data for table `user_roles` -- INSERT INTO `user_roles` (`id`, `user_id`, `role_id`, `is_active`, `assigned_at`) VALUES (1, 1, 1, 1, '2026-06-20 15:05:18'), (2, 2, 2, 1, '2026-06-20 15:05:18'); -- -- Indexes for dumped tables -- -- -- Indexes for table `actions` -- ALTER TABLE `actions` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `action_code` (`action_code`); -- -- Indexes for table `login_audit_logs` -- ALTER TABLE `login_audit_logs` ADD PRIMARY KEY (`id`), ADD KEY `idx_user_id` (`user_id`); -- -- Indexes for table `modules` -- ALTER TABLE `modules` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `module_code` (`module_code`); -- -- Indexes for table `otp_logins` -- ALTER TABLE `otp_logins` ADD PRIMARY KEY (`id`), ADD KEY `idx_phone` (`phone`); -- -- Indexes for table `refresh_tokens` -- ALTER TABLE `refresh_tokens` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`), ADD KEY `active_role_id` (`active_role_id`); -- -- Indexes for table `roles` -- ALTER TABLE `roles` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `role_code` (`role_code`); -- -- Indexes for table `role_module_permissions` -- ALTER TABLE `role_module_permissions` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uniq_role_module_action` (`role_id`,`module_id`,`action_id`), ADD KEY `module_id` (`module_id`), ADD KEY `action_id` (`action_id`); -- -- Indexes for table `social_logins` -- ALTER TABLE `social_logins` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uniq_provider_account` (`provider`,`provider_user_id`), ADD KEY `user_id` (`user_id`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `email` (`email`), ADD UNIQUE KEY `phone` (`phone`); -- -- Indexes for table `user_roles` -- ALTER TABLE `user_roles` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uniq_user_role` (`user_id`,`role_id`), ADD KEY `role_id` (`role_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `actions` -- ALTER TABLE `actions` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `login_audit_logs` -- ALTER TABLE `login_audit_logs` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `modules` -- ALTER TABLE `modules` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `otp_logins` -- ALTER TABLE `otp_logins` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `refresh_tokens` -- ALTER TABLE `refresh_tokens` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `roles` -- ALTER TABLE `roles` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT for table `role_module_permissions` -- ALTER TABLE `role_module_permissions` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `social_logins` -- ALTER TABLE `social_logins` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- AUTO_INCREMENT for table `user_roles` -- ALTER TABLE `user_roles` MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- Constraints for dumped tables -- -- -- Constraints for table `login_audit_logs` -- ALTER TABLE `login_audit_logs` ADD CONSTRAINT `login_audit_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `refresh_tokens` -- ALTER TABLE `refresh_tokens` ADD CONSTRAINT `refresh_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `refresh_tokens_ibfk_2` FOREIGN KEY (`active_role_id`) REFERENCES `roles` (`id`) ON DELETE SET NULL; -- -- Constraints for table `role_module_permissions` -- ALTER TABLE `role_module_permissions` ADD CONSTRAINT `role_module_permissions_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `role_module_permissions_ibfk_2` FOREIGN KEY (`module_id`) REFERENCES `modules` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `role_module_permissions_ibfk_3` FOREIGN KEY (`action_id`) REFERENCES `actions` (`id`) ON DELETE CASCADE; -- -- Constraints for table `social_logins` -- ALTER TABLE `social_logins` ADD CONSTRAINT `social_logins_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE; -- -- Constraints for table `user_roles` -- ALTER TABLE `user_roles` ADD CONSTRAINT `user_roles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `user_roles_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;