--
-- Host: localhost    Database: medvista
-- ------------------------------------------------------


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

CREATE TABLE `patients` (
  `id` int NOT NULL AUTO_INCREMENT,
  `fk_doctor` int NOT NULL,
  `fullname` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `dateOfbirth` date NOT NULL,
  `gender` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  `age` int NOT NULL,
  `address` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `phonenumber` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `emailid` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `tobaccouse` tinyint NOT NULL DEFAULT '0',
  `alcoholconsumption` tinyint NOT NULL DEFAULT '0',
  `dietandexercisehabits` text COLLATE utf8mb4_general_ci,
  `pastmedicalconditions` text COLLATE utf8mb4_general_ci,
  `allergies` text COLLATE utf8mb4_general_ci,
  `medications` text COLLATE utf8mb4_general_ci,
  `familymedicalhistory` text COLLATE utf8mb4_general_ci,
  `datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `patients_id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phonenumber` (`phonenumber`),
  UNIQUE KEY `emailid` (`emailid`),
  UNIQUE KEY `patients_id` (`patients_id`),
  KEY `fk_patients_users1` (`fk_doctor`),
  CONSTRAINT `fk_patients_users1` FOREIGN KEY (`fk_doctor`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `patients`
--

INSERT INTO `patients` VALUES (2,2,'Pratush Raj','1998-05-02','Male',25,'bhcddj, bcjd','1234566','pratushraj0@gmail.com',0,0,NULL,NULL,NULL,NULL,NULL,'2024-07-04 09:08:41','240704_2'),(5,2,'Pratush Raj','1998-05-02','Male',25,'bhcddj, bcjd','1234566555','pratushraj20@gmail.com',0,0,'','','','','','2024-07-04 09:55:46','240704_5');
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `before_insert_patients` BEFORE INSERT ON `patients` FOR EACH ROW BEGIN
  DECLARE max_id INT;
  DECLARE new_id VARCHAR(255);
  
  SET @date_prefix = DATE_FORMAT(NOW(), '%y%m%d');
  
  SELECT COALESCE(MAX(CAST(SUBSTRING_INDEX(patients_id COLLATE utf8mb4_general_ci, '_', -1) AS UNSIGNED)), -1)
  INTO max_id
  FROM patients
  WHERE patients_id LIKE CONCAT(@date_prefix, '_%') COLLATE utf8mb4_general_ci;
  
  SET new_id = CONCAT(@date_prefix, '_', max_id + 1);
  
  SET NEW.patients_id = new_id;
END */;;
DELIMITER ;


