create

CREATE TABLE "user" (
    "id" NUMBER(10)   NOT NULL,
    "name" VARCHAR(255)   NOT NULL,
    "surname" VARCHAR(255)   NOT NULL,
    "studyYear" NUMBER(10)   NOT NULL,
    "login" VARCHAR(255)   NOT NULL,
    "password" VARCHAR(255)   NOT NULL,
    "email" VARCHAR(255)   NOT NULL,
    "titleBefore" VARCHAR(255)   NOT NULL,
    "titleAfter" VARCHAR(255)   NOT NULL,
    "blocked" NUMBER(1)   NOT NULL,
    "roleId" NUMBER(10)   NOT NULL,
    "address" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_user" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "message" (
    "id" NUMBER(10)   NOT NULL,
    "date" DATE   NOT NULL,
    "sender" NUMBER(10)   NOT NULL,
    "receiver" NUMBER(10)   NOT NULL,
    "text" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_message" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "group" (
    "id" NUMBER(10)   NOT NULL,
    "name" VARCHAR(255)   NOT NULL,
    "group_subjectId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_group" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "user_group" (
    "userId" NUMBER(10)   NOT NULL,
    "groupId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_user_group" PRIMARY KEY (
        "userId","groupId"
     )
);

CREATE TABLE "subject" (
    "id" NUMBER(10)   NOT NULL,
    "name" VARCHAR(255)   NOT NULL,
    "shortName" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_subject" PRIMARY KEY (
        "id"
     ),
    CONSTRAINT "uc_subject_shortName" UNIQUE (
        "shortName"
    )
);

CREATE TABLE "user_subject" (
    "userId" NUMBER(10)   NOT NULL,
    "subjectId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_user_subject" PRIMARY KEY (
        "userId","subjectId"
     )
);

CREATE TABLE "comment" (
    "id" NUMBER(10)   NOT NULL,
    "content" VARCHAR(255)   NOT NULL,
    "date" DATE   NOT NULL,
    "userId" NUMBER(10)   NOT NULL,
    "postId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_comment" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "like" (
    "userId" NUMBER(10)   NOT NULL,
    "postId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_like" PRIMARY KEY (
        "userId","postId"
     )
);

CREATE TABLE "post" (
    "id" NUMBER(10)   NOT NULL,
    "text" VARCHAR(255)   NOT NULL,
    "title" VARCHAR(255)   NOT NULL,
    "creator_userId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_post" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "file" (
    "id" NUMBER(10)   NOT NULL,
    "creationDate" DATE   NOT NULL,
    "name" VARCHAR(255)   NOT NULL,
    "data" blob   NOT NULL,
    "userId" NUMBER(10)   NOT NULL,
    "fileExtId" NUMBER(10)   NOT NULL,
    CONSTRAINT "pk_file" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "role" (
    "id" NUMBER(10)   NOT NULL,
    "name" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_role" PRIMARY KEY (
        "id"
     ),
    CONSTRAINT "uc_role_name" UNIQUE (
        "name"
    )
);

CREATE TABLE "address" (
    "id" NUMBER(10)   NOT NULL,
    "city" VARCHAR(255)   NOT NULL,
    "country" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_address" PRIMARY KEY (
        "id"
     )
);

CREATE TABLE "file_ext" (
    "id" NUMBER(10)   NOT NULL,
    "name" VARCHAR(255)   NOT NULL,
    CONSTRAINT "pk_file_ext" PRIMARY KEY (
        "id"
     ),
    CONSTRAINT "uc_file_ext_name" UNIQUE (
        "name"
    )
);

ALTER TABLE "user" ADD CONSTRAINT "fk_user_roleId" FOREIGN KEY("roleId")
REFERENCES "role" ("id");

ALTER TABLE "user" ADD CONSTRAINT "fk_user_address" FOREIGN KEY("address")
REFERENCES "address" ("id");

ALTER TABLE "message" ADD CONSTRAINT "fk_message_sender" FOREIGN KEY("sender")
REFERENCES "user" ("id");

ALTER TABLE "message" ADD CONSTRAINT "fk_message_receiver" FOREIGN KEY("receiver")
REFERENCES "user" ("id");

ALTER TABLE "group" ADD CONSTRAINT "fk_group_group_subjectId" FOREIGN KEY("group_subjectId")
REFERENCES "subject" ("id");

ALTER TABLE "user_group" ADD CONSTRAINT "fk_user_group_userId" FOREIGN KEY("userId")
REFERENCES "user" ("id");

ALTER TABLE "user_group" ADD CONSTRAINT "fk_user_group_groupId" FOREIGN KEY("groupId")
REFERENCES "group" ("id");

ALTER TABLE "user_subject" ADD CONSTRAINT "fk_user_subject_userId" FOREIGN KEY("userId")
REFERENCES "user" ("id");

ALTER TABLE "user_subject" ADD CONSTRAINT "fk_user_subject_subjectId" FOREIGN KEY("subjectId")
REFERENCES "subject" ("id");

ALTER TABLE "comment" ADD CONSTRAINT "fk_comment_userId" FOREIGN KEY("userId")
REFERENCES "user" ("id");

ALTER TABLE "comment" ADD CONSTRAINT "fk_comment_postId" FOREIGN KEY("postId")
REFERENCES "post" ("id");

ALTER TABLE "like" ADD CONSTRAINT "fk_like_userId" FOREIGN KEY("userId")
REFERENCES "user" ("id");

ALTER TABLE "like" ADD CONSTRAINT "fk_like_postId" FOREIGN KEY("postId")
REFERENCES "post" ("id");

ALTER TABLE "post" ADD CONSTRAINT "fk_post_creator_userId" FOREIGN KEY("creator_userId")
REFERENCES "user" ("id");

ALTER TABLE "file" ADD CONSTRAINT "fk_file_userId" FOREIGN KEY("userId")
REFERENCES "user" ("id");

ALTER TABLE "file" ADD CONSTRAINT "fk_file_fileExtId" FOREIGN KEY("fileExtId")
REFERENCES "file_ext" ("id");