-- Create supervisor_types table and modal
CREATE TABLE IF NOT EXISTS supervisor_types (
    id SERIAL PRIMARY KEY,
    name varchar(100) NOT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO supervisor_types (name)
VALUES ('branch manager'),('supervisor'),('department manager'),('work-id manager')
ON CONFLICT DO NOTHING;

INSERT INTO ui_modals (name, config, tenant_id, branch_id)
SELECT 'Supervisor Types', '{"title":"Supervisor Types","body":[{"type":"table","options":{"tableName":"supervisor_types","showGlobalSearch":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM ui_modals WHERE name = 'Supervisor Types');

ALTER TABLE employees
    ADD COLUMN IF NOT EXISTS supervisor_type VARCHAR(50);

-- Add fields to Time Clock form (id 13)
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, options_table, options_label_column, options_value_column, order_index)
SELECT 13, 'supervisor_type', 'supervisor_type', 'supervisor_type', (SELECT id FROM element_types WHERE name='dropdown' LIMIT 1), 'supervisor_types', 'name', 'name', 9
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id=13 AND field_name='supervisor_type');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, options_table, options_label_column, options_value_column, order_index)
SELECT 13, 'supervisor_id', 'supervisor_id', 'supervisor_id', (SELECT id FROM element_types WHERE name='dropdown' LIMIT 1), 'employees', 'name', 'id', 10
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id=13 AND field_name='supervisor_id');

-- Add fields to Timesheet form (id 14)
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, options_table, options_label_column, options_value_column, order_index)
SELECT 14, 'supervisor_type', 'supervisor_type', 'supervisor_type', (SELECT id FROM element_types WHERE name='dropdown' LIMIT 1), 'supervisor_types', 'name', 'name', 7
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id=14 AND field_name='supervisor_type');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, options_table, options_label_column, options_value_column, order_index)
SELECT 14, 'supervisor_id', 'supervisor_id', 'supervisor_id', (SELECT id FROM element_types WHERE name='dropdown' LIMIT 1), 'employees', 'name', 'id', 8
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id=14 AND field_name='supervisor_id');
