= Логички и физички дизајн - Креирање база податоци (со SQL DDL)

== Фаза 2 - DDL, податоци, погледи


=== DDL скрипта за креирање на табелите

Финалната DDL скрипта за креирање на шемата:

* `schema_final.sql`
[attachment:schema8.0_dll.sql]
==== Број на податоци по табела

||= Табела ||= Број на записи ||
|| `API_USER` || 10,001,000 ||
|| `CUSTOMER` || 10,000,000 ||
|| `CUSTOMER_LOYALTY` || 10,000,000 ||
|| `CUSTOMER_ORDER` || 10,000,000 ||
|| `REVIEW` || 1,000,000 ||
|| `ORDER_MEAL` || 1,000,000 ||
|| `ORDER_DRINK` || 1,000,000 ||
|| `API_ADMIN` || 100 ||
|| `DRIVER` || 900 ||
|| `COMPANY` || 500 ||
|| `COMPANY_ORDER` || 1,000,000 ||
|| `RESTAURANT` || до 50 ||
|| `CATEGORY` || реални MealDB категории ||
|| `INGREDIENT` || реален MealDB/CocktailDB сет на состојки ||
|| `ALERGEN` || 14 ||
|| `MEAL` || 200 ||
|| `DRINK` || 100 ||
|| `MEAL_INGREDIENT` || зависно од увезените meal-ingredient врски ||
|| `ALERGEN_INGREDIENT` || зависно од совпаѓањето според имињата на состојките ||
|| `CONTRACT` || 200 ||
|| `DELIVERY` || 1,000,000 ||
|| `LUNCH_TIME` || 1,000 ||
|| `INVOICE` || 6,000 ||
|| `ORDER_REVIEW` || 700,000 ||
|| `DELIVERY_REVIEW` || 300,000 ||
|| `CONTRACT_STATUS` || 5 ||
|| `CUSTOMER_LOYALTY_STATUS` || 3 ||
|| `DELIVERY_STATUS` || 8 ||
|| `ORDER_STATUS` || 6 ||


==== Извор на податоците

* *RESTAURANT* – до 50 ресторани од реалниот dataset
* *CATEGORY* – реални категории од MealDB
* *INGREDIENT* – реален сет на состојки од MealDB/CocktailDB
* *MEAL_INGREDIENT* – врски добиени од увезените податоци за оброците и состојките
* *ALERGEN_INGREDIENT* – врски добиени преку совпаѓање на имињата на состојките
* Останатите податоци се генерирани генерички со реалистична распределба според очекуваното користење на апликацијата.

==== Скрипти за полнење на табелите

* `data_final.sql`

// Простор за поставување на data_final.sql

=== 2В: Погледи (Views)

Потребно е да се направат три погледи (views) по член, така што тие подоцна реално ќе бидат користени од апликацијата.

== Views

== View 1: Мени со пијалоци ==

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE VIEW kbnteam.v_menu_drink AS
SELECT
    d.drink_id,
    d.drink_name,
    d.drink_milliliters,
    d.drink_price,
    r.rest_id,
    r.rest_name
FROM kbnteam.drink d
JOIN kbnteam.restaurant r ON r.rest_id = d.rest_id;
}}}

== View 2: Мени со јадења ==

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE VIEW kbnteam.v_menu_meal AS
WITH meal_ingredients AS (
    SELECT
        mi.meal_id,
        string_agg(DISTINCT i.ingr_name, ', ' ORDER BY i.ingr_name) AS ingredients
    FROM kbnteam.meal_ingredient mi
    JOIN kbnteam.ingredient i ON i.ingr_id = mi.ingr_id
    GROUP BY mi.meal_id
),
meal_allergens AS (
    SELECT
        mi.meal_id,
        string_agg(DISTINCT a.alergen_name, ', ' ORDER BY a.alergen_name) AS allergens
    FROM kbnteam.meal_ingredient mi
    JOIN kbnteam.alergen_ingredient ai ON ai.ingr_id = mi.ingr_id
    JOIN kbnteam.alergen a ON a.alergen_id = ai.alergen_id
    GROUP BY mi.meal_id
)
SELECT
    m.meal_id,
    m.meal_name,
    m.meal_description,
    m.meal_price,
    m.meal_weight,
    c.cat_id,
    c.cat_name,
    r.rest_id,
    r.rest_name,
    COALESCE(mi.ingredients, '') AS ingredients,
    COALESCE(ma.allergens, '') AS allergens
FROM kbnteam.meal m
JOIN kbnteam.category c ON c.cat_id = m.cat_id
JOIN kbnteam.restaurant r ON r.rest_id = m.rest_id
LEFT JOIN meal_ingredients mi ON mi.meal_id = m.meal_id
LEFT JOIN meal_allergens ma ON ma.meal_id = m.meal_id;
}}}

== View 3: Целосен преглед на нарачките ==

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE VIEW kbnteam.v_orders_full AS
WITH order_meals AS (
    SELECT
        om.order_id,
        string_agg(DISTINCT m.meal_name, ', ' ORDER BY m.meal_name) AS meals
    FROM kbnteam.order_meal om
    JOIN kbnteam.meal m ON m.meal_id = om.meal_id
    GROUP BY om.order_id
),
order_drinks AS (
    SELECT
        od.order_id,
        string_agg(DISTINCT d.drink_name, ', ' ORDER BY d.drink_name) AS drinks
    FROM kbnteam.order_drink od
    JOIN kbnteam.drink d ON d.drink_id = od.drink_id
    GROUP BY od.order_id
)
SELECT
    o.order_id,
    o.order_datetime,
    o.order_total,
    os.o_status_name,
    cu.user_id AS customer_user_id,
    cu.company_id AS customer_company_id,
    au.user_first_name AS customer_first_name,
    au.user_last_name AS customer_last_name,
    au.user_email AS customer_email,
    co.comp_order_id,
    co.company_id AS order_company_id,
    cmp.company_name,
    d.delivery_id,
    d.delivery_date,
    ds.d_status_name,
    d.driver_user_id,
    du.user_first_name AS driver_first_name,
    du.user_last_name AS driver_last_name,
    du.user_phone_no AS driver_phone,
    COALESCE(om.meals, '') AS meals,
    COALESCE(od.drinks, '') AS drinks
FROM kbnteam.customer_order o
JOIN kbnteam.order_status os ON os.o_status_id = o.o_status_id
JOIN kbnteam.customer cu ON cu.user_id = o.customer_user_id
JOIN kbnteam.api_user au ON au.user_id = cu.user_id
JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id
JOIN kbnteam.company cmp ON cmp.company_id = co.company_id
LEFT JOIN kbnteam.delivery d ON d.delivery_id = co.delivery_id
LEFT JOIN kbnteam.delivery_status ds ON ds.d_status_id = d.d_status_id
LEFT JOIN kbnteam.api_user du ON du.user_id = d.driver_user_id
LEFT JOIN order_meals om ON om.order_id = o.order_id
LEFT JOIN order_drinks od ON od.order_id = o.order_id;
}}}

== View 4: Преглед на рецензиите ==

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE VIEW kbnteam.v_reviews_full AS
SELECT
    r.review_id,
    'order'::text AS review_type,
    r.review_created_at,
    r.review_rating,
    r.review_comment,
    orv.order_id,
    NULL::integer AS delivery_id,
    co.company_id,
    cmp.company_name,
    au.user_id AS customer_user_id,
    au.user_first_name AS customer_first_name,
    au.user_last_name AS customer_last_name,
    NULL::integer AS driver_user_id,
    NULL::varchar(255) AS driver_first_name,
    NULL::varchar(255) AS driver_last_name,
    orv.order_review_food_rating,
    orv.order_review_res_rating,
    NULL::integer AS del_review_courier_rating,
    NULL::integer AS del_review_speed_rating
FROM kbnteam.review r
JOIN kbnteam.order_review orv ON orv.review_id = r.review_id
JOIN kbnteam.customer_order o ON o.order_id = orv.order_id
JOIN kbnteam.api_user au ON au.user_id = o.customer_user_id
JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id
JOIN kbnteam.company cmp ON cmp.company_id = co.company_id

UNION ALL

SELECT
    r.review_id,
    'delivery'::text AS review_type,
    r.review_created_at,
    r.review_rating,
    r.review_comment,
    NULL::integer AS order_id,
    drvrev.delivery_id,
    co.company_id,
    cmp.company_name,
    NULL::integer AS customer_user_id,
    NULL::varchar(255) AS customer_first_name,
    NULL::varchar(255) AS customer_last_name,
    du.user_id AS driver_user_id,
    du.user_first_name AS driver_first_name,
    du.user_last_name AS driver_last_name,
    NULL::integer AS order_review_food_rating,
    NULL::integer AS order_review_res_rating,
    drvrev.del_review_courier_rating,
    drvrev.del_review_speed_rating
FROM kbnteam.review r
JOIN kbnteam.delivery_review drvrev ON drvrev.review_id = r.review_id
JOIN kbnteam.delivery d ON d.delivery_id = drvrev.delivery_id
LEFT JOIN kbnteam.api_user du ON du.user_id = d.driver_user_id
LEFT JOIN kbnteam.company_order co ON co.delivery_id = d.delivery_id
LEFT JOIN kbnteam.company cmp ON cmp.company_id = co.company_id;
}}}

== View 5: Преглед на доставите ==

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE VIEW kbnteam.v_driver_deliveries AS
SELECT
    d.delivery_id,
    d.delivery_date,
    d.delivery_notes,
    ds.d_status_name,
    drv.user_id AS driver_user_id,
    du.user_first_name AS driver_first_name,
    du.user_last_name AS driver_last_name,
    du.user_phone_no AS driver_phone,
    r.rest_id,
    r.rest_name,
    co.comp_order_id,
    cmp.company_id,
    cmp.company_name,
    o.order_id,
    o.order_datetime,
    o.order_total,
    o.customer_user_id,
    o.customer_first_name,
    o.customer_last_name,
    o.customer_email,
    COALESCE(o.meals, '') AS meals,
    COALESCE(o.drinks, '') AS drinks
FROM kbnteam.delivery d
JOIN kbnteam.delivery_status ds ON ds.d_status_id = d.d_status_id
LEFT JOIN kbnteam.driver drv ON drv.user_id = d.driver_user_id
LEFT JOIN kbnteam.api_user du ON du.user_id = drv.user_id
LEFT JOIN kbnteam.restaurant r ON r.rest_id = drv.rest_id
LEFT JOIN kbnteam.company_order co ON co.delivery_id = d.delivery_id
LEFT JOIN kbnteam.company cmp ON cmp.company_id = co.company_id
LEFT JOIN kbnteam.v_orders_full o ON o.comp_order_id = co.comp_order_id;
}}}

== View 6: Приходи според договорите ==

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE VIEW kbnteam.v_contracts_revenue AS
WITH order_restaurants AS (
    SELECT
        om.order_id,
        m.rest_id
    FROM kbnteam.order_meal om
    JOIN kbnteam.meal m ON m.meal_id = om.meal_id

    UNION

    SELECT
        od.order_id,
        d.rest_id
    FROM kbnteam.order_drink od
    JOIN kbnteam.drink d ON d.drink_id = od.drink_id
),
contract_metrics AS (
    SELECT
        co.company_id,
        orr.rest_id,
        COUNT(DISTINCT co.comp_order_id) AS company_order_count,
        COUNT(*) AS customer_order_count,
        SUM(o.order_total)::numeric(14,2) AS total_revenue
    FROM kbnteam.customer_order o
    JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id
    JOIN order_restaurants orr ON orr.order_id = o.order_id
    GROUP BY co.company_id, orr.rest_id
)
SELECT
    ct.contract_id,
    cmp.company_id,
    cmp.company_name,
    r.rest_id,
    r.rest_name,
    cs.contract_status_name,
    ct.contract_start_date,
    ct.contract_end_date,
    COALESCE(cm.company_order_count, 0) AS company_order_count,
    COALESCE(cm.customer_order_count, 0) AS customer_order_count,
    COALESCE(cm.total_revenue, 0)::numeric(14,2) AS total_revenue
FROM kbnteam.contract ct
JOIN kbnteam.company cmp ON cmp.company_id = ct.company_id
JOIN kbnteam.restaurant r ON r.rest_id = ct.rest_id
JOIN kbnteam.contract_status cs ON cs.contract_status_id = ct.contract_status_id
LEFT JOIN contract_metrics cm
    ON cm.company_id = ct.company_id
   AND cm.rest_id = ct.rest_id;
}}}

== View 7: Лојалност на клиентите ==

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE VIEW kbnteam.v_customer_loyalty_full_v2 AS
WITH order_stats AS (
    SELECT
        o.customer_user_id,
        COUNT(*) AS order_count,
        SUM(o.order_total)::numeric(14,2) AS total_spent,
        MAX(o.order_datetime) AS last_order_at
    FROM kbnteam.customer_order o
    GROUP BY o.customer_user_id
)
SELECT
    cl.cus_loyalty_id,
    cl.user_id AS customer_user_id,
    cu.company_id,
    cmp.company_name,
    au.user_first_name,
    au.user_last_name,
    au.user_email,
    au.user_phone_no,
    cl.cus_loyalty_curr_points,
    cl.cus_loyalty_joined_at,
    cls.cus_loyalty_status_id,
    cls.cus_loyalty_status_name,
    lt.tier_id,
    lt.tier_name,
    lt.tier_discount_percentage,
    lt.tier_free_delivery_eligibility,
    lt.tier_priority_support,
    COALESCE(os.order_count, 0) AS order_count,
    COALESCE(os.total_spent, 0)::numeric(14,2) AS total_spent,
    os.last_order_at
FROM kbnteam.customer_loyalty cl
JOIN kbnteam.customer cu ON cu.user_id = cl.user_id
JOIN kbnteam.company cmp ON cmp.company_id = cu.company_id
JOIN kbnteam.api_user au ON au.user_id = cl.user_id
JOIN kbnteam.customer_loyalty_status cls ON cls.cus_loyalty_status_id = cl.cus_loyalty_status_id
JOIN kbnteam.loyalty_tier lt ON lt.tier_id = cl.tier_id
LEFT JOIN order_stats os ON os.customer_user_id = cl.user_id;
}}}

== View 8: Термини за ручек на доставувачите ==

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE VIEW kbnteam.v_driver_lunch_timers AS
SELECT
    lt.lunch_time_id,
    lt.lunch_weekday,
    lt.lunch_start,
    lt.lunch_end,
    lt.lunch_preorder_offset,
    lt.comp_order_id,
    ct.contract_id,
    cmp.company_id,
    cmp.company_name,
    r.rest_id,
    r.rest_name,
    drv.user_id AS driver_user_id,
    au.user_first_name AS driver_first_name,
    au.user_last_name AS driver_last_name,
    au.user_phone_no AS driver_phone
FROM kbnteam.lunch_time lt
JOIN kbnteam.contract ct ON ct.contract_id = lt.contract_id
JOIN kbnteam.company cmp ON cmp.company_id = ct.company_id
JOIN kbnteam.restaurant r ON r.rest_id = ct.rest_id
JOIN kbnteam.driver drv ON drv.rest_id = r.rest_id
JOIN kbnteam.api_user au ON au.user_id = drv.user_id;
}}}

== View 9: Фактурирање на компаниите ==

=== SQL Дефиниција ===

{{{
#!sql
CREATE OR REPLACE VIEW kbnteam.v_company_billing_overview AS
WITH company_contracts AS (
    SELECT company_id, COUNT(*) AS contract_count
    FROM kbnteam.contract
    GROUP BY company_id
)
SELECT
    i.invoice_id,
    i.comp_order_id,
    cmp.company_id,
    cmp.company_name,
    cmp.company_address,
    co.delivery_id,
    COUNT(o.order_id) AS customer_order_count,
    COALESCE(SUM(o.order_total), 0)::numeric(14,2) AS invoice_amount,
    COALESCE(cc.contract_count, 0) AS contract_count
FROM kbnteam.invoice i
JOIN kbnteam.company_order co ON co.comp_order_id = i.comp_order_id
JOIN kbnteam.company cmp ON cmp.company_id = co.company_id
LEFT JOIN kbnteam.customer_order o ON o.comp_order_id = co.comp_order_id
LEFT JOIN company_contracts cc ON cc.company_id = cmp.company_id
GROUP BY i.invoice_id, i.comp_order_id, cmp.company_id,
         cmp.company_name, cmp.company_address, co.delivery_id, cc.contract_count;
}}}