178 lines
7.5 KiB
MySQL
178 lines
7.5 KiB
MySQL
|
-- Pfandsystem Demo - Multi-Tenant Schema
|
||
|
|
-- ZUGFeRD-Konvention: utf8mb4, InnoDB
|
||
|
|
-- Tenant-Modell: jede Daten-Tabelle hat tenant_id (FK auf tenants.id)
|
||
|
|
|
||
|
|
SET NAMES utf8mb4;
|
||
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
|
|
||
|
|
-- =========================================================
|
||
|
|
-- TENANTS (Mandanten / Demo-Accounts)
|
||
|
|
-- =========================================================
|
||
|
|
CREATE TABLE IF NOT EXISTS tenants (
|
||
|
|
id CHAR(36) NOT NULL DEFAULT (UUID()),
|
||
|
|
name VARCHAR(255) NOT NULL,
|
||
|
|
slug VARCHAR(64) NOT NULL UNIQUE,
|
||
|
|
kontakt_email VARCHAR(255) NULL,
|
||
|
|
firma VARCHAR(255) NULL,
|
||
|
|
typ ENUM('demo','live') NOT NULL DEFAULT 'demo',
|
||
|
|
status ENUM('aktiv','abgelaufen','gesperrt') NOT NULL DEFAULT 'aktiv',
|
||
|
|
demo_expires_at TIMESTAMP NULL,
|
||
|
|
max_users INT NOT NULL DEFAULT 5,
|
||
|
|
erstellt_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
aktualisiert_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (id),
|
||
|
|
INDEX idx_slug (slug),
|
||
|
|
INDEX idx_status (status),
|
||
|
|
INDEX idx_demo_expires (demo_expires_at)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- =========================================================
|
||
|
|
-- MITARBEITER (Users) - tenant-scoped, plus SuperAdmins
|
||
|
|
-- =========================================================
|
||
|
|
CREATE TABLE IF NOT EXISTS mitarbeiter (
|
||
|
|
id CHAR(36) NOT NULL DEFAULT (UUID()),
|
||
|
|
tenant_id CHAR(36) NULL, -- NULL = SuperAdmin (cross-tenant)
|
||
|
|
email VARCHAR(255) NOT NULL,
|
||
|
|
password_hash VARCHAR(255) NOT NULL,
|
||
|
|
name VARCHAR(255) NULL,
|
||
|
|
rolle ENUM('superadmin','admin','user','fahrer') NOT NULL DEFAULT 'user',
|
||
|
|
aktiv TINYINT(1) NOT NULL DEFAULT 1,
|
||
|
|
erstellt_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (id),
|
||
|
|
UNIQUE KEY uk_tenant_email (tenant_id, email),
|
||
|
|
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
INDEX idx_email (email),
|
||
|
|
INDEX idx_tenant (tenant_id)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- =========================================================
|
||
|
|
-- KUNDEN - mit Anlieferungshinweis + Lieferzeit (Erweiterung 1+3)
|
||
|
|
-- =========================================================
|
||
|
|
CREATE TABLE IF NOT EXISTS kunden (
|
||
|
|
id CHAR(36) NOT NULL DEFAULT (UUID()),
|
||
|
|
tenant_id CHAR(36) NOT NULL,
|
||
|
|
name VARCHAR(255) NOT NULL,
|
||
|
|
email VARCHAR(255) NULL,
|
||
|
|
adresse TEXT NULL,
|
||
|
|
telefon VARCHAR(50) NULL,
|
||
|
|
ansprechpartner VARCHAR(255) NULL,
|
||
|
|
notiz TEXT NULL,
|
||
|
|
anrede VARCHAR(20) NULL,
|
||
|
|
anlieferungshinweis TEXT NULL,
|
||
|
|
lieferzeit_bis VARCHAR(10) NULL, -- z.B. "05:15"
|
||
|
|
erstellt_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (id),
|
||
|
|
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
INDEX idx_name (name),
|
||
|
|
INDEX idx_tenant (tenant_id)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- =========================================================
|
||
|
|
-- KUNDEN_BILDER (Erweiterung 1)
|
||
|
|
-- =========================================================
|
||
|
|
CREATE TABLE IF NOT EXISTS kunden_bilder (
|
||
|
|
id CHAR(36) NOT NULL DEFAULT (UUID()),
|
||
|
|
tenant_id CHAR(36) NOT NULL,
|
||
|
|
kunde_id CHAR(36) NOT NULL,
|
||
|
|
dateiname VARCHAR(500) NOT NULL,
|
||
|
|
beschreibung VARCHAR(500) NULL,
|
||
|
|
sortierung INT NOT NULL DEFAULT 0,
|
||
|
|
erstellt_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (id),
|
||
|
|
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (kunde_id) REFERENCES kunden(id) ON DELETE CASCADE,
|
||
|
|
INDEX idx_kunde (kunde_id),
|
||
|
|
INDEX idx_tenant (tenant_id)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- =========================================================
|
||
|
|
-- ARTIKEL (Pfandartikel)
|
||
|
|
-- =========================================================
|
||
|
|
CREATE TABLE IF NOT EXISTS artikel (
|
||
|
|
id CHAR(36) NOT NULL DEFAULT (UUID()),
|
||
|
|
tenant_id CHAR(36) NOT NULL,
|
||
|
|
bezeichnung VARCHAR(255) NOT NULL,
|
||
|
|
pfand_betrag DECIMAL(10, 2) NOT NULL,
|
||
|
|
erstellt_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (id),
|
||
|
|
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
INDEX idx_bezeichnung (bezeichnung),
|
||
|
|
INDEX idx_tenant (tenant_id)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- =========================================================
|
||
|
|
-- GERAETE (Erweiterung 4: Seriennummern-Verwaltung)
|
||
|
|
-- =========================================================
|
||
|
|
CREATE TABLE IF NOT EXISTS geraete (
|
||
|
|
id CHAR(36) NOT NULL DEFAULT (UUID()),
|
||
|
|
tenant_id CHAR(36) NOT NULL,
|
||
|
|
seriennummer VARCHAR(255) NOT NULL,
|
||
|
|
typ VARCHAR(100) NOT NULL, -- z.B. "Ofen", "Kuehlschrank"
|
||
|
|
bezeichnung VARCHAR(255) NULL, -- z.B. "Miwe Condo 3-Etagen"
|
||
|
|
kunde_id CHAR(36) NULL, -- aktueller Standort
|
||
|
|
status ENUM('aktiv','in_reparatur','ausgemustert','lager') NOT NULL DEFAULT 'aktiv',
|
||
|
|
notiz TEXT NULL,
|
||
|
|
erstellt_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
aktualisiert_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (id),
|
||
|
|
UNIQUE KEY uk_tenant_serial (tenant_id, seriennummer),
|
||
|
|
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (kunde_id) REFERENCES kunden(id) ON DELETE SET NULL,
|
||
|
|
INDEX idx_tenant (tenant_id),
|
||
|
|
INDEX idx_kunde (kunde_id),
|
||
|
|
INDEX idx_status (status)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- =========================================================
|
||
|
|
-- LIEFERUNGEN
|
||
|
|
-- =========================================================
|
||
|
|
CREATE TABLE IF NOT EXISTS lieferungen (
|
||
|
|
id INT AUTO_INCREMENT NOT NULL,
|
||
|
|
tenant_id CHAR(36) NOT NULL,
|
||
|
|
kunde_id CHAR(36) NULL,
|
||
|
|
artikel_id CHAR(36) NULL,
|
||
|
|
anzahl INT NOT NULL,
|
||
|
|
foto_url VARCHAR(500) NULL,
|
||
|
|
mitarbeiter_id CHAR(36) NULL,
|
||
|
|
erstellt_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (id),
|
||
|
|
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (kunde_id) REFERENCES kunden(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (artikel_id) REFERENCES artikel(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (mitarbeiter_id) REFERENCES mitarbeiter(id) ON DELETE SET NULL,
|
||
|
|
INDEX idx_tenant (tenant_id),
|
||
|
|
INDEX idx_kunde (kunde_id),
|
||
|
|
INDEX idx_artikel (artikel_id),
|
||
|
|
INDEX idx_erstellt_am (erstellt_am)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- =========================================================
|
||
|
|
-- RUECKNAHMEN
|
||
|
|
-- =========================================================
|
||
|
|
CREATE TABLE IF NOT EXISTS ruecknahmen (
|
||
|
|
id INT AUTO_INCREMENT NOT NULL,
|
||
|
|
tenant_id CHAR(36) NOT NULL,
|
||
|
|
kunde_id CHAR(36) NULL,
|
||
|
|
artikel_id CHAR(36) NULL,
|
||
|
|
anzahl INT NOT NULL,
|
||
|
|
foto_url VARCHAR(500) NULL,
|
||
|
|
mitarbeiter_id CHAR(36) NULL,
|
||
|
|
erstellt_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (id),
|
||
|
|
FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (kunde_id) REFERENCES kunden(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (artikel_id) REFERENCES artikel(id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (mitarbeiter_id) REFERENCES mitarbeiter(id) ON DELETE SET NULL,
|
||
|
|
INDEX idx_tenant (tenant_id),
|
||
|
|
INDEX idx_kunde (kunde_id),
|
||
|
|
INDEX idx_artikel (artikel_id),
|
||
|
|
INDEX idx_erstellt_am (erstellt_am)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
||
|
|
|
||
|
|
-- =========================================================
|
||
|
|
-- Seed: SuperAdmin (tenant_id NULL) + Demo-Tenant
|
||
|
|
-- Passwort wird beim ersten Backend-Start gesetzt (env SUPERADMIN_*)
|
||
|
|
-- =========================================================
|