-- ivr_zipcodes.sql
-- Local ZIP -> city/state table, replaces the live zippopotam.us call in ivr.php
-- Database: windshi_main
--
-- Data source: GeoNames US postal codes (CC BY 4.0, free).
-- This is the same dataset zippopotam.us is built from, so the values you get
-- back will match what the API has been returning.
--
--   cd ~
--   wget https://download.geonames.org/export/zip/US.zip
--   unzip US.zip            # produces US.txt (tab-delimited, ~41k rows, no header)
--
-- US.txt columns, in order:
--   1 country  2 zip  3 place  4 state_name  5 state_abbr  6 county
--   7 county_fips  8 admin3  9 admin3_code  10 lat  11 lon  12 accuracy


-- ─── SCHEMA ───────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS ivr_zipcodes (
    zip        CHAR(5)       NOT NULL,
    city       VARCHAR(120)  NOT NULL DEFAULT '',
    state      CHAR(2)       NOT NULL DEFAULT '',
    state_name VARCHAR(60)   NOT NULL DEFAULT '',
    county     VARCHAR(120)  NOT NULL DEFAULT '',
    lat        DECIMAL(9,6)  DEFAULT NULL,
    lon        DECIMAL(9,6)  DEFAULT NULL,
    source     VARCHAR(16)   NOT NULL DEFAULT 'geonames',
    updated    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (zip),
    KEY idx_state (state),
    KEY idx_city_state (city, state),
    KEY idx_latlon (lat, lon)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ─── IMPORT ───────────────────────────────────────────────────────────────────
-- GeoNames has several rows for some ZIPs (multiple place names for one code).
-- IGNORE keeps the first row per ZIP, which is the primary place name.
-- Run from the directory holding US.txt:
--
--   mysql -u windshi_quote -p --local-infile=1 windshi_main < ivr_zipcodes.sql
--
-- If your MySQL rejects LOAD DATA LOCAL INFILE (some cPanel builds disable it),
-- skip this statement and use import_zipcodes.php instead.

LOAD DATA LOCAL INFILE 'US.txt'
IGNORE INTO TABLE ivr_zipcodes
FIELDS TERMINATED BY '\t'
LINES  TERMINATED BY '\n'
(@country, @zip, @place, @state_name, @state_abbr, @county,
 @county_fips, @admin3, @admin3_code, @lat, @lon, @accuracy)
SET zip        = LPAD(TRIM(@zip), 5, '0'),
    city       = TRIM(@place),
    state      = TRIM(@state_abbr),
    state_name = TRIM(@state_name),
    county     = TRIM(@county),
    lat        = NULLIF(TRIM(@lat), ''),
    lon        = NULLIF(TRIM(@lon), ''),
    source     = 'geonames';


-- ─── VERIFY ───────────────────────────────────────────────────────────────────
-- Expect roughly 41,000 rows.

SELECT COUNT(*) AS total_zips FROM ivr_zipcodes;

-- Spot-check a few, including your own area:
SELECT zip, city, state FROM ivr_zipcodes
WHERE zip IN ('92688','90001','33607','60601','10001')
ORDER BY zip;

-- Rows with no state abbreviation (should be 0 or very few — territories):
SELECT COUNT(*) AS missing_state FROM ivr_zipcodes WHERE state = '';


-- ─── MAINTENANCE ──────────────────────────────────────────────────────────────
-- ivr.php backfills any ZIP it can't find, tagged source='api'. To see which
-- ZIPs the dataset is missing (worth checking every few months):
--
--   SELECT zip, city, state, updated FROM ivr_zipcodes
--   WHERE source = 'api' ORDER BY updated DESC;
--
-- To refresh from a newer GeoNames dump, truncate and re-run the LOAD DATA,
-- but preserve the API-sourced rows first:
--
--   CREATE TABLE ivr_zipcodes_api AS SELECT * FROM ivr_zipcodes WHERE source='api';
--   TRUNCATE TABLE ivr_zipcodes;
--   -- (re-run LOAD DATA here)
--   INSERT IGNORE INTO ivr_zipcodes SELECT * FROM ivr_zipcodes_api;
--   DROP TABLE ivr_zipcodes_api;