`scripts/cross-db-website-join.sh`

80 lines
#!/usr/bin/env bash
# Cross-DB website join — pulls (name, city, website) from ventura-corridor + animals
# and matches against lacountyeats facility table on normalized name + city.
# Free, no API, pure SQL.

set -euo pipefail

LOG=~/Projects/restaurant-directory/logs/cross-db-join.log
mkdir -p "$(dirname "$LOG")"

echo "[$(date)] starting cross-db website join" | tee -a "$LOG"

# 1. Build temp table in restaurant_professional_directory
psql -d restaurant_professional_directory <<'SQL' 2>&1 | tee -a "$LOG"
DROP TABLE IF EXISTS _xdb_business_websites;
CREATE TABLE _xdb_business_websites (
  source TEXT NOT NULL,
  name TEXT NOT NULL,
  city TEXT,
  website TEXT NOT NULL,
  norm_name TEXT GENERATED ALWAYS AS (lower(regexp_replace(name, '[^a-z0-9 ]', '', 'gi'))) STORED,
  norm_city TEXT GENERATED ALWAYS AS (lower(regexp_replace(coalesce(city,''), '[^a-z0-9]', '', 'gi'))) STORED
);
CREATE INDEX ix_xdb_norm ON _xdb_business_websites(norm_name, norm_city);
SQL

# 2. Pull from ventura-corridor → restaurant_professional_directory._xdb_business_websites
psql -d ventura_corridor -A -F$'\t' -t -c "
SELECT 'ventura' AS source, name, COALESCE(city,'') AS city, website
FROM businesses
WHERE website IS NOT NULL AND website <> ''
" | psql -d restaurant_professional_directory -c "
COPY _xdb_business_websites (source, name, city, website) FROM stdin WITH (FORMAT text, DELIMITER E'\t');
" 2>&1 | tee -a "$LOG"

# 3. Pull from animals → same table
psql -d animals_directory -A -F$'\t' -t -c "
SELECT 'animals' AS source, name, COALESCE(city,'') AS city, website
FROM businesses
WHERE website IS NOT NULL AND website <> '' AND state IN ('CA','California')
" | psql -d restaurant_professional_directory -c "
COPY _xdb_business_websites (source, name, city, website) FROM stdin WITH (FORMAT text, DELIMITER E'\t');
" 2>&1 | tee -a "$LOG"

# 4. Probe match counts
psql -d restaurant_professional_directory <<'SQL' 2>&1 | tee -a "$LOG"
SELECT source, COUNT(*) AS rows FROM _xdb_business_websites GROUP BY source;

-- match count: facility.name + city → xdb business
SELECT COUNT(*) AS facilities_matched FROM facility f
WHERE EXISTS (
  SELECT 1 FROM _xdb_business_websites x
  WHERE x.norm_name = lower(regexp_replace(f.name, '[^a-z0-9 ]', '', 'gi'))
    AND x.norm_city = lower(regexp_replace(coalesce(f.city,''), '[^a-z0-9]', '', 'gi'))
);
SQL

# 5. Apply matches: insert websites into facility_enrichment
psql -d restaurant_professional_directory <<'SQL' 2>&1 | tee -a "$LOG"
WITH matches AS (
  SELECT DISTINCT ON (f.facility_id)
    f.facility_id,
    x.website
  FROM facility f
  JOIN _xdb_business_websites x
    ON x.norm_name = lower(regexp_replace(f.name, '[^a-z0-9 ]', '', 'gi'))
   AND x.norm_city = lower(regexp_replace(coalesce(f.city,''), '[^a-z0-9]', '', 'gi'))
  ORDER BY f.facility_id, x.source  -- prefer ventura over animals alphabetically
)
INSERT INTO facility_enrichment (facility_id, website)
SELECT facility_id, website FROM matches
ON CONFLICT (facility_id) DO UPDATE SET website = EXCLUDED.website
WHERE facility_enrichment.website IS NULL OR facility_enrichment.website = '';

SELECT COUNT(*) AS facilities_with_website
FROM facility_enrichment WHERE website IS NOT NULL AND website <> '';
SQL

echo "[$(date)] done" | tee -a "$LOG"