-- =====================================================================
--  ArNi Solutions CRM – Besucher-Tracking (ArNi-Tracking-WordPress-Plugin)
--  Pull-Prinzip: das CRM holt die Daten per API-Key je Webseite ab.
--  Prefix crm_ (geteilte DB) · utf8mb4 · InnoDB
-- =====================================================================
SET NAMES utf8mb4;

-- Tageskennzahlen je Webseite (aus GET /stats pro Tag).
CREATE TABLE IF NOT EXISTS `crm_visitor_daily` (
  `id`                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `website_id`        INT UNSIGNED NOT NULL,
  `date`              DATE NOT NULL,
  `visitors`          INT NOT NULL DEFAULT 0,
  `pageviews`         INT NOT NULL DEFAULT 0,
  `avg_duration`      DECIMAL(8,2) NOT NULL DEFAULT 0,
  `conversions`       INT NOT NULL DEFAULT 0,
  `conversion_value`  DECIMAL(12,2) NOT NULL DEFAULT 0,
  `form_abandonments` INT NOT NULL DEFAULT 0,
  `created_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_vd` (`website_id`,`date`),
  CONSTRAINT `fk_vd_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Aufschlüsselungen (Snapshot je Sync) aus /pages, /referrers, /campaigns, /stats(devices).
CREATE TABLE IF NOT EXISTS `crm_visitor_breakdown` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `website_id`  INT UNSIGNED NOT NULL,
  `dimension`   ENUM('page','referrer','campaign','device') NOT NULL,
  `label`       VARCHAR(512) NOT NULL,
  `title`       VARCHAR(255) NULL,
  `source`      VARCHAR(190) NULL,
  `medium`      VARCHAR(190) NULL,
  `hits`        INT NOT NULL DEFAULT 0,
  `captured_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_vb` (`website_id`,`dimension`,`hits`),
  CONSTRAINT `fk_vb_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Conversions (einzeln, dedupliziert über die Plugin-ID).
CREATE TABLE IF NOT EXISTS `crm_visitor_conversions` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `website_id`      INT UNSIGNED NOT NULL,
  `ext_id`          VARCHAR(40) NOT NULL,
  `conversion_type` VARCHAR(120) NULL,
  `reference_id`    VARCHAR(190) NULL,
  `value`           DECIMAL(12,2) NOT NULL DEFAULT 0,
  `currency`        VARCHAR(10) NULL,
  `meta`            TEXT NULL,
  `created_at`      DATETIME NULL,
  `imported_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_vc` (`website_id`,`ext_id`),
  KEY `idx_vc_date` (`website_id`,`created_at`),
  CONSTRAINT `fk_vc_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Spalten an crm_websites (siehe schema_modules.sql):
--   tracking_url, tracking_key, tracking_synced

-- Einzelne Sitzungen (aus GET /sessions; Mikrodaten + Verlauf als JSON).
CREATE TABLE IF NOT EXISTS `crm_visitor_sessions` (
  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `website_id`       INT UNSIGNED NOT NULL,
  `visit_id`         VARCHAR(40) NOT NULL,
  `session_hash`     VARCHAR(64) NULL,
  `started_at`       DATETIME NULL,
  `last_seen`        DATETIME NULL,
  `duration_seconds` INT NOT NULL DEFAULT 0,
  `country`          VARCHAR(8) NULL,
  `device`           VARCHAR(40) NULL,
  `user_agent`       VARCHAR(512) NULL,
  `referrer`         VARCHAR(255) NULL,
  `utm_source`       VARCHAR(190) NULL,
  `utm_medium`       VARCHAR(190) NULL,
  `utm_campaign`     VARCHAR(190) NULL,
  `entry_page`       VARCHAR(512) NULL,
  `exit_page`        VARCHAR(512) NULL,
  `pageviews`        INT NOT NULL DEFAULT 0,
  `is_bounce`        TINYINT(1) NOT NULL DEFAULT 0,
  `conv_count`       INT NOT NULL DEFAULT 0,
  `conv_value`       DECIMAL(12,2) NOT NULL DEFAULT 0,
  `pages_json`       MEDIUMTEXT NULL,
  `events_json`      MEDIUMTEXT NULL,
  `conversions_json` TEXT NULL,
  `imported_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_vs` (`website_id`,`visit_id`),
  KEY `idx_vs_started` (`website_id`,`started_at`),
  CONSTRAINT `fk_vs_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;