your welcome adam

-- =========================================
-- 1. STAGING TABLE FOR CSV IMPORT
-- =========================================

CREATE TABLE staging_eco_csv (
    product_id TEXT,
    product_name TEXT,
    category TEXT,
    price TEXT,
    quantity_in_stock TEXT,
    supplier_id TEXT,
    supplier_name TEXT,
    supplier_contact TEXT,
    order_id TEXT,
    order_date TEXT,
    customer_id TEXT,
    customer_name TEXT,
    customer_email TEXT,
    order_total TEXT
);

-- =========================================
-- 2. FINAL TABLES
-- =========================================

CREATE TABLE Suppliers (
    supplier_id INT PRIMARY KEY,
    supplier_name VARCHAR(255),
    supplier_contact VARCHAR(255)
);

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    category VARCHAR(255),
    price NUMERIC(10,2),
    quantity_in_stock INT,
    supplier_id INT,
    CONSTRAINT fk_products_supplier
        FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id)
);

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    customer_email VARCHAR(255)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    order_total NUMERIC(10,2),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE OrderDetails (
    order_id INT,
    product_id INT,
    quantity_ordered INT,
    line_total NUMERIC(10,2),
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_orderdetails_order
        FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    CONSTRAINT fk_orderdetails_product
        FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

-- =========================================
-- 3. IMPORT CSV INTO STAGING TABLE
-- =========================================

-- Change the file path to your actual CSV file location
-- Run this in psql:
-- \copy staging_eco_csv FROM 'C:/path/to/your/file.csv' DELIMITER ',' CSV HEADER;

-- =========================================
-- 4. LOAD DATA INTO FINAL TABLES
-- =========================================

-- Suppliers
INSERT INTO Suppliers (supplier_id, supplier_name, supplier_contact)
SELECT DISTINCT
    supplier_id::INT,
    supplier_name,
    supplier_contact
FROM staging_eco_csv
WHERE supplier_id IS NOT NULL
  AND supplier_id <> '';

-- Products
INSERT INTO Products (product_id, product_name, category, price, quantity_in_stock, supplier_id)
SELECT DISTINCT
    product_id::INT,
    product_name,
    category,
    price::NUMERIC(10,2),
    quantity_in_stock::INT,
    supplier_id::INT
FROM staging_eco_csv
WHERE product_id IS NOT NULL
  AND product_id <> '';

-- Customers
INSERT INTO Customers (customer_id, customer_name, customer_email)
SELECT DISTINCT
    customer_id::INT,
    customer_name,
    customer_email
FROM staging_eco_csv
WHERE customer_id IS NOT NULL
  AND customer_id <> '';

-- Orders
INSERT INTO Orders (order_id, order_date, customer_id, order_total)
SELECT DISTINCT
    order_id::INT,
    TO_DATE(order_date, 'MM/DD/YYYY'),
    customer_id::INT,
    order_total::NUMERIC(10,2)
FROM staging_eco_csv
WHERE order_id IS NOT NULL
  AND order_id <> '';

-- OrderDetails
-- Since the CSV fields listed do not show quantity_ordered or line_total,
-- this version inserts NULL values for those columns.
INSERT INTO OrderDetails (order_id, product_id, quantity_ordered, line_total)
SELECT DISTINCT
    order_id::INT,
    product_id::INT,
    NULL,
    NULL
FROM staging_eco_csv
WHERE order_id IS NOT NULL
  AND order_id <> ''
  AND product_id IS NOT NULL
  AND product_id <> '';