-- PostgreSQL-Schema für Softwarebestell-System
-- Für MySQL: Ersetze SERIAL durch AUTO_INCREMENT und passe CHECK-Constraints an

-- Bestellungen Haupttabelle
CREATE TABLE bestellungen (
    id SERIAL PRIMARY KEY,
    schul_name VARCHAR(255) NOT NULL,
    status VARCHAR(50) DEFAULT 'neu' CHECK (status IN ('neu', 'in_pruefung', 'korrektur_notwendig', 'genehmigt', 'abgelehnt', 'versendet')),

    -- Software-Daten
    software_name VARCHAR(255) NOT NULL,
    version VARCHAR(100) NOT NULL,
    hersteller VARCHAR(255) NOT NULL,
    hersteller_website VARCHAR(500) NOT NULL,
    isbn VARCHAR(50),

    -- Bezug
    software_quelle VARCHAR(50) NOT NULL CHECK (software_quelle IN ('downloadFree', 'downloadCustomer', 'physicalMedia')),
    download_pfad TEXT,

    -- Lizenzierung
    lizenz_typ VARCHAR(50) NOT NULL CHECK (lizenz_typ IN ('noLicense', 'licenseKey', 'licenseHardware', 'licenseServer')),
    lizenzschluessel TEXT,
    lizenzserver_anmerkungen TEXT,

    -- Metadaten
    erstellt_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    bearbeitet_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    bearbeitet_von VARCHAR(100),
    notizen TEXT
);

CREATE INDEX idx_status ON bestellungen(status);
CREATE INDEX idx_schule ON bestellungen(schul_name);
CREATE INDEX idx_erstellt ON bestellungen(erstellt_am);
CREATE INDEX idx_software ON bestellungen(software_name);

-- Änderungshistorie
CREATE TABLE bestellungen_historie (
    id SERIAL PRIMARY KEY,
    bestellung_id INTEGER NOT NULL REFERENCES bestellungen(id) ON DELETE CASCADE,
    aenderung_von VARCHAR(100),
    aenderung_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    feld VARCHAR(100),
    alter_wert TEXT,
    neuer_wert TEXT
);

CREATE INDEX idx_bestellung ON bestellungen_historie(bestellung_id);
CREATE INDEX idx_aenderung_am ON bestellungen_historie(aenderung_am);
