DROP TABLE IF EXISTS Seller_Order, Customer_Order, Meat_Supplies, Meat_Orders, Wholesaler, Meat, Orders, Seller, Customers;

CREATE TABLE Customers(
    Customer_ID INT PRIMARY KEY AUTO_INCREMENT,
    Customer_name VARCHAR(50),
    Customer_Address VARCHAR(100),
    Customer_Phone_Number VARCHAR(13) DEFAULT '000000000'
);

CREATE TABLE Seller (
    Seller_ID INT PRIMARY KEY AUTO_INCREMENT,
    Seller_Name VARCHAR(30),
    Seller_SPH DECIMAL(4,2) CHECK (Seller_SPH >= 0)
);

CREATE TABLE Orders (
    Order_ID INT PRIMARY KEY AUTO_INCREMENT,
    Order_Date DATE,
    Customer_ID INT, 
    Seller_ID INT, 
    Order_TPrice DECIMAL(9,2) CHECK (Order_TPrice >= 0),
    Order_details VARCHAR(100) DEFAULT '---------',
    FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID),
    FOREIGN KEY (Seller_ID) REFERENCES Seller(Seller_ID)
);

CREATE TABLE Meat (
    Meat_Number INT PRIMARY KEY AUTO_INCREMENT,
    Meat_Type VARCHAR(30),
    Origin_Country VARCHAR(40)
);

CREATE TABLE Wholesaler(
    Wholesaler_ID INT PRIMARY KEY AUTO_INCREMENT,
    Wholesaler_name VARCHAR(40),
    Wholesaler_Specialty VARCHAR(30),
    Wholesaler_Phone_Number VARCHAR(13) DEFAULT '000000000'
);

CREATE TABLE Meat_Orders (
    Order_ID INT,
    Meat_Number INT,
    Unit_Price DECIMAL(9,2) CHECK (Unit_Price >= 0),
    Total_Price DECIMAL(9,2) CHECK (Total_Price >= 0),
    FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID),
    FOREIGN KEY (Meat_Number) REFERENCES Meat(Meat_Number),
    PRIMARY KEY (Order_ID, Meat_Number)
);

CREATE TABLE Meat_Supplies (
    Wholesaler_ID INT, 
    Meat_Number INT,
    Meat_Supplied DECIMAL(10,2) CHECK (Meat_Supplied >= 0),
    FOREIGN KEY (Wholesaler_ID) REFERENCES Wholesaler(Wholesaler_ID),
    FOREIGN KEY (Meat_Number) REFERENCES Meat(Meat_Number),
    PRIMARY KEY (Wholesaler_ID, Meat_Number)
);

CREATE TABLE Customer_Order ( 
    Order_ID INT,
    Customer_ID INT,
    FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID),
    FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID),
    PRIMARY KEY (Order_ID, Customer_ID)
);

CREATE TABLE Seller_Order (
    Order_ID INT,
    Seller_ID INT,
    FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID),
    FOREIGN KEY (Seller_ID) REFERENCES Seller(Seller_ID),
    PRIMARY KEY (Order_ID, Seller_ID)
);

INSERT INTO Customers (Customer_ID, Customer_name, Customer_Address, Customer_Phone_Number) VALUES
(1, 'Yael Cohen', '15 Herzl St, Tel Aviv', '0521234567'),
(2, 'Moshe Levi', '123 King George Ave, Jerusalem', '0527654321'),
(3, 'Aviva Barak', '78 Yefe Nof St, Haifa', '0532345678'),
(4, 'Doron Mizrachi', '5 Ben Gurion Blvd, Ashdod', '0529876543'),
(5, 'Tamar Golan', '33 Coral Rd, Eilat', '0524567890'),
(6, 'Oren Hershko', '12 Sokolov St, Herzliya', '0521239876'),
(7, 'Neta Shapira', '88 Sderot Ben Gurion, Netanya', '0523216549');


INSERT INTO Seller (Seller_ID, Seller_Name, Seller_SPH) VALUES
(1, 'Eitan Katz', 150.00),
(2, 'Shira Nir', 165.50),
(3, 'Noam Shemesh', 170.00),
(4, 'Gilad Nevo', 160.00),
(5, 'Idit Sasson', 158.00);


INSERT INTO Meat (Meat_Number, Meat_Type, Origin_Country) VALUES
(1, 'Beef', 'Israel'),
(2, 'Chicken', 'USA'),
(3, 'Lamb', 'New Zealand'),
(4, 'Fish', 'Norway');


INSERT INTO Wholesaler (Wholesaler_ID, Wholesaler_name, Wholesaler_Specialty, Wholesaler_Phone_Number, Wholesaler_Address) VALUES
(1, 'David Malul', 'Beef', '0543219876', '1000 Market St, Tel Aviv, Israel'),
(2, 'John Doe', 'Fish', '00123456789', '2345 Dockyard Rd, Boston, USA'),
(3, 'Maria Garcia', 'Lamb', '3498765432', '678 Queso St, Wellington, New Zealand'),
(4, 'Lu Wei', 'Chicken', '8629876543', '4567 Green Leaf Rd, New York, USA'),
(5, 'Chloe Dubois', NULL, '3312345678', '123 Croissant Ave, Paris, France');


INSERT INTO Orders (Order_ID, Order_Date, Customer_ID, Seller_ID, Order_TPrice, Order_details) VALUES
(1, '2024-05-26', 1, 1, 300.00, 'Weekly beef supply'),
(2, '2024-05-27', 2, 2, 200.00, 'Bi-weekly chicken order'),
(3, '2024-05-28', 3, 3, 450.00, 'Monthly fish stock'),
(4, '2024-05-29', 4, 4, 500.00, 'Quarterly lamb stock'),
(5, '2024-05-30', 5, 5, 250.00, 'Ad-hoc chicken and fish'),
(6, '2024-05-31', 6, 1, 300.00, 'Special order beef'),
(7, '2024-06-01', 7, 2, 220.00, 'Routine chicken supply'),
(8, '2024-06-02', 1, 3, 150.00, 'Extra fish order');