-- =====================================================================
--  ArNi Solutions CRM – Zusatzmodule
--  Pushover/Erinnerungen · Webseitenverwaltung · Doku · Prompt-Vorlagen
--  Alle Tabellen mit Prefix crm_  ·  utf8mb4 · InnoDB
-- =====================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- Erinnerungen (Versand per Pushover und/oder E-Mail zu einem Zeitpunkt)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_reminders` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title`        VARCHAR(190) NOT NULL,
  `message`      TEXT NULL,
  `remind_at`    DATETIME NOT NULL,
  `channel`      ENUM('pushover','email','both') NOT NULL DEFAULT 'pushover',
  `to_email`     VARCHAR(190) NULL,
  `priority`     TINYINT NOT NULL DEFAULT 0,        -- Pushover-Priorität -2..2
  `status`       ENUM('pending','sent','failed','done') NOT NULL DEFAULT 'pending',
  `sent_at`      DATETIME NULL,
  `related_type` VARCHAR(40) NULL,                  -- z.B. 'invoice','website'
  `related_id`   INT UNSIGNED NULL,
  `created_by`   INT UNSIGNED NULL,
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_rem_due` (`status`,`remind_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Webseitenverwaltung + Online/Offline-Checker
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_websites` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`         VARCHAR(190) NOT NULL,
  `url`          VARCHAR(255) NOT NULL,
  `customer_id`  INT UNSIGNED NULL,
  `active`       TINYINT(1) NOT NULL DEFAULT 1,
  `notify`       TINYINT(1) NOT NULL DEFAULT 1,     -- bei Statuswechsel benachrichtigen
  `last_status`  ENUM('up','down','unknown') NOT NULL DEFAULT 'unknown',
  `last_code`    INT NULL,
  `last_ms`      INT NULL,
  `last_checked` DATETIME NULL,
  `last_error`   VARCHAR(255) NULL,
  `notes`        TEXT NULL,
  `gsc_property` VARCHAR(255) NULL,                 -- Google-Search-Console-Property
  `bing_site`    VARCHAR(255) NULL,                 -- Bing-Webmaster-Site
  `tracking_url`  VARCHAR(255) NULL,                -- Basis-URL ArNi-Tracking-Plugin (optional, sonst aus url abgeleitet)
  `tracking_key`  VARCHAR(190) NULL,                -- API-Key ArNi-Tracking-Plugin
  `tracking_synced` DATETIME NULL,
  `favicon_data`    MEDIUMTEXT NULL,                -- Favicon als data-URI
  `favicon_hash`    VARCHAR(32) NULL,               -- md5 der Favicon-Bytes (Änderungserkennung)
  `favicon_checked` DATETIME NULL,
  `screenshot_data` MEDIUMTEXT NULL,                -- Vorschau-Screenshot als data-URI
  `screenshot_at`   DATETIME NULL,
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_web_active` (`active`),
  KEY `idx_web_status` (`last_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_website_checks` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `website_id` INT UNSIGNED NOT NULL,
  `status`     ENUM('up','down') NOT NULL,
  `code`       INT NULL,
  `ms`         INT NULL,
  `error`      VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_check_site` (`website_id`,`created_at`),
  CONSTRAINT `fk_check_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Projekt-Dokumentation (Docs + Versionierung + Datei-Anhänge)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_doc_projects` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(190) NOT NULL,
  `slug`        VARCHAR(190) NOT NULL,
  `description` TEXT NULL,
  `customer_id` INT UNSIGNED NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_docproj_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_docs` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id`      INT UNSIGNED NOT NULL,
  `title`           VARCHAR(190) NOT NULL,
  `content`         LONGTEXT NULL,            -- aktueller Stand (Markdown/Text)
  `format`          ENUM('markdown','text','html') NOT NULL DEFAULT 'markdown',
  `current_version` INT NOT NULL DEFAULT 1,
  `created_by`      INT UNSIGNED NULL,
  `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_doc_project` (`project_id`),
  CONSTRAINT `fk_doc_project` FOREIGN KEY (`project_id`) REFERENCES `crm_doc_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_doc_versions` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `doc_id`     INT UNSIGNED NOT NULL,
  `version_no` INT NOT NULL,
  `content`    LONGTEXT NULL,
  `note`       VARCHAR(255) NULL,            -- Commit-Nachricht
  `author`     VARCHAR(120) NULL,
  `source`     VARCHAR(40) NOT NULL DEFAULT 'web', -- web|cli|ai
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ver_doc` (`doc_id`,`version_no`),
  CONSTRAINT `fk_ver_doc` FOREIGN KEY (`doc_id`) REFERENCES `crm_docs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_doc_files` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `project_id`    INT UNSIGNED NULL,
  `doc_id`        INT UNSIGNED NULL,
  `original_name` VARCHAR(255) NOT NULL,
  `stored_name`   VARCHAR(255) NOT NULL,
  `mime`          VARCHAR(120) NULL,
  `size`          INT UNSIGNED NULL,
  `is_text`       TINYINT(1) NOT NULL DEFAULT 0,   -- Code/Text → Vorschau möglich
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_docfile_project` (`project_id`),
  KEY `idx_docfile_doc` (`doc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Prompt-Vorlagen (Kategorien hierarchisch, Tags, Favoriten)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_prompt_categories` (
  `id`        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`      VARCHAR(120) NOT NULL,
  `parent_id` INT UNSIGNED NULL,
  `color`     VARCHAR(20) NULL,
  `sort`      INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_cat_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_prompts` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT UNSIGNED NULL,
  `title`       VARCHAR(190) NOT NULL,
  `body`        LONGTEXT NOT NULL,
  `type`        ENUM('image','web','text','other') NOT NULL DEFAULT 'text',
  `tags`        VARCHAR(255) NULL,
  `is_favorite` TINYINT(1) NOT NULL DEFAULT 0,
  `usage_count` INT NOT NULL DEFAULT 0,
  `notes`       TEXT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_prompt_cat` (`category_id`),
  KEY `idx_prompt_fav` (`is_favorite`),
  KEY `idx_prompt_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;