-- Create invoice_settings table and Invoice Settings form

-- Table for storing invoice configuration per tenant
CREATE TABLE IF NOT EXISTS invoice_settings (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER DEFAULT NULL,
    template_id INTEGER DEFAULT NULL,
    logo_document_id INTEGER DEFAULT NULL,
    instructions TEXT DEFAULT NULL,
    disclaimer TEXT DEFAULT NULL,
    bank_details TEXT DEFAULT NULL,
    number_format VARCHAR(50) DEFAULT NULL,
    starting_number INTEGER DEFAULT NULL,
    rollover_date DATE DEFAULT NULL,
    isDeleted BOOLEAN DEFAULT FALSE,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default settings row
INSERT INTO invoice_settings (tenant_id)
SELECT NULL
WHERE NOT EXISTS (SELECT 1 FROM invoice_settings WHERE tenant_id IS NULL);

-- Define Invoice Settings form mapped to invoice_settings table
INSERT INTO forms (id, name, description, mapped_table, tenant_id, branch_id)
SELECT 44, 'Invoice Settings', 'Configure invoice defaults', 'invoice_settings', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 44);

-- Form elements
INSERT INTO form_elements (
    form_id, label, field_name, data_column, type_id,
    options_table, options_label_column, options_value_column, order_index)
SELECT 44, 'template_id', 'template_id', 'template_id',
    (SELECT id FROM element_types WHERE name='dropdown' LIMIT 1),
    'invoice_templates', 'name', 'id', 1
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 44 AND field_name = 'template_id');

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

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

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

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

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 44, 'number_format', 'number_format', 'number_format',
    (SELECT id FROM element_types WHERE name='text' LIMIT 1), 6
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 44 AND field_name = 'number_format');

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

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