-- ============================================================================
-- Snowflake CoCo Analytics Pipeline Views
-- ============================================================================

CREATE OR REPLACE VIEW TRAILQR.REGISTRY.V_FRAUD_HOTSPOTS_BY_AREA AS
SELECT
  coarse_area,
  COUNT(*) AS total_scans,
  COUNT(CASE WHEN verdict = 'DANGEROUS' OR reported = TRUE THEN 1 END) AS threat_count,
  ROUND(100.0 * COUNT(CASE WHEN verdict = 'DANGEROUS' OR reported = TRUE THEN 1 END) / NULLIF(COUNT(*), 0), 1) AS fraud_rate_pct,
  ROUND(AVG(score), 1) AS avg_threat_score
FROM TRAILQR.REGISTRY.QR_REGISTRY
GROUP BY coarse_area
ORDER BY threat_count DESC;

CREATE OR REPLACE VIEW TRAILQR.REGISTRY.V_STICKER_SWAP_ANOMALIES AS
SELECT
  r.qr_hash,
  r.display_name AS scanned_name,
  r.coarse_area,
  c.merchant_name AS registered_merchant_name,
  CASE
    WHEN c.merchant_name IS NULL THEN 'UNREGISTERED_ENTITY_IN_AREA'
    WHEN r.reported = TRUE OR r.verdict = 'DANGEROUS' THEN 'COMMUNITY_REPORTED_STICKER_SWAP'
    ELSE 'VERIFIED_CLEAN_MERCHANT'
  END AS anomaly_classification
FROM TRAILQR.REGISTRY.QR_REGISTRY r
LEFT JOIN TRAILQR.REGISTRY.CYBERSYN_VERIFIED_MERCHANTS c
  ON LOWER(r.coarse_area) = LOWER(c.coarse_area)
  AND LOWER(r.display_name) LIKE '%' || LOWER(c.merchant_name) || '%';

CREATE OR REPLACE VIEW TRAILQR.REGISTRY.V_GEMMA_AI_NEIGHBOURHOOD_BRIEF AS
SELECT
  h.coarse_area,
  h.total_scans,
  h.threat_count,
  h.fraud_rate_pct,
  CASE
    WHEN h.fraud_rate_pct >= 50.0 THEN 'HIGH_RISK_CORRIDOR'
    WHEN h.fraud_rate_pct >= 20.0 THEN 'ELEVATED_CAUTION'
    ELSE 'GENERALLY_SAFE_COMMUNITY'
  END AS security_tier
FROM TRAILQR.REGISTRY.V_FRAUD_HOTSPOTS_BY_AREA h;