DatabaseCreation: ddl_updated.sql

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET search_path = public;
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

CREATE TABLE public.user_app (
    user_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    username varchar(20) NOT NULL UNIQUE,
    email varchar(50) NOT NULL UNIQUE,
    contact_phone varchar(50),
    CONSTRAINT chk_email_format CHECK (email LIKE '%@%.%')
);

CREATE TABLE public.order_status (
    status_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    status_name varchar(50) NOT NULL UNIQUE
);

CREATE TABLE public.event_status (
    event_status_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    status_name varchar(50) NOT NULL UNIQUE
);

CREATE TABLE public.category (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name varchar(100) NOT NULL UNIQUE
);

CREATE TABLE public.attribute (
    attribute_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    data_type varchar(20) NOT NULL,
    description text,
    is_required boolean NOT NULL
);

CREATE TABLE public.promo_code (
    promo_code_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    code varchar(64) NOT NULL UNIQUE
);

CREATE TABLE public.location_type (
    type_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    type_name varchar(100) NOT NULL UNIQUE
);

CREATE TABLE public.location (
    location_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name varchar(100) NOT NULL,
    type_id integer NOT NULL REFERENCES public.location_type(type_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    address varchar(255),
    city varchar(255) NOT NULL,
    capacity integer NOT NULL,
    CONSTRAINT chk_location_capacity CHECK (capacity > 0)
);

CREATE TABLE public.section (
    section_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    section_name varchar(100),
    capacity integer NOT NULL,
    location_id integer NOT NULL REFERENCES public.location(location_id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT chk_section_capacity CHECK (capacity > 0)
);

CREATE TABLE public.seat (
    seat_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    row_identifier varchar(10) NOT NULL,
    section_id integer NOT NULL REFERENCES public.section(section_id) ON UPDATE CASCADE ON DELETE CASCADE,
    seat_number varchar(10) NOT NULL,
    is_accessible boolean NOT NULL,
    is_available boolean NOT NULL
);

CREATE TABLE public.organiser (
    organiser_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    company_name varchar(255) NOT NULL,
    contact_phone varchar(50),
    website_url varchar(255)
);

CREATE TABLE public.event (
    event_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    event_status_id integer NOT NULL REFERENCES public.event_status(event_status_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    title varchar(64) NOT NULL,
    start_datetime timestamp without time zone NOT NULL,
    end_datetime timestamp without time zone,
    CONSTRAINT chk_event_chronology CHECK (end_datetime > start_datetime)
);

CREATE TABLE public.event_organiser (
    event_organiser_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    event_id integer NOT NULL REFERENCES public.event(event_id) ON UPDATE CASCADE ON DELETE CASCADE,
    organiser_id integer NOT NULL REFERENCES public.organiser(organiser_id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT uq_event_organiser UNIQUE (event_id, organiser_id)
);

CREATE TABLE public.event_category (
    event_category_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    event_id integer NOT NULL REFERENCES public.event(event_id) ON UPDATE CASCADE ON DELETE CASCADE,
    category_id integer NOT NULL REFERENCES public.category(id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE public.event_schedule_session (
    schedule_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    event_id integer NOT NULL REFERENCES public.event(event_id) ON UPDATE CASCADE ON DELETE CASCADE,
    session_title varchar(50),
    start_time timestamp without time zone,
    end_time timestamp without time zone,
    section_id integer NOT NULL REFERENCES public.section(section_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_session_chronology CHECK (end_time > start_time)
);

CREATE TABLE public.field (
    field_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    field_name varchar(50) NOT NULL
);

CREATE TABLE public.exhibitor (
    exhibitor_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name varchar(50) NOT NULL,
    surname varchar(50),
    field_id integer NOT NULL REFERENCES public.field(field_id) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE public.exhibitor_event_schedule_session (
    exhibitor_event_schedule_session_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    exhibitor_id integer NOT NULL REFERENCES public.exhibitor(exhibitor_id) ON UPDATE CASCADE ON DELETE CASCADE,
    event_schedule_session_id integer NOT NULL REFERENCES public.event_schedule_session(schedule_id) ON UPDATE CASCADE ON DELETE CASCADE,
    start_time timestamp without time zone NOT NULL,
    end_time timestamp without time zone NOT NULL,
    CONSTRAINT chk_exhibitor_session_chronology CHECK (end_time > start_time),
    CONSTRAINT uq_exhibitor_session UNIQUE (exhibitor_id, event_schedule_session_id)
);

CREATE TABLE public.discount (
    discount_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    event_id integer NOT NULL REFERENCES public.event(event_id) ON UPDATE CASCADE ON DELETE CASCADE,
    discount_percent numeric(5,2) NOT NULL,
    promo_code_id integer REFERENCES public.promo_code(promo_code_id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_discount_percent CHECK (discount_percent >= 0 AND discount_percent <= 100)
);

CREATE TABLE public.order_cart (
    order_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES public.user_app(user_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    discount_id integer REFERENCES public.discount(discount_id) ON UPDATE CASCADE ON DELETE SET NULL,
    status_id integer NOT NULL REFERENCES public.order_status(status_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    created_at timestamp without time zone NOT NULL,
    total_price numeric(12,2) NOT NULL,
    CONSTRAINT chk_order_total CHECK (total_price >= 0)
);

CREATE TABLE public.payment_method (
    method_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    method_name varchar(50) NOT NULL UNIQUE
);

CREATE TABLE public.payment (
    payment_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES public.order_cart(order_id) ON UPDATE CASCADE ON DELETE CASCADE,
    method_id integer NOT NULL REFERENCES public.payment_method(method_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    amount_paid numeric(12,2),
    processed_at timestamp without time zone NOT NULL,
    transaction_id varchar(255) NOT NULL,
    is_refunded boolean,
    CONSTRAINT chk_payment_amount CHECK (amount_paid >= 0)
);

CREATE TABLE public.refund_request (
    refund_request_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    reason text,
    requested_at timestamp without time zone NOT NULL,
    accepted_at timestamp without time zone,
    user_id bigint NOT NULL REFERENCES public.user_app(user_id) ON UPDATE CASCADE ON DELETE CASCADE,
    payment_id integer NOT NULL REFERENCES public.payment(payment_id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE public.review (
    review_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES public.user_app(user_id) ON UPDATE CASCADE ON DELETE CASCADE,
    event_id integer NOT NULL REFERENCES public.event(event_id) ON UPDATE CASCADE ON DELETE CASCADE,
    star_rating integer NOT NULL,
    review_text text,
    created_at timestamp without time zone NOT NULL,
    CONSTRAINT chk_star_rating CHECK (star_rating >= 1 AND star_rating <= 5),
    CONSTRAINT uq_user_event_review UNIQUE (user_id, event_id)
);

CREATE TABLE public.ticket_type (
    ticket_type_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    event_id integer NOT NULL REFERENCES public.event(event_id) ON UPDATE CASCADE ON DELETE CASCADE,
    tier_name varchar(100) NOT NULL,
    total_allocated integer NOT NULL,
    presale_start timestamp without time zone,
    presale_end timestamp without time zone,
    CONSTRAINT chk_presale_chronology CHECK (presale_end > presale_start),
    CONSTRAINT chk_ticket_allocated CHECK (total_allocated >= 0)
);

CREATE TABLE public.price_tier (
    price_tier_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ticket_type_id integer NOT NULL REFERENCES public.ticket_type(ticket_type_id) ON UPDATE CASCADE ON DELETE CASCADE,
    price numeric(10,2) NOT NULL,
    valid_from timestamp without time zone NOT NULL,
    valid_to timestamp without time zone NOT NULL,
    CONSTRAINT chk_price CHECK (price >= 0),
    CONSTRAINT chk_price_tier_chronology CHECK (valid_to > valid_from)
);

CREATE TABLE public.ticket (
    ticket_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES public.order_cart(order_id) ON UPDATE CASCADE ON DELETE CASCADE,
    ticket_type_id integer NOT NULL REFERENCES public.ticket_type(ticket_type_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    event_schedule_session_id integer NOT NULL REFERENCES public.event_schedule_session(schedule_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    lock_expires_at timestamp without time zone NOT NULL,
    status varchar(50) NOT NULL,
    barcode_hash varchar(255) NOT NULL UNIQUE,
    seat_id integer REFERENCES public.seat(seat_id) ON UPDATE CASCADE ON DELETE SET NULL,
    is_scanned boolean NOT NULL,
    scanned_at timestamp without time zone,
    is_presale boolean NOT NULL,
    CONSTRAINT chk_ticket_scanned_at_consistency CHECK (
        (is_scanned = true AND scanned_at IS NOT NULL)
        OR
        (is_scanned = false AND scanned_at IS NULL)
    )
);

CREATE TABLE public.sponsor (
    sponsor_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name varchar(64) NOT NULL UNIQUE
);

CREATE TABLE public.sponsor_event (
    sponsor_event_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    event_id integer NOT NULL REFERENCES public.event(event_id) ON UPDATE CASCADE ON DELETE CASCADE,
    sponsor_id integer NOT NULL REFERENCES public.sponsor(sponsor_id) ON UPDATE CASCADE ON DELETE CASCADE,
    sponsor_type varchar(64) NOT NULL,
    sponsor_amount_paid numeric(5,2) NOT NULL,
    CONSTRAINT chk_sponsor_amount CHECK (sponsor_amount_paid >= 0)
);

CREATE TABLE public.sponsor_type (
    sponsor_type_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    sponsor_amount_paid numeric(5,2) NOT NULL,
    type varchar(64) NOT NULL,
    CONSTRAINT chk_sponsor_type_amount CHECK (sponsor_amount_paid >= 0)
);

CREATE TABLE public.sponsor_type_sponsor (
    sponsor_type_sponsor_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    sponsor_id integer NOT NULL REFERENCES public.sponsor(sponsor_id) ON UPDATE CASCADE ON DELETE CASCADE,
    sponsor_type_id integer NOT NULL REFERENCES public.sponsor_type(sponsor_type_id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE public.staff_role (
    staff_role_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    description varchar(100) NOT NULL
);

CREATE TABLE public.staff_team (
    staff_team_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    team_name varchar(50) NOT NULL
);

CREATE TABLE public.staff_member (
    staff_member_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    assigned_role integer NOT NULL REFERENCES public.staff_role(staff_role_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    name varchar(20) NOT NULL,
    surname varchar(20) NOT NULL,
    contact_phone varchar(50),
    staff_team_id integer NOT NULL REFERENCES public.staff_team(staff_team_id) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE public.staff_team_event_schedule (
    schedule_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    shift_start timestamp without time zone NOT NULL,
    shift_end timestamp without time zone NOT NULL,
    is_available boolean DEFAULT true NOT NULL,
    staff_team_id integer NOT NULL REFERENCES public.staff_team(staff_team_id) ON UPDATE CASCADE ON DELETE CASCADE,
    event_id integer NOT NULL REFERENCES public.event(event_id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT chk_shift_chronology CHECK (shift_end > shift_start)
);

CREATE TABLE public.subscription_location (
    subscription_location_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES public.user_app(user_id) ON UPDATE CASCADE ON DELETE CASCADE,
    location_id integer NOT NULL REFERENCES public.location(location_id) ON UPDATE CASCADE ON DELETE CASCADE,
    created_at timestamp without time zone NOT NULL,
    CONSTRAINT uq_user_location_sub UNIQUE (user_id, location_id)
);

CREATE TABLE public.subscription_organiser (
    subscription_organiser_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES public.user_app(user_id) ON UPDATE CASCADE ON DELETE CASCADE,
    organiser_id integer NOT NULL REFERENCES public.organiser(organiser_id) ON UPDATE CASCADE ON DELETE CASCADE,
    created_at timestamp without time zone NOT NULL,
    CONSTRAINT uq_user_organiser_sub UNIQUE (user_id, organiser_id)
);

CREATE TABLE public.value (
    value_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    attribute_id integer NOT NULL REFERENCES public.attribute(attribute_id) ON UPDATE CASCADE ON DELETE CASCADE,
    value_string text,
    value_int integer,
    value_datetime timestamp without time zone,
    value_bool boolean,
    event_id integer NOT NULL REFERENCES public.event(event_id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE public.waitlist_entry (
    waitlist_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    joined_at timestamp without time zone NOT NULL,
    status varchar(20) NOT NULL,
    notified_at timestamp without time zone,
    expires_at timestamp without time zone,
    user_id bigint NOT NULL REFERENCES public.user_app(user_id) ON UPDATE CASCADE ON DELETE CASCADE,
    event_schedule_session_id integer NOT NULL REFERENCES public.event_schedule_session(schedule_id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT uq_user_session_waitlist UNIQUE (user_id, event_schedule_session_id)
);