PakBites: a MySQL database for food delivery

CREATE TABLE Orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    restaurant_id INT NOT NULL,
    rider_id INT,
    total_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    order_status ENUM('Pending', 'Preparing', 'Out for Delivery', 'Delivered', 'Cancelled') DEFAULT 'Pending',
    payment_method ENUM('Cash', 'Credit Card', 'Wallet', 'Digital Payment') NOT NULL,
    ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (restaurant_id) REFERENCES Restaurants(restaurant_id),
    FOREIGN KEY (rider_id) REFERENCES DeliveryRiders(rider_id)
);

CREATE TABLE Reviews (
    review_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    restaurant_id INT NOT NULL,
    rating INT NOT NULL,
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (restaurant_id) REFERENCES Restaurants(restaurant_id),
    UNIQUE (customer_id, restaurant_id),
    CONSTRAINT chk_review_rating CHECK (rating BETWEEN 1 AND 5)
);

create trigger trg_orders_after_update_status
after update on orders
for each row
begin
    if old.order_status <> new.order_status then
        insert into orderstatushistory (order_id, old_status, new_status, changed_at)
        values (new.order_id, old.order_status, new.order_status, current_timestamp);
    end if;
end //

create trigger trg_reviews_before_insert
before insert on reviews
for each row
begin
    if not exists (select 1 from orders where customer_id = new.customer_id and restaurant_id = new.restaurant_id) then
        signal sqlstate '45000' set message_text = 'customer must order from restaurant before reviewing';
    end if;
end //

create event event_autocancel_pending_orders
on schedule every 15 minute
do
begin
    update orders
    set order_status = 'cancelled'
    where order_status = 'pending'
    and ordered_at < now() - interval 2 hour;
end //

create event event_daily_revenue_summary
on schedule every 1 day
starts (curdate() + interval 1 day)
do
begin
    insert into dailysummary (summary_date, total_orders, total_revenue)
    select 
        curdate() - interval 1 day, 
        count(order_id), 
        coalesce(sum(total_amount), 0)
    from orders
    where date(ordered_at) = curdate() - interval 1 day;
end //

create view activeorders as
select 
    o.order_id, 
    concat(c.first_name, ' ', c.last_name) as customer_name, 
    r.name as restaurant_name, 
    concat(dr.first_name, ' ', dr.last_name) as rider_name, 
    o.order_status, 
    timediff(now(), o.ordered_at) as time_since_placed 
from orders o 
join customers c on o.customer_id = c.customer_id 
join restaurants r on o.restaurant_id = r.restaurant_id 
left join deliveryriders dr on o.rider_id = dr.rider_id 
where o.order_status not in ('delivered', 'cancelled');

delimiter //
Create Function CalculateDeliveryFee (distance_km DECIMAL(5,2)) 
returns decimal deterministic 
begin 
declare fee decimal (5,2);
set fee = case
        when distance_km < 3 then 50.00
        when distance_km >= 3 and distance_km <= 7 then 100.00
        when distance_km > 7 and distance_km <= 15 then 150.00
        else 250.00
           end;
    return fee;
end //
delimiter ;

select first_name, last_name from deliveryriders where rider_id in (select rider_id from orders where order_status = 'delivered' 
group by rider_id having count(*) > (select count(*) / count(distinct rider_id) from orders where order_status = 'delivered'));