DROP TABLE IF EXISTS Electorate;
CREATE TABLE Electorate (
	ID                 int(10) NOT NULL,
    Riding             int(10) NOT NULL,
	PollNumber         int(10) NOT NULL,

	FirstName          varchar(64) NOT NULL,
	MiddleName         varchar(64) NOT NULL,
	LastName           varchar(64) NOT NULL,
	Salutation         varchar(10) NOT NULL,

	StreetNumber       varchar(10) NOT NULL,
	/* This harmonizes with canada post: http://www.canadapost.ca/personal/tools/pcl/bin/advanced-e.asp */
	StreetNumberSuffix enum('', '1/4', '1/2', '3/4', 'A', 'B', 'C', 
	                        'D', 'E', 'F', 'G', 'H', 'I', 'J', 
									'K', 'L', 'M', 'N', 'O', 'P', 'Q', 
									'R', 'S', 'T', 'U', 'V', 'W', 'X', 
									'Y', 'Z') NOT NULL DEFAULT '',
	Street             varchar(128) NOT NULL,
	StreetType         varchar(20) NOT NULL,
	StreetDirection    varchar(20) NOT NULL,

	Unit               varchar(32) NOT NULL DEFAULT '',
	City               varchar(32) NOT NULL,
	Province           varchar(2) NOT NULL,
	PostalCode         varchar(6) NOT NULL,

	PhoneNumber        varchar(10) NOT NULL DEFAULT '',

	VotingAttention    enum('', 'Definite', 'Probably', 'Possibly', 'Not', 'Hostile') NOT NULL DEFAULT '',
	Canvassed          tinyint(1) NOT NULL DEFAULT 0,
	Phoned             tinyint(1) NOT NULL DEFAULT 0,
	PhoneLookedUp      tinyint(1) NOT NULL DEFAULT 0,
	Voted              tinyint(1) NOT NULL DEFAULT 0,
	PRIMARY KEY (ID),
	INDEX name (LastName, FirstName),
	INDEX phone (PHoneNumber),
	INDEX canvas (Canvassed),
	INDEX lookup (PhoneLookedUp),
	INDEX called (Phoned)
);

DROP TABLE IF EXISTS Activity;
CREATE TABLE Activity (
	PollNumber         int(10) NOT NULL,
	Action             enum('Canvassed', 'PhonLookedUp', 'Phoned') NOT NULL,
	CompletedDate      datetime NOT NULL,
	PRIMARY KEY (PollNumber, Action, CompletedDate)
);

DROP TABLE IF EXISTS PollPriority;
CREATE TABLE PollPriority (
	PollNumber         int(10) NOT NULL,
	Priority           tinyint NOT NULL,
	PRIMARY KEY (PollNumber, Priority),
	INDEX pri_value (Priority)
);


