-- Add client detail columns and update forms

ALTER TABLE clients
    ADD COLUMN IF NOT EXISTS name varchar(255) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS phone varchar(50) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS email varchar(255) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS description text DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS comments text DEFAULT NULL;

-- Remove tenant_id element from Client form
DELETE FROM form_elements WHERE form_id = 35 AND field_name = 'tenant_id';

-- Add new form elements for Client details
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 35, 'name', 'name', 'name', 1, 1
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 35 AND field_name = 'name');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 35, 'phone', 'phone', 'phone', 1, 2
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 35 AND field_name = 'phone');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 35, 'email', 'email', 'email', 1, 3
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 35 AND field_name = 'email');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 35, 'description', 'description', 'description', 6, 4
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 35 AND field_name = 'description');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 35, 'comments', 'comments', 'comments', 6, 5
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 35 AND field_name = 'comments');

-- Update Clients table view
UPDATE table_views
SET columns = '["name","email","phone"]'
WHERE form_id = 35 AND name = 'Default';
