-- Add Case Instruction and Case Expense forms

-- Case Instruction form
INSERT INTO forms (id, name, description, mapped_table, tenant_id, branch_id)
SELECT 47, 'Case Instruction', 'Matter instruction', 'case_instructions', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 47);

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 47, 'case_id', 'case_id', 'case_id',
    (SELECT id FROM element_types WHERE name = 'number' LIMIT 1), 1
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 47 AND field_name = 'case_id'
);

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 47, 'instruction_date', 'instruction_date', 'instruction_date',
    (SELECT id FROM element_types WHERE name = 'date' LIMIT 1), 2
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 47 AND field_name = 'instruction_date'
);

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 47, 'instruction', 'instruction', 'instruction',
    (SELECT id FROM element_types WHERE name = 'textarea' LIMIT 1), 3
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 47 AND field_name = 'instruction'
);

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 47, 'attachment', 'attachment', 'attachment',
    (SELECT id FROM element_types WHERE name = 'file' LIMIT 1), 4
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 47 AND field_name = 'attachment'
);

INSERT INTO table_views (form_id, name, columns, sort_by, sort_dir)
SELECT 47, 'Default', '["case_id","instruction_date","instruction"]', 'instruction_date', 'DESC'
WHERE NOT EXISTS (SELECT 1 FROM table_views WHERE form_id = 47 AND name = 'Default');

-- Case Expense form
INSERT INTO forms (id, name, description, mapped_table, tenant_id, branch_id)
SELECT 48, 'Case Expense', 'Case related expense', 'expenses', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 48);

INSERT INTO form_elements (
    form_id, label, field_name, data_column, type_id,
    options_table, options_label_column, options_value_column, order_index)
SELECT 48, 'case', 'case_id', 'case_id',
    (SELECT id FROM element_types WHERE name = 'dropdown' LIMIT 1),
    'cases', 'title', 'id', 1
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 48 AND field_name = 'case_id'
);

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 48, 'expense_date', 'expense_date', 'expense_date',
    (SELECT id FROM element_types WHERE name = 'date' LIMIT 1), 2
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 48 AND field_name = 'expense_date'
);

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 48, 'amount', 'amount', 'amount',
    (SELECT id FROM element_types WHERE name = 'number' LIMIT 1), 3
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 48 AND field_name = 'amount'
);

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 48, 'description', 'description', 'description',
    (SELECT id FROM element_types WHERE name = 'textarea' LIMIT 1), 4
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 48 AND field_name = 'description'
);

INSERT INTO form_elements (
    form_id, label, field_name, data_column, type_id,
    options_table, options_label_column, options_value_column, order_index)
SELECT 48, 'employee', 'employee_id', 'employee_id',
    (SELECT id FROM element_types WHERE name = 'dropdown' LIMIT 1),
    'employees', 'name', 'id', 5
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 48 AND field_name = 'employee_id'
);

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 48, 'document', 'document_id', 'document_id',
    (SELECT id FROM element_types WHERE name = 'file' LIMIT 1), 6
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 48 AND field_name = 'document_id'
);

INSERT INTO table_views (form_id, name, columns, sort_by, sort_dir)
SELECT 48, 'Default', '["case_id","expense_date","amount","employee_id"]', 'expense_date', 'DESC'
WHERE NOT EXISTS (SELECT 1 FROM table_views WHERE form_id = 48 AND name = 'Default');
