CREATE EXTENSION pg_trgm;

DROP TABLE IF EXISTS "auteurs";
DROP SEQUENCE IF EXISTS auteurs_id_seq;
CREATE SEQUENCE auteurs_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1;

CREATE TABLE "public"."auteurs" (
    "id" bigint DEFAULT nextval('auteurs_id_seq') NOT NULL,
    "pseudo" text NOT NULL,
    "avatar" text,
    "jva" smallint DEFAULT '1' NOT NULL,
    CONSTRAINT "auteurs_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

create index idx_gin_auteurs_pseudo on auteurs using gin (pseudo gin_trgm_ops)


DROP TABLE IF EXISTS "messages";
DROP SEQUENCE IF EXISTS messages_id_seq;
CREATE SEQUENCE messages_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1;

CREATE TABLE "public"."messages" (
    "id" bigint DEFAULT nextval('messages_id_seq') NOT NULL,
    "date_post" timestamp,
    "texte" text,
    "topic_id" bigint,
    "auteur_id" bigint,
    "jva" integer DEFAULT '1' NOT NULL,
    CONSTRAINT "messages_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

create index idx_gin_messages_texte on messages using gin (texte gin_trgm_ops)

CREATE INDEX "messages_auteur_id" ON "public"."messages" USING btree ("auteur_id");

CREATE INDEX "messages_date_post" ON "public"."messages" USING btree ("date_post");

CREATE INDEX "messages_jva" ON "public"."messages" USING btree ("jva");

CREATE INDEX "messages_topic_id" ON "public"."messages" USING btree ("topic_id");


DROP TABLE IF EXISTS "topics";
DROP SEQUENCE IF EXISTS topics_id_seq;
CREATE SEQUENCE topics_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1;

CREATE TABLE "public"."topics" (
    "id" bigint DEFAULT nextval('topics_id_seq') NOT NULL,
    "titre" text,
    "nb_messages" bigint,
    "date_creation" timestamp,
    "date_suppression" timestamp,
    "restaure" smallint,
    "delete_by_modo" character(1),
    "auteur_id" bigint,
    "date_dernier_message_enregistre" timestamp,
    "date_dernier_message" timestamp,
    "nb_messages_enregistre" bigint,
    "jva" smallint DEFAULT '1' NOT NULL,
    CONSTRAINT "topics_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

CREATE INDEX "topics_auteur_id" ON "public"."topics" USING btree ("auteur_id");

CREATE INDEX "topics_date_creation" ON "public"."topics" USING btree ("date_creation");

CREATE INDEX "topics_date_dernier_message" ON "public"."topics" USING btree ("date_dernier_message");

CREATE INDEX "topics_date_dernier_message_enregistre" ON "public"."topics" USING btree ("date_dernier_message_enregistre");

CREATE INDEX "topics_date_suppression" ON "public"."topics" USING btree ("date_suppression");

CREATE INDEX "topics_delete_by_modo" ON "public"."topics" USING btree ("delete_by_modo");

CREATE INDEX "topics_jva" ON "public"."topics" USING btree ("jva");

CREATE INDEX "topics_nb_messages" ON "public"."topics" USING btree ("nb_messages");

CREATE INDEX "topics_nb_messages_enregistre" ON "public"."topics" USING btree ("nb_messages_enregistre");

CREATE INDEX "topics_restaure" ON "public"."topics" USING btree ("restaure");

CREATE INDEX "topics_titre" ON "public"."topics" USING btree ("titre");

create index idx_gin_topics_titre on topics using gin (titre gin_trgm_ops)