-- Enable foreign key support (for SQLite)
PRAGMA foreign_keys = ON;

-- 1. Products Table
CREATE TABLE IF NOT EXISTS Products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    reorder_level INTEGER DEFAULT 10
);

-- 2. Suppliers Table
CREATE TABLE IF NOT EXISTS Suppliers (
    supplier_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    contact TEXT
);

-- 3. InventoryBatches Table (Tracks price lots for FIFO/LIFO logic)
CREATE TABLE IF NOT EXISTS InventoryBatches (
    batch_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER NOT NULL,
    supplier_id INTEGER,
    quantity INTEGER NOT NULL CHECK (quantity >= 0),
    unit_price REAL NOT NULL CHECK (unit_price > 0),
    date_added TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES Products(product_id),
    FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id)
);

-- 4. StockTransactions Table (Audit log for COGS & Stock movements)
CREATE TABLE IF NOT EXISTS StockTransactions (
    transaction_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER NOT NULL,
    trans_type TEXT NOT NULL CHECK(trans_type IN ('IN', 'OUT')),
    quantity INTEGER NOT NULL,
    cogs REAL DEFAULT 0.0,
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
-- Add a Product
INSERT INTO Products (name, reorder_level) VALUES ('Laptops', 5);

-- Add a Supplier
INSERT INTO Suppliers (name, contact) VALUES ('Tech Distributors Ltd', 'supplier@tech.com');

-- Add Batch 1: 10 units @ $500 each
INSERT INTO InventoryBatches (product_id, supplier_id, quantity, unit_price) 
VALUES (1, 1, 10, 500.00);

-- Add Batch 2: 5 units @ $550 each (Price increased)
INSERT INTO InventoryBatches (product_id, supplier_id, quantity, unit_price) 
VALUES (1, 1, 5, 550.00);

-- Log Stock In Transaction
INSERT INTO StockTransactions (product_id, trans_type, quantity, cogs) 
VALUES (1, 'IN', 15, 0.00);
-- Query total stock units and total inventory value for a product
SELECT 
    p.product_id,
    p.name,
    SUM(b.quantity) AS total_units,
    SUM(b.quantity * b.unit_price) AS total_closing_value,
    AVG(b.unit_price) AS average_cost_per_unit
FROM Products p
LEFT JOIN InventoryBatches b ON p.product_id = b.product_id
WHERE b.quantity > 0
GROUP BY p.product_id;