text: -- MySQL DDL statements for the ohmage database
  --
  -- --------------------------------------------------------------------
  -- Assuming that the ohmage db is created prior to running this script
  -- CREATE DATABASE IF NOT EXISTS ohmage CHARACTER SET utf8 COLLATE utf8_general_ci;
  -- USE ohmage;
  --- --------------------------------------------------------------------
  
  -- --------------------------------------------------------------------
  -- A lookup table for the types of HTTP requests. 
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS audit_request_type (
    -- A unique key to reference the different types.
    id int unsigned NOT NULL auto_increment,
    -- The value for the HTTP request types.
    request_type varchar(8) NOT NULL,
    PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- A table to audit all of the transactions with the server.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS audit (
    -- A unique key for each request.
    id int unsigned NOT NULL auto_increment,
    -- The type of request this was, GET, POST, etc.
    request_type_id int unsigned NOT NULL,
    -- The URI portion of the URL for the request. There will always be a URI.
    uri text NOT NULL,
    -- The client parameter. If it is missing, it will be null.
    client text,
    -- The request ID generated by the NDC.
    request_id VARCHAR(255) NOT NULL,
    -- A specific identifier for devices.
    device_id text,
    -- The response we sent back to the client. If the request failed, we will
    -- record the entire failure message. If the request succeeded, we will only
    -- record "success".
    response text NOT NULL,
    -- A timestamp as recorded by a Calendar as soon as the request arrived.
    received_millis long NOT NULL,
    -- A timestamp as recorded by a Calendar as soon as the request was 
    -- completely responded to.
    respond_millis long NOT NULL,
    -- A database timestamp about the time at which this record was made.
    db_timestamp timestamp default current_timestamp NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT FOREIGN KEY (request_type_id) REFERENCES audit_request_type (id) ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- A mapping of extra keys to their values taken from the HTTP request
  -- headers.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS audit_extra (
    -- A unique key for this parameter mapping.
    id int unsigned NOT NULL auto_increment,
    -- A reference to the audit to which this parameter was a member.
    audit_id int unsigned NOT NULL,
    -- The parameter's key.
    extra_key text NOT NULL,
    -- The parameter's value.
    extra_value text NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT FOREIGN KEY (audit_id) REFERENCES audit (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- A mapping of parameter keys to their values linked to a request. 
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS audit_parameter (
    -- A unique key for this parameter mapping.
    id int unsigned NOT NULL auto_increment,
    -- A reference to the audit to which this parameter was a member.
    audit_id int unsigned NOT NULL,
    -- The parameter's key.
    param_key text NOT NULL,
    -- The parameter's value.
    param_value text NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT FOREIGN KEY (audit_id) REFERENCES audit (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- The class concept comes from Mobilize, but it can be used for any
  -- taxonomical grouping of users. 
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS class (
    id int unsigned NOT NULL auto_increment,
    urn varchar(255) NOT NULL,
    name varchar(255) NOT NULL,
    description text,
    creation_timestamp datetime DEFAULT null,
    last_modified_timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (urn)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Lookup table for the running states of a campaign.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS campaign_running_state (
    id int unsigned NOT NULL auto_increment,
    running_state varchar(50) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (running_state)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Lookup table for the privacy states of a campaign.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS campaign_privacy_state (
    id int unsigned NOT NULL auto_increment,
    privacy_state varchar(50) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (privacy_state)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- A campaign and its associated XML configuration.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS campaign (
    id int unsigned NOT NULL auto_increment,
    urn varchar(255) NOT NULL,
    name varchar(255) NOT NULL,
    description text,
    xml mediumtext NOT NULL,
    running_state_id int unsigned NOT NULL,
    privacy_state_id int unsigned NOT NULL,
    creation_timestamp datetime NOT NULL,
    last_modified_timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    icon_url varchar(255),
    authored_by varchar(255),
    PRIMARY KEY (id),
    UNIQUE (urn),
    CONSTRAINT FOREIGN KEY (running_state_id) REFERENCES campaign_running_state (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (privacy_state_id) REFERENCES campaign_privacy_state (id) ON DELETE CASCADE ON UPDATE CASCADE
  -- create an index on name?
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Bind campaigns to classes.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS campaign_class (
    id int unsigned NOT NULL auto_increment,
    campaign_id int unsigned NOT NULL,
    class_id int unsigned NOT NULL,
    last_modified_timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (campaign_id, class_id),
    CONSTRAINT FOREIGN KEY (campaign_id) REFERENCES campaign (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (class_id) REFERENCES class (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- -----------------------------------------------------------------------
  -- System users.
  -- -----------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS user (
    id int unsigned NOT NULL auto_increment,
    username varchar(25) NOT NULL,
    password varchar(60) NOT NULL,
    enabled bit NOT NULL,
    new_account bit NOT NULL,
    campaign_creation_privilege bit NOT NULL,
    class_creation_privilege bit NOT NULL DEFAULT FALSE,
    user_setup_privilege bit NOT NULL DEFAULT FALSE,
    email_address varchar(320),
    admin bit NOT NULL,
    creation_timestamp datetime DEFAULT null,
    last_modified_timestamp timestamp DEFAULT now() ON UPDATE now(),
    initial_password text,
    PRIMARY KEY (id),
    UNIQUE (username)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  -- ALTER TABLE ohmage.user ADD COLUMN rsview varchar(320) AFTER email_address;
  -- ---------------------------------------------------------------------
  -- Due to IRB standards, we store personally identifying information
  -- separately from the user's login credentials.
  -- ---------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS user_personal (
    id int unsigned NOT NULL auto_increment,
    user_id int unsigned NOT NULL,
    first_name varchar(255) NOT NULL,
    last_name varchar(255) NOT NULL,
    organization varchar(255) NOT NULL,
    personal_id varchar(255) NOT NULL,  -- this is e.g., the Mobilize student's student id
    last_modified_timestamp timestamp DEFAULT now() ON UPDATE now(),
    PRIMARY KEY (id),
    UNIQUE (user_id),
    UNIQUE (first_name, last_name, organization, personal_id), 
    CONSTRAINT FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Create the table responsible for user registration.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS user_registration(
      -- A unique key for each request.
      id int unsigned NOT NULL auto_increment,
      -- A reference to the user.
      user_id int unsigned NOT NULL,
      -- The registration ID.
      registration_id VARCHAR(128) NOT NULL,
      -- The time at which the registration request was made.
      request_timestamp BIGINT UNSIGNED NOT NULL,
      -- The time at which the registration was accepted.
      accepted_timestamp BIGINT UNSIGNED DEFAULT NULL,
      -- The ID is the primary key.
      PRIMARY KEY (id),
      -- Guarantees that multiple requests for the same user cannot exist.
      UNIQUE (user_id),
      -- Link the user table.
      CONSTRAINT user_id FOREIGN KEY (user_id) REFERENCES user (id) ON UPDATE CASCADE ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- User role lookup table.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS user_role (
    id tinyint unsigned NOT NULL auto_increment,
    role varchar(50) NOT NULL,
    PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Bind users to roles and campaigns. A user can have a different role
  -- for each campaign they belong to.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS user_role_campaign (
    id int unsigned NOT NULL auto_increment,
    user_id int unsigned NOT NULL,
    campaign_id int unsigned NOT NULL,
    user_role_id tinyint unsigned NOT NULL,
    last_modified_timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (user_id, campaign_id, user_role_id),
    CONSTRAINT FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (campaign_id) REFERENCES campaign (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (user_role_id) REFERENCES user_role (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Roles for a user within a class.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS user_class_role (
    id int unsigned NOT NULL auto_increment,
    role varchar(50) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (role)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Bind users to classes. 
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS user_class (
    id int unsigned NOT NULL auto_increment,
    user_id int unsigned NOT NULL,
    class_id int unsigned NOT NULL,
    user_class_role_id int unsigned NOT NULL,
    last_modified_timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (user_id, class_id),
    CONSTRAINT FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (class_id) REFERENCES class (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (user_class_role_id) REFERENCES user_class_role (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Add a default role to campaign class relationships.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS campaign_class_default_role (
    id int unsigned NOT NULL auto_increment,
    campaign_class_id int unsigned NOT NULL,
    user_class_role_id int unsigned NOT NULL,
    user_role_id tinyint unsigned NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (campaign_class_id, user_class_role_id, user_role_id),
    CONSTRAINT FOREIGN KEY (campaign_class_id) REFERENCES campaign_class (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (user_class_role_id) REFERENCES user_class_role (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (user_role_id) REFERENCES user_role (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Survey response privacy states.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS survey_response_privacy_state (
    id int unsigned NOT NULL auto_increment,
    privacy_state varchar(50) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (privacy_state)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Stores survey responses for a user in a campaign 
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS survey_response (
    id int unsigned NOT NULL auto_increment,
    uuid CHAR(36) NOT NULL UNIQUE,
    user_id int unsigned NOT NULL,
    campaign_id int unsigned NOT NULL,
    client tinytext NOT NULL,
    epoch_millis bigint unsigned NOT NULL, 
    phone_timezone varchar(32) NOT NULL,
    survey_id varchar(250) NOT NULL,    
    survey text CHARACTER SET utf8 NOT NULL,
    launch_context text,                -- trigger and other data
    location_status tinytext NOT NULL,  -- one of: unavailable, valid, stale, inaccurate 
    location text,                      -- JSON location data: longitude, latitude, accuracy, provider
    upload_timestamp datetime NOT NULL, -- the upload time based on the server time and timezone  
    last_modified_timestamp timestamp default current_timestamp on update current_timestamp,
    privacy_state_id int unsigned NOT NULL,
    PRIMARY KEY (id),
    KEY key_user_id (user_id),
    KEY key_campaign_id (campaign_id),
    CONSTRAINT FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE,    
    CONSTRAINT FOREIGN KEY (campaign_id) REFERENCES campaign (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (privacy_state_id) REFERENCES survey_response_privacy_state (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Stores individual prompt responses for a user in a campaign. Both
  -- the entire survey response and each prompt response in for a survey
  -- are stored.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS prompt_response (
    id int unsigned NOT NULL auto_increment,
    survey_response_id int unsigned NOT NULL,
    prompt_id varchar(250) NOT NULL,  -- a prompt id as defined in a configuration at the XPath //promptId
    prompt_type varchar(250) NOT NULL, -- a prompt type as defined in a configuration at the XPath //promptType
    repeatable_set_id varchar(250), -- a repeatable set id as defined in a configuration at the XPath //repeatableSetId
    repeatable_set_iteration tinyint unsigned,
    response text CHARACTER SET utf8 NOT NULL,
    audit_timestamp timestamp default current_timestamp on update current_timestamp,
    PRIMARY KEY (id),
    INDEX (survey_response_id),
    INDEX (prompt_id),
    INDEX response_image (response(36)),
    
    CONSTRAINT FOREIGN KEY (survey_response_id) REFERENCES survey_response (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Points a UUID to a URL of a media resource (such as an image). The  
  -- UUID is an implicit link into the prompt_response table. The privacy
  -- for images is handled by the link up to the survey_response via
  -- prompt_response.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS url_based_resource (
      id int unsigned NOT NULL auto_increment,
      user_id int unsigned NOT NULL,
      client tinytext NOT NULL,
      uuid char (36) NOT NULL, -- joined with prompt_response.response to retrieve survey context for an item
      url text,
      metadata text CHARACTER SET utf8 DEFAULT NULL,
      audit_timestamp timestamp default current_timestamp on update current_timestamp,
      processed BOOLEAN NOT NULL DEFAULT FALSE,
      UNIQUE (uuid), -- disallow duplicates and index on UUID
      PRIMARY KEY (id),
      CONSTRAINT FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Mobility privacy states.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS mobility_privacy_state (
    id int unsigned NOT NULL auto_increment,
    privacy_state varchar(50) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (privacy_state)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- High-frequency "mode only" mobility data. Mobility data is *not*
  -- linked to a campaign.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS mobility (
    id int unsigned NOT NULL auto_increment,
    uuid CHAR(36) NOT NULL UNIQUE,
    user_id int unsigned NOT NULL,
    client tinytext NOT NULL,
    epoch_millis bigint unsigned NOT NULL,
    phone_timezone varchar(32) NOT NULL,
    location_status tinytext NOT NULL,
    location text,
    mode varchar(30) NOT NULL,
    upload_timestamp datetime NOT NULL, -- the upload time based on the server time and timezone
    audit_timestamp timestamp default current_timestamp on update current_timestamp,
    privacy_state_id int unsigned NOT NULL,
    PRIMARY KEY (id),
    INDEX (uuid),
    INDEX index_time (epoch_millis),
    CONSTRAINT FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (privacy_state_id) REFERENCES mobility_privacy_state (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- High-frequency "mode + sensor data" mobility data. Mobility data is 
  -- *not* linked to a campaign.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS mobility_extended (
    id int unsigned NOT NULL auto_increment,
    mobility_id int unsigned NOT NULL,
    sensor_data text NOT NULL,
    features text NOT NULL,
    classifier_version tinytext NOT NULL,
    audit_timestamp timestamp default current_timestamp on update current_timestamp,
    PRIMARY KEY (id),
    CONSTRAINT FOREIGN KEY (mobility_id) REFERENCES mobility (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Preferences table to hold key-value pairs of items that need to be
  -- stored but we don't want to store in configuration files.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS preference (
    p_key varchar(50) NOT NULL,
    p_value text NOT NULL,
    UNIQUE unique_p_key (p_key)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Lookup table for the privacy states of a document.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS document_privacy_state (
    id int unsigned NOT NULL auto_increment,
    privacy_state varchar(50) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (privacy_state)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Lookup table for roles for a document.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS document_role (
    id int unsigned NOT NULL auto_increment,
    role varchar(50) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (role)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Documents associated with a campaign.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS document (
    id int unsigned NOT NULL auto_increment,
    uuid char(36) NOT NULL,
    name varchar(255) NOT NULL,
    description text,
    extension varchar(50),
    url text NOT NULL,
    size int unsigned NOT NULL,
    privacy_state_id int unsigned NOT NULL,
    last_modified_timestamp timestamp default current_timestamp on update current_timestamp,
    creation_timestamp datetime NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (uuid),
    CONSTRAINT FOREIGN KEY (privacy_state_id) REFERENCES document_privacy_state (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Link of documents to classes.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS document_class_role (
    id int unsigned NOT NULL auto_increment,
    document_id int unsigned NOT NULL,
    class_id int unsigned NOT NULL,
    document_role_id int unsigned NOT NULL,
    last_modified_timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (document_id, class_id),
    CONSTRAINT FOREIGN KEY (document_id) REFERENCES document (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (class_id) REFERENCES class (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Link of documents to campaigns.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS document_campaign_role (
    id int unsigned NOT NULL auto_increment,
    document_id int unsigned NOT NULL,
    campaign_id int unsigned NOT NULL,
    document_role_id int unsigned NOT NULL,
    last_modified_timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (document_id, campaign_id),
    CONSTRAINT FOREIGN KEY (document_id) REFERENCES document (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (campaign_id) REFERENCES campaign (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Link of documents to users.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS document_user_role (
    id int unsigned NOT NULL auto_increment,
    document_id int unsigned NOT NULL,
    user_id int unsigned NOT NULL,
    document_role_id int unsigned NOT NULL,
    last_modified_timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (document_id, user_id),
    CONSTRAINT FOREIGN KEY (document_id) REFERENCES document (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Audit trail for who created which document
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS document_user_creator (
    id int unsigned NOT NULL auto_increment,
    document_id int unsigned NOT NULL,
    username varchar(25) NOT NULL,
    last_modified_timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE (document_id),
    CONSTRAINT FOREIGN KEY (document_id) REFERENCES document (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Annotations (text blobs) for different system entities.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS annotation (
    id int unsigned NOT NULL auto_increment,
    uuid CHAR(36) NOT NULL UNIQUE,
    user_id int unsigned NOT NULL,
    epoch_millis bigint unsigned NOT NULL,
    timezone varchar(32) NOT NULL,
    client tinytext NOT NULL, 
    annotation text NOT NULL,
    last_modified_timestamp timestamp default current_timestamp on update current_timestamp,
    creation_timestamp datetime NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Link survey_responses to annotations.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS survey_response_annotation (
    id int unsigned NOT NULL auto_increment,
    survey_response_id int unsigned NOT NULL,
    annotation_id int unsigned NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT FOREIGN KEY (survey_response_id) REFERENCES survey_response (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (annotation_id) REFERENCES annotation (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Link prompt_responses to annotations.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS prompt_response_annotation (
    id int unsigned NOT NULL auto_increment,
    prompt_response_id int unsigned NOT NULL,
    annotation_id int unsigned NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT FOREIGN KEY (prompt_response_id) REFERENCES prompt_response (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (annotation_id) REFERENCES annotation (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Link campaigns to annotations.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS campaign_annotation (
    id int unsigned NOT NULL auto_increment,
    campaign_id int unsigned NOT NULL,
    annotation_id int unsigned NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT FOREIGN KEY (campaign_id) REFERENCES campaign (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (annotation_id) REFERENCES annotation (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- Link annotations to annotations.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS annotation_annotation (
    id int unsigned NOT NULL auto_increment,
    root_annotation_id int unsigned NOT NULL,
    annotation_id int unsigned NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT FOREIGN KEY (root_annotation_id) REFERENCES annotation (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (annotation_id) REFERENCES annotation (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- An observer's definition.
  -- --------------------------------------------------------------------
  -- Create the observer tables.
  CREATE TABLE IF NOT EXISTS observer (
      id int unsigned NOT NULL AUTO_INCREMENT,
      user_id int unsigned NOT NULL,
      observer_id varchar(255) NOT NULL,
      version bigint NOT NULL,
      name varchar(256) NOT NULL,
      description text NOT NULL,
      version_string varchar(32) NOT NULL,
      last_modified_timestamp timestamp DEFAULT now() ON UPDATE now(),
      PRIMARY KEY (id),
      UNIQUE KEY observer_unique_key_id_version (observer_id, version),
      KEY observer_key_observer_id (observer_id),
      KEY observer_key_user_id (user_id),
      CONSTRAINT observer_foreign_key_user_id 
         FOREIGN KEY (user_id) 
         REFERENCES user (id) 
         ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- A single stream's definition.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS observer_stream (
      id int unsigned NOT NULL AUTO_INCREMENT,
      stream_id varchar(255) NOT NULL,
      version bigint NOT NULL,
      name varchar(256) NOT NULL,
      description text NOT NULL,
      with_id boolean DEFAULT NULL,
      with_timestamp boolean DEFAULT NULL,
      with_location boolean DEFAULT NULL,
      stream_schema text NOT NULL,
      last_modified_timestamp timestamp DEFAULT now() ON UPDATE now(),
      PRIMARY KEY (id),
      KEY observer_stream_key_stream_id (stream_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- The link between an observer and a stream. This is a many to one 
  -- relationship because the observer's version may change, but the stream does
  -- not. Therefore, we need to allow one version of a stream to span multiple
  -- versions of its observer.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS observer_stream_link (
    id int(10) unsigned NOT NULL AUTO_INCREMENT,
    observer_id int(10) unsigned NOT NULL,
    observer_stream_id int(10) unsigned NOT NULL,
    PRIMARY KEY (id),
    KEY observer_stream_link_key_observer_id (observer_id),
    KEY observer_stream_link_key_stream_id (observer_stream_id),
    -- There should only be one instance of an observer ID/version pair to a 
    -- stream ID/version pair.
    UNIQUE KEY observer_stream_link_unique_key_observer_stream 
      (observer_id, observer_stream_id),
    CONSTRAINT observer_stream_link_foreign_key_observer_id
      FOREIGN KEY (observer_id)
      REFERENCES observer (id)
      ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT observer_stream_link_foreign_key_stream_id
      FOREIGN KEY (observer_id)
      REFERENCES observer (id)
      ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- The data collected by a stream.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS observer_stream_data (
    id int unsigned NOT NULL AUTO_INCREMENT,
    user_id int unsigned NOT NULL,
    observer_stream_link_id int unsigned NOT NULL,
    uid varchar(255) DEFAULT NULL,
    time bigint(20) DEFAULT NULL,
    time_offset bigint(20) DEFAULT NULL,
    time_adjusted bigint(20) DEFAULT NULL,
    time_zone varchar(32) DEFAULT NULL,
    location_timestamp varchar(64) DEFAULT NULL,
    location_latitude double DEFAULT NULL,
    location_longitude double DEFAULT NULL,
    location_accuracy double DEFAULT NULL,
    location_provider varchar(255) DEFAULT NULL,
    data longtext NOT NULL,
    last_modified_timestamp timestamp DEFAULT now() ON UPDATE now(),
    PRIMARY KEY (id),
    KEY observer_stream_data_key_observer_stream_link_id (observer_stream_link_id),
    KEY observer_stream_data_key_user_id (user_id),
    INDEX observer_stream_data_index_time (time),
    INDEX observer_stream_data_index_time_adjusted (time_adjusted),
    INDEX `observer_stream_data_query`
      (`user_id`,`observer_stream_link_id`,`time_adjusted`,`time`),
    INDEX `observer_stream_data_index_link_user_adjusted`
      (`observer_stream_link_id`,`user_id`,`time_adjusted`),
    INDEX `osd_duplicate_data_point_read`
      (`user_id`, `observer_stream_link_id`, `uid`),  
    CONSTRAINT observer_stream_data_foreign_key_user_id 
      FOREIGN KEY (user_id) 
      REFERENCES user (id) 
      ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT observer_stream_data_foreign_key_observer_stream_link_id 
      FOREIGN KEY (observer_stream_link_id) 
      REFERENCES observer_stream_link (id) 
      ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- A lookup table for survey IDs to their respective campaigns.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS `campaign_survey_lookup` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `survey_id` varchar(255) NOT NULL,
    `campaign_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `campaign_survey_lookup_index_survey_id` (`survey_id`),
    KEY `campaign_survey_lookup_fk_campaign_id` (`campaign_id`),
    CONSTRAINT `campaign_survey_lookup_fk_campaign_id`
      FOREIGN KEY (`campaign_id`)
      REFERENCES `campaign` (`id`)
      ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- A lookup table for prompt IDs to their respective campaigns.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS `campaign_prompt_lookup` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `prompt_id` varchar(255) NOT NULL,
    `campaign_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `campaign_prompt_lookup_index_prompt_id` (`prompt_id`),
    KEY `campaign_prompt_lookup_fk_campaign_id` (`campaign_id`),
    CONSTRAINT `campaign_prompt_lookup_fk_campaign_id`
      FOREIGN KEY (`campaign_id`)
      REFERENCES `campaign` (`id`)
      ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- A table to track campaign masks.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS `campaign_mask` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `assigner_user_id` int(10) unsigned NOT NULL,
    `assignee_user_id` int(10) unsigned NOT NULL,
    -- Class ID?
    `campaign_id` int(10) unsigned NOT NULL,
    `mask_id` varchar(36) NOT NULL,
    `creation_time` bigint(20) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `campaign_mask_index_mask_id` (`mask_id`),
    CONSTRAINT `campaign_mask_fk_assigner_user_id` FOREIGN KEY (`assigner_user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `campaign_mask_fk_assignee_user_id` FOREIGN KEY (`assignee_user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `campaign_mask_fk_campaign_id` FOREIGN KEY (`campaign_id`) REFERENCES `campaign` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- A lookup table for survey IDs in a campaign mask.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS `campaign_mask_survey_id` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `campaign_mask_id` int(10) unsigned NOT NULL,
    `survey_id` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `campaing_mask_unique_mask_survey` (`campaign_mask_id`,`survey_id`),
    CONSTRAINT `campaign_mask_fk_survey_id` FOREIGN KEY (`campaign_mask_id`) REFERENCES `campaign_mask` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- A table to hold OmH authentication/authorization information.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS `omh_authentication` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `domain` varchar(100) NOT NULL,
    `auth_key` varchar(100) NOT NULL,
    `auth_value` varchar(100) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `omh_authentication_unique_domain_key` (`domain`,`auth_key`),
    KEY `omh_authentication_index_domain` (`domain`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- The campaign mask table that includes both survey and prompt IDs.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS `campaign_mask_survey_prompt_map` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `campaign_mask_id` int(10) unsigned NOT NULL,
      `survey_id` varchar(255) NOT NULL,
      `prompt_id` varchar(255) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `campaing_mask_unique_mask_survey_prompt`
          (`campaign_mask_id`,`survey_id`, `prompt_id`),
      CONSTRAINT `campaign_mask_fk_survey_prompt_map`
          FOREIGN KEY (`campaign_mask_id`) REFERENCES `campaign_mask` (`id`)
          ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- --------------------------------------------------------------------
  -- The table to store the invalid data.
  -- --------------------------------------------------------------------
  CREATE TABLE IF NOT EXISTS `observer_stream_data_invalid` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `user_id` int(10) unsigned NOT NULL,
      `observer_id` int(10) unsigned NOT NULL,
      `time_recorded` bigint(20) NOT NULL,
      `point_index` int(20) unsigned NOT NULL,
      `reason` text NOT NULL,
      `data` longtext NOT NULL,
      `last_modified_timestamp` timestamp NOT NULL
          DEFAULT CURRENT_TIMESTAMP
          ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      CONSTRAINT `observer_stream_data_invalid_fk_user_id`
          FOREIGN KEY (`user_id`)
          REFERENCES `user` (`id`)
          ON DELETE CASCADE
          ON UPDATE CASCADE,
      CONSTRAINT `observer_stream_data_invalid_fk_observer_id`
        FOREIGN KEY (`observer_id`)
        REFERENCES `observer` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- ----------------------------------------------------------------------
  -- The table to store the user access request 
  -------------------------------------------------------------------------
  -- use status:varchar for simplicity. possible values: pending/approved/rejected
  CREATE TABLE IF NOT EXISTS access_request (
    id int unsigned NOT NULL auto_increment,
    uuid CHAR(36) NOT NULL UNIQUE,
    user_id int unsigned NOT NULL,
    email_address varchar(320) NOT NULL,
    type varchar(25) NOT NULL,  
    status varchar(25) NOT NULL DEFAULT "pending",  
    content mediumtext NOT NULL,
    creation_timestamp datetime NOT NULL, 
    last_modified_timestamp timestamp DEFAULT now() ON UPDATE now(),
    PRIMARY KEY (id),
    UNIQUE (uuid),
    INDEX (uuid),
    INDEX (user_id),
    CONSTRAINT FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  