

-- admin users

CREATE TABLE `admin_users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  `email` varchar(128) NOT NULL DEFAULT '',
  `password_hash` char(60) NOT NULL DEFAULT '',
  `reset_token` char(32) DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;


-- blocklist stuff

CREATE TABLE `blocklist` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `twitter_id` bigint(20) NOT NULL,
  `screen_name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `twitter_uid` (`twitter_id`),
  KEY `screen_name` (`screen_name`)
) ENGINE=MyISAM AUTO_INCREMENT=10809 DEFAULT CHARSET=utf8;

-- subsidized pass form field, yes/no toggles

ALTER TABLE `surveys` ADD COLUMN `diversity_checked` TINYINT(1) NOT NULL DEFAULT 0 AFTER `q3`;
ALTER TABLE `surveys` ADD COLUMN `subsidized_checked` TINYINT(1) NOT NULL DEFAULT 0 AFTER `diversity`;
ALTER TABLE `surveys` ADD COLUMN `subsidized` TEXT NULL AFTER `subsidized_checked`;

-- audit table

CREATE TABLE `audit` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `admin_user_id` int(10) NOT NULL,
  `survey_id` int(10) NOT NULL,
  `action` text COLLATE utf8_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `admin_user_id` (`admin_user_id`),
  KEY `survey_id` (`survey_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- admin level column

ALTER TABLE `admin_users` ADD COLUMN `level` INT NOT NULL DEFAULT 100;

-- diversity votes tables

CREATE TABLE `diversity_votes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `advisor_id` int(10) unsigned NOT NULL,
  `survey_id` int(10) NOT NULL,
  `decision` varchar(20) NOT NULL DEFAULT 'rejected',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `advisor_survey` (`advisor_id`,`survey_id`),
  KEY `advisor` (`advisor_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- attendee fields

ALTER TABLE `users` ADD COLUMN `invited_to_slack` TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE `users` ADD COLUMN `list_in_directory` TINYINT(1) NOT NULL DEFAULT 1;

-- attendee accessibility and special requests

ALTER TABLE `registrations` ADD COLUMN `accessibility` TEXT NULL AFTER `shirt_size`;
ALTER TABLE `registrations` ADD COLUMN `childcare_checked` TINYINT(1) NULL DEFAULT 0 AFTER `accessibility`;
ALTER TABLE `registrations` ADD COLUMN `childcare` TEXT NULL AFTER `childcare_checked`;
ALTER TABLE `registrations` ADD COLUMN `bike_checked` TINYINT(1) NULL DEFAULT 0 AFTER `childcare`;
ALTER TABLE `registrations` ADD COLUMN `dietary` TEXT NULL AFTER `bike_checked`;


-- 11 July 2016

DROP INDEX `twitter_uid` ON `blocklist`;
ALTER TABLE `blocklist` ADD COLUMN `type` VARCHAR(15) NULL;
UPDATE `blocklist` SET `type` = 'auto';
CREATE UNIQUE INDEX `twid_type` ON `blocklist` (`twitter_id`, `type`);
ALTER TABLE `blocklist` ADD COLUMN `last_updated` DATETIME NULL;


ALTER TABLE `guestpass` ADD COLUMN `year` SMALLINT NULL AFTER `token`;
UPDATE `guestpass` SET `year` = EXTRACT(YEAR FROM `created_at`);



-- 12 July

ALTER TABLE users ADD COLUMN `partner_email` VARCHAR(255) AFTER `partner_twitter_username`

-- 16 july

ALTER TABLE surveys ADD COLUMN `purchasable_until` DATETIME NULL AFTER `notified`;
