== ЕР Дијаграм
[[Image(kbnteam_ERDiagram_ver2.jpg)]]
== Податочни побарувања
=== Ентитети



**ADMIN**
* user_id int, примарен клуч DEFAULT 1
* CONSTRAINT fk_admin_user
        FOREIGN KEY (user_id)
        REFERENCES api_user(user_id)
        ON UPDATE CASCADE
        ON DELETE SET DEFAULT

**ALLERGEN**
* allergen_id serial, примарен клуч
* allergen_name string, задолжителен и уникатен
* allergen_description string

**API_USER**
* user_id serial, примарен клуч
* user_first_name string, задолжителен
* user_last_name string, задолжителен
* user_email string, задолжителен и уникатен
* user_phone_no string, уникатен
* user_password_hash TEXT, задолжителен

**CATEGORY**
* cat_id serial, примарен клуч
* cat_name string, задолжителен и уникатен
* cat_description string

**COMPANY**
* company_id serial, примарен клуч
* company_name string, задолжителен и уникатен
* company_address string, задолжителен

**COMPANY_ORDER**
* comp_order_id serial, примарен клуч
* company_id int, задолжителен
* delivery_id int, уникатен
* CONSTRAINT fk_company_order_company
    FOREIGN KEY (company_id)
    REFERENCES company(company_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
* CONSTRAINT fk_company_order_delivery
    FOREIGN KEY (delivery_id)
    REFERENCES delivery(delivery_id)
    ON UPDATE CASCADE
    ON DELETE SET NULL

**CONTRACT**
* contract_id serial, примарен клуч
* company_id int, задолжителен
* rest_id int, задолжителен
* contract_status_id int, задолжителен
* contract_start_date date, задолжителен
* contract_end_date date, задолжителен
* CONSTRAINT fk_contract_company
    FOREIGN KEY (company_id)
    REFERENCES company(company_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
* CONSTRAINT fk_contract_restaurant
    FOREIGN KEY (rest_id)
    REFERENCES restaurant(rest_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
* CONSTRAINT fk_contract_status
    FOREIGN KEY (contract_status_id)
    REFERENCES contract_status(contract_status_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
* CONSTRAINT chk_contract_dates CHECK (contract_end_date >= contract_start_date)

**CONTRACT_STATUS**
* contract_status_id serial, примарен клуч
* contract_status_name string, задолжителен и уникатен

**CUSTOMER**
*  user_id int PRIMARY KEY DEFAULT 1
* CONSTRAINT fk_customer_user
    FOREIGN KEY (user_id)
    REFERENCES api_user(user_id)
    ON UPDATE CASCADE
    ON DELETE SET DEFAULT

**CUSTOMER_ORDER**
* order_id serial, примарен клуч
* comp_order_id int, задолжителен
* company_employee_user_id int, задолжителен DEFAULT 1
* order_datetime timestamp, задолжителен DEFAULT CURRENT_TIMESTAMP
* o_status_id int, задолжителен
* order_total NUMERIC(10,2), задолжителен
* CONSTRAINT fk_customer_order_company_order
    FOREIGN KEY (comp_order_id)
    REFERENCES company_order(comp_order_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
* CONSTRAINT fk_customer_order_employee
    FOREIGN KEY (company_employee_user_id)
    REFERENCES company_employee(user_id)
    ON UPDATE CASCADE
    ON DELETE SET DEFAULT
* CONSTRAINT fk_customer_order_status
    FOREIGN KEY (o_status_id)
    REFERENCES order_status(o_status_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
* CONSTRAINT chk_order_total CHECK (order_total >= 0)
    
**DELIVERY**
* delivery_id serial, примарен клуч
* delivery_date DATE, задолжителен DEFAULT CURRENT_DATE
* delivery_notes string
* d_status_id int, задолжителен
* driver_user_id int, задолжителен DEFAULT 1
* CONSTRAINT fk_delivery_status
    FOREIGN KEY (d_status_id)
    REFERENCES delivery_status(d_status_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
* CONSTRAINT fk_delivery_driver
    FOREIGN KEY (driver_user_id)
    REFERENCES driver(user_id)
    ON UPDATE CASCADE
    ON DELETE SET DEFAULT

**DELIVERY_REVIEW**
* delivery_review_id serial, примарен клуч
* delivery_id int, задолжителен и уникатен
* del_review_courier_rating int, задолжителен
* del_review_speed_rating int, задолжителен
* CONSTRAINT fk_delivery_review_delivery
        FOREIGN KEY (delivery_id)
        REFERENCES delivery(delivery_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
* CONSTRAINT chk_delivery_review_courier_rating CHECK (del_review_courier_rating BETWEEN 1 AND 5)
* CONSTRAINT chk_delivery_review_speed_rating CHECK (del_review_speed_rating BETWEEN 1 AND 5)

**DELIVERY_STATUS**
* d_status_id serial, примарен клуч
* d_status_name string, задолжителен и уникатен

**DRINK**
* drink_id serial, примарен клуч
* rest_id int, задолжителен
* drink_name string, задолжителен
* drink_milliliters int, задолжителен
* drink_price NUMERIC(10,2), задолжителен
* CONSTRAINT fk_drink_restaurant
    FOREIGN KEY (rest_id)
    REFERENCES restaurant(rest_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
* CONSTRAINT chk_drink_milliliters CHECK (drink_milliliters > 0)
* CONSTRAINT chk_drink_price CHECK (drink_price >= 0)
* CONSTRAINT uq_drink_restaurant_name UNIQUE (rest_id, drink_name)

**DRIVER**
* user_id int PRIMARY KEY DEFAULT 1
* CONSTRAINT fk_driver_user
    FOREIGN KEY (user_id)
    REFERENCES api_user(user_id)
    ON UPDATE CASCADE
    ON DELETE SET DEFAULT

**EMPLOYEE_LOYALTY**
* emp_loyalty_id serial, примарен клуч
* user_id int, задолжителен и уникатен
* emp_loyalty_curr_points int, задолжителен DEFAULT 0
* emp_loyalty_joined_at timestamp, задолжителен DEFAULT CURRENT_TIMESTAMP
* emp_loyalty_status_id int, задолжителен
* tier_id int, задолжителен
* CONSTRAINT fk_employee_loyalty_user
    FOREIGN KEY (user_id)
    REFERENCES company_employee(user_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
* CONSTRAINT fk_employee_loyalty_status
    FOREIGN KEY (emp_loyalty_status_id)
    REFERENCES employee_loyalty_status(emp_loyalty_status_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
* CONSTRAINT fk_employee_loyalty_tier
    FOREIGN KEY (tier_id)
    REFERENCES loyalty_tier(tier_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
* CONSTRAINT chk_employee_loyalty_points CHECK (emp_loyalty_curr_points >= 0)

**EMPLOYEE_LOYALTY_STATUS**
* emp_loyalty_status_id serial, примарен клуч
* emp_loyalty_status_name string, задолжителен и уникатен

**INGREDIENT**
* ingr_id serial, примарен клуч
* ingr_name string, задолжителен и уникатен

**LOYALTY_TIER**
* tier_id serial, примарен клуч
* tier_name string, задолжителен и уникатен
* tier_minimum_points int, задолжителен
* tier_maximum_points int, задолжителен
* tier_discount_percentage NUMERIC(5,2), задолжителен
* tier_free_delivery_eligibility bool, задолжителен DEFAULT FALSE
* tier_priority_support bool, задолжителен DEFAULT FALSE
* tier_created_at DATE, задолжителен DEFAULT CURRENT_DATE
* tier_update_at timestamp, задолжителен DEFAULT CURRENT_TIMESTAMP
* CONSTRAINT chk_tier_points_nonnegative CHECK (tier_minimum_points >= 0 AND tier_maximum_points >= 0)
* CONSTRAINT chk_tier_points_range CHECK (tier_maximum_points >= tier_minimum_points)
* CONSTRAINT chk_tier_discount CHECK (tier_discount_percentage >= 0 AND tier_discount_percentage <= 100)

**LUNCH_TIME**
* lunch_time_id serial, примарен клуч
* comp_order_id int
* contract_id int, задолжителен
* lunch_start timestamp, задолжителен
* lunch_end timestamp, задолжителен
* lunch_preorder_offset int, задолжителен DEFAULT 0
* lunch_weekday string, задолжителен
* CONSTRAINT fk_lunch_time_company_order
    FOREIGN KEY (comp_order_id)
    REFERENCES company_order(comp_order_id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
* CONSTRAINT fk_lunch_time_contract
    FOREIGN KEY (contract_id)
    REFERENCES contract(contract_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
* CONSTRAINT chk_lunch_time_offset CHECK (lunch_preorder_offset >= 0)
* CONSTRAINT chk_lunch_weekday CHECK (
    lunch_weekday IN ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday')
)
* CONSTRAINT chk_lunch_interval CHECK (lunch_end > lunch_start)

**MEAL**
* meal_id serial, примарен клуч
* cat_id int, задолжителен
* rest_id int, задолжителен
* meal_name string, задолжителен
* meal_description string
* meal_price NUMERIC(10,2), задолжителен
* meal_weight int, задолжителен
* CONSTRAINT fk_meal_category
    FOREIGN KEY (cat_id)
    REFERENCES category(cat_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
* CONSTRAINT fk_meal_restaurant
    FOREIGN KEY (rest_id)
    REFERENCES restaurant(rest_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
* CONSTRAINT chk_meal_price CHECK (meal_price >= 0)
* CONSTRAINT chk_meal_weight CHECK (meal_weight > 0)
* CONSTRAINT uq_meal_restaurant_name UNIQUE (rest_id, meal_name)

**ORDER_REVIEW**
* order_review_id serial, примарен клуч
* order_id int, задолжителен и уникатен
* order_review_food_rating int, задолжителен
* order_review_res_rating int, задолжителен
* CONSTRAINT fk_order_review_order
    FOREIGN KEY (order_id)
    REFERENCES customer_order(order_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
* CONSTRAINT chk_order_review_food_rating CHECK (order_review_food_rating BETWEEN 1 AND 5)
* CONSTRAINT chk_order_review_res_rating CHECK (order_review_res_rating BETWEEN 1 AND 5)

**ORDER_STATUS**
* o_status_id serial, примарен клуч
* o_status_name string, задолжителен и уникатен

**RESTAURANT**
* rest_id serial, примарен клуч
* rest_name string, задолжителен
* rest_email string, задолжителен и уникатен
* rest_location string, задолжителен
* rest_phone string, задолжителен и уникатен
* rest_website string, уникатен

**REVIEW**
* review_id serial, примарен клуч
* review_comment string
* review_created_at timestamp, задолжителен DEFAULT CURRENT_TIMESTAMP
* review_rating int, задолжителен
* CONSTRAINT chk_review_rating CHECK (review_rating BETWEEN 1 AND 5)

==== Релациски табели

**Order_Meal**
* order_id int, задолжителен
* meal_id int, задолжителен
* quantity int, задолжителен DEFAULT 1
* PRIMARY KEY (order_id, meal_id), примарен клуч
* CONSTRAINT fk_order_meal_order
    FOREIGN KEY (order_id)
    REFERENCES customer_order(order_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
* CONSTRAINT fk_order_meal_meal
    FOREIGN KEY (meal_id)
    REFERENCES meal(meal_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
* CONSTRAINT chk_order_meal_quantity CHECK (quantity > 0)

**Order_Drink**
* order_id int, задолжителен
* drink_id int, задолжителен
* quantity int, задолжителен DEFAULT 1
* PRIMARY KEY (order_id, drink_id), примарен клуч
* CONSTRAINT fk_order_drink_order
    FOREIGN KEY (order_id)
    REFERENCES customer_order(order_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
* CONSTRAINT fk_order_drink_drink
    FOREIGN KEY (drink_id)
    REFERENCES drink(drink_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
* CONSTRAINT chk_order_drink_quantity CHECK (quantity > 0)

**Meal_Ingredient**
* meal_id int, задолжителен
* ingr_id int, задолжителен
* PRIMARY KEY (meal_id, ingr_id), примарен клуч
* CONSTRAINT fk_meal_ingredient_meal
    FOREIGN KEY (meal_id)
    REFERENCES meal(meal_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
* CONSTRAINT fk_meal_ingredient_ingredient
    FOREIGN KEY (ingr_id)
    REFERENCES ingredient(ingr_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE

**Allergen_Ingredient**
* allergen_id int, задолжителен
* ingr_id int, задолжителен
* PRIMARY KEY (allergen_id, ingr_id), примарен клуч
* CONSTRAINT fk_allergen_ingredient_allergen
    FOREIGN KEY (allergen_id)
    REFERENCES allergen(allergen_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
* CONSTRAINT fk_allergen_ingredient_ingredient
    FOREIGN KEY (ingr_id)
    REFERENCES ingredient(ingr_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE