`db/seed/markets.js`

67 lines
'use strict';
/**
 * Seed markets table — California (§7 priority order) and Arizona.
 * Idempotent via ON CONFLICT (normalized_name) DO UPDATE.
 */

const { normalizeName } = require('../../lib/types');

const CA_MARKETS = [
  { metro: 'Greater Los Angeles', region: 'Southern California', priority: 1 },
  { metro: 'Orange County',       region: 'Southern California', priority: 2 },
  { metro: 'Inland Empire',       region: 'Southern California', priority: 3 },
  { metro: 'San Diego',           region: 'Southern California', priority: 4 },
  { metro: 'Ventura County',      region: 'Southern California', priority: 5 },
  { metro: 'San Francisco Bay Area', region: 'Northern California', priority: 6 },
  { metro: 'Sacramento',          region: 'Northern California', priority: 7 },
  { metro: 'Central Valley',      region: 'Central California',  priority: 8 },
  { metro: 'Statewide California', region: 'California',         priority: 9 },
];

const AZ_MARKETS = [
  { metro: 'Phoenix metro',   region: 'Greater Phoenix', priority: 1 },
  { metro: 'Scottsdale',      region: 'Greater Phoenix', priority: 2 },
  { metro: 'Tempe',           region: 'Greater Phoenix', priority: 3 },
  { metro: 'Mesa',            region: 'Greater Phoenix', priority: 4 },
  { metro: 'Chandler',        region: 'Greater Phoenix', priority: 5 },
  { metro: 'Gilbert',         region: 'Greater Phoenix', priority: 6 },
  { metro: 'Glendale',        region: 'Greater Phoenix', priority: 7 },
  { metro: 'Tucson',          region: 'Southern Arizona', priority: 8 },
  { metro: 'Statewide Arizona', region: 'Arizona',       priority: 9 },
];

async function seedMarkets(client) {
  let inserted = 0;
  let updated = 0;

  for (const m of CA_MARKETS) {
    const nn = normalizeName(m.metro);
    const res = await client.query(
      `INSERT INTO markets (state, region, metro, normalized_name, priority)
       VALUES ($1, $2, $3, $4, $5)
       ON CONFLICT (normalized_name)
       DO UPDATE SET state=$1, region=$2, metro=$3, priority=$5
       RETURNING (xmax = 0) AS is_insert`,
      ['CA', m.region, m.metro, nn, m.priority]
    );
    if (res.rows[0].is_insert) inserted++; else updated++;
  }

  for (const m of AZ_MARKETS) {
    const nn = normalizeName(m.metro);
    const res = await client.query(
      `INSERT INTO markets (state, region, metro, normalized_name, priority)
       VALUES ($1, $2, $3, $4, $5)
       ON CONFLICT (normalized_name)
       DO UPDATE SET state=$1, region=$2, metro=$3, priority=$5
       RETURNING (xmax = 0) AS is_insert`,
      ['AZ', m.region, m.metro, nn, m.priority]
    );
    if (res.rows[0].is_insert) inserted++; else updated++;
  }

  return { inserted, updated };
}

module.exports = { seedMarkets };