-- Usage tracking schema for Vibery Kits
-- Run: wrangler d1 execute vibery-stats --file=migrations/0001_usage_stats.sql

-- Aggregated counts per template (fast reads)
CREATE TABLE IF NOT EXISTS usage_counts (
  template_name TEXT PRIMARY KEY,
  copies INTEGER DEFAULT 0,
  downloads INTEGER DEFAULT 0,
  carts INTEGER DEFAULT 0,
  total INTEGER GENERATED ALWAYS AS (copies + downloads + carts) STORED
);

-- Create index for sorting by popularity
CREATE INDEX IF NOT EXISTS idx_total ON usage_counts(total DESC);
