-- v32: external field mapping workspace
CREATE TABLE IF NOT EXISTS external_field_mappings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  data_source_id INT NOT NULL,
  mapping_name VARCHAR(180) NOT NULL,
  import_scope VARCHAR(80) DEFAULT 'combined_registry',
  target_table VARCHAR(80) DEFAULT 'beneficiaries',
  source_field VARCHAR(120) NOT NULL,
  target_field VARCHAR(120) NOT NULL,
  transform_rule VARCHAR(80) DEFAULT 'trim',
  validation_rule VARCHAR(80) DEFAULT 'none',
  default_value VARCHAR(190) DEFAULT '',
  is_required TINYINT(1) DEFAULT 0,
  status VARCHAR(40) DEFAULT 'active',
  notes TEXT NULL,
  created_by INT DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_external_mappings_tenant_status (tenant_id, status),
  INDEX idx_external_mappings_source (data_source_id),
  INDEX idx_external_mappings_target (target_table, target_field)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(81, 'external_mappings', 'View external field mapping workspace', 'external_mappings.view'),
(82, 'external_mappings', 'Manage external field mapping workspace', 'external_mappings.manage')
ON DUPLICATE KEY UPDATE module=VALUES(module), name=VALUES(name), slug=VALUES(slug);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.slug IN ('external_mappings.view','external_mappings.manage')
LEFT JOIN role_permissions rp ON rp.role_id=r.id AND rp.permission_id=p.id
WHERE rp.id IS NULL AND (
  (r.slug IN ('super_admin','tenant_admin','programme_manager','field_officer','case_worker') AND p.slug IN ('external_mappings.view','external_mappings.manage'))
  OR (r.slug IN ('me_officer','finance_officer') AND p.slug='external_mappings.view')
  OR (r.slug='partner_user' AND p.slug='external_mappings.view')
  OR (r.slug IN ('auditor','read_only_viewer') AND p.slug='external_mappings.view')
);

INSERT INTO external_field_mappings
(tenant_id, data_source_id, mapping_name, import_scope, target_table, source_field, target_field, transform_rule, validation_rule, default_value, is_required, status, notes, created_by, created_at, updated_at)
SELECT 1, ds.id, 'SOCU Household Registry Standard Map', 'combined_registry', 'households', 'household_head_name', 'head_name', 'title_case', 'required', '', 1, 'active', 'Seeded demo mapping profile for SOCU sourced registry uploads.', 1, NOW(), NOW()
FROM data_sources ds
WHERE ds.tenant_id=1 AND (ds.source_name LIKE '%SOCU%' OR ds.source_code LIKE 'SOCU%')
AND NOT EXISTS (SELECT 1 FROM external_field_mappings WHERE tenant_id=1 AND mapping_name='SOCU Household Registry Standard Map');

INSERT INTO external_field_mappings
(tenant_id, data_source_id, mapping_name, import_scope, target_table, source_field, target_field, transform_rule, validation_rule, default_value, is_required, status, notes, created_by, created_at, updated_at)
SELECT 1, ds.id, 'SOCU Beneficiary Registry Standard Map', 'combined_registry', 'beneficiaries', 'beneficiary_phone', 'phone', 'phone_normalize', 'phone', '', 0, 'active', 'Seeded demo mapping profile for SOCU beneficiary records.', 1, NOW(), NOW()
FROM data_sources ds
WHERE ds.tenant_id=1 AND (ds.source_name LIKE '%SOCU%' OR ds.source_code LIKE 'SOCU%')
AND NOT EXISTS (SELECT 1 FROM external_field_mappings WHERE tenant_id=1 AND mapping_name='SOCU Beneficiary Registry Standard Map');