`scripts/dedupe-corridor-shells.sql`

66 lines
-- scripts/dedupe-corridor-shells.sql
--
-- Hide noisy public_records seed rows after the corridor seed has run.
-- Reversible: sets status='hidden' rather than deleting. Re-running the
-- seed never resurrects hidden rows because slug-conflict makes the
-- INSERT a no-op (ON CONFLICT (slug) DO NOTHING).
--
-- Two patterns this targets:
--
--   1. Personal-name LLCs sharing an address with a proper-business-name
--      peer. These are typically booth-renters at salons, tax preparers
--      registered to a coworking suite, or owner LLCs at the same address
--      as the actual storefront. The proper-business-name peer is "the
--      business"; the personal-name row is noise.
--
--   2. Creative vertical addresses with 4+ entries. These are talent-agency
--      / production-coworking suites (e.g. 15260 Ventura Blvd #1040 holds
--      24 entertainment-industry shell LLCs). Keep the oldest row at each
--      so the address is still represented; hide the rest.
--
-- Run: psql -d ventura_claw_leads -f scripts/dedupe-corridor-shells.sql

BEGIN;

WITH personal AS (
  SELECT id
    FROM businesses
   WHERE source='public_records' AND status='active'
     AND business_name ~ '^[A-Z][a-z]+ ([A-Z]\.? )?[A-Z][a-z]+(?:-[A-Z][a-z]+)?( [A-Z][a-z]+)?$'
     AND business_name !~* '\m(Inc|Llc|Co|Corp|Salon|Spa|Hair|Nail|Bar|Cafe|Kitchen|Studio|Boutique|Yoga|Pilates|Dance|Pet|Photo|Florist|Cleaner|Detail|Tint|Wash|Restaurant|Bakery|Pizza|Burger|Taco|Sushi|Bistro|Bagel|Coffee|Tea|Juice|Deli|Catering|Brow|Lash|Skin)\M'
),
peers AS (
  SELECT vertical, lower(regexp_replace(street, '\s+', ' ', 'g')) AS norm_street, city
    FROM businesses
   WHERE status='active'
     AND business_name !~ '^[A-Z][a-z]+ ([A-Z]\.? )?[A-Z][a-z]+(?:-[A-Z][a-z]+)?( [A-Z][a-z]+)?$'
   GROUP BY 1,2,3
)
UPDATE businesses b
   SET status='hidden'
  FROM personal p
 WHERE b.id = p.id
   AND EXISTS (
     SELECT 1 FROM peers
      WHERE peers.vertical=b.vertical
        AND peers.norm_street=lower(regexp_replace(b.street, '\s+', ' ', 'g'))
        AND peers.city=b.city
   );

WITH crowded AS (
  SELECT lower(regexp_replace(street, '\s+', ' ', 'g')) AS norm_street, city,
         array_agg(id ORDER BY id) AS ids
    FROM businesses
   WHERE source='public_records' AND vertical='creative' AND status='active' AND street IS NOT NULL
   GROUP BY 1,2 HAVING COUNT(*) >= 4
)
UPDATE businesses b
   SET status='hidden'
  FROM crowded c
 WHERE b.vertical='creative'
   AND lower(regexp_replace(b.street, '\s+', ' ', 'g')) = c.norm_street
   AND b.city = c.city
   AND b.id <> c.ids[1];

COMMIT;