app/schemas/retail.sql

-- Product inventory table
CREATE TABLE inventory (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT,
    price REAL NOT NULL,
    stock INTEGER NOT NULL,
    reorder_level INTEGER DEFAULT 5
);

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

INSERT INTO inventory (product_id, product_name, category, price, stock, reorder_level) VALUES
(1, 'Laptop', 'Electronics', 1200.0, 5, 3),
(2, 'Phone', 'Electronics', 800.0, 20, 10),
(3, 'Tablet', 'Electronics', 600.0, 8, 5),
(4, 'Monitor', 'Accessories', 300.0, 15, 7),
(5, 'Keyboard', 'Accessories', 100.0, 30, 10),
(6, 'Mouse', 'Accessories', 50.0, 50, 20),
(7, 'Headphones', 'Accessories', 150.0, 25, 10),
(8, 'Webcam', 'Accessories', 250.0, 4, 5),
(9, 'Charger', 'Electronics', 60.0, 12, 8);

INSERT INTO restocks (restock_id, product_id, restock_date, quantity, supplier) VALUES
(1, 1, '2023-01-01', 10, 'TechWorld Supplies'),
(2, 2, '2023-01-02', 20, 'GadgetMart Inc.'),
(3, 3, '2022-12-15', 15, 'ElectroParts Ltd.'),
(4, 4, '2023-01-03', 30, 'DisplayX Pvt. Ltd.'),
(5, 5, '2023-01-10', 40, 'KeyTech Solutions'),
(6, 6, '2023-01-20', 50, 'Mousey Traders'),
(7, 7, '2023-02-01', 25, 'AudioGear Distributors'),
(8, 1, '2023-03-01', 5, 'TechWorld Supplies'),
(9, 8, '2023-03-15', 10, 'VisionTech Systems'),
(10, 9, '2023-04-01', 12, 'ElectroParts Ltd.');