-- Reference SQL for the W-UNKN rule in the QCS database.

-- Step 1: Validation message for W-UNKN (id 66 – may already exist)
INSERT OR REPLACE INTO validationMessage(id,code,type,reference,description) VALUES
(66,'W-UNKN','WARNING','','Value set to missing/unknown');

-- Step 2: Rule definition for W-UNKN (id 67 – may already exist)
INSERT OR REPLACE INTO ruledefinition(ID,NAME,DESCRIPTION,POSITIVE,CASESENSITIVITY,LOCATION,IMPLEMENTATION,VALIDATIONMESSAGE_ID,RULETYPE_ID) VALUES
(67,'Fields format (unknown value)','Fields format (unknown value)',true,'INSENSITIVE','eu.europa.ec.jrc.qcs.engine.rule.field','UnknownValueRule',66,6);

-- Step 3: Attach rule to protocol(s) via protocolToRule (ids and priority depend on protocol; example for protocol 11)
-- INSERT INTO protocolToRule(id,protocol_id,priority,block,ruleDefinition_id) VALUES
-- (<id>,11,<priority>,0,67);

-- Step 4a: Option ALL_FIELDS – check every field that has unknownValue set (one row per protocol, target_id=2)
-- INSERT INTO ruletofield(id,protocol_id,ruledefinition_id,target_id,position,key,field_id) VALUES
-- (<id>,<protocol_id>,67,2,0,0,NULL);

-- Step 4b: Option SOME_FIELDS – check only listed fields (one row per field, target_id=3)
-- Example for protocol 11 (ENCR 2020 incidence) – field_ids that have unknown value and are checked for W-UNKN:
INSERT INTO ruletofield(id,protocol_id,ruledefinition_id,target_id,position,key,field_id) VALUES
(3007,11,67,3,5,0,74),
(3008,11,67,3,9,0,77),
(3009,11,67,3,11,0,79),
(3010,11,67,3,16,0,84),
(3011,11,67,3,17,0,85),
(3012,11,67,3,18,0,86),
(3013,11,67,3,19,0,87),
(3014,11,67,3,20,0,88),
(3015,11,67,3,4,0,73);
