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

START TRANSACTION;



--
-- Database: `primeaut_practicehub_db`
--

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

--
-- Table structure for table `action_register`
--

CREATE TABLE `action_register` (
  `id` int(11) NOT NULL,
  `source_id` int(11) DEFAULT NULL,
  `type` enum('clinical','feedback','business risk','follow-up') NOT NULL,
  `action_date` date NOT NULL,
  `description` text NOT NULL,
  `status` enum('Pending','Complete') NOT NULL DEFAULT 'Pending',
  `created_by` varchar(255) NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `blood_body_substance_exposures`
--

CREATE TABLE `blood_body_substance_exposures` (
  `id` int(11) NOT NULL COMMENT 'Primary key',
  `incident_report_id` int(11) DEFAULT NULL COMMENT 'References incident_reports.id',
  `exposed_person_surname` varchar(255) DEFAULT NULL,
  `exposed_person_first_name` varchar(255) DEFAULT NULL,
  `incident_date` date NOT NULL,
  `incident_time` time NOT NULL,
  `substance_type` enum('Blood','Blood product','Wound exudate','Urine','Faeces','Vomit','Saliva','Semen','Sputum','Other') NOT NULL,
  `other_substance` varchar(255) DEFAULT NULL,
  `is_blood_stained` tinyint(1) DEFAULT NULL,
  `source_infection_status` enum('Yes','No','Assumed but not confirmed','Source or material unknown') DEFAULT NULL,
  `protective_equipment` mediumtext DEFAULT NULL,
  `hepatitis_vaccination_status` enum('Vaccinated / course commenced','Natural immunity','Not vaccinated','Unknown') DEFAULT NULL,
  `body_areas_exposed` mediumtext DEFAULT NULL,
  `sharps_type` enum('Hollow bone needle','Glass object','Sharp metal object','Other') DEFAULT NULL,
  `other_sharps` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL COMMENT 'Indexed for user reference',
  `protocol_acknowledged` tinyint(1) NOT NULL DEFAULT 0,
  `protective_other_details` varchar(255) DEFAULT NULL,
  `body_area_other_details` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores details of blood/body substance exposure incidents';

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

--
-- Table structure for table `business_risk_reports`
--

CREATE TABLE `business_risk_reports` (
  `id` int(11) NOT NULL,
  `reported_by` varchar(255) NOT NULL,
  `incident_date` date NOT NULL,
  `incident_time` time NOT NULL,
  `who_involved` varchar(50) DEFAULT NULL,
  `has_witness` varchar(10) DEFAULT NULL,
  `witness_details` text DEFAULT NULL,
  `incident_classification` varchar(100) DEFAULT NULL,
  `act_of_aggression_details` varchar(50) DEFAULT NULL,
  `equipment_failure_details` varchar(50) DEFAULT NULL,
  `breach_of_privacy_details` text DEFAULT NULL,
  `fall_details` text DEFAULT NULL,
  `complaint_details` text DEFAULT NULL,
  `theft_missing_property_details` text DEFAULT NULL,
  `emotional_distress_details` text DEFAULT NULL,
  `near_miss_mistake_details` text DEFAULT NULL,
  `event_description` text DEFAULT NULL,
  `assistance_type` varchar(100) DEFAULT NULL,
  `assistance_other_details` text DEFAULT NULL,
  `action_to_address` text DEFAULT NULL,
  `person_responsible_action` varchar(255) DEFAULT NULL,
  `date_due` date DEFAULT NULL,
  `comments` text DEFAULT NULL,
  `action_prevent_recurrence` text DEFAULT NULL,
  `date_action_reviewed` date DEFAULT NULL,
  `outcome_review` text DEFAULT NULL,
  `person_responsible_review` varchar(255) DEFAULT NULL,
  `date_due_review` date DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL,
  `medical_treatment` text DEFAULT NULL,
  `investigation_details` text DEFAULT NULL,
  `debriefing_details` text DEFAULT NULL,
  `onsite_details` text DEFAULT NULL,
  `referred_details` text DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `status` enum('New','Pending','Under Investigation','Action Taken','Resolved') NOT NULL DEFAULT 'New',
  `practice_meeting` varchar(10) DEFAULT NULL,
  `report_medical_defence` varchar(10) DEFAULT NULL,
  `mdo_report_date` date DEFAULT NULL,
  `mdo_report_time` time DEFAULT NULL,
  `immediate_actions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`immediate_actions`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `business_risk_reports`
--

INSERT INTO `business_risk_reports` (`id`, `reported_by`, `incident_date`, `incident_time`, `who_involved`, `has_witness`, `witness_details`, `incident_classification`, `act_of_aggression_details`, `equipment_failure_details`, `breach_of_privacy_details`, `fall_details`, `complaint_details`, `theft_missing_property_details`, `emotional_distress_details`, `near_miss_mistake_details`, `event_description`, `assistance_type`, `assistance_other_details`, `action_to_address`, `person_responsible_action`, `date_due`, `comments`, `action_prevent_recurrence`, `date_action_reviewed`, `outcome_review`, `person_responsible_review`, `date_due_review`, `created_by`, `created_at`, `updated_at`, `medical_treatment`, `investigation_details`, `debriefing_details`, `onsite_details`, `referred_details`, `deleted_at`, `status`, `practice_meeting`, `report_medical_defence`, `mdo_report_date`, `mdo_report_time`, `immediate_actions`) VALUES
(36, 'Kamran', '2025-07-10', '18:18:00', 'Staff', 'Yes', 'svferhtrhher', 'Act of Aggression', 'Verbal', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'frgrthtrhtrh', NULL, NULL, 'gwegweg', 'agdgafddg', '2025-07-10', 'gadfgth', 'adfgag', '2025-07-10', 'gasdgtry', 'ywegdf', '2025-07-10', 1, '2025-07-10 12:19:45', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'New', NULL, 'No', NULL, NULL, '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"efwergrgegwr\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null},{\"action\":\"Assistance\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":\"CATT\",\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null},{\"action\":\"Investigation\",\"medical_treatment_details\":null,\"investigation_details\":\",olo9l9l9l7\",\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]'),
(37, 'Mubashir', '2025-07-10', '18:22:00', 'Staff', 'No', NULL, 'Complaint', NULL, NULL, NULL, NULL, 'fqeasdfasdf', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, '2025-07-10 12:22:34', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'New', 'No', 'No', NULL, NULL, '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"fasdvzzcvzc\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]'),
(38, 'jhjsadaudbf', '2025-07-10', '18:23:00', 'Staff', 'No', NULL, 'Equipment Failure', NULL, 'Billing', NULL, NULL, NULL, NULL, NULL, NULL, 'dgrwegwe', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, '2025-07-10 12:23:56', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'New', 'No', 'No', NULL, NULL, '[{\"action\":\"Investigation\",\"medical_treatment_details\":null,\"investigation_details\":\"wergssdgsdg\",\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]'),
(39, 'kjasdgfuaf', '2025-07-11', '19:24:00', 'Staff', 'No', NULL, 'Theft/Missing Property', NULL, NULL, NULL, NULL, NULL, 'asdfsdfsf', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, '2025-07-10 12:24:43', NULL, NULL, NULL, NULL, NULL, NULL, '2025-07-10 14:10:34', 'New', 'No', 'No', NULL, NULL, '[{\"action\":\"Assistance\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":\"Police\",\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]'),
(40, 'gghkjlkjlhjl', '2025-07-10', '18:43:00', 'Staff', 'Yes', 'hgjkgjkjhkgh', 'Fall', NULL, NULL, NULL, 'grtyetutu', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2025-07-10 12:44:27', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'New', 'No', 'No', NULL, NULL, '[{\"action\":\"Debriefing\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null},{\"action\":\"Medical Treatment\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]'),
(41, 'Mubashir', '2025-07-11', '14:02:00', 'Staff', 'Yes', 'hdfghdghgf', 'Breach of Privacy', NULL, NULL, 'hhdfghdfhfdh', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'hdfgfdh', 'gfdhdfhdf', '2025-07-11', 'hdfgdhf', 'hdfghfgdhfghdfhg', '2025-07-11', 'hdfhfghfdh', 'hfdghgdfgdh', '2025-07-11', 2, '2025-07-11 08:02:37', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'New', 'Yes', 'No', NULL, NULL, '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null},{\"action\":\"Medical Treatment\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]'),
(42, 'Mubashir', '2025-07-11', '15:23:00', 'Patient', 'Yes', 'dfsdsgsgdfs', 'Fall', NULL, NULL, NULL, 'gfsdfgsdg', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2025-07-11 08:24:07', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'New', 'No', 'No', NULL, NULL, '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null},{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"gfdgsfggs\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]'),
(43, 'Mubashir', '2025-07-11', '16:14:00', 'Staff', 'Yes', 'erwtrterter', 'Fall', NULL, NULL, NULL, 'werterretwer', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2025-07-11 10:14:56', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'New', 'Yes', 'No', NULL, NULL, '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"ewrtet\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null},{\"action\":\"Investigation\",\"medical_treatment_details\":null,\"investigation_details\":\"twerwtewt\",\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null},{\"action\":\"Assistance\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":\"Police\",\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]'),
(44, 'Mubashir', '2025-07-11', '18:20:00', 'Staff', 'Yes', 'fasdfsda', 'Fall', NULL, NULL, NULL, 'fsdfasdf', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, '2025-07-11 11:20:46', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'New', 'No', 'No', NULL, NULL, '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"fasdafsd\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]'),
(45, 'Mubashir', '2025-07-11', '19:13:00', 'Staff', 'Yes', 'dfasd', 'Breach of Privacy', NULL, NULL, 'fasdfsdf', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, '2025-07-11 12:13:37', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'New', NULL, 'No', NULL, NULL, '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"fasdfsdf\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null},{\"action\":\"Assistance\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":\"Police\",\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]');

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

--
-- Table structure for table `complaints`
--

CREATE TABLE `complaints` (
  `id` int(11) NOT NULL,
  `complainant_name` varchar(255) DEFAULT NULL,
  `complainant_email` varchar(255) NOT NULL,
  `complain_text` text NOT NULL,
  `submission_date` datetime NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `status` enum('New','Pending','Under Investigation','Action Taken','Resolved') DEFAULT 'New',
  `submitted_by` enum('Staff','Patient') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

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

--
-- Table structure for table `complaints_meetings`
--

CREATE TABLE `complaints_meetings` (
  `id` int(11) NOT NULL,
  `type` enum('clinical','feedback','business risk','follow-up','leave') NOT NULL,
  `source_id` int(11) DEFAULT NULL,
  `agenda_item` varchar(255) NOT NULL,
  `due_date` date NOT NULL,
  `meeting_date` date NOT NULL,
  `staff_emails` text NOT NULL,
  `status` enum('Not Discussed','Discuss') NOT NULL DEFAULT 'Not Discussed',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `complaints_meetings`
--

INSERT INTO `complaints_meetings` (`id`, `type`, `source_id`, `agenda_item`, `due_date`, `meeting_date`, `staff_emails`, `status`, `created_at`, `updated_at`, `deleted_at`) VALUES
(43, 'clinical', 32, 'xvfjghjghjhj', '2025-07-17', '2025-07-13', '', 'Not Discussed', '2025-07-10 10:05:10', '2025-07-11 12:26:31', NULL),
(44, 'business risk', 36, 'wgergwergweg', '2025-07-17', '2025-07-13', '', 'Discuss', '2025-07-10 12:19:45', '2025-07-11 12:26:45', NULL),
(45, 'clinical', 35, 'mubashirkamran', '2025-07-18', '2025-07-14', '', 'Not Discussed', '2025-07-11 08:01:07', '2025-07-11 08:01:07', NULL),
(46, 'business risk', 41, 'hdfghdfdhgfhdfhhdfghfgdhhf', '2025-07-18', '2025-07-14', '', 'Discuss', '2025-07-11 08:02:37', '2025-07-11 12:26:49', NULL),
(47, 'clinical', 39, 'gsdfgsdgsdgdfg', '2025-07-18', '2025-07-14', '', 'Discuss', '2025-07-11 08:48:20', '2025-07-12 09:30:50', NULL),
(48, 'business risk', 43, 'wergsfsdfgssds', '2025-07-18', '2025-07-14', '', 'Not Discussed', '2025-07-11 10:14:57', '2025-07-12 08:17:57', '2025-07-12 08:17:57'),
(49, 'clinical', 40, 'cvzfhgdfbzbcxb', '2025-07-18', '2025-07-14', '', 'Not Discussed', '2025-07-11 10:15:40', '2025-07-11 10:15:40', NULL),
(50, 'clinical', 42, 'fdsfasdf', '2025-07-18', '2025-07-14', '', 'Discuss', '2025-07-11 11:20:10', '2025-07-11 12:26:20', NULL),
(51, 'business risk', 45, 'fasdfsdf', '2025-07-12', '0000-00-00', 'mubashirkamranrana@gmail.com, mubashirkamranarana123@gmail.com', 'Discuss', '2025-07-11 12:13:55', '2025-07-11 12:26:22', NULL),
(52, 'follow-up', 49, 'Test', '2025-07-19', '2025-07-19', 'mubashirkamranrana@gmail.com', 'Not Discussed', '2025-07-12 09:19:21', '2025-07-12 09:19:21', NULL),
(53, 'follow-up', 45, 'sfafasdf', '2025-07-19', '2025-07-19', 'mubashirkamranrana@gmail.com', 'Not Discussed', '2025-07-12 10:04:08', '2025-07-12 10:04:08', NULL),
(54, 'follow-up', 53, 'gsdfgsdf', '2025-07-19', '2025-07-19', 'mubashirkamranrana@gmail.com', 'Not Discussed', '2025-07-12 10:24:58', '2025-07-12 10:24:58', NULL);

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

--
-- Table structure for table `feedback`
--

CREATE TABLE `feedback` (
  `id` int(11) NOT NULL,
  `feedback_date` date NOT NULL,
  `patient_name` varchar(100) NOT NULL,
  `feedback_text` text NOT NULL,
  `submission_date` datetime NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `submitted_by` enum('Staff','Patient') NOT NULL DEFAULT 'Patient',
  `patient_email` varchar(255) DEFAULT NULL,
  `minutes` text DEFAULT NULL,
  `investigation_findings` text DEFAULT NULL,
  `patient_id` int(11) DEFAULT NULL,
  `feedback_type` varchar(100) NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `status` enum('New','Pending','Under Investigation','Action Taken','Resolved') NOT NULL DEFAULT 'Pending'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `feedback`
--

INSERT INTO `feedback` (`id`, `feedback_date`, `patient_name`, `feedback_text`, `submission_date`, `created_by`, `submitted_by`, `patient_email`, `minutes`, `investigation_findings`, `patient_id`, `feedback_type`, `deleted_at`, `updated_at`, `status`) VALUES
(80, '2025-07-11', 'Kamran', 'dhdghjh', '2025-07-10 12:42:44', 2, 'Staff', 'mubashirkamranrana123@gmail.com', NULL, NULL, NULL, '', NULL, '2025-07-10 12:42:44', 'Pending'),
(81, '2025-07-11', 'Kamran', 'nfdghdfgdh', '2025-07-11 07:59:01', 2, 'Staff', 'mubashirkamranrana123@gmail.com', NULL, NULL, NULL, '', NULL, '2025-07-11 07:59:01', 'Pending'),
(82, '2025-07-11', 'Kamran', 'zxcccccc', '2025-07-11 10:15:54', 2, 'Staff', 'mubashirkamranrana123@gmail.com', NULL, NULL, NULL, '', NULL, '2025-07-11 10:15:54', 'Pending'),
(83, '2025-07-11', 'Kamran', 'sddgsdgdfsdf', '2025-07-11 10:32:54', 2, 'Staff', 'mubashirkamranrana123@gmail.com', NULL, NULL, NULL, '', NULL, '2025-07-11 10:32:54', 'Pending'),
(84, '0000-00-00', 'Mubashir', 'sdasdf', '2025-07-12 08:52:46', NULL, 'Patient', 'mubashirkamranrana123@gmail.com', NULL, NULL, NULL, '', NULL, '2025-07-12 08:52:46', 'Pending'),
(85, '0000-00-00', 'Mubashir', 'ewrter', '2025-07-12 13:54:18', NULL, 'Patient', 'mubashirkamranrana123@gmail.com', NULL, NULL, NULL, '', NULL, '2025-07-12 13:54:18', 'Pending');

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

--
-- Table structure for table `incident_reports`
--

CREATE TABLE `incident_reports` (
  `id` int(11) NOT NULL COMMENT 'Primary key',
  `reported_by` varchar(100) NOT NULL,
  `incident_date` date NOT NULL,
  `incident_time` time NOT NULL,
  `who_involved` varchar(50) DEFAULT NULL,
  `witness_details` mediumtext DEFAULT NULL,
  `incident_classification` varchar(100) DEFAULT NULL COMMENT 'Indexed for faster queries',
  `fall_details` mediumtext DEFAULT NULL,
  `exposure_subtype` varchar(50) DEFAULT NULL,
  `adverse_reaction_type` varchar(50) DEFAULT NULL,
  `breach_of_privacy_details` mediumtext DEFAULT NULL,
  `complaint_details` mediumtext DEFAULT NULL,
  `emotional_distress_details` mediumtext DEFAULT NULL,
  `near_miss_mistake_details` mediumtext DEFAULT NULL,
  `event_description` mediumtext DEFAULT NULL,
  `medical_treatment` mediumtext DEFAULT NULL,
  `assistance_police` tinyint(1) DEFAULT 0,
  `assistance_fire` tinyint(1) DEFAULT 0,
  `assistance_ambulance` tinyint(1) DEFAULT 0,
  `assistance_catt` tinyint(1) DEFAULT 0,
  `assistance_other` tinyint(1) DEFAULT 0,
  `assistance_other_details` mediumtext DEFAULT NULL,
  `debriefing_type` varchar(50) DEFAULT NULL,
  `debriefing_details` mediumtext DEFAULT NULL,
  `onsite_details` mediumtext DEFAULT NULL,
  `referred_details` mediumtext DEFAULT NULL,
  `practice_meeting` varchar(10) DEFAULT NULL,
  `report_medical_defence` varchar(10) DEFAULT NULL,
  `mdo_report_date` date DEFAULT NULL,
  `mdo_report_time` time DEFAULT NULL,
  `action_prevent_recurrence` mediumtext DEFAULT NULL,
  `date_action_reviewed` date DEFAULT NULL,
  `outcome_review` mediumtext DEFAULT NULL,
  `status` enum('New','Pending','Under Investigation','Actioned','Resolved') DEFAULT 'New',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL,
  `investigation_details` mediumtext DEFAULT NULL,
  `assistance_type` varchar(50) DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL COMMENT 'Indexed for soft deletes',
  `if_no_explanation` mediumtext DEFAULT NULL,
  `action_to_address` mediumtext DEFAULT NULL,
  `person_responsible_action` varchar(255) DEFAULT NULL,
  `date_due` date DEFAULT NULL,
  `comments` mediumtext DEFAULT NULL,
  `person_responsible_review` varchar(255) DEFAULT NULL,
  `date_due_review` date DEFAULT NULL,
  `has_witness` enum('Yes','No') NOT NULL DEFAULT 'No',
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `immediate_actions` longtext DEFAULT NULL COMMENT 'Stores JSON array of immediate actions',
  `exposed_person_surname` varchar(255) DEFAULT NULL,
  `exposed_person_first_name` varchar(255) DEFAULT NULL,
  `substance_type` enum('Blood','Blood product','Wound exudate','Urine','Faeces','Saliva','Vomit','Sputum','Other') DEFAULT NULL,
  `other_substance` varchar(255) DEFAULT NULL,
  `is_blood_stained` tinyint(1) DEFAULT NULL,
  `source_infection_status` enum('Yes','No','Assumed but not confirmed','Source refused testing','Unknown') DEFAULT NULL,
  `protective_equipment` mediumtext DEFAULT NULL,
  `hepatitis_vaccination_status` enum('Vaccinated / course commenced','Natural immunity','Vaccinated / course not commenced','Not vaccinated','Unknown') DEFAULT NULL,
  `body_areas_exposed` mediumtext DEFAULT NULL,
  `sharps_type` enum('Hollow bone needle','Glass object','Sharp metal object','Solid needle','Other') DEFAULT NULL,
  `other_sharps` varchar(255) DEFAULT NULL,
  `protocol_acknowledged` tinyint(1) NOT NULL DEFAULT 0,
  `protective_other_details` varchar(255) DEFAULT NULL,
  `body_area_other_details` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores clinical risk incident reports';

--
-- Dumping data for table `incident_reports`
--

INSERT INTO `incident_reports` (`id`, `reported_by`, `incident_date`, `incident_time`, `who_involved`, `witness_details`, `incident_classification`, `fall_details`, `exposure_subtype`, `adverse_reaction_type`, `breach_of_privacy_details`, `complaint_details`, `emotional_distress_details`, `near_miss_mistake_details`, `event_description`, `medical_treatment`, `assistance_police`, `assistance_fire`, `assistance_ambulance`, `assistance_catt`, `assistance_other`, `assistance_other_details`, `debriefing_type`, `debriefing_details`, `onsite_details`, `referred_details`, `practice_meeting`, `report_medical_defence`, `mdo_report_date`, `mdo_report_time`, `action_prevent_recurrence`, `date_action_reviewed`, `outcome_review`, `status`, `created_at`, `created_by`, `investigation_details`, `assistance_type`, `deleted_at`, `if_no_explanation`, `action_to_address`, `person_responsible_action`, `date_due`, `comments`, `person_responsible_review`, `date_due_review`, `has_witness`, `updated_at`, `immediate_actions`, `exposed_person_surname`, `exposed_person_first_name`, `substance_type`, `other_substance`, `is_blood_stained`, `source_infection_status`, `protective_equipment`, `hepatitis_vaccination_status`, `body_areas_exposed`, `sharps_type`, `other_sharps`, `protocol_acknowledged`, `protective_other_details`, `body_area_other_details`) VALUES
(30, 'Mubashir kamran', '2025-07-10', '13:29:00', 'Staff', 'fghdfgh', 'Fall', 'gdfsg', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 'No', '', NULL, NULL, NULL, NULL, NULL, 'Pending', '2025-07-10 07:30:10', 1, NULL, NULL, NULL, NULL, NULL, NULL, '2025-07-17', NULL, NULL, NULL, 'Yes', '2025-07-10 10:01:53', '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"gsdfgdgs\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]', NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL),
(31, 'Mubashir kamran', '2025-07-10', '15:39:00', 'Staff', NULL, 'Exposure to blood / body-substance', NULL, 'Needle stick', NULL, NULL, NULL, NULL, NULL, 'gsdfgsd', NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 'No', 'No', NULL, NULL, NULL, NULL, NULL, 'Pending', '2025-07-10 08:39:56', 1, NULL, NULL, NULL, NULL, NULL, NULL, '2025-07-17', NULL, NULL, NULL, 'No', '2025-07-11 11:32:09', '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"gdsfgsdg\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]', 'Kamran', 'Mubashir', 'Sputum', NULL, 1, 'Yes', '[\"Gloves, single layer\",\"Gloves, double layer\"]', '', '[\"Eyes\",\"Mouth\"]', '', NULL, 1, NULL, NULL),
(32, 'nmmmmmm', '2025-07-10', '17:04:00', 'Staff', 'fgsdreetyyerty', 'Fall', 'yeryt', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 'Yes', 'No', NULL, NULL, NULL, NULL, NULL, 'New', '2025-07-10 10:05:10', 1, NULL, NULL, NULL, NULL, NULL, NULL, '2025-07-17', NULL, NULL, NULL, 'Yes', '2025-07-10 10:05:10', '[{\"action\":\"Debriefing\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":\"On-site\",\"debriefing_onsite_details\":\"dhfghdfg\",\"debriefing_referred_details\":null}]', NULL, NULL, 'Blood', NULL, NULL, 'No', NULL, 'Unknown', NULL, '', NULL, 0, NULL, NULL),
(33, 'nmmmmmm', '2025-07-10', '16:58:00', 'Patient', NULL, 'Fall', 'dfsdasf', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 'No', NULL, NULL, NULL, NULL, NULL, NULL, 'New', '2025-07-10 10:59:19', 1, NULL, NULL, NULL, NULL, NULL, NULL, '2025-07-17', NULL, NULL, NULL, 'No', '2025-07-10 10:59:19', '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"fasdffadf\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null},{\"action\":\"Assistance\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":\"Fire\",\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]', NULL, NULL, 'Blood', NULL, NULL, 'No', NULL, 'Unknown', NULL, '', NULL, 0, NULL, NULL),
(34, 'dgjhdd', '2025-07-11', '19:42:00', 'Staff', 'jdghmkjifijd', 'Exposure to blood / body-substance', NULL, 'Spill', NULL, NULL, NULL, NULL, NULL, 'shgfsh', NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 'No', 'No', NULL, NULL, NULL, NULL, NULL, 'New', '2025-07-10 12:43:41', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Yes', '2025-07-10 12:43:41', '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"sfgfhsgh\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null},{\"action\":\"Assistance\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":\"Fire\",\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]', 'Kamran', 'Mubashir', 'Saliva', NULL, 1, 'Yes', 'Gloves, single layer, Gloves, double layer', '', 'Eyes, Mouth', '', NULL, 1, NULL, NULL),
(35, 'Mubashir kamran', '2025-07-11', '13:59:00', 'Staff', 'hdfhfdg', 'Exposure to blood / body-substance', NULL, 'Needle stick', NULL, NULL, NULL, NULL, NULL, 'ryutru', NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 'Yes', 'No', NULL, NULL, 'dhfghfghfgh', '2025-07-11', 'hdfgdfhdfg', 'New', '2025-07-11 08:01:07', 2, NULL, NULL, NULL, NULL, 'ghg', 'hdfhdfghdfghdfg', '2025-07-11', 'hdfg', 'hdfghfd', '2025-07-11', 'Yes', '2025-07-11 08:01:07', '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"hdfghfdhfg\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]', 'urty', 'urtyuy', 'Urine', NULL, 1, 'Yes', 'Gloves, single layer, Gloves, double layer', '', 'Eyes, Mouth', '', NULL, 1, NULL, NULL),
(36, 'Mubashir kamran', '2025-07-11', '15:21:00', 'Staff', 'sdfgsdfg', 'Breach of Privacy', NULL, NULL, NULL, 'sgdfsgsgfd', NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 'No', 'No', NULL, NULL, 'gsgfsdfdgsffg', '2025-07-11', 'gfsdgsfgdf', 'New', '2025-07-11 08:22:02', 2, NULL, NULL, NULL, NULL, 'sfddddgsdgsg', 'sdfdsfgsg', '2025-07-11', 'sfs', 'sdfgfgfsdg', '2025-07-11', 'Yes', '2025-07-11 08:22:02', '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"gsdfsdgsgdf\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null},{\"action\":\"Assistance\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":\"Police\",\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL),
(37, 'sgdfsdgsd', '2025-07-11', '16:22:00', 'Staff', 'sgfgsdf', 'Exposure to blood / body-substance', NULL, 'Spill', NULL, NULL, NULL, NULL, NULL, 'sdfgsdgsdfd', NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 'No', 'No', NULL, NULL, 'gsdfgsdfgsd', '2025-07-11', 'sgfgsdf', 'New', '2025-07-11 08:23:02', 2, NULL, NULL, NULL, NULL, 'fgsdfgsdfg', 'gsdfgsdfgsdg', '2025-07-11', 'dfsggds', 'sgdfgsdfg', '2025-07-12', 'Yes', '2025-07-11 08:23:02', '[{\"action\":\"Investigation\",\"medical_treatment_details\":null,\"investigation_details\":\"sdfggsdfg\",\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]', 'fsgdfg', 'sfgsgsdf', 'Blood product', NULL, 1, 'Yes', 'Gloves, single layer, Gloves, double layer', NULL, NULL, NULL, NULL, 1, NULL, NULL),
(38, 'Mubashir kamran', '2025-07-11', '14:37:00', 'Staff', 'ywerywyr', 'Fall', 'yewywry', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 'No', NULL, NULL, NULL, NULL, NULL, NULL, 'New', '2025-07-11 08:37:40', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Yes', '2025-07-11 08:37:40', '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"ewryewry\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL),
(39, 'Mubashir kamran', '2025-07-11', '15:47:00', 'Staff', 'gdsfggsdf', 'Exposure to blood / body-substance', NULL, 'Spill', NULL, NULL, NULL, NULL, NULL, 'gsdfgdf', NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 'Yes', 'No', NULL, NULL, NULL, NULL, NULL, 'New', '2025-07-11 08:48:20', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Yes', '2025-07-11 08:48:20', '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"gdfgsdfgdfg\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null},{\"action\":\"Assistance\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":\"Police\",\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]', 'gsdfg', 'dfgdfgfg', 'Blood product', NULL, 1, 'No', 'Gloves, single layer, Gloves, double layer', '', 'Eyes, Mouth', '', NULL, 1, NULL, NULL),
(40, 'Mubashir kamran', '2025-07-11', '16:15:00', 'Staff', 'afsdfasdf', 'Fall', 'sdasfsdf', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 'Yes', 'No', NULL, NULL, NULL, NULL, NULL, 'New', '2025-07-11 10:15:40', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Yes', '2025-07-11 10:15:40', '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"fadsfasfa\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null},{\"action\":\"Investigation\",\"medical_treatment_details\":null,\"investigation_details\":\"asdfsaf\",\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null},{\"action\":\"Assistance\",\"medical_treatment_details\":null,\"investigation_details\":null,\"assistance_type\":\"Police\",\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL),
(41, 'Mubashir kamran', '2025-07-11', '17:19:00', 'Staff', 'gsdf', 'Exposure to blood / body-substance', NULL, 'Spill', NULL, NULL, NULL, NULL, NULL, 'gdfgsfg', NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 'No', 'No', NULL, NULL, NULL, NULL, NULL, 'New', '2025-07-11 11:19:37', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Yes', '2025-07-11 11:19:37', '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"gsdgdg\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]', 'sgsdfg', 'gsdfg', 'Blood product', NULL, 1, 'Yes', NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL),
(42, 'vfvr', '2025-07-11', '18:19:00', 'Staff', 'fsdfasdf', 'Fall', 'fsdf', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 'Yes', 'No', NULL, NULL, NULL, NULL, NULL, 'New', '2025-07-11 11:20:10', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Yes', '2025-07-11 11:20:10', '[{\"action\":\"Medical Treatment\",\"medical_treatment_details\":\"fsdfsd\",\"investigation_details\":null,\"assistance_type\":null,\"assistance_other_details\":null,\"debriefing_type\":null,\"debriefing_onsite_details\":null,\"debriefing_referred_details\":null}]', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL),
(43, 'Mubashir kamran', '2025-07-12', '18:31:00', 'Staff', NULL, 'Exposure to blood / body-substance', NULL, 'Spill', NULL, NULL, NULL, NULL, NULL, 'gsdg', NULL, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, 'No', NULL, NULL, NULL, NULL, NULL, NULL, 'New', '2025-07-12 11:32:00', 1, NULL, NULL, NULL, NULL, NULL, NULL, '2025-07-19', NULL, NULL, NULL, 'No', '2025-07-12 11:32:00', NULL, 'gsdfg', 'fgdf', 'Saliva', NULL, 1, 'No', '[\"Gloves, single layer\",\"Gloves, double layer\"]', '', '[\"Eyes\",\"Mouth\"]', '', NULL, 1, NULL, NULL);

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

--
-- Table structure for table `leave_applications`
--

CREATE TABLE `leave_applications` (
  `id` int(11) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `surname` varchar(255) NOT NULL,
  `position` varchar(255) NOT NULL,
  `contact_phone` varchar(20) NOT NULL,
  `leave_types` text DEFAULT NULL,
  `leave_comments` text DEFAULT NULL,
  `last_day_work` date NOT NULL,
  `return_work_date` date NOT NULL,
  `total_days_off` int(11) NOT NULL,
  `employee_date` date NOT NULL,
  `reason_refusal` text DEFAULT NULL,
  `manager_name` varchar(255) DEFAULT NULL,
  `manager_date` date DEFAULT NULL,
  `submission_date` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `status` enum('New','Approved','Not Approved') NOT NULL DEFAULT 'New',
  `employee_email` varchar(255) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `leave_applications`
--

INSERT INTO `leave_applications` (`id`, `first_name`, `surname`, `position`, `contact_phone`, `leave_types`, `leave_comments`, `last_day_work`, `return_work_date`, `total_days_off`, `employee_date`, `reason_refusal`, `manager_name`, `manager_date`, `submission_date`, `updated_at`, `deleted_at`, `created_by`, `status`, `employee_email`) VALUES
(3, 'uttttturtu', 'ueruu', 'trut', '03192425138', 'Annual leave (full pay), Annual leave (half pay), Leave without pay', 'dfhfghgfhh', '2025-07-10', '2025-07-10', 5, '2025-07-10', '', 'Mubashir', '2025-07-10', '2025-07-10 12:44:59', '2025-07-10 13:52:31', NULL, 2, 'Approved', 'mubashirkamranrana@gmail.com'),
(4, 'hdfhfgd', 'hdfgh', 'fhgdf', 'hfghfh', 'Annual leave (full pay), Annual leave (half pay), Leave without pay, Long service leave', 'dsagsgdgsdf', '2025-07-11', '2025-07-11', 12, '2025-07-11', NULL, NULL, NULL, '2025-07-11 08:03:06', NULL, NULL, 2, 'New', 'mubashirkamranrana@gmail.com'),
(5, 'zbcxzc', 'zccbbzx', 'zxcbzxcb', 'zbbzxcb', 'Annual leave (full pay)', 'cXZczxc', '2025-07-11', '2025-07-11', 12, '2025-07-11', NULL, NULL, NULL, '2025-07-11 10:16:23', NULL, NULL, 2, 'New', 'mubashirkamranrana@gmail.com'),
(6, 'Mubashir', 'Kamran', 'mgfmhm', '03192425138', 'Annual leave (full pay)', 'dfasfd', '2025-07-11', '2025-07-11', 12, '2025-07-11', NULL, NULL, NULL, '2025-07-11 11:22:47', NULL, NULL, 2, 'New', 'mubashirkamranrana@gmail.com');

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

--
-- Table structure for table `meeting_minutes`
--

CREATE TABLE `meeting_minutes` (
  `id` int(11) NOT NULL,
  `meeting_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `location_type` enum('physical','virtual') NOT NULL,
  `physical_location` varchar(255) DEFAULT NULL,
  `virtual_location` varchar(255) DEFAULT NULL,
  `attendees_present` text NOT NULL,
  `attendees_absent` text DEFAULT NULL,
  `meeting_outcomes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`meeting_outcomes`)),
  `follow_up_required` enum('Yes','No','Completed') NOT NULL,
  `follow_up_action` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `meeting_minutes`
--

INSERT INTO `meeting_minutes` (`id`, `meeting_id`, `date`, `time`, `location_type`, `physical_location`, `virtual_location`, `attendees_present`, `attendees_absent`, `meeting_outcomes`, `follow_up_required`, `follow_up_action`, `created_at`, `updated_at`) VALUES
(28, 51, '2025-07-11', '17:15:00', 'physical', 'dsdfgdfg', NULL, 'mubashirkamranrana@gmail.com', 'mubashirkamranarana12345@gmail.com', '[{\"time\":\"17:15\",\"counter\":2,\"outcome\":\"gsdgsdfgsdfg\"},{\"time\":\"17:15\",\"counter\":9,\"outcome\":\"gsdfgdfgsdgf\"}]', 'Completed', NULL, '2025-07-11 12:15:56', '2025-07-11 12:15:56'),
(29, 44, '2025-07-11', '17:15:00', 'physical', 'dsdfgdfg', NULL, 'mubashirkamranrana@gmail.com', 'mubashirkamranarana12345@gmail.com', '[{\"time\":\"17:15\",\"counter\":3,\"outcome\":\"gsdfgsdfgsd\"},{\"time\":\"17:15\",\"counter\":4,\"outcome\":\"gsdfgsdf\"}]', 'Completed', NULL, '2025-07-11 12:15:56', '2025-07-11 12:15:56'),
(30, 43, '2025-07-11', '17:15:00', 'physical', 'dsdfgdfg', NULL, 'mubashirkamranrana@gmail.com', 'mubashirkamranarana12345@gmail.com', '[{\"time\":\"17:15\",\"counter\":1,\"outcome\":\"sgdfgsdfg\"},{\"time\":\"17:15\",\"counter\":3,\"outcome\":\"gsdgdsfg\"}]', 'Completed', NULL, '2025-07-11 12:15:56', '2025-07-11 12:15:56'),
(31, 49, '2025-07-12', '14:18:00', 'physical', 'jfhjfg', NULL, 'mubashirkamranrana@gmail.com', 'mubashirkamranarana12345@gmail.com', '[{\"time\":\"14:18\",\"counter\":7,\"outcome\":\"Test\"}]', 'Yes', 'Test', '2025-07-12 09:19:21', '2025-07-12 09:19:21'),
(32, 47, '2025-07-12', '14:30:00', 'physical', 'dewqw', NULL, 'mubashirkamranrana@gmail.com', 'mubashirkamranarana12345@gmail.com', '[{\"time\":\"14:30\",\"counter\":4,\"outcome\":\"ddwdwqd\"}]', 'Completed', NULL, '2025-07-12 09:30:50', '2025-07-12 09:30:50'),
(33, 45, '2025-07-12', '15:03:00', 'physical', 'sdfsdfsd', NULL, 'mubashirkamranrana@gmail.com', NULL, '[{\"time\":\"15:03\",\"counter\":6,\"outcome\":\"fsdfasdf\"}]', 'Yes', 'sfafasdf', '2025-07-12 10:04:08', '2025-07-12 10:04:08'),
(34, 53, '2025-07-12', '15:24:00', 'physical', 'sdfgdf', NULL, 'mubashirkamranrana@gmail.com', NULL, '[{\"time\":\"15:24\",\"counter\":6,\"outcome\":\"gsdfsg\"}]', 'Yes', 'gsdfgsdf', '2025-07-12 10:24:58', '2025-07-12 10:24:58');

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

--
-- Table structure for table `patients`
--

CREATE TABLE `patients` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `medical_history` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

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

--
-- Table structure for table `pm_emails`
--

CREATE TABLE `pm_emails` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `pm_emails`
--

INSERT INTO `pm_emails` (`id`, `name`, `email`, `deleted_at`, `updated_at`, `created_at`) VALUES
(2, '.htaccess', 'solxingle@gmail.com', NULL, NULL, '2025-06-24 08:18:48');

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

--
-- Table structure for table `reminders`
--

CREATE TABLE `reminders` (
  `id` int(11) NOT NULL,
  `message` text NOT NULL,
  `reminder_date` date NOT NULL,
  `staff_emails` text DEFAULT NULL,
  `pm_emails` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

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

--
-- Table structure for table `staff`
--

CREATE TABLE `staff` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `staff`
--

INSERT INTO `staff` (`id`, `name`, `email`, `created_at`, `deleted_at`, `updated_at`) VALUES
(4, 'Mubashir', 'mubashirkamranrana@gmail.com', '2025-05-07 11:19:39', NULL, '2025-06-24 08:32:39'),
(5, 'Mubashir', 'mubashirkamranarana123@gmail.com', '2025-05-17 08:28:27', NULL, NULL),
(6, 'Mubashir 3', 'mubashirkamranrana123@gmail.com', '2025-05-26 11:56:21', NULL, NULL),
(8, 'Mubashir 56', 'mubashirkamranarana1234@gmail.com', '2025-06-16 16:16:16', NULL, NULL),
(9, 'Mubashir', 'mubashirkamranarana12345@gmail.com', '2025-06-24 08:33:53', '2025-06-24 08:57:45', '2025-06-24 08:57:45');

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

--
-- Table structure for table `trash`
--

CREATE TABLE `trash` (
  `id` int(11) NOT NULL,
  `item_type` enum('feedback','incident','business_risk','meeting') NOT NULL,
  `item_id` int(11) NOT NULL,
  `deleted_at` datetime NOT NULL DEFAULT current_timestamp(),
  `deleted_by` int(11) DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `action_register`
--
ALTER TABLE `action_register`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_source_id` (`source_id`),
  ADD KEY `idx_type` (`type`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `blood_body_substance_exposures`
--
ALTER TABLE `blood_body_substance_exposures`
  ADD PRIMARY KEY (`id`) USING BTREE,
  ADD KEY `idx_blood_body_incident_report_id` (`incident_report_id`) USING BTREE,
  ADD KEY `idx_created_by` (`created_by`) USING BTREE;

--
-- Indexes for table `business_risk_reports`
--
ALTER TABLE `business_risk_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_business_risk_reports_deleted_at` (`deleted_at`);

--
-- Indexes for table `complaints`
--
ALTER TABLE `complaints`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `complaints_meetings`
--
ALTER TABLE `complaints_meetings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_type` (`type`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_meeting_date` (`meeting_date`),
  ADD KEY `idx_due_date` (`due_date`),
  ADD KEY `idx_source_id` (`source_id`);

--
-- Indexes for table `feedback`
--
ALTER TABLE `feedback`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_feedback_deleted_at` (`deleted_at`);

--
-- Indexes for table `incident_reports`
--
ALTER TABLE `incident_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_incident_classification` (`incident_classification`),
  ADD KEY `idx_incident_reports_deleted_at` (`deleted_at`);

--
-- Indexes for table `leave_applications`
--
ALTER TABLE `leave_applications`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `meeting_minutes`
--
ALTER TABLE `meeting_minutes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_meeting_id` (`meeting_id`);

--
-- Indexes for table `patients`
--
ALTER TABLE `patients`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `pm_emails`
--
ALTER TABLE `pm_emails`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Indexes for table `reminders`
--
ALTER TABLE `reminders`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `staff`
--
ALTER TABLE `staff`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Indexes for table `trash`
--
ALTER TABLE `trash`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_item_type_item_id` (`item_type`,`item_id`),
  ADD KEY `idx_deleted_at` (`deleted_at`),
  ADD KEY `deleted_by` (`deleted_by`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `action_register`
--
ALTER TABLE `action_register`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

--
-- AUTO_INCREMENT for table `blood_body_substance_exposures`
--
ALTER TABLE `blood_body_substance_exposures`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key', AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `business_risk_reports`
--
ALTER TABLE `business_risk_reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=46;

--
-- AUTO_INCREMENT for table `complaints`
--
ALTER TABLE `complaints`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `complaints_meetings`
--
ALTER TABLE `complaints_meetings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=55;

--
-- AUTO_INCREMENT for table `feedback`
--
ALTER TABLE `feedback`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=86;

--
-- AUTO_INCREMENT for table `incident_reports`
--
ALTER TABLE `incident_reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key', AUTO_INCREMENT=44;

--
-- AUTO_INCREMENT for table `leave_applications`
--
ALTER TABLE `leave_applications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `meeting_minutes`
--
ALTER TABLE `meeting_minutes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=35;

--
-- AUTO_INCREMENT for table `patients`
--
ALTER TABLE `patients`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `pm_emails`
--
ALTER TABLE `pm_emails`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `reminders`
--
ALTER TABLE `reminders`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `staff`
--
ALTER TABLE `staff`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `trash`
--
ALTER TABLE `trash`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `blood_body_substance_exposures`
--
ALTER TABLE `blood_body_substance_exposures`
  ADD CONSTRAINT `fk_blood_body_incident_report` FOREIGN KEY (`incident_report_id`) REFERENCES `incident_reports` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `complaints`
--
ALTER TABLE `complaints`
  ADD CONSTRAINT `complaints_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `feedback`
--
ALTER TABLE `feedback`
  ADD CONSTRAINT `feedback_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `meeting_minutes`
--
ALTER TABLE `meeting_minutes`
  ADD CONSTRAINT `meeting_minutes_ibfk_1` FOREIGN KEY (`meeting_id`) REFERENCES `complaints_meetings` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `patients`
--
ALTER TABLE `patients`
  ADD CONSTRAINT `patients_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `trash`
--
ALTER TABLE `trash`
  ADD CONSTRAINT `trash_ibfk_1` FOREIGN KEY (`deleted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;
COMMIT;

