/*
Created: 2011-05-23
Modified: 2011-05-29
Model: PostgreSQL 8.4
Database: PostgreSQL 8.4
*/

-- Create sequences section -------------------------------------------------

CREATE SEQUENCE "eventid"
 INCREMENT BY 1
 START WITH 1
 NO MAXVALUE
 NO MINVALUE
 CACHE 1
;

CREATE SEQUENCE "userid"
 INCREMENT BY 1
 START WITH 1
 NO MAXVALUE
 NO MINVALUE
 CACHE 1
;

CREATE SEQUENCE "categoryid"
 INCREMENT BY 1
 START WITH 1
 NO MAXVALUE
 NO MINVALUE
 CACHE 1
;

-- Create sequences section -------------------------------------------------

CREATE SEQUENCE "markid"
 INCREMENT BY 1
 START WITH 1
 NO MAXVALUE
 NO MINVALUE
 CACHE 1
;

CREATE SEQUENCE "commentid"
 INCREMENT BY 1
 START WITH 1
 NO MAXVALUE
 NO MINVALUE
 CACHE 1
;

CREATE SEQUENCE "externalparticipanid"
 INCREMENT BY 1
 START WITH 1
 NO MAXVALUE
 NO MINVALUE
 CACHE 1
;

CREATE SEQUENCE "eventnotification"
 INCREMENT BY 1
 START WITH 1
 NO MAXVALUE
 NO MINVALUE
 CACHE 1
;



-- Create tables section -------------------------------------------------

-- Table users

CREATE TABLE "users"(
 "email" Character(50) NOT NULL,
 "active_account" Boolean DEFAULT true NOT NULL,
 "currently_logged_in" Boolean DEFAULT false NOT NULL,
 "blocked_account" Boolean DEFAULT false NOT NULL,
 "creation_date" Timestamp DEFAULT now() NOT NULL,
 "notify_about_changes_in_my_events" Boolean DEFAULT true NOT NULL,
 "user_id" Bigint DEFAULT nextval('userid') NOT NULL,
 "is_admin" Boolean DEFAULT false NOT NULL,
 "age" Bigint NOT NULL
        CONSTRAINT "not_negative_age" CHECK (age>=0),
 "is_man" Boolean NOT NULL
)
WITH (OIDS=FALSE)
;

-- Add keys for table users

ALTER TABLE "users" ADD CONSTRAINT "Key3" PRIMARY KEY ("user_id")
;

ALTER TABLE "users" ADD CONSTRAINT "login" UNIQUE ("email")
;

ALTER TABLE "users" ADD CONSTRAINT "user_id" UNIQUE ("user_id")
;

-- Create tables section -------------------------------------------------

-- Table events

CREATE TABLE "events"(
 "event_id" Bigint DEFAULT nextval('eventid') NOT NULL,
 "name" Character(50) NOT NULL,
 "start_time" Timestamp DEFAULT now() NOT NULL
        CONSTRAINT "not_in_past" CHECK (start_time>=now()),
 "stop_time" Timestamp NOT NULL
        CONSTRAINT "stop_after_start" CHECK (stop_time>start_time),
 "location" Point NOT NULL,
 "is_event_private" Boolean DEFAULT false NOT NULL,
 "max_nr_people" Smallint
        CONSTRAINT "bigger_than_zero" CHECK (max_nr_people>0),
 "required_gender" Character(20) DEFAULT 0
        CONSTRAINT "correct_gender" CHECK (required_gender='no' or required_gender='man' or required_gender='woman'),
 "min_age" Smallint
        CONSTRAINT "min_age_bigger_than_zero" CHECK (min_age>0),
 "show_participants" Boolean DEFAULT true NOT NULL,
 "sing_in_deadline" Timestamp NOT NULL
        CONSTRAINT "correct_sign_in_deadline" CHECK (sing_in_deadline>now() and sing_in_deadline<=stop_time),
 "info" Text,
 "is_event_cyclic" Boolean DEFAULT false NOT NULL,
 "repetition_time" Bigint DEFAULT 7
        CONSTRAINT "correct_repetition_time_in_days" CHECK (repetition_time>=1),
 "creation_date" Timestamp DEFAULT now() NOT NULL,
 "is_canceled" Boolean DEFAULT false NOT NULL,
 "event_category" Bigint DEFAULT nextval('categoryid') NOT NULL,
 "event_owner" Bigint DEFAULT nextval('userid') NOT NULL
)
WITH (OIDS=FALSE)
;

-- Add keys for table events

ALTER TABLE "events" ADD CONSTRAINT "Key1" PRIMARY KEY ("event_id")
;

ALTER TABLE "events" ADD CONSTRAINT "event_id" UNIQUE ("event_id")
;

-- Table categories

CREATE TABLE "categories"(
 "category_name" Character(50) NOT NULL,
 "creation_date" Timestamp DEFAULT now(),
 "category_id" Bigint DEFAULT nextval('categoryid') NOT NULL,
 "user_id" Bigint DEFAULT nextval('userid') NOT NULL
)
WITH (OIDS=FALSE)
;

-- Add keys for table categories

ALTER TABLE "categories" ADD CONSTRAINT "Key2" PRIMARY KEY ("category_id")
;

ALTER TABLE "categories" ADD CONSTRAINT "category_name" UNIQUE ("category_name")
;

ALTER TABLE "categories" ADD CONSTRAINT "cat_id" UNIQUE ("category_id")
;

-- Table event_comments

CREATE TABLE "event_comments"(
 "creation_date" Timestamp(6) DEFAULT now() NOT NULL,
 "comment" Text NOT NULL,
 "comment_id" Bigint DEFAULT nextval('commentid') NOT NULL,
 "event_id" Bigint,
 "comment_author_id" Bigint DEFAULT nextval('userid')
)
WITH (OIDS=FALSE)
;

-- Add keys for table event_comments

ALTER TABLE "event_comments" ADD CONSTRAINT "Key5" PRIMARY KEY ("comment_id")
;

ALTER TABLE "event_comments" ADD CONSTRAINT "ev_comment_id" UNIQUE ("comment_id")
;

-- Table new_event_by_user_notifications

CREATE TABLE "new_event_by_user_notifications"(
 "event_organizer_id" Bigint DEFAULT nextval('userid') NOT NULL,
 "user_id" Bigint DEFAULT nextval('userid') NOT NULL
)
WITH (OIDS=FALSE)
;

-- Add keys for table new_event_by_user_notifications

ALTER TABLE "new_event_by_user_notifications" ADD CONSTRAINT "Key7" PRIMARY KEY ("event_organizer_id","user_id")
;

-- Table event_marks

CREATE TABLE "event_marks"(
 "creation_date" Timestamp(6) DEFAULT now() NOT NULL,
 "mark" Smallint NOT NULL
        CONSTRAINT "mark_in_scale" CHECK (mark>0 and mark<=10),
 "event_mark_id" Bigint DEFAULT nextval('markid') NOT NULL,
 "marked_event_id" Bigint,
 "mark_author_id" Bigint DEFAULT nextval('userid')
)
WITH (OIDS=FALSE)
;
COMMENT ON COLUMN "event_marks"."creation_date" IS 'wartość obliczana'
;

-- Add keys for table event_marks

ALTER TABLE "event_marks" ADD CONSTRAINT "Key8" PRIMARY KEY ("event_mark_id")
;

ALTER TABLE "event_marks" ADD CONSTRAINT "event_mark_id" UNIQUE ("event_mark_id")
;

-- Table event_black_lists

CREATE TABLE "event_black_lists"(
 "event_id" Bigint NOT NULL,
 "user_id" Bigint DEFAULT nextval('userid') NOT NULL
)
WITH (OIDS=FALSE)
;

-- Add keys for table event_black_lists

ALTER TABLE "event_black_lists" ADD CONSTRAINT "Key9" PRIMARY KEY ("event_id","user_id")
;

-- Table participants_and_invited_users

CREATE TABLE "participants_and_invited_users"(
 "event_id" Bigint NOT NULL,
 "user_id" Bigint DEFAULT nextval('userid') NOT NULL,
 "is_taking_part" Character(20) DEFAULT 'unknown' NOT NULL
        CONSTRAINT "enum" CHECK (is_taking_part='no' or is_taking_part='yes' or is_taking_part='unknown')
)
WITH (OIDS=FALSE)
;

-- Add keys for table participants_and_invited_users

ALTER TABLE "participants_and_invited_users" ADD CONSTRAINT "Key10" PRIMARY KEY ("event_id","user_id")
;

-- Table free_places_watchers

CREATE TABLE "free_places_watchers"(
 "event_id" Bigint NOT NULL,
 "user_id" Bigint DEFAULT nextval('userid') NOT NULL
)
WITH (OIDS=FALSE)
;

-- Add keys for table free_places_watchers

ALTER TABLE "free_places_watchers" ADD CONSTRAINT "Key11" PRIMARY KEY ("event_id","user_id")
;

-- Table invitations

CREATE TABLE "invitations"(
 "event_id" Bigint NOT NULL,
 "user_id" Bigint DEFAULT nextval('userid') NOT NULL
)
WITH (OIDS=FALSE)
;

-- Add keys for table invitations

ALTER TABLE "invitations" ADD CONSTRAINT "Key12" PRIMARY KEY ("event_id","user_id")
;

-- Table invited_external_users

CREATE TABLE "invited_external_users"(
 "event_id" Bigint NOT NULL,
 "participant_id" Bigint DEFAULT nextval('externalparticipanid') NOT NULL
)
WITH (OIDS=FALSE)
;

-- Add keys for table invited_external_users

ALTER TABLE "invited_external_users" ADD CONSTRAINT "Key13" PRIMARY KEY ("event_id","participant_id")
;

-- Table new_ev_in_cat_notifications

CREATE TABLE "new_ev_in_cat_notifications"(
 "user_id" Bigint DEFAULT nextval('userid') NOT NULL,
 "category_id" Bigint DEFAULT nextval('categoryid') NOT NULL
)
WITH (OIDS=FALSE)
;

-- Add keys for table new_ev_in_cat_notifications

ALTER TABLE "new_ev_in_cat_notifications" ADD CONSTRAINT "Key15" PRIMARY KEY ("user_id","category_id")
;

-- Table new_event_in_loc_notifications

CREATE TABLE "new_event_in_loc_notifications"(
 "events_to_notify_location" Polygon NOT NULL,
 "notification_id" Bigint DEFAULT nextval('eventnotification') NOT NULL,
 "user_id" Bigint DEFAULT nextval('userid')
)
WITH (OIDS=FALSE)
;

-- Add keys for table new_event_in_loc_notifications

ALTER TABLE "new_event_in_loc_notifications" ADD CONSTRAINT "Key16" PRIMARY KEY ("notification_id")
;

ALTER TABLE "new_event_in_loc_notifications" ADD CONSTRAINT "notification_id" UNIQUE ("notification_id")
;

-- Table external_event_participants

CREATE TABLE "external_event_participants"(
 "participant_id" Bigint DEFAULT nextval('externalparticipanid') NOT NULL,
 "email" Character(50) NOT NULL
)
WITH (OIDS=FALSE)
;

-- Add keys for table external_event_participants

ALTER TABLE "external_event_participants" ADD CONSTRAINT "Key17" PRIMARY KEY ("participant_id")
;

-- Create relationships section ------------------------------------------------- 

ALTER TABLE "event_marks" ADD CONSTRAINT "markedEvent" FOREIGN KEY ("marked_event_id") REFERENCES "events" ("event_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "invitations" ADD CONSTRAINT "event_to_invite" FOREIGN KEY ("event_id") REFERENCES "events" ("event_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "participants_and_invited_users" ADD CONSTRAINT "event_to_participate_in" FOREIGN KEY ("event_id") REFERENCES "events" ("event_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "invited_external_users" ADD CONSTRAINT "event_to_invite_for" FOREIGN KEY ("event_id") REFERENCES "events" ("event_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "invited_external_users" ADD CONSTRAINT "externalEvent" FOREIGN KEY ("participant_id") REFERENCES "external_event_participants" ("participant_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "free_places_watchers" ADD CONSTRAINT "watched_event" FOREIGN KEY ("event_id") REFERENCES "events" ("event_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "event_comments" ADD CONSTRAINT "commenteEvnet" FOREIGN KEY ("event_id") REFERENCES "events" ("event_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "events" ADD CONSTRAINT "catEv" FOREIGN KEY ("event_category") REFERENCES "categories" ("category_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "new_ev_in_cat_notifications" ADD CONSTRAINT "category_to_notify_about" FOREIGN KEY ("category_id") REFERENCES "categories" ("category_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "new_ev_in_cat_notifications" ADD CONSTRAINT "user_to_notify" FOREIGN KEY ("user_id") REFERENCES "users" ("user_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "categories" ADD CONSTRAINT "CatOwner" FOREIGN KEY ("user_id") REFERENCES "users" ("user_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "events" ADD CONSTRAINT "eventOwner" FOREIGN KEY ("event_owner") REFERENCES "users" ("user_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "event_marks" ADD CONSTRAINT "eventMarkAuthor" FOREIGN KEY ("mark_author_id") REFERENCES "users" ("user_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "event_comments" ADD CONSTRAINT "commentAuthor" FOREIGN KEY ("comment_author_id") REFERENCES "users" ("user_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "free_places_watchers" ADD CONSTRAINT "user" FOREIGN KEY ("user_id") REFERENCES "users" ("user_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "invitations" ADD CONSTRAINT "invited_user" FOREIGN KEY ("user_id") REFERENCES "users" ("user_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "participants_and_invited_users" ADD CONSTRAINT "event_participant_i" FOREIGN KEY ("user_id") REFERENCES "users" ("user_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "event_black_lists" ADD CONSTRAINT "event_for_which_black_list_created" FOREIGN KEY ("event_id") REFERENCES "events" ("event_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "event_black_lists" ADD CONSTRAINT "user_on_black_list" FOREIGN KEY ("user_id") REFERENCES "users" ("user_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "new_event_by_user_notifications" ADD CONSTRAINT "event_irganizer" FOREIGN KEY ("event_organizer_id") REFERENCES "users" ("user_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "new_event_by_user_notifications" ADD CONSTRAINT "notification_receiver" FOREIGN KEY ("user_id") REFERENCES "users" ("user_id") ON DELETE CASCADE ON UPDATE CASCADE
;

ALTER TABLE "new_event_in_loc_notifications" ADD CONSTRAINT "notification_receiver_id" FOREIGN KEY ("user_id") REFERENCES "users" ("user_id") ON DELETE CASCADE ON UPDATE CASCADE
;