-- Add Inventory Reports Table for Monthly Variance Tracking
-- Compatible with existing items table (Item_Code VARCHAR(50))

USE invcount_db;

-- Drop if exists (development only)
-- DROP TABLE IF EXISTS inventory_reports;

CREATE TABLE inventory_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    report_month DATE NOT NULL, -- Stored as YYYY-MM-01
    item_code VARCHAR(50),
    opening_qty DECIMAL(15, 3) DEFAULT 0,
    receipts_qty DECIMAL(15, 3) DEFAULT 0,
    issues_qty DECIMAL(15, 3) DEFAULT 0,
    system_qty DECIMAL(15, 3) GENERATED ALWAYS AS (opening_qty + receipts_qty - issues_qty) VIRTUAL,
    count_qty DECIMAL(15, 3) DEFAULT 0,
    variance_qty DECIMAL(15, 3) GENERATED ALWAYS AS (count_qty - (opening_qty + receipts_qty - issues_qty)) VIRTUAL,
    variance_val DECIMAL(15, 2) DEFAULT 0, 
    loss_nature VARCHAR(100),
    comment TEXT,
    action_taken TEXT,
    responsible_person VARCHAR(100),
    risk_rating ENUM('Low', 'Medium', 'High', 'Critical'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_code) REFERENCES items(Item_Code),
    INDEX idx_report_month (report_month),
    INDEX idx_item_code (item_code)
);

-- Sample Queries as Views

CREATE OR REPLACE VIEW variance_summary AS
SELECT 
    r.report_month,
    r.item_code,
    i.Item_Description as item_desc,
    i.Uom as uom,
    r.opening_qty,
    r.receipts_qty,
    r.issues_qty,
    r.system_qty,
    r.count_qty,
    r.variance_qty,
    r.variance_val,
    CASE 
        WHEN r.system_qty != 0 THEN (r.variance_qty / r.system_qty) * 100 
        ELSE 0 
    END AS variance_percentage,
    0.5 as expected_loss_pct,
    r.risk_rating,
    r.responsible_person
FROM inventory_reports r
JOIN items i ON r.item_code = i.Item_Code;

CREATE OR REPLACE VIEW high_risk_exceptions AS
SELECT 
    item_code, 
    system_qty, 
    count_qty, 
    variance_qty, 
    risk_rating, 
    comment, 
    action_taken
FROM inventory_reports
WHERE risk_rating IN ('High', 'Critical');
