-- Create Tables
CREATE TABLE IF NOT EXISTS geolocation (
    locId INTEGER PRIMARY KEY,
    country TEXT,
    region TEXT,
    city TEXT,
    postalCode TEXT,
    latitude NUMERIC,
    longitude NUMERIC,
    metroCode TEXT,
    areaCode TEXT
);

CREATE TABLE IF NOT EXISTS geoblocks (
    startIpNum NUMERIC UNIQUE,
    endIpNum NUMERIC UNIQUE,
    locId INTEGER REFERENCES geolocation(locID)
);

-- Import CSV files
.mode csv
.import GeoLiteCity-Location.csv geolocation
.import GeoLiteCity-Blocks.csv geoblocks

-- add additional index
-- (Tip by Kamil Kubica. https://www.maxmind.com/app/csv)
ALTER TABLE geoblocks ADD COLUMN idx INTEGER;
UPDATE geoblocks SET idx = (endIpNum - (endIpNum % 65536));
CREATE INDEX geoidx ON geoblocks(idx);