CREATE TABLE Patients (
    patient_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    date_of_birth DATE NOT NULL,
    gender TEXT CHECK(gender IN ('Male', 'Female', 'Other')) NOT NULL,
    contact_information TEXT
);

CREATE TABLE GeneticTests (
    test_id INTEGER PRIMARY KEY,
    test_name TEXT NOT NULL,
    description TEXT,
    created_at DATE DEFAULT CURRENT_DATE,
    updated_at DATE
);

CREATE TABLE ResearchStudies (
    study_id INTEGER PRIMARY KEY,
    study_name TEXT NOT NULL,
    lead_researcher TEXT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    description TEXT
);

CREATE TABLE Samples (
    sample_id INTEGER PRIMARY KEY,
    patient_id INTEGER,
    sample_type TEXT CHECK(sample_type IN ('Blood', 'Saliva', 'Tissue', 'Urine')) NOT NULL,
    collected_at DATE NOT NULL,
    stored_location TEXT NOT NULL,
    FOREIGN KEY (patient_id) REFERENCES Patients(patient_id)
);

CREATE TABLE TestResults (
    result_id INTEGER PRIMARY KEY,
    sample_id INTEGER,
    test_id INTEGER,
    result_date DATE NOT NULL,
    result_data TEXT NOT NULL,
    FOREIGN KEY (sample_id) REFERENCES Samples(sample_id),
    FOREIGN KEY (test_id) REFERENCES GeneticTests(test_id)
);

CREATE TABLE PatientStudies (
    patient_study_id INTEGER PRIMARY KEY,
    patient_id INTEGER,
    study_id INTEGER,
    enrollment_date DATE NOT NULL,
    FOREIGN KEY (patient_id) REFERENCES Patients(patient_id),
    FOREIGN KEY (study_id) REFERENCES ResearchStudies(study_id)
);

CREATE TABLE Researchers (
    researcher_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    specialization TEXT,
    contact_information TEXT
);

CREATE TABLE ResearcherStudies (
    researcher_study_id INTEGER PRIMARY KEY,
    researcher_id INTEGER,
    study_id INTEGER,
    role TEXT NOT NULL,
    FOREIGN KEY (researcher_id) REFERENCES Researchers(researcher_id),
    FOREIGN KEY (study_id) REFERENCES ResearchStudies(study_id)
);

CREATE TABLE LabEquipment (
    equipment_id INTEGER PRIMARY KEY,
    equipment_name TEXT NOT NULL,
    purchase_date DATE NOT NULL,
    last_maintenance_date DATE,
    status TEXT CHECK(status IN ('Operational', 'Under Maintenance', 'Out of Service')) NOT NULL
);

CREATE TABLE EquipmentSamples (
    equipment_sample_id INTEGER PRIMARY KEY,
    equipment_id INTEGER,
    sample_id INTEGER,
    usage_date DATE NOT NULL,
    FOREIGN KEY (equipment_id) REFERENCES LabEquipment(equipment_id),
    FOREIGN KEY (sample_id) REFERENCES Samples(sample_id)
);

CREATE TABLE GeneticMarkers (
    marker_id INTEGER PRIMARY KEY,
    marker_name TEXT NOT NULL,
    description TEXT,
    associated_disease TEXT
);

CREATE TABLE ResultMarkers (
    result_marker_id INTEGER PRIMARY KEY,
    result_id INTEGER,
    marker_id INTEGER,
    marker_value TEXT NOT NULL,
    FOREIGN KEY (result_id) REFERENCES TestResults(result_id),
    FOREIGN KEY (marker_id) REFERENCES GeneticMarkers(marker_id)
);

CREATE TABLE Grants (
    grant_id INTEGER PRIMARY KEY,
    grant_name TEXT NOT NULL,
    funding_agency TEXT NOT NULL,
    amount NUMERIC NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    research_study_id INTEGER,
    FOREIGN KEY (research_study_id) REFERENCES ResearchStudies(study_id)
);

CREATE TABLE ConsentForms (
    consent_id INTEGER PRIMARY KEY,
    patient_id INTEGER,
    form_date DATE NOT NULL,
    form_data TEXT NOT NULL,
    FOREIGN KEY (patient_id) REFERENCES Patients(patient_id)
);

INSERT INTO Patients (first_name, last_name, date_of_birth, gender, contact_information) VALUES
('John', 'Doe', '1980-05-15', 'Male', 'john.doe@example.com'),
('Jane', 'Smith', '1990-08-22', 'Female', 'jane.smith@example.com'),
('Alice', 'Johnson', '1975-03-10', 'Female', 'alice.johnson@example.com'),
('Bob', 'Brown', '1985-12-02', 'Male', 'bob.brown@example.com'),
('Charlie', 'Davis', '2000-07-19', 'Other', 'charlie.davis@example.com'),
('David', 'Williams', '1978-11-30', 'Male', 'david.williams@example.com'),
('Emma', 'Jones', '1989-09-21', 'Female', 'emma.jones@example.com'),
('Frank', 'Garcia', '1982-01-25', 'Male', 'frank.garcia@example.com'),
('Grace', 'Martinez', '1995-04-14', 'Female', 'grace.martinez@example.com'),
('Hannah', 'Rodriguez', '1983-02-18', 'Female', 'hannah.rodriguez@example.com'),
('Ian', 'Hernandez', '1977-07-23', 'Male', 'ian.hernandez@example.com'),
('Jack', 'Lopez', '1992-03-28', 'Male', 'jack.lopez@example.com'),
('Kathy', 'Gonzalez', '1986-06-15', 'Female', 'kathy.gonzalez@example.com'),
('Leo', 'Wilson', '1998-11-09', 'Male', 'leo.wilson@example.com'),
('Maria', 'Anderson', '1981-12-22', 'Female', 'maria.anderson@example.com'),
('Nathan', 'Clark', '1982-05-15', 'Male', 'nathan.clark@example.com');

INSERT INTO GeneticTests (test_name, description) VALUES
('BRCA1', 'Test for BRCA1 gene mutations'),
('BRCA2', 'Test for BRCA2 gene mutations'),
('APOE', 'Test for APOE gene variations related to Alzheimer''s'),
('CFTR', 'Test for cystic fibrosis mutations in the CFTR gene'),
('HLA-B27', 'Test for HLA-B27 gene associated with autoimmune diseases'),
('FMR1', 'Test for Fragile X Syndrome'),
('TP53', 'Test for mutations in the TP53 gene related to cancer'),
('MTHFR', 'Test for MTHFR gene mutations'),
('COL1A1', 'Test for Osteogenesis Imperfecta'),
('EGFR', 'Test for EGFR gene mutations in lung cancer');

INSERT INTO ResearchStudies (study_name, lead_researcher, start_date, end_date, description) VALUES
('Cancer Genomics', 'Dr. Emily White', '2021-01-01', NULL, 'Study on the genomics of various cancers'),
('Alzheimer''s Genetics', 'Dr. James Green', '2020-05-01', '2023-05-01', 'Study on genetic markers for Alzheimer''s disease'),
('Cystic Fibrosis Research', 'Dr. Sarah Black', '2022-06-15', NULL, 'Research on the genetic basis of cystic fibrosis'),
('Autoimmune Diseases', 'Dr. John Brown', '2021-09-01', NULL, 'Genetic studies on autoimmune diseases'),
('Fragile X Syndrome', 'Dr. Linda Grey', '2019-04-01', '2022-04-01', 'Study on genetic markers for Fragile X Syndrome'),
('Cancer Immunotherapy', 'Dr. Robert Black', '2022-03-01', NULL, 'Research on genetic markers for immunotherapy responses in cancer'),
('Lung Cancer Genomics', 'Dr. Alice White', '2020-11-15', NULL, 'Study on the genomics of lung cancer'),
('Osteogenesis Imperfecta', 'Dr. Michael Blue', '2021-12-01', '2024-12-01', 'Research on the genetic basis of Osteogenesis Imperfecta'),
('MTHFR Mutations', 'Dr. Steven Brown', '2021-07-01', NULL, 'Study on the effects of MTHFR gene mutations'),
('Diabetes Genetics', 'Dr. Rachel White', '2021-05-01', '2023-05-01', 'Study on genetic markers for diabetes');

INSERT INTO Samples (patient_id, sample_type, collected_at, stored_location) VALUES
(1, 'Blood', '2023-04-10', 'Freezer A1'),
(2, 'Saliva', '2023-04-12', 'Freezer B2'),
(1, 'Tissue', '2023-04-15', 'Freezer C3'),
(3, 'Urine', '2023-04-18', 'Freezer A1'),
(4, 'Blood', '2023-04-20', 'Freezer B2'),
(5, 'Saliva', '2023-04-22', 'Freezer A1'),
(6, 'Tissue', '2023-04-24', 'Freezer B2'),
(7, 'Urine', '2023-04-26', 'Freezer C3'),
(8, 'Blood', '2023-04-28', 'Freezer A1'),
(9, 'Saliva', '2023-04-30', 'Freezer B2'),
(10, 'Tissue', '2023-05-02', 'Freezer C3'),
(11, 'Urine', '2023-05-04', 'Freezer A1'),
(12, 'Blood', '2023-05-06', 'Freezer B2'),
(13, 'Saliva', '2023-05-08', 'Freezer C3'),
(14, 'Tissue', '2023-05-10', 'Freezer A1'),
(15, 'Urine', '2023-05-12', 'Freezer B2');

INSERT INTO TestResults (sample_id, test_id, result_date, result_data) VALUES
(1, 1, '2023-04-20', 'Negative'),
(2, 2, '2023-04-22', 'Positive'),
(3, 3, '2023-04-25', 'Homozygous'),
(4, 4, '2023-04-27', 'Heterozygous'),
(5, 1, '2023-04-30', 'Negative'),
(6, 2, '2023-05-02', 'Positive'),
(7, 3, '2023-05-04', 'Homozygous'),
(8, 4, '2023-05-06', 'Heterozygous'),
(9, 1, '2023-05-08', 'Negative'),
(10, 2, '2023-05-10', 'Positive'),
(11, 3, '2023-05-12', 'Homozygous'),
(12, 4, '2023-05-14', 'Heterozygous'),
(13, 1, '2023-05-16', 'Negative'),
(14, 2, '2023-05-18', 'Positive'),
(15, 3, '2023-05-20', 'Homozygous');

INSERT INTO PatientStudies (patient_id, study_id, enrollment_date) VALUES
(1, 1, '2021-02-01'),
(2, 1, '2021-03-01'),
(3, 2, '2020-06-15'),
(1, 3, '2022-07-10'),
(4, 3, '2022-08-20'),
(5, 4, '2021-09-10'),
(6, 5, '2019-05-01'),
(7, 6, '2022-03-15'),
(8, 7, '2020-12-01'),
(9, 8, '2021-12-20'),
(10, 9, '2021-07-15'),
(11, 10, '2021-05-10'),
(12, 1, '2021-06-05'),
(13, 2, '2020-07-01'),
(14, 3, '2022-08-05'),
(15, 4, '2021-09-25'),
(16, 1, '2021-09-25'),
(16, 2, '2021-09-25'),
(16, 3, '2021-09-25'),
(16, 4, '2021-09-25'),
(16, 5, '2021-09-25'),
(16, 6, '2021-09-25'),
(16, 7, '2021-09-25'),
(16, 8, '2021-09-25'),
(16, 9, '2021-09-25'),
(16, 10, '2021-09-25');
  

INSERT INTO Researchers (first_name, last_name, specialization, contact_information) VALUES
('Emily', 'White', 'Oncology', 'emily.white@example.com'),
('James', 'Green', 'Neurology', 'james.green@example.com'),
('Sarah', 'Black', 'Pulmonology', 'sarah.black@example.com'),
('Michael', 'Brown', 'Genetics', 'michael.brown@example.com'),
('Linda', 'Grey', 'Pediatrics', 'linda.grey@example.com'),
('John', 'Brown', 'Immunology', 'john.brown@example.com'),
('Robert', 'Black', 'Hematology', 'robert.black@example.com'),
('Alice', 'White', 'Pathology', 'alice.white@example.com'),
('Steven', 'Brown', 'Endocrinology', 'steven.brown@example.com'),
('Rachel', 'White', 'Cardiology', 'rachel.white@example.com');

INSERT INTO ResearcherStudies (researcher_id, study_id, role) VALUES
(1, 1, 'Lead Researcher'),
(2, 2, 'Lead Researcher'),
(3, 3, 'Lead Researcher'),
(4, 1, 'Researcher'),
(5, 4, 'Researcher'),
(6, 5, 'Lead Researcher'),
(7, 6, 'Lead Researcher'),
(8, 7, 'Lead Researcher'),
(9, 8, 'Lead Researcher'),
(10, 9, 'Lead Researcher'),
(1, 2, 'Researcher'),
(2, 3, 'Researcher'),
(3, 4, 'Researcher'),
(4, 5, 'Researcher'),
(5, 6, 'Researcher');

INSERT INTO LabEquipment (equipment_name, purchase_date, last_maintenance_date, status) VALUES
('PCR Machine', '2020-01-15', '2023-01-15', 'Operational'),
('Sequencer', '2019-07-10', '2022-07-10', 'Operational'),
('Centrifuge', '2018-03-05', '2023-03-05', 'Under Maintenance'),
('Microscope', '2021-06-20', '2023-06-20', 'Operational'),
('Autoclave', '2019-10-12', '2023-01-12', 'Operational'),
('Incubator', '2020-04-22', '2023-04-22', 'Operational'),
('Spectrophotometer', '2018-11-18', '2022-11-18', 'Operational'),
('Centrifuge', '2021-02-08', '2023-02-08', 'Operational'),
('Freezer', '2020-09-30', '2023-09-30', 'Operational'),
('Flow Cytometer', '2021-12-11', '2023-12-11', 'Operational');

INSERT INTO EquipmentSamples (equipment_id, sample_id, usage_date) VALUES
(1, 1, '2023-04-11'),
(2, 2, '2023-04-13'),
(3, 3, '2023-04-16'),
(4, 4, '2023-04-19'),
(5, 5, '2023-04-21'),
(6, 6, '2023-04-23'),
(7, 7, '2023-04-25'),
(8, 8, '2023-04-27'),
(9, 9, '2023-04-29'),
(10, 10, '2023-05-01'),
(1, 11, '2023-05-03'),
(2, 12, '2023-05-05'),
(3, 13, '2023-05-07'),
(4, 14, '2023-05-09'),
(5, 15, '2023-05-11');

INSERT INTO GeneticMarkers (marker_name, description, associated_disease) VALUES
('BRCA1', 'BRCA1 gene mutation', 'Breast Cancer'),
('BRCA2', 'BRCA2 gene mutation', 'Breast Cancer'),
('APOE', 'APOE gene variation', 'Alzheimer''s Disease'),
('CFTR', 'CFTR gene mutation', 'Cystic Fibrosis'),
('HLA-B27', 'HLA-B27 gene associated w autoimmune diseases', 'Autoimmune Diseases'),
('FMR1', 'FMR1 gene associated w Fragile X Syndrome', 'Fragile X Syndrome'),
('TP53', 'TP53 gene mutation associated w various cancers', 'Cancer'),
('MTHFR', 'MTHFR gene mutation', 'Cardiovascular Diseases'),
('COL1A1', 'COL1A1 gene mutation associated w Osteogenesis Imperfecta', 'Osteogenesis Imperfecta'),
('EGFR', 'EGFR gene mutation associated w lung cancer', 'Lung Cancer');

INSERT INTO ResultMarkers (result_id, marker_id, marker_value) VALUES
(1, 1, 'Negative'),
(2, 2, 'Positive'),
(3, 3, 'Homozygous'),
(4, 4, 'Heterozygous'),
(5, 5, 'Positive'),
(6, 6, 'Negative'),
(7, 7, 'Positive'),
(8, 8, 'Homozygous'),
(9, 9, 'Negative'),
(10, 10, 'Positive'),
(11, 1, 'Negative'),
(12, 2, 'Positive'),
(13, 3, 'Homozygous'),
(14, 4, 'Heterozygous'),
(15, 5, 'Positive');

INSERT INTO Grants (grant_name, funding_agency, amount, start_date, end_date, research_study_id) VALUES
('Cancer Research Grant', 'National Cancer Institute', 500000, '2021-01-01', '2024-12-31', 1),
('Alzheimer''s Research Grant', 'National Institute on Aging', 300000, '2020-05-01', '2023-05-01', 2),
('Cystic Fibrosis Grant', 'Cystic Fibrosis Foundation', 200000, '2022-06-15', '2025-06-14', 3),
('Autoimmune Diseases Grant', 'National Institutes of Health', 150000, '2021-09-01', '2024-08-31', 4),
('Fragile X Syndrome Grant', 'National Institute of Child Health and Human Development', 250000, '2019-04-01', '2022-04-01', 5),
('Cancer Immunotherapy Grant', 'American Cancer Society', 400000, '2022-03-01', '2025-02-28', 6),
('Lung Cancer Genomics Grant', 'National Cancer Institute', 350000, '2020-11-15', '2024-10-31', 7),
('Osteogenesis Imperfecta Grant', 'National Institute of Arthritis and Musculoskeletal and Skin Diseases', 300000, '2021-12-01', '2024-12-01', 8),
('MTHFR Mutations Grant', 'National Heart, Lung, and Blood Institute', 180000, '2021-07-01', '2024-06-30', 9),
('Diabetes Genetics Grant', 'National Institute of Diabetes and Digestive and Kidney Diseases', 220000, '2021-05-01', '2023-05-01', 10);

INSERT INTO ConsentForms (patient_id, form_date, form_data) VALUES
(1, '2021-01-15', 'Consent form for Cancer Genomics study'),
(2, '2021-02-20', 'Consent form for Cancer Genomics study'),
(3, '2020-06-10', 'Consent form for Alzheimer''s Genetics study'),
(1, '2022-07-05', 'Consent form for Cystic Fibrosis Research'),
(4, '2022-08-15', 'Consent form for Cystic Fibrosis Research'),
(5, '2021-09-05', 'Consent form for Autoimmune Diseases study'),
(6, '2019-05-10', 'Consent form for Fragile X Syndrome study'),
(7, '2022-03-10', 'Consent form for Cancer Immunotherapy study'),
(8, '2020-12-05', 'Consent form for Lung Cancer Genomics study'),
(9, '2021-12-25', 'Consent form for Osteogenesis Imperfecta study'),
(10, '2021-07-20', 'Consent form for MTHFR Mutations study'),
(11, '2021-05-15', 'Consent form for Diabetes Genetics study'),
(12, '2021-06-10', 'Consent form for Cancer Genomics study'),
(13, '2020-07-05', 'Consent form for Alzheimer''s Genetics study'),
(14, '2022-08-10', 'Consent form for Cystic Fibrosis Research'),
(15, '2021-09-30', 'Consent form for Autoimmune Diseases study');

-- WITH StudyParticipation AS (
--     SELECT 
--         patient_id,
--         COUNT(DISTINCT study_id) AS study_count
--     FROM 
--         PatientStudies
--     GROUP BY 
--         patient_id
-- )
-- SELECT 
--     p.first_name,
--     p.last_name
-- FROM 
--     StudyParticipation sp
-- JOIN 
--     Patients p ON sp.patient_id = p.patient_id
-- WHERE 
--     sp.study_count = (SELECT COUNT(*) FROM ResearchStudies);

-- WITH ResearcherStudyCount AS (
--     SELECT 
--         rs.researcher_id,
--         COUNT(DISTINCT rs.study_id) AS study_count
--     FROM 
--         ResearcherStudies rs
--     GROUP BY 
--         rs.researcher_id
-- )
-- SELECT 
--     r.first_name,
--     r.last_name,
--     rsc.study_count
-- FROM 
--     ResearcherStudyCount rsc
-- JOIN 
--     Researchers r ON rsc.researcher_id = r.researcher_id
-- WHERE 
--     rsc.study_count > 1 ORDER BY r.first_name ASC;

-- WITH SampleTestTime AS (
--     SELECT 
--         s.sample_type,
--         JULIANDAY(tr.result_date) - JULIANDAY(s.collected_at) AS days_between
--     FROM 
--         Samples s
--     JOIN 
--         TestResults tr ON s.sample_id = tr.sample_id
-- )
-- SELECT 
--     stt.sample_type,
--     AVG(stt.days_between) AS avg_days_between
-- FROM 
--     SampleTestTime stt
-- GROUP BY 
--     stt.sample_type
-- ORDER BY 
--     avg_days_between ASC
-- LIMIT 1;

-- WITH ActiveStudies AS (
--     SELECT 
--         study_id,
--         study_name
--     FROM 
--         ResearchStudies
--     WHERE 
--         end_date IS NULL OR end_date >= CURRENT_DATE
-- )
-- SELECT 
--     r.researcher_id,
--     r.first_name,
--     r.last_name,
--     a.study_name
-- FROM 
--     Researchers r
-- JOIN 
--     ResearcherStudies rs ON r.researcher_id = rs.researcher_id
-- JOIN 
--     ActiveStudies a ON rs.study_id = a.study_id
-- ORDER BY r.researcher_id ASC;

-- WITH EquipmentUsage AS (
--     SELECT equipment_id, COUNT(sample_id) AS usage_count
--     FROM EquipmentSamples
--     WHERE usage_date BETWEEN '2023-01-01' AND '2023-12-31'
--     GROUP BY equipment_id
-- )
-- SELECT 
--     le.equipment_id,
--     le.equipment_name,
--     eu.usage_count
-- FROM LabEquipment le
-- JOIN EquipmentUsage eu ON le.equipment_id = eu.equipment_id;

-- WITH OngoingStudies AS (
--     SELECT 
--         study_id,
--         study_name
--     FROM 
--         ResearchStudies
--     WHERE 
--         end_date IS NULL OR end_date >= CURRENT_DATE
-- ),
-- GrantAmounts AS (
--     SELECT 
--         research_study_id,
--         SUM(amount) AS total_grant_amount
--     FROM 
--         Grants
--     GROUP BY 
--         research_study_id
-- )
-- SELECT 
--     os.study_id,
--     os.study_name,
--     ga.total_grant_amount
-- FROM 
--     OngoingStudies os
-- JOIN 
--     GrantAmounts ga ON os.study_id = ga.research_study_id;

-- WITH PositiveResults AS (
--     SELECT 
--         s.patient_id,
--         COUNT(tr.result_id) AS positive_count
--     FROM 
--         TestResults tr
--     JOIN 
--         Samples s ON tr.sample_id = s.sample_id
--     WHERE 
--         tr.result_data = 'Positive'
--     GROUP BY 
--         s.patient_id
-- )
-- SELECT 
--     p.patient_id,
--     p.first_name,
--     p.last_name,
--     pr.positive_count
-- FROM 
--     Patients p
-- JOIN 
--     PositiveResults pr ON p.patient_id = pr.patient_id
-- ORDER BY pr.positive_count DESC;

-- WITH SampleTypeCount AS (
--     SELECT 
--         patient_id,
--         COUNT(DISTINCT sample_type) AS distinct_sample_types
--     FROM 
--         Samples
--     GROUP BY 
--         patient_id
-- ),
-- MaxSampleTypeCount AS (
--     SELECT 
--         MAX(distinct_sample_types) AS max_types
--     FROM 
--         SampleTypeCount
-- )
-- SELECT 
--     p.patient_id,
--     p.first_name,
--     p.last_name,
--     stc.distinct_sample_types
-- FROM 
--     Patients p
-- JOIN 
--     SampleTypeCount stc ON p.patient_id = stc.patient_id
-- JOIN 
--     MaxSampleTypeCount mstc ON stc.distinct_sample_types = mstc.max_types;

-- WITH StudyStatus AS (
--     SELECT 
--         study_id,
--         CASE 
--             WHEN end_date IS NULL OR end_date >= CURRENT_DATE THEN 'Ongoing'
--             ELSE 'Completed'
--         END AS status
--     FROM 
--         ResearchStudies
-- ),
-- GrantAmounts AS (
--     SELECT 
--         research_study_id,
--         amount
--     FROM 
--         Grants
-- ),
-- StudyGrantAmount AS (
--     SELECT 
--         ss.status,
--         ga.amount
--     FROM 
--         StudyStatus ss
--     JOIN 
--         GrantAmounts ga ON ss.study_id = ga.research_study_id
-- )
-- SELECT 
--     status,
--     AVG(amount) AS avg_grant_amount
-- FROM 
--     StudyGrantAmount
-- GROUP BY 
--     status;

-- WITH EquipmentUsageBySample AS (
--     SELECT 
--         es.equipment_id,
--         s.patient_id,
--         COUNT(es.sample_id) AS usage_count
--     FROM 
--         EquipmentSamples es
--     JOIN 
--         Samples s ON es.sample_id = s.sample_id
--     GROUP BY 
--         es.equipment_id, s.patient_id
-- ),
-- EquipmentUsageByGender AS (
--     SELECT 
--         eub.equipment_id,
--         p.gender,
--         SUM(eub.usage_count) AS total_usage_count
--     FROM 
--         EquipmentUsageBySample eub
--     JOIN 
--         Patients p ON eub.patient_id = p.patient_id
--     GROUP BY 
--         eub.equipment_id, p.gender
-- )
-- SELECT 
--     le.equipment_id,
--     le.equipment_name,
--     eug.gender,
--     eug.total_usage_count
-- FROM 
--     LabEquipment le
-- JOIN 
--     EquipmentUsageByGender eug ON le.equipment_id = eug.equipment_id;

WITH PositiveTestResults AS (
    SELECT 
        tr.test_id,
        COUNT(tr.result_id) AS positive_count
    FROM 
        TestResults tr
    WHERE 
        tr.result_data = 'Positive'
    GROUP BY 
        tr.test_id
),
StudyPositiveResults AS (
    SELECT 
        rs.study_id,
        rs.study_name,
        SUM(ptr.positive_count) AS total_positive_count
    FROM 
        ResearchStudies rs
    JOIN 
        PatientStudies ps ON rs.study_id = ps.study_id
    JOIN 
        Samples s ON ps.patient_id = s.patient_id
    JOIN 
        TestResults tr ON s.sample_id = tr.sample_id
    JOIN 
        PositiveTestResults ptr ON tr.test_id = ptr.test_id
    GROUP BY 
        rs.study_id, rs.study_name
)
SELECT 
    spr.study_name,
    spr.total_positive_count
FROM 
    StudyPositiveResults spr
ORDER BY 
    spr.total_positive_count DESC;