-- Add additional fields to Case Hour form

-- ensure hidden element type exists
INSERT INTO element_types (tenant_id, branch_id, name, html_type, sql_type)
SELECT NULL, NULL, 'hidden', 'hidden', 'BOOLEAN'
WHERE NOT EXISTS (SELECT 1 FROM element_types WHERE name = 'hidden');

-- Case Hour form id lookup
WITH fh AS (
  SELECT id FROM forms WHERE name = 'Case Hour' LIMIT 1
)
-- Title field
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT fh.id, 'title', 'title', 'title', (SELECT id FROM element_types WHERE name = 'text' LIMIT 1), 9
FROM fh WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = fh.id AND field_name = 'title');

-- Instructions field
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT fh.id, 'instructions', 'instructions', 'instructions', (SELECT id FROM element_types WHERE name = 'textarea' LIMIT 1), 10
FROM fh WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = fh.id AND field_name = 'instructions');

-- Posted hidden field
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT fh.id, 'posted', 'posted', 'posted', (SELECT id FROM element_types WHERE name = 'hidden' LIMIT 1), 11
FROM fh WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = fh.id AND field_name = 'posted');

-- Document upload using case_hour_documents link table
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index, validation)
SELECT fh.id, 'documents', 'documents', 'documents', (SELECT id FROM element_types WHERE name = 'file' LIMIT 1), 12,
       '{"linkTable":"case_hour_documents","linkField":"document_id"}'::json
FROM fh WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = fh.id AND field_name = 'documents');
