-- Migration 017: Product Verification Tags
-- "Verified Price" and "100% Verified" stamp system
--
-- VERIFIED PRICE: Price scraped from official vendor URL, observed within 30 days
-- 100% VERIFIED:  Verified Price + product image from official product page + details from official source
-- ─────────────────────────────────────────────────────────────────────────────

-- Add verification columns to transceivers
ALTER TABLE transceivers
  ADD COLUMN IF NOT EXISTS price_verified        BOOLEAN   NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS price_verified_at     TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS price_verified_url    TEXT,
  ADD COLUMN IF NOT EXISTS price_verified_eur    NUMERIC(10,2),
  ADD COLUMN IF NOT EXISTS image_verified        BOOLEAN   NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS image_verified_at     TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS image_verified_url    TEXT,
  ADD COLUMN IF NOT EXISTS details_verified      BOOLEAN   NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS details_verified_at   TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS details_source_url    TEXT,
  ADD COLUMN IF NOT EXISTS fully_verified        BOOLEAN   NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS fully_verified_at     TIMESTAMPTZ;

-- Add verification columns to price_observations
ALTER TABLE price_observations
  ADD COLUMN IF NOT EXISTS is_verified      BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS verified_at      TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS scrape_method    TEXT;  -- 'html_scrape', 'api', 'manual'

-- ─── Function: compute verification status for a single transceiver ───────────
CREATE OR REPLACE FUNCTION compute_transceiver_verification(t_id UUID)
RETURNS void LANGUAGE plpgsql AS $$
DECLARE
  v_price_row    RECORD;
  v_price_ok     BOOLEAN := false;
  v_price_eur    NUMERIC;
  v_price_url    TEXT;
  v_price_at     TIMESTAMPTZ;
  v_image_ok     BOOLEAN := false;
  v_details_ok   BOOLEAN := false;
  v_fully_ok     BOOLEAN := false;
BEGIN
  -- ── PRICE VERIFICATION ────────────────────────────────────────────────────
  -- Criteria: at least one price_observation with:
  --   • price > 0
  --   • url IS NOT NULL (scraped from real product page, not estimated)
  --   • observed within last 30 days
  SELECT po.price, po.currency, po.url, po.time
  INTO v_price_row
  FROM price_observations po
  WHERE po.transceiver_id = t_id
    AND po.price > 0
    AND po.url IS NOT NULL
    AND po.url != ''
    AND po.time > NOW() - INTERVAL '30 days'
  ORDER BY po.time DESC
  LIMIT 1;

  IF FOUND THEN
    v_price_ok := true;
    v_price_url := v_price_row.url;
    v_price_at  := v_price_row.time;
    -- Normalize to EUR (approximate for non-EUR currencies — scraper stores actual currency)
    v_price_eur := CASE v_price_row.currency
      WHEN 'EUR' THEN v_price_row.price
      WHEN 'USD' THEN ROUND(v_price_row.price * 0.92, 2)
      WHEN 'GBP' THEN ROUND(v_price_row.price * 1.17, 2)
      ELSE v_price_row.price
    END;
  END IF;

  -- ── IMAGE VERIFICATION ────────────────────────────────────────────────────
  -- Criteria: image_url or image_r2_key is set AND
  --   • image_url points to a real vendor/manufacturer CDN (not a generic placeholder)
  --   • OR image is stored in R2 (always real, downloaded by scraper)
  SELECT
    (
      -- R2 image always verified (downloaded by scraper from product page)
      (transceivers.image_r2_key IS NOT NULL AND transceivers.image_r2_key != '')
      OR
      -- image_url from known product CDNs (flexoptix, fs.com, vendor sites)
      (transceivers.image_url IS NOT NULL AND (
        transceivers.image_url ILIKE '%flexoptix.net%'
        OR transceivers.image_url ILIKE '%resource.fs.com%'
        OR transceivers.image_url ILIKE '%fs.com/mall%'
        OR transceivers.image_url ILIKE '%innolight%'
        OR transceivers.image_url ILIKE '%coherent.com%'
        OR transceivers.image_url ILIKE '%lumentum%'
        OR transceivers.image_url ILIKE '%ii-vi%'
        OR transceivers.image_url ILIKE '%atgbics.com%'
        OR transceivers.image_url ILIKE '%prolabs%'
        OR transceivers.image_url ILIKE '%10gtek%'
        OR transceivers.image_url ILIKE '%optcore%'
        OR transceivers.image_url ILIKE '%fluxlight%'
        OR transceivers.image_url ILIKE '%champion-one%'
        OR transceivers.image_url ILIKE '%gbics.com%'
      ))
    ) INTO v_image_ok
  FROM transceivers
  WHERE id = t_id;

  -- ── DETAILS VERIFICATION ──────────────────────────────────────────────────
  -- Criteria: product_page_url is set AND data_confidence is 'scraped_unverified' or better
  --   AND core fields are populated (form_factor, speed_gbps, reach_label, fiber_type)
  SELECT
    (
      transceivers.product_page_url IS NOT NULL
      AND transceivers.form_factor IS NOT NULL
      AND transceivers.speed_gbps IS NOT NULL
      -- reach_label must be a non-empty string (IS NOT NULL allows empty string — wrong)
      AND transceivers.reach_label IS NOT NULL
      AND transceivers.reach_label != ''
      -- part_number must not be a URL slug (garbled data from scraper)
      AND (transceivers.part_number IS NOT NULL AND transceivers.part_number != transceivers.slug)
      AND transceivers.part_number NOT ILIKE '%-compatible-transceiver%'
      AND transceivers.part_number NOT ILIKE '%-r-compatible%'
      AND transceivers.data_confidence IN ('scraped_unverified', 'verified', 'official')
    ) INTO v_details_ok
  FROM transceivers
  WHERE id = t_id;

  -- ── FULLY VERIFIED ────────────────────────────────────────────────────────
  v_fully_ok := v_price_ok AND v_image_ok AND v_details_ok;

  -- ── WRITE BACK ────────────────────────────────────────────────────────────
  UPDATE transceivers SET
    price_verified      = v_price_ok,
    price_verified_at   = CASE WHEN v_price_ok THEN v_price_at ELSE NULL END,
    price_verified_url  = CASE WHEN v_price_ok THEN v_price_url ELSE NULL END,
    price_verified_eur  = CASE WHEN v_price_ok THEN v_price_eur ELSE NULL END,
    image_verified      = v_image_ok,
    image_verified_at   = CASE WHEN v_image_ok THEN NOW() ELSE NULL END,
    details_verified    = v_details_ok,
    details_verified_at = CASE WHEN v_details_ok THEN NOW() ELSE NULL END,
    fully_verified      = v_fully_ok,
    fully_verified_at   = CASE WHEN v_fully_ok THEN NOW() ELSE NULL END,
    updated_at          = NOW()
  WHERE id = t_id;
END;
$$;

-- ─── Function: recompute ALL transceivers (run after scraper, or on demand) ──
CREATE OR REPLACE FUNCTION recompute_all_verification()
RETURNS TABLE(
  price_verified_count  INT,
  image_verified_count  INT,
  details_verified_count INT,
  fully_verified_count  INT,
  total_count           INT
) LANGUAGE plpgsql AS $$
BEGIN
  PERFORM compute_transceiver_verification(id) FROM transceivers;

  RETURN QUERY
  SELECT
    COUNT(*) FILTER (WHERE price_verified)::INT,
    COUNT(*) FILTER (WHERE image_verified)::INT,
    COUNT(*) FILTER (WHERE details_verified)::INT,
    COUNT(*) FILTER (WHERE fully_verified)::INT,
    COUNT(*)::INT
  FROM transceivers;
END;
$$;

-- ─── Initial run: compute verification for all existing products ──────────────
SELECT * FROM recompute_all_verification();

-- ─── Indexes for fast filtering ───────────────────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_transceivers_price_verified  ON transceivers (price_verified)  WHERE price_verified = true;
CREATE INDEX IF NOT EXISTS idx_transceivers_fully_verified  ON transceivers (fully_verified)  WHERE fully_verified = true;

-- ─── Convenience view ─────────────────────────────────────────────────────────
CREATE OR REPLACE VIEW v_verified_products AS
SELECT
  t.id, t.part_number, t.standard_name, t.form_factor, t.speed, t.speed_gbps,
  t.reach_label, t.fiber_type, t.connector, t.image_url, t.image_r2_key,
  t.product_page_url, t.data_confidence,
  v.name AS vendor, v.type AS vendor_type,
  t.price_verified, t.price_verified_eur, t.price_verified_url, t.price_verified_at,
  t.image_verified, t.details_verified, t.fully_verified, t.fully_verified_at
FROM transceivers t
JOIN vendors v ON t.vendor_id = v.id
WHERE t.price_verified = true OR t.fully_verified = true;

COMMENT ON VIEW v_verified_products IS
  'Products with at least a verified price. fully_verified = price + image + details all confirmed.';
