schema.sql

-- =====================================================================
-- Inventory and Supply Chain Dashboard — Database Schema
-- Database: project_py_sql
-- =====================================================================
-- Defines the tables, view, and stored procedures the app depends on.
-- Run this against a fresh MySQL database before running main.py.
-- =====================================================================


-- ---------------------------------------------------------------------
-- TABLES
-- ---------------------------------------------------------------------
-- TODO: run the following in your MySQL client and paste each result
-- below, so this file can fully recreate the database from scratch:
--
--   SHOW CREATE TABLE suppliers;
--   SHOW CREATE TABLE products;
--   SHOW CREATE TABLE shipments;
--   SHOW CREATE TABLE stock_entries;
--   SHOW CREATE TABLE reorders;


-- ---------------------------------------------------------------------
-- VIEW: product_inventory_history
-- Unifies shipments (incoming stock) and stock_entries (sales/restocks)
-- into a single chronological timeline per product.
-- Used by: db_functions.py -> get_product_history()
-- ---------------------------------------------------------------------
CREATE OR REPLACE VIEW product_inventory_history AS
SELECT
    pih.product_id,
    pih.record_type,
    pih.record_date,
    pih.quantity,
    pih.change_type,
    pr.supplier_id
FROM (
    SELECT
        product_id,
        'Shipment'        AS record_type,
        shipment_date     AS record_date,
        quantity_received AS quantity,
        NULL              AS change_type
    FROM shipments

    UNION ALL

    SELECT
        product_id,
        'Stock Entry'   AS record_type,
        entry_date      AS record_date,
        change_quantity AS quantity,
        change_type
    FROM stock_entries
) pih
JOIN products pr ON pr.product_id = pih.product_id;


-- ---------------------------------------------------------------------
-- PROCEDURE: AddNewProductManualID
-- Adds a new product, then mirrors the initial stock as both an
-- incoming shipment and a stock entry — so a brand-new product shows
-- up correctly everywhere from the moment it's created.
-- Used by: db_functions.py -> add_new_manual_id()
-- ---------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE AddNewProductManualID(
    IN p_name     VARCHAR(255),
    IN p_category VARCHAR(255),
    IN p_price    DECIMAL(10,2),
    IN p_stock    INT,
    IN p_reorder  INT,
    IN p_supplier INT
)
BEGIN
    DECLARE new_product_id  INT;
    DECLARE new_shipment_id INT;
    DECLARE new_entry_id    INT;

    -- 1. Insert the new product
    SELECT MAX(product_id) + 1 INTO new_product_id FROM products;

    INSERT INTO products (
        product_id, product_name, category, price,
        stock_quantity, reorder_level, supplier_id
    )
    VALUES (
        new_product_id, p_name, p_category, p_price,
        p_stock, p_reorder, p_supplier
    );

    -- 2. Log the initial stock as an incoming shipment
    SELECT MAX(shipment_id) + 1 INTO new_shipment_id FROM shipments;

    INSERT INTO shipments (
        shipment_id, product_id, supplier_id,
        quantity_received, shipment_date
    )
    VALUES (
        new_shipment_id, new_product_id, p_supplier,
        p_stock, CURRENT_DATE()
    );

    -- 3. Log the initial stock as a restock entry
    SELECT MAX(entry_id) + 1 INTO new_entry_id FROM stock_entries;

    INSERT INTO stock_entries (
        entry_id, product_id, change_quantity,
        change_type, entry_date
    )
    VALUES (
        new_entry_id, new_product_id, p_stock,
        'Restock', CURRENT_DATE()
    );

END $$

DELIMITER ;


-- ---------------------------------------------------------------------
-- PROCEDURE: MarkreorderAsRecieved
-- Marks a pending reorder as received, restocks the product, and logs
-- the restock as both a shipment and a stock entry. Wrapped in a
-- transaction so a failure partway through rolls back all four table
-- changes together instead of leaving data half-updated.
-- Used by: db_functions.py -> mark_reorder_as_received()
--
-- NOTE: this procedure inserts into a "shimpment_date" column, while
-- AddNewProductManualID above uses "shipment_date". Confirm which one
-- actually exists on your shipments table before running this.
-- ---------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE MarkreorderAsRecieved(
    IN in_reorder_id INT
)
BEGIN
    DECLARE prod_id         INT;
    DECLARE qty             INT;
    DECLARE sup_id          INT;
    DECLARE new_shipment_id INT;
    DECLARE new_entry_id    INT;

    START TRANSACTION;

    -- 1. Look up the product and quantity on this reorder
    SELECT product_id, reorder_quantity
    INTO prod_id, qty
    FROM reorders
    WHERE reorder_id = in_reorder_id;

    -- 2. Look up which supplier fulfilled it
    SELECT supplier_id
    INTO sup_id
    FROM products
    WHERE product_id = prod_id;

    -- 3. Mark the reorder as received
    UPDATE reorders
    SET status = 'Recieved'
    WHERE reorder_id = in_reorder_id;

    -- 4. Add the received quantity back into stock
    UPDATE products
    SET stock_quantity = stock_quantity + qty
    WHERE product_id = prod_id;

    -- 5. Log the restock as an incoming shipment
    SELECT MAX(shipment_id) + 1 INTO new_shipment_id FROM shipments;

    INSERT INTO shipments (
        shipment_id, product_id, supplier_id,
        quantity_received, shimpment_date
    )
    VALUES (
        new_shipment_id, prod_id, sup_id,
        qty, CURRENT_DATE()
    );

    -- 6. Log the restock as a stock entry
    SELECT MAX(entry_id) + 1 INTO new_entry_id FROM stock_entries;

    INSERT INTO stock_entries (
        entry_id, product_id, change_quantity,
        change_type, entry_date
    )
    VALUES (
        new_entry_id, prod_id, qty,
        'Restock', CURRENT_DATE()
    );

    COMMIT;
END $$

DELIMITER ;