-- ============================================================================
-- 0005_surface_metrics.sql
-- ----------------------------------------------------------------------------
-- Per-surface raw metrics snapshots. Insert-only on each ingestion (no
-- UPDATE); deduplicate on read via DISTINCT ON (window).
--
-- Adapters write rows; analytics-dashboard panels read via DISTINCT ON to
-- pick the most-recently-fetched snapshot per (user, surface, kind, window).
--
-- Spec source: _engineering-surface-metrics.md §3.
-- ============================================================================

CREATE TYPE surface_metric_kind AS ENUM (
  -- Discovery funnel
  'profile_view',
  'search_impression',
  'search_rank',
  'click_through',
  -- Engagement funnel
  'dm_inbound',
  'dm_outbound',
  'reply_rate',
  -- Conversion funnel
  'subscription_new',
  'subscription_total',
  'tip_amount',
  'tip_count',
  'booking_inquiry',
  'booking_confirmed',
  -- Revenue
  'gross_revenue',
  'net_revenue'
);

CREATE TYPE surface_metric_source AS ENUM (
  'native_api',     -- surface exposed an API endpoint
  'native_scrape',  -- adapter scraped the surface's own analytics page
  'derived',        -- computed from other rows (e.g. reply_rate from dm_in/dm_out)
  'manual'          -- Quinn entered manually
);

CREATE TABLE surface_metrics (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  org_id          UUID NULL     REFERENCES orgs(id)  ON DELETE CASCADE,
  surface         TEXT NOT NULL,                          -- surface_kind value
  metric_kind     surface_metric_kind NOT NULL,
  window_start    TIMESTAMPTZ NOT NULL,
  window_end      TIMESTAMPTZ NOT NULL,
  value_numeric   NUMERIC(18,4),                          -- counts, sums, rates
  value_text      TEXT,                                   -- rank labels ("Boosted"), categorical
  currency        TEXT,                                   -- ISO 4217 when monetary
  source          surface_metric_source NOT NULL,
  fetched_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  payload_jsonb   JSONB,                                  -- raw surface-side response for audit
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

  CONSTRAINT surface_metrics_window_chk CHECK (window_end > window_start),
  CONSTRAINT surface_metrics_value_chk  CHECK (value_numeric IS NOT NULL OR value_text IS NOT NULL),
  CONSTRAINT surface_metrics_currency_chk CHECK (
    currency IS NULL
    OR (currency ~ '^[A-Z]{3}$' AND value_numeric IS NOT NULL)
  )
);

CREATE INDEX idx_surface_metrics_user_surface_kind_window
  ON surface_metrics (user_id, surface, metric_kind, window_end DESC);

CREATE INDEX idx_surface_metrics_org_surface_kind_window
  ON surface_metrics (org_id, surface, metric_kind, window_end DESC)
  WHERE org_id IS NOT NULL;

-- Dedup read: DISTINCT ON (surface, metric_kind, window_start, window_end)
-- ordered by fetched_at DESC picks the latest snapshot per window.
CREATE INDEX idx_surface_metrics_dedup
  ON surface_metrics (user_id, surface, metric_kind, window_start, window_end, fetched_at DESC);

ALTER TABLE surface_metrics ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_surface_metrics ON surface_metrics
  USING (
    user_id = current_setting('app.user_id', true)::uuid
    OR (org_id IS NOT NULL AND org_id = current_setting('app.org_id', true)::uuid)
  )
  WITH CHECK (
    user_id = current_setting('app.user_id', true)::uuid
    OR (org_id IS NOT NULL AND org_id = current_setting('app.org_id', true)::uuid)
  );

COMMENT ON TABLE surface_metrics IS
  'Per-surface metric snapshots. Insert-only; reads use DISTINCT ON to dedup overlapping windows. Tier-gated surfaces (e.g. Tryst Basic) simply contribute no rows — UI shows "—" not "0".';