-- =================================================================
-- CRM für Network Marketing / Empfehlungsmarketing
-- Datenbankschema — FINALE VERSION 1.0
-- MySQL 8.x | Engine: InnoDB | Charset: utf8mb4
-- =================================================================
--
-- Änderungen gegenüber Revision 2 (letzte Optimierung):
--   1. Tabelle `partners` vollständig entfernt.
--      Partner-Status wird ausschließlich über `contacts.partner` geführt.
--   2. `activities.typ` (ENUM) ersetzt durch `activities.activity_type_id`
--      (FK -> neue Tabelle `activity_types`).
--   3. `tasks.faelligkeitsdatum`: DATE -> DATETIME.
--   4. `contacts.notizen` entfernt.
--   5. Neue Tabelle `contact_notes` (mehrere Notizen pro Kontakt,
--      mit Autor-Bezug über user_id).
--   6. Alle Foreign Keys / Indizes / ER-Modell entsprechend aktualisiert.
--
-- Offener Punkt (bitte prüfen):
--   Mit dem Entfernen von `partners` entfallen auch die Felder
--   `rang`, `teamgroesse` und `einstieg`, die bisher dort gepflegt wurden.
--   Diese sind in keiner anderen Tabelle erfasst. Falls das Team-Modul
--   (team.php) diese Informationen weiterhin benötigt, müssten sie z. B.
--   als zusätzliche Spalten auf `contacts` ergänzt werden — das habe ich
--   NICHT eigenständig hinzugefügt, da es nicht beauftragt war.
-- =================================================================


-- =================================================================
-- Tabelle: contact_statuses
-- Lookup-Tabelle für Kontakt-Status
-- =================================================================
CREATE TABLE `contact_statuses` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(50) NOT NULL,
  `sortierung`  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `farbe`       VARCHAR(7) NULL DEFAULT '#6c757d',
  `aktiv`       TINYINT(1) NOT NULL DEFAULT 1,
  `erstellt_am` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_contact_statuses_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =================================================================
-- Tabelle: lead_sources
-- Lookup-Tabelle für Kontaktquellen
-- =================================================================
CREATE TABLE `lead_sources` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(100) NOT NULL,
  `aktiv`       TINYINT(1) NOT NULL DEFAULT 1,
  `erstellt_am` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_lead_sources_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =================================================================
-- Tabelle: activity_types
-- Lookup-Tabelle für Aktivitätstypen (ersetzt vorheriges ENUM)
-- =================================================================
CREATE TABLE `activity_types` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(50) NOT NULL,
  `sortierung`  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `farbe`       VARCHAR(7) NULL DEFAULT '#6c757d',
  `aktiv`       TINYINT(1) NOT NULL DEFAULT 1,
  `erstellt_am` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_activity_types_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =================================================================
-- Tabelle: contacts
-- Zentrale Kontakt- / Lead-Tabelle
-- =================================================================
CREATE TABLE `contacts` (
  `id`                       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `vorname`                  VARCHAR(100) NOT NULL,
  `nachname`                 VARCHAR(100) NOT NULL,

  `telefon`                  VARCHAR(50)  NULL,
  `telefon_normalisiert`     VARCHAR(30)  NULL,
  `whatsapp`                 VARCHAR(50)  NULL,
  `whatsapp_normalisiert`    VARCHAR(30)  NULL,
  `email`                    VARCHAR(150) NULL,
  `adresse`                  VARCHAR(255) NULL,
  `geburtstag`               DATE NULL,

  `source_id`                INT UNSIGNED NULL,
  `sponsor_id`                INT UNSIGNED NULL,

  `status_id`                INT UNSIGNED NOT NULL DEFAULT 1,
  `interesse_level`          TINYINT UNSIGNED NULL,

  `kunde`                    TINYINT(1) NOT NULL DEFAULT 0,
  `partner`                  TINYINT(1) NOT NULL DEFAULT 0,

  `letzter_kontakt`          DATE NULL,
  `naechste_wiedervorlage`   DATETIME NULL,

  `datenschutz_einwilligung` TINYINT(1) NOT NULL DEFAULT 0,
  `einwilligung_am`          DATETIME NULL,

  `deleted_at`               DATETIME NULL,

  `erstellt_am`              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `aktualisiert_am`          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  KEY `idx_contacts_status_id` (`status_id`),
  KEY `idx_contacts_source_id` (`source_id`),
  KEY `idx_contacts_sponsor_id` (`sponsor_id`),
  KEY `idx_contacts_interesse_level` (`interesse_level`),
  KEY `idx_contacts_kunde` (`kunde`),
  KEY `idx_contacts_partner` (`partner`),
  KEY `idx_contacts_nachname` (`nachname`),
  KEY `idx_contacts_telefon_normalisiert` (`telefon_normalisiert`),
  KEY `idx_contacts_whatsapp_normalisiert` (`whatsapp_normalisiert`),
  KEY `idx_contacts_email` (`email`),
  KEY `idx_contacts_naechste_wiedervorlage` (`naechste_wiedervorlage`),
  KEY `idx_contacts_deleted_at` (`deleted_at`),

  CONSTRAINT `chk_contacts_interesse_level`
      CHECK (`interesse_level` IS NULL OR (`interesse_level` BETWEEN 1 AND 5)),

  CONSTRAINT `fk_contacts_sponsor`
      FOREIGN KEY (`sponsor_id`) REFERENCES `contacts` (`id`)
      ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_contacts_status`
      FOREIGN KEY (`status_id`) REFERENCES `contact_statuses` (`id`)
      ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_contacts_source`
      FOREIGN KEY (`source_id`) REFERENCES `lead_sources` (`id`)
      ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =================================================================
-- Tabelle: activities
-- Aktivitätsverlauf je Kontakt (Telefonat, WhatsApp, Meeting, ...)
-- =================================================================
CREATE TABLE `activities` (
  `id`                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `contact_id`        INT UNSIGNED NOT NULL,
  `user_id`           INT UNSIGNED NULL,
  `activity_type_id`  INT UNSIGNED NOT NULL,
  `datum`             DATETIME NOT NULL,
  `notiz`             TEXT NULL,
  `ergebnis`          VARCHAR(255) NULL,
  `erstellt_am`       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  KEY `idx_activities_contact_id` (`contact_id`),
  KEY `idx_activities_user_id` (`user_id`),
  KEY `idx_activities_activity_type_id` (`activity_type_id`),
  KEY `idx_activities_datum` (`datum`),
  CONSTRAINT `fk_activities_contact`
      FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`)
      ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_activities_user`
      FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
      ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_activities_type`
      FOREIGN KEY (`activity_type_id`) REFERENCES `activity_types` (`id`)
      ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =================================================================
-- Tabelle: tasks
-- Aufgaben, optional mit Kontaktbezug und Bearbeiter
-- =================================================================
CREATE TABLE `tasks` (
  `id`                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `contact_id`          INT UNSIGNED NULL,
  `assigned_user_id`    INT UNSIGNED NULL,
  `titel`               VARCHAR(150) NOT NULL,
  `beschreibung`        TEXT NULL,
  `faelligkeitsdatum`   DATETIME NULL,
  `prioritaet`          ENUM('Niedrig','Normal','Hoch') NOT NULL DEFAULT 'Normal',
  `erledigt`            TINYINT(1) NOT NULL DEFAULT 0,
  `erstellt_am`         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `aktualisiert_am`     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  KEY `idx_tasks_contact_id` (`contact_id`),
  KEY `idx_tasks_assigned_user_id` (`assigned_user_id`),
  KEY `idx_tasks_faelligkeitsdatum` (`faelligkeitsdatum`),
  KEY `idx_tasks_erledigt` (`erledigt`),
  CONSTRAINT `fk_tasks_contact`
      FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`)
      ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_tasks_user`
      FOREIGN KEY (`assigned_user_id`) REFERENCES `users` (`id`)
      ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =================================================================
-- Tabelle: contact_notes
-- Mehrere Notizen pro Kontakt, mit Autor (ersetzt contacts.notizen)
-- =================================================================
CREATE TABLE `contact_notes` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `contact_id`  INT UNSIGNED NOT NULL,
  `user_id`     INT UNSIGNED NULL,
  `notiz`       TEXT NOT NULL,
  `erstellt_am` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  KEY `idx_contact_notes_contact_id` (`contact_id`),
  KEY `idx_contact_notes_user_id` (`user_id`),
  CONSTRAINT `fk_contact_notes_contact`
      FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`)
      ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_contact_notes_user`
      FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
      ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =================================================================
-- Tabelle: tags
-- Beliebige Schlagwörter
-- =================================================================
CREATE TABLE `tags` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(100) NOT NULL,
  `farbe`       VARCHAR(7) NULL DEFAULT '#6c757d',
  `erstellt_am` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_tags_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =================================================================
-- Tabelle: contact_tags
-- n:m-Verknüpfung Kontakte <-> Tags
-- =================================================================
CREATE TABLE `contact_tags` (
  `contact_id` INT UNSIGNED NOT NULL,
  `tag_id`     INT UNSIGNED NOT NULL,

  PRIMARY KEY (`contact_id`, `tag_id`),
  KEY `idx_contact_tags_tag_id` (`tag_id`),
  CONSTRAINT `fk_contact_tags_contact`
      FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`)
      ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_contact_tags_tag`
      FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`)
      ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =================================================================
-- Seed-Daten: contact_statuses
-- Feste IDs, damit contacts.status_id DEFAULT 1 ("Neu") zuverlässig greift
-- =================================================================
INSERT INTO `contact_statuses` (`id`, `name`, `sortierung`, `farbe`) VALUES
  (1, 'Neu',                          1, '#0dcaf0'),
  (2, 'Kontaktiert',                  2, '#0d6efd'),
  (3, 'Termin geplant',               3, '#6610f2'),
  (4, 'Präsentation durchgeführt',    4, '#6f42c1'),
  (5, 'Interessiert',                 5, '#fd7e14'),
  (6, 'Kunde',                        6, '#198754'),
  (7, 'Partner',                      7, '#20c997'),
  (8, 'Kein Interesse',               8, '#dc3545');

-- =================================================================
-- Seed-Daten: lead_sources (Beispielwerte, später frei editierbar)
-- =================================================================
INSERT INTO `lead_sources` (`name`) VALUES
  ('Empfehlung'),
  ('Social Media'),
  ('Messe / Event'),
  ('Webseite'),
  ('Kaltkontakt'),
  ('Sonstiges');

-- =================================================================
-- Seed-Daten: activity_types
-- Feste IDs (ersetzt das frühere ENUM 1:1)
-- =================================================================
INSERT INTO `activity_types` (`id`, `name`, `sortierung`, `farbe`) VALUES
  (1, 'Telefonat',      1, '#0d6efd'),
  (2, 'WhatsApp',       2, '#198754'),
  (3, 'E-Mail',         3, '#6c757d'),
  (4, 'Meeting',        4, '#fd7e14'),
  (5, 'Zoom',           5, '#6610f2'),
  (6, 'Präsentation',   6, '#dc3545');

-- =================================================================
-- Seed-Daten: tags (Beispiel-Schlagwörter)
-- =================================================================
INSERT INTO `tags` (`name`, `farbe`) VALUES
  ('Fitness',          '#fd7e14'),
  ('Finanzen',          '#198754'),
  ('Immobilien',        '#0d6efd'),
  ('Online Marketing',  '#6610f2'),
  ('Unternehmer',       '#dc3545');
