[
	{
		"name": "instruments",
		"columns": [
			{
				"name": "id",
				"comment": "The instrument ID for referring to for updating and deleting.",
				"type": "integer",
				"options": { "unsigned": true, "notnull": true, "autoincrement": true }
			},
			{
				"name": "name",
				"comment": "The instrument name.",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			},
			{
				"name": "slug",
                                 "comment": "The instrument slug as a parameter for specific instrument endpoints.",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			},
			{
				"name": "description",
				"comment": "The instrument description.",
				"type": "text",
				"options": { "notnull": true }
			},
			{
				"name": "creator",
				"comment": "The actor of the analytics instrument creator.",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			},
			{
				"name": "purpose",
				"comment": "The purpose of the analytics instrument.",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			},
			{
				"name": "created_at",
				"type": "mwtimestamp",
				"options": { "notnull": true }
			},
			{
				"name": "updated_at",
				"type": "mwtimestamp",
				"options": { "notnull": true }
			},
			{
				"name": "start_date",
				"type": "mwtimestamp",
				"options": { "notnull": true }
			},
			{
				"name": "end_date",
				"type": "mwtimestamp",
				"options": { "notnull": false }
			},
			{
				"name": "task",
				"comment": "The Phabricator task id.",
				"type": "text",
				"options": { "notnull": true }
			},
			{
				"name": "compliance_requirements",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			},
			{
				"name": "sample_rate",
				"type": "mwtinyint",
				"options": { "notnull": true, "unsigned": true, "default": 0 }
			},
			{
				"name": "sample_unit",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			},
			{
				"name": "contextual_attributes",
				"type": "text",
				"options": { "notnull": true }
			},
			{
				"name": "environments",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			},
			{
				"name": "security_legal",
				"comment": "A link to documentation including review and approval from Security and Legal.",
				"type": "text",
				"options": { "notnull": true }
			},
		],
		"indexes": [
			{
				"name": "created_at",
				"comment": "Created sort",
				"columns": [ "created_at" ],
				"unique": false
			},
			{
				"name": "creator",
				"comment": "Creator sort",
				"columns": [ "creator", "updated_at" ],
				"unique": false
			},
			{
				"name": "updated_at",
				"comment": "Default sort",
				"columns": [ "updated_at" ],
				"unique": false
			}
		],
		"pk": [ "id" ]
	},
	{
		"name": "instrument_sample_rates",
		"columns": [
			{
				"name": "id",
				"comment": "The instrument sample rate ID for referring to for overriding sample rate.",
				"type": "integer",
				"options": { "unsigned": true, "notnull": true, "autoincrement": true }
			},
			{
				"name": "instrument_id",
				"comment": "The analytics instrument ID for referring to for foreign key constraint.",
				"type": "integer",
				"options": { "unsigned": true, "notnull": true }
			},
			{
				"name": "dblist",
				"comment": "Wiki that the stream applies to for overriding sample rate/unit.",
				"type": "binary",
				"options": { "notnull": true, "length": 255 }
			},
			{
				"name": "sample_rate",
				"type": "mwtinyint",
				"options": { "notnull": true, "unsigned": true, "default": 0 }
			},
		],
		"indexes": [],
		"pk": [ "id" ]
	}
]

CREATE DATABASE IF NOT EXISTS mpic;

GRANT ALL PRIVILEGES ON mpic.* TO 'maria'@'%';

USE mpic;

CREATE TABLE IF NOT EXISTS instruments (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    description TEXT,
    creator VARCHAR(255) NOT NULL,
    owner VARCHAR(255) NOT NULL,
    purpose VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    end_date TIMESTAMP,
    task TEXT NOT NULL,
    compliance_requirements SET('legal', 'gdpr') NOT NULL,
    sample_unit VARCHAR(255) NOT NULL,
    sample_rate FLOAT NOT NULL,
    contextual_attributes SET(
        'agent_app_install_id',
        'agent_client_platform',
        'agent_client_platform_family',
        'page_id',
        'page_title',
        'page_namespace',
        'page_namespace_name',
        'page_revision_id',
        'page_wikidata_id',
        'page_wikidata_qid',
        'page_content_language',
        'page_is_redirect',
        'page_user_groups_allowed_to_move',
        'page_user_groups_allowed_to_edit',
        'mediawiki_skin',
        'mediawiki_version',
        'mediawiki_is_production',
        'mediawiki_is_debug_mode',
        'mediawiki_database',
        'mediawiki_site_content_language',
        'mediawiki_site_content_language_variant',
        'performer_is_logged_in',
        'performer_id',
        'performer_name',
        'performer_session_id',
        'performer_pageview_id',
        'performer_groups',
        'performer_is_bot',
        'performer_language',
        'performer_language_variant',
        'performer_can_probably_edit_page',
        'performer_edit_count',
        'performer_edit_count_bucket',
        'performer_registration_dt'
    ) NOT NULL,
    environments SET('development', 'staging', 'production', 'external') NOT NULL,
    security_legal TEXT,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS instrument_sample_rates (
    instrument_id INT UNSIGNED NOT NULL,
    dblist VARCHAR(255) NOT NULL,
    sample_rate FLOAT NOT NULL,
    constraint fk_de_type
    foreign key(instrument_id)
    references instruments (id)
);

task VARCHAR(1000) NOT NULL,
security_legal_review VARCHAR(1000) NOT NULL,

status VARCHAR(255) NOT NULL,

CREATE TABLE IF NOT EXISTS instruments (
    ...
    contextual_attributes BIGINT NOT NULL
    ...
);

CREATE TABLE IF NOT EXISTS contextual_attributes (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    attribute VARCHAR(255) NOT NULL,
    bitmaskvalue INT NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO contextual_attributes (attribute, bitmaskvalue) VALUES ('agent_app_install_id', 1);
INSERT INTO contextual_attributes (attribute, bitmaskvalue) VALUES ('agent_client_platform', 2);
INSERT INTO contextual_attributes (attribute, bitmaskvalue) VALUES ('agent_client_platform_family', 4);
INSERT INTO contextual_attributes (attribute, bitmaskvalue) VALUES ('page_id', 8);
INSERT INTO contextual_attributes (attribute, bitmaskvalue) VALUES ('page_title', 16);
INSERT INTO contextual_attributes (attribute, bitmaskvalue) VALUES ('page_namespace', 32);
INSERT INTO contextual_attributes (attribute, bitmaskvalue) VALUES ('page_namespace_name', 64);
INSERT INTO contextual_attributes (attribute, bitmaskvalue) VALUES ('page_revision_id', 128);
INSERT INTO contextual_attributes (attribute, bitmaskvalue) VALUES (<another attribute>, <increasing powers of 2>);
...

/* Then inserting into the instrument table with say 3 contextual attributes would look like this? */
INSERT INTO instruments (... contextual_attributes ...) VALUES (... 131  ...);
/* 131 in binary equals 00000000 00000000 10000011 meaning bit 1, 2, and 8 are included ?  i.e. agent_app_install_id, agent_client_platform, and page_revision_id ? */

/* another example */
INSERT INTO instruments (... contextual_attributes ...) VALUES (... 80379  ...);
/* 80379 in binary equals 01010101 00111001 11111011  << idk what this means - does this mean all 8 if we use inclusion vs intersection? */

CREATE TABLE IF NOT EXISTS instruments (
    /* ... */
    contextual_attributes BIGINT NOT NULL
    /* ... */
);

const APP_AGENT_INSTALL_ID = 1;
const APP_AGENT_CLIENT_PLATFORM = 2;
const APP_AGENT_CLIENT_PLATFORM_FAMILY = 4;
const PAGE_ID = 8;

const _CONTEXT_ATTRIBUTES = [
        'APP_AGENT_INSTALL_ID',
        'APP_AGENT_CLIENT_PLATFORM',
        'APP_AGENT_CLIENT_PLATFORM_FAMILY',
        'PAGE_ID',
];

class ContextAttributes {
  /**
   * Encoded a list of context attributes.
   *
   * This method is expected to be used when processing raw HTML form input.
   *
   * @param {number[]} raw
   * @return {number} The encoded context attributes ready to insert into the backing store
   */
  encode( raw ) {
    return contextAttributes.reduce(
      ( result, contextAttribute ) => result + contextAttribute,
      0
    );
  }

  /**
   * @param {number} encoded
   * @param {number} contextAttribute
   *
   * @example
   * if ( ContextAttributes.hasContextAttribute( fromDB, APP_AGENT_INSTALL_ID ) ) {
   *   /* ... */
   * }
   */
  hasContextAttribute( encoded, contextAttribute ) {
    return Boolean( encoded & contextAttribute );
  }

  /**
   * Decodes the encoded context attributes to a list of names of attributes.
   *
   * This method is only expected to be used when debugging.
   *
   * @internal
   *
   * @param {number} encoded
   * @return {string[]}
   */
  decode( encoded ) {
    return _CONTEXT_ATTRIBUTES.reduce(
      ( result, contextAttribute, i ) => {
        if ( encoded & ( 2 ** i ) ) {
          result.push( contextAttribute );
        }

        return result;
      };
    );
  }
}

CREATE TABLE IF NOT EXISTS instruments (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    description TEXT,
    creator VARCHAR(255) NOT NULL,
    owner VARCHAR(255) NOT NULL,
    purpose VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    end_date TIMESTAMP,
    task VARCHAR(1000) NOT NULL,
    compliance_requirements SET('legal', 'gdpr') NOT NULL,
    sample_unit VARCHAR(255) NOT NULL,
    sample_rate FLOAT NOT NULL,
    environments SET('development', 'staging', 'production', 'external') NOT NULL,
    security_legal_review VARCHAR(1000) NOT NULL,
    status VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS instrument_sample_rates (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    instrument_id INT UNSIGNED NOT NULL,
    dblist VARCHAR(255) NOT NULL,
    sample_rate FLOAT NOT NULL,
    constraint fk_de_type
    foreign key(instrument_id)
    references instruments (id)
);

CREATE TABLE IF NOT EXISTS contextual_attributes (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    contextual_attribute_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS instrument_contextual_attribute_lookup (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    instrument_id INT UNSIGNED NOT NULL,
    contextual_attribute_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO contextual_attributes (contextual_attribute_name) VALUES
 ('agent_app_install_id'),
 ('agent_client_platform'),
 ('agent_client_platform_family'),
 ('page_id'),
 ('page_title'),
 ('page_namespace'),
 ('page_namespace_name'),
 ('page_revision_id'),
 ('page_wikidata_id'),
 ('page_wikidata_qid'),
 ('page_content_language'),
 ('page_is_redirect'),
 ('page_user_groups_allowed_to_move'),
 ('page_user_groups_allowed_to_edit'),
 ('mediawiki_skin'),
 ('mediawiki_version'),
 ('mediawiki_is_production'),
 ('mediawiki_is_debug_mode'),
 ('mediawiki_database'),
 ('mediawiki_site_content_language'),
 ('mediawiki_site_content_language_variant'),
 ('performer_is_logged_in'),
 ('performer_id'),
 ('performer_name'),
 ('performer_session_id'),
 ('performer_pageview_id'),
 ('performer_groups'),
 ('performer_is_bot'),
 ('performer_language'),
 ('performer_language_variant'),
 ('performer_can_probably_edit_page'),
 ('performer_edit_count'),
 ('performer_edit_count_bucket'),
 ('performer_registration_dt');