`db/lead_score.sql`

64 lines
-- permit_lead_score(): 0–100 lead-quality ranking for a building permit.
-- One source of truth shared by the viewer API and the CSV exports.
-- Weighting: recency (30) + project value (28) + work type (18) + status (14)
--            + building type (10)  =  100 max.  STABLE (reads now()).
CREATE OR REPLACE FUNCTION permit_lead_score(
  p_issue_date  timestamptz,
  p_valuation   numeric,
  p_permit_type text,
  p_sub_type    text,
  p_status      text
) RETURNS integer LANGUAGE sql STABLE AS $$
  SELECT (
    -- recency (0–30): fresher = hotter
    CASE
      WHEN p_issue_date IS NULL THEN 0
      WHEN p_issue_date >= now() - interval '30 days'  THEN 30
      WHEN p_issue_date >= now() - interval '90 days'  THEN 24
      WHEN p_issue_date >= now() - interval '180 days' THEN 16
      WHEN p_issue_date >= now() - interval '365 days' THEN 8
      WHEN p_issue_date >= now() - interval '730 days' THEN 3
      ELSE 0
    END
    -- project value (0–28): bigger job = bigger opportunity
    + CASE
      WHEN p_valuation IS NULL      THEN 0
      WHEN p_valuation >= 5000000   THEN 28
      WHEN p_valuation >= 2000000   THEN 24
      WHEN p_valuation >= 1000000   THEN 20
      WHEN p_valuation >= 500000    THEN 15
      WHEN p_valuation >= 250000    THEN 11
      WHEN p_valuation >= 100000    THEN 8
      WHEN p_valuation >= 50000     THEN 5
      WHEN p_valuation >= 10000     THEN 2
      ELSE 0
    END
    -- work type (0–18): ground-up + additions are the premium jobs
    + CASE p_permit_type
      WHEN 'Bldg-New'          THEN 18
      WHEN 'Bldg-Addition'     THEN 15
      WHEN 'Swimming-Pool/Spa' THEN 12
      WHEN 'Bldg-Alter/Repair' THEN 9
      WHEN 'Grading'           THEN 6
      WHEN 'Bldg-Demolition'   THEN 6
      ELSE 3
    END
    -- status (0–14): active/issued work outranks finaled/expired
    + CASE p_status
      WHEN 'Issued'           THEN 14
      WHEN 'CofO in Progress' THEN 9
      WHEN 'CofO Issued'      THEN 7
      WHEN 'Permit Finaled'   THEN 5
      WHEN 'Permit Expired'   THEN 0
      ELSE 4
    END
    -- building type (0–10)
    + CASE p_sub_type
      WHEN '1 or 2 Family Dwelling' THEN 10
      WHEN 'Commercial'             THEN 9
      WHEN 'Apartment'              THEN 8
      ELSE 4
    END
  );
$$;