USE pmis;

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE notifications;
TRUNCATE TABLE quality_inspections;
TRUNCATE TABLE product_movements;
TRUNCATE TABLE products;
TRUNCATE TABLE production_orders;
TRUNCATE TABLE users;
SET FOREIGN_KEY_CHECKS = 1;

INSERT INTO users (full_name, username, password_hash, role, is_active) VALUES
('System Admin', 'admin', '$2y$10$fA6/0yvE3NrCd0KUQwLol.38AEt/Zv9XZbtttBKMyCGUIhRtGh5uW', 'Administrator', 1),
('Ama Serwaa', 'aserwaa', '$2y$10$fKHwpS/eCDyDBszSV8/haOJcnuZ0peT46Q82fDFs/h3rW4zQrBv8u', 'Production Supervisor', 1),
('Kojo Mensah', 'kmensah', '$2y$10$5rEYW3jKYWKzlsVl/.SqT.TzitWsoIFWTyx7RA7.CvfZAoih7DAOm', 'Production Supervisor', 1),
('Inspector Afia', 'aafia', '$2y$10$OBJFeWFDgwmHPfkWMz48q.DVvEn44ZsV81UqR9OhDUH.tW/t5N4M6', 'Quality Control Officer', 1)
ON DUPLICATE KEY UPDATE
  full_name = VALUES(full_name),
  password_hash = VALUES(password_hash),
  role = VALUES(role),
  is_active = VALUES(is_active);

INSERT INTO production_orders (
  order_number, project_name, client_name, product_type, quantity,
  start_date, expected_completion_date, status, created_by
) VALUES
('PO-2026-001', 'Hotel Door Set A', 'J.O. Agyapong Homes', 'Wooden Door', 24, '2026-06-03', '2026-06-20', 'In Production', 1),
('PO-2026-002', 'Office Cabinet Run', 'Madoors Systems', 'Cabinet', 12, '2026-06-01', '2026-06-14', 'Pending', 1),
('PO-2026-003', 'Executive Suite Doors', 'Triovarc Global', 'Fire Door', 18, '2026-05-26', '2026-06-10', 'Completed', 1)
ON DUPLICATE KEY UPDATE project_name = VALUES(project_name);

INSERT INTO production_stages (stage_name, sort_order, is_active) VALUES
('Material Preparation', 1, 1),
('Cutting', 2, 1),
('Assembly', 3, 1),
('Sanding', 4, 1),
('Finishing/Painting', 5, 1),
('Quality Control', 6, 1),
('Packaging', 7, 1),
('Dispatch', 8, 1)
ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order), is_active = VALUES(is_active);

INSERT INTO notification_settings (id, sms_enabled, email_enabled) VALUES
(1, 0, 0)
ON DUPLICATE KEY UPDATE sms_enabled = VALUES(sms_enabled), email_enabled = VALUES(email_enabled);

INSERT INTO products (
  product_number, order_id, product_type, current_stage, status, completion_percent,
  last_scan_at, assigned_user
) VALUES
('D001', (SELECT id FROM production_orders WHERE order_number = 'PO-2026-001' LIMIT 1), 'Wooden Door', 'Finishing/Painting', 'In Progress', 0.68, '2026-06-08 09:42:00', 'Kojo Mensah'),
('D002', (SELECT id FROM production_orders WHERE order_number = 'PO-2026-001' LIMIT 1), 'Wooden Door', 'Quality Control', 'Awaiting QC', 0.92, '2026-06-08 12:18:00', 'Nana Addo'),
('D003', (SELECT id FROM production_orders WHERE order_number = 'PO-2026-003' LIMIT 1), 'Fire Door', 'Dispatch', 'Ready for dispatch', 1.00, '2026-06-07 16:54:00', 'Yaw Boateng')
ON DUPLICATE KEY UPDATE current_stage = VALUES(current_stage);

INSERT INTO product_movements (product_id, stage, action, remarks, moved_by, moved_at) VALUES
((SELECT id FROM products WHERE product_number = 'D001' LIMIT 1), 'Material Preparation', 'Entered stage', 'Materials confirmed', 'Ama Serwaa', '2026-06-04 08:15:00'),
((SELECT id FROM products WHERE product_number = 'D001' LIMIT 1), 'Cutting', 'Stage completed', 'Cut verified', 'Kojo Mensah', '2026-06-05 11:35:00'),
((SELECT id FROM products WHERE product_number = 'D001' LIMIT 1), 'Assembly', 'Transferred', 'Moved to assembly bay', 'Kojo Mensah', '2026-06-06 15:10:00'),
((SELECT id FROM products WHERE product_number = 'D002' LIMIT 1), 'Material Preparation', 'Entered stage', 'Materials confirmed', 'Ama Serwaa', '2026-06-04 08:20:00'),
((SELECT id FROM products WHERE product_number = 'D002' LIMIT 1), 'Finishing/Painting', 'Stage completed', 'Paint dried and checked', 'Nana Addo', '2026-06-08 08:40:00'),
((SELECT id FROM products WHERE product_number = 'D003' LIMIT 1), 'Quality Control', 'Approved', 'Passed all checks', 'Inspector Afia', '2026-06-07 14:10:00'),
((SELECT id FROM products WHERE product_number = 'D003' LIMIT 1), 'Packaging', 'Completed', 'Packed and labeled', 'Yaw Boateng', '2026-06-07 15:35:00'),
((SELECT id FROM products WHERE product_number = 'D003' LIMIT 1), 'Dispatch', 'Queued', 'Awaiting truck assignment', 'Yaw Boateng', '2026-06-07 16:54:00')
ON DUPLICATE KEY UPDATE remarks = VALUES(remarks);

INSERT INTO quality_inspections (product_id, inspection_result, inspector, remarks, inspected_at) VALUES
((SELECT id FROM products WHERE product_number = 'D002' LIMIT 1), 'Pass', 'Inspector Afia', 'All checks passed', '2026-06-08 12:30:00'),
((SELECT id FROM products WHERE product_number = 'D003' LIMIT 1), 'Pass', 'Inspector Afia', 'Approved for dispatch', '2026-06-07 14:15:00')
ON DUPLICATE KEY UPDATE remarks = VALUES(remarks);

INSERT INTO notifications (recipient_role, title, message, channel, is_read) VALUES
('Production Manager', 'Order delayed', 'PO-2026-002 has not started yet.', 'in_app', 0),
('Production Supervisor', 'Product moved', 'D002 is ready for QC.', 'in_app', 0),
('Management', 'Production complete', 'PO-2026-003 has been completed.', 'in_app', 1);