Changes between Initial Version and Version 1 of Eventix/Faza2A

Timestamp:

04/15/26 11:35:14 (2 weeks ago)

Author:

231070

Comment:

--

Eventix/Faza2A

@@ +1 @@
+= Фаза 2A – Генерирање DDL
+
+== Опис
+Оваа фаза опфаќа генерирање на DDL за ЕР дијаграмот со соодветни ограничувања и дифолтни вредности.
+
+
+== Содржина на DDL скрипта
+
+CREATE TABLE APP_USER (
+    id            BIGSERIAL    NOT NULL,
+    first_name    VARCHAR(20),
+    last_name     VARCHAR(20),
+    email         VARCHAR(50)  NOT NULL UNIQUE,
+    phone         VARCHAR(20)  NOT NULL,
+    created_at    DATE         NOT NULL,
+    password_hash VARCHAR(20)  NOT NULL,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_email_format
+        CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
+    CONSTRAINT CHK_phone_number_format
+        CHECK (phone ~ '^[0-9]{7,15}$')
+);
+
+CREATE TABLE ROLES (
+    id   BIGSERIAL   NOT NULL,
+    role VARCHAR(20) NOT NULL,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_role_title
+        CHECK (role IN ('ADMINISTRATOR', 'USER'))
+);
+
+CREATE TABLE USER_ROLES (
+    APP_USERid BIGINT NOT NULL,
+    ROLESid    BIGINT NOT NULL,
+    PRIMARY KEY (APP_USERid, ROLESid),
+    CONSTRAINT FK_user_roles_user
+        FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_user_roles_role
+        FOREIGN KEY (ROLESid) REFERENCES ROLES (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+CREATE TABLE CATEGORIZATION (
+    id   BIGSERIAL   NOT NULL,
+    name VARCHAR(20) NOT NULL,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_category_values
+        CHECK (name IN ('Concerts', 'Theatre', 'Cinema', 'Festivals', 'Exhibitions', 'Seminars'))
+);
+
+CREATE TABLE SUBCATEGORY (
+    id              BIGSERIAL   NOT NULL,
+    name            VARCHAR(20) NOT NULL,
+    CATEGORIZATIONid BIGINT,
+    PRIMARY KEY (id),
+    CONSTRAINT FK_subcategory_categorization
+        FOREIGN KEY (CATEGORIZATIONid) REFERENCES CATEGORIZATION (id)
+        ON UPDATE CASCADE ON DELETE SET NULL
+);
+
+CREATE TABLE USER_CATEGORY_SUBSCRIPTION (
+    APP_USERid       BIGINT NOT NULL DEFAULT 1,
+    CATEGORIZATIONid BIGINT NOT NULL DEFAULT 1,
+    PRIMARY KEY (APP_USERid, CATEGORIZATIONid),
+    CONSTRAINT FK_user_category_sub_user
+        FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
+        ON UPDATE CASCADE ON DELETE SET DEFAULT,
+    CONSTRAINT FK_user_category_sub_categorization
+        FOREIGN KEY (CATEGORIZATIONid) REFERENCES CATEGORIZATION (id)
+        ON UPDATE CASCADE ON DELETE SET DEFAULT
+);
+
+CREATE TABLE USER_SUBCATEGORY_SUBSCRIPTION (
+    APP_USERid   BIGINT NOT NULL DEFAULT 1,
+    SUBCATEGORYid BIGINT NOT NULL DEFAULT 1,
+    PRIMARY KEY (APP_USERid, SUBCATEGORYid),
+    CONSTRAINT FK_user_subcategory_sub_user
+        FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
+        ON UPDATE CASCADE ON DELETE SET DEFAULT,
+    CONSTRAINT FK_user_subcategory_sub_subcategory
+        FOREIGN KEY (SUBCATEGORYid) REFERENCES SUBCATEGORY (id)
+        ON UPDATE CASCADE ON DELETE SET DEFAULT
+);
+
+CREATE TABLE EVENT (
+    id              BIGSERIAL    NOT NULL,
+    title           VARCHAR(50)  NOT NULL,
+    description     VARCHAR(255),
+    start_date      DATE         NOT NULL,
+    end_date        DATE         NOT NULL,
+    CATEGORIZATIONid BIGINT,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_event_dates
+        CHECK (end_date >= start_date),
+    CONSTRAINT FK_event_categorization
+        FOREIGN KEY (CATEGORIZATIONid) REFERENCES CATEGORIZATION (id)
+        ON UPDATE CASCADE ON DELETE SET NULL
+);
+
+CREATE TABLE EVENT_IMAGE (
+    id        BIGSERIAL    NOT NULL,
+    image_url VARCHAR(255) NOT NULL,
+    EVENTid   BIGINT,
+    PRIMARY KEY (id),
+    CONSTRAINT FK_event_image_event
+        FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
+        ON UPDATE CASCADE ON DELETE CASCADE
+);
+
+CREATE TABLE EVENT_ROLE (
+    id   BIGSERIAL   NOT NULL,
+    role VARCHAR(20) NOT NULL,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_event_role_values
+        CHECK (role IN ('EVENT_ADMIN', 'SALES_MANAGER', 'INFO_ADMIN', 'CONTENT_MANAGER'))
+);
+
+CREATE TABLE USER_EVENT (
+    APP_USERid   BIGINT NOT NULL,
+    EVENTid      BIGINT NOT NULL,
+    EVENT_ROLEid BIGINT NOT NULL,
+    PRIMARY KEY (APP_USERid, EVENTid),
+    CONSTRAINT FK_user_event_user
+        FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_user_event_event
+        FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_user_event_role
+        FOREIGN KEY (EVENT_ROLEid) REFERENCES EVENT_ROLE (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+CREATE TABLE VENUE (
+    id      BIGSERIAL   NOT NULL,
+    name    VARCHAR(20) NOT NULL,
+    city    VARCHAR(20),
+    address VARCHAR(50) NOT NULL,
+    PRIMARY KEY (id)
+);
+
+CREATE TABLE HALL (
+    id       BIGSERIAL   NOT NULL,
+    name     VARCHAR(20) NOT NULL,
+    capacity INT         NOT NULL,
+    VENUEid  BIGINT,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_capacity_positive_number
+        CHECK (capacity > 0),
+    CONSTRAINT FK_hall_venue
+        FOREIGN KEY (VENUEid) REFERENCES VENUE (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+CREATE TABLE EVENT_HALL (
+    EVENTid        BIGINT  NOT NULL,
+    HALLid         BIGINT  NOT NULL,
+    allowed_access BOOLEAN NOT NULL,
+    PRIMARY KEY (EVENTid, HALLid),
+    CONSTRAINT FK_event_hall_event
+        FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_event_hall_hall
+        FOREIGN KEY (HALLid) REFERENCES HALL (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+CREATE TABLE TICKET_TYPE (
+    id                 BIGSERIAL   NOT NULL,
+    name               VARCHAR(50) NOT NULL,
+    quantity_available INT         NOT NULL,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_ticket_quantity_positive_num
+        CHECK (quantity_available >= 0),
+    CONSTRAINT CHK_ticket_type_values
+        CHECK (name IN ('GENERAL_ADMISSION', 'STANDARD', 'PARTER', 'VIP'))
+);
+
+CREATE TABLE SEAT (
+    id            BIGSERIAL NOT NULL,
+    number        INT       NOT NULL,
+    HALLid        BIGINT,
+    TICKET_TYPEid BIGINT,
+    PRIMARY KEY (id),
+    CONSTRAINT FK_seat_hall
+        FOREIGN KEY (HALLid) REFERENCES HALL (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_seat_ticket_type
+        FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id)
+        ON UPDATE CASCADE ON DELETE SET NULL
+);
+
+CREATE TABLE EVENT_TICKET_TYPE (
+    EVENTid       BIGINT NOT NULL,
+    TICKET_TYPEid BIGINT NOT NULL,
+    price         INT    NOT NULL,
+    PRIMARY KEY (EVENTid, TICKET_TYPEid),
+    CONSTRAINT CHK_ticket_price_positive_num
+        CHECK (price > 0),
+    CONSTRAINT FK_event_ticket_type_event
+        FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_event_ticket_type_ticket_type
+        FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+CREATE TABLE STATUS (
+    id          BIGSERIAL    NOT NULL,
+    name        VARCHAR(20)  NOT NULL,
+    description VARCHAR(255),
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_order_status_values
+        CHECK (name IN ('CREATED', 'PAID', 'CANCELLED'))
+);
+
+CREATE TABLE PROMO_CODE (
+    id              BIGSERIAL   NOT NULL,
+    code            VARCHAR(20) NOT NULL UNIQUE,
+    discount_percent INT        NOT NULL,
+    expiration_date  DATE       NOT NULL,
+    APP_USERid       BIGINT     DEFAULT 1,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_discount_percent
+        CHECK (discount_percent > 0 AND discount_percent < 100),
+    CONSTRAINT FK_promo_code_user
+        FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
+        ON UPDATE CASCADE ON DELETE SET DEFAULT
+);
+
+CREATE TABLE USER_ORDER (
+    id           BIGSERIAL NOT NULL,
+    order_date   DATE      NOT NULL,
+    total_amount INT       NOT NULL,
+    APP_USERid   BIGINT,
+    STATUSid     BIGINT,
+    PROMO_CODEid BIGINT,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_order_amount_positive_num
+        CHECK (total_amount > 0),
+    CONSTRAINT FK_user_order_user
+        FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_user_order_status
+        FOREIGN KEY (STATUSid) REFERENCES STATUS (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_user_order_promo_code
+        FOREIGN KEY (PROMO_CODEid) REFERENCES PROMO_CODE (id)
+        ON UPDATE CASCADE ON DELETE SET NULL
+);
+
+CREATE TABLE TICKET (
+    id            BIGSERIAL   NOT NULL,
+    code          VARCHAR(50) NOT NULL UNIQUE,
+    status        VARCHAR(20) NOT NULL,
+    TICKET_TYPEid BIGINT,
+    USER_ORDERid  BIGINT,
+    SEATid        BIGINT,
+    APP_USERid    BIGINT,
+    EVENTid       BIGINT,
+    HALLid        BIGINT,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_ticket_status_values
+        CHECK (status IN ('ACTIVE', 'USED', 'CANCELLED')),
+    CONSTRAINT FK_ticket_ticket_type
+        FOREIGN KEY (TICKET_TYPEid) REFERENCES TICKET_TYPE (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_ticket_user_order
+        FOREIGN KEY (USER_ORDERid) REFERENCES USER_ORDER (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_ticket_seat
+        FOREIGN KEY (SEATid) REFERENCES SEAT (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_ticket_user
+        FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_ticket_event
+        FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_ticket_hall
+        FOREIGN KEY (HALLid) REFERENCES HALL (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+CREATE TABLE PAYMENT_METHOD (
+    id   BIGSERIAL   NOT NULL,
+    name VARCHAR(20) NOT NULL,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_payment_method_name
+        CHECK (name IN ('CASH', 'CARD'))
+);
+
+CREATE TABLE PAYMENT (
+    id               BIGSERIAL   NOT NULL,
+    amount           INT         NOT NULL,
+    status           VARCHAR(20) NOT NULL,
+    payment_date     DATE        NOT NULL,
+    USER_ORDERid     BIGINT,
+    PAYMENT_METHODid BIGINT,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_payment_status_values
+        CHECK (status IN ('PENDING', 'COMPLETED', 'FAILED')),
+    CONSTRAINT CHK_amount_positive_number
+        CHECK (amount > 0),
+    CONSTRAINT FK_payment_user_order
+        FOREIGN KEY (USER_ORDERid) REFERENCES USER_ORDER (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_payment_payment_method
+        FOREIGN KEY (PAYMENT_METHODid) REFERENCES PAYMENT_METHOD (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+CREATE TABLE REFUND (
+    id        BIGSERIAL    NOT NULL,
+    amount    INT          NOT NULL,
+    reason    VARCHAR(255) NOT NULL,
+    status    VARCHAR(20)  NOT NULL,
+    PAYMENTid BIGINT,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_refund_amount_positive_num
+        CHECK (amount > 0),
+    CONSTRAINT CHK_refund_status_values
+        CHECK (status IN ('REQUESTED', 'APPROVED', 'REJECTED')),
+    CONSTRAINT FK_refund_payment
+        FOREIGN KEY (PAYMENTid) REFERENCES PAYMENT (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+CREATE TABLE REVIEW (
+    id         BIGSERIAL    NOT NULL,
+    rating     INT          NOT NULL,
+    comment    VARCHAR(255),
+    APP_USERid BIGINT DEFAULT 1,
+    EVENTid    BIGINT,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_rating_range
+        CHECK (rating BETWEEN 1 AND 5),
+    CONSTRAINT FK_review_user
+        FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
+        ON UPDATE CASCADE ON DELETE SET DEFAULT,
+    CONSTRAINT FK_review_event
+        FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
+        ON UPDATE CASCADE ON DELETE CASCADE
+);
+
+CREATE TABLE NOTIFICATION (
+    id         BIGSERIAL    NOT NULL,
+    message    VARCHAR(150) NOT NULL,
+    created_at DATE         NOT NULL,
+    APP_USERid BIGINT DEFAULT 1,
+    PRIMARY KEY (id),
+    CONSTRAINT FK_notification_user
+        FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
+        ON UPDATE CASCADE ON DELETE SET DEFAULT 
+);
+
+CREATE TABLE WAITLIST (
+    id         BIGSERIAL   NOT NULL,
+    created_at DATE        NOT NULL,
+    status     VARCHAR(20) NOT NULL,
+    EVENTid    BIGINT,
+    PRIMARY KEY (id),
+    CONSTRAINT CHK_waitlist_status_values
+        CHECK (status IN ('PENDING', 'CONFIRMED', 'CANCELLED')),
+    CONSTRAINT FK_waitlist_event
+        FOREIGN KEY (EVENTid) REFERENCES EVENT (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT
+);
+
+CREATE TABLE USER_WAITLIST (
+    APP_USERid BIGINT NOT NULL,
+    WAITLISTid BIGINT NOT NULL,
+    PRIMARY KEY (APP_USERid, WAITLISTid),
+    CONSTRAINT FK_user_waitlist_user
+        FOREIGN KEY (APP_USERid) REFERENCES APP_USER (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT,
+    CONSTRAINT FK_user_waitlist_waitlist
+        FOREIGN KEY (WAITLISTid) REFERENCES WAITLIST (id)
+        ON UPDATE CASCADE ON DELETE RESTRICT
+);