RelationalDesign: ddl.sql

drop schema if exists kbnteam CASCADE;

create schema kbnteam;

set search_path to kbnteam;

create table admin (
    admin_id bigserial primary key,
    admin_name varchar(255) not null,
    admin_email varchar(255) unique not null,
    admin_password varchar(255) not null
);

create table api_user (
    api_user_id bigserial primary key,
    username varchar(255) unique not null,
    password varchar(255) not null,
    role varchar(100) not null
);

create table company (
    company_id bigserial primary key,
    company_name varchar(255) not null,
    company_email varchar(255) unique,
    company_phone varchar(255) unique,
    company_address varchar(255)
);

create table contract_status (
    contract_status_id bigserial primary key,
    status_name varchar(100) not null unique
);

create table contract (
    contract_id bigserial primary key,
    company_id bigint not null,
    contract_status_id bigint not null,
    contract_start_date date,
    contract_end_date date,
    contract_value numeric(12,2),

    constraint fk_contract_company
        foreign key (company_id) references company(company_id),

    constraint fk_contract_status
        foreign key (contract_status_id) references contract_status(contract_status_id)
);

create table restaurant (
    rest_id bigserial primary key,
    rest_name varchar(255) not null,
    rest_location varchar(255) not null,
    rest_website varchar(255),
    rest_phone varchar(255) unique not null,
    rest_email varchar(255) unique,
    company_id bigint,

    constraint fk_restaurant_company
        foreign key (company_id) references company(company_id)
);

create table category (
    category_id bigserial primary key,
    category_name varchar(255) not null unique
);

create table lunch_time (
    lunch_time_id bigserial primary key,
    lunch_name varchar(100) not null,
    start_time time,
    end_time time
);

create table ingredient (
    ingredient_id bigserial primary key,
    ingredient_name varchar(255) not null unique
);

create table alergen (
    alergen_id bigserial primary key,
    alergen_name varchar(255) not null unique
);

create table meal (
    meal_id bigserial primary key,
    rest_id bigint not null,
    category_id bigint,
    lunch_time_id bigint,
    meal_name varchar(255) not null,
    meal_price numeric(10,2) not null,
    meal_description text,

    constraint fk_meal_restaurant
        foreign key (rest_id) references restaurant(rest_id),

    constraint fk_meal_category
        foreign key (category_id) references category(category_id),

    constraint fk_meal_lunch_time
        foreign key (lunch_time_id) references lunch_time(lunch_time_id)
);

create table drink (
    drink_id bigserial primary key,
    rest_id bigint not null,
    drink_name varchar(255) not null,
    drink_price numeric(10,2) not null,

    constraint fk_drink_restaurant
        foreign key (rest_id) references restaurant(rest_id)
);

create table meal_ingredient (
    meal_id bigint not null,
    ingredient_id bigint not null,

    primary key (meal_id, ingredient_id),

    constraint fk_meal_ingredient_meal
        foreign key (meal_id) references meal(meal_id),

    constraint fk_meal_ingredient_ingredient
        foreign key (ingredient_id) references ingredient(ingredient_id)
);

create table alergen_ingredient (
    alergen_id bigint not null,
    ingredient_id bigint not null,

    primary key (alergen_id, ingredient_id),

    constraint fk_alergen_ingredient_alergen
        foreign key (alergen_id) references alergen(alergen_id),

    constraint fk_alergen_ingredient_ingredient
        foreign key (ingredient_id) references ingredient(ingredient_id)
);

create table customer (
    customer_id bigserial primary key,
    first_name varchar(255) not null,
    last_name varchar(255) not null,
    email varchar(255) unique not null,
    phone varchar(255) unique,
    address varchar(255),
    password varchar(255) not null
);

create table loyalty_tier (
    loyalty_tier_id bigserial primary key,
    tier_name varchar(100) not null unique,
    min_points integer not null,
    discount_percent numeric(5,2)
);

create table customer_loyalty_status (
    customer_loyalty_status_id bigserial primary key,
    status_name varchar(100) not null unique
);

create table customer_loyalty (
    customer_loyalty_id bigserial primary key,
    customer_id bigint not null unique,
    loyalty_tier_id bigint,
    customer_loyalty_status_id bigint,
    points integer default 0 not null,

    constraint fk_customer_loyalty_customer
        foreign key (customer_id) references customer(customer_id),

    constraint fk_customer_loyalty_tier
        foreign key (loyalty_tier_id) references loyalty_tier(loyalty_tier_id),

    constraint fk_customer_loyalty_status
        foreign key (customer_loyalty_status_id)
        references customer_loyalty_status(customer_loyalty_status_id)
);

create table driver (
    driver_id bigserial primary key,
    company_id bigint,
    first_name varchar(255) not null,
    last_name varchar(255) not null,
    phone varchar(255) unique,
    vehicle_info varchar(255),

    constraint fk_driver_company
        foreign key (company_id) references company(company_id)
);

create table order_status (
    order_status_id bigserial primary key,
    status_name varchar(100) not null unique
);

create table customer_order (
    order_id bigserial primary key,
    customer_id bigint not null,
    rest_id bigint not null,
    order_status_id bigint not null,
    order_date timestamp not null default current_timestamp,
    total_amount numeric(12,2) not null,

    constraint fk_customer_order_customer
        foreign key (customer_id) references customer(customer_id),

    constraint fk_customer_order_restaurant
        foreign key (rest_id) references restaurant(rest_id),

    constraint fk_customer_order_status
        foreign key (order_status_id) references order_status(order_status_id)
);

create table order_meal (
    order_id bigint not null,
    meal_id bigint not null,
    quantity integer not null default 1,

    primary key (order_id, meal_id),

    constraint fk_order_meal_order
        foreign key (order_id) references customer_order(order_id),

    constraint fk_order_meal_meal
        foreign key (meal_id) references meal(meal_id)
);

create table order_drink (
    order_id bigint not null,
    drink_id bigint not null,
    quantity integer not null default 1,

    primary key (order_id, drink_id),

    constraint fk_order_drink_order
        foreign key (order_id) references customer_order(order_id),

    constraint fk_order_drink_drink
        foreign key (drink_id) references drink(drink_id)
);

create table delivery_status (
    delivery_status_id bigserial primary key,
    status_name varchar(100) not null unique
);

create table delivery (
    delivery_id bigserial primary key,
    order_id bigint not null unique,
    driver_id bigint,
    delivery_status_id bigint,
    delivered_at timestamp,

    constraint fk_delivery_order
        foreign key (order_id) references customer_order(order_id),

    constraint fk_delivery_driver
        foreign key (driver_id) references driver(driver_id),

    constraint fk_delivery_status
        foreign key (delivery_status_id) references delivery_status(delivery_status_id)
);

create table review (
    review_id bigserial primary key,
    customer_id bigint not null,
    review_text text,
    rating integer check (rating between 1 and 5),

    constraint fk_review_customer
        foreign key (customer_id) references customer(customer_id)
);

create table order_review (
    order_review_id bigserial primary key,
    order_id bigint not null unique,
    review_id bigint not null unique,

    constraint fk_order_review_order
        foreign key (order_id) references customer_order(order_id),

    constraint fk_order_review_review
        foreign key (review_id) references review(review_id)
);

create table delivery_review (
    delivery_review_id bigserial primary key,
    delivery_id bigint not null unique,
    review_id bigint not null unique,

    constraint fk_delivery_review_delivery
        foreign key (delivery_id) references delivery(delivery_id),

    constraint fk_delivery_review_review
        foreign key (review_id) references review(review_id)
);

create table invoice (
    invoice_id bigserial primary key,
    order_id bigint not null unique,
    issued_at timestamp default current_timestamp,
    amount numeric(12,2) not null,

    constraint fk_invoice_order
        foreign key (order_id) references customer_order(order_id)
);

create table company_order (
    company_order_id bigserial primary key,
    company_id bigint not null,
    order_id bigint not null unique,

    constraint fk_company_order_company
        foreign key (company_id) references company(company_id),

    constraint fk_company_order_order
        foreign key (order_id) references customer_order(order_id)
);