-- ============================================================
-- Migration 002: Seed existing hardcoded data into config tables
-- Run AFTER 001_config_tables.sql
-- ============================================================

-- ── Snow-load regions ────────────────────────────────────────────────────────
-- Source: isHighSnowLoadArea() in quoteGHL.php lines 208-262
INSERT IGNORE INTO `snow_load_regions` (match_type, state, city, notes) VALUES
  -- Full-state snow load
  ('state', 'AK', NULL, 'Alaska'),
  ('state', 'ME', NULL, 'Maine'),
  ('state', 'VT', NULL, 'Vermont'),
  ('state', 'NH', NULL, 'New Hampshire'),
  ('state', 'WA', NULL, 'Washington'),
  ('state', 'UT', NULL, 'Utah'),
  ('state', 'MT', NULL, 'Montana'),
  ('state', 'ID', NULL, 'Idaho'),
  ('state', 'CO', NULL, 'Colorado'),
  ('state', 'WY', NULL, 'Wyoming'),
  ('state', 'MI', NULL, 'Michigan'),
  ('state', 'WI', NULL, 'Wisconsin'),
  -- Specific city
  ('city',  NULL, 'truckee', 'Truckee CA — high Sierra Nevada snow load'),
  -- Upstate New York cities (stored state='NY', matched client-side by city+state)
  ('city',  NULL, 'buffalo',          'Upstate NY'),
  ('city',  NULL, 'rochester',        'Upstate NY'),
  ('city',  NULL, 'niagara falls',    'Upstate NY'),
  ('city',  NULL, 'lackawanna',       'Upstate NY'),
  ('city',  NULL, 'tonawanda',        'Upstate NY'),
  ('city',  NULL, 'jamestown',        'Upstate NY'),
  ('city',  NULL, 'olean',            'Upstate NY'),
  ('city',  NULL, 'salamanca',        'Upstate NY'),
  ('city',  NULL, 'geneva',           'Upstate NY'),
  ('city',  NULL, 'canandaigua',      'Upstate NY'),
  ('city',  NULL, 'binghamton',       'Upstate NY'),
  ('city',  NULL, 'ithaca',           'Upstate NY'),
  ('city',  NULL, 'elmira',           'Upstate NY'),
  ('city',  NULL, 'corning',          'Upstate NY'),
  ('city',  NULL, 'hornell',          'Upstate NY'),
  ('city',  NULL, 'syracuse',         'Upstate NY'),
  ('city',  NULL, 'utica',            'Upstate NY'),
  ('city',  NULL, 'rome',             'Upstate NY'),
  ('city',  NULL, 'auburn',           'Upstate NY'),
  ('city',  NULL, 'oneida',           'Upstate NY'),
  ('city',  NULL, 'little falls',     'Upstate NY'),
  ('city',  NULL, 'sherrill',         'Upstate NY'),
  ('city',  NULL, 'albany',           'Upstate NY'),
  ('city',  NULL, 'schenectady',      'Upstate NY'),
  ('city',  NULL, 'troy',             'Upstate NY'),
  ('city',  NULL, 'saratoga springs', 'Upstate NY'),
  ('city',  NULL, 'cohoes',           'Upstate NY'),
  ('city',  NULL, 'watervliet',       'Upstate NY'),
  ('city',  NULL, 'rensselaer',       'Upstate NY'),
  ('city',  NULL, 'mechanicville',    'Upstate NY'),
  ('city',  NULL, 'watertown',        'Upstate NY'),
  ('city',  NULL, 'plattsburgh',      'Upstate NY'),
  ('city',  NULL, 'ogdensburg',       'Upstate NY'),
  ('city',  NULL, 'glens falls',      'Upstate NY'),
  ('city',  NULL, 'poughkeepsie',     'Upstate NY'),
  ('city',  NULL, 'kingston',         'Upstate NY'),
  ('city',  NULL, 'newburgh',         'Upstate NY'),
  ('city',  NULL, 'middletown',       'Upstate NY'),
  ('city',  NULL, 'port jervis',      'Upstate NY'),
  ('city',  NULL, 'beacon',           'Upstate NY'),
  ('city',  NULL, 'hudson',           'Upstate NY');

-- ── PLA regions ──────────────────────────────────────────────────────────────
-- Source: PLA block in quoteGHL.php lines 1112-1153
INSERT IGNORE INTO `pla_regions` (state, county, city, notes) VALUES
  ('CA', 'Los Angeles', 'Pasadena',       'LA County — Pasadena requires PLA'),
  ('CA', 'Los Angeles', 'Monterey Park',  'LA County — Monterey Park requires PLA'),
  ('CA', 'Santa Barbara', NULL,           'Santa Barbara County requires PLA'),
  ('VA', 'PRINCE WILLIAM CO SP', NULL,    'Prince William County VA requires PLA'),
  ('IL', 'Will', NULL,                    'Will County IL requires PLA'),
  ('MO', NULL, 'Kansas City',             'Kansas City MO requires PLA'),
  ('WI', NULL, 'Madison',                 'Madison WI requires PLA'),
  ('MA', NULL, 'Palmer',                  'Palmer MA requires PLA');

-- ── Sales rep GHL user ID map ────────────────────────────────────────────────
-- Source: assignedTo switch in quoteGHL.php lines 383-402
INSERT IGNORE INTO `sales_rep_map` (ghl_user_id, rep_name, rep_email) VALUES
  ('tNNz4h2tdrD5IUGcb9rx', 'Todd Evirs',    'toddevirs@gigawattinc.com'),
  ('RFyfVIAencH13YbJfMJO', 'Zach Stevens',  'zachstevens@gigawattinc.com'),
  ('iASpxzLRg6FTMrjqqCCJ', 'Linh Tran',     'linhtran@gigawattinc.com'),
  ('OKh95qUzXfPdURl7Q8H6', 'John Davis',    'johndavis@gigawattinc.com'),
  ('AL4HgwBgamzArAgpkQDO', 'Raj Patel',     'rajpatel@gigawattinc.com'),
  ('kuQY6TIvhDSG2cGCnsNN', 'Josh Bostic',   'joshbostic@gigawattinc.com'),
  ('wmhDMIMWtRRA799aOBgw', 'Deep Patel',    'deeppatel@gigawattinc.com'),
  ('InmZPsuQUIDG0x0JNvUB', 'Jackie Lane',   'jackielane@gigawattinc.com'),
  ('y8OqwzgzWKxylyJJvWhV', 'Standy Lempka', 'standylempka@gigawattinc.com'),
  ('wxghB3PQAxOTchoGYfC7', 'Ben Mitrovich', 'benmitrovich@gigawattinc.com');

-- ── State tax rules ───────────────────────────────────────────────────────────
INSERT IGNORE INTO `state_tax_rules` (state, lookup_by, table_name, stored_as) VALUES
  ('CA', 'city', 'SalesTaxCA', 'percent'),   -- CA stores Rate as 9.50 (percent)
  ('TX', 'zip',  'SalesTaxTX', 'decimal'),
  ('NM', 'zip',  'SalesTaxNM', 'decimal'),
  ('ID', 'zip',  'SalesTaxID', 'decimal'),
  ('GA', 'zip',  'SalesTaxGA', 'decimal'),
  ('WA', 'zip',  'SalesTaxWA', 'decimal'),
  ('OH', 'zip',  'SalesTaxOH', 'decimal'),
  ('FL', 'zip',  'SalesTaxFL', 'none'),       -- FL solar is tax-exempt
  ('PA', 'zip',  'SalesTaxPA', 'decimal'),
  ('VA', 'zip',  'SalesTaxVA', 'decimal'),
  ('IL', 'zip',  'SalesTaxIL', 'decimal'),
  ('IN', 'zip',  'SalesTaxIN', 'decimal'),
  ('ME', 'zip',  'SalesTaxME', 'decimal'),
  ('AR', 'zip',  'SalesTaxAR', 'decimal'),
  ('NC', 'zip',  'SalesTaxNC', 'decimal');
