-- Datenbank: softwarebestellung
-- Testdaten entfernt

SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

-- --------------------------------------------------------
-- Datenbank anlegen
-- --------------------------------------------------------

CREATE DATABASE IF NOT EXISTS `softwarebestellung`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `softwarebestellung`;

-- --------------------------------------------------------
-- Benutzer anlegen
-- (Passwort bitte vor dem Einspielen setzen)
-- --------------------------------------------------------

CREATE USER IF NOT EXISTS 'sql-softwarebestellung'@'localhost' IDENTIFIED BY 'IXuoDh04_~2crfmw';
GRANT ALL PRIVILEGES ON `softwarebestellung`.* TO 'sql-softwarebestellung'@'localhost';
FLUSH PRIVILEGES;

-- --------------------------------------------------------
-- Tabelle: bestellungen
-- --------------------------------------------------------

CREATE TABLE `bestellungen` (
  `id`                        INT            NOT NULL AUTO_INCREMENT,
  `schul_name`                VARCHAR(255)   NOT NULL,
  `status`                    VARCHAR(50)    DEFAULT 'warte_auf_genehmigung',
  `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)    DEFAULT NULL,
  `software_quelle`           VARCHAR(50)    NOT NULL,
  `download_pfad`             TEXT           DEFAULT NULL,
  `lizenz_typ`                VARCHAR(50)    NOT NULL,
  `lizenzschluessel`          TEXT           DEFAULT NULL,
  `lizenzserver_anmerkungen`  TEXT           DEFAULT NULL,
  `erstellt_am`               DATETIME       DEFAULT CURRENT_TIMESTAMP,
  `bearbeitet_am`             DATETIME       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `bearbeitet_von`            VARCHAR(100)   DEFAULT NULL,
  `notizen`                   TEXT           DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `bestellungen_status_check`
    CHECK (`status` IN ('warte_auf_genehmigung','genehmigt','gesendet','erledigt',
                        'abgelehnt','neu','in_pruefung','korrektur_notwendig','versendet')),
  CONSTRAINT `bestellungen_software_quelle_check`
    CHECK (`software_quelle` IN ('downloadFree','downloadCustomer','physicalMedia')),
  CONSTRAINT `bestellungen_lizenz_typ_check`
    CHECK (`lizenz_typ` IN ('noLicense','licenseKey','licenseHardware','licenseServer'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Tabelle: bestellungen_archiv
-- --------------------------------------------------------

CREATE TABLE `bestellungen_archiv` (
  `id`                        INT            NOT NULL,
  `schul_name`                VARCHAR(255)   NOT NULL,
  `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)    DEFAULT NULL,
  `software_quelle`           VARCHAR(50)    NOT NULL,
  `download_pfad`             TEXT           DEFAULT NULL,
  `lizenz_typ`                VARCHAR(50)    NOT NULL,
  `lizenzschluessel`          TEXT           DEFAULT NULL,
  `lizenzserver_anmerkungen`  TEXT           DEFAULT NULL,
  `erstellt_am`               DATETIME       DEFAULT NULL,
  `bearbeitet_am`             DATETIME       DEFAULT NULL,
  `bearbeitet_von`            VARCHAR(100)   DEFAULT NULL,
  `notizen`                   TEXT           DEFAULT NULL,
  `archiviert_am`             DATETIME       DEFAULT CURRENT_TIMESTAMP,
  `archiviert_von`            VARCHAR(100)   DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Tabelle: bestellungen_historie
-- --------------------------------------------------------

CREATE TABLE `bestellungen_historie` (
  `id`             INT           NOT NULL AUTO_INCREMENT,
  `bestellung_id`  INT           NOT NULL,
  `aenderung_von`  VARCHAR(100)  DEFAULT NULL,
  `aenderung_am`   DATETIME      DEFAULT CURRENT_TIMESTAMP,
  `feld`           VARCHAR(100)  DEFAULT NULL,
  `alter_wert`     TEXT          DEFAULT NULL,
  `neuer_wert`     TEXT          DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_bestellung`  (`bestellung_id`),
  KEY `idx_aenderung_am` (`aenderung_am`),
  CONSTRAINT `bestellungen_historie_bestellung_id_fkey`
    FOREIGN KEY (`bestellung_id`) REFERENCES `bestellungen` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Indizes auf bestellungen
-- --------------------------------------------------------

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

-- --------------------------------------------------------
-- AUTO_INCREMENT-Startwerte (anpassen falls Altdaten migriert werden)
-- --------------------------------------------------------

ALTER TABLE `bestellungen`          AUTO_INCREMENT = 1;
ALTER TABLE `bestellungen_historie` AUTO_INCREMENT = 1;

SET foreign_key_checks = 1;
