-- SQL statements to register the E-AUBS rule in the QCS database
-- This file should be executed against both qcs.db files in quality-check-software

-- Step 1: Add validation message for E-AUBS
INSERT INTO validationMessage(id,code,type,reference,description) VALUES
(216,'E-AUBS','ERROR','Autopsy+BoD+Surv time/Date of Followup-Date of Incidence not valid.','Autopsy+BoD+Surv time/Date of Followup-Date of Incidence not valid.');

-- Step 2: Add rule definition for E-AUBS
INSERT INTO ruledefinition(ID,NAME,DESCRIPTION,POSITIVE,CASESENSITIVITY,LOCATION,IMPLEMENTATION,VALIDATIONMESSAGE_ID,RULETYPE_ID) VALUES
(321,'Autopsy BoD Survival Exclusion','Autopsy+BoD+SurvDate of Followup-Date of Incidence not valid',true,'INSENSITIVE','eu.europa.ec.jrc.qcs.engine.rule.crossfield.encr2020','AutopsyBoDSurvivalExclusionRule',216,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
(374,11,7,0,321);

-- Step 4: Rule-to-Field Mappings (7 fields)
INSERT INTO ruletofield(id,protocol_id,ruledefinition_id,target_id,position,key,field_id) VALUES ((SELECT next_val FROM ruleToField_SEQ),11,321,3,1,0,84);  -- Autopsy (field_id=84 - INCIDENCE_AUTOPSY)
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,321,3,2,0,79);  -- BoD (field_id=79 - INCIDENCE_BOD)
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,321,3,3,0,88);  -- Surv_time (field_id=88 - INCIDENCE_SURVIVAL)
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,321,3,4,0,77);  -- MoI - Month of Incidence (field_id=77 - INCIDENCE_MONTH_DOI)
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,321,3,5,0,78);  -- YoI - Year of Incidence (field_id=78 - INCIDENCE_YEAR_DOI)
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,321,3,6,0,86);  -- MoF - Month of Follow-up (field_id=86 - INCIDENCE_MONTH_FU)
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,321,3,7,0,87);  -- YoF - Year of Follow-up (field_id=87 - INCIDENCE_YEAR_FU)
UPDATE ruleToField_SEQ SET next_val = next_val + 1;
