-- SQL statements to register the W-SSMI rule in the QCS database
-- This file should be executed against both qcs.db files in quality-check-software and qcs-admin projects
--
-- W-SSMI: Stage cannot be computed if any pair of T or N or M is missing/unknown/wrong and (cM,pM) are not 1.

-- Step 1: Add validation message for W-SSMI
INSERT INTO validationMessage(id,code,type,reference,description) VALUES
('214','W-SSMI','WARNING','','Stage not available and cannot be computed.');

-- Step 2: Add rule definition for W-SSMI
INSERT INTO ruledefinition(ID,NAME,DESCRIPTION,POSITIVE,CASESENSITIVITY,LOCATION,IMPLEMENTATION,VALIDATIONMESSAGE_ID,RULETYPE_ID) VALUES
(318,'Stage Variables Pair Missing','Stage Variables Pair Missing',true,'INSENSITIVE','eu.europa.ec.jrc.qcs.engine.rule.crossfield.encr2020','StageVariablesPairMissingRule',214,8);

-- Step 3: Add rule to ENCR 2020 protocol (protocol_id=11) with priority 7 (cross-field rules level)
INSERT INTO protocolToRule(id,protocol_id,priority,block,ruleDefinition_id) VALUES 
(372,11,7,0,318);

-- Step 4: Rule-to-Field Mappings (8 fields)
-- Protocol 11 (ENCR 2020): Stage=99, cT=92, pT=95, cN=93, pN=96, cM=94, pM=97, Beh=82
INSERT INTO ruletofield(id,protocol_id,ruledefinition_id,target_id,position,key,field_id) VALUES ((SELECT next_val FROM ruleToField_SEQ),11,318,3,1,0,99); -- Stage (field_id=99)
UPDATE ruleToField_SEQ SET next_val = next_val + 1;
INSERT INTO ruletofield(id,protocol_id,ruledefinition_id,target_id,position,key,field_id) VALUES ((SELECT next_val FROM ruleToField_SEQ),11,318,3,2,0,92); -- cT (field_id=92)
UPDATE ruleToField_SEQ SET next_val = next_val + 1;
INSERT INTO ruletofield(id,protocol_id,ruledefinition_id,target_id,position,key,field_id) VALUES ((SELECT next_val FROM ruleToField_SEQ),11,318,3,3,0,95); -- pT (field_id=95)
UPDATE ruleToField_SEQ SET next_val = next_val + 1;
INSERT INTO ruletofield(id,protocol_id,ruledefinition_id,target_id,position,key,field_id) VALUES ((SELECT next_val FROM ruleToField_SEQ),11,318,3,4,0,93); -- cN (field_id=93)
UPDATE ruleToField_SEQ SET next_val = next_val + 1;
INSERT INTO ruletofield(id,protocol_id,ruledefinition_id,target_id,position,key,field_id) VALUES ((SELECT next_val FROM ruleToField_SEQ),11,318,3,5,0,96); -- pN (field_id=96)
UPDATE ruleToField_SEQ SET next_val = next_val + 1;
INSERT INTO ruletofield(id,protocol_id,ruledefinition_id,target_id,position,key,field_id) VALUES ((SELECT next_val FROM ruleToField_SEQ),11,318,3,6,0,94); -- cM (field_id=94)
UPDATE ruleToField_SEQ SET next_val = next_val + 1;
INSERT INTO ruletofield(id,protocol_id,ruledefinition_id,target_id,position,key,field_id) VALUES ((SELECT next_val FROM ruleToField_SEQ),11,318,3,7,0,97); -- pM (field_id=97)
UPDATE ruleToField_SEQ SET next_val = next_val + 1;
INSERT INTO ruletofield(id,protocol_id,ruledefinition_id,target_id,position,key,field_id) VALUES ((SELECT next_val FROM ruleToField_SEQ),11,318,3,8,0,82); -- Beh (field_id=82)
UPDATE ruleToField_SEQ SET next_val = next_val + 1;
