DROP TABLE rankings;
DROP TABLE rankers;
DROP TABLE presenters;
DROP TABLE abstracts;

CREATE TABLE abstracts (
  abstract_id integer primary key,
  abstract_submission integer,
  abstract_title varchar not null,
  abstract_description varchar not null,
  abstract_status integer not null default 1,
  abstract_tsearch tsvector
);

CREATE INDEX abstracts_tsearch_idx ON abstracts USING GIST ( abstract_tsearch );

CREATE OR REPLACE FUNCTION abstracts_tsearch() RETURNS trigger AS '
  BEGIN
    NEW.abstract_tsearch = to_tsvector( 
      NEW.abstract_title || '' '' || 
      NEW.abstract_description );
    return NEW;
  END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER abstracts_tsearch
  BEFORE INSERT OR UPDATE ON abstracts
    FOR EACH ROW
      EXECUTE PROCEDURE abstracts_tsearch();


CREATE TABLE presenters (
  presenter_id serial PRIMARY KEY,
  abstract_id integer NOT NULL REFERENCES abstracts(abstract_id),
  presenter_first_name varchar,
  presenter_last_name varchar,
  presenter_email varchar,
  presenter_organization varchar,
  presenter_department varchar,
  presenter_type varchar
);

CREATE INDEX presenter_abstract_fkidx ON presenters ( abstract_id );

CREATE TABLE rankers (
  ranker_id varchar PRIMARY KEY,
  ranker_email varchar NOT NULL,
  ranker_timestamp timestamp NOT NULL DEFAULT now()
);

CREATE TABLE rankings (
  ranker_id varchar NOT NULL REFERENCES rankers(ranker_id),
  abstract_id integer NOT NULL REFERENCES abstracts(abstract_id),
  ranking_ip inet NOT NULL,
  ranking_timestamp timestamp NOT NULL DEFAULT now(),
  ranking_value integer NOT NULL,
  PRIMARY KEY(ranker_id, abstract_id)
);

CREATE INDEX ranking_abstract_idx ON rankings ( abstract_id );

CREATE OR REPLACE FUNCTION ranking_timestamp() RETURNS trigger AS '
  BEGIN
    NEW.ranking_timestamp = now();
    return NEW;
  END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER rankings_update_timestamp
   BEFORE UPDATE ON rankings
      FOR EACH ROW
          EXECUTE PROCEDURE ranking_timestamp();