CREATE TABLE IF NOT EXISTS `Events_RSVP` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `event_ID` int(10) unsigned NOT NULL,
  `user_ID` int(10) unsigned NOT NULL,
  `comment` text collate latin1_german2_ci NOT NULL,
  `TS` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `INTERSECT` (`event_ID`,`user_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=19 ;



CREATE TABLE IF NOT EXISTS `Forum_Topics` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `user_ID` int(11) NOT NULL,
  `topic` varchar(255) collate latin1_german2_ci NOT NULL,
  `TS` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `INTERSECT` (`topic`,`user_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=13 ;



CREATE TABLE IF NOT EXISTS `Representatives` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `district_ID` int(10) unsigned NOT NULL,
  `gov_track_ID` int(11) default NULL COMMENT 'All of this data is from Govtrack, this is the internal ID that govtrack uses',
  `first_name` varchar(255) collate latin1_german2_ci NOT NULL,
  `last_name` varchar(255) collate latin1_german2_ci NOT NULL,
  `middle_name` varchar(255) collate latin1_german2_ci default NULL,
  `gender` char(1) collate latin1_german2_ci NOT NULL,
  `birthday` date default NULL,
  `party` varchar(255) collate latin1_german2_ci default NULL COMMENT 'This could be another table, but it''s way faster to put the party in this way rather than always having to reference the other table that will mostly just be Republican and Democrat anyway...',
  `party_abbreviated` varchar(1) collate latin1_german2_ci default NULL,
  `religion` varchar(255) collate latin1_german2_ci default NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `email` varchar(255) collate latin1_german2_ci default NULL,
  `official_url` varchar(255) collate latin1_german2_ci NOT NULL,
  `official_rss_url` varchar(255) collate latin1_german2_ci default NULL,
  `congress_office_address` varchar(255) collate latin1_german2_ci default NULL,
  `phone` int(10) default NULL,
  `contact_form_url` varchar(255) collate latin1_german2_ci default NULL,
  `twitter_ID` varchar(255) collate latin1_german2_ci default NULL,
  `youtube_url` varchar(255) collate latin1_german2_ci default NULL,
  `support_flag` int(11) NOT NULL default '0',
  `support_status` varchar(255) collate latin1_german2_ci NOT NULL default 'Not a supporter of single-payer',
  `is_senator_flag` int(11) NOT NULL default '0',
  `is_current_flag` int(1) default '0',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `INTERSECT` (`first_name`,`last_name`,`end_date`,`district_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci COMMENT='Rather misleading, includes senators and congresspeople' AUTO_INCREMENT=40854 ;



CREATE TABLE IF NOT EXISTS `Users` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `district_ID` int(11) NOT NULL,
  `first_name` varchar(255) collate latin1_german2_ci NOT NULL,
  `last_name` varchar(255) collate latin1_german2_ci NOT NULL,
  `email` varchar(255) collate latin1_german2_ci NOT NULL,
  `password` varchar(34) character set latin1 collate latin1_bin NOT NULL,
  `zip` int(9) NOT NULL,
  `phone` bigint(10) NOT NULL,
  `city` varchar(255) collate latin1_german2_ci NOT NULL,
  `state` varchar(255) collate latin1_german2_ci NOT NULL,
  `join_TS` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `photo_URL` varchar(255) collate latin1_german2_ci default NULL,
  `email_sent_flag` int(1) NOT NULL default '0',
  `confirmed_flag` int(1) NOT NULL default '0',
  `admin_flag` int(1) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `INTERSECT` (`district_ID`,`first_name`,`last_name`,`email`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=2 ;


CREATE TABLE IF NOT EXISTS `Users_Invites` (
  `ID` int(11) NOT NULL auto_increment,
  `user_ID` int(11) NOT NULL,
  `to_name` varchar(255) collate latin1_german2_ci NOT NULL,
  `to_email` varchar(255) collate latin1_german2_ci NOT NULL,
  `subject` varchar(255) collate latin1_german2_ci NOT NULL,
  `open_flag` int(1) NOT NULL default '0',
  `TS` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `to_email` (`to_email`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1 ;




CREATE TABLE IF NOT EXISTS `User_Messages` (
  `ID` int(11) NOT NULL auto_increment,
  `from_user_ID` int(11) NOT NULL,
  `to_user_ID` int(11) NOT NULL,
  `open_flag` int(11) NOT NULL default '0',
  `TS` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `subject` varchar(100) collate latin1_german2_ci NOT NULL,
  `body` text collate latin1_german2_ci NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `INTERSECT` (`from_user_ID`,`to_user_ID`,`subject`,`TS`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1 ;



CREATE TABLE IF NOT EXISTS `User_News` (
  `ID` int(11) NOT NULL auto_increment,
  `user_ID` int(11) NOT NULL,
  `title` varchar(255) collate latin1_german2_ci NOT NULL,
  `excerpt` varchar(201) collate latin1_german2_ci NOT NULL COMMENT 'Users are limited to 200 characters for their news excerpt. This is highly arbitrary and could be changed later.',
  `url` varchar(255) collate latin1_german2_ci NOT NULL,
  `TS` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `INTERSECT` (`excerpt`,`url`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1;