/* SQLEditor (Postgres)*/


CREATE TABLE public."accounts_password_auth"
(
id BIGINT NOT NULL DEFAULT nextval('oauth_authorizations_id_seq'::regclass),
"user_id" BIGINT,
"password_hash" CHARACTER VARYING(255),
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "oauth_authorizations_pkey" PRIMARY KEY (id)
);

CREATE TABLE public.actors
(
id BIGINT NOT NULL DEFAULT nextval('users_id_seq'::regclass),

/*used to be 'ap_id'*/
uri CHARACTER VARYING(255),
nickname CITEXT,
local BOOLEAN DEFAULT true,
"primary_user_id" BIGINT,
type ARRAY,
"actor_openness" CHARACTER VARYING(100),
name CHARACTER VARYING(255),
bio TEXT,
avatar JSONB,
info JSONB,
"follower_address" CHARACTER VARYING(255),
"follower_count" BIGINT,
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "users_pkey" PRIMARY KEY (id)
);

CREATE TABLE public."actors_relations"
(
id BIGINT NOT NULL DEFAULT nextval('lists_id_seq'::regclass),
"subject_actor_id" BIGINT,
"target_actor_id" BIGINT,
type ARRAY DEFAULT following,
confirmed BOOLEAN,
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "lists_pkey" PRIMARY KEY (id)
);

CREATE TABLE public."accounts_users"
(
id BIGINT NOT NULL DEFAULT nextval('users_id_seq'::regclass),
email CITEXT,
"primary_actor_id" BIGINT,
"following_count" BIGINT,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "users_pkey" PRIMARY KEY (id)
);

CREATE TABLE public.apps
(
id BIGINT NOT NULL DEFAULT nextval('apps_id_seq'::regclass),
"client_name" CHARACTER VARYING(255),
"redirect_uris" CHARACTER VARYING(255),
scopes CHARACTER VARYING(255),
website CHARACTER VARYING(255),
"client_id" CHARACTER VARYING(255),
"client_secret" CHARACTER VARYING(255),
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "apps_pkey" PRIMARY KEY (id)
);

CREATE TABLE public.lists
(
id BIGINT NOT NULL DEFAULT nextval('lists_id_seq'::regclass),
"user_id" BIGINT,
title CHARACTER VARYING(255),
following ARRAY,
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "lists_pkey" PRIMARY KEY (id)
);

CREATE TABLE public.notifications
(
id BIGINT NOT NULL DEFAULT nextval('notifications_id_seq'::regclass),
"user_id" BIGINT,
"activity_id" BIGINT,
seen BOOLEAN DEFAULT false,
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "notifications_pkey" PRIMARY KEY (id)
);

CREATE TABLE public."oauth_authorizations"
(
id BIGINT NOT NULL DEFAULT nextval('oauth_authorizations_id_seq'::regclass),
"app_id" BIGINT,
"user_id" BIGINT,
token CHARACTER VARYING(255),
"valid_until" TIMESTAMP WITHOUT TIME ZONE,
used BOOLEAN DEFAULT false,
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "oauth_authorizations_pkey" PRIMARY KEY (id)
);

CREATE TABLE public."oauth_tokens"
(
id BIGINT NOT NULL DEFAULT nextval('oauth_tokens_id_seq'::regclass),
"app_id" BIGINT,
"user_id" BIGINT,
token CHARACTER VARYING(255),
"refresh_token" CHARACTER VARYING(255),
"valid_until" TIMESTAMP WITHOUT TIME ZONE,
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "oauth_tokens_pkey" PRIMARY KEY (id)
);

CREATE TABLE public.objects
(
id BIGINT NOT NULL DEFAULT nextval('objects_id_seq'::regclass),
uri CHARACTER VARYING(255),
data JSONB,
"like_count" BIGINT,
"share_count" BIGINT,
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "objects_pkey" PRIMARY KEY (id)
);

CREATE TABLE public.activities
(
id BIGINT NOT NULL DEFAULT nextval('activities_id_seq'::regclass),
uri TEXT,
data JSONB,
local BOOLEAN DEFAULT true,
"actor_uri" CHARACTER VARYING(255),
"actor_id" BIGINT,
"object_uri" CHARACTER VARYING(255),
"object_id" BIGINT,
"recipient_actor_uris" ARRAY,
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "activities_pkey" PRIMARY KEY (id)
);

CREATE TABLE public."password_reset_tokens"
(
id BIGINT NOT NULL DEFAULT nextval('password_reset_tokens_id_seq'::regclass),
"user_id" BIGINT,
token CHARACTER VARYING(255),
used BOOLEAN DEFAULT false,
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "password_reset_tokens_pkey" PRIMARY KEY (id)
);

CREATE TABLE public."schema_migrations"
(
version BIGINT NOT NULL,
"inserted_at" TIMESTAMP WITHOUT TIME ZONE,
CONSTRAINT "schema_migrations_pkey" PRIMARY KEY (version)
);

CREATE TABLE public."user_invite_tokens"
(
id BIGINT NOT NULL DEFAULT nextval('user_invite_tokens_id_seq'::regclass),
token CHARACTER VARYING(255),
used BOOLEAN DEFAULT false,
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "user_invite_tokens_pkey" PRIMARY KEY (id)
);

CREATE TABLE public."websub_client_subscriptions"
(
id BIGINT NOT NULL DEFAULT nextval('websub_client_subscriptions_id_seq'::regclass),
topic CHARACTER VARYING(255),
secret CHARACTER VARYING(255),
"valid_until" TIMESTAMP WITHOUT TIME ZONE,
state CHARACTER VARYING(255),
subscribers JSONB,
hub CHARACTER VARYING(255),
"user_id" BIGINT,
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "websub_client_subscriptions_pkey" PRIMARY KEY (id)
);

CREATE TABLE public."websub_server_subscriptions"
(
id BIGINT NOT NULL DEFAULT nextval('websub_server_subscriptions_id_seq'::regclass),
topic CHARACTER VARYING(255),
callback CHARACTER VARYING(255),
secret CHARACTER VARYING(255),
"valid_until" TIMESTAMP WITHOUT TIME ZONE,
state CHARACTER VARYING(255),
"inserted_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"updated_at" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
CONSTRAINT "websub_server_subscriptions_pkey" PRIMARY KEY (id)
);

ALTER TABLE public."accounts_password_auth" ADD CONSTRAINT "oauth_authorizations_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES public."accounts_users" (id);

CREATE INDEX "public.actors_uri_idx" ON public.actors(uri);

ALTER TABLE public.actors ADD CONSTRAINT "oauth_tokens_user_id_fkey" FOREIGN KEY ("primary_user_id") REFERENCES public."accounts_users" (id);

ALTER TABLE public."actors_relations" ADD CONSTRAINT "lists_user_id_fkey" FOREIGN KEY ("subject_actor_id") REFERENCES public.actors (id);

ALTER TABLE public."actors_relations" ADD CONSTRAINT "lists_user_id_fkey" FOREIGN KEY ("target_actor_id") REFERENCES public.actors (id);

ALTER TABLE public."accounts_users" ADD FOREIGN KEY ("primary_actor_id") REFERENCES public.actors (id);

ALTER TABLE public.lists ADD CONSTRAINT "lists_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES public."accounts_users" (id) ON DELETE CASCADE  ON UPDATE NO ACTION;

ALTER TABLE public.notifications ADD CONSTRAINT "notifications_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES public."accounts_users" (id) ON DELETE CASCADE  ON UPDATE NO ACTION;

ALTER TABLE public.notifications ADD CONSTRAINT "notifications_activity_id_fkey" FOREIGN KEY ("activity_id") REFERENCES public.activities (id) ON DELETE CASCADE  ON UPDATE NO ACTION;

ALTER TABLE public."oauth_authorizations" ADD CONSTRAINT "oauth_authorizations_app_id_fkey" FOREIGN KEY ("app_id") REFERENCES public.apps (id) ON DELETE NO ACTION  ON UPDATE NO ACTION;

ALTER TABLE public."oauth_authorizations" ADD CONSTRAINT "oauth_authorizations_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES public."accounts_users" (id) ON DELETE NO ACTION  ON UPDATE NO ACTION;

ALTER TABLE public."oauth_tokens" ADD CONSTRAINT "oauth_tokens_app_id_fkey" FOREIGN KEY ("app_id") REFERENCES public.apps (id) ON DELETE NO ACTION  ON UPDATE NO ACTION;

ALTER TABLE public."oauth_tokens" ADD CONSTRAINT "oauth_tokens_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES public."accounts_users" (id) ON DELETE NO ACTION  ON UPDATE NO ACTION;

CREATE INDEX "public.activities_actor_uri_idx" ON public.activities("actor_uri");

CREATE INDEX "public.activities_actor_id_idx" ON public.activities("actor_id");

ALTER TABLE public.activities ADD FOREIGN KEY ("actor_id") REFERENCES public.actors (id);

CREATE INDEX "public.activities_object_id_idx" ON public.activities("object_id");

ALTER TABLE public.activities ADD FOREIGN KEY ("object_id") REFERENCES public.objects (id);

ALTER TABLE public."password_reset_tokens" ADD CONSTRAINT "password_reset_tokens_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES public."accounts_users" (id) ON DELETE NO ACTION  ON UPDATE NO ACTION;

ALTER TABLE public."websub_client_subscriptions" ADD CONSTRAINT "websub_client_subscriptions_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES public."accounts_users" (id) ON DELETE NO ACTION  ON UPDATE NO ACTION;