-- Admin Panel Tables
-- Run after 001_initial.sql

-- Admin Users
CREATE TABLE IF NOT EXISTS admin_users (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email         TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  name          TEXT,
  created_at    TIMESTAMPTZ DEFAULT now()
);

-- Click Events
CREATE TABLE IF NOT EXISTS click_events (
  id              BIGSERIAL PRIMARY KEY,
  click_ref       TEXT UNIQUE NOT NULL,
  product_id      UUID NOT NULL,
  shop_id         UUID NOT NULL,
  offer_id        UUID,
  session_id      TEXT,
  source          TEXT NOT NULL,
  source_detail   TEXT,
  position        SMALLINT,
  user_agent      TEXT,
  ip_hash         TEXT,
  referrer        TEXT,
  utm_source      TEXT,
  utm_medium      TEXT,
  utm_campaign    TEXT,
  created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_click_events_created ON click_events (created_at);
CREATE INDEX IF NOT EXISTS idx_click_events_product ON click_events (product_id, created_at);
CREATE INDEX IF NOT EXISTS idx_click_events_shop ON click_events (shop_id, created_at);
CREATE INDEX IF NOT EXISTS idx_click_events_ref ON click_events (click_ref);
CREATE INDEX IF NOT EXISTS idx_click_events_source ON click_events (source, created_at);

-- 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)
);

-- Seed default admin user (admin@sneakerpicks.nl / admin)
-- bcrypt hash of 'admin'
INSERT INTO admin_users (email, password_hash, name)
VALUES ('admin@sneakerpicks.nl', '$2a$10$rQEY0tKjQ8GaoHmx9PvPNOQXdj5K5G5G5G5G5G5G5G5G5G5G5G5G5', 'Admin')
ON CONFLICT (email) DO NOTHING;
