DROP TABLE machine_fingerprints;

CREATE TABLE machine_fingerprints (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  fingerprint VARCHAR(20000) NOT NULL,
  when_taken VARCHAR NOT NULL,
  print_type VARCHAR NOT NULL,
  remote_ip VARCHAR(1000) NOT NULL,
  jam_track_right_id BIGINT REFERENCES jam_track_rights(id) ON DELETE SET NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE machine_extras (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  machine_fingerprint_id VARCHAR(64) NOT NULL REFERENCES machine_fingerprints(id) ON DELETE CASCADE,
  mac_address VARCHAR(100),
  mac_name VARCHAR(255),
  upstate BOOLEAN,
  ipaddr_0 VARCHAR(200),
  ipaddr_1 VARCHAR(200),
  ipaddr_2 VARCHAR(200),
  ipaddr_3 VARCHAR(200),
  ipaddr_4 VARCHAR(200),
  ipaddr_5 VARCHAR(200),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE fraud_alerts (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  machine_fingerprint_id VARCHAR(64) NOT NULL REFERENCES machine_fingerprints(id) ON DELETE CASCADE,
  user_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  resolved BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE fingerprint_whitelists (
    id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
    fingerprint VARCHAR(20000) UNIQUE NOT NULL
);

CREATE INDEX machine_fingerprints_index1 ON machine_fingerprints USING btree (fingerprint, user_id, remote_ip, created_at);