`db/lead_score_v2.sql`

33 lines
-- permit_lead_score_v2(): parcel-signal-enriched lead ranking (0–100).
-- NEW function — does NOT replace the live permit_lead_score. v2 = v1 + up to 15 pts of
-- PARCEL signal the base can't see (capped at 100). No rescale of v1 — a permit WITHOUT
-- parcel enrichment scores exactly v1 (never penalized); parcel signal only LIFTS:
--   • teardown / high-value redevelopment: new construction on a valuable, older parcel
--   • parcel assessed-value tier (owner equity / asset size)
-- Swapping v2 into the live viewer is a GATED action (draft to pending-approval).
CREATE OR REPLACE FUNCTION permit_lead_score_v2(
  p_issue_date  timestamptz,
  p_valuation   numeric,
  p_permit_type text,
  p_sub_type    text,
  p_status      text,
  p_assessed    numeric,
  p_year_built  text
) RETURNS integer LANGUAGE sql STABLE AS $$
  SELECT LEAST(100, (
    permit_lead_score(p_issue_date, p_valuation, p_permit_type, p_sub_type, p_status)
    + LEAST(15,
        -- teardown / redevelopment: SUBSTANTIAL ground-up build (>=$500k, not a garage/
        -- partial) on a valuable, OLD parcel — parcel wealth alone isn't a teardown (Cody c5)
        (CASE WHEN p_permit_type = 'Bldg-New' AND p_valuation >= 500000 AND p_assessed >= 1000000
                   AND p_year_built ~ '^[0-9]{4}$' AND (p_year_built)::int BETWEEN 1 AND 1970
              THEN 8 ELSE 0 END)
        -- parcel assessed-value tier
        + (CASE WHEN p_assessed >= 3000000 THEN 7
                WHEN p_assessed >= 1000000 THEN 5
                WHEN p_assessed >=  500000 THEN 3
                ELSE 0 END)
      )
  ))::int;
$$;