-- Migration: Add benefits tables for member affiliate offers
-- Date: 2026-02-02
-- Author: Claude

-- Create benefits table
CREATE TABLE IF NOT EXISTS benefits (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    short_description VARCHAR(200),
    description TEXT,
    category VARCHAR(50),
    regular_price VARCHAR(50),
    member_price VARCHAR(50),
    discount_description VARCHAR(100),
    affiliate_url VARCHAR(500),
    product_url VARCHAR(500),
    logo_url VARCHAR(500),
    promo_code VARCHAR(50),
    promo_code_instructions TEXT,
    commission_rate VARCHAR(50),
    commission_duration VARCHAR(50),
    partner_platform VARCHAR(100),
    partner_since DATE,
    is_featured BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    display_order INTEGER DEFAULT 0,
    click_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create benefit_clicks table for tracking
CREATE TABLE IF NOT EXISTS benefit_clicks (
    id SERIAL PRIMARY KEY,
    benefit_id INTEGER NOT NULL REFERENCES benefits(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    clicked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45),
    user_agent VARCHAR(500)
);

-- Create index for benefit_clicks
CREATE INDEX IF NOT EXISTS idx_benefit_clicks_benefit_id ON benefit_clicks(benefit_id);
CREATE INDEX IF NOT EXISTS idx_benefit_clicks_clicked_at ON benefit_clicks(clicked_at);

-- Grant permissions
GRANT ALL ON TABLE benefits TO nordabiz_app;
GRANT ALL ON TABLE benefit_clicks TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE benefits_id_seq TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE benefit_clicks_id_seq TO nordabiz_app;

-- Insert WisprFlow as first benefit
INSERT INTO benefits (
    name,
    slug,
    short_description,
    description,
    category,
    regular_price,
    member_price,
    affiliate_url,
    product_url,
    commission_rate,
    commission_duration,
    partner_platform,
    partner_since,
    is_featured,
    is_active,
    display_order
) VALUES (
    'WisprFlow',
    'wispr-flow',
    'AI do transkrypcji i notatek ze spotkań. Oszczędź czas na notowaniu.',
    'WisprFlow to narzędzie AI, które automatycznie transkrybuje Twoje spotkania i tworzy inteligentne notatki. Idealne dla przedsiębiorców, którzy chcą skupić się na rozmowie zamiast na pisaniu notatek.

Funkcje:
- Automatyczna transkrypcja w czasie rzeczywistym
- Inteligentne podsumowania spotkań
- Wyciąganie kluczowych punktów i zadań
- Integracja z kalendarzem
- Wsparcie dla języka polskiego',
    'productivity',
    '$10/mies',
    '$8.50/mies (plan roczny)',
    'https://ref.wisprflow.ai/norda',
    'https://wisprflow.ai',
    '25%',
    '12 miesięcy',
    'Dub Partners',
    '2026-02-02',
    TRUE,
    TRUE,
    1
) ON CONFLICT (slug) DO NOTHING;

-- Verification
SELECT id, name, slug, is_active FROM benefits;