-- Create schema for the SCS member and event database.

-- Drop tables and sequences used
DROP FUNCTION mailto(int4);
DROP TABLE mem_ev_lnk;
DROP TABLE events;
DROP SEQUENCE event_id;
DROP TABLE members;
DROP SEQUENCE member_id;

-- Sequence for Member ID's
CREATE SEQUENCE member_id INCREMENT 1 START 1;

-- Member table.
CREATE TABLE members (
	memberid		int4 NOT NULL,
	firstname		char(25) NOT NULL,
	lastname		char(25) NOT NULL,
	nickname		char(20),
	pin				char(10),
	age				int2,
	phone1			char(20),
	ph1_pub			bool,
	phone2			char(20),
	ph2_pub			bool,
	email			char(70) NOT NULL,
	email_pub		bool,
	address1		char(30),
	address2		char(30),
	address3		char(30),
	addy_pub		bool,
	comments		text,
	joined			date,
	lastmembfee		money,
	lastpaiddate	date,

	PRIMARY KEY (memberid),

	CONSTRAINT memcon CHECK (firstname <> ''), CHECK (lastname <> ''), CHECK (nickname <> ''),
	CHECK (memberid > 0)
	);

-- Sequence for Event ID's
CREATE SEQUENCE event_id INCREMENT 1 START 1;

-- Event table.
CREATE TABLE events (
	eventid		int4 NOT NULL,
	name		text NOT NULL,
	description	text NOT NULL,
	when		datetime NOT NULL,
	duration	timespan,
	cost		text NOT NULL,
	contact		text NOT NULL,

	PRIMARY KEY (eventid),

	CONSTRAINT evcon CHECK (eventid > 0), CHECK (name <> ''), CHECK (description <> ''),
					 CHECK (cost <> ''), CHECK (contact <> '')
	);

-- Table to link the Event table and the Member ID table
CREATE TABLE mem_ev_lnk (
	memberid	int4,
	eventid		int4,
	contact		bool,

	FOREIGN KEY (memberid) REFERENCES members,
	FOREIGN KEY (eventid) REFERENCES events,

	CONSTRAINT lnkcon CHECK (memberid > 0), CHECK (eventid > 0)
	);

-- Function which returns a list of email addresses to mail about a
-- given eventid
CREATE FUNCTION mailto(int4) RETURNS SETOF bpchar AS
	'SELECT email FROM members WHERE memberid IN
		(SELECT memberid FROM mem_ev_lnk WHERE eventid = $1 AND contact = \'yes\')'
	LANGUAGE 'sql';

GRANT SELECT, INSERT, UPDATE ON members, events, mem_ev_lnk TO www;
GRANT SELECT, INSERT, UPDATE ON members, events, mem_ev_lnk TO darius;