fadsf.sql

create table profile
(
	id bigserial not null
		constraint profile_pkey
			primary key,
	first_name varchar(255) not null,
	last_name varchar(255) not null,
	username varchar(255) not null,
	user_id varchar(255) not null,
	created_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	updated_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	last_inbox_fetch timestamp with time zone default CURRENT_TIMESTAMP,
	profile_img_url varchar
);

alter table profile owner to oppmein;

create index profile_user_id_index
	on profile (user_id);

create table "group"
(
	id bigserial not null
		constraint group_pkey
			primary key,
	name varchar(255) not null
		constraint group_name_unique
			unique,
	description varchar(255) not null,
	icon_image_url varchar(255) not null,
	street varchar(255) not null,
	city varchar(255) not null,
	state varchar(255) not null,
	zipcode varchar(255) not null,
	phone varchar(255) not null,
	owner_profile_id bigint not null
		constraint group_owner_profile_id_foreign
			references profile,
	created_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	updated_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	public boolean default false not null
);

alter table "group" owner to oppmein;

create table subgroup_pair
(
	id bigserial not null
		constraint subgroup_pair_pkey
			primary key,
	group_id bigint not null
		constraint subgroup_pair_group_id_foreign
			references "group",
	subgroup_id bigint not null
		constraint subgroup_pair_subgroup_id_foreign
			references "group",
	created_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	updated_at timestamp with time zone default CURRENT_TIMESTAMP not null
);

alter table subgroup_pair owner to oppmein;

create table membership
(
	id bigserial not null
		constraint membership_pkey
			primary key,
	group_id bigint not null
		constraint membership_group_id_foreign
			references "group",
	profile_id bigint not null
		constraint membership_profile_id_foreign
			references profile,
	created_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	updated_at timestamp with time zone default CURRENT_TIMESTAMP not null
);

alter table membership owner to oppmein;

create table post_type
(
	id bigserial not null
		constraint post_type_pkey
			primary key,
	name varchar(255) not null,
	created_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	updated_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	icon varchar not null,
	position smallint,
	active_icon varchar default 'https://static.thenounproject.com/png/1197942-200.png'::character varying not null,
	color varchar(8) default '#ffffff'::character varying
);

alter table post_type owner to oppmein;

create table post
(
	id bigserial not null
		constraint post_pkey
			primary key,
	title varchar(255) not null,
	description varchar not null,
	street varchar(255) not null,
	city varchar(255) not null,
	state varchar(255) not null,
	zipcode varchar(255) not null,
	email varchar(255) not null,
	phone varchar(255) not null,
	confirmations_enabled boolean not null,
	notifications_enabled boolean not null,
	users_needed_count integer not null,
	start_at timestamp with time zone not null,
	expire_at timestamp with time zone not null,
	host_profile_id bigint not null
		constraint post_host_profile_id_foreign
			references profile,
	group_id bigint not null
		constraint post_group_id_foreign
			references "group",
	post_type_id bigint not null
		constraint post_post_type_id_foreign
			references post_type,
	created_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	updated_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	image_url varchar,
	public boolean default false not null
);

alter table post owner to oppmein;

create table invitations
(
	id bigserial not null
		constraint invitations_pkey
			primary key,
	invite_code varchar(8) not null,
	inviter_id bigint not null
		constraint invitations_inviter_id_foreign
			references profile,
	invited_id bigint
		constraint invitations_profile_id_fk
			references profile,
	updated_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	created_at timestamp with time zone default CURRENT_TIMESTAMP
);

alter table invitations owner to oppmein;

create table invitation_to_groups
(
	group_id bigint not null
		constraint invitation_to_groups_group_id_foreign
			references "group"
				on delete cascade,
	invitation_id bigint not null
		constraint invitation_to_groups_invitation_id_foreign
			references invitations
				on delete cascade
);

alter table invitation_to_groups owner to oppmein;

create table application
(
	id bigserial not null
		constraint application_pkey
			primary key,
	name varchar(255) not null,
	post_id bigint not null
		constraint application_post_id_foreign
			references post,
	profile_id bigint not null
		constraint application_profile_id_foreign
			references profile,
	created_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	updated_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	action integer not null,
	read_at timestamp with time zone
);

alter table application owner to oppmein;

create table message
(
	id bigserial not null
		constraint comment_pkey
			primary key,
	message_body varchar(255),
	author_id bigint not null
		constraint comment_profile_id_foreign
			references profile,
	created_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	updated_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	read_at timestamp with time zone
);

alter table message owner to oppmein;

create table connection
(
	id serial not null
		constraint connection_pkey
			primary key,
	responded_at timestamp with time zone,
	requestor_profile_id bigint not null
		constraint connection_requestor_profile_id_foreign
			references profile,
	requestee_profile_id bigint not null
		constraint connection_requestee_profile_id_foreign
			references profile,
	created_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	updated_at timestamp with time zone default CURRENT_TIMESTAMP not null,
	status integer default 0 not null,
	constraint connection_requestor_profile_id_requestee_profile_id_key
		unique (requestor_profile_id, requestee_profile_id)
);

alter table connection owner to oppmein;

create table message_to_post
(
	id bigserial not null
		constraint comment_to_post_pk
			primary key,
	message_id bigint not null
		constraint comment_to_post_comment_id_fk
			references message,
	post_id bigint not null
		constraint comment_to_post_post_id_fk
			references post
);

alter table message_to_post owner to oppmein;

create table message_to_user
(
	message_id bigint not null
		constraint message_to_user_message_id_fk
			references message
				on update cascade,
	user_id bigint not null
		constraint message_to_user_profile_id_fk
			references profile,
	id bigserial not null
		constraint message_to_user_pk
			primary key
);

alter table message_to_user owner to oppmein;

create table group_requests
(
	id bigserial not null
		constraint group_requests_pk
			primary key,
	post_id bigint,
	user_id bigint,
	status text default 'pending'::text not null
);

alter table group_requests owner to oppmein;

create table group_to_subgroup
(
	id serial not null
		constraint group_to_subgroup_pk
			primary key,
	supergroup integer not null,
	subgroup integer not null
);

alter table group_to_subgroup owner to oppmein;

create unique index group_to_subgroup_id_uindex
	on group_to_subgroup (id);

create table hash_tags
(
	id bigserial not null
		constraint hash_tags_pk
			primary key,
	name varchar not null
);

alter table hash_tags owner to oppmein;

create unique index hash_tags_name_uindex
	on hash_tags (name);

create table hash_tag_to_group
(
	tag_id bigint not null
		constraint hash_tag_to_group_hash_tags_id_fk
			references hash_tags,
	group_id bigint not null
		constraint hash_tag_to_group_group_id_fk
			references "group"
);

alter table hash_tag_to_group owner to oppmein;

create table hash_tag_to_post
(
	tag_id bigint not null
		constraint hash_tag_to_post_hash_tags_id_fk
			references hash_tags,
	post_id bigint not null
		constraint hash_tag_to_post_post_id_fk
			references post
);

alter table hash_tag_to_post owner to oppmein;

create table notification_object
(
	id serial not null
		constraint notification_pk
			primary key,
	entity_id bigint,
	entity_type smallint not null,
	status smallint not null,
	created_at timestamp with time zone not null
);

alter table notification_object owner to oppmein;

create unique index notification_id_uindex
	on notification_object (id);

create table notification_change
(
	id serial not null
		constraint notification_change_pk
			primary key,
	notification_object integer not null,
	actor_id integer not null,
	status smallint not null
);

alter table notification_change owner to oppmein;

create table notification
(
	id serial not null,
	notification_object_id integer not null,
	notified_id integer not null,
	status smallint not null
);

alter table notification owner to oppmein;