-- Create ohs_needs_expectations table
CREATE TABLE ohs_needs_expectations (
    id SERIAL,
    interested_party VARCHAR(255) NOT NULL,
    needs TEXT DEFAULT NULL,
    expectations TEXT DEFAULT NULL,
    compliance_required BOOLEAN DEFAULT FALSE,
    source VARCHAR(255) DEFAULT NULL,
    status VARCHAR(50) DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    review_date DATE DEFAULT NULL,
    review_by INTEGER DEFAULT NULL,
    tenant_id INTEGER DEFAULT NULL,
    branch_id INTEGER DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);
CREATE INDEX one_tenant_idx ON ohs_needs_expectations (tenant_id);
CREATE INDEX one_branch_idx ON ohs_needs_expectations (branch_id);

-- Needs & Expectations Assessment form
INSERT INTO forms (id, name, description, mapped_table, tenant_id, branch_id)
SELECT 66, 'Needs & Expectations Assessment', 'Stakeholder needs and expectations', 'ohs_needs_expectations', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 66);

-- Form fields
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 66, 'interested_party', 'interested_party', 'interested_party', (SELECT id FROM element_types WHERE name='text' LIMIT 1), 1
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 66 AND field_name = 'interested_party');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 66, 'needs', 'needs', 'needs', (SELECT id FROM element_types WHERE name='textarea' LIMIT 1), 2
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 66 AND field_name = 'needs');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 66, 'expectations', 'expectations', 'expectations', (SELECT id FROM element_types WHERE name='textarea' LIMIT 1), 3
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 66 AND field_name = 'expectations');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 66, 'compliance_required', 'compliance_required', 'compliance_required', (SELECT id FROM element_types WHERE name='checkbox' LIMIT 1), 4
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 66 AND field_name = 'compliance_required');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 66, 'source', 'source', 'source', (SELECT id FROM element_types WHERE name='text' LIMIT 1), 5
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 66 AND field_name = 'source');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 66, 'status', 'status', 'status', (SELECT id FROM element_types WHERE name='text' LIMIT 1), 6
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 66 AND field_name = 'status');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 66, 'notes', 'notes', 'notes', (SELECT id FROM element_types WHERE name='textarea' LIMIT 1), 7
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 66 AND field_name = 'notes');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 66, 'review_date', 'review_date', 'review_date', (SELECT id FROM element_types WHERE name='date' LIMIT 1), 8
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 66 AND field_name = 'review_date');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, options_table, options_label_column, options_value_column, order_index)
SELECT 66, 'review_by', 'review_by', 'review_by', (SELECT id FROM element_types WHERE name='dropdown' LIMIT 1), 'users', 'full_name', 'id', 9
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 66 AND field_name = 'review_by');

-- Default table view
INSERT INTO table_views (form_id, name, columns, sort_by, sort_dir)
SELECT 66, 'Default', '["interested_party","status","review_date"]', 'interested_party', 'ASC'
WHERE NOT EXISTS (SELECT 1 FROM table_views WHERE form_id = 66 AND name = 'Default');

-- Needs & Expectations modal
INSERT INTO ui_modals (name, config, tenant_id, branch_id, icon, aria_label, description)
SELECT 'Needs & Expectations', '{"title":"Needs & Expectations","header":[{"type":"button","label":"Add Assessment","formName":"Needs & Expectations Assessment"}],"body":[{"type":"table","options":{"tableName":"ohs_needs_expectations","showGlobalSearch":false,"rowClick":"selectRecord"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' , NULL, NULL, 'fa-hands-helping', 'Needs & Expectations', 'Needs & Expectations'
WHERE NOT EXISTS (SELECT 1 FROM ui_modals WHERE name = 'Needs & Expectations');
