-- =====================================================================
--  ArNi Solutions CRM – Suchmaschinen-Modul (Google Search Console / Bing)
--  Prefix crm_ (geteilte DB) · utf8mb4 · InnoDB
-- =====================================================================
SET NAMES utf8mb4;

-- Suchmaschinen-Kennzahlen je Webseite/Quelle/Tag/Dimension.
--  dimension='total'      → Tagessumme (dim_value NULL)  – Verlauf/KPIs
--  dimension='query'      → Suchanfrage
--  dimension='page'       → Seite/URL
--  dimension='country'    → Land
--  dimension='device'     → Gerät
--  dimension='appearance' → Darstellung in der Suche (searchAppearance)
CREATE TABLE IF NOT EXISTS `crm_search_metrics` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `website_id`  INT UNSIGNED NOT NULL,
  `source`      ENUM('gsc','bing') NOT NULL,
  `dimension`   ENUM('total','query','page','country','device','appearance') NOT NULL DEFAULT 'total',
  `dim_value`   VARCHAR(512) NULL,
  `date`        DATE NOT NULL,
  `clicks`      INT NOT NULL DEFAULT 0,
  `impressions` INT NOT NULL DEFAULT 0,
  `ctr`         DECIMAL(7,4) NOT NULL DEFAULT 0.0000,   -- Prozent
  `position`    DECIMAL(7,2) NOT NULL DEFAULT 0.00,      -- Durchschnittsposition
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_sm_site_src_date` (`website_id`,`source`,`date`),
  KEY `idx_sm_dim` (`website_id`,`source`,`dimension`,`date`),
  CONSTRAINT `fk_sm_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Property-/Site-Zuordnung je Webseite (für GSC & Bing).
ALTER TABLE `crm_websites`
  ADD COLUMN IF NOT EXISTS `gsc_property` VARCHAR(255) NULL AFTER `notes`,
  ADD COLUMN IF NOT EXISTS `bing_site`    VARCHAR(255) NULL AFTER `gsc_property`;