BEGIN;
CREATE TABLE IF NOT EXISTS hr_payroll.onboarding_asset_templates (
 id BIGSERIAL PRIMARY KEY, tenant_id UUID NOT NULL, template_no VARCHAR(80) NOT NULL,
 template_name VARCHAR(160) NOT NULL, description TEXT NULL, items JSONB NOT NULL DEFAULT '[]'::jsonb,
 eligible_role_ids JSONB NOT NULL DEFAULT '[]'::jsonb, eligible_department_ids JSONB NOT NULL DEFAULT '[]'::jsonb,
 approval_required BOOLEAN NOT NULL DEFAULT TRUE, effective_from DATE NULL, effective_to DATE NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'active', is_active BOOLEAN NOT NULL DEFAULT TRUE,
 is_deleted BOOLEAN NOT NULL DEFAULT FALSE, created_by UUID NULL, updated_by UUID NULL, deleted_by UUID NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at TIMESTAMP NULL, UNIQUE (tenant_id, template_no)
);
CREATE INDEX IF NOT EXISTS idx_onboarding_asset_templates_active ON hr_payroll.onboarding_asset_templates
 (tenant_id,status,effective_from,effective_to) WHERE is_deleted=FALSE;
WITH p(module_code,permission_code,permission_name,description) AS (VALUES
 ('hr-payroll','hr.onboarding.assets.approve','Approve Onboarding Assets','Approve or reject submitted onboarding asset requests.'),
 ('hr-payroll','hr.onboarding.assets.allocate','Allocate Onboarding Assets','Allocate approved inventory assets and record serial evidence.'),
 ('hr-payroll','hr.onboarding.assets.deliver','Deliver Onboarding Assets','Record delivery of allocated onboarding assets.'),
 ('hr-payroll','hr.onboarding.assets.acknowledge','Acknowledge Onboarding Assets','Record receipt acknowledgement for delivered onboarding assets.'))
INSERT INTO iam.permissions(module_code,permission_code,permission_name,description)
SELECT * FROM p WHERE NOT EXISTS(SELECT 1 FROM iam.permissions x WHERE x.permission_code=p.permission_code);
WITH g(role_code,permission_code) AS (VALUES
 ('hr_approver','hr.onboarding.assets.approve'),('inventory_manager','hr.onboarding.assets.allocate'),
 ('inventory_manager','hr.onboarding.assets.deliver'),('warehouse_manager','hr.onboarding.assets.allocate'),
 ('warehouse_manager','hr.onboarding.assets.deliver'),('hr_operations','hr.onboarding.assets.acknowledge'))
INSERT INTO iam.role_permissions(role_id,permission_id)
SELECT r.id,p.id FROM g JOIN iam.roles r ON LOWER(r.code)=g.role_code JOIN iam.permissions p ON p.permission_code=g.permission_code
WHERE NOT EXISTS(SELECT 1 FROM iam.role_permissions rp WHERE rp.role_id=r.id AND rp.permission_id=p.id);
INSERT INTO hr_payroll.onboarding_asset_templates(tenant_id,template_no,template_name,description,items)
SELECT t.id,v.no,v.name,v.description,v.items::jsonb FROM platform.tenants t CROSS JOIN (VALUES
 ('OAT-001','Standard Knowledge Worker','Office-based knowledge workers','[{"name":"Laptop","quantity":1},{"name":"Monitor","quantity":1},{"name":"Mouse/Keyboard","quantity":1},{"name":"Headset","quantity":1}]'),
 ('OAT-002','Field Sales Kit','Field sales roles','[{"name":"Laptop","quantity":1},{"name":"Mobile + SIM","quantity":1},{"name":"Backpack","quantity":1},{"name":"CRM access","quantity":1}]'),
 ('OAT-003','Warehouse Frontline','Warehouse roles','[{"name":"PPE Kit","quantity":1},{"name":"Handheld Scanner","quantity":1},{"name":"Two-way Radio","quantity":1}]'),
 ('OAT-004','Executive Bundle','Executive roles','[{"name":"MacBook Pro","quantity":1},{"name":"iPhone Pro","quantity":1},{"name":"Dual Monitor","quantity":1},{"name":"Security Token","quantity":1}]')) v(no,name,description,items)
ON CONFLICT(tenant_id,template_no) DO NOTHING;
COMMIT;