-- Create database
CREATE DATABASE patient_readmission;

-- Use database
USE patient_readmission;

-- Create patients table
CREATE TABLE patients (
    patient_id INT PRIMARY KEY,
    age INT,
    gender VARCHAR(10),
    admission_date DATE,
    discharge_date DATE
);

-- Create diagnoses table
CREATE TABLE diagnoses (
    diagnosis_id INT PRIMARY KEY,
    patient_id INT,
    icd_code VARCHAR(10),
    diagnosis_date DATE,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
);

-- Create medications table
CREATE TABLE medications (
    medication_id INT PRIMARY KEY,
    patient_id INT,
    medication_name VARCHAR(50),
    start_date DATE,
    end_date DATE,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
);

-- Create lab_results table
CREATE TABLE lab_results (
    lab_result_id INT PRIMARY KEY,
    patient_id INT,
    lab_test VARCHAR(50),
    result_value DECIMAL(10, 2),
    result_date DATE,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
);

    -- Use window functions to calculate comorbidity index
WITH patient_comorbidities AS (
    SELECT 
        patient_id,
        COUNT(DISTINCT icd_code) AS comorbidity_index
    FROM 
        diagnoses
    GROUP BY 
        patient_id
)
SELECT 
    p.patient_id,
    p.age,
    p.gender,
    pc.comorbidity_index
FROM 
    patients p
JOIN 
    patient_comorbidities pc ON p.patient_id = pc.patient_id;
    
-- Use common table expressions to calculate medication duration
WITH patient_medications AS (
    SELECT 
        patient_id,
        medication_name,
        start_date,
        end_date,
        DATEDIFF(end_date, start_date) AS medication_duration
    FROM 
        medications
)
SELECT 
    p.patient_id,
    p.age,
    p.gender,
    pm.medication_duration
FROM 
    patients p
JOIN 
    patient_medications pm ON p.patient_id = pm.patient_id;
    
-- Use subqueries to calculate lab result average
SELECT 
    p.patient_id,
    p.age,
    p.gender,
    (SELECT AVG(result_value) FROM lab_results WHERE patient_id = p.patient_id) AS lab_result_average
FROM 
    patients p;
    
-- Use indexing and optimization techniques to improve query performance
CREATE INDEX idx_patients_patient_id ON patients (patient_id);
CREATE INDEX idx_diagnoses_patient_id ON diagnoses (patient_id);
CREATE INDEX idx_medications_patient_id ON medications (patient_id);
CREATE INDEX idx_lab_results_patient_id ON lab_results (patient_id);

CREATE TABLE readmission_risk (
    patient_id INT PRIMARY KEY,
    readmission_risk INT
);
    
WITH patient_medications AS (
    SELECT 
        patient_id,
        medication_name,
        start_date,
        end_date,
        DATEDIFF(end_date, start_date) AS medication_duration
    FROM 
        medications
),
lab_result_averages AS (
    SELECT 
        patient_id,
        AVG(result_value) AS lab_result_average
    FROM 
        lab_results
    GROUP BY 
        patient_id
)
SELECT 
    p.patient_id,
    p.age,
    p.gender,
    d.comorbidity_count,
    pm.medication_duration,
    lra.lab_result_average,
    r.risk
FROM 
    patients p
JOIN 
    diagnoses d ON p.patient_id = d.patient_id
JOIN 
    patient_medications pm ON p.patient_id = pm.patient_id
JOIN 
    lab_result_averages lra ON p.patient_id = lra.patient_id
JOIN 
    readmission_risk r ON p.patient_id = r.patient_id;
    SHOW COLUMNS FROM diagnoses;

LOAD DATA INFILE "C:\\Users\\Hp\\Desktop\\IITK\\patient readmission project\\patients2.csv" INTO TABLE patients FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
LOAD DATA INFILE "C:\\Users\\Hp\\Desktop\\IITK\\patient readmission project\\diagnoses2.csv" INTO TABLE diagnoses FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
LOAD DATA INFILE "C:\\Users\\Hp\\Desktop\\IITK\\patient readmission project\\medications2.csv" INTO TABLE medications FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
LOAD DATA INFILE "C:\\Users\\Hp\\Desktop\\IITK\\patient readmission project\\lab_results2.csv" INTO TABLE lab_results FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
LOAD DATA INFILE "C:\\Users\\Hp\\Desktop\\IITK\\patient readmission project\\readmission_risk2.csv" INTO TABLE readmission_risk FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;



LOAD DATA INFILE "C:\\Users\\Hp\\Desktop\\IITK\\patient readmission project\\patients2.csv" 
REPLACE
INTO TABLE patients 
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS;


select  discharge_date, admission_date, DATEDIFF(discharge_date,admission_date) as duration_admit from patients