-- Create ohs_stakeholders table and associated UI

CREATE TABLE ohs_stakeholders (
    id SERIAL,
    stakeholder_name varchar(255) NOT NULL,
    category varchar(100) DEFAULT NULL,
    role_influence text DEFAULT NULL,
    needs_expectations text DEFAULT NULL,
    impact text DEFAULT NULL,
    communication_method varchar(100) DEFAULT NULL,
    review_date date 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 ohs_stakeholders_tenant_idx ON ohs_stakeholders (tenant_id);
CREATE INDEX ohs_stakeholders_branch_idx ON ohs_stakeholders (branch_id);

-- Register table for sync
INSERT INTO tenant_tables (tenant_id, user_id, table_name, definition)
SELECT NULL, NULL, 'ohs_stakeholders', '{}'
WHERE NOT EXISTS (
    SELECT 1 FROM tenant_tables WHERE table_name = 'ohs_stakeholders'
);

-- Stakeholder form definition
INSERT INTO forms (id, name, description, mapped_table, tenant_id, branch_id)
SELECT 66, 'Stakeholder', 'OHS stakeholder entry', 'ohs_stakeholders', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 66);

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 66, 'stakeholder_name', 'stakeholder_name', 'stakeholder_name',
       (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 = 'stakeholder_name'
);
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 66, 'category', 'category', 'category',
       (SELECT id FROM element_types WHERE name='text' LIMIT 1), 2
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 66 AND field_name = 'category'
);
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 66, 'role_influence', 'role_influence', 'role_influence',
       (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 = 'role_influence'
);
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 66, 'needs_expectations', 'needs_expectations', 'needs_expectations',
       (SELECT id FROM element_types WHERE name='textarea' LIMIT 1), 4
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 66 AND field_name = 'needs_expectations'
);
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 66, 'impact', 'impact', 'impact',
       (SELECT id FROM element_types WHERE name='textarea' LIMIT 1), 5
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 66 AND field_name = 'impact'
);
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 66, 'communication_method', 'communication_method', 'communication_method',
       (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 = 'communication_method'
);
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), 7
WHERE NOT EXISTS (
    SELECT 1 FROM form_elements WHERE form_id = 66 AND field_name = 'review_date'
);

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

-- Modal definition
INSERT INTO ui_modals (name, config, tenant_id, branch_id, icon, aria_label, description)
SELECT 'Stakeholder Register',
       '{"title":"Stakeholder Register","header":[{"type":"button","label":"Add Stakeholder","formName":"Stakeholder"}],"body":[{"type":"table","options":{"tableName":"ohs_stakeholders","showGlobalSearch":false,"rowClick":"selectRecord"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
       NULL, NULL, 'fa-users', 'Stakeholder Register', 'Stakeholder Register'
WHERE NOT EXISTS (
    SELECT 1 FROM ui_modals WHERE name = 'Stakeholder Register'
);
