CREATE TABLE country (
    country_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    country_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE city (
    city_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    city_name VARCHAR(100) NOT NULL,
    postal_code VARCHAR(20) NOT NULL,
    country_id BIGINT NOT NULL,
    UNIQUE (city_name, country_id),
    FOREIGN KEY (country_id)
        REFERENCES country(country_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE venue (
    venue_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    venue_title VARCHAR(100) NOT NULL,
    street_address VARCHAR(100) NOT NULL,
    capacity INTEGER NOT NULL CHECK (capacity > 0),
    city_id BIGINT NOT NULL,
    UNIQUE (venue_title, street_address, city_id), -- еден објект може да има повеке сали/простории
    FOREIGN KEY (city_id)
        REFERENCES city(city_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE admin (
    admin_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

CREATE TABLE customer (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    hash_password VARCHAR(100) NOT NULL,
    CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

CREATE TABLE category (
    category_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE,
    category_description VARCHAR(255) NOT NULL,
    parent_category_id BIGINT,
    FOREIGN KEY (parent_category_id)
        REFERENCES category(category_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE sponsor_type (
    sponsor_type_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sponsor_type_name VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255)
);

CREATE TABLE ticket_type (
    ticket_type_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    type_name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE sponsor (
    sponsor_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sponsor_name VARCHAR(100) NOT NULL UNIQUE,
    contact_email VARCHAR(100) NOT NULL,
    website VARCHAR(100),
    sponsor_type_id BIGINT NOT NULL,
    FOREIGN KEY (sponsor_type_id)
        REFERENCES sponsor_type(sponsor_type_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    CHECK (contact_email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

CREATE TABLE event (
    event_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(255),
    start_datetime TIMESTAMP NOT NULL,
    end_datetime TIMESTAMP NOT NULL,
    status VARCHAR(50) NOT NULL,
    venue_id BIGINT NOT NULL,
    category_id BIGINT NOT NULL,
    CHECK (end_datetime > start_datetime),
    CHECK (status IN ('DRAFT', 'PUBLISHED', 'ONGOING', 'COMPLETED', 'CANCELLED')),
    UNIQUE (name, start_datetime, venue_id),
    FOREIGN KEY (venue_id)
        REFERENCES venue(venue_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    FOREIGN KEY (category_id)
        REFERENCES category(category_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE seat (
    seat_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    seat_number INTEGER NOT NULL,
    row_number INTEGER NOT NULL,
    section INTEGER NOT NULL,
    section_type VARCHAR(100) NOT NULL,
    venue_id BIGINT NOT NULL,
    UNIQUE (venue_id, section, row_number, seat_number),
    FOREIGN KEY (venue_id)
        REFERENCES venue(venue_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE -- seat е зависен од venue
);

CREATE TABLE event_admin (
    admin_id BIGINT PRIMARY KEY,
    FOREIGN KEY (admin_id)
        REFERENCES admin(admin_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE ticket_admin (
    admin_id BIGINT PRIMARY KEY,
    FOREIGN KEY (admin_id)
        REFERENCES admin(admin_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE discount (
    discount_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    discount_code VARCHAR(50) NOT NULL UNIQUE,
    discount_percent INTEGER NOT NULL CHECK (discount_percent BETWEEN 0 AND 100),
    event_admin_id BIGINT NOT NULL,
    FOREIGN KEY (event_admin_id)
        REFERENCES event_admin(admin_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE payment (
    payment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount NUMERIC(10,2) NOT NULL CHECK (amount >= 0),
    payment_method VARCHAR(100) NOT NULL,
    payment_status VARCHAR(50) NOT NULL CHECK (payment_status IN ('PENDING','COMPLETED','FAILED','REFUNDED')),
    payment_date TIMESTAMP NOT NULL,
    customer_id BIGINT NOT NULL,
    discount_id BIGINT,
    CHECK (payment_date <= CURRENT_TIMESTAMP),
    CHECK (payment_method IN ('CASH','CARD','ONLINE','TRANSFER')),
    FOREIGN KEY (customer_id)
        REFERENCES customer(customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    FOREIGN KEY (discount_id)
        REFERENCES discount(discount_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE ticket (
    ticket_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status VARCHAR(50) NOT NULL DEFAULT 'AVAILABLE' CHECK (status IN ('AVAILABLE','RESERVED','PURCHASED','SCANNED','CANCELLED')),
    reserved_at TIMESTAMP,
    expires_at TIMESTAMP,
    purchased_at TIMESTAMP,
    ticket_price NUMERIC(10,2) NOT NULL CHECK (ticket_price >= 0),
    qr_code VARCHAR(255) NOT NULL UNIQUE,
    customer_id BIGINT,
    seat_id BIGINT NOT NULL,
    event_id BIGINT NOT NULL,
    ticket_type_id BIGINT NOT NULL,
    payment_id BIGINT,
    UNIQUE (seat_id, event_id),
    CHECK (purchased_at IS NULL OR reserved_at IS NULL OR purchased_at >= reserved_at),
    CHECK (expires_at IS NULL OR reserved_at IS NULL OR expires_at >= reserved_at),
    CHECK (purchased_at IS NULL OR expires_at IS NULL OR purchased_at <= expires_at),
    CHECK (
    (status = 'AVAILABLE' AND reserved_at IS NULL AND purchased_at IS NULL)
    OR
    (status = 'RESERVED' AND reserved_at IS NOT NULL AND purchased_at IS NULL)
    OR
    (status = 'PURCHASED' AND purchased_at IS NOT NULL)
    OR
    (status = 'SCANNED' AND purchased_at IS NOT NULL)
    OR
    (status = 'CANCELLED')),
    FOREIGN KEY (customer_id)
        REFERENCES customer(customer_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    FOREIGN KEY (seat_id)
        REFERENCES seat(seat_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    FOREIGN KEY (event_id)
        REFERENCES event(event_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    FOREIGN KEY (ticket_type_id)
        REFERENCES ticket_type(ticket_type_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    FOREIGN KEY (payment_id)
        REFERENCES payment(payment_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE event_media (
    media_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    url VARCHAR(255) NOT NULL,
    event_id BIGINT NOT NULL,
    FOREIGN KEY (event_id)
        REFERENCES event(event_id)
        ON DELETE CASCADE -- медија нема смисла без event
        ON UPDATE CASCADE
);

CREATE TABLE review (
    review_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    comment VARCHAR(255),
    rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    customer_id BIGINT NOT NULL,
    event_id BIGINT NOT NULL,
    UNIQUE (customer_id, event_id),
    FOREIGN KEY (customer_id)
        REFERENCES customer(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (event_id)
        REFERENCES event(event_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE notification (
    notification_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    message VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    is_read BOOLEAN DEFAULT FALSE NOT NULL,
    customer_id BIGINT,
    event_id BIGINT,
    CHECK (created_at <= CURRENT_TIMESTAMP),
    FOREIGN KEY (customer_id)
        REFERENCES customer(customer_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    FOREIGN KEY (event_id)
        REFERENCES event(event_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE seat_reservation (
    reservation_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    reserved_at TIMESTAMP NOT NULL,
    status VARCHAR(50) NOT NULL,
    seat_id BIGINT NOT NULL,
    ticket_id BIGINT NOT NULL,
    CHECK (status IN ('ACTIVE','CANCELLED','EXPIRED')),
    FOREIGN KEY (seat_id)
        REFERENCES seat(seat_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    FOREIGN KEY (ticket_id)
        REFERENCES ticket(ticket_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

CREATE TABLE event_sponsorship (
    sponsor_id BIGINT,
    event_id BIGINT,
    sponsorship_amount NUMERIC(12,2) NOT NULL CHECK (sponsorship_amount >= 0),
    contract_date TIMESTAMP NOT NULL,
    PRIMARY KEY (sponsor_id, event_id),
    CHECK (contract_date <= CURRENT_TIMESTAMP),
    FOREIGN KEY (sponsor_id)
        REFERENCES sponsor(sponsor_id)
        ON DELETE CASCADE -- bridge table
        ON UPDATE CASCADE,
    FOREIGN KEY (event_id)
        REFERENCES event(event_id)
        ON DELETE CASCADE -- bridge table
        ON UPDATE CASCADE
);

CREATE TABLE event_manages (
    event_id BIGINT,
    event_admin_id BIGINT,
    PRIMARY KEY (event_id, event_admin_id),
    FOREIGN KEY (event_id)
        REFERENCES event(event_id)
        ON DELETE CASCADE -- bridge table
        ON UPDATE CASCADE,
    FOREIGN KEY (event_admin_id)
        REFERENCES event_admin(admin_id)
        ON DELETE CASCADE -- bridge table
        ON UPDATE CASCADE
);