Погледи

Целосен преглед на нарачки

Опис

Погледот прикажува целосен преглед на сите нарачки со детали за масата, келнерот, плаќањето и вкупниот износ.

Имплементација

CREATE OR REPLACE VIEW v_orders_full AS
SELECT 
    o.order_id,
    o.created_at,
    o.status AS order_status,
    rt.table_number,
    rt.capacity,
    u.first_name || ' ' || u.last_name AS waiter_name,
    r.role_name AS waiter_role,
    p.amount AS total_paid,
    p.method AS payment_method,
    p.payment_date,
    i.invoice_number,
    i.issued_at,
    COUNT(oi.item_number) AS total_items,
    SUM(oi.quantity) AS total_quantity
FROM project.orders o
JOIN project.restaurant_table rt ON rt.table_id = o.table_id
JOIN project.app_user u ON u.user_id = o.user_id
JOIN project.role r ON r.role_id = u.role_id
LEFT JOIN project.payment p ON p.order_id = o.order_id
LEFT JOIN project.invoice i ON i.payment_id = p.payment_id
LEFT JOIN project.order_item oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.created_at, o.status, rt.table_number, rt.capacity,
         u.first_name, u.last_name, r.role_name,
         p.amount, p.method, p.payment_date,
         i.invoice_number, i.issued_at
ORDER BY o.created_at DESC;

Преглед на маси со нивниот статус

Опис

Погледот прикажува сите маси со нивниот статус и активна нарачка (ако има).

Имплементација

CREATE OR REPLACE VIEW v_tables_status AS
SELECT 
    rt.table_id,
    rt.table_number,
    rt.capacity,
    rt.status,
    o.order_id AS active_order_id,
    o.created_at AS order_time,
    u.first_name || ' ' || u.last_name AS waiter_name,
    COUNT(oi.item_number) AS items_in_order
FROM project.restaurant_table rt
LEFT JOIN project.orders o ON o.table_id = rt.table_id AND o.status != 'ПЛАТЕНА'
LEFT JOIN project.app_user u ON u.user_id = o.user_id
LEFT JOIN project.order_item oi ON oi.order_id = o.order_id
GROUP BY rt.table_id, rt.table_number, rt.capacity, rt.status,
         o.order_id, o.created_at, u.first_name, u.last_name
ORDER BY rt.table_number::int;

Месечен приход по категорија

Опис

Погледот го прикажува месечниот приход групиран по категорија.

Имплементација

CREATE OR REPLACE VIEW v_monthly_revenue_by_category AS
SELECT 
    TO_CHAR(o.created_at, 'YYYY-MM') AS month,
    c.category_id,
    c.name AS category_name,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(oi.quantity) AS items_sold,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    ROUND(AVG(oi.quantity * oi.unit_price), 2) AS avg_item_price
FROM project.orders o
JOIN project.order_item oi ON oi.order_id = o.order_id
JOIN project.product p ON p.product_id = oi.product_id
JOIN project.category c ON c.category_id = p.category_id
WHERE o.status = 'ПЛАТЕНА'
GROUP BY TO_CHAR(o.created_at, 'YYYY-MM'), c.category_id, c.name
ORDER BY month DESC, total_revenue DESC;

Извештај за најпродавани производи

Опис

Погледот ги прикажува најпродаваните производи со вкупна количина и приход.

Имплементација

CREATE OR REPLACE VIEW v_top_products AS
SELECT 
    p.product_id,
    p.name AS product_name,
    c.name AS category_name,
    p.price AS current_price,
    COUNT(DISTINCT oi.order_id) AS order_count,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    ROUND(AVG(oi.unit_price), 2) AS avg_selling_price
FROM project.product p
JOIN project.category c ON c.category_id = p.category_id
LEFT JOIN project.order_item oi ON oi.product_id = p.product_id
LEFT JOIN project.orders o ON o.order_id = oi.order_id AND o.status = 'ПЛАТЕНА'
WHERE p.active = TRUE
GROUP BY p.product_id, p.name, c.name, p.price
ORDER BY total_revenue DESC NULLS LAST;

Дневен извештај за продажба

Опис

Погледот прикажува дневен извештај за продажбата со вкупен приход, број на нарачки и просечна вредност.

Имплементација

CREATE OR REPLACE VIEW v_daily_sales AS
SELECT 
    DATE(o.created_at) AS sale_date,
    TO_CHAR(o.created_at, 'Day') AS day_of_week,
    COUNT(DISTINCT o.order_id) AS order_count,
    COUNT(DISTINCT o.table_id) AS tables_used,
    COUNT(DISTINCT o.user_id) AS waiters_active,
    SUM(p.amount) AS total_revenue,
    ROUND(AVG(p.amount), 2) AS avg_order_value,
    SUM(CASE WHEN p.method = 'КЕШ' THEN p.amount ELSE 0 END) AS cash_revenue,
    SUM(CASE WHEN p.method = 'КАРТИЧКА' THEN p.amount ELSE 0 END) AS card_revenue
FROM project.orders o
JOIN project.payment p ON p.order_id = o.order_id
WHERE o.status = 'ПЛАТЕНА'
GROUP BY DATE(o.created_at), TO_CHAR(o.created_at, 'Day')
ORDER BY sale_date DESC;

Преглед на залиха по производ

Опис

Погледот прикажува моментална залиха за секој производ со статус (ОК, НИСКО, КРИТИЧНО).

Имплементација

CREATE OR REPLACE VIEW v_product_stock AS
SELECT 
    p.product_id,
    p.name AS product_name,
    c.name AS category_name,
    p.min_stock,
    COALESCE(SUM(i.quantity_change), 0) AS current_stock,
    CASE 
        WHEN COALESCE(SUM(i.quantity_change), 0) <= 0 THEN 'КРИТИЧНО'
        WHEN COALESCE(SUM(i.quantity_change), 0) < p.min_stock THEN 'НИСКО'
        ELSE 'ОК'
    END AS stock_status,
    MAX(i.created_at) AS last_movement
FROM project.product p
JOIN project.category c ON c.category_id = p.category_id
LEFT JOIN project.inventory i ON i.product_id = p.product_id
WHERE p.active = TRUE
GROUP BY p.product_id, p.name, c.name, p.min_stock
ORDER BY stock_status, current_stock ASC;