-- ============================================================
-- Tierarztpraxis Juckstadt — Lehrbuch-Schema
-- Vereinfachte Version basierend auf dem Produktiv-Schema
-- der Praxisverwaltungssoftware (tierarztpraxis_app)
--
-- Verwendung:
--   createdb tierarztpraxis
--   psql -d tierarztpraxis -f tierarztpraxis_schema.sql
-- ============================================================

-- Aufräumen falls vorhanden
DROP TABLE IF EXISTS bestandsbewegung CASCADE;
DROP TABLE IF EXISTS laborbefund CASCADE;
DROP TABLE IF EXISTS labor_tier_mapping CASCADE;
DROP TABLE IF EXISTS einheiten_faktoren CASCADE;
DROP TABLE IF EXISTS rechnungspos CASCADE;
DROP TABLE IF EXISTS rechnung CASCADE;
DROP TABLE IF EXISTS verwendung CASCADE;
DROP TABLE IF EXISTS medikament CASCADE;
DROP TABLE IF EXISTS lieferant CASCADE;
DROP TABLE IF EXISTS behandlung CASCADE;
DROP TABLE IF EXISTS tier CASCADE;
DROP TABLE IF EXISTS personal CASCADE;
DROP TABLE IF EXISTS tierbesitzer CASCADE;

-- ============================================================
-- Stammdaten
-- ============================================================

CREATE TABLE tierbesitzer (
    kunden_nr    SERIAL PRIMARY KEY,
    anrede       VARCHAR(20) NOT NULL,
    vorname      VARCHAR(50) NOT NULL,
    nachname     VARCHAR(50) NOT NULL,
    strasse      VARCHAR(100),
    plz          VARCHAR(10),
    ort          VARCHAR(50) DEFAULT 'Juckstadt',
    telefon      VARCHAR(20),
    email        VARCHAR(100),
    erstellt_am  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tier (
    patienten_nr SERIAL PRIMARY KEY,
    kunden_nr    INTEGER NOT NULL
                 REFERENCES tierbesitzer(kunden_nr)
                 ON DELETE RESTRICT,
    name         VARCHAR(50) NOT NULL,
    tierart      VARCHAR(30) NOT NULL,
    rasse        VARCHAR(50) DEFAULT 'Mischling',
    geschlecht   VARCHAR(10) CHECK (geschlecht IN
                 ('maennlich', 'weiblich', 'unbekannt')),
    geburtsdatum DATE,
    chip_nr      VARCHAR(20),
    gewicht_kg   NUMERIC(6,2),
    allergien    TEXT,
    ist_aktiv    BOOLEAN DEFAULT TRUE,
    erstellt_am  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE personal (
    pers_nr      SERIAL PRIMARY KEY,
    vorname      VARCHAR(50) NOT NULL,
    nachname     VARCHAR(50) NOT NULL,
    rolle        VARCHAR(30) NOT NULL
                 CHECK (rolle IN ('Tieraerztin',
                 'Fachangestellte', 'Assistent')),
    telefon      VARCHAR(20),
    ist_aktiv    BOOLEAN DEFAULT TRUE
);

-- Lieferanten (Stammdaten für Medikamentenbezug)
CREATE TABLE lieferant (
    lieferant_nr SERIAL PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    telefon      VARCHAR(30),
    email        VARCHAR(100),
    lieferzeit_tage INTEGER DEFAULT 3
);

CREATE TABLE medikament (
    med_nr       SERIAL PRIMARY KEY,
    pzn          VARCHAR(8),
    bezeichnung  VARCHAR(100) NOT NULL,
    wirkstoff    VARCHAR(100),
    einheit      VARCHAR(20) DEFAULT 'Stueck',
    bestand      INTEGER DEFAULT 0,
    mindestbestand INTEGER DEFAULT 5,
    reorder_punkt  INTEGER,
    lieferant_nr INTEGER REFERENCES lieferant(lieferant_nr),
    preis_netto  NUMERIC(8,2),
    mwst_satz    NUMERIC(4,2) DEFAULT 19.00
);

-- ============================================================
-- Bewegungsdaten
-- ============================================================

CREATE TABLE behandlung (
    beh_nr       SERIAL PRIMARY KEY,
    datum        DATE NOT NULL DEFAULT CURRENT_DATE,
    patienten_nr INTEGER NOT NULL
                 REFERENCES tier(patienten_nr),
    pers_nr      INTEGER NOT NULL
                 REFERENCES personal(pers_nr),
    diagnose     TEXT,
    notizen      TEXT,
    erstellt_am  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE verwendung (
    beh_nr       INTEGER REFERENCES behandlung(beh_nr),
    med_nr       INTEGER REFERENCES medikament(med_nr),
    dosis        VARCHAR(50),
    menge        INTEGER DEFAULT 1,
    PRIMARY KEY (beh_nr, med_nr)
);

CREATE TABLE rechnung (
    rech_nr      SERIAL PRIMARY KEY,
    datum        DATE NOT NULL DEFAULT CURRENT_DATE,
    kunden_nr    INTEGER NOT NULL
                 REFERENCES tierbesitzer(kunden_nr),
    beh_nr       INTEGER REFERENCES behandlung(beh_nr),
    status       VARCHAR(20) DEFAULT 'ENTWURF'
                 CHECK (status IN ('ENTWURF', 'OFFEN',
                 'BEZAHLT', 'STORNIERT')),
    gesamtbetrag NUMERIC(10,2),
    erstellt_am  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE rechnungspos (
    rech_nr      INTEGER REFERENCES rechnung(rech_nr)
                 ON DELETE CASCADE,
    pos_nr       INTEGER,
    bezeichnung  VARCHAR(200) NOT NULL,
    menge        INTEGER DEFAULT 1,
    einzelpreis  NUMERIC(8,2) NOT NULL,
    mwst_satz    NUMERIC(4,2) DEFAULT 19.00,
    PRIMARY KEY (rech_nr, pos_nr)
);

-- ============================================================
-- Stammdaten-/Lagermanagement
-- ============================================================

-- Bestandsbewegungen: Zugänge, Abgänge, Inventurdifferenzen
CREATE TABLE bestandsbewegung (
    bew_nr       SERIAL PRIMARY KEY,
    med_nr       INTEGER NOT NULL
                 REFERENCES medikament(med_nr),
    pers_nr      INTEGER REFERENCES personal(pers_nr),
    datum        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    typ          VARCHAR(20) NOT NULL
                 CHECK (typ IN ('ZUGANG', 'ABGANG', 'INVENTUR')),
    menge        INTEGER NOT NULL,
    bemerkung    TEXT
);

-- ============================================================
-- Laborintegration
-- ============================================================

-- Mapping externe Labor-ID → interne Patientennummer
CREATE TABLE labor_tier_mapping (
    labor_patient_id VARCHAR(30) PRIMARY KEY,
    patienten_nr     INTEGER NOT NULL
                     REFERENCES tier(patienten_nr),
    erstellt_am      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Einheitenumrechnung für Laborbefunde
CREATE TABLE einheiten_faktoren (
    quelleinheit  VARCHAR(20),
    zieleinheit   VARCHAR(20),
    faktor        NUMERIC(12,6) NOT NULL,
    PRIMARY KEY (quelleinheit, zieleinheit)
);

-- Importierte Labor-Befunde (Zieltabelle des ETL-Prozesses)
CREATE TABLE laborbefund (
    befund_nr    SERIAL PRIMARY KEY,
    patienten_nr INTEGER NOT NULL
                 REFERENCES tier(patienten_nr),
    datum        DATE NOT NULL,
    testtyp      VARCHAR(50) NOT NULL,
    ergebnis     NUMERIC(12,4),
    einheit      VARCHAR(20),
    referenz_min NUMERIC(12,4),
    referenz_max NUMERIC(12,4),
    import_am    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- Indizes
-- ============================================================

CREATE INDEX idx_tier_kunden_nr ON tier(kunden_nr);
CREATE INDEX idx_behandlung_datum ON behandlung(datum);
CREATE INDEX idx_behandlung_patienten ON behandlung(patienten_nr);
CREATE INDEX idx_rechnung_kunden ON rechnung(kunden_nr);
CREATE INDEX idx_rechnung_status ON rechnung(status);
CREATE INDEX idx_bestandsbewegung_med ON bestandsbewegung(med_nr, datum);
CREATE INDEX idx_laborbefund_patient ON laborbefund(patienten_nr, datum);