`full/analyze.sql`

71 lines
\echo '================ product_colors_v2 STAGING SUMMARY ================'
\echo '--- total rows loaded ---'
select count(*) as v2_rows from product_colors_v2;

\echo '--- verdict distribution (all statuses) ---'
select verdict, count(*), round(100.0*count(*)/sum(count(*)) over (),1) as pct
from product_colors_v2 group by verdict order by 2 desc;

\echo '--- verdict distribution (ACTIVE only: ACTIVE+active) ---'
select verdict, count(*)
from product_colors_v2 where lower(status)='active' group by verdict order by 2 desc;

\echo '================ V2 EXTRACTION QUALITY vs CACHED ================'
\echo '--- cached_hex near-white capture rate (the documented defect) ---'
select
  count(*) total,
  count(*) filter (where upper(cached_hex) in ('#F2F2F2','#FFFFFF','#FAFAFA','#F5F5F5','#FFFFFE','#FEFEFE')) as cached_exact_white,
  count(*) filter (where cached_hex ~* '^#F[0-9A-F]F[0-9A-F]F[0-9A-F]$') as cached_nearwhite_pattern
from product_colors_v2;

\echo '--- v2 background-suppression: how often v2 differs from naive v1 ---'
select
  count(*) filter (where real_hex_v1 <> real_hex) as bg_suppressed_changed,
  count(*) as total,
  round(100.0*count(*) filter (where real_hex_v1 <> real_hex)/count(*),1) as pct_changed
from product_colors_v2 where verdict <> 'ERROR';

\echo '--- v2 real_hex still landing on white/neutral (extraction ceiling) ---'
select image_family, count(*) from product_colors_v2 where verdict<>'ERROR'
group by image_family order by 2 desc;

\echo '================ DEFECT-SET RE-CONFIRMATION (prior 625 vs v2) ================'
\echo '--- match rate of prior defects into v2 by image_url ---'
select pd.tier,
  count(*) as prior,
  count(v2.shopify_product_id) as matched_in_v2,
  count(*) filter (where v2.shopify_product_id is null) as unmatched
from prior_defects pd
left join product_colors_v2 v2 on v2.image_url = pd.image_url
group by pd.tier order by 1;

\echo '--- how prior verdicts shift under v2 (matched rows) ---'
select pd.tier, pd.prior_verdict, v2.verdict as v2_verdict, count(*)
from prior_defects pd
join product_colors_v2 v2 on v2.image_url = pd.image_url
group by pd.tier, pd.prior_verdict, v2.verdict
order by pd.tier, pd.prior_verdict, 4 desc;

\echo '--- net: prior defects CONFIRMED (still HIGH/REVIEW) vs CLEARED (now OK) under v2 ---'
select pd.tier,
  count(*) filter (where v2.verdict in ('HIGH','REVIEW')) as still_defect,
  count(*) filter (where v2.verdict='OK') as cleared_ok,
  count(*) filter (where v2.verdict='UNKNOWN') as now_unknown,
  count(*) filter (where v2.verdict='ERROR') as v2_error
from prior_defects pd
join product_colors_v2 v2 on v2.image_url = pd.image_url
group by pd.tier order by 1;

\echo '--- NEW HIGH-severity defects v2 surfaces beyond the prior set (ACTIVE only) ---'
select count(*) as new_high_active
from product_colors_v2 v2
left join prior_defects pd on pd.image_url = v2.image_url
where v2.verdict='HIGH' and lower(v2.status)='active' and pd.image_url is null;

\echo '--- top vendors by v2 HIGH (ACTIVE only) ---'
select vendor, count(*) filter (where verdict='HIGH') as high,
       count(*) filter (where verdict='REVIEW') as review, count(*) as scanned
from product_colors_v2 where lower(status)='active'
group by vendor having count(*) filter (where verdict='HIGH')>0
order by high desc limit 20;