-- Migration: Add inspector_id to supplier_audits + seed inspector users
-- FCQC — Supplier Audit Inspector Assignment

-- 1. Add inspector_id column to supplier_audits
ALTER TABLE supplier_audits
  ADD COLUMN inspector_id INT UNSIGNED NULL AFTER auditor_name,
  ADD INDEX idx_inspector_id (inspector_id);

-- 2. Create inspector users (password: Inspector@123)
-- Hash generated via PHP: password_hash('Inspector@123', PASSWORD_DEFAULT)
SET @inspector_hash = '$2y$10$iOJVgRS9Lf22Rk/Oit2Sge1fK.TSt8SqgrlgXen3HBUl0XoJJ1B.C';

INSERT INTO users (company_id, name, email, password_hash, role_id, status, portal_enabled, employee_code)
VALUES
  (1, 'Rafiq Hasan',   'inspector1@fcqc.com', @inspector_hash, 3, 'active', 0, 'INS-001'),
  (1, 'Fatima Begum',  'inspector2@fcqc.com', @inspector_hash, 3, 'active', 0, 'INS-002'),
  (1, 'Kamal Hossain', 'inspector3@fcqc.com', @inspector_hash, 3, 'active', 0, 'INS-003')
ON DUPLICATE KEY UPDATE role_id = 3, status = 'active';

-- 3. Grant quality.inspect permission to quality_inspector role if not already
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 3, id FROM permissions WHERE code IN ('quality.view', 'quality.inspect', 'quality.edit');
