DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Customers;

-- ================================================
-- Create and populate the Customers table
-- ================================================
CREATE TABLE Customers (
    CustID   INT         NOT NULL PRIMARY KEY,
    Name     VARCHAR(50) NOT NULL,
    Country  VARCHAR(50)
);

INSERT INTO Customers (CustID, Name, Country) VALUES
    (1, 'Alice',   'USA'),
    (2, 'Bob',     'Canada'),
    (3, 'Charlie', 'UK'),
    (4, 'David',   'USA'),
    (5, 'Emma',    'Canada');


-- ================================================
-- Create and populate the Orders table
-- ================================================
CREATE TABLE Orders (
    OrderID    INT           NOT NULL PRIMARY KEY,
    CustID     INT           NOT NULL,
    OrderDate  DATE,
    Product    VARCHAR(100),
    Amount     DECIMAL(10,2),
    CONSTRAINT fk_customer FOREIGN KEY (CustID) REFERENCES Customers(CustID)
);

INSERT INTO Orders (OrderID, CustID, OrderDate, Product, Amount) VALUES
    (1001, 1, '2025-01-05', 'Laptop',     999.99),
    (1002, 2, '2025-01-08', 'Headphones', 149.99),
    (1003, 3, '2025-01-10', 'Keyboard',    79.99),
    (1004, 1, '2025-01-15', 'Mouse',       39.99),
    (1005, 4, '2025-01-18', 'Monitor',    499.99),
    (1006, 5, '2025-01-20', 'Webcam',      89.99),
    (1007, 2, '2025-02-01', 'Desk Chair', 299.99),
    (1008, 3, '2025-02-05', 'USB Hub',     29.99);