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


--
-- Table structure for table `causes`
--

CREATE TABLE `causes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `createdby` int NOT NULL DEFAULT '1' COMMENT '1: Created by AI\\r\\n2: Created by Doctor',
  `fk_symptoms` int NOT NULL,
  `fk_reasonforvisit` int NOT NULL,
  `datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_causes_symptoms1` (`fk_symptoms`),
  KEY `fk_causes_reasonforvisit1` (`fk_reasonforvisit`),
  CONSTRAINT `fk_causes_reasonforvisit1` FOREIGN KEY (`fk_reasonforvisit`) REFERENCES `reasonforvisit` (`id`),
  CONSTRAINT `fk_causes_symptoms1` FOREIGN KEY (`fk_symptoms`) REFERENCES `symptoms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `causes`
--


--
-- Table structure for table `diagnostics`
--

CREATE TABLE `diagnostics` (
  `id` int NOT NULL AUTO_INCREMENT,
  `patient_id` int DEFAULT NULL,
  `visits_id` int DEFAULT NULL,
  `diagnostics` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `doctor_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `patient_id` (`patient_id`),
  KEY `visits_id` (`visits_id`),
  CONSTRAINT `diagnostics_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`),
  CONSTRAINT `diagnostics_ibfk_2` FOREIGN KEY (`visits_id`) REFERENCES `visits` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `diagnostics`
--


--
-- Table structure for table `diagnostics_by_doctor`
--

CREATE TABLE `diagnostics_by_doctor` (
  `id` int NOT NULL AUTO_INCREMENT,
  `patient_id` int DEFAULT NULL,
  `visits_id` int DEFAULT NULL,
  `doctor_id` int DEFAULT NULL,
  `diagnostics` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `patient_id` (`patient_id`),
  KEY `visits_id` (`visits_id`),
  KEY `doctor_id` (`doctor_id`),
  CONSTRAINT `diagnostics_by_doctor_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`),
  CONSTRAINT `diagnostics_by_doctor_ibfk_2` FOREIGN KEY (`visits_id`) REFERENCES `visits` (`id`),
  CONSTRAINT `diagnostics_by_doctor_ibfk_3` FOREIGN KEY (`doctor_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `diagnostics_by_doctor`
--


--
-- Table structure for table `diet`
--

CREATE TABLE `diet` (
  `id` int NOT NULL AUTO_INCREMENT,
  `patient_id` int DEFAULT NULL,
  `visits_id` int DEFAULT NULL,
  `doctor_id` int DEFAULT NULL,
  `meal` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `weight_g` int DEFAULT NULL,
  `water_intake` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `time` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `food_item` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `key_vitamins` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `benefits` text COLLATE utf8mb4_general_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `patient_id` (`patient_id`),
  KEY `visits_id` (`visits_id`),
  KEY `doctor_id` (`doctor_id`),
  CONSTRAINT `diet_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`),
  CONSTRAINT `diet_ibfk_2` FOREIGN KEY (`visits_id`) REFERENCES `visits` (`id`),
  CONSTRAINT `diet_ibfk_3` FOREIGN KEY (`doctor_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `diet`
--


--
-- Table structure for table `diet_by_doctor`
--

CREATE TABLE `diet_by_doctor` (
  `id` int NOT NULL AUTO_INCREMENT,
  `patient_id` int DEFAULT NULL,
  `visits_id` int DEFAULT NULL,
  `doctor_id` int DEFAULT NULL,
  `diet_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `patient_id` (`patient_id`),
  KEY `visits_id` (`visits_id`),
  KEY `doctor_id` (`doctor_id`),
  CONSTRAINT `diet_by_doctor_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`),
  CONSTRAINT `diet_by_doctor_ibfk_2` FOREIGN KEY (`visits_id`) REFERENCES `visits` (`id`),
  CONSTRAINT `diet_by_doctor_ibfk_3` FOREIGN KEY (`doctor_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `diet_by_doctor`
--


--
-- Table structure for table `doctor_timings`
--

CREATE TABLE `doctor_timings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doctor_id` int NOT NULL,
  `day_of_week` enum('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday') COLLATE utf8mb4_general_ci NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `doctor_id` (`doctor_id`),
  CONSTRAINT `doctor_timings_ibfk_1` FOREIGN KEY (`doctor_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `doctor_timings`
--


--
-- Table structure for table `lifestylefactors`
--

CREATE TABLE `lifestylefactors` (
  `id` int NOT NULL,
  `fk_patients` int NOT NULL,
  `tobaccouse` tinyint NOT NULL DEFAULT '0',
  `AlcoholConsumption` varchar(45) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `lifestylefactors`
--


--
-- Table structure for table `llx_c_country`
--

CREATE TABLE `llx_c_country` (
  `rowid` int NOT NULL,
  `code` varchar(2) NOT NULL,
  `code_iso` varchar(3) DEFAULT NULL,
  `numeric_code` varchar(3) DEFAULT NULL,
  `label` varchar(128) NOT NULL,
  `eec` tinyint NOT NULL DEFAULT '0',
  `active` tinyint NOT NULL DEFAULT '1',
  `favorite` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`rowid`),
  UNIQUE KEY `idx_c_country_code` (`code`),
  UNIQUE KEY `idx_c_country_label` (`label`),
  UNIQUE KEY `idx_c_country_code_iso` (`code_iso`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `llx_c_country`
--

INSERT INTO `llx_c_country` VALUES (0,'',NULL,NULL,'-',0,1,1),(1,'FR','FRA','250','France',1,1,0),(2,'BE','BEL','056','Belgium',1,1,0),(3,'IT','ITA','380','Italy',1,1,0),(4,'ES','ESP','724','Spain',1,1,0),(5,'DE','DEU','276','Germany',1,1,0),(6,'CH','CHE','756','Switzerland',0,1,0),(7,'GB','GBR','826','United Kingdom',0,1,0),(8,'IE','IRL','372','Ireland',1,1,0),(9,'CN','CHN','156','China',0,1,0),(10,'TN','TUN','788','Tunisia',0,1,0),(11,'US','USA','840','United States',0,1,0),(12,'MA','MAR','504','Morocco',0,1,0),(13,'DZ','DZA','012','Algeria',0,1,0),(14,'CA','CAN','124','Canada',0,1,0),(15,'TG','TGO','768','Togo',0,1,0),(16,'GA','GAB','266','Gabon',0,1,0),(17,'NL','NLD','528','Netherlands',1,1,0),(18,'HU','HUN','348','Hungary',1,1,0),(19,'RU','RUS','643','Russia',0,1,0),(20,'SE','SWE','752','Sweden',1,1,0),(21,'CI','CIV','384','Côte d\'Ivoire',0,1,0),(22,'SN','SEN','686','Senegal',0,1,0),(23,'AR','ARG','032','Argentina',0,1,0),(24,'CM','CMR','120','Cameroun',0,1,0),(25,'PT','PRT','620','Portugal',1,1,0),(26,'SA','SAU','682','Saudi Arabia',0,1,0),(27,'MC','MCO','492','Monaco',1,1,0),(28,'AU','AUS','036','Australia',0,1,0),(29,'SG','SGP','702','Singapore',0,1,0),(30,'AF','AFG','004','Afghanistan',0,1,0),(31,'AX','ALA','248','Åland Island',0,1,0),(32,'AL','ALB','008','Albania',0,1,0),(33,'AS','ASM','016','American Samoa',0,1,0),(34,'AD','AND','020','Andorra',0,1,0),(35,'AO','AGO','024','Angola',0,1,0),(36,'AI','AIA','660','Anguilla',0,1,0),(37,'AQ','ATA','010','Antarctica',0,1,0),(38,'AG','ATG','028','Antigua and Barbuda',0,1,0),(39,'AM','ARM','051','Armenia',0,1,0),(40,'AW','ABW','533','Aruba',0,1,0),(41,'AT','AUT','040','Austria',1,1,0),(42,'AZ','AZE','031','Azerbaijan',0,1,0),(43,'BS','BHS','044','Bahamas',0,1,0),(44,'BH','BHR','048','Bahrain',0,1,0),(45,'BD','BGD','050','Bangladesh',0,1,0),(46,'BB','BRB','052','Barbados',0,1,0),(47,'BY','BLR','112','Belarus',0,1,0),(48,'BZ','BLZ','084','Belize',0,1,0),(49,'BJ','BEN','204','Benin',0,1,0),(50,'BM','BMU','060','Bermuda',0,1,0),(51,'BT','BTN','064','Bhutan',0,1,0),(52,'BO','BOL','068','Bolivia',0,1,0),(53,'BA','BIH','070','Bosnia and Herzegovina',0,1,0),(54,'BW','BWA','072','Botswana',0,1,0),(55,'BV','BVT','074','Bouvet Island',0,0,0),(56,'BR','BRA','076','Brazil',0,1,0),(57,'IO','IOT','086','British Indian Ocean Territory',0,1,0),(58,'BN','BRN','096','Brunei',0,1,0),(59,'BG','BGR','100','Bulgaria',1,1,0),(60,'BF','BFA','854','Burkina Faso',0,1,0),(61,'BI','BDI','108','Burundi',0,1,0),(62,'KH','KHM','116','Cambodge / Cambodia',0,1,0),(63,'CV','CPV','132','Cap-Vert',0,1,0),(64,'KY','CYM','136','Iles Cayman',0,1,0),(65,'CF','CAF','140','Central African Republic (CAR/RCA)',0,1,0),(66,'TD','TCD','148','Tchad',0,1,0),(67,'CL','CHL','152','Chile',0,1,0),(68,'CX','CXR','162','Ile Christmas',0,1,0),(69,'CC','CCK','166','Iles des Cocos (Keeling)',0,1,0),(70,'CO','COL','170','Colombie',0,1,0),(71,'KM','COM','174','Comores',0,1,0),(72,'CG','COG','178','Congo',0,1,0),(73,'CD','COD','180','DR Congo (RDC)',0,1,0),(74,'CK','COK','184','Iles Cook',0,1,0),(75,'CR','CRI','188','Costa Rica',0,1,0),(76,'HR','HRV','191','Croatia',1,1,0),(77,'CU','CUB','192','Cuba',0,1,0),(78,'CY','CYP','196','Cyprus',1,1,0),(79,'CZ','CZE','203','Czech Republic',1,1,0),(80,'DK','DNK','208','Denmark',1,1,0),(81,'DJ','DJI','262','Djibouti',0,1,0),(82,'DM','DMA','212','Dominica',0,1,0),(83,'DO','DOM','214','Dominican Republic',0,1,0),(84,'EC','ECU','218','Republic of Ecuador',0,1,0),(85,'EG','EGY','818','Egypt',0,1,0),(86,'SV','SLV','222','El Salvador',0,1,0),(87,'GQ','GNQ','226','Equatorial Guinea',0,1,0),(88,'ER','ERI','232','Eritrea',0,1,0),(89,'EE','EST','233','Estonia',1,1,0),(90,'ET','ETH','231','Ethiopia',0,1,0),(91,'FK','FLK','238','Falkland Islands',0,1,0),(92,'FO','FRO','234','Faroe Islands',0,1,0),(93,'FJ','FJI','242','Fidji Islands',0,1,0),(94,'FI','FIN','246','Finland',1,1,0),(95,'GF','GUF','254','French Guiana',0,1,0),(96,'PF','PYF','258','French Polynesia',0,1,0),(97,'TF','ATF','260','Terres australes françaises',0,1,0),(98,'GM','GMB','270','Gambie',0,1,0),(99,'GE','GEO','268','Georgia',0,1,0),(100,'GH','GHA','288','Ghana',0,1,0),(101,'GI','GIB','292','Gibraltar',0,1,0),(102,'GR','GRC','300','Greece',1,1,0),(103,'GL','GRL','304','Groenland',0,1,0),(104,'GD','GRD','308','Grenade',0,1,0),(106,'GU','GUM','316','Guam',0,1,0),(107,'GT','GTM','320','Guatemala',0,1,0),(108,'GN','GIN','324','Guinea',0,1,0),(109,'GW','GNB','624','Guinea-Bissao',0,1,0),(111,'HT','HTI','332','Haiti',0,1,0),(112,'HM','HMD','334','Iles Heard et McDonald',0,1,0),(113,'VA','VAT','336','Vatican City (Saint-Siège)',0,1,0),(114,'HN','HND','340','Honduras',0,1,0),(115,'HK','HKG','344','Hong Kong',0,1,0),(116,'IS','ISL','352','Islande',0,1,0),(117,'IN','IND','356','India',0,1,0),(118,'ID','IDN','360','Indonesia',0,1,0),(119,'IR','IRN','364','Iran',0,1,0),(120,'IQ','IRQ','368','Iraq',0,1,0),(121,'IL','ISR','376','Israel',0,1,0),(122,'JM','JAM','388','Jamaica',0,1,0),(123,'JP','JPN','392','Japan (Nippon)',0,1,0),(124,'JO','JOR','400','Jordanie',0,1,0),(125,'KZ','KAZ','398','Kazakhstan',0,1,0),(126,'KE','KEN','404','Kenya',0,1,0),(127,'KI','KIR','296','Kiribati',0,1,0),(128,'KP','PRK','408','North Corea',0,1,0),(129,'KR','KOR','410','South Corea',0,1,0),(130,'KW','KWT','414','Koweït',0,1,0),(131,'KG','KGZ','417','Kirghizistan',0,1,0),(132,'LA','LAO','418','Laos',0,1,0),(133,'LV','LVA','428','Lettonie',1,1,0),(134,'LB','LBN','422','Liban',0,1,0),(135,'LS','LSO','426','Lesotho',0,1,0),(136,'LR','LBR','430','Liberia',0,1,0),(137,'LY','LBY','434','Libye',0,1,0),(138,'LI','LIE','438','Liechtenstein',0,1,0),(139,'LT','LTU','440','Lituanie',1,1,0),(140,'LU','LUX','442','Luxembourg',1,1,0),(141,'MO','MAC','446','Macao',0,1,0),(142,'MK','MKD','807','North Macedonia',0,1,0),(143,'MG','MDG','450','Madagascar',0,1,0),(144,'MW','MWI','454','Malawi',0,1,0),(145,'MY','MYS','458','Malaisie',0,1,0),(146,'MV','MDV','462','Maldives',0,1,0),(147,'ML','MLI','466','Mali',0,1,0),(148,'MT','MLT','470','Malte',1,1,0),(149,'MH','MHL','584','Iles Marshall',0,1,0),(151,'MR','MRT','478','Mauritanie',0,1,0),(152,'MU','MUS','480','Maurice',0,1,0),(153,'YT','MYT','175','Mayotte',0,1,0),(154,'MX','MEX','484','Mexique',0,1,0),(155,'FM','FSM','583','Micronésie',0,1,0),(156,'MD','MDA','498','Moldavie',0,1,0),(157,'MN','MNG','496','Mongolie',0,1,0),(158,'MS','MSR','500','Monserrat',0,1,0),(159,'MZ','MOZ','508','Mozambique',0,1,0),(160,'MM','MMR','104','Birmanie (Myanmar)',0,1,0),(161,'NA','NAM','516','Namibie',0,1,0),(162,'NR','NRU','520','Nauru',0,1,0),(163,'NP','NPL','524','Népal',0,1,0),(165,'NC','NCL','540','New Caledonia',0,1,0),(166,'NZ','NZL','554','New Zealand',0,1,0),(167,'NI','NIC','558','Nicaragua',0,1,0),(168,'NE','NER','562','Niger',0,1,0),(169,'NG','NGA','566','Nigeria',0,1,0),(170,'NU','NIU','570','Niue',0,1,0),(171,'NF','NFK','574','Norfolk Island',0,1,0),(172,'MP','MNP','580','Northern Mariana Islands',0,1,0),(173,'NO','NOR','578','Norway',0,1,0),(174,'OM','OMN','512','Oman',0,1,0),(175,'PK','PAK','586','Pakistan',0,1,0),(176,'PW','PLW','585','Palau',0,1,0),(177,'PS','PSE','275','Palestinian territories',0,1,0),(178,'PA','PAN','591','Panama',0,1,0),(179,'PG','PNG','598','Papua New Guinea',0,1,0),(180,'PY','PRY','600','Paraguay',0,1,0),(181,'PE','PER','604','Peru',0,1,0),(182,'PH','PHL','608','Philippines',0,1,0),(183,'PN','PCN','612','Pitcairn Islands',0,1,0),(184,'PL','POL','616','Pologne',1,1,0),(185,'PR','PRI','630','Puerto Rico',0,1,0),(186,'QA','QAT','634','Qatar',0,1,0),(188,'RO','ROU','642','Romania',1,1,0),(189,'RW','RWA','646','Rwanda',0,1,0),(190,'SH','SHN','654','Saint Helena',0,1,0),(191,'KN','KNA','659','Saint Kitts and Nevis',0,1,0),(192,'LC','LCA','662','Saint Lucia',0,1,0),(193,'PM','SPM','666','Saint Pierre and Miquelon',0,1,0),(194,'VC','VCT','670','Saint Vincent and the Grenadines',0,1,0),(195,'WS','WSM','882','Samoa',0,1,0),(196,'SM','SMR','674','San Marino ',0,1,0),(197,'ST','STP','678','Saint Thomas and Prince',0,1,0),(198,'RS','SRB','688','Serbia',0,1,0),(199,'SC','SYC','690','Seychelles',0,1,0),(200,'SL','SLE','694','Sierra Leone',0,1,0),(201,'SK','SVK','703','Slovakia',1,1,0),(202,'SI','SVN','705','Slovenia',1,1,0),(203,'SB','SLB','090','Solomon Islands',0,1,0),(204,'SO','SOM','706','Somalia',0,1,0),(205,'ZA','ZAF','710','South Africa',0,1,0),(206,'GS','SGS','239','South Georgia and the South Sandwich Islands ',0,1,0),(207,'LK','LKA','144','Sri Lanka',0,1,0),(208,'SD','SDN','729','Sudan',0,1,0),(209,'SR','SUR','740','Suriname',0,1,0),(210,'SJ','SJM','744','Svalbard and Jan Mayen',0,1,0),(211,'SZ','SWZ','748','Swaziland / Eswatini',0,1,0),(212,'SY','SYR','760','Syria',0,1,0),(213,'TW','TWN','158','Taiwan',0,1,0),(214,'TJ','TJK','762','Tajikistan',0,1,0),(215,'TZ','TZA','834','Tanzania',0,1,0),(216,'TH','THA','764','Thailand',0,1,0),(217,'TL','TLS','626','Timor-Leste',0,1,0),(218,'TK','TKL','772','Tokelau',0,1,0),(219,'TO','TON','776','Tonga',0,1,0),(220,'TT','TTO','780','Trinidad and Tobago',0,1,0),(221,'TR','TUR','792','Turkey',0,1,0),(222,'TM','TKM','795','Turkmenistan',0,1,0),(223,'TC','TCA','796','Turks and Caicos Islands',0,1,0),(224,'TV','TUV','798','Tuvalu',0,1,0),(225,'UG','UGA','800','Uganda',0,1,0),(226,'UA','UKR','804','Ukraine',0,1,0),(227,'AE','ARE','784','United Arab Emirates',0,1,0),(228,'UM','UMI','581','United States Minor Outlying Islands',0,1,0),(229,'UY','URY','858','Uruguay',0,1,0),(230,'UZ','UZB','860','Uzbekistan',0,1,0),(231,'VU','VUT','548','Vanuatu',0,1,0),(232,'VE','VEN','862','Venezuela',0,1,0),(233,'VN','VNM','704','Vietnam',0,1,0),(234,'VG','VGB','092','British Virgin Islands',0,1,0),(235,'VI','VIR','850','Virgin Islands of the United States',0,1,0),(236,'WF','WLF','876','Wallis and Futuna',0,1,0),(237,'EH','ESH','732','Western Sahara',0,1,0),(238,'YE','YEM','887','Yemen',0,1,0),(239,'ZM','ZMB','894','Zambia',0,1,0),(240,'ZW','ZWE','716','Zimbabwe',0,1,0),(241,'GG','GGY','831','Guernsey',0,1,0),(242,'IM','IMN','833','Isle of Man',0,1,0),(243,'JE','JEY','832','Jersey',0,1,0),(244,'ME','MNE','499','Montenegro',0,1,0),(245,'BL','BLM','652','Saint-Barthélemy',0,1,0),(246,'MF','MAF','663','Saint-Martin',0,1,0),(247,'XK','XKX',NULL,'Kosovo',0,1,0),(300,'CW','CUW','531','Curaçao',0,1,0),(301,'SX','SXM','534','Sint Maarten',0,1,0);

--
-- Table structure for table `medicalhistory`
--

CREATE TABLE `medicalhistory` (
  `id` int NOT NULL AUTO_INCREMENT,
  `fk_patients` int NOT NULL,
  `pastmedicalconditions` text COLLATE utf8mb4_general_ci,
  `allergies` text COLLATE utf8mb4_general_ci,
  `medications` text COLLATE utf8mb4_general_ci,
  `familymedicalhistory` text COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`id`),
  KEY `idk_fk_patients` (`fk_patients`),
  CONSTRAINT `idk_fk_patients` FOREIGN KEY (`fk_patients`) REFERENCES `patients` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `medicalhistory`
--


--
-- Table structure for table `medications`
--

CREATE TABLE `medications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `patient_id` int DEFAULT NULL,
  `visits_id` int DEFAULT NULL,
  `medications` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `doctor_id` int DEFAULT NULL,
  `duration` varchar(255) DEFAULT NULL,
  `salt` varchar(255) DEFAULT NULL,
  `dosage` varchar(255) DEFAULT NULL,
  `time` varchar(255) DEFAULT NULL,
  `doseinterval` varchar(255) DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `patient_id` (`patient_id`),
  KEY `visits_id` (`visits_id`),
  CONSTRAINT `medications_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`),
  CONSTRAINT `medications_ibfk_2` FOREIGN KEY (`visits_id`) REFERENCES `visits` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `medications`
--


--
-- Table structure for table `medications_by_doctor`
--

CREATE TABLE `medications_by_doctor` (
  `id` int NOT NULL AUTO_INCREMENT,
  `patient_id` int DEFAULT NULL,
  `visits_id` int DEFAULT NULL,
  `doctor_id` int DEFAULT NULL,
  `medications` varchar(100) DEFAULT NULL,
  `dose_timings` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `patient_id` (`patient_id`),
  KEY `visits_id` (`visits_id`),
  KEY `doctor_id` (`doctor_id`),
  CONSTRAINT `medications_by_doctor_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`),
  CONSTRAINT `medications_by_doctor_ibfk_2` FOREIGN KEY (`visits_id`) REFERENCES `visits` (`id`),
  CONSTRAINT `medications_by_doctor_ibfk_3` FOREIGN KEY (`doctor_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `medications_by_doctor`
--


--
-- Table structure for table `paitent_possible_cause`
--

CREATE TABLE `paitent_possible_cause` (
  `id` int NOT NULL AUTO_INCREMENT,
  `fk_visits` int NOT NULL,
  `fk_patients` int NOT NULL,
  `fk_causes` int NOT NULL,
  `createdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifieddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_paitent_possible_cause_visits1` (`fk_visits`),
  KEY `fk_paitent_possible_cause_patients1` (`fk_patients`),
  KEY `fk_paitent_possible_cause_causes1` (`fk_causes`),
  CONSTRAINT `fk_paitent_possible_cause_causes1` FOREIGN KEY (`fk_causes`) REFERENCES `causes` (`id`),
  CONSTRAINT `fk_paitent_possible_cause_patients1` FOREIGN KEY (`fk_patients`) REFERENCES `patients` (`id`),
  CONSTRAINT `fk_paitent_possible_cause_visits1` FOREIGN KEY (`fk_visits`) REFERENCES `visits` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `paitent_possible_cause`
--


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

--
-- Table structure for table `possible_causes`
--

CREATE TABLE `possible_causes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `patient_id` int DEFAULT NULL,
  `visits_id` int DEFAULT NULL,
  `possible_causes` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `doctor_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `patient_id` (`patient_id`),
  KEY `visits_id` (`visits_id`),
  CONSTRAINT `possible_causes_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`),
  CONSTRAINT `possible_causes_ibfk_2` FOREIGN KEY (`visits_id`) REFERENCES `visits` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `possible_causes`
--


--
-- Table structure for table `possible_causes_by_doctor`
--

CREATE TABLE `possible_causes_by_doctor` (
  `id` int NOT NULL AUTO_INCREMENT,
  `patient_id` int DEFAULT NULL,
  `visits_id` int DEFAULT NULL,
  `doctor_id` int DEFAULT NULL,
  `possible_causes` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `patient_id` (`patient_id`),
  KEY `visits_id` (`visits_id`),
  KEY `doctor_id` (`doctor_id`),
  CONSTRAINT `possible_causes_by_doctor_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`),
  CONSTRAINT `possible_causes_by_doctor_ibfk_2` FOREIGN KEY (`visits_id`) REFERENCES `visits` (`id`),
  CONSTRAINT `possible_causes_by_doctor_ibfk_3` FOREIGN KEY (`doctor_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `possible_causes_by_doctor`
--


--
-- Table structure for table `reasonforvisit`
--

CREATE TABLE `reasonforvisit` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `createddate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `reasonforvisit`
--


--
-- Table structure for table `symptoms`
--

CREATE TABLE `symptoms` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'This is the symptoms name that we need to save',
  `createdby` int NOT NULL DEFAULT '1' COMMENT '1: Created by AI\\r\\n2: Created by Doctor',
  `datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `symptoms`
--


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

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `firstname` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `lastname` varchar(25) COLLATE utf8mb4_general_ci NOT NULL,
  `role` int NOT NULL COMMENT '1: admin, 2: doctor, 3: compounder, 4: patient ',
  `phone` bigint DEFAULT NULL,
  `status` int NOT NULL DEFAULT '1',
  `createdatetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifieddatetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `filename` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `users` VALUES (2,'pratushraj1@gmail.com','$2b$12$q945w8XKLdW7sMbfK3Doa.93qZdCBRj1IZOZzXyR.01q3tWedxPSe','Pratush','Raj',2,NULL,0,'2024-07-04 09:08:15','2024-07-04 09:08:15','Screenshot (4908).png'),(8,'testuser@example.com','$2b$12$JR1nKcW4B2oVobH9m48rxuJbd1nIMxLJ5SfVH5w7JCrgjA/retCLO','Jane','Doe',2,NULL,0,'2024-08-23 11:23:19','2024-08-23 11:23:19','Screenshot (4909).png');

--
-- Table structure for table `visits`
--

CREATE TABLE `visits` (
  `id` int NOT NULL AUTO_INCREMENT,
  `fk_patients` int DEFAULT NULL,
  `visitdate` date NOT NULL,
  `reasonforvisit` text COLLATE utf8mb4_general_ci NOT NULL,
  `symptoms` text COLLATE utf8mb4_general_ci NOT NULL,
  `bloodpressure` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `height` float DEFAULT NULL,
  `weight` float DEFAULT NULL,
  `BMI` float DEFAULT NULL,
  `glucoselevel` float DEFAULT NULL,
  `pulseoximetry` float DEFAULT NULL,
  `recentillnessesorinjuries` text COLLATE utf8mb4_general_ci,
  `additionalnotesorcomments` text COLLATE utf8mb4_general_ci,
  `fk_doctor` int DEFAULT NULL,
  `possible_cause` tinyint DEFAULT NULL,
  `diagnostics` tinyint DEFAULT NULL,
  `medications` tinyint DEFAULT NULL,
  `diet` tinyint DEFAULT NULL,
  `food_type` enum('veg','non-veg','mix') COLLATE utf8mb4_general_ci DEFAULT 'veg',
  PRIMARY KEY (`id`),
  KEY `fk_patients` (`fk_patients`),
  CONSTRAINT `visits_ibfk_1` FOREIGN KEY (`fk_patients`) REFERENCES `patients` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `visits`
--

INSERT INTO `visits` VALUES (5,5,'2024-08-20','Consultation for flu symptoms','Cough, fever, sore throat','118/78',175,71,23.2,NULL,NULL,NULL,NULL,2,1,1,1,1,'veg'),(6,5,'2024-08-15','Follow-up on hypertension','Headache, dizziness','130/85',175,72,23.5,NULL,NULL,NULL,NULL,2,1,1,0,0,'veg'),(9,5,'2024-07-30','Consultation for back pain','Lower back pain','122/79',175,73,23.8,NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,NULL,'veg'),(10,5,'2024-07-25','Check-up after surgery','Pain at surgical site','118/76',175,71,23.2,NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,NULL,'veg'),(11,5,'2024-07-20','Consultation for fatigue','Persistent tiredness','117/75',175,72,23.5,NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,NULL,'veg'),(12,5,'2024-07-15','Visit for skin rash','Itchy red rash on arms','120/80',175,70,22.9,NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,NULL,'veg'),(13,5,'2024-07-10','Follow-up on diabetes management','Increased thirst, frequent urination','130/85',175,74,24.1,NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,NULL,'veg');

--
-- Dumping events for database 'medvista'
--

--
-- Dumping routines for database 'medvista'
--


