-- Create drivers table and modal

-- Table: drivers
CREATE TABLE drivers (
    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 drivers_tenant_idx ON drivers (tenant_id);

-- Driver form and modal
INSERT INTO forms (id, name, description, mapped_table, tenant_id, branch_id)
SELECT 45, 'Driver', 'Driver details', 'drivers', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 45);

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 45, 'full_name', 'full_name', 'full_name', 1, 1
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 45 AND field_name = 'full_name');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 45, 'email', 'email', 'email', 1, 2
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 45 AND field_name = 'email');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 45, 'phone', 'phone', 'phone', 1, 3
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 45 AND field_name = 'phone');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 45, '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 = 45 AND field_name = 'password');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 45, 'date_joined', 'date_joined', 'date_joined', 3, 5
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 45 AND field_name = 'date_joined');

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

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