"""Visitor Monitor models.

This module stores four tables powering the admin Visitor Monitor portal:

* ``campaigns`` — marketing campaigns + referral links generated from the admin.
* ``visitor_sessions`` — one row per browser session, with UTM / device / geo metadata.
* ``visitor_events`` — raw timeline events (``pageview``, ``click``, ``form_submit``,
  ``signup``, ``order``, ``conversion``) keyed by ``session_id``.
* ``tracking_settings`` — singleton privacy / retention configuration.

Design notes
------------
- ``session_id`` is a client-generated UUID persisted in ``sessionStorage``.
  It is NOT a DB primary key: we use it as an indexed lookup column so public
  tracking endpoints can upsert without a round-trip first.
- Sensitive PII (passwords, payment data) must never be persisted here; the
  ingest service strips common sensitive keys defensively.
- ``ip_address`` is stored as TEXT (max 64 chars) to accommodate IPv6; we can
  mask the last octet at read-time via the ``mask_sensitive`` setting.
"""

from __future__ import annotations

from core.database import Base
from sqlalchemy import (
    Boolean,
    Column,
    DateTime,
    Integer,
    String,
    Text,
)
from sqlalchemy.sql import func


class Campaign(Base):
    __tablename__ = "vm_campaigns"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    utm_source = Column(String(100), nullable=False, index=True)
    utm_medium = Column(String(100), nullable=True, index=True)
    utm_campaign = Column(String(100), nullable=False, index=True)
    utm_term = Column(String(100), nullable=True)
    utm_content = Column(String(100), nullable=True)
    # Landing page path inside our site, e.g. "/cloud-hosting"
    landing_page = Column(String(500), nullable=False, server_default="/")
    # Optional external destination (e.g. an affiliate page) — mainly
    # informational for the admin. We always redirect through our site.
    target_url = Column(String(1000), nullable=True)
    start_date = Column(DateTime(timezone=True), nullable=True)
    end_date = Column(DateTime(timezone=True), nullable=True)
    # "active" | "paused" | "archived"
    status = Column(String(20), nullable=False, server_default="active", index=True)
    notes = Column(Text, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False
    )


class VisitorSession(Base):
    __tablename__ = "vm_sessions"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    # Client-issued session UUID from sessionStorage; unique per browser tab
    # group. Indexed for fast upsert.
    session_id = Column(String(64), nullable=False, unique=True, index=True)
    # Best-effort visitor identifier (hash of IP + UA); stable across a
    # session but resets if the user clears cookies.
    visitor_id = Column(String(64), nullable=True, index=True)
    # Linked logged-in user id, if the visitor authenticates during the session.
    user_id = Column(String(255), nullable=True, index=True)
    ip_address = Column(String(64), nullable=True)
    country = Column(String(100), nullable=True, index=True)
    city = Column(String(150), nullable=True)
    device = Column(String(20), nullable=True, index=True)  # desktop|mobile|tablet|bot
    browser = Column(String(60), nullable=True, index=True)
    os = Column(String(60), nullable=True)
    user_agent = Column(String(512), nullable=True)
    referrer = Column(String(1000), nullable=True)
    landing_page = Column(String(500), nullable=True)
    current_page = Column(String(500), nullable=True)
    # UTM params captured on first hit of the session.
    utm_source = Column(String(100), nullable=True, index=True)
    utm_medium = Column(String(100), nullable=True, index=True)
    utm_campaign = Column(String(100), nullable=True, index=True)
    utm_term = Column(String(100), nullable=True)
    utm_content = Column(String(100), nullable=True)
    # Denormalised counters so the live list / reports stay cheap.
    pageview_count = Column(Integer, nullable=False, server_default="0")
    # "visited" | "lead" | "signup" | "order"
    conversion_stage = Column(
        String(20), nullable=False, server_default="visited", index=True
    )
    # True if the session matched a staff-exclusion rule at ingest time. We
    # keep the row (for diagnostics) but filter it out of analytics.
    is_excluded = Column(Boolean, nullable=False, server_default="0")
    started_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False, index=True)
    last_activity_at = Column(
        DateTime(timezone=True), server_default=func.now(), nullable=False, index=True
    )
    ended_at = Column(DateTime(timezone=True), nullable=True)


class VisitorEvent(Base):
    __tablename__ = "vm_events"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    session_id = Column(String(64), nullable=False, index=True)
    # "pageview" | "click" | "form_submit" | "signup" | "order" | "conversion" | "custom"
    event_type = Column(String(30), nullable=False, index=True)
    # Arbitrary short label: button name, form id, route, etc.
    label = Column(String(255), nullable=True)
    page = Column(String(500), nullable=True)
    # JSON-encoded extra payload (optional). Kept as TEXT for portability.
    metadata_json = Column(Text, nullable=True)
    created_at = Column(
        DateTime(timezone=True), server_default=func.now(), nullable=False, index=True
    )


class TrackingSetting(Base):
    """Singleton settings row (id=1). Not all fields must be filled."""

    __tablename__ = "vm_settings"

    id = Column(Integer, primary_key=True, autoincrement=True)
    # Master switch for visitor tracking ingestion.
    tracking_enabled = Column(Boolean, nullable=False, server_default="1")
    # Days to retain sessions/events before cleanup job can purge.
    retention_days = Column(Integer, nullable=False, server_default="90")
    # Comma-separated IPs / CIDRs to exclude (e.g. office IPs).
    excluded_ips = Column(Text, nullable=True)
    # Exclude logged-in admin/staff roles from tracking.
    exclude_staff = Column(Boolean, nullable=False, server_default="1")
    # Mask last octet of IPv4 / last group of IPv6 in admin views.
    mask_sensitive = Column(Boolean, nullable=False, server_default="1")
    updated_at = Column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False
    )