CREATE TABLE `events` (
  `id` int(11) NOT NULL,
  `title` text NOT NULL,
  `location` text NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `source` int(11) NOT NULL,
  `added_by` int(11) NOT NULL,
  `description` mediumtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `event_keyword` (
  `event` int(11) NOT NULL,
  `keyword` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `keywords` (
  `id` int(11) NOT NULL,
  `keyword` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sources` (
  `id` int(11) NOT NULL,
  `source` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` mediumtext NOT NULL,
  `email` varchar(255) NOT NULL,
  `pgp_fingerprint` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `events`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `title_2` (`title`(255),`start_date`),
  ADD KEY `start_date` (`start_date`),
  ADD KEY `end_date` (`end_date`),
  ADD KEY `source` (`source`),
  ADD KEY `added_by` (`added_by`);
ALTER TABLE `events` ADD FULLTEXT KEY `location` (`location`);
ALTER TABLE `events` ADD FULLTEXT KEY `title` (`title`);
ALTER TABLE `events` ADD FULLTEXT KEY `description` (`description`);

ALTER TABLE `event_keyword`
  ADD UNIQUE KEY `event_2` (`event`,`keyword`),
  ADD KEY `event` (`event`),
  ADD KEY `keyword` (`keyword`);

ALTER TABLE `keywords`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `keyword` (`keyword`(127)) USING BTREE;
ALTER TABLE `keywords` ADD FULLTEXT KEY `keyword_2` (`keyword`);

ALTER TABLE `sources`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `sources` ADD FULLTEXT KEY `source` (`source`);

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `name` (`name`(127));


ALTER TABLE `events`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `keywords`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `sources`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `events`
  ADD CONSTRAINT `events_ibfk_1` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `events_ibfk_2` FOREIGN KEY (`source`) REFERENCES `sources` (`id`);

ALTER TABLE `event_keyword`
  ADD CONSTRAINT `event_keyword_ibfk_1` FOREIGN KEY (`event`) REFERENCES `events` (`id`),
  ADD CONSTRAINT `event_keyword_ibfk_2` FOREIGN KEY (`keyword`) REFERENCES `keywords` (`id`);