create database invitotron;
use invitotron;

CREATE TABLE email_addresses (
  email_address_id smallint(6) NOT NULL auto_increment,
  name varchar(255) default NULL,
  email_address varchar(255) default NULL,
  email_alias1 varchar(255) default NULL,
  email_alias2 varchar(255) default NULL,
  email_alias3 varchar(255) default NULL,
  preferences set('showname','multipartmail') default NULL,
  PRIMARY KEY  (email_address_id),
  UNIQUE KEY mailindex (email_address),
  KEY nameindex (name)
) TYPE=MyISAM;

INSERT INTO email_addresses VALUES (1,'Marty Smyth','martian@midgard.org',NULL,NULL,NULL,'showname,multipartmail');


CREATE TABLE invitation_message_sent (
  invitation_message_id int(10) unsigned NOT NULL default '0',
  email_address_id smallint(6) NOT NULL default '0',
  mail_sent_date timestamp(14) NOT NULL,
  PRIMARY KEY  (invitation_message_id,email_address_id)
) TYPE=MyISAM;


CREATE TABLE invitation_messages (
  invitation_message_id int(10) unsigned NOT NULL auto_increment,
  invitation_id int(10) unsigned NOT NULL default '0',
  message_date timestamp(14) NOT NULL,
  subjectline varchar(200) default NULL,
  message text,
  send_to enum('All','Yes','No','Maybe','No Response','Not No','Not No Response','Undecided','Yes/Maybe') default 'All',
  PRIMARY KEY  (invitation_message_id),
  KEY invite_message_index (invitation_id)
) TYPE=MyISAM;


CREATE TABLE invitations (
  invitation_id int(10) unsigned NOT NULL auto_increment,
  owner smallint(6) default NULL,
  name varchar(255) default NULL,
  event_date datetime default NULL,
  ready enum('N','Y') default 'N',
  active enum('N','Y') default 'Y',
  PRIMARY KEY  (invitation_id)
) TYPE=MyISAM;


CREATE TABLE invitees (
  invitation_id int(10) unsigned NOT NULL default '0',
  email_address_id smallint(6) NOT NULL default '0',
  response enum('No Response','No','Maybe','Yes') default 'No Response',
  guestcount tinyint(4) default '1',
  mail_sent enum('N','Y') default 'N',
  invite_map_id varchar(10) default NULL,
  added_by smallint(6) default NULL,
  last_updated timestamp(14) NOT NULL,
  comments varchar(255) default NULL,
  PRIMARY KEY  (invitation_id,email_address_id),
  UNIQUE KEY mapid (invite_map_id)
) TYPE=MyISAM;


CREATE TABLE polls (
  poll_id int(11) NOT NULL auto_increment,
  invitation_id int(10) unsigned NOT NULL default '0',
  question varchar(255) default NULL,
  question_type enum('multichoice','shortanswer','yesno') default NULL,
  answers mediumblob,
  PRIMARY KEY  (poll_id)
) TYPE=MyISAM;


CREATE TABLE poll_results (
  poll_id int(11) NOT NULL default '0',
  email_address_id smallint(6) NOT NULL default '0',
  answer_choice tinyint(4) default NULL,
  answer_text varchar(255) default NULL,
  PRIMARY KEY  (poll_id,email_address_id)
) TYPE=MyISAM;


CREATE TABLE users (
  userid smallint(6) NOT NULL auto_increment,
  name varchar(255) default NULL,
  login varchar(20) NOT NULL default '',
  password varchar(16) binary NOT NULL default '****************',
  email_address_id smallint(6) default NULL,
  perms set('view','browse','create','admin') default NULL,
  PRIMARY KEY  (userid),
  UNIQUE KEY login_index (login)
) TYPE=MyISAM;

INSERT INTO users VALUES (1,'Marty Smyth','martian',PASSWORD('drowssap'),1,'view,browse,create,admin');


# There are more appropriate commands to do this, but I can't remember them.
# This should work, anyway.

use mysql;

INSERT INTO user VALUES ('%','invitotron',PASSWORD('drowssap'),'N','N','N','N','N','N','N','N','N','N','N','N','N','N');
INSERT INTO db VALUES ('%','invitotron','invitotron','Y','Y','Y','Y','N','N','N','N','N','Y');
INSERT INTO tables_priv VALUES ('%','mysql','invitotron','invitotron','root@localhost',NULL,'Insert,Delete,Create,Alter','');

flush privileges;