--------------------------------------------------------------------------------
-- Calendar -- a web application for publishing calendars
-- Copyright (C) 2006 Sam Clippinger (samc (at) silence (dot) org)
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
--------------------------------------------------------------------------------
-- This SQL script will create the database tables required for the calendar
-- application.  See the file INSTALL.txt in the "documentation" folder for
-- instructions.

CREATE TABLE category (
	category_id char(26) NOT NULL PRIMARY KEY,
	parent_category_id char(26) NOT NULL,
        date_created datetime NOT NULL,
        date_modified datetime NOT NULL,
        compute_percentage bit NOT NULL,
        highlight_percentage int NOT NULL,
	name varchar(200) NOT NULL,
	prefix varchar(50) NOT NULL 
);

CREATE TABLE calendar_category_event (
	category_event_id char(26) NOT NULL PRIMARY KEY,
	category_id char(26) NOT NULL,
	event_id char(26) NOT NULL,
        date_created datetime NOT NULL,
        date_modified datetime NOT NULL
);

CREATE TABLE calendar_event (
	event_id char(26) NOT NULL PRIMARY KEY,
        subscription_id char(26) NOT NULL,
        date_created datetime NOT NULL,
        date_modified datetime NOT NULL,
	name varchar(200) NOT NULL,
	description text NOT NULL 
);

CREATE TABLE calendar_occurrence (
	occurrence_id char(26) NOT NULL PRIMARY KEY,
	event_id char(26) NOT NULL,
        date_created datetime NOT NULL,
        date_modified datetime NOT NULL,
	date_start datetime NOT NULL,
	date_end datetime NOT NULL 
);

CREATE TABLE calendar_recurrence (
	recurrence_id char(26) NOT NULL PRIMARY KEY,
        event_id char(26) NOT NULL,
        date_created datetime NOT NULL,
        date_modified datetime NOT NULL,
	date_start datetime NOT NULL,
	date_end datetime NOT NULL,
	recurrence_date_end datetime NOT NULL,
	num_recurrences int NOT NULL,
	type int NOT NULL,
	frequency int NOT NULL,
	nth int NOT NULL,
	weekday_0 bit NOT NULL,
	weekday_1 bit NOT NULL,
	weekday_2 bit NOT NULL,
	weekday_3 bit NOT NULL,
	weekday_4 bit NOT NULL,
	weekday_5 bit NOT NULL,
	weekday_6 bit NOT NULL,
	day int NOT NULL,
	month int NOT NULL
);

CREATE TABLE user (
	user_id char(26) NOT NULL PRIMARY KEY,
        theme_id char(26) NOT NULL,
        date_created datetime NOT NULL,
        date_modified datetime NOT NULL,
        guest_account bit NOT NULL,
	type int NOT NULL,
	name varchar(100) NOT NULL,
	email_address varchar(200) NOT NULL,
	password varchar(25) NOT NULL 
);

CREATE TABLE calendar_theme (
        theme_id varchar(26) NOT NULL PRIMARY KEY,
        date_created datetime NOT NULL,
        date_modified datetime NOT NULL,
        is_default bit NOT NULL,
        text_bold_body_text bit NOT NULL,
        text_italics_body_text bit NOT NULL,
        text_smallcaps_body_text bit NOT NULL,
        text_underline_body_text bit NOT NULL,
        text_bold_link bit NOT NULL,
        text_italics_link bit NOT NULL,
        text_smallcaps_link bit NOT NULL,
        text_underline_link bit NOT NULL,
        text_bold_page_header bit NOT NULL,
        text_italics_page_header bit NOT NULL,
        text_smallcaps_page_header bit NOT NULL,
        text_underline_page_header bit NOT NULL,
        text_bold_grid_header bit NOT NULL,
        text_italics_grid_header bit NOT NULL,
        text_smallcaps_grid_header bit NOT NULL,
        text_underline_grid_header bit NOT NULL,
        text_bold_grid bit NOT NULL,
        text_italics_grid bit NOT NULL,
        text_smallcaps_grid bit NOT NULL,
        text_underline_grid bit NOT NULL,
        text_bold_grid_event bit NOT NULL,
        text_italics_grid_event bit NOT NULL,
        text_smallcaps_grid_event bit NOT NULL,
        text_underline_grid_event bit NOT NULL,
        text_bold_summary_date bit NOT NULL,
        text_italics_summary_date bit NOT NULL,
        text_smallcaps_summary_date bit NOT NULL,
        text_underline_summary_date bit NOT NULL,
        text_bold_category_prefix bit NOT NULL,
        text_italics_category_prefix bit NOT NULL,
        text_smallcaps_category_prefix bit NOT NULL,
        text_underline_category_prefix bit NOT NULL,
        link_rollover_invert bit NOT NULL,
        first_day_of_week int NOT NULL,
        text_size_body_text int NOT NULL,
        text_size_link int NOT NULL,
        text_size_page_header int NOT NULL,
        text_size_grid_header int NOT NULL,
        text_size_grid int NOT NULL,
        text_size_grid_event int NOT NULL,
        text_size_summary_date int NOT NULL,
        text_size_category_prefix int NOT NULL,
        border_size_grid int NOT NULL,
        border_size_event int NOT NULL,
        width_weekday_0 float NOT NULL,
        width_weekday_1 float NOT NULL,
        width_weekday_2 float NOT NULL,
        width_weekday_3 float NOT NULL,
        width_weekday_4 float NOT NULL,
        width_weekday_5 float NOT NULL,
        width_weekday_6 float NOT NULL,
        background_body varchar(20) NOT NULL,
        background_grid_header varchar(20) NOT NULL,
        background_grid_on_month varchar(20) NOT NULL,
        background_grid_off_month varchar(20) NOT NULL,
        background_grid_on_month_today varchar(20) NOT NULL,
        background_grid_off_month_today varchar(20) NOT NULL,
        background_event_single_day_even varchar(20) NOT NULL,
        background_event_single_day_odd varchar(20) NOT NULL,
        background_event_multi_day_even varchar(20) NOT NULL,
        background_event_multi_day_odd varchar(20) NOT NULL,
        background_link varchar(20) NOT NULL,
        text_color_body_text varchar(20) NOT NULL,
        text_color_link varchar(20) NOT NULL,
        text_color_page_header varchar(20) NOT NULL,
        text_color_grid_header varchar(20) NOT NULL,
        text_color_grid varchar(20) NOT NULL,
        text_color_grid_event varchar(20) NOT NULL,
        text_color_summary_date varchar(20) NOT NULL,
        text_color_category_prefix varchar(20) NOT NULL,
        border_color_grid varchar(20) NOT NULL,
        border_style_grid varchar(20) NOT NULL,
        border_color_event varchar(20) NOT NULL,
        border_style_event varchar(20) NOT NULL,
        name varchar(200) NOT NULL,
        title varchar(50) NOT NULL,
        pre_page_include text NOT NULL,
        post_page_include text NOT NULL
);

CREATE TABLE calendar_theme_category (
	theme_category_id char(26) NOT NULL PRIMARY KEY,
        theme_id char(26) NOT NULL,
	category_id char(26) NOT NULL,
        date_created datetime NOT NULL,
        date_modified datetime NOT NULL
);

CREATE TABLE calendar_subscription (
        subscription_id char(26) NOT NULL PRIMARY KEY,
        date_created datetime NOT NULL,
        date_modified datetime NOT NULL,
        date_refreshed datetime NOT NULL,
        refresh_interval_mins int NOT NULL,
        name varchar(100) NOT NULL,
        prefix varchar(50) NOT NULL,
        url varchar(500) NOT NULL,
        username varchar(50) NOT NULL,
        password varchar(50) NOT NULL
);

CREATE TABLE calendar_theme_subscription (
	theme_subscription_id char(26) NOT NULL PRIMARY KEY,
        theme_id char(26) NOT NULL,
	subscription_id char(26) NOT NULL,
        date_created datetime NOT NULL,
        date_modified datetime NOT NULL
);