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

START TRANSACTION;



--
-- Database: `medvista`
--

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

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

CREATE TABLE `causes` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `createdby` int(11) NOT NULL DEFAULT 1 COMMENT '1: Created by AI\\r\\n2: Created by Doctor',
  `fk_symptoms` int(11) NOT NULL,
  `fk_reasonforvisit` int(11) NOT NULL,
  `datetime` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

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

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

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

CREATE TABLE `medicalhistory` (
  `id` int(11) NOT NULL,
  `fk_patients` int(11) NOT NULL,
  `pastmedicalconditions` text DEFAULT NULL,
  `allergies` text DEFAULT NULL,
  `medications` text DEFAULT NULL,
  `familymedicalhistory` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `paitent_possible_cause` (
  `id` int(11) NOT NULL,
  `fk_visits` int(11) NOT NULL,
  `fk_patients` int(11) NOT NULL,
  `fk_causes` int(11) NOT NULL,
  `createdate` timestamp NOT NULL DEFAULT current_timestamp(),
  `modifieddate` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `patients` (
  `id` int(11) NOT NULL,
  `fk_doctor` int(11) NOT NULL,
  `fullname` varchar(255) NOT NULL,
  `dateOfbirth` date NOT NULL,
  `gender` varchar(10) NOT NULL,
  `age` int(11) NOT NULL,
  `address` varchar(255) NOT NULL,
  `phonenumber` varchar(20) NOT NULL,
  `emailid` varchar(255) NOT NULL,
  `tobaccouse` tinyint(4) NOT NULL DEFAULT 0,
  `alcoholconsumption` tinyint(4) NOT NULL DEFAULT 0,
  `dietandexercisehabits` text DEFAULT NULL,
  `pastmedicalconditions` text DEFAULT NULL,
  `allergies` text DEFAULT NULL,
  `medications` text DEFAULT NULL,
  `familymedicalhistory` text DEFAULT NULL,
  `datetime` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `patients` (`id`, `fk_doctor`, `fullname`, `dateOfbirth`, `gender`, `age`, `address`, `phonenumber`, `emailid`, `tobaccouse`, `alcoholconsumption`, `dietandexercisehabits`, `pastmedicalconditions`, `allergies`, `medications`, `familymedicalhistory`, `datetime`) 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'),
(5, 2, 'Pratush Raj', '1998-05-02', 'Male', 25, 'bhcddj, bcjd', '1234566555', 'pratushraj20@gmail.com', 0, 0, '', '', '', '', '', '2024-07-04 09:55:46');

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

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

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

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

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

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

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

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

CREATE TABLE `visits` (
  `id` int(11) NOT NULL,
  `fk_patients` int(11) DEFAULT NULL,
  `visitdate` date NOT NULL,
  `reasonforvisit` text NOT NULL,
  `symptoms` text NOT NULL,
  `bloodpressure` varchar(20) DEFAULT NULL,
  `height` float DEFAULT NULL,
  `weight` float DEFAULT NULL,
  `BMI` float DEFAULT NULL,
  `glucoselevel` float DEFAULT NULL,
  `pulseoximetry` float DEFAULT NULL,
  `recentillnessesorinjuries` text DEFAULT NULL,
  `additionalnotesorcomments` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `causes`
--
ALTER TABLE `causes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_causes_symptoms1` (`fk_symptoms`),
  ADD KEY `fk_causes_reasonforvisit1` (`fk_reasonforvisit`);

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

--
-- Indexes for table `medicalhistory`
--
ALTER TABLE `medicalhistory`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idk_fk_patients` (`fk_patients`);

--
-- Indexes for table `paitent_possible_cause`
--
ALTER TABLE `paitent_possible_cause`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_paitent_possible_cause_visits1` (`fk_visits`),
  ADD KEY `fk_paitent_possible_cause_patients1` (`fk_patients`),
  ADD KEY `fk_paitent_possible_cause_causes1` (`fk_causes`);

--
-- Indexes for table `patients`
--
ALTER TABLE `patients`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `phonenumber` (`phonenumber`),
  ADD UNIQUE KEY `emailid` (`emailid`),
  ADD KEY `fk_patients_users1` (`fk_doctor`);

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

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

--
-- Indexes for table `visits`
--
ALTER TABLE `visits`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_patients` (`fk_patients`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `causes`
--
ALTER TABLE `causes`
  ADD CONSTRAINT `fk_causes_reasonforvisit1` FOREIGN KEY (`fk_reasonforvisit`) REFERENCES `reasonforvisit` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_causes_symptoms1` FOREIGN KEY (`fk_symptoms`) REFERENCES `symptoms` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `medicalhistory`
--
ALTER TABLE `medicalhistory`
  ADD CONSTRAINT `idk_fk_patients` FOREIGN KEY (`fk_patients`) REFERENCES `patients` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `paitent_possible_cause`
--
ALTER TABLE `paitent_possible_cause`
  ADD CONSTRAINT `fk_paitent_possible_cause_causes1` FOREIGN KEY (`fk_causes`) REFERENCES `causes` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_paitent_possible_cause_patients1` FOREIGN KEY (`fk_patients`) REFERENCES `patients` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_paitent_possible_cause_visits1` FOREIGN KEY (`fk_visits`) REFERENCES `visits` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `patients`
--
ALTER TABLE `patients`
  ADD CONSTRAINT `fk_patients_users1` FOREIGN KEY (`fk_doctor`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `visits`
--
ALTER TABLE `visits`
  ADD CONSTRAINT `visits_ibfk_1` FOREIGN KEY (`fk_patients`) REFERENCES `patients` (`id`);
COMMIT;


CREATE TABLE possible_causes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    visits_id INT,
    possible_causes VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (visits_id) REFERENCES visits(id)
);

CREATE TABLE possible_causes_by_doctor (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    visits_id INT,
    doctor_id INT,
    possible_causes VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (visits_id) REFERENCES visits(id),
    FOREIGN KEY (doctor_id) REFERENCES users(id)
);

CREATE TABLE diagnostics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    visits_id INT,
    diagnostics VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (visits_id) REFERENCES visits(id)
);

CREATE TABLE diagnostics_by_doctor (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    visits_id INT,
    doctor_id INT,
    diagnostics VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (visits_id) REFERENCES visits(id),
    FOREIGN KEY (doctor_id) REFERENCES users(id)
);

CREATE TABLE medications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    visits_id INT,
    medications VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (visits_id) REFERENCES visits(id)
);

CREATE TABLE medications_by_doctor (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    visits_id INT,
    doctor_id INT,
    medications VARCHAR(100),
    dose_timings VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (visits_id) REFERENCES visits(id),
    FOREIGN KEY (doctor_id) REFERENCES users(id)
);

ALTER TABLE possible_causes
ADD COLUMN doctor_id INT DEFAULT NULL;

ALTER TABLE diagnostics
ADD COLUMN doctor_id INT DEFAULT NULL;

ALTER TABLE medications
ADD COLUMN doctor_id INT DEFAULT NULL;

ALTER TABLE visits
ADD COLUMN fk_doctor INT DEFAULT NULL;


ALTER TABLE `visits`
ADD COLUMN `possible_cause` TINYINT DEFAULT NULL,
ADD COLUMN `diagnostics` TINYINT DEFAULT NULL,
ADD COLUMN `medications` TINYINT DEFAULT NULL;

ALTER TABLE medications
ADD COLUMN duration VARCHAR(255) NULL,
ADD COLUMN salt VARCHAR(255) NULL,
ADD COLUMN dosage VARCHAR(255) NULL,
ADD COLUMN time VARCHAR(255) NULL,
ADD COLUMN doseinterval VARCHAR(255) NULL,
ADD COLUMN reason VARCHAR(255) NULL;



INSERT INTO `patients` (`fk_doctor`, `fullname`, `dateOfbirth`, `gender`, `age`, `address`, `phonenumber`, `emailid`, `tobaccouse`, `alcoholconsumption`, `dietandexercisehabits`, `pastmedicalconditions`, `allergies`, `medications`, `familymedicalhistory`, `datetime`) VALUES
( 2, 'Dev Namdev', '1998-05-02', 'Male', 25, 'bhcddj, bcjd', '9926040407', 'devnamdevcse@gmail.com', 0, 0, NULL, NULL, NULL, NULL, NULL, '2024-07-04 09:08:41');

ALTER TABLE patients
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER DATABASE medvista
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE patients
ADD COLUMN patients_id VARCHAR(255) UNIQUE;

DROP TRIGGER IF EXISTS before_insert_patients;

DELIMITER $$

CREATE 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');
  
  SET new_id = CONCAT(@date_prefix, '_', max_id + 1);
  
  SET NEW.patients_id = new_id;
END$$

DELIMITER ;

ALTER TABLE visits
ADD COLUMN follow_up VARCHAR(255) DEFAULT NULL;

CREATE TABLE doctor_timings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    doctor_id INT NOT NULL,
    day_of_week ENUM('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (doctor_id) REFERENCES users(id)
);

INSERT INTO doctor_timings (doctor_id, day_of_week, start_time, end_time)
VALUES 
(2, 'Monday', '10:00:00', '17:00:00'),
(2, 'Tuesday', '12:00:00', '18:00:00'),
(2, 'Wednesday', '09:00:00', '16:00:00'),
(2, 'Thursday', '11:00:00', '17:00:00'),
(2, 'Friday', '10:30:00', '15:30:00');

CREATE TABLE diet_by_doctor (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    visits_id INT,
    doctor_id INT,
    diet_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (visits_id) REFERENCES visits(id),
    FOREIGN KEY (doctor_id) REFERENCES users(id)
);

CREATE TABLE diet(
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT,
    visits_id INT,
    doctor_id INT,
    meal VARCHAR(500),
    weight_g INT,
    water_intake VARCHAR(100),
    time VARCHAR(100),
    food_item VARCHAR(500),
    key_vitamins VARCHAR(500),
    benefits TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (visits_id) REFERENCES visits(id),
    FOREIGN KEY (doctor_id) REFERENCES users(id)
);



ALTER TABLE `visits`
ADD COLUMN `diet` TINYINT DEFAULT NULL;

ALTER TABLE visits
ADD COLUMN food_type ENUM('veg', 'non-veg', 'mix') DEFAULT 'veg';


CREATE TABLE file_uploads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    filevalue LONGBLOB NOT NULL,
    file_type VARCHAR(50),
    purpose ENUM('profile', 'logo', 'signature', 'other') NOT NULL, -- Added purpose column
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive', 'deleted') DEFAULT 'active'
);


ALTER TABLE possible_causes
ADD COLUMN updated_by INT DEFAULT 0;
ALTER TABLE diagnostics
ADD COLUMN updated_by INT DEFAULT 0;
ALTER TABLE medications
ADD COLUMN updated_by INT DEFAULT 0;
ALTER TABLE diet
ADD COLUMN updated_by INT DEFAULT 0;


CREATE TABLE weekly_diet (
  id int NOT NULL AUTO_INCREMENT,
  patient_id int DEFAULT NULL,
  visits_id int DEFAULT NULL,
  doctor_id int DEFAULT NULL,
  day1meal TEXT DEFAULT NULL,
  day2meal TEXT DEFAULT NULL,
  day3meal TEXT DEFAULT NULL,
  day4meal TEXT DEFAULT NULL,
  day5meal TEXT DEFAULT NULL,
  day6meal TEXT DEFAULT NULL,
  day7meal TEXT DEFAULT NULL,
  created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by int DEFAULT '0',
  PRIMARY KEY (id)
);


CREATE TABLE user_activity_management (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- Auto-increment primary key
    user_id INT NOT NULL, -- Foreign key to the users table
    status ENUM('online', 'offline', 'inactive') NOT NULL DEFAULT 'offline', -- User status
    last_login_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Timestamp of the last login
    last_logout_time DATETIME DEFAULT NULL, -- Timestamp of the last logout
    total_active_time TIME DEFAULT '00:00:00', -- Total active time in the current session
    total_online_time TIME DEFAULT '00:00:00', -- Total accumulated online time
    is_active BOOLEAN NOT NULL DEFAULT TRUE, -- Indicates if the user is currently active
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- Record update timestamp
);


DROP TRIGGER IF EXISTS before_insert_patients;