CREATE SEQUENCE "serial_id" start 9 increment 1 maxvalue 2147483647 minvalue 1 cache 1 cycle;
CREATE TABLE projects (
	"intfield01" integer,
	"fuid" integer,
	"creation_date" timestamp with time zone,
	"created_from" text,
	"changing_date" timestamp with time zone,
	"changed_from" text,
	"user_right" text,
	"sid" text,
	"tid" text,
	"project_startdate" timestamp with time zone,
	"project_enddate" timestamp with time zone,
	"project_deadline" timestamp with time zone,
	"project_reminder" timestamp with time zone,
	"project_budget" text,
	"project_effort" text,
	"project_effort_unit" text,
	"project_currency" text,
	"project_type" text,
	"project_kickoff" timestamp with time zone,
	"project_description" text,
	"project_goal" text,
	"project_customer" text,
	"project_name" text,
	"project_status" text,
	"project_phase" text,
	"project_number_of_attachments" integer,
	"project_task_folder_id" integer,
	"project_manage_permissions" integer,
	primary key (intfield01)
);

CREATE TABLE backup_projects (
	"intfield01" integer,
	"fuid" integer,
	"creation_date" timestamp with time zone,
	"created_from" text,
	"changing_date" timestamp with time zone,
	"changed_from" text,
	"user_right" text,
	"sid" text,
	"tid" text,
	"project_startdate" timestamp with time zone,
	"project_enddate" timestamp with time zone,
	"project_deadline" timestamp with time zone,
	"project_reminder" timestamp with time zone,
	"project_budget" text,
	"project_effort" text,
	"project_effort_unit" text,
	"project_currency" text,
	"project_type" text,
	"project_kickoff" timestamp with time zone,
	"project_description" text,
	"project_goal" text,
	"project_customer" text,
	"project_name" text,
	"project_status" text,
	"project_phase" text,
	"project_number_of_attachments" integer,
	"project_task_folder_id" integer,
	"project_manage_permissions" integer,
	primary key (intfield01)
);

CREATE TABLE projects_participants (
	"intfield01" integer,
	"id" text,
	"name" text,
	"role" integer,
	"ptype" integer,
	"group_id" text,
	"merged_permission" integer,
	primary key (intfield01, id, group_id),
	foreign key (intfield01) references projects on delete cascade on update cascade
);

CREATE TABLE backup_projects_participants (
	"intfield01" integer,
	"id" text,
	"name" text,
	"role" integer,
	"ptype" integer,
	"group_id" text,
	"merged_permission" integer,
	primary key (intfield01, id, group_id),
	foreign key (intfield01) references backup_projects on delete cascade on update cascade
);

CREATE TABLE projects_milestones (
	"intfield01" integer,
	"id" integer,
	"name" text,
	"description" text,
	"mdate" timestamp with time zone,
	primary key (intfield01, id),
	foreign key (intfield01) references projects on delete cascade on update cascade
);

CREATE TABLE backup_projects_milestones (
	"intfield01" integer,
	"id" integer,
	"name" text,
	"description" text,
	"mdate" timestamp with time zone,
	primary key (intfield01, id),
	foreign key (intfield01) references backup_projects on delete cascade on update cascade
);

CREATE TABLE projects_notes (
	"intfield01" integer,
	"note_id" text,
	"member_id" text,
	primary key (intfield01, note_id),
	foreign key (intfield01) references projects on delete cascade on update cascade
);

CREATE TABLE backup_projects_notes (
	"intfield01" integer,
	"note_id" text,
	"member_id" text,
	primary key (intfield01, note_id),
	foreign key (intfield01) references backup_projects on delete cascade on update cascade
);

CREATE TABLE projects_tasks (
	"intfield01" integer,
	"task_id" integer,
	primary key (intfield01, task_id),
	foreign key (intfield01) references projects on delete cascade on update cascade
);

CREATE TABLE backup_projects_tasks (
	"intfield01" integer,
	"task_id" integer,
	primary key (intfield01, task_id),
	foreign key (intfield01) references backup_projects on delete cascade on update cascade
);

CREATE TABLE projects_dependencies (
	"intfield01" integer,
	"id" integer,
	"successor" text,
	"dependency_type" integer,
	"object_type" integer,
	primary key (intfield01, id),
	foreign key (intfield01) references projects on delete cascade on update cascade
);

CREATE TABLE backup_projects_dependencies (
	"intfield01" integer,
	"id" integer,
	"successor" text,
	"dependency_type" integer,
	"object_type" integer,
	primary key (intfield01, id),
	foreign key (intfield01) references backup_projects on delete cascade on update cascade
);

CREATE TABLE projects_antecessors (
	"intfield01" integer,
	"id" integer,
	"antecessor" text,
	"object_type" integer,
	primary key (intfield01, id, antecessor),
	foreign key (intfield01, id) references projects_dependencies on delete cascade on update cascade
);

CREATE TABLE backup_projects_antecessors (
	"intfield01" integer,
	"id" integer,
	"antecessor" text,
	"object_type" integer,
	primary key (intfield01, id, antecessor),
	foreign key (intfield01, id) references backup_projects_dependencies on delete cascade on update cascade
);

CREATE TABLE projects_puids (
	"intfield01" integer,
	"puid" integer,
	"entity" text,
	"fuid" integer,
	primary key (intfield01, puid),
	foreign key (intfield01) references projects on delete cascade on update cascade
);

CREATE TABLE backup_projects_puids (
	"intfield01" integer,
	"puid" integer,
	"entity" text,
	"fuid" integer,
	primary key (intfield01, puid),
	foreign key (intfield01) references backup_projects on delete cascade on update cascade
);

CREATE table "oxfolder_userfolders" (
		"module_name" text,
		"linksite" text,
		"target" text,
		"img" text
);


CREATE TABLE "ical_principal" (
		"object_id" int,
		"principal" text,
		"calendarfolder" int,
		"taskfolder" int
);

CREATE TABLE "ical_ids" (
		"object_id" int,
		"principal_id" int,
		"client_id" text,
		"target_object_id" int,
		"module" int
);

CREATE TABLE "vcard_principal" (
		"object_id" int,
		"principal" text,
		"contactfolder" int
);

CREATE TABLE "vcard_ids" (
		"object_id" int,
		"principal_id" int,
		"client_id" text,
		"target_object_id" int
);

INSERT INTO oxfolder_tree VALUES (7, 0, 'user', 'system', 'system','system', 'system', 'now', 'System', null, null);
INSERT INTO oxfolder_tree VALUES (8, 7, 'projects', 'projects', 'system','system', 'system', 'now', 'System', null, null);
INSERT INTO oxfolder_permissions VALUES ((select nextval('serial_id')), 7, 512, 'all_ox_users_and_ox_groups', 0, 2, 0, 0, 0);
INSERT INTO oxfolder_permissions VALUES ((select nextval('serial_id')), 8, 512, 'all_ox_users_and_ox_groups', 0, 8, 4, 2, 2);
INSERT INTO oxfolder_specialfolders VALUES ('user', 7);

INSERT INTO oxfolder_userfolders VALUES ('projects', 'projects/projects_list_all', null, 'folder/item_projects.png');
