

-- Conversions
CREATE TABLE IF NOT EXISTS conversions (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  click_ref       TEXT,
  network         TEXT NOT NULL,
  network_id      TEXT NOT NULL,
  status          TEXT DEFAULT 'pending',
  commission      DECIMAL(10,2) NOT NULL,
  sale_amount     DECIMAL(10,2),
  currency        TEXT DEFAULT 'EUR',
  product_id      UUID,
  shop_id         UUID,
  click_time      TIMESTAMPTZ,
  conversion_time TIMESTAMPTZ NOT NULL,
  network_data    JSONB,
  matched         BOOLEAN DEFAULT false,
  created_at      TIMESTAMPTZ DEFAULT now(),
  updated_at      TIMESTAMPTZ DEFAULT now(),
  UNIQUE(network, network_id)
);

CREATE INDEX IF NOT EXISTS idx_conversions_click_ref ON conversions (click_ref);
CREATE INDEX IF NOT EXISTS idx_conversions_time ON conversions (conversion_time);
CREATE INDEX IF NOT EXISTS idx_conversions_status ON conversions (status);

-- Daily Stats (Aggregation)
CREATE TABLE IF NOT EXISTS daily_stats (
  id          BIGSERIAL PRIMARY KEY,
  date        DATE NOT NULL,
  dimension   TEXT NOT NULL,
  dimension_id TEXT,
  clicks      INTEGER DEFAULT 0,
  impressions INTEGER DEFAULT 0,
  conversions INTEGER DEFAULT 0,
  revenue     DECIMAL(10,2) DEFAULT 0,
  ad_spend    DECIMAL(10,2) DEFAULT 0,
  UNIQUE(date, dimension, dimension_id)
);

CREATE INDEX IF NOT EXISTS idx_daily_stats_date ON daily_stats (date, dimension);

-- Homepage Sections
CREATE TABLE IF NOT EXISTS homepage_sections (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type        TEXT NOT NULL,
  title       TEXT,
  config      JSONB NOT NULL DEFAULT '{}',
  sort_order  SMALLINT NOT NULL,
  enabled     BOOLEAN DEFAULT true,
  created_at  TIMESTAMPTZ DEFAULT now(),
  updated_at  TIMESTAMPTZ DEFAULT now()
);

-- Collections
CREATE TABLE IF NOT EXISTS collections (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  slug        TEXT UNIQUE NOT NULL,
  type        TEXT NOT NULL,
  rules       JSONB,
  sort_field  TEXT DEFAULT 'performance_score',
  sort_dir    TEXT DEFAULT 'desc',
  item_limit  INTEGER DEFAULT 50,
  description TEXT,
  seo_title   TEXT,
  seo_desc    TEXT,
  enabled     BOOLEAN DEFAULT true,
  created_at  TIMESTAMPTZ DEFAULT now(),
  updated_at  TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS collection_products (
  collection_id UUID REFERENCES collections(id) ON DELETE CASCADE,
  product_id    UUID NOT NULL,
  sort_order    SMALLINT,
  PRIMARY KEY (collection_id, product_id)
);
