-- 049_analytics_rollups.sql
--
-- Pre-aggregated tables that Analytics reads INSTEAD of raw events.
--
-- `audit.activity_log` was sized at ~3M rows/day at 1,000 stores. An analytics
-- page that runs GROUP BY over that on every load becomes the slowest thing in
-- the product within a month — and it does that scan on the same disk serving
-- checkout. So the page never touches raw events; a cron folds them down once.
--
-- Three tables, two audiences:
--   * activity_rollup_daily / product_stats_daily → the MERCHANT's analytics
--   * merchant_stats_daily                        → the PLATFORM admin's
--
-- All three are DERIVED and disposable: they can be rebuilt from the source
-- tables within their retention window, so a bug in a rollup job is never a
-- data-loss event. They are also kept LONGER than their sources on purpose —
-- 13 months, so year-on-year still works after raw activity is purged at 90
-- days. That is what lets raw retention stay short.

BEGIN;

-- ─────────────────────────────────────────────────────────────────────────────
-- Merchant: event counts per day
-- ─────────────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS audit.activity_rollup_daily (
  "storeId"      TEXT NOT NULL,
  "day"          DATE NOT NULL,
  "eventName"    TEXT NOT NULL,
  "eventCount"   INTEGER NOT NULL DEFAULT 0,
  -- Distinct sessions, not just events: 40 pageviews from one visitor is a
  -- very different fact from 40 visitors, and a funnel built on event counts
  -- reports conversion rates above 100%.
  "sessionCount" INTEGER NOT NULL DEFAULT 0,
  "visitorCount" INTEGER NOT NULL DEFAULT 0,
  "updatedAt"    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ("storeId", "day", "eventName")
);

CREATE INDEX IF NOT EXISTS "activity_rollup_store_day_idx"
  ON audit.activity_rollup_daily ("storeId", "day" DESC);

-- ─────────────────────────────────────────────────────────────────────────────
-- Merchant: per-product funnel
-- ─────────────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS audit.product_stats_daily (
  "storeId"     TEXT NOT NULL,
  "day"         DATE NOT NULL,
  "productId"   TEXT NOT NULL,
  "views"       INTEGER NOT NULL DEFAULT 0,
  "addToCarts"  INTEGER NOT NULL DEFAULT 0,
  "purchases"   INTEGER NOT NULL DEFAULT 0,
  -- Paise, and taken from the ORDER table rather than from a client event, so
  -- it reconciles with the merchant's books. This is the number GA can never
  -- produce: its "purchase" is a browser event that cannot see a later refund.
  "revenue"     BIGINT  NOT NULL DEFAULT 0,
  "updatedAt"   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ("storeId", "day", "productId")
);

CREATE INDEX IF NOT EXISTS "product_stats_store_day_idx"
  ON audit.product_stats_daily ("storeId", "day" DESC);
-- Powers "most viewed but not selling", which is the whole point of the table.
CREATE INDEX IF NOT EXISTS "product_stats_store_views_idx"
  ON audit.product_stats_daily ("storeId", "views" DESC);

-- ─────────────────────────────────────────────────────────────────────────────
-- Merchant: searches, including the ones that found nothing
-- ─────────────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS audit.search_rollup_daily (
  "storeId"     TEXT NOT NULL,
  "day"         DATE NOT NULL,
  "term"        TEXT NOT NULL,
  "searchCount" INTEGER NOT NULL DEFAULT 0,
  -- How many of those searches returned nothing. A term with a high count here
  -- is demand the merchant is failing to meet, and nothing else in the product
  -- surfaces it.
  "zeroResults" INTEGER NOT NULL DEFAULT 0,
  "updatedAt"   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ("storeId", "day", "term")
);

CREATE INDEX IF NOT EXISTS "search_rollup_store_zero_idx"
  ON audit.search_rollup_daily ("storeId", "zeroResults" DESC);

-- ─────────────────────────────────────────────────────────────────────────────
-- Platform: one row per store per day — the business of the platform
-- ─────────────────────────────────────────────────────────────────────────────
--
-- Deliberately in the `platform` schema, not `audit`: this is the platform's
-- own operating data, and a merchant must never be able to read it.

CREATE TABLE IF NOT EXISTS platform.merchant_stats_daily (
  "storeId"        TEXT NOT NULL,
  "organizationId" TEXT,
  "day"            DATE NOT NULL,
  -- Commercial
  "orders"         INTEGER NOT NULL DEFAULT 0,
  "gmv"            BIGINT  NOT NULL DEFAULT 0,
  -- Engagement
  "sessions"       INTEGER NOT NULL DEFAULT 0,
  "adminActions"   INTEGER NOT NULL DEFAULT 0,
  -- Cost to serve. Almost no platform can compute this per tenant; we can,
  -- because the audit layer already records it per store. It answers which
  -- merchants are unprofitable and which plan tiers are mispriced.
  "apiCalls"       INTEGER NOT NULL DEFAULT 0,
  "apiErrors"      INTEGER NOT NULL DEFAULT 0,
  "storageBytes"   BIGINT  NOT NULL DEFAULT 0,
  "auditRows"      INTEGER NOT NULL DEFAULT 0,
  "updatedAt"      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ("storeId", "day")
);

CREATE INDEX IF NOT EXISTS "merchant_stats_day_idx"
  ON platform.merchant_stats_daily ("day" DESC);
CREATE INDEX IF NOT EXISTS "merchant_stats_gmv_idx"
  ON platform.merchant_stats_daily ("day" DESC, "gmv" DESC);
-- Finds a merchant having a bad day before they complain.
CREATE INDEX IF NOT EXISTS "merchant_stats_errors_idx"
  ON platform.merchant_stats_daily ("day" DESC, "apiErrors" DESC)
  WHERE "apiErrors" > 0;

COMMIT;