CREATE TABLE weblogs (
    id serial PRIMARY KEY,
    name character varying(32) NOT NULL UNIQUE,
    title text,
    preamble text,
    num_displayed integer DEFAULT 25 NOT NULL
);

CREATE TABLE users (
    id serial PRIMARY KEY,
    website text,
    contact text,
    perms integer NOT NULL,
    name character varying(32) NOT NULL UNIQUE,
    pass text NOT NULL,
    tz text DEFAULT 'Canada/Mountain',
    dispname text
);

CREATE TABLE groups (
    id serial PRIMARY KEY,
    name character varying(50) NOT NULL,
	owner_id integer REFERENCES users(id) ON DELETE CASCADE,
	usage_code character varying(32) NOT NULL,
	specialgroup_everyone boolean NOT NULL DEFAULT FALSE,
	specialgroup_loggedin boolean NOT NULL DEFAULT FALSE
);

INSERT INTO groups (name, owner_id, usage_code, specialgroup_everyone, specialgroup_loggedin) VALUES ('Everyone', NULL, 'ALL', TRUE, FALSE);
INSERT INTO groups (name, owner_id, usage_code, specialgroup_everyone, specialgroup_loggedin) VALUES ('Logged In Users', NULL, 'ALL', FALSE, TRUE);

CREATE TABLE entries (
    id serial PRIMARY KEY,
    subject text,
    text text NOT NULL,
    "timestamp" integer NOT NULL,
    "day" integer NOT NULL,
    "month" integer NOT NULL,
    "year" integer NOT NULL,
    serial integer NOT NULL,
    weblog_id integer NOT NULL REFERENCES weblogs(id),
    author_id integer NOT NULL REFERENCES users(id),
	lock_group_id integer REFERENCES groups(id)
);

CREATE TABLE comments (
    id serial PRIMARY KEY,
    subject text,
    text text NOT NULL,
    entry_id integer NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
    "timestamp" integer NOT NULL,
    author_id integer REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE authors (
    id serial PRIMARY KEY,
    user_id integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    weblog_id integer NOT NULL REFERENCES weblogs(id) ON DELETE CASCADE
);

CREATE TABLE group_users (
    id serial PRIMARY KEY,
    group_id integer NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    user_id integer NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE photo_files (
	id serial PRIMARY KEY,
	file_name text UNIQUE,
	modified_date integer NOT NULL,
	checksum_crc32 integer NOT NULL,
	width integer,
	height integer
);

CREATE TABLE photos (
	id serial PRIMARY KEY,
	thumbnail_id integer NOT NULL REFERENCES photo_files(id),
	midsize_id integer NOT NULL REFERENCES photo_files(id),
	fullsize_id integer NOT NULL REFERENCES photo_files(id),
	title text,
	description text
);

CREATE TABLE tag_names (
	id serial PRIMARY KEY,
	name character varying(80) NOT NULL UNIQUE
);

CREATE TABLE tags (
	id serial PRIMARY KEY,
	photo_id integer REFERENCES photos(id),
	entry_id integer REFERENCES entries(id),
	tag_name_id integer NOT NULL REFERENCES tag_names(id) ON DELETE CASCADE
);

CREATE TABLE photo_galleries (
	id serial PRIMARY KEY,
	name character varying(32) NOT NULL UNIQUE,
	title text NOT NULL,
	subtitle text NOT NULL
);

CREATE TABLE forums (
	id serial PRIMARY KEY,
    name character varying(32) NOT NULL UNIQUE,
	title character varying(80) NOT NULL,
	subtitle character varying(200),
	description text,
	moderator_group_id integer NOT NULL REFERENCES groups(id)
);

CREATE TABLE forum_threads (
	id serial PRIMARY KEY,
	subject character varying(200) NOT NULL,
	forum_id integer NOT NULL REFERENCES forums(id) ON DELETE CASCADE,
	bump_date date NOT NULL,
	bump_id integer NOT NULL REFERENCES forum_replies(id),
	author_id integer REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE forum_replies (
	id serial PRIMARY KEY,
	thread_id integer NOT NULL REFERENCES forum_threads(id) ON DELETE CASCADE,
	text text NOT NULL,
	author_id integer REFERENCES users(id) ON DELETE SET NULL,
	serial integer NOT NULL,
	timestamp integer NOT NULL,
	edit_timestamp integer
);


