CREATE TABLE Clients (
  Client_Id int PRIMARY KEY,
  First_name varchar(255),
  Last_name varchar(255),
  Address varchar(255),
  City varchar(255),
  State varchar(255)  
);

CREATE TABLE Customers ( 
  Customer_Id int PRIMARY KEY,
  First_name varchar(255),
  Last_name varchar(255),
  Age int,
  Country varchar(255)  
);

CREATE TABLE Orders ( 
  Order_Id int PRIMARY KEY,
  Item varchar(255),
  Amount Decimal(10,2),
  Customer_Id int,
  FOREIGN KEY (Customer_Id) REFERENCES Customers(Customer_Id)
);

CREATE TABLE Shippings (
  Shipping_Id int PRIMARY KEY,
  Status varchar(255),
  Customer_Id int,  
  FOREIGN KEY (Customer_Id) REFERENCES Customers(Customer_Id)
);

INSERT OR IGNORE INTO Clients (Client_id, First_name, Last_name, Address, City, State) VALUES
(0909, 'Josh', 'Gwan', 'Raya Garden Cond', 'Paranaque', 'Metro Manila'),
(0907, 'John', 'Togonon', 'Hawaiian', 'Silay', 'Negros Occidental'),
(0919, 'John', 'Barcelona', 'IDK', 'Pontevedra', 'Negros Occidental'),
(0875, 'Regine', 'Mesicula', 'IDK', 'Hinigaran', 'Negros Occidental'),
(0914, 'Mark', 'Gwan', '35th Street', 'Quezon', 'Metro Manila'),
(0866, 'Romana', 'Gatmaitan', 'Alvarez Street', 'Pasay', 'Metro Manila'),
(0842, 'Charisma', 'Gatmaitan', 'Alvarez Street', 'Pasay', 'Metro Manila'),
(0902, 'Nica', 'Gatmaitan', 'Fishers Market', 'Novaliches', 'Metro Manila'),
(0935, 'Chin', 'Gwan', 'Raya Garden Cond', 'Paranaque', 'Metro Manila'),
(0922, 'Rosalyn', 'Gwan', 'Executive Village', 'Talisay', 'Negros Occidental');

INSERT OR IGNORE INTO Customers (Customer_Id, First_name, Last_name, Age, Country) VALUES
(909, 'Josh', 'Gwan', 22, 'Korea'),  
(907, 'John', 'Togonon', 22, 'Africa'),
(919, 'John', 'Barcelona', 23, 'Nigeria'),
(875, 'Regine', 'Mesicula', 22, 'Uganda'),
(914, 'Mark', 'Gwan', 30, 'North Korea'),
(866, 'Romana', 'Gatmaitan', 23, 'Canada'),
(842, 'Charisma', 'Gatmaitan', 28, 'New Zealand'),
(902, 'Nica', 'Gatmaitan', 31, 'Korea'),
(935, 'Chin', 'Gwan', 26, 'North Korea'),
(922, 'Rosalyn', 'Gwan', 59, 'Japan');

INSERT OR IGNORE INTO Orders (Order_Id, Item, Amount, Customer_Id) VALUES
(6, '21 Inch Wood', 750, 909),
(7, '***** Plunger 21cm Wide', 2050, 907),
(8, 'Oakwood 21 Inch Cue Stick', 35000, 919),
(9, 'XXXL Grape Blck Dispo', 1050, 875),
(10, 'GTA 6 and Multisense Triple Shock PS5 Console', 10500, 914),
(11, 'Lululemon Terno Tights Navy Blue', 8500, 866),
(12, 'Phillips Baby Milk Dispenser', 12500, 842),
(13, 'A5 Wagyu Vacuum Sealed Dry Aged', 5500, 902),
(14, 'Coach Backpack Corduroy Olive Green', 65000, 935),
(15, 'Kate Spade Limited Edition Wallet', 25000, 922);  

INSERT OR IGNORE INTO Shippings (Shipping_Id, Status, Customer_Id) VALUES
(6, 'Lost Package', 909),
(7, 'Ultimately Lost Package', 907),
(8, 'Delivering', 919),
(9, 'Pending', 875),
(10, 'Delivered', 914),
(11, 'Delivered', 866),
(12, 'Unpaid', 842),
(13, 'Damaged', 902),  
(14, 'Delivering', 935),
(15, 'Delayed Package', 922);

SELECT * FROM Clients 
ORDER BY Client_Id ASC, Last_Name DESC;

SELECT * FROM Customers 
WHERE Age < 28;

SELECT AVG(Amount) AS Average_Amount 
FROM Orders
GROUP BY Order_Id;

CREATE PROCEDURE GetCustomerOrders(IN CustomerId INT)
BEGIN 
    SELECT o.Order_Id, o.Item, o.Amount 
    FROM Orders o  
    JOIN Customers c ON o.Customer_Id = c.Customer_Id 
    WHERE c.Customer_Id = CustomerId;
END;

CREATE PROCEDURE UpdateShippingStatus(IN shippingId INT, IN newStatus VARCHAR(255))
BEGIN
    UPDATE Shippings
    SET Status = newStatus
    WHERE Shipping_Id = shippingId;
END;