create database if not exists epapertrail;
use epapertrail;

create table if not exists bill (
       id    	    	int auto_increment primary key,
       congress		int,
       billtype		varchar(2),
       number		int,
       unique (congress, billtype, number),
       introduced	datetime,
       sponsor_rep_id	int
) type=MyISAM;

create table if not exists bill_title (
       id    	    	int auto_increment primary key,
       bill_id	        int,
       title		text,
       unique(bill_id, title(320)),
       fulltext(title)
) type=MyISAM;

create table if not exists bill_sponsor (
       bill_id	    	int,
       rep_id		int,
       joined		datetime,
       primary key (bill_id, rep_id)
) type=MyISAM;

create table if not exists amendment (
       id    	    	int auto_increment primary key,
       congress		int,
       chamber		char(1),
       number		int,
       bill_id		int,
       sequence		int,
       offered		datetime,
       sponsor_rep_id	int,
       description	text,
       unique(congress, chamber, number),
       fulltext(description)
) type=MyISAM;

create table if not exists record (
       id    	    	int auto_increment primary key,
       congress		int,
       chamber		char(1),
       sequence		int,
       occurred		datetime,
       speaker_rep_id	int,
       bill_id		int,
       topic		text,
       speech		longtext,
       unique(congress, chamber, sequence, occurred, speaker_rep_id, topic(300)),
       fulltext(topic),
       fulltext(speech)
) type=MyISAM;

create table if not exists vote (
       id    	    	int auto_increment primary key,
       congress	    	int,
       session		int,
       chamber		char(1),
       number		int,
       unique (congress, session, chamber, number),
       bill_id		varchar(255),
       amendment_id    	varchar(255),
       question	    	text,
       type	    	varchar(255),
       required		varchar(255),
       result	    	varchar(255),
       votedate	    	datetime
) type=MyISAM;

create table if not exists rep (
       id    	    	int primary key,
       bioguide_id	varchar(255),
       party		char(2),
       state		char(2),
       district		int,
       gender		char(1),
       title		varchar(255),
       firstname	varchar(255),
       middlename	varchar(255),
       lastname		varchar(255),
       name_suffix	varchar(255),
       nickname		varchar(255),
       phone		varchar(255),
       fax		varchar(255),
       website		varchar(255),
       webform		varchar(255),
       email		varchar(255),
       congress_office	varchar(255),
       twitter_id	varchar(255),
       youtube_url	varchar(255)
) type=MyISAM;

create table if not exists vote_rep (
       vote_id	      	int,
       rep_id	      	int,
       vote	      	varchar(255) not null,
       primary key (vote_id, rep_id)
) type=MyISAM;

create table if not exists vote_stats (
       vote_id	    	int primary key,
       dmajority	char(1),
       rmajority	char(1)
) type=MyISAM;