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

START TRANSACTION;



--
-- Database: `trachcare`
--

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

--
-- Table structure for table `addpatients`
--

CREATE TABLE `addpatients` (
  `doctor_id` varchar(255) DEFAULT NULL,
  `patient_id` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone_number` varchar(20) NOT NULL,
  `password` varchar(255) NOT NULL,
  `age` varchar(10) NOT NULL,
  `gender` varchar(255) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `appoinment` varchar(255) NOT NULL,
  `bmi` varchar(10) DEFAULT NULL,
  `diagnosis` varchar(500) DEFAULT NULL,
  `surgery_status` varchar(100) DEFAULT NULL,
  `post_op_tracheostomy_day` varchar(10) DEFAULT NULL,
  `tube_name_size` varchar(50) DEFAULT NULL,
  `baseline_vitals` varchar(500) DEFAULT NULL,
  `respiratory_rate` varchar(10) DEFAULT NULL,
  `heart_rate` varchar(10) DEFAULT NULL,
  `spo2_room_air` varchar(10) DEFAULT NULL,
  `indication_of_tracheostomy` varchar(500) DEFAULT NULL,
  `comorbidities` varchar(500) DEFAULT NULL,
  `hemoglobin` varchar(10) DEFAULT NULL,
  `sr_sodium` varchar(10) DEFAULT NULL,
  `sr_potassium` varchar(10) DEFAULT NULL,
  `sr_calcium` varchar(10) DEFAULT NULL,
  `sr_bicarbonate` varchar(10) DEFAULT NULL,
  `pt` varchar(10) DEFAULT NULL,
  `aptt` varchar(10) DEFAULT NULL,
  `inr` varchar(10) DEFAULT NULL,
  `platelets` varchar(10) DEFAULT NULL,
  `liver_function_test` varchar(500) DEFAULT NULL,
  `renal_function_test` varchar(500) DEFAULT NULL,
  `image_path` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `addpatients`
--

INSERT INTO `addpatients` (`doctor_id`, `patient_id`, `username`, `email`, `phone_number`, `password`, `age`, `gender`, `address`, `appoinment`, `bmi`, `diagnosis`, `surgery_status`, `post_op_tracheostomy_day`, `tube_name_size`, `baseline_vitals`, `respiratory_rate`, `heart_rate`, `spo2_room_air`, `indication_of_tracheostomy`, `comorbidities`, `hemoglobin`, `sr_sodium`, `sr_potassium`, `sr_calcium`, `sr_bicarbonate`, `pt`, `aptt`, `inr`, `platelets`, `liver_function_test`, `renal_function_test`, `image_path`) VALUES
('65227192121057', '78945siva', 'SIVANESAN', 'test@test.com', ' 1234567890', '123', ' 45', ' Male', ' 123 Main St, Springfield', ' 2024-12-05', ' 24.5', ' Hypertension', ' Not Required', ' 0', ' None', ' Normal', ' 18', ' 72', ' 98', ' None', ' Diabetes', ' 14.2', ' 136', ' 4.2', ' 9.1', ' 23', ' 12.5', ' 30', ' 1.1', ' 250000', ' Normal', ' Normal', '../uploads/patient_images/images.jpg'),
('65227192121057', '80056gowrishankar', 'gowrishankar', 'p@test.com', '12356', 'tgshs', 'hsj', 'nxjx', 'sjsj', '2024-12-25', 'nsjs', 'sjz', 'sjz', 'shsh', 'sjaj', 'ssj', 'aajs', 'ajaj', 'haha', 'sjsj', 'hshs', 'hsjs', 'jjs', 'jsj', 'haah', 'aja', 'hsh', 'ahs', 'cucu', 'gzgz', 'hsh', 'sbbs', '../uploads/patient_images/patient_67533e9796c368.18824085.jpg');

--
-- Triggers `addpatients`
--
DELIMITER $$
CREATE TRIGGER `Add_spogotting` AFTER INSERT ON `addpatients` FOR EACH ROW INSERT INTO spiotting_status(doctor_id,patient_id,username) VALUES(NEW.doctor_id,NEW.patient_id,NEW.username)
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `deletespigotting` AFTER DELETE ON `addpatients` FOR EACH ROW DELETE FROM spiotting_status
WHERE patient_id = OLD.patient_id
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `deletestatus` AFTER DELETE ON `addpatients` FOR EACH ROW DELETE FROM daily_stauts
WHERE patient_id = OLD.patient_id
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `statusAdd` AFTER INSERT ON `addpatients` FOR EACH ROW INSERT INTO daily_stauts(doctor_id,patient_id,username,status_10,status_12,status_2,status_4,status_6,issues)VALUES(NEW.doctor_id,NEW.patient_id,NEW.username,0,0,0,0,0,0)
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `update_report` AFTER UPDATE ON `addpatients` FOR EACH ROW UPDATE daily_report SET 
username = NEW.username,image_path = NEW.image_path 
WHERE patient_id = NEW.patient_id
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `update_spigotting` AFTER UPDATE ON `addpatients` FOR EACH ROW UPDATE spiotting_status SET username = NEW.username WHERE patient_id = NEW.patient_id
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `update_status` AFTER UPDATE ON `addpatients` FOR EACH ROW UPDATE daily_stauts SET username = NEW.username WHERE patient_id = OLD.patient_id
$$
DELIMITER ;

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

--
-- Table structure for table `adminlogin`
--

CREATE TABLE `adminlogin` (
  `id` int(11) NOT NULL,
  `doctor_id` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `doctor_reg_no` varchar(20) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone_number` varchar(20) NOT NULL,
  `password` varchar(255) NOT NULL,
  `image_path` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `adminlogin`
--

INSERT INTO `adminlogin` (`id`, `doctor_id`, `username`, `doctor_reg_no`, `email`, `phone_number`, `password`, `image_path`) VALUES
(1, '192121057', 'admin', '192121057', 'admin@trachcare.com', '9500077434', '123456', '../uploads/admin/JPEG_20241104_183943_7336376290832889215.jpg');

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

--
-- 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 `doctorprofile`
--

CREATE TABLE `doctorprofile` (
  `doctor_id` varchar(500) NOT NULL,
  `username` varchar(255) NOT NULL,
  `doctor_reg_no` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone_number` varchar(20) NOT NULL,
  `password` varchar(255) NOT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `doctorprofile`
--

INSERT INTO `doctorprofile` (`doctor_id`, `username`, `doctor_reg_no`, `email`, `phone_number`, `password`, `image_path`, `created_at`) VALUES
('65227192121057', 'Dr. varsha', '192121057', 'test@test.com', '1234567890', '12345678', '../uploads/doctorimages/6751a986e4e80.jpg', '2024-12-05 13:24:22');

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

--
-- 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 dumped tables
--

--
-- Indexes for table `addpatients`
--
ALTER TABLE `addpatients`
  ADD UNIQUE KEY `patient_id` (`patient_id`),
  ADD UNIQUE KEY `email` (`email`,`phone_number`);

--
-- Indexes for table `adminlogin`
--
ALTER TABLE `adminlogin`
  ADD UNIQUE KEY `username` (`username`),
  ADD KEY `id` (`id`) USING BTREE;

--
-- 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 `doctorprofile`
--
ALTER TABLE `doctorprofile`
  ADD PRIMARY KEY (`doctor_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 dumped tables
--

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

--
-- 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;

