AdvancedTopics: olap_fact_reservation.sql

-- FactReservation


CREATE TABLE FactReservation (
                                 reservation_key     BIGSERIAL,

                                 date_key            INTEGER       NOT NULL,
                                 check_in_date_key   INTEGER       NOT NULL,
                                 check_out_date_key  INTEGER       NOT NULL,
                                 hotel_key           INTEGER       NOT NULL,
                                 room_key            INTEGER       NOT NULL,
                                 pet_key             INTEGER       NOT NULL,
                                 customer_key        INTEGER       NOT NULL,
                                 employee_key        INTEGER       NOT NULL,

                                 total_cost          NUMERIC(10,2) NOT NULL,
                                 nights_stayed       INTEGER       NOT NULL,
                                 num_services        INTEGER       NOT NULL DEFAULT 0,
                                 has_delivery        BOOLEAN       NOT NULL DEFAULT FALSE,
                                 status               VARCHAR(20)   NOT NULL,
                                 payment_method        VARCHAR(30),
                                 payment_status        VARCHAR(20),
                                 amount_paid           NUMERIC(10,2)
)
    PARTITION BY RANGE (date_key);


CREATE TABLE FactReservation_2015
    PARTITION OF FactReservation
        FOR VALUES FROM (20150101) TO (20160101);

CREATE TABLE FactReservation_2016
    PARTITION OF FactReservation
        FOR VALUES FROM (20160101) TO (20170101);

CREATE TABLE FactReservation_2017
    PARTITION OF FactReservation
        FOR VALUES FROM (20170101) TO (20180101);

CREATE TABLE FactReservation_2018
    PARTITION OF FactReservation
        FOR VALUES FROM (20180101) TO (20190101);

CREATE TABLE FactReservation_2019
    PARTITION OF FactReservation
        FOR VALUES FROM (20190101) TO (20200101);

CREATE TABLE FactReservation_2020
    PARTITION OF FactReservation
        FOR VALUES FROM (20200101) TO (20210101);

CREATE TABLE FactReservation_2021
    PARTITION OF FactReservation
        FOR VALUES FROM (20210101) TO (20220101);

CREATE TABLE FactReservation_2022
    PARTITION OF FactReservation
        FOR VALUES FROM (20220101) TO (20230101);

CREATE TABLE FactReservation_2023
    PARTITION OF FactReservation
        FOR VALUES FROM (20230101) TO (20240101);

CREATE TABLE FactReservation_2024
    PARTITION OF FactReservation
        FOR VALUES FROM (20240101) TO (20250101);

CREATE TABLE FactReservation_2025
    PARTITION OF FactReservation
        FOR VALUES FROM (20250101) TO (20260101);

CREATE TABLE FactReservation_2026
    PARTITION OF FactReservation
        FOR VALUES FROM (20260101) TO (20270101);

CREATE TABLE FactReservation_2027
    PARTITION OF FactReservation
        FOR VALUES FROM (20270101) TO (20280101);


-- ETL
INSERT INTO FactReservation (
    date_key,
    check_in_date_key,
    check_out_date_key,
    hotel_key,
    room_key,
    pet_key,
    customer_key,
    employee_key,
    total_cost,
    nights_stayed,
    num_services,
    has_delivery,
    status,
    payment_method,
    payment_status,
    amount_paid
)
SELECT
    -- date_key
    TO_CHAR(r.reservation_date, 'YYYYMMDD')::INTEGER,

    -- check in / check out date keys
    TO_CHAR(rr.check_in_date,  'YYYYMMDD')::INTEGER,
    TO_CHAR(rr.check_out_date, 'YYYYMMDD')::INTEGER,

    -- dims
    h.hotel_key,
    dm_room.room_key,
    dp.pet_key,
    dc.customer_key,
    de.employee_key,

    r.total_cost,
    (rr.check_out_date - rr.check_in_date)   AS nights_stayed,

    -- num services
    COALESCE(svc.num_services, 0),

    -- dali ima dostava
    (del.reservation_id IS NOT NULL),

    -- status i plakjanje
    rs.name,
    pay.payment_method,
    pay.payment_status,
    pay.amount

FROM Reservation r

-- res status
         JOIN ReservationStatus rs
              ON rs.reservation_status_id = r.status_id

-- room i hotel
         JOIN RoomReservation rr
              ON rr.reservation_id = r.reservation_id
         JOIN DimRoom dm_room
              ON dm_room.room_id   = rr.room_id
                  AND dm_room.is_current = TRUE
         JOIN DimHotel h
              ON h.hotel_key = dm_room.hotel_key

-- pet
         JOIN DimPet dp
              ON dp.pet_id     = r.pet_id
                  AND dp.is_current = TRUE

-- klient
         JOIN Pet pet
              ON pet.pet_id = r.pet_id
         JOIN DimCustomer dc
              ON dc.customer_id = pet.customer_id
                  AND dc.is_current  = TRUE

-- emp
         JOIN DimEmployee de
              ON de.employee_id = r.employee_id
                  AND de.is_current  = TRUE

-- broj na services
         LEFT JOIN (
    SELECT reservation_id, COUNT(*) AS num_services
    FROM ServiceReservation
    GROUP BY reservation_id
) svc ON svc.reservation_id = r.reservation_id

-- dali ima dostava
         LEFT JOIN (
    SELECT DISTINCT reservation_id
    FROM PetDelivery
) del ON del.reservation_id = r.reservation_id

    -- plakjanje
         LEFT JOIN (
    SELECT DISTINCT ON (p.reservation_id)
        p.reservation_id,
        pm.name   AS payment_method,
        ps.name   AS payment_status,
        p.amount,
        p.payment_date
    FROM Payment p
             JOIN PaymentStatus ps ON ps.payment_status_id = p.status_id
             JOIN PaymentMethod pm ON pm.payment_method_id = p.payment_method_id
    WHERE ps.name = 'completed'
    ORDER BY p.reservation_id, p.payment_date DESC
) pay ON pay.reservation_id = r.reservation_id;