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



**ADMIN**
* user_id INTEGER PRIMARY KEY DEFAULT 1,
* CONSTRAINT fk_admin_user
        FOREIGN KEY (user_id)
        REFERENCES api_user(user_id)
        ON UPDATE CASCADE
        ON DELETE SET DEFAULT

**ALERGEN**
* alergen_id SERIAL PRIMARY KEY,
* alergen_name VARCHAR(255) NOT NULL UNIQUE,
* alergen_description VARCHAR(255)

**API_USER**
* user_id SERIAL PRIMARY KEY,
* user_first_name VARCHAR(255) NOT NULL,
* user_last_name VARCHAR(255) NOT NULL,
* user_email VARCHAR(255) NOT NULL UNIQUE,
* user_phone_no VARCHAR(20) UNIQUE,
* user_password_hash TEXT NOT NULL

**CATEGORY**
* cat_id SERIAL PRIMARY KEY,
* cat_name VARCHAR(255) NOT NULL UNIQUE,
* cat_description VARCHAR(255)

**COMPANY**
* company_id SERIAL PRIMARY KEY,
* company_name VARCHAR(255) NOT NULL UNIQUE,
* company_address VARCHAR(255) NOT NULL

**COMPANY_ORDER**
* comp_order_id SERIAL PRIMARY KEY,
* company_id INTEGER NOT NULL,
* delivery_id INTEGER UNIQUE,
* 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 PRIMARY KEY,
* company_id INTEGER NOT NULL,
* rest_id INTEGER NOT NULL,
* contract_status_id INTEGER NOT NULL,
* contract_start_date DATE NOT NULL,
* contract_end_date DATE NOT NULL,
* 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 PRIMARY KEY,
* contract_status_name VARCHAR(255) NOT NULL UNIQUE

**CUSTOMER**
*  user_id INTEGER 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 PRIMARY KEY,
* comp_order_id INTEGER NOT NULL,
* company_employee_user_id INTEGER NOT NULL DEFAULT 1,
* order_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
* o_status_id INTEGER NOT NULL,
* order_total NUMERIC(10,2) NOT NULL,
* 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 PRIMARY KEY,
* delivery_date DATE NOT NULL DEFAULT CURRENT_DATE,
* delivery_notes VARCHAR(255),
* d_status_id INTEGER NOT NULL,
* driver_user_id INTEGER NOT NULL 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 PRIMARY KEY,
* delivery_id INTEGER NOT NULL UNIQUE,
* del_review_courier_rating INTEGER NOT NULL,
* del_review_speed_rating INTEGER NOT NULL,
* 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 PRIMARY KEY,
* d_status_name VARCHAR(255) NOT NULL UNIQUE

**DRINK**
* drink_id SERIAL PRIMARY KEY,
* rest_id INTEGER NOT NULL,
* drink_name VARCHAR(255) NOT NULL,
* drink_milliliters INTEGER NOT NULL,
* drink_price NUMERIC(10,2) NOT NULL,
* 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 INTEGER 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 PRIMARY KEY,
* user_id INTEGER NOT NULL UNIQUE,
* emp_loyalty_curr_points INTEGER NOT NULL DEFAULT 0,
* emp_loyalty_joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
* emp_loyalty_status_id INTEGER NOT NULL,
* tier_id INTEGER NOT NULL,
* 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 PRIMARY KEY,
* emp_loyalty_status_name VARCHAR(255) NOT NULL UNIQUE

**INGREDIENT**
* ingr_id SERIAL PRIMARY KEY,
* ingr_name VARCHAR(255) NOT NULL UNIQUE

**LOYALTY_TIER**
* tier_id SERIAL PRIMARY KEY,
* tier_name VARCHAR(255) NOT NULL UNIQUE,
* tier_minimum_points INTEGER NOT NULL,
* tier_maximum_points INTEGER NOT NULL,
* tier_discount_percentage NUMERIC(5,2) NOT NULL,
* tier_free_delivery_eligibility BOOLEAN NOT NULL DEFAULT FALSE,
* tier_priority_support BOOLEAN NOT NULL DEFAULT FALSE,
* tier_created_at DATE NOT NULL DEFAULT CURRENT_DATE,
* tier_update_at TIMESTAMP NOT NULL 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 PRIMARY KEY,
* comp_order_id INTEGER,
* contract_id INTEGER NOT NULL,
* lunch_start TIMESTAMP NOT NULL,
* lunch_end TIMESTAMP NOT NULL,
* lunch_preorder_offset INTEGER NOT NULL DEFAULT 0,
* lunch_weekday VARCHAR(15) NOT NULL,
* 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 PRIMARY KEY,
* cat_id INTEGER NOT NULL,
* rest_id INTEGER NOT NULL,
* meal_name VARCHAR(255) NOT NULL,
* meal_description VARCHAR(255),
* meal_price NUMERIC(10,2) NOT NULL,
* meal_weight INTEGER NOT NULL,
* 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 PRIMARY KEY,
* order_id INTEGER NOT NULL UNIQUE,
* order_review_food_rating INTEGER NOT NULL,
* order_review_res_rating INTEGER NOT NULL,
* 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 PRIMARY KEY,
* o_status_name VARCHAR(255) NOT NULL UNIQUE

**RESTAURANT**
* rest_id SERIAL PRIMARY KEY,
* rest_name VARCHAR(255) NOT NULL,
* rest_email VARCHAR(255) NOT NULL UNIQUE,
* rest_location VARCHAR(255) NOT NULL,
* rest_phone VARCHAR(20) NOT NULL UNIQUE,
* rest_website VARCHAR(255) UNIQUE

**REVIEW**
* review_id SERIAL PRIMARY KEY,
* review_comment VARCHAR(255),
* review_created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
* review_rating INTEGER NOT NULL,
* CONSTRAINT chk_review_rating CHECK (review_rating BETWEEN 1 AND 5)