queries.sql

-- =====================================================================
-- Queries used directly by db_functions.py
-- Inventory and Supply Chain Dashboard
--
-- Table/view definitions and stored procedure bodies: see schema.sql.
-- Only the CALL statements for the two procedures are shown here.
-- =====================================================================


-- ---------------------------------------------------------------------
-- get_basic_info()  ->  the 6 metric cards on the Basic Information page
-- ---------------------------------------------------------------------

-- Total Suppliers
SELECT COUNT(*) AS count FROM suppliers;

-- Total Products
SELECT COUNT(*) AS count FROM products;

-- Total Categories Dealing
SELECT COUNT(DISTINCT category) AS count FROM products;

-- Total Sale Value (Last 3 Months)
SELECT COALESCE(
           ROUND(SUM(ABS(se.change_quantity) * p.price), 2),
           0
       ) AS total_sale
FROM stock_entries se
JOIN products p ON se.product_id = p.product_id
WHERE se.change_type = 'Sale'
  AND se.entry_date >= (
        SELECT DATE_SUB(MAX(entry_date), INTERVAL 3 MONTH)
        FROM stock_entries
      );

-- Total Restock Value (Last 3 Months)
SELECT ROUND(SUM(se.change_quantity * p.price), 2) AS total_restock
FROM stock_entries se
JOIN products p ON se.product_id = p.product_id
WHERE se.change_type = 'Restock'
  AND se.entry_date >= (
        SELECT DATE_SUB(MAX(entry_date), INTERVAL 3 MONTH)
        FROM stock_entries
      );

-- Below Reorder Level & No Pending Reorder
SELECT COUNT(*) AS below_reorder
FROM products p
WHERE p.stock_quantity < p.reorder_level
  AND p.product_id NOT IN (
        SELECT DISTINCT product_id
        FROM reorders
        WHERE status = 'Pending'
      );


-- ---------------------------------------------------------------------
-- get_additional_tables()  ->  the 3 detail tables on the Basic
-- Information page
-- ---------------------------------------------------------------------

-- Suppliers Contact Details
SELECT supplier_name, contact_name, email, phone
FROM suppliers;

-- Products with Supplier and Stock
SELECT
    p.product_name,
    s.supplier_name,
    p.stock_quantity,
    p.reorder_level
FROM products p
JOIN suppliers s ON p.supplier_id = s.supplier_id
ORDER BY p.product_name ASC;

-- Products Needing Reorder
SELECT product_name, stock_quantity, reorder_level
FROM products
WHERE stock_quantity <= reorder_level;


-- ---------------------------------------------------------------------
-- Dropdown helpers: get_categories(), get_suppliers(), get_all_products()
-- ---------------------------------------------------------------------

SELECT DISTINCT category FROM products ORDER BY category ASC;

SELECT supplier_id, supplier_name FROM suppliers ORDER BY supplier_name ASC;

SELECT product_id, product_name FROM products ORDER BY product_name ASC;


-- ---------------------------------------------------------------------
-- get_product_history(product_id)  ->  Product History task
-- Reads from the product_inventory_history view (see schema.sql)
-- ---------------------------------------------------------------------
SELECT *
FROM product_inventory_history
WHERE product_id = %s
ORDER BY record_date DESC;


-- ---------------------------------------------------------------------
-- place_reorder(product_id, reorder_quantity)  ->  Place Reorder task
-- ---------------------------------------------------------------------
INSERT INTO reorders (reorder_id, product_id, reorder_quantity, reorder_date, status)
SELECT
    MAX(reorder_id) + 1,
    %s,
    %s,
    CURRENT_DATE(),
    'Ordered'
FROM reorders;


-- ---------------------------------------------------------------------
-- get_pending_reorders()  ->  dropdown on the Receive Reorder task
-- ---------------------------------------------------------------------
SELECT r.reorder_id, p.product_name
FROM reorders r
JOIN products p ON r.product_id = p.product_id;


-- ---------------------------------------------------------------------
-- add_new_manual_id(...)  ->  Add New Product task
-- mark_reorder_as_received(reorder_id)  ->  Receive Reorder task
-- Full logic for both lives in the stored procedures — see schema.sql
-- ---------------------------------------------------------------------
CALL AddNewProductManualID(%s, %s, %s, %s, %s, %s);

CALL MarkreorderAsRecieved(%s);