insert_data.sql

-- ======================================================
-- SAMPLE DATA INSERTION FOR MSTOCK DATABASE
-- ======================================================

-- 1. Insert two users
INSERT INTO app_user (username, role, email, password, active) VALUES
('admin_user', 'ADMIN', 'admin@mstock.com', '$2a$10$dummyHashedPasswordForAdmin123', true),
('pharmacian_john', 'PHARMACIAN', 'john.pharmacian@mstock.com', '$2a$10$dummyHashedPasswordForPharmacian456', true);

-- Note: Passwords above are placeholders. In real usage, use bcrypt hashed values.

-- 2. Insert 5 products
INSERT INTO products (name, nsn_code, description, minimum_stock_level, active, version) VALUES
('Paracetamol 500mg', '6505-01-123-4567', 'Pain reliever and fever reducer', 100, true, 0),
('Amoxicillin 250mg', '6505-01-234-5678', 'Antibiotic for bacterial infections', 50, true, 0),
('Insulin Glargine 100U/mL', '6505-01-345-6789', 'Long-acting insulin for diabetes', 30, true, 0),
('N95 Respirator Mask', '6515-01-456-7890', 'Respiratory protection', 500, true, 0),
('Saline Solution 0.9% 1L', '6505-01-567-8901', 'IV fluid and wound irrigation', 20, true, 0);

-- 3. Insert 10 batches across the products
-- Each batch has unique lot number, location, quantity, expiration date, status ACTIVE
INSERT INTO batches (lot_number, quantity, expiration_date, location, status, product_id, version) VALUES
-- Batches for Paracetamol 500mg (product_id = 1)
('PAR-2025-001', 500, '2026-12-31 23:59:59', 'A-01-Shelf1', 'ACTIVE', 1, 0),
('PAR-2025-002', 300, '2026-10-15 23:59:59', 'A-01-Shelf2', 'ACTIVE', 1, 0),

-- Batches for Amoxicillin 250mg (product_id = 2)
('AMX-2025-001', 200, '2026-08-20 23:59:59', 'B-02-Cold1', 'ACTIVE', 2, 0),
('AMX-2025-002', 150, '2026-09-10 23:59:59', 'B-02-Cold2', 'ACTIVE', 2, 0),

-- Batches for Insulin Glargine (product_id = 3)
('INS-2025-001', 100, '2026-02-28 23:59:59', 'C-03-Fridge1', 'ACTIVE', 3, 0),
('INS-2025-002', 80,  '2026-03-15 23:59:59', 'C-03-Fridge2', 'ACTIVE', 3, 0),

-- Batches for N95 Respirator Mask (product_id = 4)
('N95-2025-001', 2000, '2027-01-01 23:59:59', 'D-04-Pallet1', 'ACTIVE', 4, 0),
('N95-2025-002', 1500, '2027-02-01 23:59:59', 'D-04-Pallet2', 'ACTIVE', 4, 0),
('N95-2025-003', 1000, '2027-03-01 23:59:59', 'D-04-Pallet3', 'ACTIVE', 4, 0),

-- Batch for Saline Solution (product_id = 5)
('SAL-2025-001', 60, '2026-11-30 23:59:59', 'E-05-Rack1', 'ACTIVE', 5, 0);

-- 4. Insert transactions (type = 'IN') to add the initial stock for each batch.
-- Using user_id 1 (admin_user) to perform the receiving. Timestamps auto-generated.
-- Provide a simple reason for each.
INSERT INTO transactions (type, quantity, reason, user_id, product_id, batch_id) VALUES
-- Paracetamol batches
('IN', 500, 'Initial stock receiving', 1, 1, 1),
('IN', 300, 'Initial stock receiving', 1, 1, 2),

-- Amoxicillin batches
('IN', 200, 'Initial stock receiving', 1, 2, 3),
('IN', 150, 'Initial stock receiving', 1, 2, 4),

-- Insulin batches
('IN', 100, 'Initial stock receiving', 1, 3, 5),
('IN', 80,  'Initial stock receiving', 1, 3, 6),

-- N95 batches
('IN', 2000, 'Initial stock receiving', 1, 4, 7),
('IN', 1500, 'Initial stock receiving', 1, 4, 8),
('IN', 1000, 'Initial stock receiving', 1, 4, 9),

-- Saline batch
('IN', 60, 'Initial stock receiving', 1, 5, 10);

-- ======================================================
-- END OF SAMPLE DATA SCRIPT
-- ======================================================