-- =============================================================================
-- 013 — Site analytics: who visits the marketing site (landing, login, live demo).
--   * site_visitors: one row per browser (random key kept in localStorage), first-touch source,
--     location, device, and the account once they log in.
--   * site_pageviews: every page view with time on page.
--   * site_events: CTA clicks (WhatsApp, email, phone, live demo, login).
-- Privacy: the raw IP is never stored — only a masked IP (last part zeroed) and a salted hash.
-- =============================================================================

CREATE TABLE site_visitors (
  id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  visitor_key      CHAR(32)     NOT NULL,
  first_seen_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sessions_count   INT UNSIGNED NOT NULL DEFAULT 1,
  pageviews_count  INT UNSIGNED NOT NULL DEFAULT 0,
  last_session_key CHAR(32)     NULL,
  first_path       VARCHAR(500) NULL,
  first_referrer   VARCHAR(500) NULL,
  first_source     VARCHAR(60)  NULL,          -- google, x, linkedin, whatsapp, instagram, facebook, direct, <domain>
  utm_source       VARCHAR(150) NULL,
  utm_medium       VARCHAR(150) NULL,
  utm_campaign     VARCHAR(150) NULL,
  country_code     CHAR(2)      NULL,
  country          VARCHAR(80)  NULL,
  region           VARCHAR(120) NULL,
  city             VARCHAR(120) NULL,
  device_type      ENUM('desktop','mobile','tablet','other') NOT NULL DEFAULT 'other',
  browser          VARCHAR(60)  NULL,
  os               VARCHAR(60)  NULL,
  language         VARCHAR(35)  NULL,
  timezone         VARCHAR(64)  NULL,
  ip_masked        VARCHAR(45)  NULL,
  ip_hash          CHAR(64)     NULL,
  user_id          INT UNSIGNED NULL,          -- set when the visitor logs in
  restaurant_id    INT UNSIGNED NULL,
  contact_name     VARCHAR(150) NULL,
  contact_email    VARCHAR(190) NULL,
  identified_at    DATETIME     NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_site_visitors_key (visitor_key),
  KEY idx_site_visitors_last_seen (last_seen_at),
  KEY idx_site_visitors_user (user_id),
  KEY idx_site_visitors_country (country_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE site_pageviews (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  visitor_id   INT UNSIGNED    NOT NULL,
  session_key  CHAR(32)        NOT NULL,
  path         VARCHAR(500)    NOT NULL,
  title        VARCHAR(200)    NULL,
  referrer     VARCHAR(500)    NULL,
  source       VARCHAR(60)     NULL,
  utm_source   VARCHAR(150)    NULL,
  utm_medium   VARCHAR(150)    NULL,
  utm_campaign VARCHAR(150)    NULL,
  country_code CHAR(2)         NULL,
  country      VARCHAR(80)     NULL,
  region       VARCHAR(120)    NULL,
  city         VARCHAR(120)    NULL,
  device_type  ENUM('desktop','mobile','tablet','other') NOT NULL DEFAULT 'other',
  browser      VARCHAR(60)     NULL,
  os           VARCHAR(60)     NULL,
  screen_w     SMALLINT UNSIGNED NULL,
  screen_h     SMALLINT UNSIGNED NULL,
  language     VARCHAR(35)     NULL,
  timezone     VARCHAR(64)     NULL,
  user_id      INT UNSIGNED    NULL,
  duration_ms  INT UNSIGNED    NULL,           -- filled when the visitor leaves the page
  created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_site_pageviews_visitor (visitor_id, created_at),
  KEY idx_site_pageviews_created (created_at),
  CONSTRAINT fk_site_pageviews_visitor FOREIGN KEY (visitor_id) REFERENCES site_visitors (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE site_events (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  visitor_id  INT UNSIGNED    NOT NULL,
  session_key CHAR(32)        NOT NULL,
  type        VARCHAR(30)     NOT NULL,        -- click
  label       VARCHAR(100)    NULL,            -- whatsapp, email, phone, demo, login, …
  target      VARCHAR(500)    NULL,
  path        VARCHAR(500)    NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_site_events_created (created_at),
  KEY idx_site_events_visitor (visitor_id),
  CONSTRAINT fk_site_events_visitor FOREIGN KEY (visitor_id) REFERENCES site_visitors (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
