BEGIN;

DROP SCHEMA IF EXISTS kbnteam CASCADE;

CREATE SCHEMA kbnteam;

SET search_path TO kbnteam;


CREATE TABLE 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_role VARCHAR(20) NOT NULL,
    CONSTRAINT uq_api_user_id_role
        UNIQUE (user_id, user_role),
    CONSTRAINT chk_api_user_email
        CHECK (user_email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$'),
    CONSTRAINT chk_api_user_phone
        CHECK (user_phone_no IS NULL OR user_phone_no ~ '^\+?[0-9]{7,15}$'),
    CONSTRAINT chk_api_user_role
        CHECK (user_role IN ('api_admin', 'customer', 'driver'))
);

CREATE TABLE company (
    company_id SERIAL PRIMARY KEY,
    company_name VARCHAR(255) NOT NULL UNIQUE,
    company_address VARCHAR(255) NOT NULL
);

CREATE TABLE 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,
    CONSTRAINT chk_restaurant_email
        CHECK (rest_email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$'),
    CONSTRAINT chk_restaurant_phone
        CHECK (rest_phone ~ '^\+?[0-9]{7,15}$')
);

CREATE TABLE category (
    cat_id SERIAL PRIMARY KEY,
    cat_name VARCHAR(255) NOT NULL UNIQUE,
    cat_description VARCHAR(255)
);

CREATE TABLE contract_status (
    contract_status_id SERIAL PRIMARY KEY,
    contract_status_name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE customer_loyalty_status (
    cus_loyalty_status_id SERIAL PRIMARY KEY,
    cus_loyalty_status_name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE delivery_status (
    d_status_id SERIAL PRIMARY KEY,
    d_status_name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE order_status (
    o_status_id SERIAL PRIMARY KEY,
    o_status_name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE ingredient (
    ingr_id SERIAL PRIMARY KEY,
    ingr_name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE alergen (
    alergen_id SERIAL PRIMARY KEY,
    alergen_name VARCHAR(255) NOT NULL UNIQUE,
    alergen_description VARCHAR(255)
);

CREATE TABLE 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)
);

CREATE TABLE 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)
);

CREATE TABLE api_admin (
    user_id INTEGER PRIMARY KEY,
    user_role VARCHAR(20) NOT NULL DEFAULT 'api_admin',
    rest_id INTEGER NOT NULL,
    CONSTRAINT chk_api_admin_role
        CHECK (user_role = 'api_admin'),
    CONSTRAINT fk_admin_user
        FOREIGN KEY (user_id, user_role)
        REFERENCES api_user(user_id, user_role)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_admin_restaurant
        FOREIGN KEY (rest_id)
        REFERENCES restaurant(rest_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE customer (
    user_id INTEGER PRIMARY KEY,
    user_role VARCHAR(20) NOT NULL DEFAULT 'customer',
    company_id INTEGER NOT NULL,
    CONSTRAINT chk_customer_role
        CHECK (user_role = 'customer'),
    CONSTRAINT fk_customer_user
        FOREIGN KEY (user_id, user_role)
        REFERENCES api_user(user_id, user_role)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_customer_company
        FOREIGN KEY (company_id)
        REFERENCES company(company_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE driver (
    user_id INTEGER PRIMARY KEY,
    user_role VARCHAR(20) NOT NULL DEFAULT 'driver',
    rest_id INTEGER NOT NULL,
    CONSTRAINT chk_driver_role
        CHECK (user_role = 'driver'),
    CONSTRAINT fk_driver_user
        FOREIGN KEY (user_id, user_role)
        REFERENCES api_user(user_id, user_role)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_driver_restaurant
        FOREIGN KEY (rest_id)
        REFERENCES restaurant(rest_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

CREATE TABLE contract (
    contract_id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL,
    contract_end_date DATE NOT NULL,
    contract_start_date DATE NOT NULL,
    contract_status_id INTEGER NOT NULL,
    rest_id INTEGER NOT NULL,
    CONSTRAINT fk_contract_company
        FOREIGN KEY (company_id)
        REFERENCES company(company_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 fk_contract_restaurant
        FOREIGN KEY (rest_id)
        REFERENCES restaurant(rest_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT chk_contract_dates
        CHECK (contract_end_date >= contract_start_date)
);

CREATE TABLE 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,
    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 NULL
);

CREATE TABLE 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
);

CREATE TABLE meal (
    meal_id SERIAL PRIMARY KEY,
    cat_id INTEGER NOT NULL,
    meal_description VARCHAR(255),
    meal_name VARCHAR(255) NOT NULL,
    meal_price NUMERIC(10,2) NOT NULL,
    meal_weight INTEGER NOT NULL,
    rest_id 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)
);

CREATE TABLE drink (
    drink_id SERIAL PRIMARY KEY,
    drink_milliliters INTEGER NOT NULL,
    drink_name VARCHAR(255) NOT NULL,
    drink_price NUMERIC(10,2) NOT NULL,
    rest_id INTEGER 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)
);

CREATE TABLE customer_loyalty (
    cus_loyalty_id SERIAL PRIMARY KEY,
    cus_loyalty_curr_points INTEGER NOT NULL DEFAULT 0,
    cus_loyalty_joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    cus_loyalty_status_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL UNIQUE,
    tier_id INTEGER NOT NULL,
    CONSTRAINT fk_customer_loyalty_status
        FOREIGN KEY (cus_loyalty_status_id)
        REFERENCES customer_loyalty_status(cus_loyalty_status_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_customer_loyalty_user
        FOREIGN KEY (user_id)
        REFERENCES customer(user_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_customer_loyalty_tier
        FOREIGN KEY (tier_id)
        REFERENCES loyalty_tier(tier_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT chk_customer_loyalty_points
        CHECK (cus_loyalty_curr_points >= 0)
);

CREATE TABLE customer_order (
    order_id SERIAL PRIMARY KEY,
    comp_order_id INTEGER NOT NULL,
    customer_user_id INTEGER NOT NULL,
    order_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    o_status_id INTEGER NOT NULL,
    order_total NUMERIC(10,2) NOT NULL DEFAULT 0,
    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_customer
        FOREIGN KEY (customer_user_id)
        REFERENCES customer(user_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    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)
);

CREATE TABLE invoice (
    invoice_id SERIAL PRIMARY KEY,
    comp_order_id INTEGER NOT NULL UNIQUE,
    CONSTRAINT fk_invoice_company_order
        FOREIGN KEY (comp_order_id)
        REFERENCES company_order(comp_order_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE lunch_time (
    lunch_time_id SERIAL PRIMARY KEY,
    comp_order_id INTEGER,
    contract_id INTEGER NOT NULL,
    lunch_end TIMESTAMP NOT NULL,
    lunch_preorder_offset INTEGER NOT NULL DEFAULT 0,
    lunch_start TIMESTAMP NOT NULL,
    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_interval
        CHECK (lunch_end > lunch_start),
    CONSTRAINT chk_lunch_weekday
        CHECK (lunch_weekday IN ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'))
);

CREATE TABLE meal_ingredient (
    ingr_id INTEGER NOT NULL,
    meal_id INTEGER NOT NULL,
    PRIMARY KEY (ingr_id, meal_id),
    CONSTRAINT fk_meal_ingredient_ingredient
        FOREIGN KEY (ingr_id)
        REFERENCES ingredient(ingr_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_meal_ingredient_meal
        FOREIGN KEY (meal_id)
        REFERENCES meal(meal_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE alergen_ingredient (
    alergen_id INTEGER NOT NULL,
    ingr_id INTEGER NOT NULL,
    PRIMARY KEY (alergen_id, ingr_id),
    CONSTRAINT fk_alergen_ingredient_alergen
        FOREIGN KEY (alergen_id)
        REFERENCES alergen(alergen_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_alergen_ingredient_ingredient
        FOREIGN KEY (ingr_id)
        REFERENCES ingredient(ingr_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE order_meal (
    meal_id INTEGER NOT NULL,
    order_id INTEGER NOT NULL,
    PRIMARY KEY (meal_id, order_id),
    CONSTRAINT fk_order_meal_meal
        FOREIGN KEY (meal_id)
        REFERENCES meal(meal_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_order_meal_order
        FOREIGN KEY (order_id)
        REFERENCES customer_order(order_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE order_drink (
    drink_id INTEGER NOT NULL,
    order_id INTEGER NOT NULL,
    PRIMARY KEY (drink_id, order_id),
    CONSTRAINT fk_order_drink_drink
        FOREIGN KEY (drink_id)
        REFERENCES drink(drink_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_order_drink_order
        FOREIGN KEY (order_id)
        REFERENCES customer_order(order_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE delivery_review (
    delivery_review_id SERIAL PRIMARY KEY,
    del_review_courier_rating INTEGER NOT NULL,
    del_review_speed_rating INTEGER NOT NULL,
    delivery_id INTEGER NOT NULL UNIQUE,
    review_id INTEGER NOT NULL UNIQUE,
    CONSTRAINT fk_delivery_review_delivery
        FOREIGN KEY (delivery_id)
        REFERENCES delivery(delivery_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_delivery_review_review
        FOREIGN KEY (review_id)
        REFERENCES review(review_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)
);

CREATE TABLE 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,
    review_id INTEGER NOT NULL UNIQUE,
    CONSTRAINT fk_order_review_order
        FOREIGN KEY (order_id)
        REFERENCES customer_order(order_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_order_review_review
        FOREIGN KEY (review_id)
        REFERENCES review(review_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)
);

COMMIT;