01_database_and_tables.sql

create database FoodDelivery;
use FoodDelivery;

create table Customers(
	CustomerID int auto_increment primary key,
    FirstName varchar (30) not null,
    LastName varchar (30),
    Phone VARCHAR(15) NOT NULL UNIQUE,
    Email VARCHAR(40) NOT NULL UNIQUE,
	City varchar (50),
    SignupDate date default (CURRENT_DATE),
  	Status VARCHAR(15) DEFAULT 'Active' CHECK (Status IN ('Active','Inactive','Blocked'))
);

create table RestaurantCategories(
	CategoryID int auto_increment primary key,
    CategoryName varchar(35) not null 
);


ALTER TABLE RestaurantCategories
ADD UNIQUE (CategoryName);


create table Restaurants(
	RestaurantID int auto_increment primary key,
    RestaurantName varchar(50) not null unique,
	CategoryID int not null,
    City varchar (35),
	Rating decimal (2,1) check (Rating between 1.0 and 5.0 ),
    OpeningTime time,
    ClosingTime time,
    Status varchar (20) check (Status IN('Open', 'Closed', 'Temporarily Closed')),
    foreign key (CategoryID) references RestaurantCategories (CategoryID)
);


create table MenuItems(
	MenuItemID int auto_increment primary key,
	RestaurantID int not null,
	ItemName varchar(50) not null,
	Price decimal (10,2) not null check (price >= 0),
	Type varchar(20) not null check (type in ('Veg', 'Non-veg')), 
	Availability varchar(25) check (Availability in('Available', 'Out of Stock')),
    foreign key (RestaurantID) references Restaurants (RestaurantID)
);

create table Orders(
	OrderID int auto_increment primary key,
	CustomerID int not null,
	RestaurantID int not null,
	OrderDate date not null,
	TotalAmount decimal (10,2) not null check (totalamount >= 0),
	OrderStatus varchar(25) check (OrderStatus in('Preparing', 'Out for Delivery', 'Delivered', 'Cancelled')),
    Foreign key (CustomerID) references Customers (CustomerID),
    Foreign key (RestaurantID) references Restaurants (RestaurantID)
);

alter table Orders 
modify OrderDate DATETIME DEFAULT CURRENT_TIMESTAMP;

ALTER table ORDERS
ADD PartnerID INT;


Create Table OrderItems(
	OrderItemID int auto_increment primary key, 
	OrderID int not null,
	MenuItemID int not null,
	Quantity int not null,
	Price decimal (10,2) check (Price >= 0),
	Foreign key(OrderID) References Orders (OrderID),
    Foreign key(MenuItemID) References MenuItems (MenuItemID)
);

alter table OrderItems
modify Quantity INT NOT NULL CHECK (Quantity > 0);

Create table DeliveryPartners(
	PartnerID int auto_increment primary key,
	PartnerName varchar(50) not null,
	Phone varchar (15) not null unique,
	VehicleType varchar(15) not null Check (VehicleType in('Bike', 'Scooter', 'Cycle')),
	Rating decimal (2,1) Check(Rating Between 1.0 and 5.0),
	Status varchar(20) DEFAULT 'Available' NOT NULL check (Status in ('Available', 'On Delivery', 'Offline'))
	
);


ALTER table ORDERS
ADD FOREIGN KEY (PartnerID) 
REFERENCES DeliveryPartners (PartnerID);


create table IF NOT EXISTS Payments(
	PaymentID int primary key,
	OrderID int not null,
	PaymentMethod varchar (15) check(PaymentMethod in ('UPI', 'Card', 'COD', 'Wallet')),
	Amount decimal(10,2) check (Amount >= 0),
	PaymentStatus varchar(15) check(PaymentStatus IN ('Success', 'Pending', 'Failed')),
    foreign key (OrderID) references Orders (OrderID)
);

alter table payments
modify amount decimal(10,2) check (Amount >= 0) not null;

alter table payments
modify PaymentID INT AUTO_INCREMENT;

Create table if not exists Reviews(
	ReviewID int auto_increment primary key,
	CustomerID int not null,
	RestaurantID int not null,
	Rating decimal (2,1) not null check(Rating between 1.0 and 5.0),
	Comment varchar (255),
	ReviewDate date not null,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
	FOREIGN KEY (RestaurantID) REFERENCES Restaurants(RestaurantID),
    UNIQUE(CustomerID, RestaurantID)
);


create table if not exists Coupons(
	CouponID int auto_increment primary key,
	CouponCode varchar(30) not null unique,
	DiscountPercent	int default 10 CHECK (DiscountPercent BETWEEN 1 AND 100),
	ExpiryDate date,
	MinimumOrder int default 300 CHECK (MinimumOrder >= 0),
	Status varchar(25) not null default 'active' check(status in('Expired', 'Active'))
);

SHOW TABLES;


SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'restaurants'
AND TABLE_SCHEMA = 'fooddelivery';