-- Feed Management System Tables
-- Run: psql $DATABASE_URL -f db/migrations/001_feed_tables.sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- =============================================================================
-- providers — Affiliate network registry
-- =============================================================================
CREATE TABLE IF NOT EXISTS providers (
    id              SERIAL PRIMARY KEY,
    slug            VARCHAR(50) UNIQUE NOT NULL,
    name            VARCHAR(100) NOT NULL,
    adapter_type    VARCHAR(50) NOT NULL,
    config          JSONB NOT NULL DEFAULT '{}',
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO providers (slug, name, adapter_type, config) VALUES
('webgains', 'Webgains', 'webgains', '{"api_url": "https://platform-api.webgains.com"}'),
('tradetracker', 'TradeTracker', 'tradetracker', '{"wsdl_url": "https://ws.tradetracker.com/soap/affiliate?wsdl", "customer_id": 35058, "locale": "nl_NL"}'),
('awin', 'Awin', 'awin', '{"api_url": "https://api.awin.com"}')
ON CONFLICT (slug) DO NOTHING;

-- =============================================================================
-- feeds — Individual product feeds
-- =============================================================================
CREATE TABLE IF NOT EXISTS feeds (
    id              SERIAL PRIMARY KEY,
    provider_id     INTEGER NOT NULL REFERENCES providers(id),
    external_id     VARCHAR(255),
    name            VARCHAR(255) NOT NULL,
    merchant_name   VARCHAR(255),
    feed_url        TEXT,
    sync_frequency  VARCHAR(10) NOT NULL DEFAULT '4h',
    is_enabled      BOOLEAN NOT NULL DEFAULT true,
    priority        INTEGER NOT NULL DEFAULT 0,
    config          JSONB NOT NULL DEFAULT '{}',
    product_count   INTEGER NOT NULL DEFAULT 0,
    last_sync_at    TIMESTAMPTZ,
    last_sync_status VARCHAR(20),
    last_error      TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_feeds_provider ON feeds(provider_id);
CREATE INDEX IF NOT EXISTS idx_feeds_enabled ON feeds(is_enabled) WHERE is_enabled = true;
CREATE INDEX IF NOT EXISTS idx_feeds_next_sync ON feeds(sync_frequency, last_sync_at);

-- =============================================================================
-- field_mappings — Configurable field mapping per feed/provider
-- =============================================================================
CREATE TABLE IF NOT EXISTS field_mappings (
    id              SERIAL PRIMARY KEY,
    provider_id     INTEGER REFERENCES providers(id),
    feed_id         INTEGER REFERENCES feeds(id),
    source_field    VARCHAR(255) NOT NULL,
    target_field    VARCHAR(100) NOT NULL,
    transform       VARCHAR(50),
    transform_args  JSONB,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(provider_id, feed_id, source_field)
);

CREATE INDEX IF NOT EXISTS idx_field_mappings_provider ON field_mappings(provider_id);
CREATE INDEX IF NOT EXISTS idx_field_mappings_feed ON field_mappings(feed_id);

-- =============================================================================
-- sync_logs — Audit trail for every sync run
-- =============================================================================
CREATE TABLE IF NOT EXISTS sync_logs (
    id                  SERIAL PRIMARY KEY,
    feed_id             INTEGER NOT NULL REFERENCES feeds(id),
    started_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    finished_at         TIMESTAMPTZ,
    status              VARCHAR(20) NOT NULL DEFAULT 'running',
    products_fetched    INTEGER DEFAULT 0,
    products_new        INTEGER DEFAULT 0,
    products_updated    INTEGER DEFAULT 0,
    products_removed    INTEGER DEFAULT 0,
    products_matched    INTEGER DEFAULT 0,
    errors_count        INTEGER DEFAULT 0,
    error_details       JSONB,
    metadata            JSONB
);

CREATE INDEX IF NOT EXISTS idx_sync_logs_feed ON sync_logs(feed_id, started_at DESC);
CREATE INDEX IF NOT EXISTS idx_sync_logs_status ON sync_logs(status) WHERE status = 'failed';

-- =============================================================================
-- product_offers — Per-feed pricing (price comparison core)
-- =============================================================================
CREATE TABLE IF NOT EXISTS product_offers (
    id              SERIAL PRIMARY KEY,
    product_id      INTEGER NOT NULL REFERENCES products(id),
    feed_id         INTEGER NOT NULL REFERENCES feeds(id),
    external_id     VARCHAR(255),
    price           DECIMAL(10,2) NOT NULL,
    original_price  DECIMAL(10,2),
    currency        VARCHAR(3) NOT NULL DEFAULT 'EUR',
    product_url     TEXT NOT NULL,
    image_url       TEXT,
    in_stock        BOOLEAN DEFAULT true,
    last_seen_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(feed_id, external_id)
);

CREATE INDEX IF NOT EXISTS idx_offers_product ON product_offers(product_id);
CREATE INDEX IF NOT EXISTS idx_offers_feed ON product_offers(feed_id);
CREATE INDEX IF NOT EXISTS idx_offers_stale ON product_offers(last_seen_at);

-- =============================================================================
-- Additions to existing products table
-- =============================================================================
ALTER TABLE products ADD COLUMN IF NOT EXISTS ean VARCHAR(20);
ALTER TABLE products ADD COLUMN IF NOT EXISTS gtin VARCHAR(20);
ALTER TABLE products ADD COLUMN IF NOT EXISTS brand_normalized VARCHAR(100);
ALTER TABLE products ADD COLUMN IF NOT EXISTS model_normalized VARCHAR(255);
ALTER TABLE products ADD COLUMN IF NOT EXISTS colorway_normalized VARCHAR(100);
ALTER TABLE products ADD COLUMN IF NOT EXISTS match_fingerprint VARCHAR(64);

CREATE INDEX IF NOT EXISTS idx_products_ean ON products(ean) WHERE ean IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_products_gtin ON products(gtin) WHERE gtin IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_products_fingerprint ON products(match_fingerprint);
CREATE INDEX IF NOT EXISTS idx_products_brand_model ON products(brand_normalized, model_normalized);

-- =============================================================================
-- Default field mappings for Webgains
-- =============================================================================
INSERT INTO field_mappings (provider_id, feed_id, source_field, target_field, transform) VALUES
((SELECT id FROM providers WHERE slug='webgains'), NULL, 'title', 'name', NULL),
((SELECT id FROM providers WHERE slug='webgains'), NULL, 'brand', 'brand', NULL),
((SELECT id FROM providers WHERE slug='webgains'), NULL, 'model_number', 'model', NULL),
((SELECT id FROM providers WHERE slug='webgains'), NULL, 'colour', 'colorway', 'lowercase'),
((SELECT id FROM providers WHERE slug='webgains'), NULL, 'ean', 'ean', 'strip_whitespace'),
((SELECT id FROM providers WHERE slug='webgains'), NULL, 'price', 'price', 'parse_price'),
((SELECT id FROM providers WHERE slug='webgains'), NULL, 'rrp', 'original_price', 'parse_price'),
((SELECT id FROM providers WHERE slug='webgains'), NULL, 'currency', 'currency', 'uppercase'),
((SELECT id FROM providers WHERE slug='webgains'), NULL, 'link', 'product_url', NULL),
((SELECT id FROM providers WHERE slug='webgains'), NULL, 'image_link', 'image_url', NULL),
((SELECT id FROM providers WHERE slug='webgains'), NULL, 'description', 'description', 'strip_html'),
((SELECT id FROM providers WHERE slug='webgains'), NULL, 'category_name', 'category', NULL),
((SELECT id FROM providers WHERE slug='webgains'), NULL, 'in_stock', 'in_stock', 'parse_boolean')
ON CONFLICT DO NOTHING;

-- Default field mappings for TradeTracker
INSERT INTO field_mappings (provider_id, feed_id, source_field, target_field, transform) VALUES
((SELECT id FROM providers WHERE slug='tradetracker'), NULL, 'name', 'name', NULL),
((SELECT id FROM providers WHERE slug='tradetracker'), NULL, 'additional_brand', 'brand', NULL),
((SELECT id FROM providers WHERE slug='tradetracker'), NULL, 'additional_EAN', 'ean', 'strip_whitespace'),
((SELECT id FROM providers WHERE slug='tradetracker'), NULL, 'additional_color', 'colorway', 'lowercase'),
((SELECT id FROM providers WHERE slug='tradetracker'), NULL, 'additional_size', 'size', NULL),
((SELECT id FROM providers WHERE slug='tradetracker'), NULL, 'price', 'price', 'parse_price'),
((SELECT id FROM providers WHERE slug='tradetracker'), NULL, 'additional_fromPrice', 'original_price', 'parse_price'),
((SELECT id FROM providers WHERE slug='tradetracker'), NULL, 'productURL', 'product_url', NULL),
((SELECT id FROM providers WHERE slug='tradetracker'), NULL, 'imageURL', 'image_url', NULL),
((SELECT id FROM providers WHERE slug='tradetracker'), NULL, 'description', 'description', 'strip_html'),
((SELECT id FROM providers WHERE slug='tradetracker'), NULL, 'productCategoryName', 'category', NULL)
ON CONFLICT DO NOTHING;

-- Default field mappings for Awin
INSERT INTO field_mappings (provider_id, feed_id, source_field, target_field, transform) VALUES
((SELECT id FROM providers WHERE slug='awin'), NULL, 'product_name', 'name', NULL),
((SELECT id FROM providers WHERE slug='awin'), NULL, 'brand_name', 'brand', NULL),
((SELECT id FROM providers WHERE slug='awin'), NULL, 'model_number', 'model', NULL),
((SELECT id FROM providers WHERE slug='awin'), NULL, 'colour', 'colorway', 'lowercase'),
((SELECT id FROM providers WHERE slug='awin'), NULL, 'ean', 'ean', 'strip_whitespace'),
((SELECT id FROM providers WHERE slug='awin'), NULL, 'search_price', 'price', 'parse_price'),
((SELECT id FROM providers WHERE slug='awin'), NULL, 'rrp_price', 'original_price', 'parse_price'),
((SELECT id FROM providers WHERE slug='awin'), NULL, 'currency', 'currency', 'uppercase'),
((SELECT id FROM providers WHERE slug='awin'), NULL, 'aw_deep_link', 'product_url', NULL),
((SELECT id FROM providers WHERE slug='awin'), NULL, 'aw_image_url', 'image_url', NULL),
((SELECT id FROM providers WHERE slug='awin'), NULL, 'description', 'description', 'strip_html'),
((SELECT id FROM providers WHERE slug='awin'), NULL, 'merchant_category', 'category', NULL),
((SELECT id FROM providers WHERE slug='awin'), NULL, 'in_stock', 'in_stock', 'parse_boolean'),
((SELECT id FROM providers WHERE slug='awin'), NULL, 'aw_product_id', 'sku', NULL)
ON CONFLICT DO NOTHING;
