$$using My sql i need help creating a stored procedure that Query the database to determine if a customer is eligible for a loyalty reward. Customers can be eligible for different types of loyalty rewards $$ frequency $($ex$.$ Buy $10$ coffees, get one free$)$ or amount $($ex$.$ Spend $$20,$ get $$2$ off$).$ Your query also needs to check the date of the purchase to see if the loyalty reward is valid for that date.

datebase information

$--$ Create Tables

CREATE TABLE Customer $($

CustomerID INT PRIMARY KEY,

FirstName VARCHAR$(50),$

LastName VARCHAR$(50),$

Email VARCHAR$(100),$

Phone VARCHAR$(20),$

Password VARCHAR$(100),$

DateOfBirth DATE

$)$;

CREATE TABLE Location $($

LocationID INT PRIMARY KEY,

City VARCHAR$(50),$

Address VARCHAR$(100),$

ManagerName VARCHAR$(50),$

Phone VARCHAR$(20)$

$)$;

CREATE TABLE Orders $($

OrderID INT PRIMARY KEY,

CustomerID INT,

LocationID INT,

OrderDate DATE,

TotalAmount DECIMAL$(10,2),$

CampaignID INT,

FOREIGN KEY $($CustomerID$)$ REFERENCES Customer$($CustomerID$),$

FOREIGN KEY $($LocationID$)$ REFERENCES Location$($LocationID$)$

$)$;

CREATE TABLE Inventory $($

InventoryID INT PRIMARY KEY,

LocationID INT,

StockLevel INT,

DateUpdated DATE,

FOREIGN KEY $($LocationID$)$ REFERENCES Location$($LocationID$)$

$)$;

CREATE TABLE Payment $($

PaymentID INT PRIMARY KEY,

PaymentMethod VARCHAR$(50),$

AccountName VARCHAR$(50),$

ZipCode VARCHAR$(10),$

CardNumber VARCHAR$(20),$

CVV VARCHAR$(4),$

ExpirationDate DATE

$)$;

CREATE TABLE CustomerContact $($

ContactID INT PRIMARY KEY,

CustomerID INT,

Email VARCHAR$(100),$

PhoneNumber VARCHAR$(20),$

FOREIGN KEY $($CustomerID$)$ REFERENCES Customer$($CustomerID$)$

$)$;

CREATE TABLE LoyaltyCampaign $($

CampaignID INT PRIMARY KEY,

CampaignName VARCHAR$(100),$

StartDate DATE,

EndDate DATE,

DiscountType VARCHAR$(50),$

RequiredItems INT,

RequiredValue DECIMAL$(10,2),$

DiscountAmount DECIMAL$(10,2)$

$)$;

CREATE TABLE Employee $($

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR$(50),$

LastName VARCHAR$(50),$

Email VARCHAR$(100),$

Position VARCHAR$(50),$

LocationID INT,

FOREIGN KEY $($LocationID$)$ REFERENCES Location$($LocationID$)$

$)$;

CREATE TABLE OrderItem $($

OrderItemID INT PRIMARY KEY,

OrderID INT,

ProductID INT,

Quantity INT,

ItemPrice DECIMAL$(10,2),$

FOREIGN KEY $($OrderID$)$ REFERENCES Orders$($OrderID$)$

$)$;

CREATE TABLE OnlineCart $($

CartID INT PRIMARY KEY,

CustomerID INT,

price DECIMAL$(10,2),$

quantity INT,

subtotal DECIMAL$(10,2),$

productID INT,

FOREIGN KEY $($CustomerID$)$ REFERENCES Customer$($CustomerID$)$

$)$;

CREATE TABLE Product $($

ProductID INT PRIMARY KEY,

ProductName VARCHAR$(100),$

ProductType VARCHAR$(50),$

Price DECIMAL$(10,2),$

StockLevel INT

$)$;

CREATE TABLE Rewards $($

RewardID INT PRIMARY KEY,

CustomerID INT,

RewardType VARCHAR$(50),$

RewardValue DECIMAL$(10,2),$

EarnedDate DATE,

ExpirationDate DATE,

isRedeemed BOOLEAN,

FOREIGN KEY $($CustomerID$)$ REFERENCES Customer$($CustomerID$)$

$)$;

CREATE TABLE TransactionHistory $($

TransactionID INT PRIMARY KEY,

OrderID INT,

CustomerID INT,

TransactionDate DATE,

PaymentType VARCHAR$(50),$

TransactionAmount DECIMAL$(10,2),$

FOREIGN KEY $($OrderID$)$ REFERENCES Orders$($OrderID$),$

FOREIGN KEY $($CustomerID$)$ REFERENCES Customer$($CustomerID$)$

$)$;

CREATE TABLE CustomerSession $($

CustomerSessionID INT PRIMARY KEY,

CustomerID INT,

Date DATE,

LoginTime TIME,

ProductID INT,

FOREIGN KEY $($CustomerID$)$ REFERENCES Customer$($CustomerID$),$

FOREIGN KEY $($ProductID$)$ REFERENCES Product$($ProductID$)$

$)$;