app/schemas/inventory.sql

-- Inventory Table
CREATE TABLE inventory (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT NOT NULL,
    price REAL NOT NULL,
    stock INTEGER NOT NULL CHECK (stock >= 0)
);

-- Restocks Table
CREATE TABLE restocks (
    restock_id INTEGER PRIMARY KEY,
    product_id INTEGER,
    restock_date DATE NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    supplier TEXT,
    FOREIGN KEY (product_id) REFERENCES inventory(product_id)
);


INSERT INTO inventory (product_id, product_name, category, price, stock) VALUES
(1, 'Laptop', 'Electronics', 1200.00, 10),
(2, 'Smartphone', 'Electronics', 800.00, 15),
(3, 'Printer', 'Peripherals', 300.00, 5),
(4, 'Desk Chair', 'Furniture', 150.00, 20),
(5, 'Monitor', 'Peripherals', 400.00, 8),
(6, 'Mouse', 'Accessories', 40.00, 50),
(7, 'Keyboard', 'Accessories', 60.00, 30),
(8, 'Router', 'Networking', 90.00, 12),
(9, 'External HDD', 'Storage', 110.00, 7),
(10, 'USB-C Hub', 'Accessories', 25.00, 40);

INSERT INTO restocks (restock_id, product_id, restock_date, quantity, supplier) VALUES
(1, 1, '2023-01-01', 5, 'TechSupplier Inc.'),
(2, 2, '2023-01-05', 10, 'MobileMart Ltd.'),
(3, 3, '2023-01-10', 3, 'PrintPerfect'),
(4, 4, '2023-01-12', 15, 'OfficeFurnish Co.'),
(5, 5, '2023-01-15', 6, 'PeripheralWorks'),
(6, 6, '2023-01-17', 25, 'AccsGlobal'),
(7, 7, '2023-01-18', 10, 'AccsGlobal'),
(8, 8, '2023-01-20', 8, 'NetConnect'),
(9, 9, '2023-01-21', 5, 'StorageHouse'),
(10, 10, '2023-01-22', 20, 'TechSupplier Inc.'),
(11, 1, '2023-02-01', 3, 'TechSupplier Inc.'),
(12, 2, '2023-02-03', 7, 'MobileMart Ltd.');