-- Adminer 4.8.1 MySQL 8.0.37-0ubuntu0.22.04.3 dump

SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';


CREATE TABLE `bathroom_types` (
  `id` int NOT NULL AUTO_INCREMENT,
  `bathroom_id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_d0617825e2d5aa33b72cf260a90` (`bathroom_id`),
  CONSTRAINT `FK_d0617825e2d5aa33b72cf260a90` FOREIGN KEY (`bathroom_id`) REFERENCES `bathrooms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `bathrooms` (
  `id` int NOT NULL AUTO_INCREMENT,
  `site_id` int DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(10) NOT NULL,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `rating` int NOT NULL DEFAULT '0',
  `bathroomTypes` json NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_6935e7fa1496ae9791470e2db5` (`code`),
  KEY `FK_e039cf7b63f010f46cc7021eca0` (`site_id`),
  CONSTRAINT `FK_e039cf7b63f010f46cc7021eca0` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `feedbacks` (
  `id` int NOT NULL AUTO_INCREMENT,
  `site_id` int NOT NULL,
  `rating` enum('Very Poor','Poor','Average','Good','Excellent') NOT NULL DEFAULT 'Average',
  `bathroom_id` int NOT NULL,
  `toilet_type` enum('Male','Female','All Gender','Accessible') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'Male',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `FK_0546d794ea85a54ec6be2224c20` (`site_id`),
  KEY `FK_89968641e4d0d99207d82788cd1` (`bathroom_id`),
  CONSTRAINT `FK_0546d794ea85a54ec6be2224c20` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`),
  CONSTRAINT `FK_89968641e4d0d99207d82788cd1` FOREIGN KEY (`bathroom_id`) REFERENCES `bathrooms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `notifications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `body` longtext,
  `user_id` int DEFAULT NULL,
  `seen` enum('seen','unseen') NOT NULL DEFAULT 'unseen',
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `user_type` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `resource` (
  `id` int NOT NULL AUTO_INCREMENT,
  `originalName` varchar(255) DEFAULT NULL,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `uid` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `type` varchar(255) DEFAULT NULL,
  `size` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_e6349ecf05ea1e0a1c6ddba88d` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `resource` (`id`, `originalName`, `status`, `uid`, `url`, `type`, `size`) VALUES
(1,	'floor_cleaning.png',	'Active',	'jdu0ey3oewTV',	'https://api-kiosk.springmounttech.au/public/resources/images/floor_cleaning.png',	'image/png',	'364857'),
(2,	'bashin_cleaning.png',	'Active',	'jdu11y3oewTV',	'https://api-kiosk.springmounttech.au/public/resources/images/bashin_cleaning.png',	'image/png',	'364857'),
(3,	'shoap_refill.png',	'Active',	'jdu1123oewTV',	'https://api-kiosk.springmounttech.au/public/resources/images/shoap_refill.png',	'image/png',	'364857'),
(4,	'toilet_cleaning.png',	'Active',	'jdu2123oewTV',	'https://api-kiosk.springmounttech.au/public/resources/images/toilet_cleaning.png',	'image/png',	'364857');


CREATE TABLE `services` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `avatar` int DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_019d74f7abcdcb5a0113010cb0` (`name`),
  UNIQUE KEY `REL_35da7ee9799a1039d9cd43b9b9` (`avatar`),
  CONSTRAINT `FK_35da7ee9799a1039d9cd43b9b9b` FOREIGN KEY (`avatar`) REFERENCES `resource` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `services` (`id`, `name`, `avatar`, `created_at`, `updated_at`) VALUES
(1,	'Soap Refill',	3,	'2024-04-05 12:34:54',	'2024-04-05 12:34:54'),
(2,	'Basin Clean',	2,	'2024-04-05 12:34:54',	'2024-04-05 12:34:54'),
(3,	'Floor Cleaning',	1,	'2024-04-05 12:34:54',	'2024-04-05 12:34:54'),
(4,	'Toilet Cleaning',	4,	'2024-04-05 12:34:54',	'2024-04-05 12:34:54');

CREATE TABLE `sites` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `latitude` varchar(255) DEFAULT NULL,
  `longitude` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `avatarId` int DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_7a7dbd4513de54a315d97e1a7d` (`name`),
  UNIQUE KEY `REL_f4fc834b8c50de905174eb95f9` (`avatarId`),
  CONSTRAINT `FK_f4fc834b8c50de905174eb95f9e` FOREIGN KEY (`avatarId`) REFERENCES `resource` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `sites_services` (
  `servicesId` int NOT NULL,
  `sitesId` int NOT NULL,
  PRIMARY KEY (`servicesId`,`sitesId`),
  KEY `IDX_e423751873841757182a65f212` (`servicesId`),
  KEY `IDX_71ffd6db167b100de8a66b9d35` (`sitesId`),
  CONSTRAINT `FK_71ffd6db167b100de8a66b9d35b` FOREIGN KEY (`sitesId`) REFERENCES `sites` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_e423751873841757182a65f212b` FOREIGN KEY (`servicesId`) REFERENCES `services` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `tasks` (
  `id` int NOT NULL AUTO_INCREMENT,
  `site_id` int NOT NULL,
  `employee_id` int DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `progress` enum('Pending','Assigned','Accepted','In Progress','Completed','Rejected','Incomplete') NOT NULL DEFAULT 'Pending',
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `service_id` int NOT NULL,
  `bathroom_id` int NOT NULL,
  `toilet_type` enum('Male','Female','All Gender','Accessible') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'Male',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `entry_time` datetime DEFAULT NULL,
  `complete_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_1c866e0c96d58ffc40f876934a4` (`employee_id`),
  KEY `FK_e19820760cd36e8fa8ae3d0abc4` (`site_id`),
  KEY `FK_e386ab36d7390d85584cf50ffe5` (`service_id`),
  KEY `FK_f6da924249daf93bcfaa1b78560` (`bathroom_id`),
  CONSTRAINT `FK_1c866e0c96d58ffc40f876934a4` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`),
  CONSTRAINT `FK_e19820760cd36e8fa8ae3d0abc4` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`),
  CONSTRAINT `FK_e386ab36d7390d85584cf50ffe5` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`),
  CONSTRAINT `FK_f6da924249daf93bcfaa1b78560` FOREIGN KEY (`bathroom_id`) REFERENCES `bathrooms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

