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

START TRANSACTION;



--
-- Triggers `addpatients`
--
DELIMITER $$
DELIMITER ;
DELIMITER $$
DELIMITER ;
DELIMITER $$
DELIMITER ;
DELIMITER $$
DELIMITER ;
DELIMITER $$
DELIMITER ;
DELIMITER $$
DELIMITER ;
DELIMITER $$
DELIMITER ;

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

--
-- Table structure for table `daily_report`
--

CREATE TABLE `daily_report` (
  `id` int(11) NOT NULL,
  `doctor_id` varchar(255) NOT NULL,
  `patient_id` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `date` date NOT NULL DEFAULT current_timestamp(),
  `respiratory_rate` int(11) DEFAULT NULL,
  `heart_rate` int(11) DEFAULT NULL,
  `spo2_room_air` float DEFAULT NULL,
  `daily_dressing_done` enum('Yes','No') DEFAULT 'No',
  `tracheostomy_tie_changed` enum('Yes','No') DEFAULT 'No',
  `suctioning_done` enum('Yes','No') DEFAULT 'No',
  `oral_feeds_started` enum('Yes','No') DEFAULT 'No',
  `changed_to_green_tube` enum('Yes','No') DEFAULT 'No',
  `able_to_breathe_through_nose` enum('Yes','No') DEFAULT 'No',
  `secretion_color_consistency` varchar(255) DEFAULT NULL,
  `cough_or_breathlessness` enum('Yes','No') DEFAULT 'No',
  `breath_duration` int(11) DEFAULT NULL,
  `image_path` varchar(255) 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_general_ci;

--
-- Dumping data for table `daily_report`
--

INSERT INTO `daily_report` (`id`, `doctor_id`, `patient_id`, `username`, `date`, `respiratory_rate`, `heart_rate`, `spo2_room_air`, `daily_dressing_done`, `tracheostomy_tie_changed`, `suctioning_done`, `oral_feeds_started`, `changed_to_green_tube`, `able_to_breathe_through_nose`, `secretion_color_consistency`, `cough_or_breathlessness`, `breath_duration`, `image_path`, `created_at`, `updated_at`) VALUES
(1, '65227192121057', '78945siva', 'SIVANESAN', '2024-12-05', 88, 93, 24, 'Yes', 'No', 'No', 'Yes', 'Yes', 'Yes', '', 'Yes', 20, '../uploads/patient_images/images.jpg', '2024-12-05 12:46:03', '2024-12-07 02:57:44'),
(3, '65227192121057', '78945siva', 'SIVANESAN', '2024-12-07', 20, 75, 98, 'Yes', 'No', 'Yes', 'Yes', 'No', 'Yes', 'Clear', 'Yes', 45, '../uploads/patient_images/images.jpg', '2024-12-05 18:55:48', '2024-12-07 03:01:46'),
(4, '65227192121057', '80056gowrishankar', 'gowrishankar', '2024-12-07', 23, 86, 12, 'Yes', 'No', 'Yes', 'No', 'Yes', 'Yes', 'white', 'Yes', 45, '../uploads/patient_images/patient_67533e9796c368.18824085.jpg', '2024-12-06 18:18:17', '2024-12-07 03:01:46');

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

--
-- Table structure for table `daily_stauts`
--

CREATE TABLE `daily_stauts` (
  `doctor_id` varchar(255) NOT NULL,
  `patient_id` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `status_10` tinyint(1) NOT NULL,
  `status_12` tinyint(1) NOT NULL,
  `status_2` tinyint(1) NOT NULL,
  `status_4` tinyint(1) NOT NULL,
  `status_6` tinyint(1) NOT NULL,
  `issues` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `daily_stauts`
--

INSERT INTO `daily_stauts` (`doctor_id`, `patient_id`, `username`, `status_10`, `status_12`, `status_2`, `status_4`, `status_6`, `issues`) VALUES
('65227192121057', '78945siva', 'SIVANESAN', 0, 0, 0, 0, 0, 0),
('65227192121057', '80056gowrishankar', 'gowrishankar', 0, 0, 0, 0, 0, 0);

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

--
-- Table structure for table `medication_schedule`
--

CREATE TABLE `medication_schedule` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `tablet_name` varchar(255) NOT NULL,
  `morning_before_food` tinyint(1) DEFAULT 0,
  `morning_after_food` tinyint(1) DEFAULT 0,
  `afternoon_before_food` tinyint(1) DEFAULT 0,
  `afternoon_after_food` tinyint(1) DEFAULT 0,
  `night_before_food` tinyint(1) DEFAULT 0,
  `night_after_food` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `patientvideotable`
--

CREATE TABLE `patientvideotable` (
  `s.no` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` varchar(500) NOT NULL,
  `Video_url` varchar(255) DEFAULT NULL,
  `Thumbnail_url` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `patientvideotable`
--

INSERT INTO `patientvideotable` (`s.no`, `title`, `description`, `Video_url`, `Thumbnail_url`) VALUES
(1, 'Tracheostomy tie change ', 'How to change tracheostomy ties ', '../uploads/videos/90448VID_20241106_114338_580.mp4', '../uploads/thumbnails/90448JPEG_20241205_183937_6331898773629660637.jpg'),
(2, 'Tracheostomy dressing ', 'How to change tracheostomy dressing ', '../uploads/videos/83406VID_20241106_125602_863.mp4', '../uploads/thumbnails/83406JPEG_20241205_185122_1281896336247547824.jpg'),
(3, 'Tracheostomy suction', 'How to suction', '../uploads/videos/77722VID_20241111_215159_951.mp4', '../uploads/thumbnails/77722JPEG_20241205_185230_5450340506280209027.jpg');

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

--
-- Table structure for table `spiotting_status`
--

CREATE TABLE `spiotting_status` (
  `doctor_id` varchar(255) NOT NULL,
  `patient_id` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `date` date NOT NULL DEFAULT current_timestamp(),
  `cough_or_breathlessness` varchar(255) DEFAULT NULL,
  `breath_duration` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `spiotting_status`
--

INSERT INTO `spiotting_status` (`doctor_id`, `patient_id`, `username`, `date`, `cough_or_breathlessness`, `breath_duration`) VALUES
('65227192121057', '78945siva', 'SIVANESAN', '2024-12-07', 'Yes', '45'),
('65227192121057', '80056gowrishankar', 'gowrishankar', '2024-12-07', 'Yes', '20');

--
-- Triggers `spiotting_status`
--
DELIMITER $$
CREATE TRIGGER `updatetoDR` AFTER UPDATE ON `spiotting_status` FOR EACH ROW UPDATE daily_report SET 
cough_or_breathlessness = NEW.cough_or_breathlessness,
breath_duration = NEW.breath_duration
WHERE date = NEW.date
$$
DELIMITER ;

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

--
-- Indexes for table `daily_stauts`
--
ALTER TABLE `daily_stauts`
  ADD UNIQUE KEY `doctorid` (`doctor_id`,`patient_id`);

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

--
-- Indexes for table `patientvideotable`
--
ALTER TABLE `patientvideotable`
  ADD PRIMARY KEY (`s.no`);

--
-- Indexes for table `spiotting_status`
--
ALTER TABLE `spiotting_status`
  ADD UNIQUE KEY `patient_id` (`patient_id`);

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

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

--
-- AUTO_INCREMENT for table `patientvideotable`
--
ALTER TABLE `patientvideotable`
  MODIFY `s.no` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

DELIMITER $$
--
-- Events
--
CREATE DEFINER=`root`@`localhost` EVENT `daily_status` ON SCHEDULE EVERY 1 DAY STARTS '2024-11-06 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO UPDATE daily_stauts SET status_10=0,status_12=0,status_2=0,status_4=0,status_6=0$$$$

CREATE DEFINER=`root`@`localhost` EVENT `daily_status_issues` ON SCHEDULE EVERY 2 HOUR STARTS '2024-11-06 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO UPDATE daily_stauts SET issues=0$$$$

CREATE DEFINER=`root`@`localhost` EVENT `spogoting_update` ON SCHEDULE EVERY 2 HOUR STARTS '2024-11-06 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO UPDATE spiotting_status SET cough_or_breathlessness=NULL,breath_duration=NULL,date=NULL$$$$

DELIMITER ;
COMMIT;

