-- ============================================================================
-- Newsletter- & Mailing-System (Web & Projekte → Newsletter)
-- Projekt-Basis: crm_websites (website_id = Projekt). Prefix: crm_nl_
-- Idempotent (CREATE TABLE IF NOT EXISTS) – wird zusätzlich von
-- Newsletter::ensureSchema() beim ersten Aufruf automatisch eingespielt.
-- ============================================================================

-- Projektbezogene Newsletter-Einstellungen (1 Zeile je Website)
CREATE TABLE IF NOT EXISTS crm_nl_settings (
  website_id      INT UNSIGNED NOT NULL PRIMARY KEY,
  from_name       VARCHAR(120) NOT NULL DEFAULT '',
  from_email      VARCHAR(190) NOT NULL DEFAULT '',
  reply_to        VARCHAR(190) NOT NULL DEFAULT '',
  company_name    VARCHAR(190) NOT NULL DEFAULT '',      -- rechtliche Angaben (Footer)
  company_address VARCHAR(255) NOT NULL DEFAULT '',
  doi_subject     VARCHAR(190) NOT NULL DEFAULT '',      -- Bestätigungs-Mail
  doi_html        MEDIUMTEXT NULL,
  welcome_subject VARCHAR(190) NOT NULL DEFAULT '',      -- optionale Willkommens-Mail
  welcome_html    MEDIUMTEXT NULL,
  bye_subject     VARCHAR(190) NOT NULL DEFAULT '',      -- optionale Abmelde-Bestätigung
  bye_html        MEDIUMTEXT NULL,
  track_opens     TINYINT(1) NOT NULL DEFAULT 1,
  track_clicks    TINYINT(1) NOT NULL DEFAULT 1,
  daily_limit     INT UNSIGNED NOT NULL DEFAULT 0,       -- 0 = unbegrenzt (Projekt-Limit)
  token_ttl_hours INT UNSIGNED NOT NULL DEFAULT 72,      -- DOI-Token-Gültigkeit
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Verteilerlisten je Projekt
CREATE TABLE IF NOT EXISTS crm_nl_lists (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  website_id  INT UNSIGNED NOT NULL,
  name        VARCHAR(120) NOT NULL,
  slug        VARCHAR(120) NOT NULL,                     -- für Plugin/API ('hauptnewsletter')
  description VARCHAR(255) NOT NULL DEFAULT '',
  active      TINYINT(1) NOT NULL DEFAULT 1,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_site_slug (website_id, slug),
  KEY idx_site (website_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Abonnenten: 1 Datensatz je (Projekt, E-Mail) – Projekttrennung per Design
CREATE TABLE IF NOT EXISTS crm_nl_subscribers (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid           CHAR(36) NOT NULL,
  website_id     INT UNSIGNED NOT NULL,
  email          VARCHAR(190) NOT NULL,
  salutation     VARCHAR(20)  NOT NULL DEFAULT '',
  first_name     VARCHAR(80)  NOT NULL DEFAULT '',
  last_name      VARCHAR(80)  NOT NULL DEFAULT '',
  lang           VARCHAR(5)   NOT NULL DEFAULT 'de',
  status         ENUM('pending','active','unsubscribed','bounced','blocked','complained','manually_disabled') NOT NULL DEFAULT 'pending',
  source         VARCHAR(120) NOT NULL DEFAULT '',       -- z.B. wordpress:form-slug, import, manual
  tags           VARCHAR(500) NOT NULL DEFAULT '',       -- normalisierte CSV (FIND_IN_SET)
  custom_fields  TEXT NULL,                              -- JSON
  note           TEXT NULL,                              -- interne Notiz
  consent_basis  VARCHAR(120) NOT NULL DEFAULT '',       -- Rechtsgrundlage (Import!)
  optin_ip       VARCHAR(45) NOT NULL DEFAULT '',
  optin_ua       VARCHAR(255) NOT NULL DEFAULT '',
  confirm_ip     VARCHAR(45) NOT NULL DEFAULT '',
  optout_ip      VARCHAR(45) NOT NULL DEFAULT '',
  doi_token_hash CHAR(64) NULL,                          -- SHA256 – Token nie im Klartext
  doi_expires_at DATETIME NULL,
  subscribed_at  DATETIME NULL,
  confirmed_at   DATETIME NULL,
  unsubscribed_at DATETIME NULL,
  bounce_count   INT UNSIGNED NOT NULL DEFAULT 0,
  last_sent_at   DATETIME NULL,
  last_open_at   DATETIME NULL,
  last_click_at  DATETIME NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_site_email (website_id, email),
  UNIQUE KEY uq_uuid (uuid),
  KEY idx_site_status (website_id, status),
  KEY idx_doi (doi_token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Listen-Zuordnung (n:m)
CREATE TABLE IF NOT EXISTS crm_nl_list_members (
  list_id       INT UNSIGNED NOT NULL,
  subscriber_id INT UNSIGNED NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (list_id, subscriber_id),
  KEY idx_sub (subscriber_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dynamische Segmente (Regeln als JSON, Auflösung erst beim Versand)
CREATE TABLE IF NOT EXISTS crm_nl_segments (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  website_id INT UNSIGNED NOT NULL,
  name       VARCHAR(120) NOT NULL,
  rules      TEXT NULL,                                  -- JSON: {list_id, tag, lang, status, source, since, opened_campaign, not_opened_campaign, clicked_campaign}
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_site (website_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Templates (projektbezogen oder global via website_id=0)
CREATE TABLE IF NOT EXISTS crm_nl_templates (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  website_id  INT UNSIGNED NOT NULL DEFAULT 0,           -- 0 = global
  name        VARCHAR(120) NOT NULL,
  description VARCHAR(255) NOT NULL DEFAULT '',
  category    VARCHAR(60)  NOT NULL DEFAULT '',
  html        MEDIUMTEXT NULL,
  text        MEDIUMTEXT NULL,
  active      TINYINT(1) NOT NULL DEFAULT 1,
  created_by  INT UNSIGNED NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_site (website_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Template-Versionshistorie
CREATE TABLE IF NOT EXISTS crm_nl_template_versions (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  template_id INT UNSIGNED NOT NULL,
  html        MEDIUMTEXT NULL,
  text        MEDIUMTEXT NULL,
  created_by  INT UNSIGNED NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_tpl (template_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Anmelde-Formulare (öffentlicher form_key fürs WordPress-Plugin, streng getrennt
-- von administrativen API-Tokens und darf NUR anmelden, nichts lesen)
CREATE TABLE IF NOT EXISTS crm_nl_forms (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  website_id     INT UNSIGNED NOT NULL,
  name           VARCHAR(120) NOT NULL,
  form_key       CHAR(40) NOT NULL,                      -- öffentlicher Schlüssel (nlf_…)
  list_id        INT UNSIGNED NOT NULL DEFAULT 0,        -- Standardliste
  fields         VARCHAR(255) NOT NULL DEFAULT 'email',  -- CSV: email,salutation,first_name,last_name,lang
  success_text   VARCHAR(255) NOT NULL DEFAULT '',
  error_text     VARCHAR(255) NOT NULL DEFAULT '',
  privacy_text   TEXT NULL,                              -- Checkbox-/Datenschutztext
  lang           VARCHAR(5) NOT NULL DEFAULT 'de',
  active         TINYINT(1) NOT NULL DEFAULT 1,
  rate_per_10min INT UNSIGNED NOT NULL DEFAULT 10,       -- Anmeldungen je IP/10 Min
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_key (form_key),
  KEY idx_site (website_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- SMTP-Verbindungen je Projekt (Passwort AES-verschlüsselt via crm_encrypt)
CREATE TABLE IF NOT EXISTS crm_nl_smtp (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  website_id    INT UNSIGNED NOT NULL,
  label         VARCHAR(120) NOT NULL,
  host          VARCHAR(190) NOT NULL,
  port          INT UNSIGNED NOT NULL DEFAULT 587,
  secure        ENUM('tls','ssl','none') NOT NULL DEFAULT 'tls',
  username      VARCHAR(190) NOT NULL DEFAULT '',
  pass_enc      TEXT NULL,                               -- crm_encrypt() – nie Klartext
  from_email    VARCHAR(190) NOT NULL DEFAULT '',
  from_name     VARCHAR(120) NOT NULL DEFAULT '',
  reply_to      VARCHAR(190) NOT NULL DEFAULT '',
  timeout_sec   INT UNSIGNED NOT NULL DEFAULT 15,
  hourly_limit  INT UNSIGNED NOT NULL DEFAULT 0,         -- 0 = unbegrenzt
  daily_limit   INT UNSIGNED NOT NULL DEFAULT 0,
  batch_size    INT UNSIGNED NOT NULL DEFAULT 50,        -- Mails je Worker-Lauf
  pause_ms      INT UNSIGNED NOT NULL DEFAULT 200,       -- Pause zwischen Mails
  priority      INT NOT NULL DEFAULT 0,                  -- höhere zuerst (Rotation)
  active        TINYINT(1) NOT NULL DEFAULT 1,
  last_ok_at    DATETIME NULL,
  last_error    VARCHAR(500) NOT NULL DEFAULT '',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_site (website_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Kampagnen
CREATE TABLE IF NOT EXISTS crm_nl_campaigns (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  website_id    INT UNSIGNED NOT NULL,
  name          VARCHAR(150) NOT NULL,                   -- interner Name
  subject       VARCHAR(190) NOT NULL DEFAULT '',
  preheader     VARCHAR(190) NOT NULL DEFAULT '',
  from_name     VARCHAR(120) NOT NULL DEFAULT '',
  from_email    VARCHAR(190) NOT NULL DEFAULT '',
  reply_to      VARCHAR(190) NOT NULL DEFAULT '',
  template_id   INT UNSIGNED NOT NULL DEFAULT 0,
  html          MEDIUMTEXT NULL,
  text          MEDIUMTEXT NULL,
  list_id       INT UNSIGNED NOT NULL DEFAULT 0,         -- Empfänger: Liste ODER Segment
  segment_id    INT UNSIGNED NOT NULL DEFAULT 0,
  smtp_id       INT UNSIGNED NOT NULL DEFAULT 0,         -- 0 = automatische Rotation
  status        ENUM('draft','ready','scheduled','queued','sending','paused','completed','cancelled','failed') NOT NULL DEFAULT 'draft',
  priority      INT NOT NULL DEFAULT 0,
  track_opens   TINYINT(1) NOT NULL DEFAULT 1,
  track_clicks  TINYINT(1) NOT NULL DEFAULT 1,
  scheduled_at  DATETIME NULL,
  started_at    DATETIME NULL,
  completed_at  DATETIME NULL,
  total_recipients INT UNSIGNED NOT NULL DEFAULT 0,
  notes         TEXT NULL,
  created_by    INT UNSIGNED NOT NULL DEFAULT 0,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_site_status (website_id, status),
  KEY idx_sched (status, scheduled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Versandwarteschlange: 1 Zeile je (Kampagne, Empfänger) – Doppelversand
-- ist durch den UNIQUE-Key technisch ausgeschlossen
CREATE TABLE IF NOT EXISTS crm_nl_queue (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  uuid          CHAR(36) NOT NULL,
  campaign_id   INT UNSIGNED NOT NULL,
  website_id    INT UNSIGNED NOT NULL,
  subscriber_id INT UNSIGNED NOT NULL,
  email         VARCHAR(190) NOT NULL,
  smtp_id       INT UNSIGNED NOT NULL DEFAULT 0,
  status        ENUM('pending','processing','sent','retry','failed','cancelled','suppressed') NOT NULL DEFAULT 'pending',
  attempts      INT UNSIGNED NOT NULL DEFAULT 0,
  send_after    DATETIME NULL,                           -- geplante/Backoff-Zustellung
  reserved_by   VARCHAR(40) NOT NULL DEFAULT '',         -- Worker-Lock (atomar)
  reserved_at   DATETIME NULL,
  last_attempt_at DATETIME NULL,
  error_code    VARCHAR(40) NOT NULL DEFAULT '',
  error_msg     VARCHAR(500) NOT NULL DEFAULT '',
  message_id    VARCHAR(190) NOT NULL DEFAULT '',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at       DATETIME NULL,
  UNIQUE KEY uq_camp_sub (campaign_id, subscriber_id),
  KEY idx_work (status, send_after),
  KEY idx_camp (campaign_id, status),
  KEY idx_site_sent (website_id, sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Kampagnen-Links (Klick-Tracking: Redirect NUR auf hier gespeicherte URLs)
CREATE TABLE IF NOT EXISTS crm_nl_links (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT UNSIGNED NOT NULL,
  url         VARCHAR(1000) NOT NULL,
  token       CHAR(16) NOT NULL,                         -- nicht erratbar, je Kampagne eindeutig
  clicks      INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY uq_camp_token (campaign_id, token),
  KEY idx_camp (campaign_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Versand-/Interaktions-Ereignisse (Statistiken, pseudonym über subscriber_id)
CREATE TABLE IF NOT EXISTS crm_nl_events (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  website_id    INT UNSIGNED NOT NULL,
  campaign_id   INT UNSIGNED NOT NULL DEFAULT 0,
  subscriber_id INT UNSIGNED NOT NULL DEFAULT 0,
  type          ENUM('sent','open','click','bounce_soft','bounce_hard','unsubscribe','complaint') NOT NULL,
  link_id       INT UNSIGNED NOT NULL DEFAULT 0,
  meta          VARCHAR(255) NOT NULL DEFAULT '',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_camp_type (campaign_id, type),
  KEY idx_sub (subscriber_id),
  KEY idx_site_day (website_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Suppression: dauerhaft gesperrte Adressen je Projekt (Abmeldung/Hard-Bounce/
-- Beschwerde) – Import/Sync darf diese NIE reaktivieren
CREATE TABLE IF NOT EXISTS crm_nl_suppression (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  website_id INT UNSIGNED NOT NULL,
  email      VARCHAR(190) NOT NULL,
  reason     ENUM('unsubscribed','bounced','complained','manual') NOT NULL DEFAULT 'unsubscribed',
  detail     VARCHAR(255) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_site_email (website_id, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Fachliches Protokoll / Audit (keine Mail-Inhalte, keine Passwörter)
CREATE TABLE IF NOT EXISTS crm_nl_log (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  website_id    INT UNSIGNED NOT NULL DEFAULT 0,
  campaign_id   INT UNSIGNED NOT NULL DEFAULT 0,
  subscriber_id INT UNSIGNED NOT NULL DEFAULT 0,
  actor         VARCHAR(120) NOT NULL DEFAULT '',        -- User-Name | system | api | wordpress
  event         VARCHAR(60) NOT NULL,                    -- subscribe, confirm, unsubscribe, import, campaign_start, smtp_change, …
  detail        VARCHAR(500) NOT NULL DEFAULT '',
  ip            VARCHAR(45) NOT NULL DEFAULT '',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_site_event (website_id, event),
  KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;