--
-- Host: localhost    Database: health_audit
-- ------------------------------------------------------


--
-- Current Database: `health_audit`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `health_audit` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;

USE `health_audit`;

--
-- Table structure for table `_prisma_migrations`
--

CREATE TABLE `_prisma_migrations` (
  `id` varchar(36) NOT NULL,
  `checksum` varchar(64) NOT NULL,
  `finished_at` datetime(3) DEFAULT NULL,
  `migration_name` varchar(255) NOT NULL,
  `logs` text DEFAULT NULL,
  `rolled_back_at` datetime(3) DEFAULT NULL,
  `started_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `applied_steps_count` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `_prisma_migrations`
--

INSERT INTO `_prisma_migrations` VALUES ('0e626391-7320-4a79-8d1d-39234db4a115','d807f2faaefaeff8dae8567eefff28d4990eabb9b6014716bd3f2d87f90a42c8','2025-06-10 21:51:26.993','20250610215124_update_document_category_relation',NULL,NULL,'2025-06-10 21:51:26.886',1),('105d0b64-22b4-40c7-a11f-2d6829dab13c','e6ed015a72bd89bf95bdc4d3d7f0abaddf71eb2d1ee3c868861f3b85d1e1994f','2025-05-30 07:02:38.627','20250525093900_add_regulatory_models',NULL,NULL,'2025-05-30 07:02:38.581',1),('2b68dad0-d6bb-47d4-bc86-88cd3c8940c9','37f5673e137027c61f9046b9328462eedfb9f95c55bb281511028df3bdb2374b','2025-06-28 18:27:47.093','20250628182746_add_finding_severity_and_status_tables',NULL,NULL,'2025-06-28 18:27:47.062',1),('3cae9ca9-3b05-4950-98f2-728650e0655f','352b8df9b965b477029fbc41371a193b819975db2cc2d9b2a7570c2115f48d3b','2025-05-30 07:02:38.750','20250528075000_add_timeline_to_findings',NULL,NULL,'2025-05-30 07:02:38.745',1),('46994780-a3d2-455c-b736-d509f6b08f25','3e372644092952a39f03301a4d3b82a370c84b0aea79ef7309ff6435b5da6f30','2025-05-30 07:02:38.738','20250525100933_add_hospital_relations_to_schedule_tables',NULL,NULL,'2025-05-30 07:02:38.629',1),('54f4989a-f3a0-4cb7-b6ef-8dccb508eeca','321dc7d3124f221728d6e2b7dcd3b3d787f442143bac2bccbbe201b895f40b46','2025-06-28 18:54:07.970','20250628185405_add_document_status_table',NULL,NULL,'2025-06-28 18:54:07.912',1),('80a7bcce-52fc-4d64-b93f-32992539d566','28fe5a3e2424cd0e66fbd59696d1092d1e312fe8efe8c74c037b2165958e2b6d','2025-05-30 07:02:38.744','20250527094607_add_resolution_date_to_findings',NULL,NULL,'2025-05-30 07:02:38.739',1),('97e91740-af8c-4c0b-a817-283acc367ff8','2cb82cd2713481b3bfb6b12397a6a0c634c46491666c8ae07213badb9d0697e9','2025-05-30 09:07:01.083','20250530090659_add_departments_model',NULL,NULL,'2025-05-30 09:07:00.965',1),('a6671113-fd72-4f23-b10f-4965d76b9bad','445c7f1dafa248ff66b19fc633950ccd610c21e23e4df5161a55b133322ac04e','2025-05-30 07:02:38.758','20250529_add_required_to_standards',NULL,NULL,'2025-05-30 07:02:38.752',1),('ac30c26b-1017-4e5d-ac0f-e2627a9fd423','7524d2cb2d3d00e775e7dddfb6da0d050eb02e0c0658bcd792a88659c0825d36','2025-06-28 10:33:53.134','20250628103350_add_policy_and_report_type_tables',NULL,NULL,'2025-06-28 10:33:53.052',1),('c22c32c9-4521-41dc-b0c8-dc4cb6f3efaf','3d35010fd725af13f88b6ddf6fa32f586523a20de23898bae50894e8dd3a8071','2025-05-30 07:02:38.813','20250530062448_add_staff_compliance_model',NULL,NULL,'2025-05-30 07:02:38.759',1),('c49499f6-7f7e-4bc2-b1f5-233549e24d36','174801eadea743b8601ca71b208c76c07642d8c516a48119efa99aa47747c72b','2025-05-30 07:02:38.580','20250525075747_add_document_models',NULL,NULL,'2025-05-30 07:02:38.364',1),('d29bb076-46d2-4c81-b52b-383afbb814c7','22ef1904539bb30e9d6a55d7bb3e582af52cd01bfabf5df49a0695c015081e62','2025-06-10 20:45:31.993','20250610204529_make_document_fields_optional',NULL,NULL,'2025-06-10 20:45:31.907',1);

--
-- Table structure for table `accounts`
--

CREATE TABLE `accounts` (
  `id` varchar(191) NOT NULL,
  `userId` int(11) NOT NULL,
  `type` varchar(191) NOT NULL,
  `provider` varchar(191) NOT NULL,
  `providerAccountId` varchar(191) NOT NULL,
  `refresh_token` text DEFAULT NULL,
  `access_token` text DEFAULT NULL,
  `expires_at` int(11) DEFAULT NULL,
  `token_type` varchar(191) DEFAULT NULL,
  `scope` varchar(191) DEFAULT NULL,
  `id_token` text DEFAULT NULL,
  `session_state` varchar(191) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `accounts_provider_providerAccountId_key` (`provider`,`providerAccountId`),
  KEY `accounts_userId_fkey` (`userId`),
  CONSTRAINT `accounts_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `accounts`
--


--
-- Table structure for table `audit_findings`
--

CREATE TABLE `audit_findings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `department` varchar(100) NOT NULL,
  `severity` enum('critical','high','medium','low') NOT NULL,
  `status` enum('open','in_progress','resolved') NOT NULL,
  `description` text DEFAULT NULL,
  `date_reported` datetime(3) NOT NULL,
  `hospital_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `resolution_date` datetime(3) DEFAULT NULL,
  `timeline` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `audit_findings_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `audit_findings_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `audit_findings`
--

INSERT INTO `audit_findings` VALUES (8,'Missing documentation for equipment maintenance','Facilities','high','resolved','Regular maintenance records for radiology equipment are missing for the last quarter.','2023-05-10 00:00:00.000',1,'2025-05-30 06:13:15','2025-06-10 03:12:58','2025-06-10 09:12:58.117',NULL),(9,'Staff training records incomplete','Human Resources','medium','resolved','Several staff members are missing required annual compliance training documentation.','2023-05-08 00:00:00.000',1,'2025-05-30 06:13:15','2025-06-10 03:14:00','2025-06-10 09:14:00.031',NULL),(10,'Patient data access logs not maintained','IT Security','critical','resolved','Access logs for patient records are not being properly maintained and reviewed.','2023-05-05 00:00:00.000',1,'2025-05-30 06:13:15','2025-06-10 03:12:45','2025-06-10 09:12:44.990',NULL),(11,'Medication storage temperature log inconsistent','Pharmacy','medium','resolved','Temperature logs for medication storage were not consistently maintained.','2023-05-03 00:00:00.000',1,'2025-05-30 06:13:15','2025-05-30 06:13:15',NULL,NULL),(12,'Emergency evacuation plan outdated','Safety','high','resolved','The emergency evacuation plan has not been updated to reflect recent building renovations.','2023-05-02 00:00:00.000',1,'2025-05-30 06:13:15','2025-06-10 03:12:51','2025-06-10 09:12:51.773',NULL),(13,'Incomplete informed consent documentation','Legal','high','in_progress','Several patient files have incomplete informed consent documentation.','2023-05-12 00:00:00.000',2,'2025-05-30 06:13:15','2025-05-30 06:13:15',NULL,NULL),(14,'Expired supplies found in storage','Inventory','medium','resolved','Multiple expired medical supplies were found during inventory audit.','2023-05-01 00:00:00.000',2,'2025-05-30 06:13:15','2025-05-30 06:13:15',NULL,NULL),(15,'Unauthorized access to patient records detected','IT Security','critical','resolved','System logs indicate multiple instances of unauthorized access to patient records from external IP addresses.','2023-06-15 00:00:00.000',1,'2025-05-30 06:13:28','2025-06-10 03:13:51','2025-06-10 09:13:51.300',NULL),(16,'Fire suppression system failure in server room','Facilities','critical','resolved','The automated fire suppression system in the main server room failed multiple safety tests.','2023-06-10 00:00:00.000',2,'2025-05-30 06:13:28','2025-06-10 02:52:12','2025-06-10 08:52:10.785',NULL),(17,'Medication administration errors in Pediatrics','Nursing','high','resolved','Multiple incidents of incorrect medication dosing were reported in the pediatric department over the past month.','2023-06-08 00:00:00.000',1,'2025-05-30 06:13:28','2025-06-10 03:12:28','2025-06-10 09:12:22.910',NULL),(18,'Outdated patient consent forms in use','Legal','high','resolved','Several departments are still using outdated patient consent forms that do not comply with current regulations.','2023-06-05 00:00:00.000',2,'2025-05-30 06:13:28','2025-06-10 02:52:39','2025-06-10 08:52:39.313',NULL),(19,'Inconsistent patient ID verification process','Administration','medium','resolved','Staff members are not consistently following the required two-step patient identification process.','2023-06-03 00:00:00.000',1,'2025-05-30 06:13:28','2025-06-10 03:12:37','2025-06-10 09:12:37.789',NULL),(20,'Incomplete preventive maintenance records','Laboratory','medium','resolved','Preventive maintenance records for laboratory equipment are incomplete or missing for Q1 2023.','2023-05-28 00:00:00.000',2,'2025-05-30 06:13:28','2025-05-30 06:13:28',NULL,NULL),(21,'vxcvxcv','5f015fd5-d133-4e50-a6e9-00ee732a34fc','medium','in_progress','xcvxcv','2025-06-09 22:54:42.238',1,'2025-06-09 16:54:42','2025-06-28 12:46:52',NULL,'2025-07-03 00:00:00.000'),(22,'Finding Title 5','14273b58-4e9e-4432-8e4c-cde317c42704','high','resolved','Finding Title','2025-08-06 08:35:04.421',1,'2025-08-06 02:35:04','2025-08-06 02:35:11','2025-08-06 08:35:08.340','2025-08-05 00:00:00.000');

--
-- Table structure for table `audit_schedules`
--

CREATE TABLE `audit_schedules` (
  `id` varchar(191) NOT NULL,
  `title` varchar(200) NOT NULL,
  `date` datetime(3) NOT NULL,
  `department` varchar(100) NOT NULL,
  `auditor` varchar(100) NOT NULL,
  `status` varchar(50) NOT NULL,
  `hospital_id` int(11) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `updatedAt` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `audit_schedules_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `audit_schedules_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `audit_schedules`
--

INSERT INTO `audit_schedules` VALUES ('51370543-4886-4197-8449-7a4c995963cd','New audit','2025-06-11 00:00:00.000','Nursing','John Smith','scheduled',1,'2025-06-10 18:15:16.000','2025-06-10 18:15:16.000');

--
-- Table structure for table `compliance_data`
--

CREATE TABLE `compliance_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `compliance` double NOT NULL,
  `required` double NOT NULL,
  `hospital_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `compliance_data_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `compliance_data_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `compliance_data`
--

INSERT INTO `compliance_data` VALUES (17,'ISO 9001',87,90,1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(18,'HIPAA',94,90,1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(19,'JCI',78,90,1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(20,'GDPR',82,90,1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(21,'FDA 21 CFR',76,90,1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(22,'OSHA',88,90,1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(23,'ISO 27001',81,90,1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(24,'ISO 14001',79,85,1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(25,'ISO 9001',91,90,2,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(26,'HIPAA',89,90,2,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(27,'JCI',82,90,2,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(28,'GDPR',77,90,2,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(29,'FDA 21 CFR',84,90,2,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(30,'OSHA',92,90,2,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(31,'ISO 27001',86,90,2,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(32,'ISO 14001',81,85,2,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(33,'Klinicx Health solutions',65,90,1,'2025-06-11 08:25:57','2025-06-11 08:25:57'),(34,'Klinicx Health solutions 2',10,95,1,'2025-06-11 08:26:22','2025-06-11 08:26:22');

--
-- Table structure for table `compliance_policies`
--

CREATE TABLE `compliance_policies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `category` varchar(100) NOT NULL,
  `status` varchar(50) NOT NULL,
  `hospital_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `compliance_policies_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `compliance_policies_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `compliance_policies`
--

INSERT INTO `compliance_policies` VALUES (13,'Patient Data Access','Data Privacy Policies','Active',1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(14,'Electronic Records Security','Data Privacy Policies','Active',1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(15,'Data Breach Response','Data Privacy Policies','Active',1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(16,'Quality Assurance','Quality Management Policies','Active',1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(17,'Risk Management','Quality Management Policies','Review Due',1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(18,'Corrective Actions','Quality Management Policies','Active',1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(19,'Emergency Procedures','Facility Safety Policies','Active',1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(20,'Infection Control','Facility Safety Policies','Review Due',1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(21,'Equipment Safety','Facility Safety Policies','Update Required',1,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(22,'Patient Data Access','Data Privacy Policies','Active',2,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(23,'Electronic Records Security','Data Privacy Policies','Review Due',2,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(24,'Emergency Procedures','Facility Safety Policies','Active',2,'2025-05-30 06:13:15','2025-05-30 06:13:15'),(25,'Klinicx Health solutions','Quality Management Policies','Review Due',1,'2025-06-28 04:28:16','2025-06-28 04:28:16'),(26,'Klinicx Health solutions','Facility Safety Policies','Review Due',1,'2025-06-28 04:37:35','2025-06-28 04:37:35'),(27,'Klinicx Health solutions','Facility Safety Policies','Update Required',1,'2025-06-28 04:39:11','2025-06-28 04:39:11'),(28,'Klinicx Health solutions','HR Policies','Review Due',1,'2025-06-28 12:20:23','2025-06-28 12:20:23'),(29,'Admin User','Facility Safety Policies','Inactive',1,'2025-06-28 12:20:36','2025-06-28 12:20:36'),(30,'Rizvi Mahmud Plabon','Quality Management Policies','Update Required',1,'2025-06-28 12:20:56','2025-06-28 12:20:56'),(31,'Klinicx Health solutions','Information Security Policies','Update Required',1,'2025-06-28 12:21:13','2025-06-28 12:21:13');

--
-- Table structure for table `compliance_reports`
--

CREATE TABLE `compliance_reports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `type` varchar(100) NOT NULL,
  `file_url` varchar(255) DEFAULT NULL,
  `hospital_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `compliance_reports_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `compliance_reports_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `compliance_reports`
--

INSERT INTO `compliance_reports` VALUES (9,'HIPAA Quarterly Assessment - Q1 2023','compliance_summary',NULL,2,'2025-05-30 06:13:15'),(10,'Staff Training Completion Report - May 2023','training_status',NULL,2,'2025-05-30 06:13:15');

--
-- Table structure for table `departments`
--

CREATE TABLE `departments` (
  `id` varchar(191) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `hospital_id` int(11) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `updatedAt` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `departments_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `departments_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `departments`
--

INSERT INTO `departments` VALUES ('07075d6b-26ee-4b88-9945-5a198bf58d04','Human Resources','Staffing and personnel management',1,'2025-05-30 09:13:25.045','2025-05-30 09:13:25.045'),('08e4825a-7a54-4aef-b7ec-82218cd85a44','Surgery','Surgical services and operating rooms',1,'2025-05-30 09:13:25.022','2025-05-30 09:13:25.022'),('0fc0845f-0b0d-4111-a9f4-f7bc022ecbee','Emergency','Emergency medicine and urgent care',2,'2025-05-30 09:13:25.084','2025-05-30 09:13:25.084'),('14273b58-4e9e-4432-8e4c-cde317c42704','Facilities','Building maintenance and operations',1,'2025-05-30 09:13:25.025','2025-05-30 09:13:25.025'),('188ca242-3a76-4ac8-9c32-62ba7e0bdb52','Pharmacy','Medication management and pharmacy services',2,'2025-05-30 09:13:25.069','2025-05-30 09:13:25.069'),('1df1bdc4-a055-4f02-bbf6-04ef2c49b9d5','Administration','Hospital administration and management',2,'2025-05-30 09:13:25.105','2025-05-30 09:13:25.105'),('1f92c881-133b-45b4-8557-9ad1c18ebb09','Surgery','Surgical services and operating rooms',2,'2025-05-30 09:13:25.089','2025-05-30 09:13:25.089'),('2c57794f-945a-4822-a5de-77dbae8d0b86','Facilities','Building maintenance and operations',2,'2025-05-30 09:13:25.093','2025-05-30 09:13:25.093'),('2ffb17d1-d1f3-4cbc-b286-dc62cb063623','Nursing','Nursing and patient care staff',1,'2025-05-30 09:13:24.996','2025-05-30 09:13:24.996'),('36902b25-0923-4583-a256-d171ac4c6ed1','Quality Management','Quality assurance and improvement',1,'2025-05-30 09:13:25.051','2025-05-30 09:13:25.051'),('3de115ef-2301-446c-9732-f1259d5e012f','Human Resources','Staffing and personnel management',2,'2025-05-30 09:13:25.108','2025-05-30 09:13:25.108'),('43f79340-e14d-4564-aeba-e64267295d12','Laboratory','Medical testing and laboratory services',1,'2025-05-30 09:13:25.010','2025-05-30 09:13:25.010'),('5f015fd5-d133-4e50-a6e9-00ee732a34fc','Information Technology','IT systems and technical support',1,'2025-05-30 09:13:25.033','2025-05-30 09:13:25.033'),('8d591d52-398c-4c07-9c6b-6d60b214aa7e','Quality Management','Quality assurance and improvement',2,'2025-05-30 09:13:25.116','2025-05-30 09:13:25.116'),('a30dc8ab-d726-4315-beb9-f9f68fdf339f','Infection Control','Infection prevention and control measures',1,'2025-05-30 09:13:25.054','2025-05-30 09:13:25.054'),('abcd9016-9f5d-41d9-96c0-47c1daa1745a','Pharmacy','Medication management and pharmacy services',1,'2025-05-30 09:13:25.001','2025-05-30 09:13:25.001'),('b1a6adc6-2b85-4c71-aa9f-fa29826c7ea9','Emergency','Emergency medicine and urgent care',1,'2025-05-30 09:13:25.020','2025-05-30 09:13:25.020'),('b5a8034c-7068-4522-860e-4d6579576d72','Information Technology','IT systems and technical support',2,'2025-05-30 09:13:25.099','2025-05-30 09:13:25.099'),('c0c06d84-eccf-405e-875c-e201f6f805a4','Infection Control','Infection prevention and control measures',2,'2025-05-30 09:13:25.120','2025-05-30 09:13:25.120'),('c12ca384-c3ec-4804-b2ca-8bfb37dd6f8e','Radiology','Imaging and diagnostic services',1,'2025-05-30 09:13:25.017','2025-05-30 09:13:25.017'),('cae5bdb7-2d1f-418c-9049-327aceee2762','Laboratory','Medical testing and laboratory services',2,'2025-05-30 09:13:25.074','2025-05-30 09:13:25.074'),('d0c8fd3a-745f-4917-8fbd-5e21e9e06769','Nursing','Nursing and patient care staff',2,'2025-05-30 09:13:25.060','2025-05-30 09:13:25.060'),('d76a3720-729d-41df-8c71-24c282da6ed8','Administration','Hospital administration and management',1,'2025-05-30 09:13:25.037','2025-05-30 09:13:25.037'),('e4165e32-784c-46ae-afa4-82d00b83dbdb','Radiology','Imaging and diagnostic services',2,'2025-05-30 09:13:25.078','2025-05-30 09:13:25.078');

--
-- Table structure for table `document_categories`
--

CREATE TABLE `document_categories` (
  `id` varchar(191) NOT NULL,
  `name` varchar(100) NOT NULL,
  `icon` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `hospital_id` int(11) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `updatedAt` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `document_categories_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `document_categories_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `document_categories`
--

INSERT INTO `document_categories` VALUES ('11c7383f-3c25-4516-885e-91c9413e8a1a','Forms','FileText','Standard forms for patient care and administration',2,'2025-05-30 12:13:25.420','2025-05-30 12:13:25.420'),('1df361f8-e177-4cf0-8dd1-be3950a8e665','Forms','FileText','Standard forms for patient care and administration',1,'2025-05-30 12:13:25.335','2025-05-30 12:13:25.335'),('32c3af17-0acc-48ac-8e31-a49373ee4288','Training Materials','GraduationCap','Staff training resources and compliance education',1,'2025-05-30 12:13:25.342','2025-05-30 12:13:25.342'),('5e4b962e-f5f3-4922-8535-a2cd1b6af00a','Policies','FileText','Organizational policies for compliance and governance',2,'2025-05-30 12:13:25.410','2025-05-30 12:13:25.410'),('618d2823-3220-49ca-9fc0-2cda2e45a17f','Procedures','FileCheck','Step-by-step procedures for clinical operations',1,'2025-05-30 12:13:25.325','2025-05-30 12:13:25.325'),('71f44d2b-be74-4af5-af0b-1c680defa2b6','Policies','FileText','Organizational policies for compliance and governance',1,'2025-05-30 12:13:25.319','2025-05-30 12:13:25.319'),('9cfe71ea-62f5-434f-a47a-4b85ed47d93f','Reports','BarChart','Audit reports and compliance assessments',1,'2025-05-30 12:13:25.356','2025-05-30 12:13:25.356'),('a1bac78e-4b5e-4428-b729-91571d2bf5be','Training Materials','GraduationCap','Staff training resources and compliance education',2,'2025-05-30 12:13:25.425','2025-05-30 12:13:25.425'),('b2dc1001-d686-40e0-bf19-e755b834b86a','Reports','BarChart','Audit reports and compliance assessments',2,'2025-05-30 12:13:25.433','2025-05-30 12:13:25.433'),('b483e825-a14c-46c9-ac61-fe0b7987b10f','Certifications','Award','Certifications and accreditations',1,'2025-05-30 12:13:25.350','2025-05-30 12:13:25.350'),('b701090c-6d62-4854-9ec8-201ddaecd8f6','Procedures','FileCheck','Step-by-step procedures for clinical operations',2,'2025-05-30 12:13:25.415','2025-05-30 12:13:25.415'),('c8f015fd-19cc-4f7f-9497-7589c63c343e','Klinicx Health solutions','FileText','',1,'2025-06-10 19:25:43.178','2025-06-10 19:25:43.178'),('dc6bb4ad-da0a-4ff2-a6f5-2739d31f2dba','Certifications','Award','Certifications and accreditations',2,'2025-05-30 12:13:25.429','2025-05-30 12:13:25.429'),('dd371f61-457b-4268-b8e4-04060ffd3b1a','New category','BarChart','',1,'2025-06-10 18:31:00.107','2025-06-10 18:31:00.107');

--
-- Table structure for table `document_statuses`
--

CREATE TABLE `document_statuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(50) NOT NULL,
  `label` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `color_class` varchar(100) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `document_statuses_value_key` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `document_statuses`
--

INSERT INTO `document_statuses` VALUES (1,'active','Active','Document is active and current','text-green-600 border-green-300',1,1,'2025-06-28 12:54:49','2025-06-28 12:54:49'),(2,'review','Review Needed','Document requires review or updates','text-amber-600 border-amber-300',2,1,'2025-06-28 12:54:49','2025-06-28 12:54:49'),(3,'archived','Archived','Document is archived and no longer current','text-gray-600 border-gray-300',3,1,'2025-06-28 12:54:49','2025-06-28 12:54:49'),(4,'draft','Draft','Document is in draft status','text-blue-600 border-blue-300',4,1,'2025-06-28 12:54:49','2025-06-28 12:54:49');

--
-- Table structure for table `documents`
--

CREATE TABLE `documents` (
  `id` varchar(191) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `fileName` varchar(255) DEFAULT NULL,
  `fileUrl` varchar(255) DEFAULT NULL,
  `fileSize` int(11) DEFAULT NULL,
  `fileType` varchar(50) DEFAULT NULL,
  `status` varchar(20) NOT NULL,
  `department` varchar(100) DEFAULT NULL,
  `updatedBy` varchar(100) NOT NULL,
  `category_id` varchar(191) NOT NULL,
  `hospital_id` int(11) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `updatedAt` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `documents_hospital_id_fkey` (`hospital_id`),
  KEY `documents_category_id_fkey` (`category_id`),
  CONSTRAINT `documents_category_id_fkey` FOREIGN KEY (`category_id`) REFERENCES `document_categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `documents_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `documents`
--

INSERT INTO `documents` VALUES ('024e3a63-5f6c-4060-a769-4ef0d0757ed5','Document Title 5','userIcon.png','/documents/1/67622413-351a-450c-815e-a53b00cacc93-userIcon.png',13470,'image/png','active','Nursing','superadmin@audit.com','1df361f8-e177-4cf0-8dd1-be3950a8e665',1,'2025-08-06 08:33:55.293','2025-08-06 08:33:55.293'),('2d47e1bb-ed0c-4942-a6a9-9ae6dd037bdb','Infection Control Protocol 2023','infection_control_2023.pdf',NULL,NULL,NULL,'active','Clinical Operations','admin@example.com','b701090c-6d62-4854-9ec8-201ddaecd8f6',2,'2025-05-30 12:13:25.444','2025-05-30 12:13:25.444'),('3f30bb5a-8bb8-42a3-aeb5-aae4b0693948','Patient Data Access Request Form','patient_data_access.pdf',NULL,NULL,NULL,'active','Legal','admin@example.com','11c7383f-3c25-4516-885e-91c9413e8a1a',2,'2025-05-30 12:13:25.449','2025-05-30 12:13:25.449'),('41915ee5-d4a3-44b9-a3db-6f40eace6b55','Another new doc','invoice-68454c77ef3bac6bfa2ad7e0.pdf','/documents/1/b8aaa1ab-f909-4a9e-8a08-244f9eb25462-invoice-68454c77ef3bac6bfa2ad7e0.pdf',6330,'application/pdf','active','Department','superadmin@audit.com','1df361f8-e177-4cf0-8dd1-be3950a8e665',1,'2025-06-10 21:59:19.434','2025-06-10 21:59:19.434'),('43ef94c0-8eb6-49d9-a133-1f8fb76ccfd8','HIPAA Compliance Manual v3.2','hipaa_compliance_manual.pdf',NULL,NULL,NULL,'active','Clinical Operations','admin@example.com','5e4b962e-f5f3-4922-8535-a2cd1b6af00a',2,'2025-05-30 12:13:25.439','2025-05-30 12:13:25.439'),('7339fd39-593e-42c6-99ef-db984be398c3','New doc','invoice-68454e07966c6815fc463f54.pdf','/documents/1/8ba88943-5060-4b89-8208-b1e02424d6be-invoice-68454e07966c6815fc463f54.pdf',5654,'application/pdf','review','Nursing','superadmin@audit.com','1df361f8-e177-4cf0-8dd1-be3950a8e665',1,'2025-06-10 21:58:39.937','2025-06-10 21:58:39.937'),('86279f5e-e736-43c3-9677-6a46efeb3898','hhfghfh543345','Screenshot from 2025-06-21 02-57-22.png','/documents/1/50bb1564-2223-4545-9cfb-36ab823bf9e3-Screenshot from 2025-06-21 02-57-22.png',1597244,'image/png','review','Nursing','superadmin@audit.com','1df361f8-e177-4cf0-8dd1-be3950a8e665',1,'2025-06-28 18:52:02.696','2025-06-28 18:52:02.696'),('950a9945-1d29-48c7-9910-54a67b84014d','Medical Waste Disposal Procedure','waste_disposal.pdf',NULL,NULL,NULL,'review','Environmental Services','admin@example.com','b701090c-6d62-4854-9ec8-201ddaecd8f6',2,'2025-05-30 12:13:25.454','2025-05-30 12:13:25.454'),('df8783a0-aeeb-46bd-8105-bc24b2b3ea3c','Patient Privacy Policy','patient_privacy_policy.pdf',NULL,NULL,NULL,'active','Legal','admin@example.com','5e4b962e-f5f3-4922-8535-a2cd1b6af00a',2,'2025-05-30 12:13:25.462','2025-05-30 12:13:25.462'),('e2f1db07-5d68-4a81-9ebe-3b43a432c38c','kjhkhk','write_report_enhanced.php','/documents/1/d00beffe-98e8-4839-8229-89934c557c4a-write_report_enhanced.php',294410,'application/x-php','active',NULL,'superadmin@audit.com','71f44d2b-be74-4af5-af0b-1c680defa2b6',1,'2025-06-28 19:38:46.479','2025-06-28 19:38:46.479');

--
-- Table structure for table `finding_severities`
--

CREATE TABLE `finding_severities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(50) NOT NULL,
  `label` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `color_class` varchar(100) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `finding_severities_value_key` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `finding_severities`
--

INSERT INTO `finding_severities` VALUES (1,'critical','Critical','Critical issues requiring immediate attention','bg-red-700 text-white',1,1,'2025-06-28 12:28:25','2025-06-28 12:28:25'),(2,'high','High','High priority issues requiring prompt attention','bg-red-500 text-white',2,1,'2025-06-28 12:28:25','2025-06-28 12:28:25'),(3,'medium','Medium','Medium priority issues requiring attention','bg-amber-500 text-white',3,1,'2025-06-28 12:28:25','2025-06-28 12:28:25'),(4,'low','Low','Low priority issues for future consideration','bg-blue-500 text-white',4,1,'2025-06-28 12:28:25','2025-06-28 12:28:25');

--
-- Table structure for table `finding_statuses`
--

CREATE TABLE `finding_statuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(50) NOT NULL,
  `label` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `color_class` varchar(100) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `finding_statuses_value_key` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `finding_statuses`
--

INSERT INTO `finding_statuses` VALUES (1,'open','Open','Finding is open and requires action','text-gray-600 border-gray-300',1,1,'2025-06-28 12:28:25','2025-06-28 12:28:25'),(2,'in_progress','In Progress','Finding is being actively addressed','text-blue-600 border-blue-300',2,1,'2025-06-28 12:28:25','2025-06-28 12:28:25'),(3,'resolved','Resolved','Finding has been resolved','text-green-600 border-green-300',3,1,'2025-06-28 12:28:25','2025-06-28 12:28:25');

--
-- Table structure for table `hospitals`
--

CREATE TABLE `hospitals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `location` varchar(255) NOT NULL,
  `logo_url` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hospitals`
--

INSERT INTO `hospitals` VALUES (1,'City General Hospital','New York, NY','/images/hospitals/city-general.png','2025-05-30 01:02:43','2025-05-30 01:02:43'),(2,'County Medical Center','Los Angeles, CA','/images/hospitals/county-medical.png','2025-05-30 01:02:43','2025-05-30 01:02:43');

--
-- Table structure for table `policy_categories`
--

CREATE TABLE `policy_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(100) NOT NULL,
  `label` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `policy_categories_value_key` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `policy_categories`
--

INSERT INTO `policy_categories` VALUES (1,'Data Privacy Policies','Data Privacy Policies','Policies related to data protection and privacy',1,'2025-06-28 04:34:26','2025-06-28 04:34:26'),(2,'Quality Management Policies','Quality Management Policies','Policies for maintaining quality standards',1,'2025-06-28 04:34:26','2025-06-28 04:34:26'),(3,'Facility Safety Policies','Facility Safety Policies','Policies ensuring facility safety and security',1,'2025-06-28 04:34:26','2025-06-28 04:34:26'),(4,'HR Policies','HR Policies','Human resources and personnel policies',1,'2025-06-28 04:34:26','2025-06-28 04:34:26'),(5,'Information Security Policies','Information Security Policies','Policies for protecting information systems and data',1,'2025-06-28 04:34:26','2025-06-28 04:34:26');

--
-- Table structure for table `policy_statuses`
--

CREATE TABLE `policy_statuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(50) NOT NULL,
  `label` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `policy_statuses_value_key` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `policy_statuses`
--

INSERT INTO `policy_statuses` VALUES (1,'Active','Active','Policy is currently active and in effect',1,'2025-06-28 04:34:26','2025-06-28 04:34:26'),(2,'Review Due','Review Due','Policy requires review and potential updates',1,'2025-06-28 04:34:26','2025-06-28 04:34:26'),(3,'Update Required','Update Required','Policy needs to be updated before it can be active',1,'2025-06-28 04:34:26','2025-06-28 04:34:26'),(4,'Inactive','Inactive','Policy is no longer in effect',1,'2025-06-28 04:34:26','2025-06-28 04:34:26');

--
-- Table structure for table `regulatory_deadlines`
--

CREATE TABLE `regulatory_deadlines` (
  `id` varchar(191) NOT NULL,
  `title` varchar(200) NOT NULL,
  `dueDate` datetime(3) NOT NULL,
  `authority` varchar(100) NOT NULL,
  `criticalLevel` varchar(20) NOT NULL,
  `status` varchar(50) NOT NULL,
  `assignedTo` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `hospital_id` int(11) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `updatedAt` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `regulatory_deadlines_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `regulatory_deadlines_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `regulatory_deadlines`
--


--
-- Table structure for table `regulatory_news`
--

CREATE TABLE `regulatory_news` (
  `id` varchar(191) NOT NULL,
  `title` varchar(255) NOT NULL,
  `source` varchar(100) NOT NULL,
  `date` datetime(3) NOT NULL,
  `category` varchar(100) NOT NULL,
  `content` text DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `isNew` tinyint(1) NOT NULL DEFAULT 1,
  `hospital_id` int(11) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `updatedAt` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `regulatory_news_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `regulatory_news_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `regulatory_news`
--

INSERT INTO `regulatory_news` VALUES ('0af795d9-0065-4edc-9f1c-9f1c53dadf01','New patient privacy requirements announced','Regional Health Authority','2023-05-09 00:00:00.000','Privacy','The Regional Health Authority has announced new patient privacy requirements that will come into effect next quarter.',NULL,1,1,'2025-05-30 12:13:21.820','2025-05-30 12:13:21.820'),('0e838ffb-dc0d-4c25-9cd8-310ba7f0cbca','FDA releases new guidance on medical device reporting','U.S. FDA','2023-05-10 00:00:00.000','Regulatory Guidance','The FDA has published new guidance on medical device reporting requirements, with updated timelines and processes.','https://www.fda.gov/medical-devices',0,NULL,'2025-05-30 12:13:21.801','2025-05-30 12:13:21.801'),('9291a429-8c53-4ce4-8ae8-c2314509cd2f','JCI announces 7th Edition Standard updates','Joint Commission International','2023-05-12 00:00:00.000','Standards Update','Joint Commission International has announced significant updates to their 7th Edition Standards, focusing on patient safety and quality improvement.','https://www.jointcommissioninternational.org',1,NULL,'2025-05-30 12:13:21.793','2025-05-30 12:13:21.793'),('a05bc865-5493-4dd3-a8c1-8631baa7cccd','WHO publishes updated patient safety framework','World Health Organization','2023-05-05 00:00:00.000','Standards Update','The World Health Organization has released an updated framework for patient safety, emphasizing a systems approach to reducing errors.','https://www.who.int/health-topics/patient-safety',1,NULL,'2025-05-30 12:13:21.816','2025-05-30 12:13:21.816'),('e28f742c-183f-4240-8dd0-30b21dad30bc','GDPR enforcement actions increasing in healthcare sector','EU Commission','2023-05-07 00:00:00.000','Enforcement','The EU Commission has reported an increase in GDPR enforcement actions specifically targeting healthcare organizations.','https://ec.europa.eu/info/law/law-topic/data-protection_en',0,NULL,'2025-05-30 12:13:21.808','2025-05-30 12:13:21.808');

--
-- Table structure for table `regulatory_standards`
--

CREATE TABLE `regulatory_standards` (
  `id` varchar(191) NOT NULL,
  `name` varchar(100) NOT NULL,
  `category` varchar(100) NOT NULL,
  `relevance` varchar(50) NOT NULL,
  `lastUpdated` datetime(3) NOT NULL,
  `description` text DEFAULT NULL,
  `hospital_id` int(11) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `updatedAt` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `regulatory_standards_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `regulatory_standards_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `regulatory_standards`
--

INSERT INTO `regulatory_standards` VALUES ('011aa0d9-a329-4fd5-9578-207598af2b4e','FDA 21 CFR Part 11','Electronic Records','medium','2023-02-18 00:00:00.000','Regulations on electronic records and electronic signatures.',1,'2025-05-30 12:13:21.772','2025-05-30 12:13:21.772'),('1b602d86-326b-47b2-a113-b314f8bfcc70','HIPAA','Data Privacy','high','2023-03-22 00:00:00.000','Health Insurance Portability and Accountability Act - Sets standards for protecting sensitive patient data.',1,'2025-05-30 12:13:21.762','2025-05-30 12:13:21.762'),('2a497ff5-b6a0-4f82-8e5d-197145c8a445','HIPAA','Data Privacy','high','2023-04-15 00:00:00.000','Health Insurance Portability and Accountability Act - Sets standards for protecting sensitive patient data.',2,'2025-05-30 12:13:21.784','2025-05-30 12:13:21.784'),('383badf9-4df7-488a-a4b7-9c759c890562','JCI 7th Edition','Healthcare Quality','high','2023-05-05 00:00:00.000','Joint Commission International standards for hospital accreditation.',1,'2025-05-30 12:13:21.769','2025-05-30 12:13:21.769'),('3c83ea2c-12ad-4b8e-b707-3bf06964781c','JCI 7th Edition','Healthcare Quality','high','2023-05-20 00:00:00.000','Joint Commission International standards for hospital accreditation.',2,'2025-05-30 12:13:21.787','2025-05-30 12:13:21.787'),('6302fc7d-a79a-4e1d-9a32-59269e156971','GDPR','Data Protection','medium','2023-04-10 00:00:00.000','General Data Protection Regulation - EU regulation on data protection and privacy.',1,'2025-05-30 12:13:21.766','2025-05-30 12:13:21.766'),('8f64e4a5-5a36-4f4e-9f2f-7a0bdb984ce7','ISO 9001:2015','Quality Management','medium','2023-02-10 00:00:00.000','Specifies requirements for a quality management system. Helps organizations ensure they meet customer and regulatory requirements.',2,'2025-05-30 12:13:21.780','2025-05-30 12:13:21.780'),('ccc68bf0-99d7-4538-97d8-9db30127ed18','ISO 9001:2015','Quality Management','high','2023-01-15 00:00:00.000','Specifies requirements for a quality management system. Helps organizations ensure they meet customer and regulatory requirements.',1,'2025-05-30 12:13:21.756','2025-05-30 12:13:21.756'),('fbf7a50a-6038-459c-bb08-715848604e1f','OSHA','Workplace Safety','low','2023-03-10 00:00:00.000','Occupational Safety and Health Administration standards for workplace safety.',1,'2025-05-30 12:13:21.775','2025-05-30 12:13:21.775');

--
-- Table structure for table `report_types`
--

CREATE TABLE `report_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(100) NOT NULL,
  `label` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `report_types_value_key` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `report_types`
--

INSERT INTO `report_types` VALUES (1,'compliance_summary','Compliance Summary','Summary of overall compliance status',1,'2025-06-28 04:34:26','2025-06-28 04:34:26'),(2,'training_status','Training Status','Status of staff training and certifications',1,'2025-06-28 04:34:26','2025-06-28 04:34:26'),(3,'audit_findings','Audit Findings','Results and findings from audits',1,'2025-06-28 04:34:26','2025-06-28 04:34:26'),(4,'risk_assessment','Risk Assessment','Analysis of potential risks and mitigation strategies',1,'2025-06-28 04:34:26','2025-06-28 04:34:26');

--
-- Table structure for table `resource_allocations`
--

CREATE TABLE `resource_allocations` (
  `id` varchar(191) NOT NULL,
  `resourceName` varchar(100) NOT NULL,
  `role` varchar(100) NOT NULL,
  `allocatedTo` varchar(200) NOT NULL,
  `startDate` datetime(3) NOT NULL,
  `endDate` datetime(3) NOT NULL,
  `utilizationPercentage` int(11) NOT NULL,
  `department` varchar(100) NOT NULL,
  `hospital_id` int(11) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `updatedAt` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `resource_allocations_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `resource_allocations_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `resource_allocations`
--


--
-- Table structure for table `sessions`
--

CREATE TABLE `sessions` (
  `id` varchar(191) NOT NULL,
  `sessionToken` varchar(191) NOT NULL,
  `userId` int(11) NOT NULL,
  `expires` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sessions_sessionToken_key` (`sessionToken`),
  KEY `sessions_userId_fkey` (`userId`),
  CONSTRAINT `sessions_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `sessions`
--


--
-- Table structure for table `staff_compliance`
--

CREATE TABLE `staff_compliance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `department` varchar(100) NOT NULL,
  `staff_count` int(11) NOT NULL,
  `compliance_rate` double NOT NULL,
  `notes` text DEFAULT NULL,
  `hospital_id` int(11) NOT NULL,
  `created_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `updated_at` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `staff_compliance_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `staff_compliance_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `staff_compliance`
--

INSERT INTO `staff_compliance` VALUES (1,'Nursing',45,88,'Sample compliance data for Nursing department',1,'2025-05-30 07:03:43.923','2025-05-30 07:03:43.923'),(2,'Emergency',32,92,'Sample compliance data for Emergency department',1,'2025-05-30 07:03:43.933','2025-05-30 07:03:43.933'),(3,'Surgery',28,85,'Sample compliance data for Surgery department',1,'2025-05-30 07:03:43.938','2025-05-30 07:03:43.938'),(4,'Radiology',15,95,'Sample compliance data for Radiology department',1,'2025-05-30 07:03:43.941','2025-05-30 07:03:43.941'),(5,'Laboratory',20,90,'Sample compliance data for Laboratory department',1,'2025-05-30 07:03:43.944','2025-05-30 07:03:43.944'),(6,'Nursing',45,88,'Sample compliance data for Nursing department',2,'2025-05-30 07:03:43.947','2025-05-30 07:03:43.947'),(7,'Emergency',32,92,'Sample compliance data for Emergency department',2,'2025-05-30 07:03:43.951','2025-05-30 07:03:43.951'),(8,'Surgery',28,85,'Sample compliance data for Surgery department',2,'2025-05-30 07:03:43.956','2025-05-30 07:03:43.956'),(9,'Radiology',15,95,'Sample compliance data for Radiology department',2,'2025-05-30 07:03:43.961','2025-05-30 07:03:43.961'),(10,'Laboratory',20,90,'Sample compliance data for Laboratory department',2,'2025-05-30 07:03:43.964','2025-05-30 07:03:43.964');

--
-- Table structure for table `standard_compliance`
--

CREATE TABLE `standard_compliance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `standard` varchar(100) NOT NULL,
  `category` varchar(100) NOT NULL,
  `compliance` double NOT NULL,
  `last_assessed` datetime(3) NOT NULL,
  `hospital_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `required_threshold` double NOT NULL DEFAULT 90,
  PRIMARY KEY (`id`),
  KEY `standard_compliance_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `standard_compliance_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `standard_compliance`
--

INSERT INTO `standard_compliance` VALUES (9,'HIPAA Privacy Rule','Data Privacy',94,'2023-05-28 00:00:00.000',1,'2025-05-30 06:13:15','2025-05-30 06:13:15',90),(10,'ISO 9001:2015','Quality Management',82,'2023-06-10 00:00:00.000',1,'2025-05-30 06:13:15','2025-05-30 06:13:15',90),(11,'JCI 7th Edition','Patient Safety',68,'2023-06-15 00:00:00.000',1,'2025-05-30 06:13:15','2025-05-30 06:13:15',90),(12,'HIPAA Security Rule','Data Security',88,'2023-05-15 00:00:00.000',1,'2025-05-30 06:13:15','2025-05-30 06:13:15',90),(13,'GDPR','Data Privacy',76,'2023-04-20 00:00:00.000',1,'2025-05-30 06:13:15','2025-05-30 06:13:15',90),(14,'HIPAA Privacy Rule','Data Privacy',91,'2023-05-28 00:00:00.000',2,'2025-05-30 06:13:15','2025-05-30 06:13:15',90),(15,'ISO 9001:2015','Quality Management',87,'2023-06-10 00:00:00.000',2,'2025-05-30 06:13:15','2025-05-30 06:13:15',90),(16,'JCI 7th Edition','Patient Safety',78,'2023-06-15 00:00:00.000',2,'2025-05-30 06:13:15','2025-05-30 06:13:15',90);

--
-- Table structure for table `training_events`
--

CREATE TABLE `training_events` (
  `id` varchar(191) NOT NULL,
  `title` varchar(200) NOT NULL,
  `date` datetime(3) NOT NULL,
  `instructor` varchar(100) NOT NULL,
  `location` varchar(100) NOT NULL,
  `department` varchar(100) NOT NULL,
  `duration` varchar(50) NOT NULL,
  `capacity` int(11) NOT NULL,
  `enrolled` int(11) NOT NULL,
  `required` tinyint(1) NOT NULL DEFAULT 0,
  `hospital_id` int(11) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `updatedAt` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `training_events_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `training_events_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `training_events`
--

INSERT INTO `training_events` VALUES ('27daffb1-bda4-4bc3-b088-5989228d7b0f','sasadasd','2025-06-11 00:00:00.000','New instructor','Dhaka','d76a3720-729d-41df-8c71-24c282da6ed8','2 hours',10,0,0,1,'2025-06-10 11:22:06.724','2025-06-10 11:22:06.724'),('e12dd9d0-aa47-423f-88d9-2c0ec841e8dd','fvgfgdfg','2025-06-11 00:00:00.000','New instructor','Dhaka','43f79340-e14d-4564-aeba-e64267295d12','2 hours',3,0,0,1,'2025-06-10 11:40:55.469','2025-06-10 11:40:55.469'),('f53c37a6-315c-4d2a-828a-523e2014230b','sasadasd','2025-06-11 00:00:00.000','New instructor','Dhaka','d76a3720-729d-41df-8c71-24c282da6ed8','2 hours',10,0,0,1,'2025-06-10 11:22:06.658','2025-06-10 11:22:06.658');

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `auth0_id` varchar(100) DEFAULT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(100) NOT NULL,
  `role` enum('hospital','super_admin') NOT NULL DEFAULT 'hospital',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `hospital_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_username_key` (`username`),
  UNIQUE KEY `users_email_key` (`email`),
  KEY `users_hospital_id_fkey` (`hospital_id`),
  CONSTRAINT `users_hospital_id_fkey` FOREIGN KEY (`hospital_id`) REFERENCES `hospitals` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES (1,NULL,'superadmin','$2b$10$L7jXhh2oFBOLE2ogVFtq9egus3X.7Q9FpYsYH1rFXzsYG.QLZ0jDG','superadmin@audit.com','super_admin','2025-05-30 01:02:43',NULL),(2,NULL,'hospital','$2b$10$L7jXhh2oFBOLE2ogVFtq9egus3X.7Q9FpYsYH1rFXzsYG.QLZ0jDG','hospital@audit.com','hospital','2025-05-30 01:02:44',1);

--
-- Table structure for table `verification_tokens`
--

CREATE TABLE `verification_tokens` (
  `identifier` varchar(191) NOT NULL,
  `token` varchar(191) NOT NULL,
  `expires` datetime(3) NOT NULL,
  UNIQUE KEY `verification_tokens_token_key` (`token`),
  UNIQUE KEY `verification_tokens_identifier_token_key` (`identifier`,`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `verification_tokens`
--



