USE dr_rautenberg_goldschmidt;
-- Krankenkassen
CREATE TABLE IF NOT EXISTS krankenkasse (
    KKNr INT AUTO_INCREMENT PRIMARY KEY,
    Bezeichnung VARCHAR(50),
    Ansprechpartner VARCHAR(40)
);

-- Fachärzte
CREATE TABLE IF NOT EXISTS facharzt (
    FaNr INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50)
);

-- Therapien
CREATE TABLE IF NOT EXISTS therapie (
    TNr INT AUTO_INCREMENT PRIMARY KEY,
    Bezeichnung VARCHAR(50)
);

-- Zwischentabelle Diagnose <-> Therapie (n:m)
CREATE TABLE IF NOT EXISTS diagnose_therapie (
    DNr INT,
    TNr INT,
    PRIMARY KEY (DNr, TNr),
    FOREIGN KEY (DNr) REFERENCES diagnose(DNr),
    FOREIGN KEY (TNr) REFERENCES therapie(TNr)
);
INSERT INTO krankenkasse (Bezeichnung, Ansprechpartner) VALUES
('AOK', 'Müller'),
('TK', 'Schmidt'),
('Barmer', 'Klein');

INSERT INTO facharzt (Name) VALUES
('Dr. Rautenberg'),
('Dr. Meyer'),
('Dr. Schulz');

INSERT INTO therapie (Bezeichnung) VALUES
('Fango-Packung'),
('Verbandskasten'),
('Medikamentengabe');
-- Patient -> Krankenkasse
ALTER TABLE patienten
ADD CONSTRAINT fk_patient_krankenkasse
FOREIGN KEY (KKNr) REFERENCES krankenkasse(KKNr);

-- Diagnose -> Facharzt
ALTER TABLE diagnose
ADD CONSTRAINT fk_diagnose_facharzt
FOREIGN KEY (FaNr) REFERENCES facharzt(FaNr);
-- Falls noch kein Facharzt gesetzt ist
UPDATE diagnose
SET FaNr = 1
WHERE FaNr IS NULL;

-- Diagnose-Therapie-Verknüpfungen
INSERT INTO diagnose_therapie (DNr, TNr) VALUES
(1, 1),
(1, 2),
(2, 3);
SELECT
    LEFT(Postleitzahl, 2) AS PLZ_Bezirk,
    COUNT(*) AS Anzahl_Patienten
FROM patienten
WHERE LEFT(Postleitzahl, 2) IN ('20', '21')
GROUP BY LEFT(Postleitzahl, 2)
ORDER BY Anzahl_Patienten DESC;
SELECT
    p.Vorname,
    p.Nachname,
    p.Telefon,
    p.MobilNr,
    k.Bezeichnung AS Krankheit,
    d.Feststellungsdatum
FROM patienten p
JOIN diagnose d ON p.PNr = d.PNr
JOIN krankheiten k ON d.KNr = k.KNr
WHERE k.Bezeichnung = 'Masern'
  AND d.Feststellungsdatum >= '2015-01-01';