-- Updates to Risk Identification form

-- Ensure risk_description element exists
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 75, 'risk_description', 'risk_description', 'risk_description',
       (SELECT id FROM element_types WHERE name='textarea' LIMIT 1), 1
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 75 AND field_name = 'risk_description'
);

-- Ensure current_controls element exists
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 75, 'current_controls', 'current_controls', 'current_controls',
       (SELECT id FROM element_types WHERE name='textarea' LIMIT 1), 7
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 75 AND field_name = 'current_controls'
);

-- Ensure risk_category label is correct
UPDATE form_elements
SET label = 'Risk category'
WHERE form_id = 75 AND field_name = 'risk_category_id';

-- Switch likelihood and impact to dropdown elements
UPDATE form_elements
SET type_id = (SELECT id FROM element_types WHERE name='dropdown' LIMIT 1)
WHERE form_id = 75 AND field_name IN ('likelihood','impact');

-- Dropdown options 1-5 for likelihood
INSERT INTO element_options (element_id, display_text, value)
SELECT fe.id, v.opt, v.opt
FROM (VALUES ('1'),('2'),('3'),('4'),('5')) AS v(opt)
CROSS JOIN LATERAL (
    SELECT id FROM form_elements WHERE form_id = 75 AND field_name = 'likelihood'
) fe
WHERE NOT EXISTS (
    SELECT 1 FROM element_options eo
    WHERE eo.element_id = fe.id AND eo.value = v.opt
);

-- Dropdown options 1-5 for impact
INSERT INTO element_options (element_id, display_text, value)
SELECT fe.id, v.opt, v.opt
FROM (VALUES ('1'),('2'),('3'),('4'),('5')) AS v(opt)
CROSS JOIN LATERAL (
    SELECT id FROM form_elements WHERE form_id = 75 AND field_name = 'impact'
) fe
WHERE NOT EXISTS (
    SELECT 1 FROM element_options eo
    WHERE eo.element_id = fe.id AND eo.value = v.opt
);

-- Remove risk_rating element
DELETE FROM form_elements
WHERE form_id = 75 AND field_name = 'risk_rating';

-- Update table view columns
UPDATE table_views
SET columns = '["risk_description","risk_category_id","risk_category_name","likelihood","impact",{"field":"risk_rating","formula":"impact * likelihood"}]'
WHERE form_id = 75 AND name = 'Default';
