schema.sql

CREATE TYPE "event_type" AS ENUM (
    'general',
    'personal',
    'group'
    );

CREATE TYPE "application_status" AS ENUM (
    'pending',
    'approved',
    'rejected'
    );

CREATE TABLE "notification_preferences"
(
    "id"                    INTEGER NOT NULL UNIQUE GENERATED BY DEFAULT AS IDENTITY,
    "notify_telegram"       BOOLEAN NOT NULL DEFAULT false,
    "notify_email"          BOOLEAN NOT NULL DEFAULT false,
    "reminder_before_1day"  BOOLEAN NOT NULL DEFAULT true,
    "reminder_before_1hour" BOOLEAN NOT NULL DEFAULT true,
    PRIMARY KEY ("id")
);

CREATE TABLE "users"
(
    "id"                          INTEGER      NOT NULL UNIQUE GENERATED BY DEFAULT AS IDENTITY,
    "login"                       VARCHAR(32)  NOT NULL UNIQUE,
    "password_hash"               VARCHAR(255) NOT NULL,
    "name"                        VARCHAR(32)  NOT NULL,
    "surname"                     VARCHAR(64)  NOT NULL,
    "email"                       VARCHAR(64),
    "telegram_id"                 VARCHAR(32),
    "notification_preferences_id" INTEGER      NOT NULL,
    "avatar"                      BYTEA,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("notification_preferences_id") REFERENCES "notification_preferences" ("id")
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE "events"
(
    "id"          INTEGER       NOT NULL UNIQUE GENERATED BY DEFAULT AS IDENTITY,
    "name"        VARCHAR(64)   NOT NULL,
    "description" TEXT,
    "time_start"  TIMESTAMP     NOT NULL,
    "time_end"    TIMESTAMP,
    "type"        EVENT_TYPE    NOT NULL,
    "location"    VARCHAR(1000) NOT NULL,
    "created_by_id"  INTEGER       NOT NULL,
    "keywords"    JSONB         NOT NULL DEFAULT '[]'::jsonb,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("created_by_id") REFERENCES "users" ("id")
        ON UPDATE CASCADE ON DELETE NO ACTION 
);

CREATE TABLE "event_participants"
(
    "user_id"           INTEGER NOT NULL,
    "event_id"          INTEGER NOT NULL,
    "is_speaker"        BOOLEAN NOT NULL DEFAULT false,
    "attendance_marked" BOOLEAN NOT NULL DEFAULT false,
    PRIMARY KEY ("user_id", "event_id"),
    FOREIGN KEY ("user_id") REFERENCES "users" ("id")
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY ("event_id") REFERENCES "events" ("id")
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE "event_photos"
(
    "id"          INTEGER   NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "event_id"    INTEGER   NOT NULL,
    "image"       BYTEA     NOT NULL,
    "mime_type"   TEXT      NOT NULL,
    "uploaded_at" TIMESTAMP NOT NULL DEFAULT now(),
    "description" TEXT,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("event_id") REFERENCES "events" ("id")
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE "event_feedback"
(
    "id"         INTEGER   NOT NULL UNIQUE GENERATED BY DEFAULT AS IDENTITY,
    "user_id"    INTEGER   NOT NULL,
    "event_id"   INTEGER   NOT NULL,
    "rating"     SMALLINT  NOT NULL CHECK (rating BETWEEN 1 AND 5),
    "created_at" TIMESTAMP NOT NULL DEFAULT now(),
    "comment"    TEXT,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("event_id") REFERENCES "events" ("id")
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY ("user_id") REFERENCES "users" ("id")
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE "speaker_applications"
(
    "id"         INTEGER            NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "user_id"    INTEGER            NOT NULL,
    "event_id"   INTEGER            NOT NULL,
    "topic"      TEXT               NOT NULL,
    "status"     APPLICATION_STATUS NOT NULL DEFAULT 'pending',
    "created_at" TIMESTAMP          NOT NULL DEFAULT now(),
    PRIMARY KEY ("id"),
    FOREIGN KEY ("event_id") REFERENCES "events" ("id")
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY ("user_id") REFERENCES "users" ("id")
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE "teams"
(
    "id"          INTEGER      NOT NULL UNIQUE GENERATED BY DEFAULT AS IDENTITY,
    "name"        VARCHAR(255) NOT NULL UNIQUE,
    "description" TEXT,
    PRIMARY KEY ("id")
);

CREATE TABLE "user_teams"
(
    "user_id" INTEGER NOT NULL,
    "team_id" INTEGER NOT NULL,
    PRIMARY KEY ("user_id", "team_id"),
    FOREIGN KEY ("user_id") REFERENCES "users" ("id")
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY ("team_id") REFERENCES "teams" ("id")
        ON UPDATE CASCADE ON DELETE CASCADE
);