geograsim/App/Don_t_Deploy/2026-08-26-lizenz-vertrieb.sql

-- =====================================================================
-- Lizenz-Vergabe / Vertrieb  (2026-08-26)
-- Additiv & non-breaking: bestehende `licenses`-Zeilen (batch_id NULL) und
-- die Flows generate/redeem/assign bleiben unveraendert funktionsfaehig.
-- Neu: Kunden, Chargen (Bestellungen), Mail-Archiv, Storno, Verlaengerung,
--      Brute-Force-Schutz + Audit-Log, laengere Codes, Charge-Einloeselink.
-- =====================================================================

-- 1) Kunden (Besteller) ------------------------------------------------
CREATE TABLE IF NOT EXISTS license_customers (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_no VARCHAR(40)  NULL,          -- INTERNE Kundennummer (optional)
  email       VARCHAR(190) NULL,
  name        VARCHAR(190) NULL,          -- Besteller / Verlag / Schule
  note        VARCHAR(255) NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_customer_no (customer_no),
  KEY idx_email (email),
  KEY idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2) Chargen (Bestellungen) -------------------------------------------
CREATE TABLE IF NOT EXISTS license_batches (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id         INT UNSIGNED NULL,
  order_no            VARCHAR(120) NULL,   -- EXTERNE Bestellnummer (Marktplatz/Ministerium), Freitext -> geht in die Mail
  sales_type          VARCHAR(30) NOT NULL DEFAULT 'privat', -- marktplatz | direktvertrieb | privat (spaeter erweiterbar)
  qty                 INT UNSIGNED NOT NULL DEFAULT 0,
  default_valid_from  DATE NULL,
  default_valid_until DATE NULL,
  note                VARCHAR(255) NULL,
  redeem_token        CHAR(40) NULL,       -- Sammel-Einloeselink der ganzen Charge
  created_by          INT UNSIGNED NULL,   -- admin_users.id
  created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  canceled_at         TIMESTAMP NULL,
  canceled_reason     VARCHAR(255) NULL,
  UNIQUE KEY uq_redeem_token (redeem_token),
  KEY idx_customer (customer_id),
  KEY idx_order (order_no),
  KEY idx_type (sales_type),
  KEY idx_created (created_at),
  CONSTRAINT fk_batch_customer FOREIGN KEY (customer_id) REFERENCES license_customers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3) licenses erweitern ------------------------------------------------
--    code auf 32 Zeichen verbreitern (laengere Codes), Vertriebsfelder ergaenzen.
ALTER TABLE licenses
  MODIFY COLUMN code VARCHAR(32) NOT NULL,
  ADD COLUMN batch_id        INT UNSIGNED NULL AFTER school_year,
  ADD COLUMN issued_at       TIMESTAMP NULL     AFTER redeemed_at,
  ADD COLUMN valid_from      DATE NULL          AFTER issued_at,
  ADD COLUMN valid_until     DATE NULL          AFTER valid_from,
  ADD COLUMN canceled_at     TIMESTAMP NULL     AFTER valid_until,
  ADD COLUMN canceled_reason VARCHAR(255) NULL  AFTER canceled_at,
  ADD KEY idx_batch (batch_id),
  ADD KEY idx_valid_until (valid_until),
  ADD KEY idx_issued (issued_at),
  ADD CONSTRAINT fk_license_batch FOREIGN KEY (batch_id) REFERENCES license_batches(id) ON DELETE SET NULL;

-- 4) Mail-Archiv / Outbox (unabhaengig vom IMAP-Postausgang) -----------
CREATE TABLE IF NOT EXISTS license_mails (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  batch_id   INT UNSIGNED NULL,
  to_email   VARCHAR(190) NOT NULL,
  subject    VARCHAR(255) NOT NULL,
  body_text  MEDIUMTEXT NULL,             -- gerenderter Klartext (kopierbar + Archiv)
  body_html  MEDIUMTEXT NULL,
  provider   VARCHAR(20) NOT NULL DEFAULT 'server', -- server | ses
  status     VARCHAR(12) NOT NULL DEFAULT 'queued', -- queued | sent | failed
  error      VARCHAR(255) NULL,
  created_by INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at    TIMESTAMP NULL,
  KEY idx_batch (batch_id),
  KEY idx_status (status),
  CONSTRAINT fk_mail_batch FOREIGN KEY (batch_id) REFERENCES license_batches(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5) Brute-Force-Schutz + Audit beim Einloesen -------------------------
CREATE TABLE IF NOT EXISTS license_redeem_attempts (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ip         VARBINARY(16) NULL,          -- INET6_ATON(...)
  teacher_id INT UNSIGNED NULL,
  code_tried VARCHAR(32) NULL,
  ok         TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ip_time (ip, created_at),
  KEY idx_teacher_time (teacher_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6) Audit-Log fuer Chargen/Codes -------------------------------------
CREATE TABLE IF NOT EXISTS license_events (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  batch_id   INT UNSIGNED NULL,
  license_id INT UNSIGNED NULL,
  event      VARCHAR(30) NOT NULL,        -- issued|extended|canceled|mailed|redeemed
  detail     VARCHAR(255) NULL,
  admin_id   INT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_batch (batch_id),
  KEY idx_license (license_id),
  KEY idx_event (event)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;