-- Create Database if not exists
CREATE DATABASE IF NOT EXISTS supply_chain_db;
USE supply_chain_db;

-- Drop tables in reverse order of foreign keys for safe recreation
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS customers;

-- 1. Departments Table
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

-- 2. Categories Table
CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL
);

-- 3. Customers Table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_fname VARCHAR(100),
    customer_lname VARCHAR(100),
    customer_segment VARCHAR(50),
    customer_street VARCHAR(255),
    customer_city VARCHAR(100),
    customer_state VARCHAR(50),
    customer_country VARCHAR(100),
    customer_zipcode VARCHAR(50)
);

-- 4. Products Table
CREATE TABLE products (
    product_card_id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    product_category_id INT,
    product_price DECIMAL(10, 2),
    product_status INT,
    FOREIGN KEY (product_category_id) REFERENCES categories(category_id)
);

-- 5. Orders Table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_customer_id INT,
    order_date DATETIME,
    shipping_date DATETIME,
    shipping_mode VARCHAR(50),
    days_for_shipping_real INT,
    days_for_shipping_scheduled INT,
    delivery_status VARCHAR(50),
    late_delivery_risk INT,
    order_status VARCHAR(50),
    type VARCHAR(50),
    market VARCHAR(50),
    order_region VARCHAR(100),
    order_country VARCHAR(100),
    order_state VARCHAR(100),
    order_city VARCHAR(100),
    order_zipcode VARCHAR(50),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    FOREIGN KEY (order_customer_id) REFERENCES customers(customer_id)
);

-- 6. Order Items Table
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    order_item_cardprod_id INT,
    order_item_quantity INT,
    order_item_product_price DECIMAL(10, 2),
    order_item_discount DECIMAL(10, 2),
    order_item_discount_rate DECIMAL(5, 4),
    order_item_total DECIMAL(10, 2), -- Net sales (after discount)
    sales DECIMAL(10, 2),            -- Gross sales (before discount)
    benefit_per_order DECIMAL(10, 2),-- Net profit
    order_item_profit_ratio DECIMAL(5, 4),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (order_item_cardprod_id) REFERENCES products(product_card_id)
);