-- ============================================================
-- Migracja 011: Etap 9 - analityka i KPI konwersji (Pakiet 4)
-- Tartak Dana CMS
-- Kompatybilnosc: MySQL 8.0
-- ============================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `analytics_events` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `event_name`    VARCHAR(64)     NOT NULL,
    `event_group`   VARCHAR(32)     NOT NULL DEFAULT '',
    `page_path`     VARCHAR(255)    NOT NULL DEFAULT '/',
    `page_type`     VARCHAR(32)     NOT NULL DEFAULT '',
    `target_type`   VARCHAR(32)     NOT NULL DEFAULT '',
    `target_value`  VARCHAR(190)    NOT NULL DEFAULT '',
    `lead_type`     VARCHAR(32)     NOT NULL DEFAULT '',
    `source`        VARCHAR(32)     NOT NULL DEFAULT '',
    `session_hash`  CHAR(64)        NOT NULL DEFAULT '',
    `ip_hash`       CHAR(64)        NOT NULL DEFAULT '',
    `user_agent`    VARCHAR(255)    NOT NULL DEFAULT '',
    `payload_json`  TEXT            NULL,
    `occurred_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_analytics_occurred` (`occurred_at`),
    INDEX `idx_analytics_event_time` (`event_name`, `occurred_at`),
    INDEX `idx_analytics_page_event_time` (`page_path`, `event_name`, `occurred_at`),
    INDEX `idx_analytics_page_type_time` (`page_type`, `occurred_at`),
    INDEX `idx_analytics_session_time` (`session_hash`, `occurred_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
