-- VRFS-1924

-- generic, single row config table.  if you need a new config, add a column
CREATE TABLE generic_state (
  id VARCHAR(64)  PRIMARY KEY DEFAULT uuid_generate_v4(),
  score_history_last_imported_at TIMESTAMP
);

INSERT INTO generic_state (id, score_history_last_imported_at) VALUES ('default', TIMESTAMP '01-01-1900 00:00:00');

CREATE TABLE score_histories (
  from_client_id VARCHAR(64),
  from_addr BIGINT,
  from_isp VARCHAR(50),
  from_country VARCHAR(64),
  from_region VARCHAR(64),
  from_city VARCHAR(255),
  from_postal VARCHAR(25),
  from_latitude DOUBLE PRECISION,
  from_longitude DOUBLE PRECISION,
  to_client_id VARCHAR(64),
  to_addr BIGINT,
  to_isp VARCHAR(50),
  to_country VARCHAR(64),
  to_region VARCHAR(64),
  to_city VARCHAR(255),
  to_postal VARCHAR(25),
  to_latitude DOUBLE PRECISION,
  to_longitude DOUBLE PRECISION,
  score INTEGER NOT NULL,
  score_dt TIMESTAMP NOT NULL,
  scoring_data TEXT
);