-- SQL statements to register the W-SUBA rule in the QCS database
-- W-SUBA: Survival time zero with ambiguous/unlikely values of BoD/Autopsy

-- Step 1: Add validation message for W-SUBA
INSERT INTO validationMessage(id,code,type,reference,description) VALUES
(217,'W-SUBA','WARNING','','Survival time zero with ambiguous/unlikely values of BoD/Autopsy');

-- Step 2: Add rule definition for W-SUBA
INSERT INTO ruledefinition(ID,NAME,DESCRIPTION,POSITIVE,CASESENSITIVITY,LOCATION,IMPLEMENTATION,VALIDATIONMESSAGE_ID,RULETYPE_ID) VALUES
(322,'Survival zero BoD Autopsy ','Survival BoD Autopsy Zero',true,'INSENSITIVE','eu.europa.ec.jrc.qcs.engine.rule.crossfield.encr2020','SurvivalZeroWithBoDAutopsyRule',217,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, enabled) VALUES 
(375,11,7,0,322,1);

-- Step 4: Rule-to-Field Mappings (4 fields)
INSERT INTO ruletofield(id,protocol_id,ruledefinition_id,target_id,position,key,field_id) VALUES ((SELECT next_val FROM ruleToField_SEQ),11,322,3,1,0,88);  -- Surv_time (field_id=88)
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,322,3,2,0,79);  -- BoD (field_id=79)
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,322,3,3,0,84);  -- Autopsy (field_id=84)
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,322,3,4,0,85);  -- Vital status (field_id=85)
UPDATE ruleToField_SEQ SET next_val = next_val + 1;

