-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `gen`
--

-- --------------------------------------------------------

--
-- Table structure for table `alerts`
--

CREATE TABLE IF NOT EXISTS `alerts` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `alert_category_id` bigint UNSIGNED NOT NULL,
  `alert_title` varchar(500) COLLATE utf8mb3_unicode_ci NOT NULL,
  `alert_description` text COLLATE utf8mb3_unicode_ci NOT NULL,
  `alert_impact` text COLLATE utf8mb3_unicode_ci NOT NULL,
  `alert_action` text COLLATE utf8mb3_unicode_ci NOT NULL,
  `image` text COLLATE utf8mb3_unicode_ci,
  `lat` text COLLATE utf8mb3_unicode_ci NOT NULL,
  `long` text COLLATE utf8mb3_unicode_ci NOT NULL,
  `sevarity_level` enum('Low','Medium','High') COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` enum('Active','Inactive') COLLATE utf8mb3_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `language` varchar(30) COLLATE utf8mb3_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `alerts_alert_category_id_foreign` (`alert_category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Dumping data for table `alerts`
--

INSERT INTO `alerts` (`id`, `alert_category_id`, `alert_title`, `alert_description`, `alert_impact`, `alert_action`, `image`, `lat`, `long`, `sevarity_level`, `status`, `created_at`, `updated_at`, `language`) VALUES
(2, 4, 'Thunderstorm in Punjab', 'Heavy thunderstorm is likely to develop over western areas of Punjab, posing a significant risk to the districts of Layyah, Bhakkar, Taunsa, DG Khan, and Rajanpur.', 'Storms may unleash torrential rainfall, with amounts ranging from 100-200 mm in isolated areas, leading to widespread rainfall across Western, Central, and Southern Punjab over the next 48-72 hours.', 'Stay informed by monitoring weather updates and alerts through local news, radio, or weather apps. Sign up for emergency notifications in your area. Trim trees and branches that could fall during the storm. Unplug electronics and appliances to prevent damage from power surges. Stay indoors and avoid windows during the storm. Do not use electrical equipment and corded phones during the storm. Avoid plumbing fixtures, as plumbing can conduct electricity. If driving, safely exit the roadway and park, avoiding trees and power lines.', 'MY4RZ2K6nzv3vfjltZbm2vhi71VIHvfxOiLIB5QP.jpg', '31.61007646450926', '71.08759007730457', 'High', 'Active', '2024-07-05 00:40:01', '2024-07-05 03:21:46', 'Urdu');

-- --------------------------------------------------------

--
-- Table structure for table `alert_categories`
--

CREATE TABLE IF NOT EXISTS `alert_categories` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_name` varchar(200) COLLATE utf8mb3_unicode_ci NOT NULL,
  `icon_one` text COLLATE utf8mb3_unicode_ci NOT NULL,
  `icon_two` text COLLATE utf8mb3_unicode_ci NOT NULL,
  `icon_three` text COLLATE utf8mb3_unicode_ci,
  `attachment` text COLLATE utf8mb3_unicode_ci,
  `status` tinyint(1) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Dumping data for table `alert_categories`
--

INSERT INTO `alert_categories` (`id`, `category_name`, `icon_one`, `icon_two`, `icon_three`, `attachment`, `status`, `created_at`, `updated_at`) VALUES
(4, 'Thunderstorm', '2FLtGMjNRyHb8gPeMzNhMPPrSgUlq0J8K3Z2G50w.png', 'JWgRie11VHkphfA1g75MgfDSnhQ8RimPPfghQmuU.png', NULL, NULL, 1, '2024-07-05 00:38:26', '2024-07-05 00:38:26');

-- --------------------------------------------------------

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE IF NOT EXISTS `failed_jobs` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `connection` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `queue` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `payload` longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `exception` longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `migrations`
--

CREATE TABLE IF NOT EXISTS `migrations` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES
(1, '2014_10_12_000000_create_users_table', 1),
(2, '2014_10_12_100000_create_password_resets_table', 1),
(3, '2014_10_12_200000_add_two_factor_columns_to_users_table', 1),
(4, '2019_08_19_000000_create_failed_jobs_table', 1),
(5, '2019_12_14_000001_create_personal_access_tokens_table', 1),
(6, '2022_01_12_072103_add_avatar_to_users_table', 1),
(7, '2022_01_12_130803_create_permission_tables', 1),
(8, '2024_06_29_062619_create_alert_categories_table', 2),
(9, '2024_06_29_063856_create_alerts_table', 3);

-- --------------------------------------------------------

--
-- Table structure for table `model_has_permissions`
--

CREATE TABLE IF NOT EXISTS `model_has_permissions` (
  `permission_id` bigint UNSIGNED NOT NULL,
  `model_type` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `model_id` bigint UNSIGNED NOT NULL,
  PRIMARY KEY (`permission_id`,`model_id`,`model_type`),
  KEY `model_has_permissions_model_id_model_type_index` (`model_id`,`model_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `model_has_roles`
--

CREATE TABLE IF NOT EXISTS `model_has_roles` (
  `role_id` bigint UNSIGNED NOT NULL,
  `model_type` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `model_id` bigint UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`,`model_id`,`model_type`),
  KEY `model_has_roles_model_id_model_type_index` (`model_id`,`model_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Dumping data for table `model_has_roles`
--

INSERT INTO `model_has_roles` (`role_id`, `model_type`, `model_id`) VALUES
(1, 'App\\Models\\User', 1),
(2, 'App\\Models\\User', 102);

-- --------------------------------------------------------

--
-- Table structure for table `permissions`
--

CREATE TABLE IF NOT EXISTS `permissions` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `guard_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permissions_name_guard_name_unique` (`name`,`guard_name`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Dumping data for table `permissions`
--

INSERT INTO `permissions` (`id`, `name`, `guard_name`, `created_at`, `updated_at`) VALUES
(1, 'user view', 'web', '2023-02-04 13:40:25', '2023-02-04 13:40:25'),
(2, 'user create', 'web', '2023-02-04 13:40:25', '2023-02-04 13:40:25'),
(3, 'user edit', 'web', '2023-02-04 13:40:25', '2023-02-04 13:40:25'),
(4, 'user delete', 'web', '2023-02-04 13:40:25', '2023-02-04 13:40:25'),
(5, 'role & permission view', 'web', '2023-02-04 13:40:25', '2023-02-04 13:40:25'),
(6, 'role & permission create', 'web', '2023-02-04 13:40:25', '2023-02-04 13:40:25'),
(7, 'role & permission edit', 'web', '2023-02-04 13:40:25', '2023-02-04 13:40:25'),
(8, 'role & permission delete', 'web', '2023-02-04 13:40:25', '2023-02-04 13:40:25'),
(9, 'test view', 'web', '2023-02-04 13:40:25', '2023-02-04 13:40:25'),
(10, 'alert category view', 'web', '2024-06-29 01:26:20', '2024-06-29 01:26:20'),
(11, 'alert category create', 'web', '2024-06-29 01:26:20', '2024-06-29 01:26:20'),
(12, 'alert category edit', 'web', '2024-06-29 01:26:20', '2024-06-29 01:26:20'),
(13, 'alert category delete', 'web', '2024-06-29 01:26:20', '2024-06-29 01:26:20'),
(14, 'alert view', 'web', '2024-06-29 01:38:56', '2024-06-29 01:38:56'),
(15, 'alert create', 'web', '2024-06-29 01:38:56', '2024-06-29 01:38:56'),
(16, 'alert edit', 'web', '2024-06-29 01:38:57', '2024-06-29 01:38:57'),
(17, 'alert delete', 'web', '2024-06-29 01:38:57', '2024-06-29 01:38:57');

-- --------------------------------------------------------

--
-- Table structure for table `roles`
--

CREATE TABLE IF NOT EXISTS `roles` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `guard_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_name_guard_name_unique` (`name`,`guard_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` (`id`, `name`, `guard_name`, `created_at`, `updated_at`) VALUES
(1, 'Admin', 'web', '2023-02-04 13:40:25', '2023-02-04 13:40:25'),
(2, 'Manager', 'web', '2024-06-29 03:35:20', '2024-06-29 03:35:20');

-- --------------------------------------------------------

--
-- Table structure for table `role_has_permissions`
--

CREATE TABLE IF NOT EXISTS `role_has_permissions` (
  `permission_id` bigint UNSIGNED NOT NULL,
  `role_id` bigint UNSIGNED NOT NULL,
  PRIMARY KEY (`permission_id`,`role_id`),
  KEY `role_has_permissions_role_id_foreign` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Dumping data for table `role_has_permissions`
--

INSERT INTO `role_has_permissions` (`permission_id`, `role_id`) VALUES
(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),
(16, 1),
(17, 1),
(10, 2),
(11, 2),
(12, 2),
(14, 2),
(15, 2),
(16, 2),
(17, 2);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `alerts`
--
ALTER TABLE `alerts`
  ADD CONSTRAINT `alerts_alert_category_id_foreign` FOREIGN KEY (`alert_category_id`) REFERENCES `alert_categories` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

--
-- Constraints for table `model_has_permissions`
--
ALTER TABLE `model_has_permissions`
  ADD CONSTRAINT `model_has_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `model_has_roles`
--
ALTER TABLE `model_has_roles`
  ADD CONSTRAINT `model_has_roles_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `role_has_permissions`
--
ALTER TABLE `role_has_permissions`
  ADD CONSTRAINT `role_has_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `role_has_permissions_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE;
COMMIT;

