`database/migrations/003-affiliates.sql`

35 lines
-- Migration 003: Affiliate System
CREATE TABLE IF NOT EXISTS affiliate_programs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL UNIQUE,
    base_url VARCHAR(500) NOT NULL,
    tracking_param VARCHAR(50) DEFAULT 'ref',
    commission_rate NUMERIC(5,2),
    logo_url VARCHAR(500),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS affiliate_clicks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id),
    watch_id UUID REFERENCES watches(id),
    program_id UUID NOT NULL REFERENCES affiliate_programs(id),
    clicked_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    ip_address INET,
    user_agent TEXT,
    converted BOOLEAN DEFAULT false,
    commission NUMERIC(10,2)
);

CREATE INDEX idx_clicks_program ON affiliate_clicks(program_id);
CREATE INDEX idx_clicks_user ON affiliate_clicks(user_id);
CREATE INDEX idx_clicks_date ON affiliate_clicks(clicked_at);

INSERT INTO affiliate_programs (name, base_url, tracking_param, commission_rate) VALUES
('Chrono24', 'https://www.chrono24.com', 'ref', 2.5),
('eBay', 'https://www.ebay.com', 'campid', 4.0),
('Jomashop', 'https://www.jomashop.com', 'aid', 3.0),
('Bobs Watches', 'https://www.bobswatches.com', 'ref', 2.0)
ON CONFLICT (name) DO NOTHING;