-- vi:ts=2 sw=2 et
.bail on
.eqp on
.echo on

BEGIN TRANSACTION;

DROP TABLE IF EXISTS NationalFile;

CREATE TABLE main.NationalFile(
  FEATURE_ID INT,
  FEATURE_NAME TEXT,
  FEATURE_CLASS TEXT,
  STATE_ALPHA TEXT,
  STATE_NUMERIC TEXT,
  COUNTY_NAME TEXT,
  COUNTY_NUMERIC TEXT,
  PRIMARY_LAT_DMS TEXT,
  PRIM_LONG_DMS TEXT,
  PRIM_LAT_DEC REAL,
  PRIM_LONG_DEC REAL,
  SOURCE_LAT_DMS TEXT,
  SOURCE_LONG_DMS TEXT,
  SOURCE_LAT_DEC REAL,
  SOURCE_LONG_DEC REAL,
  ELEV_IN_M INT,
  ELEV_IN_FT INT,
  MAP_NAME TEXT,
  DATE_CREATED TEXT,
  DATE_EDITED TEXT
) STRICT;

CREATE INDEX NationalFile__FEATURE_ID ON NationalFile(FEATURE_ID);
CREATE INDEX NationalFile__FEATURE_NAME ON NationalFile(FEATURE_NAME);
CREATE INDEX NationalFile__FEATURE_CLASS ON NationalFile(FEATURE_CLASS);
CREATE INDEX NationalFile__STATE_ALPHA ON NationalFile(STATE_ALPHA);
CREATE INDEX NationalFile__STATE_NUMERIC ON NationalFile(STATE_NUMERIC);
CREATE INDEX NationalFile__COUNTY_NAME ON NationalFile(COUNTY_NAME);
CREATE INDEX NationalFile__COUNTY_NUMERIC ON NationalFile(COUNTY_NUMERIC);
CREATE INDEX NationalFile__PRIMARY_LAT_DMS ON NationalFile(PRIMARY_LAT_DMS);
CREATE INDEX NationalFile__PRIM_LONG_DMS ON NationalFile(PRIM_LONG_DMS);
CREATE INDEX NationalFile__PRIM_LAT_DEC ON NationalFile(PRIM_LAT_DEC);
CREATE INDEX NationalFile__PRIM_LONG_DEC ON NationalFile(PRIM_LONG_DEC);
CREATE INDEX NationalFile__SOURCE_LAT_DMS ON NationalFile(SOURCE_LAT_DMS);
CREATE INDEX NationalFile__SOURCE_LONG_DMS ON NationalFile(SOURCE_LONG_DMS);
CREATE INDEX NationalFile__SOURCE_LAT_DEC ON NationalFile(SOURCE_LAT_DEC);
CREATE INDEX NationalFile__SOURCE_LONG_DEC ON NationalFile(SOURCE_LONG_DEC);
CREATE INDEX NationalFile__ELEV_IN_M ON NationalFile(ELEV_IN_M);
CREATE INDEX NationalFile__ELEV_IN_FT ON NationalFile(ELEV_IN_FT);
CREATE INDEX NationalFile__MAP_NAME ON NationalFile(MAP_NAME);
CREATE INDEX NationalFile__DATE_CREATED ON NationalFile(DATE_CREATED);
CREATE INDEX NationalFile__DATE_EDITED ON NationalFile(DATE_EDITED);

.import --schema temp --csv '|unzip -p NationalFile.zip|xsv input "-d|"' NationalFile

INSERT INTO main.NationalFile 
SELECT
  FEATURE_ID,
  FEATURE_NAME,
  FEATURE_CLASS,
  STATE_ALPHA,
  STATE_NUMERIC,
  IIF(COUNTY_NAME='',NULL,COUNTY_NAME) AS COUNTY_NAME,
  IIF(COUNTY_NUMERIC='',NULL,COUNTY_NUMERIC) AS COUNTY_NUMERIC,
  PRIMARY_LAT_DMS,
  PRIM_LONG_DMS,
  PRIM_LAT_DEC,
  PRIM_LONG_DEC,
  IIF(SOURCE_LAT_DMS='',NULL,SOURCE_LAT_DMS) AS SOURCE_LAT_DMS,
  IIF(SOURCE_LONG_DMS='',NULL,SOURCE_LONG_DMS) AS SOURCE_LONG_DMS,
  IIF(SOURCE_LAT_DEC='',NULL,SOURCE_LAT_DEC) AS SOURCE_LAT_DEC,
  IIF(SOURCE_LONG_DEC='',NULL,SOURCE_LONG_DEC) AS SOURCE_LONG_DEC,
  IIF(ELEV_IN_M='',NULL,ELEV_IN_M) AS ELEV_IN_M,
  IIF(ELEV_IN_FT='',NULL,ELEV_IN_FT) AS ELEV_IN_FT,
  MAP_NAME,
  IIF(DATE_CREATED='',NULL,DATE_CREATED) AS DATE_CREATED,
  IIF(DATE_EDITED='',NULL,DATE_EDITED) AS DATE_EDITED
FROM temp.NationalFile;

COMMIT;
