-- Create case_invoices and case_payments tables

CREATE TABLE IF NOT EXISTS case_invoices (
    id SERIAL PRIMARY KEY,
    case_id INTEGER NOT NULL,
    invoice_number VARCHAR(100) NOT NULL,
    invoice_date DATE DEFAULT NULL,
    amount DECIMAL(10,2) DEFAULT 0,
    status VARCHAR(50) DEFAULT NULL,
    sent_at TIMESTAMP DEFAULT NULL,
    tenant_id INTEGER DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS case_payments (
    id SERIAL PRIMARY KEY,
    case_invoice_id INTEGER NOT NULL,
    payment_date DATE DEFAULT NULL,
    amount DECIMAL(10,2) DEFAULT 0,
    payment_method VARCHAR(50) DEFAULT NULL,
    tenant_id INTEGER DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Case Invoice form definition
INSERT INTO forms (id, name, description, mapped_table, tenant_id, branch_id)
SELECT 42, 'Case Invoice', 'Invoice for a case', 'invoices', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 42);

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 42, 'reference_id', 'reference_id', 'reference_id', (SELECT id FROM element_types WHERE name='hidden' LIMIT 1), 1
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 42 AND field_name = 'reference_id');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 42, 'invoice_number', 'invoice_number', 'invoice_number', (SELECT id FROM element_types WHERE name='text' LIMIT 1), 2
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 42 AND field_name = 'invoice_number');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 42, 'invoice_date', 'invoice_date', 'invoice_date', (SELECT id FROM element_types WHERE name='date' LIMIT 1), 3
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 42 AND field_name = 'invoice_date');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 42, 'amount', 'amount', 'amount', (SELECT id FROM element_types WHERE name='number' LIMIT 1), 4
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 42 AND field_name = 'amount');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 42, 'status', 'status', 'status', (SELECT id FROM element_types WHERE name='text' LIMIT 1), 5
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 42 AND field_name = 'status');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 42, 'sent_at', 'sent_at', 'sent_at', (SELECT id FROM element_types WHERE name='datetime' LIMIT 1), 6
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 42 AND field_name = 'sent_at');

INSERT INTO table_views (form_id, name, columns, sort_by, sort_dir)
SELECT 42, 'Default', '["reference_id","invoice_number","invoice_date","amount","status"]', 'invoice_date', 'DESC'
WHERE NOT EXISTS (SELECT 1 FROM table_views WHERE form_id = 42 AND name = 'Default');

-- Case Payment form definition
INSERT INTO forms (id, name, description, mapped_table, tenant_id, branch_id)
SELECT 43, 'Case Payment', 'Payment for a case invoice', 'payments', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 43);

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 43, 'invoice_id', 'invoice_id', 'invoice_id', (SELECT id FROM element_types WHERE name='dropdown' LIMIT 1), 1
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 43 AND field_name = 'case_invoice_id');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 43, 'payment_date', 'payment_date', 'payment_date', (SELECT id FROM element_types WHERE name='date' LIMIT 1), 2
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 43 AND field_name = 'payment_date');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 43, '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 = 43 AND field_name = 'amount');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 43, 'payment_method', 'payment_method', 'payment_method', (SELECT id FROM element_types WHERE name='text' LIMIT 1), 4
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 43 AND field_name = 'payment_method');

INSERT INTO table_views (form_id, name, columns, sort_by, sort_dir)
SELECT 43, 'Default', '["case_invoice_id","payment_date","amount","payment_method"]', 'payment_date', 'DESC'
WHERE NOT EXISTS (SELECT 1 FROM table_views WHERE form_id = 43 AND name = 'Default');
