-- SQL statements to register the W-TNMS rule in the QCS database

-- Step 1: Add validation message for W-TNMS
INSERT INTO validationMessage(id,code,type,reference,description) VALUES
(143,'W-TNMS','WARNING','','Topography + TNM edition + T,N,M + Stage (not in accordance with the target TNM edition)');

-- Step 2: Add rule definition for W-TNMM (RuleDefinition ID = 236)
INSERT INTO ruledefinition(ID,NAME,DESCRIPTION,POSITIVE,CASESENSITIVITY,LOCATION,IMPLEMENTATION,VALIDATIONMESSAGE_ID,RULETYPE_ID) VALUES
(238,'TNM standard rule (Stage)','TNM standard rule (Stage)',true,'INSENSITIVE','eu.europa.ec.jrc.qcs.engine.rule.crossfield.encr2020','TNMStageRule',143,8);

-- Step 3: Add rule to protocol 11 (priority 7, cross-field level)
INSERT INTO protocolToRule(id,protocol_id,priority,block,ruleDefinition_id) VALUES
(352,11,7,0,238);

-- Step 4: Rule-to-Field mappings

-- Protocol 11 (ENCR 2020): Topo=80, Morpho=81, TNM_ed=91, Stage=99, pT=95, pN=96, pM=97, cT=92, cN=93, cM=94, Grade=83, Age=73, Beh=82
INSERT INTO ruletofield(id,protocol_id,ruledefinition_id,target_id,position,key,field_id) VALUES ((SELECT next_val FROM ruleToField_SEQ),11,238,3,1,0,80); -- Topo (field_id=80)
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,238,3,2,0,81); -- Morpho (field_id=81)
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,238,3,3,0,91); -- TNM_ed (field_id=91)
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,238,3,4,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,238,3,5,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,238,3,6,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,238,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,238,3,8,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,238,3,9,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,238,3,10,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,238,3,11,0,83); -- Grade (field_id=83)
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,238,3,12,0,73); -- Age (field_id=73)
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,238,3,13,0,82); -- Beh (field_id=82)
UPDATE ruleToField_SEQ SET next_val = next_val + 1;


