USE studies;

DROP TABLE studies;
CREATE TABLE studies
(
  id                  INTEGER IDENTITY,
  title               VARCHAR(300),
  description         VARCHAR(4000),
  url                 VARCHAR(255),
  period              VARCHAR(300),
  dtstart             DATE,
  dtend               DATE,
  rpo                 CHAR(1),
  accrual             CHAR(1),
  layman_title        VARCHAR(300),
  layman_description  VARCHAR(4000),
  layman_categories   VARCHAR(300),
  layman_keywords     VARCHAR(300),
  expiration          DATE,
  irb                 CHAR(1),
  irb_study_code      VARCHAR(20),
  study_plan          VARCHAR(4000),
  study_purpose       VARCHAR(2000),
  keywords            VARCHAR(300),
  PRIMARY KEY (id)
);

DROP TABLE grant_amounts;
CREATE TABLE grant_amounts
(
  id        INTEGER IDENTITY,
  study_id  INTEGER,
  currency  VARCHAR(15),
  amount    FLOAT(2),
  PRIMARY KEY (id)
);

DROP TABLE entities;
CREATE TABLE entities
(
  id           INTEGER IDENTITY,
  entity_type  VARCHAR(10),
  study_id     INTEGER,
  tel          VARCHAR(25),
  email        VARCHAR(300),
  url          VARCHAR(255),
  org          VARCHAR(300),
  fn           VARCHAR(300),
  PRIMARY KEY (id)
);


DROP TABLE addresses;
CREATE TABLE addresses
(
  id                INTEGER IDENTITY,
  entity_id         INTEGER,
  staffer_id        INTEGER,
  post_office_box   VARCHAR(10),
  street_address    VARCHAR(300),
  extended_address  VARCHAR(300),
  locality          VARCHAR(30),
  region            VARCHAR(30),
  postal_code       VARCHAR(10),
  country_name      VARCHAR(30),
  PRIMARY KEY (id)
);

DROP TABLE focus_regions;
CREATE TABLE focus_regions
(
  id           INTEGER IDENTITY,
  study_id     INTEGER,
  locality     VARCHAR(30),
  region       VARCHAR(30),
  postal_code  VARCHAR(10),
  country      VARCHAR(30),
  tel          VARCHAR(10),
  email        VARCHAR(300),
  url          VARCHAR(300),
  org          VARCHAR(300),
  fn           VARCHAR(300),
  PRIMARY KEY (id)
);

DROP TABLE staffers;
CREATE TABLE staffers
(
  id           INTEGER IDENTITY,
  study_id     INTEGER,
  ufid         VARCHAR(30),
  involvement  VARCHAR(30),
  fn           VARCHAR(30),
  PRIMARY KEY (id)
);