CREATE TABLE AV_FILES (
	TITLE		TEXT PRIMARY KEY
) WITH OIDS;

CREATE TABLE AV_FILE_COMPONENT (
	FILE		TEXT NOT NULL,
	TITLE		TEXT REFERENCES AV_FILES 
						ON DELETE CASCADE
						ON UPDATE CASCADE,
	DURATION	INTERVAL NOT NULL,
	SEQUENCE_NO	INT DEFAULT (0),
	PRIMARY KEY (FILE,SEQUENCE_NO)
) WITH OIDS;

CREATE TABLE FILL_SHORTS (
	TITLE		TEXT REFERENCES AV_FILES ON DELETE CASCADE
						 ON UPDATE CASCADE,
	GROUP_NAME 	TEXT
) WITH OIDS;

CREATE TABLE CONTENTS (
	TITLE		TEXT PRIMARY KEY REFERENCES AV_FILES
				ON DELETE CASCADE
				ON UPDATE CASCADE,
	TYPE		TEXT,
	DIRECTOR	TEXT,
	DESCRIPTION	TEXT
) WITH OIDS;

CREATE TABLE SCHEDULES (
	NAME		TEXT PRIMARY KEY
) WITH OIDS;

CREATE TABLE CONTENT_SCHEDULE (
	ID		SERIAL PRIMARY KEY,
	TITLE 		TEXT NOT NULL REFERENCES AV_FILES
				ON DELETE CASCADE
				ON UPDATE CASCADE,
	SCHEDULE	TEXT DEFAULT 'Baycon 2005' REFERENCES SCHEDULES (NAME) 
				ON DELETE CASCADE
				ON UPDATE CASCADE,
	LISTED		BOOLEAN DEFAULT TRUE,
	START_TIME	TIMESTAMP WITH TIME ZONE NOT NULL,
	UNIQUE(START_TIME,SCHEDULE)
) WITH OIDS;

CREATE FUNCTION AVFILE_DURATION(TEXT) RETURNS INTERVAL AS '
DECLARE
TOTAL_LENGTH INTERVAL;
STITLE ALIAS FOR $1;
BEGIN
	SELECT SUM(DURATION) INTO TOTAL_LENGTH FROM AV_FILE_COMPONENT 
		WHERE TITLE = STITLE;
	RETURN TOTAL_LENGTH;
END
' LANGUAGE 'PLPGSQL';

CREATE FUNCTION STOP_TIME(TEXT,TIMESTAMP WITH TIME ZONE) 
RETURNS TIMESTAMP WITH TIME ZONE AS '
DECLARE
STITLE ALIAS FOR $1;
START_TIME ALIAS FOR $2;
BEGIN
	RETURN START_TIME + AVFILE_DURATION(STITLE);
END
' LANGUAGE 'PLPGSQL';


CREATE FUNCTION CHECK_OVERLAP() RETURNS TRIGGER AS '
BEGIN
	IF EXISTS( SELECT ID FROM CONTENT_SCHEDULE
		WHERE SCHEDULE = NEW.SCHEDULE
			AND OID != NEW.OID
			AND OVERLAPS(NEW.START_TIME, ( AVFILE_DURATION(NEW.TITLE) + INTERVAL ''1 SEC''), START_TIME, ( AVFILE_DURATION(TITLE) + INTERVAL ''1 SEC''))
	)
	THEN
		RAISE EXCEPTION ''SCHEDULE ENTRY CONFLICTS WITH EXISTING ENTRY'';
	END IF;
	RETURN NEW;
END;
' LANGUAGE 'PLPGSQL';

CREATE TRIGGER CONTENT_CHECK_OVERLAP BEFORE INSERT OR UPDATE ON CONTENT_SCHEDULE
	FOR EACH ROW EXECUTE PROCEDURE CHECK_OVERLAP();


---
--- Views
---
CREATE OR REPLACE VIEW FILLS AS
	SELECT TITLE, AVFILE_DURATION(TITLE) AS DURATION
	FROM FILL_SHORTS;


--
-- Listing of movie contents
CREATE OR REPLACE VIEW MOVIES AS
	SELECT TITLE, AVFILE_DURATION(TITLE) AS DURATION
	FROM CONTENTS;


CREATE OR REPLACE VIEW SCHEDULE_TIMES AS
	SELECT 
		CONTENT_SCHEDULE.ID AS ID,
		CONTENT_SCHEDULE.TITLE,
		SCHEDULE,
		START_TIME,
		STOP_TIME(CONTENT_SCHEDULE.TITLE,START_TIME) AS STOP_TIME,
		DESCRIPTION,
		LISTED
	FROM CONTENT_SCHEDULE,CONTENTS
	WHERE CONTENTS.TITLE = CONTENT_SCHEDULE.TITLE;
	

CREATE OR REPLACE VIEW SCHEDULE_TIMES_RAW AS
	SELECT 
		ID,
	 	TITLE,
		SCHEDULE,
		DESCRIPTION,
               	DATE_PART('epoch', START_TIME) AS START_TIME,
               	DATE_PART('epoch', STOP_TIME) AS STOP_TIME,
		LISTED
	FROM SCHEDULE_TIMES;


---
--- Permissions
---
GRANT SELECT ON TABLE SCHEDULES TO APACHE;
GRANT SELECT ON TABLE AV_FILE_COMPONENT TO APACHE;
GRANT SELECT ON TABLE SCHEDULE_TIMES TO APACHE;
GRANT SELECT ON TABLE MOVIES TO APACHE;
GRANT SELECT ON TABLE FILLS TO APACHE;
GRANT ALL ON TABLE CONTENT_SCHEDULE TO APACHE;
GRANT ALL ON TABLE AV_FILES TO APACHE;
GRANT ALL ON CONTENT_SCHEDULE_ID_SEQ TO APACHE;