-- usmaps_schema.sql
-- Creates a database/table for storing U.S. state outline coordinate data.
-- The map_coordinates field stores the state's boundary geometry as GeoJSON text.

CREATE DATABASE IF NOT EXISTS usmaps
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE usmaps;

DROP TABLE IF EXISTS states;

CREATE TABLE states (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    state_name VARCHAR(50) NOT NULL,
    state_initials CHAR(2) NOT NULL,
    capital_city VARCHAR(80) NOT NULL,
    map_coordinates LONGTEXT NULL,
    source_name VARCHAR(255) NULL,
    source_url TEXT NULL,
    imported_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_state_initials (state_initials),
    UNIQUE KEY uniq_state_name (state_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO states (state_name, state_initials, capital_city) VALUES
('Alabama','AL','Montgomery'),
('Alaska','AK','Juneau'),
('Arizona','AZ','Phoenix'),
('Arkansas','AR','Little Rock'),
('California','CA','Sacramento'),
('Colorado','CO','Denver'),
('Connecticut','CT','Hartford'),
('Delaware','DE','Dover'),
('Florida','FL','Tallahassee'),
('Georgia','GA','Atlanta'),
('Hawaii','HI','Honolulu'),
('Idaho','ID','Boise'),
('Illinois','IL','Springfield'),
('Indiana','IN','Indianapolis'),
('Iowa','IA','Des Moines'),
('Kansas','KS','Topeka'),
('Kentucky','KY','Frankfort'),
('Louisiana','LA','Baton Rouge'),
('Maine','ME','Augusta'),
('Maryland','MD','Annapolis'),
('Massachusetts','MA','Boston'),
('Michigan','MI','Lansing'),
('Minnesota','MN','Saint Paul'),
('Mississippi','MS','Jackson'),
('Missouri','MO','Jefferson City'),
('Montana','MT','Helena'),
('Nebraska','NE','Lincoln'),
('Nevada','NV','Carson City'),
('New Hampshire','NH','Concord'),
('New Jersey','NJ','Trenton'),
('New Mexico','NM','Santa Fe'),
('New York','NY','Albany'),
('North Carolina','NC','Raleigh'),
('North Dakota','ND','Bismarck'),
('Ohio','OH','Columbus'),
('Oklahoma','OK','Oklahoma City'),
('Oregon','OR','Salem'),
('Pennsylvania','PA','Harrisburg'),
('Rhode Island','RI','Providence'),
('South Carolina','SC','Columbia'),
('South Dakota','SD','Pierre'),
('Tennessee','TN','Nashville'),
('Texas','TX','Austin'),
('Utah','UT','Salt Lake City'),
('Vermont','VT','Montpelier'),
('Virginia','VA','Richmond'),
('Washington','WA','Olympia'),
('West Virginia','WV','Charleston'),
('Wisconsin','WI','Madison'),
('Wyoming','WY','Cheyenne');
