-- 1. Create Tables

CREATE TABLE Sales_Manager (
    Region NVARCHAR(50) PRIMARY KEY,
    Person NVARCHAR(100) NOT NULL
);

CREATE TABLE Orders (
    Order_ID NVARCHAR(50) PRIMARY KEY,
    Order_Date DATE NOT NULL,
    Ship_Date DATE NOT NULL,
    Ship_Mode NVARCHAR(50),
    Customer_ID NVARCHAR(50),
    Customer_Name NVARCHAR(100),
    Segment NVARCHAR(50),
    City NVARCHAR(100),
    State NVARCHAR(100),
    Country NVARCHAR(100),
    Postal_Code NVARCHAR(20),
    Market NVARCHAR(50),
    Region NVARCHAR(50) FOREIGN KEY REFERENCES Sales_Manager(Region),
    Product_ID NVARCHAR(50),
    Category NVARCHAR(50),
    Sub_Category NVARCHAR(50),
    Product_Name NVARCHAR(255),
    Sales DECIMAL(10,2),
    Quantity INT,
    Discount DECIMAL(4,2),
    Profit DECIMAL(10,2),
    Shipping_Cost DECIMAL(10,2),
    Order_Priority NVARCHAR(20)
);

CREATE TABLE Returns (
    Order_ID NVARCHAR(50) PRIMARY KEY FOREIGN KEY REFERENCES Orders(Order_ID),
    Returned BIT NOT NULL,
    Market NVARCHAR(50)
);
GO

-- 2. Populate Sales Manager Data

INSERT INTO Sales_Manager (Region, Person)
VALUES
('East', 'John Smith'),
('West', 'Sarah Johnson'),
('North', 'Mike Brown'),
('South', 'Emily Davis');
GO

-- 3. Populate Orders (Sample Data)

INSERT INTO Orders VALUES
('ORD001', '2023-01-05', '2023-01-07', 'Standard', 'CUST101', 'Alice Brown', 'Corporate', 'New York', 'NY', 'USA', '10001', 'US Market', 'East', 'PROD202', 'Technology', 'Phones', 'Smartphone X', 899.99, 2, 0.10, 150.00, 15.00, 'High'),
('ORD002', '2023-01-08', '2023-01-10', 'Express', 'CUST102', 'Bob Wilson', 'Consumer', 'Los Angeles', 'CA', 'USA', '90001', 'US Market', 'West', 'PROD305', 'Furniture', 'Chairs', 'Ergonomic Chair', 450.50, 1, 0.00, 90.00, 25.00, 'Medium'),
('ORD003', '2023-01-12', '2023-01-14', 'Standard', 'CUST103', 'Carol Taylor', 'Home Office', 'Chicago', 'IL', 'USA', '60007', 'US Market', 'North', 'PROD415', 'Office Supplies', 'Paper', 'Premium A4 Paper', 45.75, 5, 0.05, 10.00, 5.00, 'Low'),
('ORD004', '2023-01-15', '2023-01-17', 'Express', 'CUST104', 'David Lee', 'Corporate', 'Houston', 'TX', 'USA', '77001', 'US Market', 'South', 'PROD518', 'Technology', 'Laptops', 'Business Laptop Pro', 1499.99, 1, 0.15, 300.00, 35.00, 'High');
GO

-- 4. Populate Returns Data (Example)

INSERT INTO Returns VALUES
('ORD001', 1, 'US Market'),
('ORD004', 1, 'US Market');
GO