-- =====================================================================
--  ArNi Solutions CRM – Interner On-Page-SEO-Scanner
--  Prefix crm_ (geteilte DB) · utf8mb4 · InnoDB
-- =====================================================================
SET NAMES utf8mb4;

-- Ein Scan-Lauf je Webseite (Verlauf bleibt erhalten; Anzeige = letzter „done").
CREATE TABLE IF NOT EXISTS `crm_website_seo_scans` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `website_id`      INT UNSIGNED NULL,
  `started_at`      DATETIME NULL,
  `finished_at`     DATETIME NULL,
  `status`          ENUM('running','done','error') NOT NULL DEFAULT 'running',
  `score`           TINYINT UNSIGNED NULL,
  `pages_checked`   INT NOT NULL DEFAULT 0,
  `problems_count`  INT NOT NULL DEFAULT 0,
  `warnings_count`  INT NOT NULL DEFAULT 0,
  `strengths_count` INT NOT NULL DEFAULT 0,
  `raw_summary_json` MEDIUMTEXT NULL,
  `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_scan_site` (`website_id`,`status`,`id`),
  CONSTRAINT `fk_seoscan_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Einzelne Funde eines Scans (Problem/Warnung/Hinweis/Stärke).
CREATE TABLE IF NOT EXISTS `crm_website_seo_scan_items` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `scan_id`        INT UNSIGNED NOT NULL,
  `website_id`     INT UNSIGNED NULL,
  `url`            VARCHAR(700) NULL,
  `category`       VARCHAR(32) NOT NULL,   -- Technik|Content|Struktur|Performance|Links|Indexierung|Social
  `type`           VARCHAR(16) NOT NULL,   -- Problem|Warnung|Hinweis|Stärke
  `priority`       VARCHAR(16) NOT NULL DEFAULT 'Mittel', -- Hoch|Mittel|Niedrig
  `title`          VARCHAR(255) NOT NULL,
  `description`    TEXT NULL,
  `recommendation` TEXT NULL,
  `status`         VARCHAR(16) NOT NULL DEFAULT 'Offen',  -- Offen|Behoben|Ignoriert
  `data_json`      TEXT NULL,
  `created_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_item_scan` (`scan_id`),
  KEY `idx_item_site` (`website_id`),
  CONSTRAINT `fk_seoitem_scan` FOREIGN KEY (`scan_id`) REFERENCES `crm_website_seo_scans`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;