-- Add invoice_template_id column to invoices table and update Case Invoice form

ALTER TABLE invoices
    ADD COLUMN IF NOT EXISTS invoice_template_id INTEGER;
CREATE INDEX IF NOT EXISTS invoices_template_idx ON invoices(invoice_template_id);

-- Add dropdown to Case Invoice form
INSERT INTO form_elements (
    form_id, label, field_name, data_column, type_id,
    options_table, options_label_column, options_value_column, order_index)
SELECT 49, 'invoice_template_id', 'invoice_template_id', 'invoice_template_id',
    (SELECT id FROM element_types WHERE name='dropdown' LIMIT 1),
    'invoice_templates', 'name', 'id', 6
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 49 AND field_name = 'invoice_template_id');
