= Логички и физички дизајн
== Релациска шема (мапирачка трансформација)
* примарни клучеви: bold и underline
* Надворешни клучеви: bold и * до името на надворешниот клуч, во заграда табелата кон која покажува


== Шема
User('''__userId__''', firstName, lastName, email, password, role, phone, address, membershipLevel, registrationDate)

Restaurant('''__restaurantId__''', name, address, cuisineType, phone, operatingHours, website, socialMediaLinks, rating)

TableEntity('''__tableId__''', '''restaurantId'''*(Restaurant), capacity, location, isSmokingArea, description, reservationDurationHours)

Menu('''__menuId__''', '''restaurantId'''*(Restaurant), itemName, category, price, description)

MenuTag('''__menuTagId__''', '''menuId'''*(Menu), tagName, tagValue)

Reservation('''__reservationId__''', '''userId'''*(User), '''tableId'''*(TableEntity), '''restaurantId'''*(Restaurant), reservationDateTime, partySize, specialRequests, status, checkInTime, checkOutTime, paymentStatus)

PreorderedItem('''__preOrderedItemId__''', '''reservationId'''*(Reservation), name, quantity, price)

ReservationHistory('''__reservationHistoryId__''', '''userId'''*(User), '''tableId'''*(TableEntity), '''restaurantId'''*(Restaurant), reservationDateTime, partySize, specialRequests, status, cancellationReason, checkInDate)

PriceHistory('''__priceHistoryId__''', '''menuId'''*(Menu), oldPrice, changeDate)  


== DDL скрипта за бришење на табелите и креирање на табелите
[[Image(kreiranje.sql​)]]
== DML скрипта за полнење на табелите со податоци
[[Image(polnenje.sql)]]

== Тригери
{{{
CREATE OR REPLACE FUNCTION trg_validate_preorder_price()
RETURNS TRIGGER AS $$
DECLARE
    v_menu_price NUMERIC;
    v_restaurant_id INTEGER;
BEGIN
    SELECT restaurantid INTO v_restaurant_id
    FROM reservations
    WHERE reservationid = NEW.reservation_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Reservation ID % not found', NEW.reservation_id;
    END IF;

    SELECT price INTO v_menu_price
    FROM menus
    WHERE item_name = NEW.preordered_item_name AND restaurant_id = v_restaurant_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Menu item "%" not found for restaurant ID %', NEW.preordered_item_name, v_restaurant_id;
    END IF;

    IF NEW.quantity <= 0 THEN
        RAISE EXCEPTION 'Invalid quantity for item "%": must be greater than 0', NEW.preordered_item_name;
    END IF;

    IF NEW.price_at_order <> v_menu_price THEN
        RAISE EXCEPTION 'Price mismatch for item "%": expected %, got %',
            NEW.preordered_item_name, v_menu_price, NEW.price_at_order;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_preorder_price_trigger
BEFORE INSERT OR UPDATE ON reservation_preordered_items
FOR EACH ROW
EXECUTE FUNCTION trg_validate_preorder_price();

--Test cases

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (22, 'California Roll', 1, 5.99);

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (14, 'California Roll', 1, 5.99);

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (14, 'Roll', 1, 8.99);

INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
VALUES (14, 'California Roll', 0, 8.99);
}}}

== Погледи(Views)

{{{
create or replace view v_reservation_details as
select
    r.reservationid,
    u.first_name || ' ' || u.last_name as customer_name,
    res.name as restaurant_name,
    r.check_in_time,
    r.check_out_time,
    r.party_size,
    r.status,
    p.preordered_item_name,
    p.quantity,
    p.price_at_order
from reservations r
join app_user u on r.userid = u.user_id
join restaurants res on r.restaurantid = res.restaurantid
left join reservation_preordered_items p on r.reservationid = p.reservation_id;
}}}

{{{
create or replace view v_menu_with_tags as
select
    m.menu_id,
    m.item_name,
    m.description,
    m.menu_category,
    m.price,
    r.name as restaurant_name,
    t.tag_name,
    t.tag_value
from menus m
join restaurants r on m.restaurant_id = r.restaurantid
left join menu_tags t on m.menu_id = t.menu_id;
}}}

== Релациски дијаграм изведен од DBeaver
[[Image(finalDbeaverER.jpg​)]]