96 lines
3.6 KiB
SQL
96 lines
3.6 KiB
SQL
CREATE TABLE IF NOT EXISTS kl_Mitarbeiter (
|
|
MitarbeiterID INT AUTO_INCREMENT PRIMARY KEY,
|
|
Name VARCHAR(255) NOT NULL,
|
|
Email VARCHAR(255) NOT NULL,
|
|
paypalname VARCHAR(255) NULL,
|
|
aktiv TINYINT(1) NOT NULL DEFAULT 1,
|
|
admin TINYINT(1) NOT NULL DEFAULT 0,
|
|
UNIQUE KEY uq_kl_mitarbeiter_email (Email)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS kl_config (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
KostenproStrich DECIMAL(10,2) NOT NULL DEFAULT 0.20,
|
|
paypaluse TINYINT(1) NOT NULL DEFAULT 0,
|
|
paypallink VARCHAR(1000) NOT NULL DEFAULT '',
|
|
strichperweb TINYINT(1) NOT NULL DEFAULT 1
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS kl_Einzahlungen (
|
|
EinzahlungsID INT AUTO_INCREMENT PRIMARY KEY,
|
|
MitarbeiterID INT NOT NULL,
|
|
Betrag DECIMAL(10,2) NOT NULL,
|
|
Datum DATETIME NOT NULL,
|
|
KEY idx_kl_einzahlungen_mitarbeiter_datum (MitarbeiterID, Datum),
|
|
CONSTRAINT fk_kl_einzahlungen_mitarbeiter
|
|
FOREIGN KEY (MitarbeiterID) REFERENCES kl_Mitarbeiter(MitarbeiterID)
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS kl_Kaffeeverbrauch (
|
|
VerbrauchID INT AUTO_INCREMENT PRIMARY KEY,
|
|
MitarbeiterID INT NOT NULL,
|
|
AnzahlStriche INT NOT NULL,
|
|
Kosten DECIMAL(10,2) NOT NULL,
|
|
KostenproStrich DECIMAL(10,2) NOT NULL,
|
|
Datum DATETIME NOT NULL,
|
|
Eintragsart INT NULL,
|
|
KEY idx_kl_verbrauch_mitarbeiter_datum (MitarbeiterID, Datum),
|
|
CONSTRAINT fk_kl_verbrauch_mitarbeiter
|
|
FOREIGN KEY (MitarbeiterID) REFERENCES kl_Mitarbeiter(MitarbeiterID)
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS kl_hinweise (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nachricht TEXT NOT NULL,
|
|
gueltig_bis DATETIME NOT NULL,
|
|
KEY idx_kl_hinweise_gueltig_bis (gueltig_bis)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS CoffeeSurveyVotedEmails (
|
|
EmailNorm VARCHAR(255) PRIMARY KEY,
|
|
CreatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS CoffeeSurveyResponses (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
Q1_ListEase INT NOT NULL,
|
|
Q2_ListSatisfaction INT NOT NULL,
|
|
Q3_CoffeeQuality INT NOT NULL,
|
|
Q4_WebsiteSatisfaction INT NOT NULL,
|
|
Q5_Drinks TEXT NOT NULL,
|
|
Q5_DrinksOther TEXT NULL,
|
|
Q6_NewVarieties TEXT NULL,
|
|
Q7_ListProblem VARCHAR(100) NOT NULL,
|
|
Q7_ListProblemOther TEXT NULL,
|
|
Q8_Improvements TEXT NOT NULL,
|
|
Q8_ImprovementsOther TEXT NULL,
|
|
Q9_BetterIdeas TEXT NULL,
|
|
CreatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS dev_baseline_metadata (
|
|
meta_key VARCHAR(100) PRIMARY KEY,
|
|
meta_value TEXT NOT NULL,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
INSERT INTO kl_config (id, KostenproStrich, paypaluse, paypallink, strichperweb)
|
|
VALUES (1, 0.20, 0, '', 1)
|
|
ON DUPLICATE KEY UPDATE
|
|
KostenproStrich = VALUES(KostenproStrich),
|
|
paypaluse = VALUES(paypaluse),
|
|
strichperweb = VALUES(strichperweb);
|
|
|
|
INSERT INTO dev_baseline_metadata (meta_key, meta_value)
|
|
VALUES
|
|
('baseline_kind', 'reconstructed_mysql_dev'),
|
|
('legacy_database_status', 'no_ms_sql_legacy_snapshot_available'),
|
|
('legacy_behavior_source', 'code_and_screenshots'),
|
|
('visual_reference_path', 'docs/m0/screenshots'),
|
|
('schema_source', 'database/mysql-dev-schema.sql')
|
|
ON DUPLICATE KEY UPDATE
|
|
meta_value = VALUES(meta_value);
|