04_views.sql

-- =====================================================================
--  04_views.sql — представления (готовые запросы)
--
--  Представления решают две задачи одновременно:
--   1) закрывают типовые запросы групп пользователей;
--   2) служат единицей разграничения доступа — например, администратор
--      получает право на UPDATE не всей таблицы Bookings, а только
--      представления curr_bookings, то есть активных броней.
-- =====================================================================

-- 1. Текущие бронирования: активные на сегодня брони со стоимостью
--    проживания. Используют управляющий и администратор.
CREATE OR REPLACE VIEW curr_bookings AS
    SELECT b.b_id,
           r.r_num    AS room_num,
           rc.rc_name AS category,
           rc.rc_price AS price_per_night,
           b.b_begin,
           b.b_end,
           b.b_status,
           (b.b_end - b.b_begin)              AS nights,
           (b.b_end - b.b_begin) * rc.rc_price AS total_cost
    FROM Bookings b
    JOIN Rooms r           ON r.r_num   = b.b_room
    JOIN RoomCategories rc ON rc.rc_name = r.r_cat
    WHERE b.b_status IN ('забронировано', 'заселён');

-- Представление с соединениями не является автообновляемым, поэтому
-- права на UPDATE, выданные администратору (см. 06_roles_grants.sql),
-- обслуживает INSTEAD OF триггер. Через него доступен ровно один вид
-- изменения — смена статуса активной брони при заезде и выезде;
-- завершённых и отменённых броней в представлении нет, а значит,
-- администратор физически не может их тронуть.
CREATE OR REPLACE FUNCTION curr_bookings_update()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE Bookings
       SET b_status = NEW.b_status,
           b_finish = CASE WHEN NEW.b_status = 'выехал'
                           THEN COALESCE(b_finish, CURRENT_DATE)
                           ELSE b_finish
                      END
     WHERE b_id = OLD.b_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_curr_bookings_update
    INSTEAD OF UPDATE ON curr_bookings
    FOR EACH ROW
    EXECUTE PROCEDURE curr_bookings_update();

-- 2. Гости текущих бронирований с контактами и отметкой основного
--    гостя. Используется администратором при заселении.
CREATE OR REPLACE VIEW curr_guests AS
    SELECT cb.b_id,
           cb.room_num,
           cb.b_begin,
           cb.b_end,
           g.g_fname || ' ' || g.g_name ||
               COALESCE(' ' || g.g_patr, '') AS full_name,
           g.g_nation,
           bg.bg_main,
           gc.gc_phone,
           gc.gc_email
    FROM curr_bookings cb
    JOIN BookingGuests bg   ON bg.bg_book  = cb.b_id
    JOIN Guests g           ON g.g_id      = bg.bg_guest
    LEFT JOIN GuestContacts gc ON gc.gc_guest = g.g_id
    ORDER BY cb.b_begin, cb.room_num, bg.bg_main DESC;

-- 3. Свободные номера на текущую дату. Используется менеджером при
--    оформлении новой брони.
CREATE OR REPLACE VIEW free_rooms AS
    SELECT r.r_num,
           r.r_floor,
           r.r_cap,
           r.r_area,
           rc.rc_name  AS category,
           rc.rc_price AS price_per_night
    FROM Rooms r
    JOIN RoomCategories rc ON rc.rc_name = r.r_cat
    WHERE r.r_status = 'свободен'
      AND r.r_num NOT IN (
              SELECT b_room FROM Bookings
              WHERE b_status IN ('забронировано', 'заселён')
                AND CURRENT_DATE BETWEEN b_begin AND b_end
          )
    ORDER BY rc.rc_price, r.r_num;

-- 4. Выручка по бронированиям: ожидаемая сумма (проживание + услуги)
--    против фактически поступившей. Используется управляющим и
--    бухгалтером; разница между колонками — задолженность.
CREATE OR REPLACE VIEW revenue_summary AS
    SELECT b.b_id,
           b.b_begin,
           b.b_end,
           b.b_status,
           rc.rc_price * (b.b_end - b.b_begin)        AS room_cost,
           COALESCE(svc.svc_total, 0)                 AS services_cost,
           rc.rc_price * (b.b_end - b.b_begin)
               + COALESCE(svc.svc_total, 0)           AS expected_sum,
           COALESCE(pay.pay_total, 0)                 AS received_sum
    FROM Bookings b
    JOIN Rooms r           ON r.r_num   = b.b_room
    JOIN RoomCategories rc ON rc.rc_name = r.r_cat
    LEFT JOIN (
        SELECT bs.bs_book, SUM(s.s_price * bs.bs_qty) AS svc_total
        FROM BookingServices bs
        JOIN Services s ON s.s_name = bs.bs_svc
        GROUP BY bs.bs_book
    ) svc ON svc.bs_book = b.b_id
    LEFT JOIN (
        SELECT pay_book, SUM(pay_sum) AS pay_total
        FROM Payments
        GROUP BY pay_book
    ) pay ON pay.pay_book = b.b_id
    ORDER BY b.b_begin DESC;

-- 5. Оказанные услуги по бронированиям — основа для счёта при выезде.
CREATE OR REPLACE VIEW booking_services_view AS
    SELECT bs.bs_book AS booking_id,
           bs.bs_date AS service_date,
           s.s_name   AS service_name,
           s.s_price  AS base_price,
           bs.bs_qty  AS quantity,
           s.s_price * bs.bs_qty AS total_price
    FROM BookingServices bs
    JOIN Services s ON s.s_name = bs.bs_svc
    ORDER BY bs.bs_book, bs.bs_date;

-- 6. Прайс-лист услуг. Доступен всем ролям только на чтение.
CREATE OR REPLACE VIEW price_list AS
    SELECT s_name  AS service_name,
           s_price AS price,
           s_desc  AS description
    FROM Services
    ORDER BY s_name;

-- 7. История посещений: число завершённых заездов и суммарные траты
--    гостя. Используется менеджером для оценки лояльности.
CREATE OR REPLACE VIEW guest_history AS
    SELECT g.g_id,
           g.g_fname || ' ' || g.g_name ||
               COALESCE(' ' || g.g_patr, '') AS guest_name,
           COUNT(DISTINCT b.b_id) AS total_bookings,
           SUM(p.pay_sum)         AS total_paid
    FROM Guests g
    JOIN BookingGuests bg ON bg.bg_guest = g.g_id
    JOIN Bookings b       ON b.b_id      = bg.bg_book
    LEFT JOIN Payments p  ON p.pay_book  = b.b_id
    WHERE b.b_status = 'выехал'
    GROUP BY g.g_id, g.g_fname, g.g_name, g.g_patr
    ORDER BY total_paid DESC NULLS LAST;

-- 8. Свободные номера на произвольный диапазон дат — основная задача
--    менеджера при оформлении брони. Представление параметры принимать
--    не может, поэтому запрос оформлен функцией.
--
--    Условие занятости то же, что в ограничении no_booking_overlap:
--    номер занят, если есть активная бронь, диапазон которой
--    пересекается с запрошенным. Оператор && работает с полуоткрытыми
--    интервалами, поэтому выезд 10-го и заезд 10-го конфликтом не
--    считаются. Проверка идёт через NOT EXISTS, а не NOT IN: NOT IN
--    возвращает пустой результат, если в подзапросе окажется NULL.
CREATE OR REPLACE FUNCTION available_rooms(p_from DATE, p_to DATE)
RETURNS TABLE (
    room_num        NUMERIC,
    category        VARCHAR,
    capacity        NUMERIC,
    price_per_night NUMERIC,
    total_cost      NUMERIC
) AS $$
    SELECT r.r_num,
           rc.rc_name,
           r.r_cap,
           rc.rc_price,
           rc.rc_price * (p_to - p_from)
    FROM Rooms r
    JOIN RoomCategories rc ON rc.rc_name = r.r_cat
    WHERE r.r_status <> 'на обслуживании'
      AND NOT EXISTS (
              SELECT 1
              FROM Bookings b
              WHERE b.b_room = r.r_num
                AND b.b_status IN ('забронировано', 'заселён')
                AND daterange(b.b_begin, b.b_end)
                    && daterange(p_from, p_to)
          )
    ORDER BY rc.rc_price, r.r_num;
$$ LANGUAGE sql STABLE;

-- Пример: SELECT * FROM available_rooms('2026-09-10', '2026-09-15');