-- Anonymous first-party business analytics and verified payment attribution.
-- Exact network addresses are deliberately excluded from this schema.

CREATE TABLE IF NOT EXISTS analytics_sessions (
  session_id       UUID PRIMARY KEY,
  first_seen_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_seen_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  page_views       INTEGER NOT NULL DEFAULT 0 CHECK (page_views >= 0),
  engaged          BOOLEAN NOT NULL DEFAULT FALSE,
  landing_path     TEXT,
  referrer_host    TEXT,
  source           TEXT,
  device_type      TEXT,
  browser          TEXT,
  operating_system TEXT,
  device_model     TEXT,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_analytics_sessions_first_seen
  ON analytics_sessions(first_seen_at DESC);
CREATE INDEX IF NOT EXISTS idx_analytics_sessions_last_seen
  ON analytics_sessions(last_seen_at DESC);

CREATE TABLE IF NOT EXISTS analytics_events (
  event_id     UUID PRIMARY KEY,
  session_id   UUID NOT NULL REFERENCES analytics_sessions(session_id) ON DELETE CASCADE,
  event_name   TEXT NOT NULL CHECK (event_name IN (
    'session_start', 'page_view', 'engaged', 'order_started',
    'design_started', 'checkout_started', 'payment_succeeded'
  )),
  path         TEXT,
  occurred_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at   TIMESTAMPTZ NOT NULL DEFAULT (NOW() + INTERVAL '30 days'),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_analytics_events_session_time
  ON analytics_events(session_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_analytics_events_expiry
  ON analytics_events(expires_at);

CREATE TABLE IF NOT EXISTS analytics_daily (
  analytics_date DATE PRIMARY KEY,
  sessions INTEGER NOT NULL,
  page_views INTEGER NOT NULL,
  engaged_sessions INTEGER NOT NULL,
  converted_sessions INTEGER NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE payments
  ADD COLUMN IF NOT EXISTS analytics_session_id UUID;

CREATE INDEX IF NOT EXISTS idx_payments_analytics_session
  ON payments(analytics_session_id)
  WHERE analytics_session_id IS NOT NULL;

COMMENT ON TABLE analytics_sessions IS
  'Anonymous 30-minute first-party business sessions; contains no IP address.';
COMMENT ON TABLE analytics_events IS
  'Allow-listed anonymous journey events retained for 30 days.';
COMMENT ON TABLE analytics_daily IS
  'Non-identifying daily business aggregates retained after session-detail expiry.';
COMMENT ON COLUMN payments.analytics_session_id IS
  'Anonymous browser session attributed through Stripe metadata and a verified webhook.';
