-- We may need to limit the bots permissions :)

-- pw: flagbotpw
CREATE ROLE flagbot LOGIN
  ENCRYPTED PASSWORD 'md58096a1ddf68ea631cefdf8d8f2455952'
  SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;


CREATE DATABASE flagbot
  WITH OWNER = flagbot
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;


\connect flagbot


CREATE TABLE IF NOT EXISTS flag_ids (
service character varying(64) NOT NULL,
team character varying(64) NOT NULL,
host character varying(10) NOT NULL,
port character varying(10) NOT NULL,
flag_id character varying(128) NOT NULL,
received timestamp without time zone NOT NULL DEFAULT date_trunc('second'::text, now()),
handed_out timestamp without time zone,
status integer NOT NULL DEFAULT 0,
CONSTRAINT flag_ids_pkey PRIMARY KEY (service, flag_id)
);

ALTER TABLE flag_ids OWNER TO flagbot;


CREATE TABLE IF NOT EXISTS flags (
fid serial NOT NULL,
flag character varying(32) NOT NULL,
service character varying(32),
flag_id character varying(128),
submitter character varying(32),
received timestamp without time zone NOT NULL DEFAULT date_trunc('second'::text, now()),
submitted timestamp without time zone,
status integer NOT NULL DEFAULT 0, 
srvresponse character varying(128),
CONSTRAINT flags_pkey PRIMARY KEY (fid),
CONSTRAINT flags_flag_key UNIQUE (flag)
);

ALTER TABLE flags OWNER TO flagbot;
