CREATE TABLE preferences
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32),
  `value` VARCHAR(64)
) ENGINE=InnoDB;
CREATE UNIQUE INDEX preferences_name_unique ON preferences (`name`);

CREATE TABLE time_zones
(
  id INT(11) PRIMARY KEY NOT NULL,
  `name` VARCHAR(64) NOT NULL,
  `value` VARCHAR(32) NOT NULL
) ENGINE=InnoDB;
CREATE UNIQUE INDEX time_zone_name_unique ON time_zones (`name`);

INSERT INTO time_zones (id, `name`, `value`) VALUES (1, 'Eastern Time', 'EST');
INSERT INTO time_zones (id, `name`, `value`) VALUES (2, 'Central Time', 'America/Chicago');
INSERT INTO time_zones (id, `name`, `value`) VALUES (3, 'Mountain Time', 'America/Denver');
INSERT INTO time_zones (id, `name`, `value`) VALUES (4, 'Mountain Time (no DST)', 'America/Phoenix');
INSERT INTO time_zones (id, `name`, `value`) VALUES (5, 'Pacific Time', 'America/Los_Angeles');
INSERT INTO time_zones (id, `name`, `value`) VALUES (6, 'Alaska Time', 'America/Anchorage');
INSERT INTO time_zones (id, `name`, `value`) VALUES (7, 'Hawaii-Aleutian', 'America/Adak');
INSERT INTO time_zones (id, `name`, `value`) VALUES (8, 'Hawaii-Aleutian (no DST)', 'Pacific/Honolulu');

CREATE TABLE banned_hosts
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  ip VARCHAR(64) NOT NULL,
  expire DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE sites
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `number` CHAR(3) NOT NULL,
  `name` VARCHAR(32) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE UNIQUE INDEX `number` ON sites (`name`);

CREATE TABLE patients
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  site_id INT(11) NOT NULL,
  `number` CHAR(4) NOT NULL,
  CONSTRAINT patient_site_fk FOREIGN KEY (site_id) REFERENCES sites (id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE INDEX patient_site_idx ON patients (site_id);
CREATE UNIQUE INDEX patient_number_unique ON patients (`number`);

CREATE TABLE donors
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  site_id INT(11) NOT NULL,
  `number` CHAR(4) NOT NULL,
  CONSTRAINT donor_site_fk FOREIGN KEY (site_id) REFERENCES sites (id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE INDEX donor_site_idx ON donors (site_id);
CREATE UNIQUE INDEX donor_number_unique ON donors (`number`);

### Forms

CREATE TABLE `cases`
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  site_id INT(11) NOT NULL,
  donor_id INT(11) NOT NULL,
  patient_id INT(11) NULL DEFAULT NULL,
  `name` VARCHAR(32),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_valid TINYINT(4) DEFAULT '0' NOT NULL,
  is_reviewed TINYINT(4) DEFAULT '0' NOT NULL,
  is_monitored TINYINT(4) DEFAULT '0' NOT NULL,
  is_locked TINYINT(4) DEFAULT '0' NOT NULL,
  query_state TINYINT(4) DEFAULT '0' NOT NULL,
  sign_state TINYINT(4) DEFAULT '0' NOT NULL,
  in_use_by INT(11) NULL DEFAULT NULL,
  in_use_from DATETIME NULL DEFAULT NULL,
  CONSTRAINT case_site_fk FOREIGN KEY (site_id) REFERENCES sites (id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT case_donor_fk FOREIGN KEY (donor_id) REFERENCES donors (id) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT case_patient_fk FOREIGN KEY (patient_id) REFERENCES patients (id) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT case_in_use_fk FOREIGN KEY (in_use_by) REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE INDEX case_site_idx ON cases (site_id);
CREATE INDEX case_donor_idx ON cases (donor_id);
CREATE INDEX case_patient_idx ON cases (patient_id);
CREATE INDEX case_in_use_idx ON cases (in_use_by);
CREATE UNIQUE INDEX case_name_unique ON cases (`name`);
CREATE UNIQUE INDEX case_donor_patient_unique ON cases (donor_id, patient_id);

CREATE TABLE `forms`
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  case_id INT(11) NOT NULL,
  `name` VARCHAR(16) NOT NULL,
  is_reviewed TINYINT(4) DEFAULT '0' NOT NULL,
  is_monitored TINYINT(4) DEFAULT '0' NOT NULL,
  is_locked TINYINT(4) DEFAULT '0' NOT NULL,
  query_state TINYINT(4) DEFAULT '0' NOT NULL,
  sign_state TINYINT(4) DEFAULT NULL,
  entry_complete TINYINT(4) DEFAULT '0' NOT NULL,
  CONSTRAINT form_case_fk FOREIGN KEY (case_id) REFERENCES cases (id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE INDEX form_case_idx ON forms (case_id);
CREATE UNIQUE INDEX form_case_name_unique ON forms (case_id, `name`);

CREATE TABLE `fields`
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  case_id INT(11) NOT NULL,
  form_id INT(11) NOT NULL,
  `name` VARCHAR(16) NOT NULL,
  `value` VARCHAR(255),
  is_reviewed TINYINT(4) DEFAULT '0' NULL,
  is_monitored TINYINT(4) DEFAULT '0' NULL,
  CONSTRAINT field_case_fk FOREIGN KEY (case_id) REFERENCES cases (id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT field_form_fk FOREIGN KEY (form_id) REFERENCES forms (id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE INDEX field_case_idx ON `fields` (case_id);
CREATE INDEX field_form_idx ON `fields` (form_id);
CREATE UNIQUE INDEX field_name ON `fields` (`case_id`, `name`);

CREATE TABLE `audit`
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  case_id INT(11) NOT NULL,
  form_id INT(11) NOT NULL,
  field_id INT(11) NOT NULL,
  `value` VARCHAR(255) NOT NULL,
  updated_by INT(11) NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT audit_case_fk FOREIGN KEY (case_id) REFERENCES cases (id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT audit_form_fk FOREIGN KEY (form_id) REFERENCES forms (id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT audit_field_fk FOREIGN KEY (field_id) REFERENCES `fields` (id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE INDEX audit_case_idx ON audit (case_id);
CREATE INDEX audit_form_idx ON audit (form_id);
CREATE INDEX audit_field_idx ON audit (field_id);

CREATE TABLE `queries`
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  case_id INT(11) NOT NULL,
  form_id INT(11) NOT NULL,
  field_id INT(11) NOT NULL,
  created_by INT(11) NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_resolved TINYINT(4) NOT NULL DEFAULT '0',
  is_answered TINYINT(4) NOT NULL DEFAULT '0',
  message VARCHAR(255) NOT NULL,
  CONSTRAINT query_case_fk FOREIGN KEY (case_id) REFERENCES cases (id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT query_form_fk FOREIGN KEY (form_id) REFERENCES forms (id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT query_field_fk FOREIGN KEY (field_id) REFERENCES `fields` (id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT query_author_fk FOREIGN KEY (created_by) REFERENCES `users` (id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE INDEX query_case_idx ON queries (case_id);
CREATE INDEX query_form_idx ON queries (form_id);
CREATE INDEX query_field_idx ON queries (field_id);
CREATE INDEX query_author_idx ON queries (created_by);

CREATE TABLE `query_messages`
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  query_id INT(11) NOT NULL,
  created_by INT(11) NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  message VARCHAR(255) NOT NULL,
  CONSTRAINT query_message_query_fk FOREIGN KEY (query_id) REFERENCES queries (id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT query_message_author_fk FOREIGN KEY (created_by) REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;

### Document Management

CREATE TABLE `directories`
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  parent_id INT(11) NULL DEFAULT NULL,
  access SMALLINT(6) NOT NULL,
  `name` VARCHAR(64) NOT NULL,
  created_at DATETIME NOT NULL,
  created_by INT(11) NULL DEFAULT NULL,
  `type` VARCHAR(32),
  `readonly` TINYINT(4) NOT NULL DEFAULT '0',
  CONSTRAINT directory_created_fk FOREIGN KEY (created_by) REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE INDEX directory_created_idx ON directories (created_by);
CREATE INDEX directory_parent_idx ON directories (parent_id);
CREATE UNIQUE INDEX directory_parent_name_unique ON directories (parent_id, `name`);
ALTER TABLE directories ADD CONSTRAINT directory_parent_fk FOREIGN KEY (parent_id) REFERENCES directories (id) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE TABLE directory_permissions
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  directory_id INT(11) NOT NULL,
  site_id INT(11) NULL DEFAULT NULL,
  CONSTRAINT directory_permissions_dir_fk FOREIGN KEY (directory_id) REFERENCES directories (id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT directory_permissions_site_fk FOREIGN KEY (site_id) REFERENCES sites (id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE INDEX directory_permissions_dir_idx ON directory_permissions (directory_id);
CREATE INDEX directory_permissions_site_idx ON directory_permissions (site_id);

CREATE TABLE files
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  directory_id INT(11) NOT NULL,
  site_id INT(11) NULL DEFAULT NULL,
  type SMALLINT(6) NOT NULL,
  access SMALLINT(6) NOT NULL,
  `name` VARCHAR(64) NOT NULL,
  path VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by INT(11) NOT NULL,
  mime VARCHAR(255),
  CONSTRAINT file_directory_fk FOREIGN KEY (directory_id) REFERENCES directories (id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT file_site_fk FOREIGN KEY (site_id) REFERENCES sites (id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE INDEX file_directory_idx ON files (directory_id);
CREATE INDEX file_site_idx ON files (site_id);
CREATE UNIQUE INDEX file_directory_name_unique ON files (directory_id, `name`);

CREATE TABLE file_permissions
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  file_id INT(11) NOT NULL,
  site_id INT(11) NOT NULL,
  CONSTRAINT file_permissions_file_fk FOREIGN KEY (file_id) REFERENCES files (id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT file_permissions_site_fk FOREIGN KEY (site_id) REFERENCES sites (id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE INDEX file_permissions_file_idx ON file_permissions (file_id);
CREATE INDEX file_permissions_site_idx ON file_permissions (site_id);

CREATE TABLE form_signatures
(
  id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  form_id INT(11) NOT NULL,
  user_id INT(11) NOT NULL,
  type SMALLINT(6) NOT NULL,
  stamp VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT signature_form_fk FOREIGN KEY (form_id) REFERENCES forms (id) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT signature_user_fk FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE INDEX signature_form_idx ON form_signatures (form_id);
CREATE INDEX signature_user_idx ON form_signatures (user_id);
CREATE UNIQUE INDEX signature_unique ON form_signatures (form_id, user_id, type);
