schema.sql

-- ============================================================
-- CS348 Inventory Management System – Database Schema
-- ============================================================

-- Categories: organise products into logical groups
CREATE TABLE IF NOT EXISTS Categories (
    category_id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name          TEXT    NOT NULL UNIQUE,
    description   TEXT
);

-- Suppliers: vendors who supply products
CREATE TABLE IF NOT EXISTS Suppliers (
    supplier_id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name          TEXT    NOT NULL,
    contact_email TEXT,
    phone         TEXT,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products: main table (Requirement 1 – insert / update / delete)
CREATE TABLE IF NOT EXISTS Products (
    product_id        INTEGER PRIMARY KEY AUTOINCREMENT,
    name              TEXT    NOT NULL,
    sku               TEXT    NOT NULL UNIQUE,
    description       TEXT,
    unit_price        REAL    NOT NULL CHECK (unit_price >= 0),
    quantity_in_stock INTEGER NOT NULL DEFAULT 0 CHECK (quantity_in_stock >= 0),
    reorder_level     INTEGER NOT NULL DEFAULT 10,
    category_id       INTEGER,
    supplier_id       INTEGER,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES Categories (category_id) ON DELETE SET NULL,
    FOREIGN KEY (supplier_id) REFERENCES Suppliers  (supplier_id) ON DELETE SET NULL
);

-- ============================================================
-- Stage 3: Transaction Audit + Performance Indexes
-- ============================================================

-- Logs multi-step write transactions for Stage 3 discussion/demo.
CREATE TABLE IF NOT EXISTS InventoryTxLog (
    tx_id          INTEGER PRIMARY KEY AUTOINCREMENT,
    tx_type        TEXT    NOT NULL,
    notes          TEXT,
    affected_rows  INTEGER NOT NULL DEFAULT 0,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes that support frequent filters/reports and list ordering.
-- 1) Report filters by category and price range.
CREATE INDEX IF NOT EXISTS idx_products_category_price
    ON Products (category_id, unit_price);

-- 2) Report filters by supplier and price range.
CREATE INDEX IF NOT EXISTS idx_products_supplier_price
    ON Products (supplier_id, unit_price);

-- 3) Stock alert/report queries use quantity and reorder comparisons.
CREATE INDEX IF NOT EXISTS idx_products_stock_levels
    ON Products (quantity_in_stock, reorder_level);

-- 4) Product list/report sort by name.
CREATE INDEX IF NOT EXISTS idx_products_name
    ON Products (name);

-- ============================================================
-- Sample Data
-- ============================================================

INSERT OR IGNORE INTO Categories (name, description) VALUES
    ('Electronics',      'Electronic devices, peripherals, and accessories'),
    ('Office Supplies',  'Stationery, paper, and everyday office consumables'),
    ('Tools & Equipment','Hand tools, power tools, and maintenance equipment'),
    ('Furniture',        'Desks, chairs, storage, and workspace fixtures'),
    ('Safety',           'Personal protective equipment and first-aid supplies');

INSERT OR IGNORE INTO Suppliers (name, contact_email, phone) VALUES
    ('TechNova Distributors', 'orders@technova.com',       '555-0101'),
    ('OfficeMax Pro',         'supply@officemaxpro.com',   '555-0202'),
    ('ProTools Inc.',         'sales@protools.com',         '555-0303'),
    ('WorkSpace Direct',      'orders@workspacedirect.com', '555-0404'),
    ('SafeGuard Supply Co.',  'contact@safeguard.com',      '555-0505');

INSERT OR IGNORE INTO Products
    (name, sku, description, unit_price, quantity_in_stock, reorder_level, category_id, supplier_id)
VALUES
    -- Electronics (cat 1 / sup 1) -----------------------------------------
    ('Dell 27" Monitor',         'ELEC-MON-001', 'Full HD IPS display with adjustable stand',          329.99, 45,  10, 1, 1),
    ('Wireless Keyboard',        'ELEC-KEY-002', 'Bluetooth keyboard with ergonomic layout',            79.99,  8,  15, 1, 1),
    ('USB-C Hub 7-in-1',         'ELEC-HUB-003', 'HDMI, USB 3.0, SD card reader, PD charging',         49.99,  0,  20, 1, 1),
    ('Logitech MX Master Mouse', 'ELEC-MOU-004', 'Advanced wireless mouse with precision scroll',       99.99, 32,  10, 1, 1),
    ('Noise Cancelling Headset', 'ELEC-HEAD-005','USB headset with boom mic, ideal for calls',         149.99, 25,   8, 1, 1),
    -- Office Supplies (cat 2 / sup 2) ----------------------------------------
    ('Printer Paper A4 (500pk)', 'OFFS-PAP-001', '80 gsm white copy paper, ream of 500 sheets',        12.99, 120,  50, 2, 2),
    ('Ballpoint Pens (Box 50)',   'OFFS-PEN-002', 'Medium-point blue ink pens, box of 50',              18.99,  5,  20, 2, 2),
    ('Heavy Duty Stapler',        'OFFS-STA-003', 'Staples up to 50 sheets, includes staple remover',   34.99, 22,   8, 2, 2),
    ('Sticky Notes 3×3 (12pk)',  'OFFS-STK-004', 'Assorted neon colours, 100 sheets per pad',          14.99,  0,  30, 2, 2),
    ('Whiteboard Markers (8pk)', 'OFFS-MRK-005', 'Dry-erase, assorted colours, chisel tip',             9.99, 48,  15, 2, 2),
    -- Tools & Equipment (cat 3 / sup 3) --------------------------------------
    ('Cordless Drill 18V',        'TOOL-DRL-001', '2-speed drill, 2× batteries + charger included',    159.99, 18,   5, 3, 3),
    ('Tape Measure 25 ft',        'TOOL-TAP-002', 'Heavy-duty with magnetic tip and belt clip',         24.99, 40,  10, 3, 3),
    ('Adjustable Wrench Set',     'TOOL-WRN-003', 'Set of 3 chrome-vanadium adjustable wrenches',       44.99, 14,   8, 3, 3),
    ('Extension Cord 25 ft',      'TOOL-EXT-004', '3-outlet indoor/outdoor heavy-duty cord',            29.99,  6,  10, 3, 3),
    -- Furniture (cat 4 / sup 4) -----------------------------------------------
    ('Ergonomic Office Chair',    'FURN-CHR-001', 'Lumbar support, adjustable height and armrests',    449.99, 12,   3, 4, 4),
    ('Electric Standing Desk',    'FURN-DSK-002', '60×30 in, dual motor height-adjustable',            699.99,  1,   2, 4, 4),
    ('3-Drawer Mobile Pedestal',  'FURN-PED-003', 'Lockable steel unit on smooth-rolling casters',     199.99,  9,   4, 4, 4),
    ('Magnetic Whiteboard 4×3',   'FURN-WBD-004', 'Aluminium-framed dry-erase board with marker tray',129.99,  0,   3, 4, 4),
    -- Safety (cat 5 / sup 5) -------------------------------------------------
    ('Hi-Vis Safety Vest',        'SAFE-VST-001', 'ANSI Class 2 reflective vest, sizes S–XXL',          22.99, 60,  20, 5, 5),
    ('Safety Glasses (Clear)',    'SAFE-GLS-002', 'Anti-scratch polycarbonate lens, wraparound frame',   8.99,  7,  25, 5, 5),
    ('Safety Hard Hat',           'SAFE-HAT-003', 'Type I Class E hard hat, adjustable suspension',     29.99,  3,  15, 5, 5),
    ('First Aid Kit (50-person)', 'SAFE-FAK-004', 'OSHA-compliant kit in wall-mountable cabinet',        89.99,  4,   5, 5, 5);