CREATE TABLE APP_USER (
    id            BIGSERIAL   NOT NULL,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    email         VARCHAR(50)  NOT NULL UNIQUE,
    phone         VARCHAR(20)  NOT NULL,
    created_at    DATE         NOT NULL,
    user_password VARCHAR(255)  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_name VARCHAR(20) NOT null unique,
    PRIMARY KEY (id),
    CONSTRAINT CHK_role_title
        CHECK (role_name 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,
    category_name VARCHAR(20) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT CHK_category_values
        CHECK (category_name IN ('Uncategorized', 'Concerts', 'Theatre', 'Cinema', 'Festivals', 'Exhibitions', 'Seminars'))
);

CREATE TABLE SUBCATEGORY (
    id              BIGSERIAL   NOT NULL,
    subcategory_name VARCHAR(50) NOT NULL,
    CATEGORIZATIONid BIGINT NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT FK_subcategory_categorization
        FOREIGN KEY (CATEGORIZATIONid) REFERENCES CATEGORIZATION (id)
        ON UPDATE CASCADE ON DELETE RESTRICT 
);

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(100)  NOT NULL,
    description     VARCHAR(255),
    start_date      DATE         NOT NULL,
    end_date        DATE         NOT NULL,
    CATEGORIZATIONid BIGINT NOT NULL DEFAULT 1,
    SUBCATEGORYid 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 DEFAULT,
    CONSTRAINT FK_event_subcategory
        FOREIGN KEY (SUBCATEGORYid) REFERENCES SUBCATEGORY (id)
        ON UPDATE CASCADE ON DELETE SET NULL
);

CREATE TABLE EVENT_IMAGE (
    id        BIGSERIAL    NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    EVENTid   BIGINT NOT NULL,
    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_name VARCHAR(20) NOT null unique,
    PRIMARY KEY (id),
    CONSTRAINT CHK_event_role_values
        CHECK (role_name 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,
    venue_name    VARCHAR(100) NOT NULL,
    city    VARCHAR(50),
    address VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE HALL (
    id       BIGSERIAL   NOT NULL,
    hall_name     VARCHAR(50) NOT NULL,
    capacity INT         NOT NULL,
    VENUEid  BIGINT NOT NULL,
    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,
    type_name               VARCHAR(50) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT CHK_ticket_type_values
        CHECK (type_name IN ('GENERAL_ADMISSION', 'STANDARD', 'PARTER', 'VIP'))
);

CREATE TABLE SEAT (
    id            BIGSERIAL NOT NULL,
    seat_number   INT       NOT NULL,
    HALLid        BIGINT NOT NULL,
    TICKET_TYPEid BIGINT NOT NULL,
    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 RESTRICT
);

CREATE TABLE EVENT_TICKET_TYPE (
    EVENTid       BIGINT    NOT NULL,
    TICKET_TYPEid BIGINT    NOT NULL,
    price         INT       NOT NULL,
    quantity_available INT  NOT NULL,
    PRIMARY KEY (EVENTid, TICKET_TYPEid),
    CONSTRAINT CHK_ticket_price_positive_num
        CHECK (price > 0),
    CONSTRAINT CHK_ticket_quantity_positive_num
        CHECK (quantity_available >= 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,
    status_name        VARCHAR(20)  NOT NULL,
    description VARCHAR(255),
    PRIMARY KEY (id),
    CONSTRAINT CHK_order_status_values
        CHECK (status_name IN ('CREATED', 'PAID', 'CANCELLED'))
);

CREATE TABLE PROMO_CODE (
    id              BIGSERIAL   NOT NULL,
    code            VARCHAR(50) 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 NOT NULL,
    STATUSid     BIGINT NOT NULL,
    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 NOT NULL,
    USER_ORDERid  BIGINT NOT NULL,
    SEATid        BIGINT,
    APP_USERid    BIGINT NOT NULL,
    EVENTid       BIGINT NOT NULL,
    HALLid        BIGINT NOT NULL,
    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,
    method_name VARCHAR(20) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT CHK_payment_method_name
        CHECK (method_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      NOT NULL,
    PAYMENT_METHODid BIGINT      NOT NULL,
    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       NOT NULL,
    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,
    review_comment    VARCHAR(255),
    APP_USERid BIGINT      DEFAULT 1,
    EVENTid    BIGINT       NOT NULL,
    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 NOT NULL 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      NOT NULL,
    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 CASCADE
);

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
);