-- SQL statements to register the updated W-TNME rule in the QCS database
-- This file should be executed against both qcs.db files in quality-check-software and qcs-admin projects
-- Consistency check for TNM Edition

-- 1. Insert new Rule Definition for TNMEditionRule2
INSERT INTO ruledefinition(ID,NAME,DESCRIPTION,POSITIVE,CASESENSITIVITY,LOCATION,IMPLEMENTATION,VALIDATIONMESSAGE_ID,RULETYPE_ID) 
VALUES (319,'TNM Edition missing (v2)','TNM edition is missing but valid TNM variables',
        true,'INSENSITIVE','eu.europa.ec.jrc.qcs.engine.rule.crossfield.encr2020','TNMEditionRule2',147,8);

-- 2. Remove old rule (TNMEditionRule, ID 305) from PROTOCOL_TO_RULE table for Protocol 11
DELETE FROM protocolToRule WHERE protocol_id=11 AND ruleDefinition_id=305;

-- 3. Insert the new rule in the PROTOCOL_TO_RULE table
INSERT INTO protocolToRule(id,protocol_id,priority,block,ruleDefinition_id) 
VALUES (361,11,6,0,319);

-- 4. Rule-to-Field Mappings
-- Insert RULE_TO_FIELD entries for the new rule (ID 319) for Protocol 11 (replicating configuration of rule 305)
INSERT INTO ruletofield(id,protocol_id,ruledefinition_id,target_id,position,key,field_id) VALUES
(3000,11,319,3,1,0,91),  -- TNM Edition (field_id=91)
(3001,11,319,3,2,0,95),  -- pT (field_id=95)
(3002,11,319,3,3,0,96),  -- pN (field_id=96)
(3003,11,319,3,4,0,97),  -- pM (field_id=97)
(3004,11,319,3,5,0,92),  -- cT (field_id=92)
(3005,11,319,3,6,0,93),  -- cN (field_id=93)
(3006,11,319,3,7,0,94);  -- cM (field_id=94)

-- 5. Update the message in the VALIDATION_MESSAGE table ??
UPDATE validationMessage SET description = 'valid TNM variables but TNM edition is not available' WHERE code = 'W-TNME';
