CREATE TABLE IF NOT EXISTS products (
    product_id int PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(20) NOT NULL,
    price DOUBLE NOT NULL,
    stock_quantity INT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(20) NOT NULL,
    phone VARCHAR(11)
);


CREATE TABLE IF NOT EXISTS kpi (
    kid INT PRIMARY KEY AUTO_INCREMENT,
    grade INT CHECK(grade >= 0),
    sid INT,
    FOREIGN KEY (sid) REFERENCES sales_people(salesperson_id)
);

CREATE TABLE IF NOT EXISTS sales_people (
    salesperson_id INT PRIMARY KEY AUTO_INCREMENT,
    saleperson_name VARCHAR(20) NOT NULL,
    phone VARCHAR(11)
);


CREATE TABLE IF NOT EXISTS suppliers (
    supplier_id INT PRIMARY KEY AUTO_INCREMENT,
    company_name VARCHAR(20) NOT NULL,
    phone VARCHAR(11)
);


CREATE TABLE IF NOT EXISTS warehouses (
    warehouse_id INT PRIMARY KEY AUTO_INCREMENT,
    location VARCHAR(20) NOT NULL,
    capacity INT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS sales_orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT,
    salesperson_id INT,
    total_amount int NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (salesperson_id) REFERENCES sales_people(salesperson_id)
);

CREATE TABLE IF NOT EXISTS order_details (
    order_detail_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    unit_price DOUBLE NOT NULL,
    FOREIGN KEY (order_id) REFERENCES sales_orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE IF NOT EXISTS returns (
    return_id INT PRIMARY KEY AUTO_INCREMENT,
    return_date DATE NOT NULL,
    order_id INT,
    reason varchar(30),
    FOREIGN KEY (order_id) REFERENCES sales_orders(order_id)
);

CREATE TABLE IF NOT EXISTS product_suppliers (
    product_supplier_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    supplier_id INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);

CREATE TABLE IF NOT EXISTS product_warehouses (
    product_warehouse_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    warehouse_id INT,
    quantity_in_warehouse INT DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
);

CREATE TABLE IF NOT EXISTS sales_activities (
    activity_id INT PRIMARY KEY AUTO_INCREMENT,
    activity_name VARCHAR(20) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_sales_amount double DEFAULT 0
);

CREATE TABLE IF NOT EXISTS shipping_methods (
    shipping_method_id INT PRIMARY KEY AUTO_INCREMENT,
    methodname VARCHAR(20) NOT NULL,
    cost_per_unit double NOT NULL CHECK (cost_per_unit >= 0),
    estimated_delivery_time INT NOT NULL CHECK (estimated_delivery_time > 0),
    order_id INT,
    FOREIGN KEY (order_id) REFERENCES sales_orders(order_id)
);