-- Create fleet_engineers table and modal

-- Table: fleet_engineers
CREATE TABLE fleet_engineers (
    id SERIAL,
    tenant_id INTEGER DEFAULT NULL,
    full_name VARCHAR(255) DEFAULT NULL,
    email VARCHAR(255) DEFAULT NULL,
    phone VARCHAR(50) DEFAULT NULL,
    date_joined DATE DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);
CREATE INDEX fleet_engineers_tenant_idx ON fleet_engineers (tenant_id);

-- Fleet Engineer form and modal
INSERT INTO forms (id, name, description, mapped_table, tenant_id, branch_id)
SELECT 46, 'Fleet Engineer', 'Fleet engineer details', 'fleet_engineers', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 46);

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 46, 'full_name', 'full_name', 'full_name', 1, 1
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 46 AND field_name = 'full_name');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 46, 'email', 'email', 'email', 1, 2
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 46 AND field_name = 'email');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 46, 'phone', 'phone', 'phone', 1, 3
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 46 AND field_name = 'phone');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 46, 'password', 'password', NULL, (SELECT id FROM element_types WHERE name='text' LIMIT 1), 4
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 46 AND field_name = 'password');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 46, 'date_joined', 'date_joined', 'date_joined', 3, 5
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 46 AND field_name = 'date_joined');

INSERT INTO table_views (form_id, name, columns, sort_by, sort_dir)
SELECT 46, 'Default', '["full_name","email","phone","date_joined"]', 'full_name', 'ASC'
WHERE NOT EXISTS (SELECT 1 FROM table_views WHERE form_id = 46 AND name = 'Default');

INSERT INTO ui_modals (name, config, tenant_id, branch_id, icon, aria_label, description)
SELECT 'Fleet Engineers', '{"title":"Fleet Engineers","header":[{"type":"button","label":"Add Fleet Engineer","formName":"Fleet Engineer"},{"type":"button","label":"Detail View","onClick":"openSelectedDetail"}],"body":[{"type":"table","options":{"tableName":"fleet_engineers","showGlobalSearch":false,"addButton":false,"rowClick":"selectRecord"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' , NULL, NULL, 'fa-screwdriver-wrench', 'Fleet Engineers', 'Fleet Engineers'
WHERE NOT EXISTS (SELECT 1 FROM ui_modals WHERE name = 'Fleet Engineers');

-- Ensure Fleet Engineers group exists
INSERT INTO groups (name, description)
SELECT 'Fleet Engineers', 'Fleet maintenance crew'
WHERE NOT EXISTS (SELECT 1 FROM groups WHERE name = 'Fleet Engineers');
