practice/proiz_pract_script.sql

CREATE TABLE TourFirm (
    firm_id      SERIAL PRIMARY KEY,
    firm_name    VARCHAR(100) NOT NULL,
    people_count INT          NOT NULL CHECK (people_count > 0)
); 
CREATE TABLE RoomType (
    room_type_id SERIAL PRIMARY KEY,
    type_name    VARCHAR(50) NOT NULL UNIQUE
); 
CREATE TABLE Room (
    room_id      SERIAL PRIMARY KEY,
    room_type_id INT          NOT NULL REFERENCES RoomType(room_type_id)
                                ON DELETE RESTRICT ON UPDATE CASCADE,
    room_number  INT          NOT NULL UNIQUE CHECK (room_number > 0),
    occupancy    INT          NOT NULL CHECK (occupancy > 0),
    rating       DECIMAL(3,1) NOT NULL DEFAULT 0.0
                               CHECK (rating >= 0.0 AND rating <= 5.0)
); 
CREATE TABLE Client (
    client_id    SERIAL PRIMARY KEY,
    firm_id      INT         NOT NULL REFERENCES TourFirm(firm_id)
                               ON DELETE RESTRICT ON UPDATE CASCADE,
    last_name    VARCHAR(60) NOT NULL,
    first_name   VARCHAR(60) NOT NULL,
    patronymic   VARCHAR(60),
    phone_number VARCHAR(20) NOT NULL
);
CREATE TABLE Booking (
    booking_id     SERIAL PRIMARY KEY,
    client_id      INT          NOT NULL REFERENCES Client(client_id)
                                  ON DELETE RESTRICT ON UPDATE CASCADE,
    room_id        INT          NOT NULL REFERENCES Room(room_id)
                                  ON DELETE RESTRICT ON UPDATE CASCADE,
    check_in_date  DATE         NOT NULL,
    check_out_date DATE         NOT NULL,
    price_per_night DECIMAL(10,2) NOT NULL CHECK (price_per_night > 0),s
    CHECK (check_out_date > check_in_date)
); 
CREATE TABLE Service (
    service_id   SERIAL PRIMARY KEY,
    service_name VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE RoomTypeService (
    room_type_id INT NOT NULL REFERENCES RoomType(room_type_id)
                       ON DELETE RESTRICT ON UPDATE CASCADE,
    service_id   INT NOT NULL REFERENCES Service(service_id)
                       ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (room_type_id, service_id)
);

SELECT * FROM booking

--Расчетный атрибут — итоговая стоимость бронирования
--total_cost = price_per_night * (check_out_date - check_in_date)

 
CREATE OR REPLACE VIEW BookingCostView AS
SELECT
    b.booking_id,
    c.last_name || ' ' || c.first_name || ' ' || COALESCE(c.patronymic, '') AS client_full_name,
    r.room_number,
    rt.type_name                                               AS room_type,
    b.check_in_date,
    b.check_out_date,
    (b.check_out_date - b.check_in_date)                      AS nights,
    b.price_per_night,
    b.price_per_night * (b.check_out_date - b.check_in_date)  AS total_cost
FROM Booking b
JOIN Client   c  ON c.client_id     = b.client_id
JOIN Room     r  ON r.room_id       = b.room_id
JOIN RoomType rt ON rt.room_type_id = r.room_type_id;


--Проверочный запрос

SELECT * FROM BookingCostView ORDER BY total_cost DESC LIMIT 10;


--Возвращает итоговую стоимость конкретного бронирования
--Использование в приложении: при отображении карточки брони


CREATE OR REPLACE FUNCTION get_booking_cost(p_booking_id INT)
RETURNS DECIMAL(10,2)
LANGUAGE plpgsql
AS $$
DECLARE
    v_cost DECIMAL(10,2);
BEGIN
    SELECT price_per_night * (check_out_date - check_in_date)
    INTO v_cost
    FROM Booking
    WHERE booking_id = p_booking_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Бронирование с ID % не найдено', p_booking_id;
    END IF;

    RETURN v_cost;
END;
$$;

SELECT get_booking_cost(2);



-- Создаёт новое бронирование

CREATE OR REPLACE PROCEDURE create_booking(
    p_client_id      INT,
    p_room_id        INT,
    p_check_in_date  DATE,
    p_check_out_date DATE,
    p_price_per_night DECIMAL(10,2)
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_check_out_date <= p_check_in_date THEN
        RAISE EXCEPTION 'Дата выезда должна быть позже даты заезда';
    END IF;
	
    IF NOT EXISTS (SELECT 1 FROM Client WHERE client_id = p_client_id) THEN
        RAISE EXCEPTION 'Клиент с ID % не найден', p_client_id;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM Room WHERE room_id = p_room_id) THEN
        RAISE EXCEPTION 'Комната с ID % не найдена', p_room_id;
    END IF;
	
    IF EXISTS (
        SELECT 1 FROM Booking
        WHERE room_id = p_room_id
          AND check_in_date  < p_check_out_date
          AND check_out_date > p_check_in_date
    ) THEN
        RAISE EXCEPTION 'Комната % уже забронирована на указанные даты', p_room_id;
    END IF;
	
    INSERT INTO Booking (client_id, room_id, check_in_date, check_out_date, price_per_night)
    VALUES (p_client_id, p_room_id, p_check_in_date, p_check_out_date, p_price_per_night);
	
    RAISE NOTICE 'Бронирование успешно создано для клиента ID %', p_client_id;
END;
$$;


CALL create_booking(3, 5, '2026-03-01', '2026-03-014', 2500.00);

SELECT setval('tourfirm_firm_id_seq',   (SELECT MAX(firm_id)      FROM TourFirm));
SELECT setval('client_client_id_seq',   (SELECT MAX(client_id)    FROM Client));
SELECT setval('room_room_id_seq',        (SELECT MAX(room_id)      FROM Room));
SELECT setval('roomtype_room_type_id_seq',(SELECT MAX(room_type_id) FROM RoomType));
SELECT setval('service_service_id_seq', (SELECT MAX(service_id)   FROM Service));
SELECT setval('booking_booking_id_seq', (SELECT MAX(booking_id)   FROM Booking));

--Логирование бронирований

CREATE TABLE AuditLog (
    log_id    SERIAL PRIMARY KEY,
    tbl_name  VARCHAR(50),
    action    VARCHAR(10),
    changed_at TIMESTAMP DEFAULT NOW(),
    old_data  JSONB,
    new_data  JSONB
);

CREATE OR REPLACE FUNCTION audit_booking()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO AuditLog (tbl_name, action, old_data, new_data)
    VALUES (
        TG_TABLE_NAME, TG_OP,
        CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD)::jsonb ELSE NULL END,
        CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW)::jsonb ELSE NULL END
    );
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_audit_booking
AFTER INSERT OR UPDATE OR DELETE ON Booking
FOR EACH ROW EXECUTE FUNCTION audit_booking();