-- Default application settings
INSERT INTO settings (
    name, value, category, tenant_id, branch_id
) VALUES
    ('default_currency', 'FJD', 'finance', NULL, NULL),
    ('timezone', 'Pacific/Fiji', 'system', 1, 1),
    ('policy_publish_webhook', '', 'system', NULL, NULL),
    ('policy_update_webhook', '', 'system', NULL, NULL);

-- Ensure the sequence for settings continues from the inserted values
SELECT setval('settings_id_seq', (SELECT MAX(id) FROM settings));

-- Default hazard types
INSERT INTO ohs_hazard_types (name, tenant_id, branch_id) VALUES
    ('Physical', NULL, NULL),
    ('Chemical', NULL, NULL),
    ('Biological', NULL, NULL),
    ('Psychosocial', NULL, NULL),
    ('Ergonomic', NULL, NULL);

-- Ensure the sequence for ohs_hazard_types continues from the inserted values
SELECT setval('ohs_hazard_types_id_seq', (SELECT MAX(id) FROM ohs_hazard_types));

-- Default incident types
INSERT INTO ohs_incident_types (name, form_id, tenant_id, branch_id) VALUES
    ('Injury', 20, NULL, NULL),
    ('Property Damage', 13, NULL, NULL),
    ('Environmental', 14, NULL, NULL),
    ('Near Miss', 15, NULL, NULL),
    ('First Aid', 16, NULL, NULL),
    ('Medical Treatment', 17, NULL, NULL),
    ('LTI', 18, NULL, NULL),
    ('Fatality', 19, NULL, NULL),
    ('Field Incident', 105, NULL, NULL);

-- Ensure the sequence for ohs_incident_types continues from the inserted values
SELECT setval('ohs_incident_types_id_seq', (SELECT MAX(id) FROM ohs_incident_types));

-- Default safety incident types
INSERT INTO safety_incident_types (name, form_id, tenant_id, branch_id) VALUES
    ('Minor Injury', 20, NULL, NULL),
    ('Near Miss', 15, NULL, NULL),
    ('Property Damage', 13, NULL, NULL),
    ('Environmental', 14, NULL, NULL),
    ('Other', 97, NULL, NULL),
    ('Field Incident', 108, NULL, NULL);

-- Ensure the sequence for safety_incident_types continues from the inserted values
SELECT setval('safety_incident_types_id_seq', (SELECT MAX(id) FROM safety_incident_types));

-- Default incident statuses
INSERT INTO ohs_incident_statuses (name, purpose, tenant_id, branch_id) VALUES
    ('Reported / Logged', 'Capture the initial incident details', NULL, NULL),
    ('Under Review / Triage', 'Assess seriousness and need for investigation', NULL, NULL),
    ('Investigation in Progress', 'Conduct full investigation and identify root causes', NULL, NULL),
    ('Action Required', 'Implement corrective and preventive measures', NULL, NULL),
    ('Awaiting Review', 'Final review before closure', NULL, NULL),
    ('Closed / Resolved', 'All actions complete, incident formally closed', NULL, NULL),
    ('Reopened', 'Reevaluate due to new info or recurrence', NULL, NULL),
    ('Deferred / On Hold', 'Temporarily paused due to dependencies', NULL, NULL);

-- Ensure the sequence for ohs_incident_statuses continues from the inserted values
SELECT setval('ohs_incident_statuses_id_seq', (SELECT MAX(id) FROM ohs_incident_statuses));

-- Default severity levels
INSERT INTO ohs_severities (name, tenant_id, branch_id) VALUES
    ('Minor', NULL, NULL),
    ('Moderate', NULL, NULL),
    ('Major', NULL, NULL),
    ('Severe', NULL, NULL),
    ('Critical', NULL, NULL);

-- Ensure the sequence for ohs_severities continues from the inserted values
SELECT setval('ohs_severities_id_seq', (SELECT MAX(id) FROM ohs_severities));

-- Default district levels
INSERT INTO districts (name, tenant_id, branch_id) VALUES
    ('Central', NULL, NULL),
    ('Eastern', NULL, NULL),
    ('Northern', NULL, NULL),
    ('Western', NULL, NULL);

-- Ensure the sequence for districts continues from the inserted values
SELECT setval('districts_id_seq', (SELECT MAX(id) FROM districts));

-- Default inspection types
INSERT INTO ohs_inspection_types (name, form_id, tenant_id, branch_id) VALUES
    ('General Site', 91, NULL, NULL),
    ('Plant & Equipment', 92, NULL, NULL),
    ('Confined Space Entry', 93, NULL, NULL),
    ('Field Work', 94, NULL, NULL),
    ('Vehicle', 95, NULL, NULL);

-- Ensure the sequence for ohs_inspection_types continues from the inserted values
SELECT setval('ohs_inspection_types_id_seq', (SELECT MAX(id) FROM ohs_inspection_types));

-- Default work types for time entries
INSERT INTO work_types (name) VALUES
    ('Research'),
    ('Drafting'),
    ('Review'),
    ('Client Meeting'),
    ('Court Preparation'),
    ('Court Appearance'),
    ('Document Preparation'),
    ('Administrative');


-- Default calendar categories for kanban boards
INSERT INTO calendar_categories (name, tenant_id, branch_id)
VALUES
  ('WorkCOM Inspections', NULL, NULL),
  ('OHS Inspections', NULL, NULL),
  ('OHS Incidents', NULL, NULL),
  ('Safety Incidents', NULL, NULL);

-- Ensure the sequence for calendar_categories continues from the inserted values
SELECT setval('calendar_categories_id_seq', (SELECT MAX(id) FROM calendar_categories));

-- Default kanban board for WorkCOM inspections
INSERT INTO kanban_boards (project_id, name, calendar_category_id)
VALUES
  (1, 'WorkCOM Inspections', (SELECT id FROM calendar_categories WHERE name='WorkCOM Inspections' LIMIT 1)),
  (1, 'OHS Inspections', (SELECT id FROM calendar_categories WHERE name='OHS Inspections' LIMIT 1)),
  (1, 'OHS Incidents', (SELECT id FROM calendar_categories WHERE name='OHS Incidents' LIMIT 1)),
  (1, 'Safety Incidents', (SELECT id FROM calendar_categories WHERE name='Safety Incidents' LIMIT 1));

-- Ensure the sequence for kanban_boards continues from the inserted values
SELECT setval('kanban_boards_id_seq', (SELECT MAX(id) FROM kanban_boards));


INSERT INTO kanban_columns (board_id, title, position)
VALUES
  ((SELECT id FROM kanban_boards WHERE name='WorkCOM Inspections' LIMIT 1), 'Pending', 1),
  ((SELECT id FROM kanban_boards WHERE name='WorkCOM Inspections' LIMIT 1), 'In Progress', 2),
  ((SELECT id FROM kanban_boards WHERE name='WorkCOM Inspections' LIMIT 1), 'Completed', 3),
  ((SELECT id FROM kanban_boards WHERE name='OHS Inspections' LIMIT 1), 'Pending', 1),
  ((SELECT id FROM kanban_boards WHERE name='OHS Inspections' LIMIT 1), 'In Progress', 2),
  ((SELECT id FROM kanban_boards WHERE name='OHS Inspections' LIMIT 1), 'Completed', 3),
  ((SELECT id FROM kanban_boards WHERE name='OHS Incidents' LIMIT 1), 'Pending', 1),
  ((SELECT id FROM kanban_boards WHERE name='OHS Incidents' LIMIT 1), 'In Progress', 2),
  ((SELECT id FROM kanban_boards WHERE name='OHS Incidents' LIMIT 1), 'Completed', 3),
  ((SELECT id FROM kanban_boards WHERE name='Safety Incidents' LIMIT 1), 'Pending', 1),
  ((SELECT id FROM kanban_boards WHERE name='Safety Incidents' LIMIT 1), 'In Progress', 2),
  ((SELECT id FROM kanban_boards WHERE name='Safety Incidents' LIMIT 1), 'Completed', 3);


-- Ensure the sequence for kanban_columns continues from the inserted values
SELECT setval('kanban_columns_id_seq', (SELECT MAX(id) FROM kanban_columns));

-- Seed tasks for the default WorkCOM kanban board
INSERT INTO kanban_tasks (column_id, title, description, assignee_id, due_date, status)
VALUES
  ((SELECT id FROM kanban_columns WHERE title='Pending' AND board_id=(SELECT id FROM kanban_boards WHERE name='WorkCOM Inspections' LIMIT 1) LIMIT 1),
   'Schedule initial inspection', 'Arrange inspection date with site manager', 1, '2025-07-01', 'pending'),
  ((SELECT id FROM kanban_columns WHERE title='In Progress' AND board_id=(SELECT id FROM kanban_boards WHERE name='WorkCOM Inspections' LIMIT 1) LIMIT 1),
   'Conduct site inspection', 'Review compliance checklist on-site', 1, '2025-07-10', 'in_progress'),
  ((SELECT id FROM kanban_columns WHERE title='Completed' AND board_id=(SELECT id FROM kanban_boards WHERE name='WorkCOM Inspections' LIMIT 1) LIMIT 1),
   'Submit inspection report', 'Compile results and upload to system', 1, '2025-07-15', 'pending');

-- Ensure the sequence for kanban_tasks continues from the inserted values
SELECT setval('kanban_tasks_id_seq', (SELECT MAX(id) FROM kanban_tasks));

INSERT INTO user_board_settings (user_id, board_id, column_order, hidden_columns)
VALUES (
    1,
    (SELECT id FROM kanban_boards WHERE name='WorkCOM Inspections' LIMIT 1),
    '[1,2,3]',
    '[]'
  ),
  (
    1,
    (SELECT id FROM kanban_boards WHERE name='OHS Inspections' LIMIT 1),
    '[1,2,3]',
    '[]'
  ),
  (
    1,
    (SELECT id FROM kanban_boards WHERE name='OHS Incidents' LIMIT 1),
    '[1,2,3]',
    '[]'
  ),
  (
    1,
    (SELECT id FROM kanban_boards WHERE name='Safety Incidents' LIMIT 1),
    '[1,2,3]',
    '[]'
  );

-- Ensure the sequence for user_board_settings continues from the inserted values
SELECT setval('user_board_settings_id_seq', (SELECT MAX(id) FROM user_board_settings));

-- Calendar events for seeded kanban tasks
INSERT INTO calendar_events (title, start_time, kanban_task_id, category_id, tenant_id, branch_id)
SELECT t.title, t.due_date, t.id, b.calendar_category_id, NULL, NULL
FROM kanban_tasks t
JOIN kanban_columns c ON t.column_id = c.id
JOIN kanban_boards  b ON c.board_id = b.id;

-- Ensure the sequence for calendar_events continues from the inserted values
SELECT setval('calendar_events_id_seq', (SELECT MAX(id) FROM calendar_events));

-- Default form element types
INSERT INTO element_types (name, html_type, sql_type)
VALUES
  ('text', 'text', 'VARCHAR(255)'),
  ('number', 'number', 'INT'),
  ('date', 'date', 'DATE'),
  ('dropdown', 'select', 'VARCHAR(255)'),
  ('checkbox', 'checkbox', 'BOOLEAN'),
  ('checkbox-table', 'checkbox-table', NULL),
  ('input-table', 'input-table', NULL),
  ('textarea', 'textarea', 'TEXT'),
  ('file', 'file', 'VARCHAR(255)'),
  ('datetime', 'datetime-local', 'TIMESTAMP'),
  ('group', 'group', NULL),
  ('section', 'section', NULL),
  ('select-addable', 'select', 'VARCHAR(255)'),
  ('multiselect-search', 'multiselect', 'VARCHAR(255)'),
  ('email', 'email', 'VARCHAR(255)'),
  ('text-multiple', 'text-multiple', 'VARCHAR(255)'),
  ('hidden', 'hidden', 'INT'),
  ('rating', 'rating', 'INT'),
  ('button', 'button', NULL),
  ('gps', 'gps', 'VARCHAR(255)'),
  ('camera', 'camera', 'VARCHAR(255)'),
  ('dynamic-table', 'dynamic-table', NULL),
  ('select-search', 'select-search', 'VARCHAR(255)');

-- Ensure the sequence for element_types continues from the inserted values
SELECT setval('element_types_id_seq', (SELECT MAX(id) FROM element_types));

-- Service Types modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Service Types',
  '{"title":"Service Types","header":[{"type":"button","label":"Add Service Type","formName":"Service Type"}],"body":[{"type":"table","options":{"tableName":"service_types","showGlobalSearch":false,"addButton":false,"editFormId":31}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-wrench',
  'Service Types',
  'Service Types'
);


-- Vehicles modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Vehicles',
  '{"title":"Vehicles","header":[{"type":"button","label":"Add Vehicle","formName":"Vehicle Registration"}],"body":[{"type":"table","options":{"tableName":"vehicles","showGlobalSearch":false,"addButton":false,"editFormId":29}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-car',
  'Vehicles',
  'Vehicles'
);


-- Attendance modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Attendance',
  '{"title":"Attendance","body":[{"type":"table","options":{"tableName":"attendance","searchInputTypes":{"employee":"text","date":"date","timeIn":"time","timeOut":"time","timeInLat":"number","timeInLng":"number","timeOutLat":"number","timeOutLng":"number"},"columnsToShow":["employee","date","timeIn","timeOut","hoursWorked","overtime"]}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-user-check',
  'Attendance',
  'Attendance'
);


-- Attendance Dashboard modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Attendance Dashboard',
  '{"title":"Attendance Dashboard","body":[{"type":"html","id":"attendanceDash","html":"<div class=\"grid grid-cols-2 gap-4\"><div class=\"bg-base-100 rounded-lg shadow p-4 text-center\"><h3 class=\"text-sm font-medium\">Clocked In</h3><p id=\"adClockIn\" class=\"text-2xl font-bold\">0</p></div><div class=\"bg-base-100 rounded-lg shadow p-4 text-center\"><h3 class=\"text-sm font-medium\">Clocked Out</h3><p id=\"adClockOut\" class=\"text-2xl font-bold\">0</p></div></div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-chart-pie',
  'Attendance Dashboard',
  'Attendance Dashboard'
);


-- Time Clocks modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Time Clocks',
  '{"title":"Time Clocks","mapLibraries":{"provider":"google"},"body":[{"type":"table","options":{"tableName":"time_clocks","searchInputTypes":{"user_id":"number","clock_type":{"type":"select","options":["in","out"]},"lat":"number","lng":"number","location_id":"number","supervisor_id":"number","supervisor_type":"text","work_id":"number","security_type":"text","clocked_at":"datetime-local"},"columnsToShow":["user_id","clock_type","location_id","stationApproved","supervisor_id","supervisor_type","work_id","security_type","clocked_at"]}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-clipboard-user',
  'Time Clocks',
  'Time Clocks'
);


-- Time Clock Locations modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Time Clock Locations',
  '{"title":"Time Clock Locations","mapLibraries":{"provider":"google","libraries":"drawing"},"body":[{"type":"table","options":{"tableName":"time_clock_locations","searchInputTypes":{"name":"text","shape":"text","radius_m":"number","assigned_to_type":"text"},"columnsToShow":["name","shape","radius_m","assigned_to_type"]}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-map-location-dot',
  'Time Clock Locations',
  'Time Clock Locations'
);


-- Timesheet Approval modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Timesheet Approval',
  '{"title":"Timesheet Approval","body":[{"type":"table","options":{"tableName":"pending_timesheets","tableUrl":"../api/timesheets/pending","columnsToShow":["id","user_id","date","standard_hours","overtime_hours","break_hours"],"apiRowActions":[{"buttonText":"Approve","apiUrl":"../api/timesheets/approve/{id}"}]}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-clipboard-check',
  'Timesheet Approval',
  'Timesheet Approval'
);


-- Clock Adjustment modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Clock Adjustment',
  '{"title":"Clock Adjustment","body":[{"type":"table","options":{"tableName":"time_clocks","searchInputTypes":{"clocked_at":"datetime-local","approval_comment":"text"},"columnsToShow":["user_id","clock_type","clocked_at","approval_comment"],"addButton":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-clock',
  'Clock Adjustment',
  'Clock Adjustment'
);

-- Timesheet Summary modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Timesheet Summary',
  '{"title":"Timesheet Summary","body":[{"type":"table","options":{"tableName":"timesheet_summary","tableUrl":"../api/timesheets/summary","columnsToShow":["user_id","approved_hours","rejected_hours","comments"],"addButton":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-list-check',
  'Timesheet Summary',
  'Timesheet Summary'
);

-- User Groups modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'User Groups',
  '{"title":"User Groups","header":[{"type":"button","label":"Add Group","formName":"User Group"}],"body":[{"type":"table","options":{"tableName":"groups","showGlobalSearch":true,"addButton":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-users',
  'User Groups',
  'User Groups'
);

-- Group Members modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Group Members',
  '{"title":"Group Members","header":[{"type":"button","label":"Add Member","formName":"Group Member"}],"body":[{"type":"table","options":{"tableName":"user_group_memberships","showGlobalSearch":true,"addButton":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-user-group',
  'Group Members',
  'Group Members'
);

-- Form Categories manager modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'form_group_manager',
  '{"title":"Form Categories","body":[{"type":"table","options":{"tableName":"form_categories","editFormId":68}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-layer-group',
  'Form Categories',
  'Manage form categories'
);


-- Users modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Users',
  '{"title":"Users","header":[{"type":"button","label":"Add User","formName":"User"}],"body":[{"type":"table","options":{"tableName":"users","showGlobalSearch":true,"addButton":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-users-gear',
  'Manage Users',
  'Manage Users'
);


-- Dashboard card permissions modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'dashboard_card_permissions',
  '{"title":"Dashboard Card Permissions","body":[{"type":"html","html":"<select id=\"dcpTenantSelect\"><option value=\"\">All</option></select><div class=\"tabs mb-2\" id=\"dcp-tabs\"><a class=\"tab tab-bordered tab-active\" data-tab=\"groups\">Groups</a><a class=\"tab tab-bordered\" data-tab=\"users\">Users</a></div>"},{"type":"table","id":"dcpGroups","options":{"tableName":"groups","addButton":false,"showGlobalSearch":false,"actionsHeader":"Allow"}},{"type":"table","id":"dcpUsers","options":{"tableName":"users","addButton":false,"showGlobalSearch":false,"actionsHeader":"Allow"}}],"footer":[{"type":"button","label":"Save","onClick":"saveDashboardCardPerms"},{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-id-badge',
  'Card Permissions',
  'Card Permissions'
);


-- Dashboard card permissions manager modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'dashboard_card_permissions_manager',
  '{"title":"Card Permissions Manager","body":[{"type":"html","html":"<select id=\"dcpTenantSelect\"></select><select id=\"dcpmGroupSelect\"></select><select id=\"dcpmUserSelect\"></select>"},{"type":"html","id":"dcpmCards","html":""},{"type":"html","html":"<div id=\"dcpm-detail\" class=\"hidden\"></div>"}],"footer":[{"type":"button","label":"Save","onClick":"saveCardPermissions"},{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-id-badge',
  'Card Permissions Manager',
  'Card Permissions Manager'
);


-- Workflow Builder modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'workflow_builder',
  '{"title":"Workflow Builder","body":[{"type":"html","id":"wfBuilderContainer","html":""}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-sitemap',
  'Workflow Builder',
  'Workflow Builder'
);


-- Trip Logs modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Trip Logs',
  '{"title":"Trip Logs","header":[{"type":"button","label":"Map View","onClick":"openTripMapView"}],"body":[{"type":"table","options":{"tableName":"vehicle_trips","addButton":false,"rowClick":"selectRecord","showGlobalSearch":true}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-road',
  'Trip Logs',
  'Trip Logs'
);

-- Vehicle Reports modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Vehicle Reports',
  '{"title":"Vehicle Reports","header":[{"type":"button","label":"Add Report","formName":"Vehicle Issue Report"},{"type":"button","label":"Detail View","onClick":"openSelectedDetail"}],"body":[{"type":"table","id":"vehicleReports","options":{"tableName":"vehicle_reports","showGlobalSearch":false,"addButton":false,"rowClick":"selectRecord","filters":{"status":"open"},"apiRowActions":[{"buttonText":"Accept","apiUrl":"../api/vehicle_reports/{id}/assign"},{"buttonText":"Confirm","apiUrl":"../api/vehicle_reports/{id}/confirm"}]}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-clipboard-list',
  'Vehicle Reports',
  'Vehicle Reports'
);


-- Fleet Analytics modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Fleet Analytics',
  '{"title":"Fleet Analytics","body":[{"type":"html","id":"fleetAnalyticsRoot","html":"<div id=\"fleetAnalytics\"></div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-truck-fast',
  'Fleet Analytics',
  'Fleet Analytics'
);

-- Vehicle Downtime modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Vehicle Downtime',
  '{"title":"Vehicle Downtime","header":[{"type":"button","label":"Add Downtime","formName":"Vehicle Downtime"}],"body":[{"type":"table","options":{"tableName":"vehicle_downtime","showGlobalSearch":false,"addButton":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-clock',
  'Vehicle Downtime',
  'Vehicle Downtime'
);


-- Clients modal
INSERT INTO modules (module_id, name, config, org_id, branch_id, icon, aria_label, description)
SELECT '0194a8b2-aaaa-7123-8123-123456789ac9'::uuid, 'Clients', 
  '{  "title":"Clients",
      "header":[
        {"type":"button","label":"Add Client","formName":"Client"},
        {"type":"button","label":"Detail View","onClick":"openSelectedDetail"}
      ],
      "body":[
        { "type":"table",
          "options":
          { "tableName":"clients",
            "showGlobalSearch":false,
            "columnsToShow":["name","council","phone","email"],
            "addButton":false,
            "rowClick":"selectRecord"
          }
        }],
        "footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', 
NULL, NULL, 'fa-users', 'Clients', 'Clients'
WHERE NOT EXISTS (SELECT 1 FROM modules WHERE name = 'Clients');


-- Cases modal
INSERT INTO modules (module_id, name, config, org_id, branch_id, icon, aria_label, description)
SELECT '0194a8b2-bbbb-7123-8123-123456789aca'::uuid, 'Cases', 
  '{  "title":"Cases",
      "header":[
      {"type":"button","label":"Add Case","formName":"Case"},
      {"type":"button","label":"Detail View","onClick":"openSelectedDetail"}],
      "body":[
        {"type":"table",
        "options":
        { "tableName":"cases",
          "showGlobalSearch":false,
          "addButton":false,
          "rowClick":"selectRecord",
          "columnsToShow": ["client_name","case_number","title","lead_rep_name","added_by"]
        }}],
      "footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-folder-open', 'Cases', 'Cases'
WHERE NOT EXISTS (SELECT 1 FROM modules WHERE name = 'Cases');



INSERT INTO modules (module_id, name, config, org_id, branch_id, icon, aria_label, description)
SELECT '0194a8b2-8888-7123-8123-123456789ac7'::uuid,
       'Case Management',
       $json$
{
  "title": "Case Management",
  "header": [
    {
      "type": "filter-select",
      "options": {
        "source_table": "cases",
        "source_column": "case_id",
        "display_column": "title",
        "data_column": "case_id",
        "data_column_id": "selected_case_id",
        "placeholder": "Search cases by number, title, or client...",
        "search_fields": ["case_number","title","client_name"],
        "max_results": 25,
        "debounce_ms": 250,
        "scope": "admin_user",
        "userField": "lead_rep_id"
      }
    },
    { "type": "button", "label": "Add Client", "formName": "Client" },
    { "type": "button", "label": "Edit Client", "onClick": "openEditForm" },
    { "type": "button", "label": "Add Case", "formName": "Case" },
    { "type": "button", "label": "Edit Case", "onClick": "openEditForm" },
    { "type": "button", "label": "Add Hour", "formName": "Time Entries", "prefillFilter": "case_id", "source_column": "case_id" },
    { "type": "button", "label": "Add Expense", "formName": "Case Expense", "prefillFilter": "case_id" },
    { "type": "button", "label": "Add One Time Cost", "formName": "One Time Cost", "prefillFilter": "case_id" },
    { "type": "button", "label": "Create Cost Schedule", "onClick": "createCostSchedule" },
    { "type": "button", "label": "Create Invoice", "onClick": "createCostSchedule" },
    { "type": "button", "label": "Record Payment", "onClick": "recordGeneralPayment" }
  ],
  "body": [
    {
      "type": "tabs",
      "tabs": [
        {
          "label": "Details",
          "elements": [
            { "type": "detail-view", "viewId": 2, "filterField": "case_id", "tableName": "cases" }
          ]
        },
        {
          "label": "Instructions",
          "elements": [
            { "type": "button", "label": "Add Instruction", "formName": "Case Instruction", "prefillFilter": "case_id" },
            { "type": "table", "options": { "tableName": "case_instructions", "inlineEdit": true, "showGlobalSearch": false, "addButton": false, "filterField": "case_id" } }
          ]
        },
        {
          "label": "Documents",
          "elements": [
            { "type": "documents", "options": { "filterField": "case_id" } }
          ]
        },
        {
          "label": "Hours",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "time_entries",
                "inlineEdit": true,
                "ownerEditOnly": true,
                "tableUrl": "../api/time_entries",
                "showGlobalSearch": false,
                "addButton": false,
                "editFormId": 38,
                "columnsToShow": ["Council","date","hours","total","work_type","description"],
                "filterField": "reference_id",
                "listenColumn": "case_id",
                "multipleSelect": true
              }
            }
          ]
        },
        {
          "label": "Expenses",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "expenses",
                "inlineEdit": true,
                "ownerEditOnly": true,
                "showGlobalSearch": false,
                "addButton": false,
                "editFormId": 48,
                "columnsToShow": ["council","amount","description","billable"],
                "filterField": "reference_id",
                "listenColumn": "case_id",
                "multipleSelect": true
              }
            }
          ]
        },
        {
          "label": "One Time Costs",
          "elements": [
            { "type": "button", "label": "Add One Time Cost", "formName": "One Time Cost", "prefillFilter": "case_id" },
            {
              "type": "table",
              "options": {
                "tableName": "one_time_service_costs",
                "inlineEdit": true,
                "ownerEditOnly": true,
                "showGlobalSearch": false,
                "addButton": false,
                "editFormId": 58,
                "columnsToShow": ["date","amount","description","billable"],
                "filterField": "reference_id",
                "listenColumn": "case_id",
                "multipleSelect": true
              }
            }
          ]
        },
        {
          "label": "Invoices",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "invoices",
                "inlineEdit": true,
                "showGlobalSearch": false,
                "addButton": false,
                "filterField": "reference_id",
                "listenColumn": "case_id",
                "filters": { "reference_table": "cases" },
                "columnsToShow": ["invoice_date", "invoice_number", "amount", "amount_paid", "balance"],
                "rowActions": [
                  { "label": "Edit", "onClick": "openCaseInvoiceEditForm" },
                  { "label": "Record Payment", "onClick": "recordInvoicePayment" },
                  { "label": "Download", "onClick": "downloadCaseInvoice" },
                  { "label": "Preview", "onClick": "previewCaseInvoice" }
                ]
              }
            }
          ]
        },
        {
          "label": "Payments",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "payments",
                "inlineEdit": true,
                "showGlobalSearch": false,
                "addButton": false,
                "filterField": "reference_id",
                "listenColumn": "case_id",
                "filters": { "reference_table": "cases" }
              }
            }
          ]
        }
      ]
    }
  ],
  "footer": [
    { "type": "button", "label": "Close", "onClick": "closeModal" }
  ]
}
 $json$::jsonb,
       NULL,
       NULL,
       'fa-briefcase',
       'Case Management',
       'Case Management'
WHERE NOT EXISTS (SELECT 1 FROM modules WHERE name = 'Case Management');

-- Ensure Case Detail modal has invoice download and preview actions
--UPDATE modules SET config='{"title":"Case Detail","body":[{"type":"tabs","tabs":[{"label":"Hours","elements":[{"type":"button","label":"Add Hour","onClick":"openCaseHourForm"},{"type":"table-view","viewId":100,"recordMap":"formatCaseHourRow"}]},{"label":"Documents","elements":[{"type":"button","label":"Upload Document","onClick":"uploadCaseDocument"},{"type":"table","id":"caseDocsTbl","options":{"tableName":"documents","addButton":false,"showGlobalSearch":false}}]},{"label":"Invoices","elements":[{"type":"button","label":"Create Invoice","onClick":"openCaseInvoiceForm"},{"type":"table","id":"caseInvoicesTbl","options":{"tableName":"invoices","addButton":false,"showGlobalSearch":false,"rowActions":[{"label":"Download","onClick":"downloadCaseInvoice"},{"label":"Preview","onClick":"previewCaseInvoice"}]}}]},{"label":"Payments","elements":[{"type":"button","label":"Record Payment","onClick":"openCasePaymentForm"},{"type":"table","id":"casePaymentsTbl","options":{"tableName":"payments","addButton":false,"showGlobalSearch":false,"filterField":"reference_id","filters":{"reference_table":"cases"}}}]}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' WHERE name='Case Detail';

-- Time Entries & Expenses modal
INSERT INTO modules (module_id, name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  '0194a8b2-9999-7123-8123-123456789ac8'::uuid, 'time_entries_expenses',
  '{
    "title":"Time Entries & Expenses",
    "header":[
      {"type":"filter-select",
        "options":{
          "source_table":"users",
          "source_column":"user_id",
          "display_column":"full_name",
          "default_label":"All",
          "data_column":"user_id",
          "search_fields":["full_name", "email"],
          "placeholder":"Search users..."
        }
      },
      {"type":"filter-select",
        "options":{
          "data_column":"approved",
          "options":[
            {"label":"All","value":""},
            {"label":"Approved","value":true},
            {"label":"Pending","value":false}
          ],
          "value": false
        }
      },
      {"type":"filter-date-range",
        "options":{"data_column":"date"}
      }
    ],
    "body":[
      {"type":"tabs",
        "tabs":[
          {"label":"Time Entries","elements":[
            {"type":"table","options":{
              "tableName":"time_entries",
              "inlineEdit":true,
              "showGlobalSearch":false,
              "addButton":false,
              "editFormId":38,
              "filterField":["user_id","approved","date"],
              "filters":{"approved":false},
              "columnsToShow":["council", "client_name","case_name", "hours", "total", "description", "billable","approved","approval_comment"]
            }}
          ]},
          {"label":"Expenses","elements":[
            {"type":"table","options":{
              "tableName":"expenses",
              "inlineEdit":true,
              "showGlobalSearch":false,
              "addButton":false,
              "editFormId": 48,
              "filterField":["user_id","approved","date"],
              "filters":{"approved":false},
              "columnsToShow":["council", "client_name","case_name","amount","description","billable","approved","approval_comment"]
            }}
          ]},
          {"label":"One Time Costs","elements":[
            {"type":"table","options":{
              "tableName":"one_time_service_costs",
              "inlineEdit":true,
              "editFormId": 58,
              "filterField":["user_id","approved","date"],
              "filters":{"approved":false},
              "columnsToShow":["council", "client_name", "case_name","amount","description","billable","approved","comments"]
            }}
          ]}
        ]
      }
    ],
    "footer":[{"type":"button","label":"Close","onClick":"closeModal"}]
  }',
  NULL, NULL, 'fa-clock', 'Time Entries & Expenses', 'Time Entries & Expenses'
);


-- Tenants modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description)
SELECT 'Tenants', '{"title":"Tenants","header":[{"type":"button","label":"Add Tenant","formName":"Tenant"}],"body":[{"type":"table","options":{"tableName":"tenants","showGlobalSearch":false,"addButton":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL, 'fa-users-gear', 'Tenant Admin', 'Tenant Admin'
WHERE NOT EXISTS (SELECT 1 FROM modules WHERE name = 'Tenants');

-- Admins modal definition
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description)
SELECT 'Admins', '{"title":"Admins","header":[{"type":"button","label":"Add Admin","formName":"Admin"}],"body":[{"type":"table","options":{"tableName":"users","filters":{"is_admin":"1"},"showGlobalSearch":false,"addButton":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL, 'fa-user-shield', 'Admins', 'Admins'
WHERE NOT EXISTS (SELECT 1 FROM modules WHERE name = 'Admins');

-- Default workflow to launch the Dashboard Card Permissions modal
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
  'Dashboard Card Permissions',
  'dashboard.card.permissions',
  '{"conditions":[]}',
  TRUE
);


INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
VALUES (
  (SELECT id FROM workflows WHERE name='Dashboard Card Permissions' LIMIT 1),
  1,
  'open_modal',
  '{"modal":"dashboard_card_permissions"}'
);


-- Default vehicle makes and models
INSERT INTO vehicle_makes (name, tenant_id, branch_id)
VALUES ('Toyota', NULL, NULL);


INSERT INTO vehicle_models (make_id, name, tenant_id, branch_id)
VALUES ((SELECT id FROM vehicle_makes WHERE name='Toyota' LIMIT 1), 'Corolla',NULL, NULL);


-- Default vehicle service workflow to schedule maintenance after registration
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES ('Vehicle Service Setup', 'vehicle.registered', '{"conditions": []}', TRUE);


INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
VALUES (
  (SELECT id FROM workflows WHERE name='Vehicle Service Setup' LIMIT 1),
  1,
  'schedule_services',
  '{}'
);


-- Table Builder modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Table Builder',
  '{"title":"Table Builder","body":[{"type":"html","html":"<div class=\"mb-2\"><label class=\"label\">Table Name</label><input id=\"tbTableName\" class=\"input input-bordered w-full\"/></div>"},{"type":"html","html":"<div class=\"mt-4\"><button id=\"addColumnBtn\" type=\"button\" class=\"btn btn-sm mb-2\">Add Column</button><table id=\"tbColumns\" class=\"table table-sm w-full\"><thead><tr><th>Name</th><th>Type</th><th>Default</th><th>Required</th><th></th></tr></thead><tbody></tbody></table></div>"},{"type":"html","html":"<div class=\"mt-4\"><button id=\"addRelationBtn\" type=\"button\" class=\"btn btn-sm mb-2\">Add Relationship</button><table id=\"tbRelations\" class=\"table table-sm w-full\"><thead><tr><th>Column</th><th>References Table</th><th>References Column</th><th></th></tr></thead><tbody></tbody></table></div>"}],"footer":[{"type":"button","label":"Save","onClick":"submitTableDefinition"},{"type":"button","label":"Close","onClick":"closeModal"}]}' ,
  NULL,
  NULL,
  'fa-table',
  'Table Builder',
  'Table Builder'
);

-- Graph Builder modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Graph Builder',
  '{"title":"Graph Builder","body":[{"type":"html","id":"graphBuilderMount","html":"<div id=\"graphBuilderMount\"></div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' ,
  NULL,
  NULL,
  'fa-chart-line',
  'Graph Builder',
  'Graph Builder'
);


-- Tenant Table Manager modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'tenant_table_manager',
  '{"title":"Tenant Table Manager","body":[{"type":"table","options":{"tableName":"tenant_tables","showGlobalSearch":false,"addButton":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-table-list',
  'Tenant Table Manager',
  'Tenant Table Manager'
);


-- Module Manager modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'module_manager',
  '{"title":"Module Manager","body":[{"type":"html","id":"moduleManagerContainer","html":""}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-cubes',
  'Module Manager',
  'Module Manager'
);

-- Permissions Manager modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'permissions_manager',
  '{"title":"Permissions Manager","body":[{"type":"html","id":"permissionsContainer","html":""}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-user-shield',
  'Permissions Manager',
  'Permissions Manager'
);


-- Vendor Quotes modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Vendor Quotes',
  '{"title":"Vendor Quotes","header":[{"type":"button","label":"Add Quote","formName":"Vendor Quote"}],"body":[{"type":"table","options":{"tableName":"vendor_quotes","showGlobalSearch":false,"addButton":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-file-lines',
  'Vendor Quotes',
  'Vendor Quotes'
);

-- Time Entries Entry modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Time Entries Entry',
  '{"title":"Time Entries Entry","header":[{"type":"button","label":"Add Hour","formName":"Time Entries"}],"body":[{"type":"table","options":{"tableName":"time_entries","tableUrl":"../api/time_entries","showGlobalSearch":false,"addButton":false,"editFormId":38,"editFormId":38}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-user-clock',
  'Time Entries Entry',
  'Time Entries Entry'
);


-- Workflow step type definitions
INSERT INTO workflow_step_types (type, description, metadata) VALUES
  ('start', 'Workflow entry point', '{"trigger":""}'),
  ('save_record', 'Insert or update a record', '{"table":"","data":{},"match":{}}'),
  ('sms', 'Send an SMS notification', '{"to":"","message":""}'),
  ('http_request', 'HTTP request to external API', '{"url":"","method":"GET"}'),
  ('alert', 'Send a push notification', '{"title":"","body":""}'),
  ('create_task', 'Add a kanban board task', '{"column_id":0,"title":""}'),
  ('add_calendar_event', 'Create a calendar event', '{"title":"","start_time":"","end_time":""}'),
  ('open_form', 'Open a form for the user', '{"form_id":0}'),
  ('condition', 'Evaluate workflow conditions', '{"conditions":[]}'),
  ('query_budget', 'Load remaining budget for a category', '{"category":"","context_key":"budget_amount"}'),
  ('map_fields', 'Copy values between context fields', '{"map":{}}'),
  ('load_record', 'Load a record into context', '{"table":"","id_key":"id","context_key":"record"}'),
  ('query_records', 'Query records from a table', '{"table":"","where":{},"context_key":"records"}'),
  ('for_each_record', 'Execute steps for each record', '{"list_key":"records","steps":[]}'),
  ('queue_notification', 'Queue a notification payload', '{"entity":"","record_id":"","event_type":"insert","payload":null}');


INSERT INTO modules (name,config,org_id,branch_id,icon,aria_label,description) VALUES
  ('Drivers','{"title":"Drivers","header":[{"type":"button","label":"Add Driver","formName":"Driver"}],"body":[{"type":"table","options":{"tableName":"users","showGlobalSearch":false,"filterField":"groups.name","filterValue":"Drivers"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-id-card','Drivers','Drivers');


INSERT INTO modules (name,config,org_id,branch_id,icon,aria_label,description) VALUES
  ('Lawyers','{"title":"Lawyers","header":[{"type":"button","label":"Add Lawyer","formName":"Lawyer"}],"body":[{"type":"table","options":{"tableName":"users","showGlobalSearch":false,"filterField":"groups.name","filterValue":"Lawyers"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-gavel','Lawyers','Lawyers');

INSERT INTO modules (module_id, name,config,org_id,branch_id,icon,aria_label,description) VALUES
  ('0194a8b2-7777-7123-8123-123456789ac6'::uuid, 'Employees','{"title":"Employee Management","body":[{"type":"custom","module":"Employees"}]}',NULL, NULL,'fa-users','Employees','Manage employees, rates, and types');

INSERT INTO modules (name,config,org_id,branch_id,icon,aria_label,description) VALUES
  ('Investigators','{"title":"Investigators","header":[{"type":"button","label":"Add Investigator","formName":"Investigator"}],"body":[{"type":"table","options":{"tableName":"users","showGlobalSearch":false,"filterField":"groups.name","filterValue":"Investigators"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-user-secret','Investigators','Investigators');


-- Case Detail modal
INSERT INTO modules (name, config, org_id, branch_id) VALUES (
  'Case Detail',
  '{"title":"Case Detail","body":[{"type":"tabs","tabs":[{"label":"Hours","elements":[{"type":"button","label":"Add Hour","onClick":"openCaseHourForm"},{"type":"table-view","viewId":100,"recordMap":"formatCaseHourRow"}]},{"label":"Documents","elements":[{"type":"button","label":"Upload Document","onClick":"uploadCaseDocument"},{"type":"table","id":"caseDocsTbl","options":{"tableName":"documents","addButton":false,"showGlobalSearch":false}}]},{"label":"Invoices","elements":[{"type":"button","label":"Create Invoice","onClick":"openCaseInvoiceForm"},{"type":"table","id":"caseInvoicesTbl","options":{"tableName":"invoices","addButton":false,"showGlobalSearch":false,"rowActions":[{"label":"Download","onClick":"downloadCaseInvoice"},{"label":"Preview","onClick":"previewCaseInvoice"}]}}]},{"label":"Payments","elements":[{"type":"button","label":"Record Payment","onClick":"openCasePaymentForm"},{"type":"table","id":"casePaymentsTbl","options":{"tableName":"payments","addButton":false,"showGlobalSearch":false}}]}]}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' ,
  NULL,
  NULL
);

-- Case Invoice Preview modal
INSERT INTO modules (name, config, org_id, branch_id) VALUES (
  'Case Invoice Preview',
  '{"title":"Case Invoice","body":[{"type":"html","id":"ciPreview","html":""},{"type":"form","formName":"Case Invoice"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL
);

-- Workplace Invoice Preview modal
INSERT INTO modules (name, config, org_id, branch_id)
SELECT 'Workplace Invoice Preview',
       '{"title":"Workplace Invoice Preview","body":[{"type":"html","id":"invoicePreview","html":""},{"type":"html","html":"<button id=\"payNowBtn\" class=\"btn\">Pay Now</button>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
       NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM modules WHERE name = 'Workplace Invoice Preview');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'DMS Showcase',
  '{"title":"DMS Showcase","body":[{"type":"documents"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-folder-open',
  'DMS Showcase',
  'DMS Showcase'
);

-- Additional modules from Dashboard
INSERT INTO modules (module_id, name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('0194a8b2-0001-7123-8123-123456789abc'::uuid, 'SystemSettings', '{"title":"System Settings","body":[{"type":"html","html":"System settings content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-gear', 'System Settings', 'System Settings'),
  ('0194a8b2-0002-7123-8123-123456789abc'::uuid, 'WorkflowInstances', '{"title":"Workflow Instances","body":[{"type":"html","html":"Workflow instances content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-clock-rotate-left', 'Workflow Instances', 'Workflow Instances'),
  ('0194a8b2-0003-7123-8123-123456789abc'::uuid, 'CustomizeDashboard', '{"title":"Customize Dashboard","body":[{"type":"html","html":"Customize dashboard content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-pen-to-square', 'Customize Dashboard', 'Customize Dashboard'),
  ('0194a8b2-0004-7123-8123-123456789abc'::uuid, 'FormBuilder', '{"title":"Form Builder","body":[{"type":"html","html":"Form builder content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-wpforms', 'Form Builder', 'Form Builder'),
  ('0194a8b2-0005-7123-8123-123456789abc'::uuid, 'DetailViewBuilder', '{"title":"Detail View Builder","body":[{"type":"html","html":"Detail view builder content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-address-card', 'Detail View Builder', 'Detail View Builder'),
  ('0194a8b2-0006-7123-8123-123456789abc'::uuid, 'PageCustomizer', '{"title":"Page Customizer","body":[{"type":"html","html":"Page customizer content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-object-group', 'Page Customizer', 'Page Customizer'),
  ('0194a8b2-0007-7123-8123-123456789abc'::uuid, 'PageConfigBuilder', '{"title":"Page Config Builder","body":[{"type":"html","html":"Page config builder content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-layer-group', 'Page Config Builder', 'Page Config Builder'),
  ('0194a8b2-0008-7123-8123-123456789abc'::uuid, 'ModuleBuilder', '{"title":"Module Builder","body":[{"type":"html","html":"Module builder content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-puzzle-piece', 'Module Builder', 'Module Builder'),
  ('0194a8b2-0009-7123-8123-123456789abc'::uuid, 'TableBuilder', '{"title":"Table Builder","body":[{"type":"html","html":"Table builder content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-table', 'Table Builder', 'Table Builder'),
  ('0194a8b2-0010-7123-8123-123456789abc'::uuid, 'TableManager', '{"title":"Table Manager","body":[{"type":"html","html":"Table manager content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-table-list', 'Table Manager', 'Table Manager'),
  ('0194a8b2-0011-7123-8123-123456789abc'::uuid, 'ModuleManager', '{"title":"Module Manager","body":[{"type":"html","html":"Module manager content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-cubes', 'Module Manager', 'Module Manager'),
  ('0194a8b2-0012-7123-8123-123456789abc'::uuid, 'PermissionsManager', '{"title":"Permissions Manager","body":[{"type":"html","html":"Permissions manager content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-user-shield', 'Permissions Manager', 'Permissions Manager'),
  ('0194a8b2-0013-7123-8123-123456789abc'::uuid, 'CreateUser', '{"title":"Create User","body":[{"type":"html","html":"Create user content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-user-plus', 'Create User', 'Create User'),
  ('0194a8b2-0014-7123-8123-123456789abc'::uuid, 'Payments', '{"title":"Payments","body":[{"type":"html","html":"Payments content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}', NULL, NULL, 'fa-money-check-dollar', 'Payments', 'Payments'),
  ('0194a8b2-0015-7123-8123-123456789abc'::uuid, 'POS', '{"title":"POS","body":[{"type":"html","html":"POS content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}],"legacy":true}', NULL, NULL, 'fa-cash-register', 'POS', 'POS'),
  ('0194a8b2-0016-7123-8123-123456789abc'::uuid, 'InventoryManagement', '{"title":"Inventory Management","body":[{"type":"html","html":"Inventory management content"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}],"legacy":true}', NULL, NULL, 'fa-boxes', 'Inventory Management', 'Inventory Management');

-- Default document folders
INSERT INTO document_folders (id, name, tenant_id, branch_id)
VALUES
  (1, 'Training', NULL, NULL),
  (2, 'Compliance', NULL, NULL)
ON CONFLICT (id) DO NOTHING;

-- OHS inspection forms
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  91,
  'General OHS Site Inspection',
  'Routine safety inspection of work areas, signage, and emergency readiness',
  'ohs_inspections',
  '[{"field_name":"inspection_type_id","data_column":"inspection_type_id","type_id":1,"order_index":0},{"label":"Short Description","field_name":"short_description","data_column":"short_description","type_id":1,"order_index":0},
    {"label":"Inspection Date","field_name":"inspection_date","data_column":"date","type_id":3,"order_index":1},
    {"label":"Site Name","field_name":"site_name","data_column":"location","type_id":1,"order_index":2},
    {"label":"Inspector Comments","field_name":"comments","json_column":"form_data","type_id":7,"order_index":3},
    {"label":"Emergency Exits Clear","field_name":"emergency_exits","json_column":"form_data","type_id":5,"order_index":4},
    {"label":"Safety Signage Present","field_name":"signage","json_column":"form_data","type_id":5,"order_index":5},
    {"label":"General Comments","field_name":"general_comments","json_column":"form_data","type_id":7,"order_index":6}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  92,
  'Plant & Equipment OHS Inspection',
  'Inspection of machinery, maintenance records, guards, and operating safety',
  'ohs_inspections',
  '[{"field_name":"inspection_type_id","data_column":"inspection_type_id","type_id":1,"order_index":0},{"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":1},{"label":"Short Description","field_name":"short_description","data_column":"short_description","type_id":1,"order_index":0},
    {"label":"Equipment Name","field_name":"equipment","json_column":"form_data","type_id":1,"order_index":1},
    {"label":"Inspection Date","field_name":"inspection_date","data_column":"date","type_id":3,"order_index":2},
    {"label":"Last Maintenance Date","field_name":"maintenance_date","data_column":"date","type_id":3,"order_index":3},
    {"label":"Guards in Place","field_name":"guards","json_column":"form_data","type_id":5,"order_index":4},
    {"label":"Emergency Stop Working","field_name":"emergency_stop","json_column":"form_data","type_id":5,"order_index":5},
    {"label":"Operator Safety Training Verified","field_name":"training","json_column":"form_data","type_id":5,"order_index":6},
    {"label":"Remarks","field_name":"remarks","json_column":"form_data","type_id":7,"order_index":7}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  93,
  'Confined Space Entry Inspection',
  'Inspection checklist before confined space entry at tanks or manholes',
  'ohs_inspections',
  '[{"field_name":"inspection_type_id","data_column":"inspection_type_id","type_id":1,"order_index":0},{"label":"Short Description","field_name":"short_description","data_column":"short_description","type_id":1,"order_index":0},
    {"label":"Inspection Date","field_name":"inspection_date","data_column":"date","type_id":3,"order_index":1},
    {"label":"Gas Test Conducted","field_name":"gas_test","json_column":"form_data","type_id":5,"order_index":2},
    {"label":"Ventilation Set Up","field_name":"ventilation","json_column":"form_data","type_id":5,"order_index":3},
    {"label":"Permit to Work Issued","field_name":"ptw","json_column":"form_data","type_id":5,"order_index":4},
    {"label":"Rescue Equipment Available","field_name":"rescue","json_column":"form_data","type_id":5,"order_index":5},
    {"label":"Remarks","field_name":"remarks","json_column":"form_data","type_id":7,"order_index":6}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  94,
  'Field Work Safety Inspection',
  'On-site inspection checklist for pipe repair, excavation, or rural works',
  'ohs_inspections',
  '[{"field_name":"inspection_type_id","data_column":"inspection_type_id","type_id":1,"order_index":0},{"label":"Short Description","field_name":"short_description","data_column":"short_description","type_id":1,"order_index":0},
    {"label":"Inspection Date","field_name":"inspection_date","data_column":"date","type_id":3,"order_index":1},
    {"label":"Traffic Management In Place","field_name":"traffic_control","json_column":"form_data","type_id":5,"order_index":2},
    {"label":"Tools in Good Condition","field_name":"tools","json_column":"form_data","type_id":5,"order_index":3},
    {"label":"First Aid Kit Available","field_name":"first_aid","json_column":"form_data","type_id":5,"order_index":4},
    {"label":"Team Briefing Conducted","field_name":"briefing","json_column":"form_data","type_id":5,"order_index":5},
    {"label":"Inspector Notes","field_name":"notes","json_column":"form_data","type_id":7,"order_index":6}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  95,
  'OHS Vehicle Inspection',
  'Safety checklist for operational and pool vehicles used by staff',
  'ohs_inspections',
  '[{"field_name":"inspection_type_id","data_column":"inspection_type_id","type_id":1,"order_index":0},{"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":1},{"label":"Short Description","field_name":"short_description","data_column":"short_description","type_id":1,"order_index":0},
    {"label":"Inspection Date","field_name":"inspection_date","data_column":"date","type_id":3,"order_index":1},
    {"label":"Vehicle Number","field_name":"vehicle_no","json_column":"form_data","type_id":1,"order_index":2},
    {"label":"Brakes Functional","field_name":"brakes","json_column":"form_data","type_id":5,"order_index":3},
    {"label":"Headlights & Signals","field_name":"lights","json_column":"form_data","type_id":5,"order_index":4},
    {"label":"Seat Belts Working","field_name":"seat_belts","json_column":"form_data","type_id":5,"order_index":5},
    {"label":"Fire Extinguisher Present","field_name":"extinguisher","json_column":"form_data","type_id":5,"order_index":6},
    {"label":"Inspector Comments","field_name":"comments","json_column":"form_data","type_id":7,"order_index":7}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  96,
  'OHS Inspection Start',
  'Choose inspection type and location',
  'ohs_inspections',
  '[{"label":"Inspection Type","field_name":"inspection_type_id","data_column":"inspection_type_id","type_id":4,"options_table":"ohs_inspection_types","options_label_column":"name","options_value_column":"id","order_index":1},
    {"label":"Location","field_name":"location","data_column":"location","type_id":4,"options_table":"locations","options_label_column":"name","options_value_column":"name","order_index":2}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO form_buttons (form_id,label,action_type,action_payload,order_index)
VALUES (
  96,'Open Form','open-form',
  '{"lookupTable":"ohs_inspection_types","lookupField":"inspection_type_id","valueColumn":"id","formColumn":"form_id","prefillFields":["inspection_type_id","location"]}',
  1
) ON CONFLICT DO NOTHING;

INSERT INTO document_categories (id, name, tenant_id, branch_id)
VALUES
  (1, 'Training', NULL, NULL),
  (2, 'Compliance', NULL, NULL)
ON CONFLICT (id) DO NOTHING;


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Training Documents',
  '{"title":"Training Documents","body":[{"type":"documents","options":{"category_id":1,"scope":"tenant"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-folder-open',
  'Training Documents',
  'Training Documents'
);

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Compliance Documents',
  '{"title":"Compliance Documents","body":[{"type":"documents","options":{"category_id":2,"scope":"tenant","showExpiry":true}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-folder-open',
  'Compliance Documents',
  'Compliance Documents'
);

INSERT INTO form_buttons (form_id,label,action_type,action_payload,order_index) VALUES
  (51,'Approve','workflow',(SELECT id FROM workflows WHERE trigger_event='case.approved' LIMIT 1),1),
  (51,'Reject','workflow',(SELECT id FROM workflows WHERE trigger_event='case.rejected' LIMIT 1),2);

INSERT INTO form_buttons (form_id, label, action_type, action_payload, order_index)
VALUES
  (59, 'Open Form', 'open-form',
   '{"lookupTable":"ohs_incident_types","lookupField":"classification","valueColumn":"name","formColumn":"form_id","prefillFields":["classification","location","severity"]}',
   1);



INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES
  ('Driver Created', 'form.45.updated', '{"conditions":[]}', TRUE),
  ('Lawyer Created', 'form.37.updated', '{"conditions":[]}', TRUE),
  ('Fleet Engineer Created', 'form.46.updated', '{"conditions":[]}', TRUE),
  ('Investigator Created', 'form.73.updated', '{"conditions":[]}', TRUE);


-- Driver workflow steps
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config) VALUES
  ((SELECT id FROM workflows WHERE name='Driver Created'),1,'map_fields','{"map":{"user_id":"id"}}'),
  ((SELECT id FROM workflows WHERE name='Driver Created'),2,'ensure_group','{"name":"Drivers"}'),
  ((SELECT id FROM workflows WHERE name='Driver Created'),3,'save_record','{"table":"user_group_memberships","data":{"user_id":"{user_id}","group_id":"{group_id}","role":"Driver"}}'),
  ((SELECT id FROM workflows WHERE name='Driver Created'),4,'load_record','{"table":"users","id_key":"user_id","context_key":"user"}'),
  ((SELECT id FROM workflows WHERE name='Driver Created'),5,'queue_notification','{"entity":"users","record_id":"{user_id}","event_type":"insert","payload":"{user}"}');


-- Lawyer workflow steps
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config) VALUES
  ((SELECT id FROM workflows WHERE name='Lawyer Created'),1,'map_fields','{"map":{"user_id":"id"}}'),
  ((SELECT id FROM workflows WHERE name='Lawyer Created'),2,'ensure_group','{"name":"Lawyers"}'),
  ((SELECT id FROM workflows WHERE name='Lawyer Created'),3,'save_record','{"table":"user_group_memberships","data":{"user_id":"{user_id}","group_id":"{group_id}","role":"Lawyer"}}'),
  ((SELECT id FROM workflows WHERE name='Lawyer Created'),4,'load_record','{"table":"users","id_key":"user_id","context_key":"user"}'),
  ((SELECT id FROM workflows WHERE name='Lawyer Created'),5,'queue_notification','{"entity":"users","record_id":"{user_id}","event_type":"insert","payload":"{user}"}');


-- Ensure Lawyer form references the correct workflow now that it exists
UPDATE forms
  SET on_submit_workflow_id = (
    SELECT id FROM workflows WHERE name='Lawyer Created'
  )
WHERE name='Lawyer';


-- Fleet Engineer workflow steps
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config) VALUES
  ((SELECT id FROM workflows WHERE name='Fleet Engineer Created'),1,'map_fields','{"map":{"user_id":"id"}}'),
  ((SELECT id FROM workflows WHERE name='Fleet Engineer Created'),2,'ensure_group','{"name":"Fleet Engineers"}'),
  ((SELECT id FROM workflows WHERE name='Fleet Engineer Created'),3,'save_record','{"table":"user_group_memberships","data":{"user_id":"{user_id}","group_id":"{group_id}","role":"Fleet Engineer"}}'),
  ((SELECT id FROM workflows WHERE name='Fleet Engineer Created'),4,'load_record','{"table":"users","id_key":"user_id","context_key":"user"}'),
  ((SELECT id FROM workflows WHERE name='Fleet Engineer Created'),5,'queue_notification','{"entity":"users","record_id":"{user_id}","event_type":"insert","payload":"{user}"}');

-- Investigator workflow steps
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config) VALUES
  ((SELECT id FROM workflows WHERE name='Investigator Created'),1,'map_fields','{"map":{"user_id":"id"}}'),
  ((SELECT id FROM workflows WHERE name='Investigator Created'),2,'save_record','{"table":"users","data":{"full_name":"{full_name}","password":"{password}"},"context_key":"user_id"}'),
  ((SELECT id FROM workflows WHERE name='Investigator Created'),3,'ensure_group','{"name":"Investigators"}'),
  ((SELECT id FROM workflows WHERE name='Investigator Created'),4,'save_record','{"table":"user_group_memberships","data":{"user_id":"{user_id}","group_id":"{group_id}","role":"Investigator"}}'),
  ((SELECT id FROM workflows WHERE name='Investigator Created'),5,'load_record','{"table":"users","id_key":"user_id","context_key":"user"}'),
  ((SELECT id FROM workflows WHERE name='Investigator Created'),6,'queue_notification','{"entity":"users","record_id":"{user_id}","event_type":"insert","payload":"{user}"}');



-- Modal using sample detail view
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Sample Detail Modal',
  '{"title":"Sample Detail","body":[{"type":"detail-view","viewId":1}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-address-card',
  'Sample Detail View',
  'Sample Detail View'
);


INSERT INTO detail_views (id, name, config, tenant_id, branch_id) VALUES (
  2,
  'Case Details',
  $${
    "name": "Case Details",
    "sources": [
      { "table": "clients", "foreignKey": "client_id", "alias": "client" },
      { "table": "time_entries", "foreignKey": "time_entry_id" }
    ],
    "rows": [
      {
        "classes": "card bg-base-100 shadow card-body grid grid-cols-4 gap-4",
        "columns": [
          {
            "classes": "card bg-base-100 shadow p-4 space-y-1",
            "elements": [
              { "type": "heading", "text": "Client Information", "tag": "h2", "classes": "card-title" },
              { "type": "paragraph", "label": "Name", "data_column": "client.name", "classes": "grid grid-cols-2 gap-2", "label_classes": "font-semibold" },
              { "type": "paragraph", "label": "Phone", "data_column": "client.phone", "classes": "grid grid-cols-2 gap-2", "label_classes": "font-semibold" },
              { "type": "paragraph", "label": "Email", "data_column": "client.email", "classes": "grid grid-cols-2 gap-2", "label_classes": "font-semibold" },
              { "type": "paragraph", "label": "Address", "data_column": "client.address", "classes": "grid grid-cols-2 gap-2", "label_classes": "font-semibold" },
              { "type": "paragraph", "label": "City", "data_column": "client.city", "classes": "grid grid-cols-2 gap-2", "label_classes": "font-semibold" },
              { "type": "paragraph", "label": "Country", "data_column": "client.country", "classes": "grid grid-cols-2 gap-2", "label_classes": "font-semibold" },
              { "type": "paragraph", "label": "Description", "data_column": "client.description", "classes": "grid grid-cols-2 gap-2", "label_classes": "font-semibold" }
            ]
          },
          {
            "classes": "card bg-base-100 shadow p-4 space-y-1",
            "elements": [
              { "type": "heading", "text": "Case Information", "tag": "h2", "classes": "card-title" },
              { "type": "paragraph", "label": "Title", "data_column": "title", "classes": "grid grid-cols-2 gap-2", "label_classes": "font-semibold" },
              { "type": "paragraph", "label": "Case Number", "data_column": "case_number", "classes": "grid grid-cols-2 gap-2", "label_classes": "font-semibold" },
              { "type": "paragraph", "label": "Status", "data_column": "status", "classes": "grid grid-cols-2 gap-2", "label_classes": "font-semibold" },
              { "type": "paragraph", "label": "Lead Rep", "data_column": "lead_rep_name", "classes": "grid grid-cols-2 gap-2", "label_classes": "font-semibold" },
              { "type": "paragraph", "label": "Opened", "data_column": "opened_at", "classes": "grid grid-cols-2 gap-2", "label_classes": "font-semibold" },
              { "type": "paragraph", "label": "Closed", "data_column": "closed_at", "classes": "grid grid-cols-2 gap-2", "label_classes": "font-semibold" }
            ]
          },
          {
            "classes": "stats",
            "elements": [
              {
                "type": "div",
                "classes": "stat",
                "elements": [
                  { "type": "div", "classes": "stat-title", "text": "Hours" },
                  { "type": "aggregate", "table": "time_entries", "column": "hours", "function": "sum", "filterField": "time_entry_id", "classes": "stat-value" }
                ]
              },
              {
                "type": "div",
                "classes": "stat",
                "elements": [
                  { "type": "div", "classes": "stat-title", "text": "Expenses" },
                  { "type": "aggregate", "table": "expenses", "column": "amount", "function": "sum", "filterField": "case_id", "classes": "stat-value" }
                ]
              },
              {
                "type": "div",
                "classes": "stat",
                "elements": [
                  { "type": "div", "classes": "stat-title", "text": "Total Hours" },
                  { "type": "aggregate", "table": "time_entries", "column": "hours", "function": "sum", "filterField": "time_entry_id", "classes": "stat-value" }
                ]
              },
              {
                "type": "div",
                "classes": "stat",
                "elements": [
                  { "type": "div", "classes": "stat-title", "text": "Value" },
                  { "type": "aggregate", "table": "time_entries", "column": "total", "function": "sum", "filterField": "time_entry_id", "classes": "stat-value" }
                ]
              },
              {
                "type": "div",
                "classes": "stat",
                "elements": [
                  { "type": "div", "classes": "stat-title", "text": "Billed Hours" },
                  { "type": "aggregate", "table": "time_entries", "column": "hours", "function": "sum", "filterField": "time_entry_id", "filters": { "billed_to_client": 1 }, "classes": "stat-value" }
                ]
              },
              {
                "type": "div",
                "classes": "stat",
                "elements": [
                  { "type": "div", "classes": "stat-title", "text": "Billed Amount" },
                  { "type": "aggregate", "table": "time_entries", "column": "total", "function": "sum", "filterField": "time_entry_id", "filters": { "billed_to_client": 1 }, "classes": "stat-value" }
                ]
              }
            ]
          },
          {
            "classes": "card bg-base-100 shadow p-4 space-y-1",
            "elements": [
              { "type": "paragraph", "label": "Initial Instructions", "data_column": "initial_instructions" }
            ]
          }
        ]
      }
    ]
  }$$,
  NULL,
  NULL
);


-- Default invoice templates
INSERT INTO invoice_templates (name, file_path, tenant_id)
VALUES
  ('Basic', 'templates/invoices/basic.php', NULL),
  ('Detailed', 'templates/invoices/detailed.php', NULL),
  ('Modern', 'templates/invoices/modern.php', NULL),
  ('Notice of Certain Workplaces', 'templates/invoices/workplace_notice.php', NULL),
  ('Jagath Case', 'templates/invoices/jagath_template.php', NULL);

-- Default certificate templates
INSERT INTO certificate_templates (name, file_path, tenant_id)
VALUES ('Basic Certificate', 'templates/certificates/basic.php', NULL);

-- Default invoice settings row
INSERT INTO invoice_settings (tenant_id)
VALUES (NULL);

UPDATE invoice_settings
SET template_id = (SELECT id FROM invoice_templates WHERE name='Notice of Certain Workplaces' LIMIT 1),
    case_template_id = (SELECT id FROM invoice_templates WHERE name='Jagath Case' LIMIT 1)
WHERE tenant_id IS NULL;


-- Duplicate "Notice of Certain Workplaces" template for tenant 4
INSERT INTO invoice_templates (name, file_path, tenant_id)
SELECT name, file_path, 4
FROM invoice_templates
WHERE name = 'Notice of Certain Workplaces' AND tenant_id IS NULL
LIMIT 1;

-- Duplicate "Jagath Case" template for tenant 4
INSERT INTO invoice_templates (name, file_path, tenant_id)
SELECT name, file_path, 4
FROM invoice_templates
WHERE name = 'Jagath Case' AND tenant_id IS NULL
LIMIT 1;

-- Invoice settings for tenant 4 using the duplicated template
--INSERT INTO invoice_settings (tenant_id, template_id, case_template_id)
--VALUES (
--  4,
--  (SELECT id FROM invoice_templates WHERE name='Notice of Certain Workplaces' AND tenant_id=4 LIMIT 1),
--  (SELECT id FROM invoice_templates WHERE name='Jagath Case' AND tenant_id=4 LIMIT 1)
--);


-- Invoice Configuration modal and workflow
INSERT INTO modules (name, config, org_id, branch_id) VALUES (
  'Invoice Configuration',
  '{"title":"Invoice Configuration","body":[{"type":"form","formId":44}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL
);


INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
  'Invoice Configuration',
  'invoice.configure',
  '{"conditions":[]}',
  TRUE
);


INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
VALUES (
  (SELECT id FROM workflows WHERE name='Invoice Configuration' LIMIT 1),
  1,
  'open_modal',
  '{"modal":"Invoice Configuration"}'
);


-- Workflows triggered when time entries are approved or rejected
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES ('Time Entries Approved', 'time_entries.approved', '{"conditions": []}', TRUE);


INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES ('Time Entries Rejected', 'time_entries.rejected', '{"conditions": []}', TRUE);


INSERT INTO workflow_steps (workflow_id, step_order, step_type, config) VALUES
  ((SELECT id FROM workflows WHERE name='Time Entries Approved' LIMIT 1),1,'map_fields','{"map":{"id":"time_entry_id"}}'),
  ((SELECT id FROM workflows WHERE name='Time Entries Approved' LIMIT 1),2,'save_record','{"table":"time_entries","data":{"comments":"{comments}","status":"Approved"},"match":{"id":"{id}"}}'),
  ((SELECT id FROM workflows WHERE name='Time Entries Approved' LIMIT 1),3,'load_record','{"table":"time_entries","id_key":"id","context_key":"time_entry"}'),
  ((SELECT id FROM workflows WHERE name='Time Entries Approved' LIMIT 1),4,'queue_notification','{"entity":"time_entries","record_id":"{id}","payload":"{time_entry}"}');


INSERT INTO workflow_steps (workflow_id, step_order, step_type, config) VALUES
  ((SELECT id FROM workflows WHERE name='Time Entries Rejected' LIMIT 1),1,'map_fields','{"map":{"id":"time_entry_id"}}'),
  ((SELECT id FROM workflows WHERE name='Time Entries Rejected' LIMIT 1),2,'save_record','{"table":"time_entries","data":{"comments":"{comments}","status":"Rejected"},"match":{"id":"{time_entry_id}"}}'),
  ((SELECT id FROM workflows WHERE name='Time Entries Rejected' LIMIT 1),3,'load_record','{"table":"time_entries","id_key":"id","context_key":"time_entry"}'),
  ((SELECT id FROM workflows WHERE name='Time Entries Rejected' LIMIT 1),4,'queue_notification','{"entity":"time_entries","record_id":"{id}","payload":"{time_entry}"}');


INSERT INTO workflow_event_triggers (event_name, table_name, trigger_type, operations, field_map)
VALUES (
  'workplace.registration.created',
  'workplace_registrations',
  'insert',
  '["insert"]',
  '{"id":"id","assigned_reviewer_id":"assigned_reviewer_id"}'
);

-- Workflow to auto assign inspectors when WorkCOM inspections are created
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
  'WorkCOM Inspection Assignment',
  'workplace.inspection.created',
  '{"conditions": []}',
  TRUE
);

INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
VALUES (
  (SELECT id FROM workflows WHERE name='WorkCOM Inspection Assignment' LIMIT 1),
  1,
  'query_records',
  '{"table":"inspection_assignment_rules","where":{"district_id":"{district_id}"},"context_key":"rules"}'
);

INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
VALUES (
  (SELECT id FROM workflows WHERE name='WorkCOM Inspection Assignment' LIMIT 1),
  2,
  'map_fields',
  '{"map":{"inspector_id":"rules.0.inspector_id"}}'
);

INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
VALUES (
  (SELECT id FROM workflows WHERE name='WorkCOM Inspection Assignment' LIMIT 1),
  3,
  'save_record',
  '{"table":"workplace_inspections","data":{"inspector_id":"{inspector_id}"},"match":{"id":"{id}"}}'
);

INSERT INTO workflow_event_triggers (event_name, table_name, trigger_type, operations, field_map)
VALUES (
  'workplace.inspection.created',
  'workplace_inspections',
  'insert',
  '["insert"]',
  '{"id":"id","location":"location","inspection_type_id":"inspection_type_id","inspector_id":"inspector_id"}'
);


-- Workflow to handle WorkCOM payment verification and schedule a Kanban task and calendar event
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
  'WorkCOM Payment Verification',
  'invoice.payment.verified',
  '{"conditions": []}',
  TRUE
);

-- Step 1: load workplace registration
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
VALUES (
  (SELECT id FROM workflows WHERE name='WorkCOM Payment Verification' LIMIT 1),
  1,
  'query_records',
  '{"table":"workplace_registrations","where":{"id":"{workplace_id}"},"context_key":"registration"}'
);

-- Step 2: map registration fields
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
VALUES (
  (SELECT id FROM workflows WHERE name='WorkCOM Payment Verification' LIMIT 1),
  2,
  'map_fields',
  '{"map":{"district_id":"registration.0.district","company_name":"registration.0.company_name","contact_person":"registration.0.contact_person","contact_email":"registration.0.contact_email","contact_phone":"registration.0.contact_phone","contact_telephone_number":"registration.0.contact_telephone_number","branch_id":"registration.0.branch_id","tenant_id":"registration.0.tenant_id"}}'
);

-- Step 3: find inspector assignment rules
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
VALUES (
  (SELECT id FROM workflows WHERE name='WorkCOM Payment Verification' LIMIT 1),
  3,
  'query_records',
  '{"table":"inspection_assignment_rules","where":{"district_id":"{district_id}"},"context_key":"rules"}'
);

-- Step 4: map inspector to assignee
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
VALUES (
  (SELECT id FROM workflows WHERE name='WorkCOM Payment Verification' LIMIT 1),
  4,
  'map_fields',
  '{"map":{"inspector_id":"rules.0.inspector_id","assignee_id":"rules.0.inspector_id"}}'
);

-- Step 5: create kanban task
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT
  (SELECT id FROM workflows WHERE name='WorkCOM Payment Verification' LIMIT 1),
  5,
  'create_task',
  json_build_object(
    'column_id', kc.id,
    'title', 'Workplace Inspection - {company_name}',
    'description', 'Inspection for {company_name}',
    'assignee_id', '{assignee_id}',
    'due_date', '{due_date}'
  )
FROM kanban_columns kc
JOIN kanban_boards kb ON kc.board_id = kb.id
WHERE kb.name='WorkCOM Inspections' AND kc.title='Pending'
LIMIT 1;

-- Step 6: add calendar event
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT
  (SELECT id FROM workflows WHERE name='WorkCOM Payment Verification' LIMIT 1),
  6,
  'add_calendar_event',
  json_build_object(
    'title', 'Workplace Inspection',
    'start_time', '{due_date}',
    'category_id', kb.calendar_category_id,
    'user_id', '{assignee_id}'
  )
FROM kanban_boards kb
WHERE kb.name='WorkCOM Inspections'
LIMIT 1;

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
(
  'Compliance Dashboard',
  $json$
  {
    "title": "Compliance Dashboard",
    "body": [
      {
        "type": "html",
        "id": "complianceDash",
        "html": "<div id=\"complianceDashboard\"></div>"
      }
    ],
    "footer": [
      {
        "type": "button",
        "label": "Close",
        "onClick": "closeModal"
      }
    ]
  }
  $json$,
  NULL,
  NULL,
  'fa-gauge',
  'Compliance Dashboard',
  'Compliance Dashboard'
);


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Drill Down Dashboard','{"title":"Drill Down Dashboard","body":[{"type":"table","options":{"tableName":"ohs_incidents"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-filter','Drill Down Dashboard','Drill Down Dashboard');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Emergency Preparedness','{"title":"Emergency Preparedness","body":[{"type":"table","options":{"tableName":"ohs_emergency_drills"}},{"type":"table","options":{"tableName":"ohs_emergency_contacts"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-phone','Emergency Preparedness','Emergency Preparedness');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Hazard Management','{"title":"Hazard Management","header":[{"type":"button","label":"Add Hazard","formName":"OHS Hazard"}],"body":[{"type":"table","options":{"tableName":"ohs_hazards","showGlobalSearch":false,"addButton":false,"editFormId":7}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-shield-halved','Hazard Management','Hazard Management');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Hazard Types','{"title":"Hazard Types","header":[{"type":"button","label":"Add Hazard Type","formName":"Hazard Type"}],"body":[{"type":"table","options":{"tableName":"ohs_hazard_types","showGlobalSearch":false,"addButton":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-layer-group','Hazard Types','Hazard Types');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Incident Heatmap','{"title":"Incident Heatmap","body":[{"type":"graph-map","options":{"endpoint":"../api/ohs_dashboard/heatmap","dataPath":"rows"}},{"type":"graph-chart","options":{"endpoint":"../api/ohs_dashboard/heatmap","dataPath":"chart","type":"bar"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-map','Incident Heatmap','Incident Heatmap');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Incident Summary Dashboard','{"title":"Incident Summary","body":[{"type":"graph-chart","options":{"endpoint":"../api/ohs_dashboard/summary","type":"pie"}},{"type":"graph-chart","options":{"endpoint":"../api/ohs_dashboard/monthly_trend","type":"line"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-chart-pie','Incident Summary Dashboard','Incident Summary Dashboard');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Inspection Audit','{"title":"Inspection Audit","body":[{"type":"table","options":{"tableName":"ohs_inspection_audits"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-list-check','Inspection Audit','Inspection Audit');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('JSA Training Status','{"title":"JSA Training Status","body":[{"type":"table","options":{"tableName":"ohs_jsas"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-user-graduate','JSA Training Status','JSA Training Status');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Near Miss Dashboard','{"title":"Near Miss Dashboard","body":[{"type":"html","id":"nearMissDash","html":"<canvas id=\"nearMissTrend\"></canvas><table class=\"near-miss-table\"></table>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-chart-line','Near Miss Dashboard','Near Miss Dashboard');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('OHS Assignment Rules','{"title":"OHS Assignment Rules","body":[{"type":"table","options":{"tableName":"ohs_assignment_rules"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-route','OHS Assignment Rules','OHS Assignment Rules');

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Inspection Assignment Rules','{"title":"Inspection Assignment Rules","body":[{"type":"table","options":{"tableName":"inspection_assignment_rules"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-route','Inspection Assignment Rules','Inspection Assignment Rules');
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('WorkCOM Inspection Assignment Rules','{"title":"WorkCOM Inspection Assignment Rules","body":[{"type":"table","options":{"tableName":"inspection_assignment_rules"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-route','WorkCOM Inspection Assignment Rules','WorkCOM Inspection Assignment Rules');

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('OHS Incident Types','{"title":"OHS Incident Types","header":[{"type":"button","label":"Add Incident Type","formName":"Incident Type"}],"body":[{"type":"table","options":{"tableName":"ohs_incident_types","showGlobalSearch":false,"addButton":false,"editFormId":57}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-clipboard-list','OHS Incident Types','OHS Incident Types');

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('OHS Inspection Types','{"title":"OHS Inspection Types","header":[{"type":"button","label":"Add Inspection Type","formName":"Inspection Type"}],"body":[{"type":"table","options":{"tableName":"ohs_inspection_types","showGlobalSearch":false,"addButton":false,"editFormId":68}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-clipboard-list','OHS Inspection Types','OHS Inspection Types');

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('OHS Inspection Templates','{"title":"OHS Inspection Templates","header":[{"type":"button","label":"Add Template","formName":"Inspection Template"}],"body":[{"type":"table","options":{"tableName":"ohs_inspection_templates","showGlobalSearch":false,"addButton":false,"editFormId":74}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-clipboard-list','OHS Inspection Templates','OHS Inspection Templates');

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('WorkCOM Inspection Types','{"title":"WorkCOM Inspection Types","header":[{"type":"button","label":"Add Inspection Type","formName":"WorkCOM Inspection Type"}],"body":[{"type":"table","options":{"tableName":"workcom_inspection_types","showGlobalSearch":false,"addButton":false,"editFormId":104}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-clipboard-list','WorkCOM Inspection Types','WorkCOM Inspection Types');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('OHS Incident Wizard','{"title":"OHS Incident Wizard","body":[{"type":"html","id":"incidentWizard","html":"<div id=\"incidentWizard\"></div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-wand-magic-sparkles','OHS Incident Wizard','OHS Incident Wizard');


-- Wizard configuration for incident forms
INSERT INTO form_wizards (id, name, description, tenant_id, branch_id)
VALUES (1, 'OHS Incident Wizard', 'Guided incident reporting',NULL, NULL)
ON CONFLICT DO NOTHING;

INSERT INTO form_wizards (id, name, description, tenant_id, branch_id)
VALUES (3, 'OHS Inspection Wizard', 'Guided inspection entry',NULL, NULL)
ON CONFLICT DO NOTHING;

INSERT INTO form_wizards (id, name, description, tenant_id, branch_id)
VALUES (5, 'WorkCOM Inspection Wizard', 'Guided WorkCOM inspection entry',NULL, NULL)
ON CONFLICT DO NOTHING;

INSERT INTO form_wizards (id, name, description, tenant_id, branch_id)
VALUES (6, 'Safety Incident Wizard', 'Guided safety incident entry', NULL, NULL)
ON CONFLICT DO NOTHING;


INSERT INTO form_wizard_steps (wizard_id, step_number, form_id, condition_field, condition_value)
VALUES
  (1, 1, 59,NULL, NULL),
  (1, 2, 13, 'classification', 'Property Damage'),
  (1, 3, 14, 'classification', 'Environmental'),
  (1, 4, 15, 'classification', 'Near Miss'),
  (1, 5, 16, 'classification', 'First Aid'),
  (1, 6, 17, 'classification', 'Medical Treatment'),
  (1, 7, 18, 'classification', 'LTI'),
  (1, 8, 19, 'classification', 'Fatality'),
  (1, 9, 105, 'classification', 'Field Incident')
ON CONFLICT DO NOTHING;

INSERT INTO form_wizard_steps (wizard_id, step_number, form_id, condition_field, condition_value)
VALUES (6, 1, 107, NULL, NULL)
ON CONFLICT DO NOTHING;


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('OHS Incidents','{"title":"OHS Incidents","header":[{"type":"button","label":"Add Incident","wizardId":1}],"body":[{"type":"table","options":{"tableName":"ohs_incidents","showGlobalSearch":false,"addButton":false,"editFormLookup":{"table":"ohs_incident_types","field":"classification","valueColumn":"name","formColumn":"form_id"}}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-exclamation-triangle','OHS Incidents','OHS Incidents');

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('OHS Field Incident','{"title":"OHS Field Incident","header":[{"type":"button","label":"Add Field Incident","wizardId":1}],"body":[{"type":"table","options":{"tableName":"ohs_incidents","showGlobalSearch":false,"addButton":false,"filterField":"classification","filterValue":"Field Incident","editFormLookup":{"table":"ohs_incident_types","field":"classification","valueColumn":"name","formColumn":"form_id"}}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-exclamation-triangle','OHS Field Incident','OHS Field Incident');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('OHS Inspections','{"title":"OHS Inspections","header":[{"type":"button","label":"Add Inspection","wizardId":3}],"body":[{"type":"table","options":{"tableName":"ohs_inspections","showGlobalSearch":false,"addButton":false,"editFormLookup":{"table":"ohs_inspection_types","field":"inspection_type_id","valueColumn":"id","formColumn":"form_id"}}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-clipboard-check','OHS Inspections','OHS Inspections');

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('My Inspections','{"title":"My Inspections","header":[{"type":"button","label":"Add Inspection","wizardId":3}],"body":[{"type":"table","options":{"tableName":"ohs_inspections","scope":"user","userField":"inspector_id","editFormLookup":{"table":"ohs_inspection_types","field":"inspection_type_id","valueColumn":"id","formColumn":"form_id"}}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-clipboard-check','My Inspections','My Inspections');

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('WorkCOM Inspections','{"title":"WorkCOM Inspections","header":[{"type":"button","label":"Add Inspection","wizardId":5}],"body":[{"type":"table","options":{"tableName":"workcom_inspections","showGlobalSearch":false,"addButton":false,"editFormLookup":{"table":"workcom_inspection_types","field":"inspection_type_id","valueColumn":"id","formColumn":"form_id"}}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-clipboard-check','WorkCOM Inspections','WorkCOM Inspections');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('OHS Investigation Dashboard','{"title":"Investigation Dashboard","header":[{"type":"button","label":"Add Incident","wizardId":1}],"body":[{"type":"table","options":{"tableName":"ohs_incidents","editFormId":4,"rowClick":"selectRecord"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-clipboard-question','OHS Investigation Dashboard','OHS Investigation Dashboard');
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'WorkCOM Inspection Dashboard',
  '{"title":"WorkCOM Inspection Dashboard","body":[{"type":"table","options":{"tableName":"workcom_inspections","calculatedColumns":{"status":"inspector_id ? ''Complete'' : ''Pending''"},"searchInputTypes":{"status":{"type":"select","options":["Pending","Complete"]}}}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL, NULL, 'fa-chart-bar', 'WorkCOM Inspection Dashboard', 'WorkCOM Inspection Dashboard'
);

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Inspector Workload',
  '{"title":"Inspector Workload","body":[{"type":"table","options":{"tableName":"workcom_inspections","scope":"user","userField":"inspector_id","calculatedColumns":{"pending_count":"inspector_id ? 0 : 1","completed_count":"inspector_id ? 1 : 0"}}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL, NULL, 'fa-tasks', 'Inspector Workload', 'Inspector Workload'
);


-- Kanban board for WorkCOM inspections
INSERT INTO kanban_boards (id, name, description, tenant_id, branch_id)
VALUES (1, 'WorkCOM Inspection Kanban', 'Inspection workflow board', NULL, NULL)
ON CONFLICT DO NOTHING;

INSERT INTO kanban_columns (board_id, title, position, tenant_id, branch_id)
VALUES
  (1, 'Pending', 1, NULL, NULL),
  (1, 'In Progress', 2, NULL, NULL),
  (1, 'Completed', 3, NULL, NULL)
ON CONFLICT DO NOTHING;

INSERT INTO modules (
  name, config, org_id, branch_id, icon, aria_label, description
) VALUES (
  'WorkCOM Inspection Kanban',
  $json$
  {
    "title": "WorkCOM Inspection Kanban",
    "body": [
      {
        "type": "html",
        "html": "<div id=\"workcomKanban\"></div>"
      }
    ],
    "footer": [
      {
        "type": "button",
        "label": "Close",
        "onClick": "closeModal"
      }
    ]
  }
  $json$::jsonb,
  NULL, NULL, 'fa-table-columns', 'WorkCOM Inspection Kanban', 'WorkCOM Inspection Kanban'
);

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Investigation Form','{"title":"Investigation Form","body":[{"type":"html","html":"<div id=\"whyList\"></div><button id=\"addWhyBtn\" type=\"button\">Add Why</button><button id=\"saveInvBtn\" type=\"button\">Save</button>"}],"footer":[]}',NULL,NULL,'fa-question','Investigation Form','Investigation Form');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('OHS JSA','{"title":"OHS JSA","header":[{"type":"button","label":"Create JSA","formName":"Job Safety Analysis"}],"body":[{"type":"table","options":{"tableName":"ohs_jsas","showGlobalSearch":false,"addButton":false,"editFormId":6}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-helmet-safety','OHS JSA','OHS JSA');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Safety Documents','{"title":"Safety Documents","body":[{"type":"table","options":{"tableName":"safety_documents"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-file-lines','Safety Documents','Safety Documents');

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Safety Incidents',
  '{"title":"Safety Incidents","header":[{"type":"button","label":"Add Incident","wizardId":6}],"body":[{"type":"table","options":{"tableName":"safety_incidents","showGlobalSearch":false,"addButton":false,"editFormLookup":{"table":"safety_incident_types","field":"incident_type","valueColumn":"id","formColumn":"form_id"},"columnLabels":{"document_ids":"Documents"}}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-helmet-safety',
  'Safety Incidents',
  'Safety Incidents'
);

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description)
SELECT 'Safety Field Incident', '{"title":"Safety Field Incident","header":[{"type":"button","label":"Add Field Incident","wizardId":6}],"body":[{"type":"table","options":{"tableName":"safety_incidents","showGlobalSearch":false,"addButton":false,"filterField":"incident_type","filterValue":6,"editFormLookup":{"table":"safety_incident_types","field":"incident_type","valueColumn":"id","formColumn":"form_id"}}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' , NULL, NULL, 'fa-helmet-safety', 'Safety Field Incident', 'Safety Field Incident'
WHERE NOT EXISTS (SELECT 1 FROM modules WHERE name='Safety Field Incident');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
('Ohs Reports','{"title": "OHS Reports", "header": [{"type": "html", "html": "<button class=\"btn btn-sm\" onclick=\"location.href=''../api/ohs_reports/export/excel''\">Export Excel</button><button class=\"btn btn-sm ml-2\" onclick=\"location.href=''../api/ohs_reports/export/pdf''\">Export PDF</button>"}], "body": [{"type": "graph-chart", "options": {"endpoint": "../api/ohs_reports/trendsByDepartment", "type": "line"}}, {"type": "graph-chart", "options": {"endpoint": "../api/ohs_reports/locationBenchmark", "type": "bar"}}], "footer": [{"type": "button", "label": "Close", "onClick": "closeModal"}]}',NULL,NULL,'fa-chart-bar','OHS Reports','OHS Reports');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Risk Matrix Dashboard','{"title":"Risk Matrix Dashboard","body":[{"type":"heatmap","id":"riskMatrixHeatmap","options":{"endpoint":"../api/ohs_risk_matrices"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-table-cells','Risk Matrix Dashboard','Risk Matrix Dashboard');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Root Cause Dashboard','{"title":"Root Cause Dashboard","body":[{"type":"html","html":"<div id=\"rootCauseDashboard\"></div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-magnifying-glass-chart','Root Cause Dashboard','Root Cause Dashboard');

INSERT INTO fishbone_diagrams (id, problem, categories, diagram_data)
VALUES
-- Diagram 1
(1, 'Low Water Pressure in Urban Areas',
 '[{"name":"Infrastructure","causes":["Aging pipes","Undersized mains","Leaks in distribution lines"]},{"name":"Demand Surge","causes":["Population growth","Unauthorized connections"]},{"name":"Maintenance","causes":["Infrequent valve flushing","Inadequate pressure zoning"]},{"name":"Energy Supply","causes":["Pump outages","Fluctuating voltage"]}]',
 $json$
 {
   "problem": "Low Water Pressure in Urban Areas",
   "categories": [
     {
       "name": "Infrastructure",
       "color": "#3366cc",
       "comment": "Focus on pipe upgrades",
       "causes": [
         { "text": "Aging pipes", "color": "#333", "comment": "Over 30 years old" },
         { "text": "Undersized mains", "color": "#333", "comment": "Can't handle peak flow" },
         { "text": "Leaks in distribution lines", "color": "#333", "comment": "Common in zones 4, 6" }
       ]
     },
     {
       "name": "Demand Surge",
       "color": "#ff9900",
       "comment": "Urban population growth outpacing planning",
       "causes": [
         { "text": "Population growth", "color": "#333" },
         { "text": "Unauthorized connections", "color": "#cc0000", "comment": "Found in squatter areas" }
       ]
     },
     {
       "name": "Maintenance",
       "color": "#109618",
       "causes": [
         { "text": "Infrequent valve flushing", "color": "#333" },
         { "text": "Inadequate pressure zoning", "color": "#333" }
       ]
     },
     {
       "name": "Energy Supply",
       "color": "#990099",
       "causes": [
         { "text": "Pump outages", "color": "#333" },
         { "text": "Fluctuating voltage", "color": "#333" }
       ]
     }
   ]
 }
 $json$::jsonb),

-- Diagram 2
(2, 'High Water Loss (Non-Revenue Water)',
 '[{"name":"Metering","causes":["Inaccurate customer meters","Missing bulk meters"]},{"name":"Illegal Use","causes":["Bypassed meters","Unauthorized hydrants"]},{"name":"Pipeline Integrity","causes":["Undetected leaks","Old pipe materials"]},{"name":"Monitoring","causes":["Poor SCADA integration","Infrequent audits"]}]',
 $json$
 {
   "problem": "High Water Loss (Non-Revenue Water)",
   "categories": [
     {
       "name": "Metering",
       "color": "#1f77b4",
       "causes": [
         { "text": "Inaccurate customer meters", "comment": "Affects billing accuracy" },
         { "text": "Missing bulk meters" }
       ]
     },
     {
       "name": "Illegal Use",
       "color": "#d62728",
       "causes": [
         { "text": "Bypassed meters" },
         { "text": "Unauthorized hydrants" }
       ]
     },
     {
       "name": "Pipeline Integrity",
       "color": "#2ca02c",
       "causes": [
         { "text": "Undetected leaks", "comment": "Leak detection overdue" },
         { "text": "Old pipe materials" }
       ]
     },
     {
       "name": "Monitoring",
       "color": "#ff7f0e",
       "causes": [
         { "text": "Poor SCADA integration" },
         { "text": "Infrequent audits" }
       ]
     }
   ]
 }
 $json$::jsonb),

-- Diagram 3
(3, 'Delayed Response to Water Complaints',
 '[{"name":"Customer Service","causes":["Long call wait times","Poor ticket routing"]},{"name":"Field Operations","causes":["Limited repair crews","Incomplete work logs"]},{"name":"Communication","causes":["No SMS alerts","Delayed internal escalation"]},{"name":"Technology","causes":["Outdated ticketing system","No real-time dashboard"]}]',
 $json$
 {
   "problem": "Delayed Response to Water Complaints",
   "categories": [
     {
       "name": "Customer Service",
       "color": "#8c564b",
       "causes": [
         { "text": "Long call wait times" },
         { "text": "Poor ticket routing" }
       ]
     },
     {
       "name": "Field Operations",
       "color": "#e377c2",
       "causes": [
         { "text": "Limited repair crews" },
         { "text": "Incomplete work logs" }
       ]
     },
     {
       "name": "Communication",
       "color": "#7f7f7f",
       "causes": [
         { "text": "No SMS alerts" },
         { "text": "Delayed internal escalation" }
       ]
     },
     {
       "name": "Technology",
       "color": "#bcbd22",
       "causes": [
         { "text": "Outdated ticketing system" },
         { "text": "No real-time dashboard" }
       ]
     }
   ]
 }
 $json$::jsonb);


INSERT INTO modules (name, config, org_id, branch_id) VALUES ('Fishbone Example','{"title":"Fishbone Example","body":[{"type":"fishbone-diagram","options":{"diagramId":1}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL);


INSERT INTO graphs (name, config)
VALUES
  ('Incidents by classification',
   '{"table":"ohs_incidents","x":"classification","y":"id","type":"bar","groupBy":"classification","aggregateFunc":"count","timeUnit":""}'),
  ('Incident Trends',
   '{"table":"ohs_incidents","x":"date","y":"id","type":"groupedBar","groupBy":"date,classification","aggregateFunc":"count","timeUnit":"year"}');

INSERT INTO modules (name, config, org_id, branch_id) VALUES ('Graph Demo','{"title":"Graph Demo","body":[{"type":"graph-d3","graphId":1}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL);

INSERT INTO ohs_fault_trees (id, incident_id, tree_data, tenant_id, branch_id)
VALUES (1, 0, '{"name":"Top Event","children":[{"name":"Cause A"},{"name":"Cause B"}]}', NULL, NULL);

INSERT INTO modules (name, config, org_id, branch_id) VALUES ('Fishbone Diagram Manager','{"title":"Fishbone Diagram Manager","header":[{"type":"button","label":"Add Diagram","onClick":"addFishboneDiagram"}],"body":[{"type":"table","id":"fishboneList","options":{"tableName":"fishbone_diagrams","showGlobalSearch":false,"addButton":false,"rowActions":[{"label":"Edit","onClick":"editFishboneDiagram"}]}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL);
INSERT INTO modules (name, config, org_id, branch_id) VALUES ('Fault Tree Manager','{"title":"Fault Tree Manager","header":[{"type":"button","label":"Add Diagram","onClick":"addFaultTreeDiagram"}],"body":[{"type":"table","id":"ftList","options":{"tableName":"ohs_fault_trees","showGlobalSearch":false,"addButton":false,"rowActions":[{"label":"Edit","onClick":"editFaultTreeDiagram"}]}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL);
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description)
VALUES (
  'Five Whys Manager',
  '{"title":"Five Whys Manager","header":[{"type":"button","label":"Add Whys","onClick":"addFiveWhys"}],"body":[{"type":"table","options":{"tableName":"ohs_five_whys","showGlobalSearch":false,"rowActions":[{"label":"Edit","onClick":"editFiveWhys"}],"editFormId":83}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL, NULL, 'fa-question', 'Five Whys Manager','Five Whys Manager'
);


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Severity Dashboard','{"title":"Severity Dashboard","body":[{"type":"html","id":"severityDash","html":"<canvas id=\"sevChart\"></canvas><table class=\"treat-outcomes\"></table>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-notes-medical','Severity Dashboard','Severity Dashboard');

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('OHS Actions','{"title":"OHS Actions","header":[{"type":"button","label":"Add Action","formName":"OHS Action"}],"body":[{"type":"table","options":{"tableName":"ohs_actions","showGlobalSearch":false,"rowClick":"selectRecord"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-list-check','OHS Actions','OHS Actions');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('OHS Regulations','{"title":"OHS Regulations","header":[{"type":"button","label":"Add Regulation","formName":"OHS Regulation"}],"body":[{"type":"table","options":{"tableName":"ohs_regulations","showGlobalSearch":false,"rowClick":"selectRecord"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-book','OHS Regulations','OHS Regulations');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('OHS Compliance Tasks','{"title":"OHS Compliance Tasks","header":[{"type":"button","label":"Add Task","formName":"OHS Compliance Task"}],"body":[{"type":"table","options":{"tableName":"ohs_compliance_tasks","showGlobalSearch":false,"rowClick":"selectRecord"}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-list-check','OHS Compliance Tasks','OHS Compliance Tasks');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Safety Inductions','{"title":"Safety Inductions","header":[{"type":"button","label":"Add Induction","formName":"Safety Induction"}],"body":[{"type":"table","options":{"tableName":"safety_inductions","showGlobalSearch":false,"rowClick":"selectRecord","editFormId":10}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-helmet-safety','Safety Inductions','Safety Inductions');


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Issues Log','{"title":"Issues Log","header":[{"type":"button","label":"Add Issue","formName":"Issues Log"}],"body":[{"type":"table","options":{"tableName":"ohs_issues","showGlobalSearch":false,"addButton":false,"editFormId":66}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-bug','Issues Log','Issues Log');

INSERT INTO form_wizard_steps (wizard_id, step_number, form_id, condition_field, condition_value)
VALUES
  (3, 1, 96,NULL, NULL)
ON CONFLICT DO NOTHING;

INSERT INTO form_wizard_steps (wizard_id, step_number, form_id, condition_field, condition_value)
VALUES
  (5, 1, 103, NULL, NULL)
ON CONFLICT DO NOTHING;

-- Employee Licenses modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Employee Licenses','{"title":"Employee Licenses","header":[{"type":"button","label":"Add License","formId":11}],"body":[{"type":"table","options":{"tableName":"employee_licenses","showGlobalSearch":false,"editFormId":11}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-id-card','Employee Licenses','Employee Licenses');


-- Permits modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES
  ('Permits','{"title":"Permits","header":[{"type":"button","label":"Add Application","formId":15}],"body":[{"type":"table","options":{"tableName":"permit_applications","showGlobalSearch":false,"addButton":false,"editFormId":15,"nestedTableConfig":{"childTableName":"permit_inspections","linkField":"permit_application_id","columnsToShow":["inspector_id","scheduled_for","result","notes","hours_logged"],"editFormId":55}}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-passport','Permits','Permits');
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES ('Certification Tracking','{"title":"Certification Tracking","header":[{"type":"button","label":"Add New","formName":"Training Certificate"}],"body":[{"type":"table","options":{"tableName":"training_certificates","showGlobalSearch":false,"addButton":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL, NULL,'fa-certificate','Certification Tracking','Certification Tracking');


-- Needs & Expectations modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description)
VALUES ('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');

-- Link OHS modals to their forms and disable search/add buttons
UPDATE modules SET config='{"title":"OHS Incidents","header":[{"type":"button","label":"Add Incident","wizardId":1}],"body":[{"type":"table","options":{"tableName":"ohs_incidents","showGlobalSearch":false,"addButton":false,"editFormLookup":{"table":"ohs_incident_types","field":"classification","valueColumn":"name","formColumn":"form_id"}}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' WHERE name='OHS Incidents';

UPDATE modules SET config='{"title":"OHS Inspections","header":[{"type":"button","label":"Add Inspection","wizardId":3}],"body":[{"type":"table","options":{"tableName":"ohs_inspections","showGlobalSearch":false,"addButton":false,"editFormLookup":{"table":"ohs_inspection_types","field":"inspection_type_id","valueColumn":"id","formColumn":"form_id"}}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' WHERE name='OHS Inspections';

UPDATE modules SET config='{"title":"Hazard Management","header":[{"type":"button","label":"Add Hazard","formName":"OHS Hazard"}],"body":[{"type":"table","options":{"tableName":"ohs_hazards","showGlobalSearch":false,"addButton":false,"editFormId":7}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' WHERE name='Hazard Management';

UPDATE modules SET config='{"title":"Hazard Types","header":[{"type":"button","label":"Add Hazard Type","formName":"Hazard Type"}],"body":[{"type":"table","options":{"tableName":"ohs_hazard_types","showGlobalSearch":false,"addButton":false,"editFormId":56}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' WHERE name='Hazard Types';

UPDATE modules SET config='{"title":"OHS JSA","header":[{"type":"button","label":"Create JSA","formName":"Job Safety Analysis"}],"body":[{"type":"table","options":{"tableName":"ohs_jsas","showGlobalSearch":false,"addButton":false,"editFormId":6}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' WHERE name='OHS JSA';

UPDATE modules SET config='{"title":"OHS Assignment Rules","header":[{"type":"button","label":"Add Rule","formId":58}],"body":[{"type":"table","options":{"tableName":"ohs_assignment_rules","addButton":false,"editFormId":58}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' WHERE name='OHS Assignment Rules';

UPDATE modules SET config='{"title":"Inspection Assignment Rules","header":[{"type":"button","label":"Add Rule","formId":84}],"body":[{"type":"table","options":{"tableName":"inspection_assignment_rules","addButton":false,"editFormId":84}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' WHERE name='Inspection Assignment Rules';
UPDATE modules SET config='{"title":"WorkCOM Inspection Assignment Rules","header":[{"type":"button","label":"Add Rule","formId":101}],"body":[{"type":"table","options":{"tableName":"inspection_assignment_rules","addButton":false,"editFormId":101,"columnsToShow":["location","type","severity","inspector_id","district","assign","email","phone","send_email","send_sms","send_push"]}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' WHERE name='WorkCOM Inspection Assignment Rules';


-- Converted on 2025-07-11T16:28:17+12:00

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 4, 'OHS Incident', 'Default incident report form', 'ohs_incidents', '[{"label":"Date","field_name":"date","data_column":"date","type_id":3,"order_index":1},{"label":"Time","field_name":"time","data_column":"time","type_id":1,"order_index":2},{"label":"Classification","field_name":"classification","data_column":"classification","type_id":4,"order_index":3},{"label":"Nature","field_name":"nature","data_column":"nature","type_id":1,"order_index":4},{"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":5},{"label":"Status","field_name":"status","data_column":"status_id","type_id":4,"order_index":6,"options_table":"ohs_incident_statuses","options_label_column":"name","options_value_column":"id"}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 4);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 6, 'Job Safety Analysis', 'Record JSA details', 'ohs_jsas', '[{"label":"Title","field_name":"title","data_column":"title","type_id":1,"order_index":1},{"label":"Description","field_name":"description","data_column":"description","type_id":1,"order_index":2},{"label":"Doc Path","field_name":"doc_path","data_column":"doc_path","type_id":7,"order_index":3},{"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":4},{"label":"Department Id","field_name":"department_id","data_column":"department_id","type_id":2,"order_index":5},{"label":"Analysis Date","field_name":"analysis_date","data_column":"analysis_date","type_id":3,"order_index":6},{"label":"Prepared By","field_name":"prepared_by","data_column":"prepared_by","type_id":2,"order_index":7},{"label":"Team Member Ids","field_name":"team_member_ids","data_column":"team_member_ids","type_id":7,"order_index":8},{"label":"Ppe Required","field_name":"ppe_required","data_column":"ppe_required","type_id":7,"order_index":9},{"label":"Permit Requirements","field_name":"permit_requirements","data_column":"permit_requirements","type_id":7,"order_index":10},{"label":"Review Date","field_name":"review_date","data_column":"review_date","type_id":3,"order_index":11}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 6);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 7, 'OHS Hazard', 'Default hazard report form', 'ohs_hazards', '[{"label":"Description","field_name":"description","data_column":"description","type_id":1,"order_index":1},{"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":2},{"label":"Probability","field_name":"probability","data_column":"probability","type_id":4,"order_index":4,"options":[{"display_text":"1","value":"1"},{"display_text":"2","value":"2"},{"display_text":"3","value":"3"},{"display_text":"4","value":"4"},{"display_text":"5","value":"5"}]},{"label":"Severity","field_name":"severity","data_column":"severity","type_id":4,"order_index":5,"options":[{"display_text":"1","value":"1"},{"display_text":"2","value":"2"},{"display_text":"3","value":"3"},{"display_text":"4","value":"4"},{"display_text":"5","value":"5"}]},{"label":"Risk Level","field_name":"risk_level","data_column":"risk_level","type_id":2,"order_index":6},{"label":"Latitude","field_name":"latitude","data_column":"latitude","type_id":2,"order_index":7},{"label":"Longitude","field_name":"longitude","data_column":"longitude","type_id":2,"order_index":8},{"label":"Hazard Type","field_name":"hazard_type_id","data_column":"hazard_type_id","type_id":4,"order_index":3,"options_table":"ohs_hazard_types","options_label_column":"name","options_value_column":"id"}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 7);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 8, 'SCORM Package', 'Upload SCORM content', ' NULL', '[{"label":"Course Id","field_name":"course_id","data_column":"course_id","type_id":2,"order_index":1},{"label":"Title","field_name":"title","data_column":"title","type_id":1,"order_index":2},{"label":"Scorm Zip","field_name":"scorm_zip","data_column":"scorm_zip","type_id":7,"order_index":3},{"label":"Is Scorm","field_name":"is_scorm","data_column":"is_scorm","type_id":5,"order_index":4},{"label":"Caption","field_name":"caption","data_column":"caption","type_id":7,"order_index":5},{"label":"Language Code","field_name":"language_code","data_column":"language_code","type_id":1,"order_index":6}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 8);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 9, 'Training Path', 'Create a learning path', ' NULL', '[{"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},{"label":"Description","field_name":"description","data_column":"description","type_id":1,"order_index":2},{"label":"Department Id","field_name":"department_id","data_column":"department_id","type_id":2,"order_index":3},{"label":"Role Id","field_name":"role_id","data_column":"role_id","type_id":2,"order_index":4}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 9);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 10, 'Safety Induction', 'Record employee safety inductions', ' NULL', '[{"label":"Employee Id","field_name":"employee_id","data_column":"employee_id","type_id":2,"order_index":1},{"label":"Induction Date","field_name":"induction_date","data_column":"induction_date","type_id":3,"order_index":2},{"label":"Document Id","field_name":"document_id","data_column":"document_id","type_id":7,"order_index":3}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 10);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 11, 'Employee License', 'Track employee licenses', ' NULL', '[{"label":"Employee Id","field_name":"employee_id","data_column":"employee_id","type_id":2,"order_index":1},{"label":"License Type","field_name":"license_type","data_column":"license_type","type_id":1,"order_index":2},{"label":"License Number","field_name":"license_number","data_column":"license_number","type_id":1,"order_index":3},{"label":"Expiry Date","field_name":"expiry_date","data_column":"expiry_date","type_id":3,"order_index":4},{"label":"Document Id","field_name":"document_id","data_column":"document_id","type_id":7,"order_index":5}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 11);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 12, 'Training Requirement', 'Assign mandatory courses per project', ' NULL', '[{"label":"Project Id","field_name":"project_id","data_column":"project_id","type_id":2,"order_index":1},{"label":"Role Id","field_name":"role_id","data_column":"role_id","type_id":2,"order_index":2},{"label":"Location Id","field_name":"location_id","data_column":"location_id","type_id":2,"order_index":3},{"label":"Course Id","field_name":"course_id","data_column":"course_id","type_id":2,"order_index":4}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 12);
-- Category for incident forms
INSERT INTO form_categories (name, description, tenant_id, branch_id)
VALUES ('OHS Incident Forms', 'Incident reporting forms', NULL, NULL)
ON CONFLICT DO NOTHING;
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id, category_id)
VALUES (
  13,
  'OHS Incident – Property Damage',
  'Report property damage incidents',
  'ohs_incidents',
  $json$[{"label":"Short Description","field_name":"short_description","data_column":"short_description","type_id":1,"order_index":0},
      {"label":"Date of Damage","field_name":"date","data_column":"date","type_id":3,"order_index":1},
      {"label":"Time of Damage","field_name":"time","data_column":"time","type_id":1,"order_index":2},
      {"label":"Location","field_name":"location","data_column":"location","type_id":17,"order_index":3},
      {"label":"Asset Affected","field_name":"asset_affected","type_id":1,"order_index":4,"json_column":"form_data"},
      {"label":"Damage Description","field_name":"damage_description","type_id":1,"order_index":5,"json_column":"form_data"},
      {"label":"Estimated Cost","field_name":"estimated_cost","type_id":2,"order_index":6,"json_column":"form_data"},
      {"label":"Immediate Action","field_name":"immediate_action","type_id":1,"order_index":7,"json_column":"form_data"},
      {"label":"Severity","field_name":"severity","data_column":"severity","type_id":17,"order_index":12},
      {"label":"Responsible Person","field_name":"responsible_person","type_id":1,"order_index":8,"json_column":"form_data"},
      {"label":"Status","field_name":"status","type_id":4,"order_index":9,"options_table":"ohs_incident_statuses","options_label_column":"name","options_value_column":"id","json_column":"form_data"},
      {"label":"Investigator","field_name":"investigator_id","data_column":"investigator_id","type_id":17,"order_index":10,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Investigators\"]}}"},
      {"label":"Reviewer","field_name":"reviewer_id","data_column":"reviewer_id","type_id":17,"order_index":11,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Reviewers\"]}}"},
      {"label":"Classification","field_name":"classification","data_column":"classification","type_id":17,"order_index":12}]$json$::jsonb,
  NULL,
  NULL,
  1
)
ON CONFLICT (id) DO NOTHING;
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id, category_id)
VALUES (
  14,
  'OHS Incident – Environmental',
  'Report environmental incidents (spills, emissions, etc.)',
  'ohs_incidents',
  $json$
  [
    {"label":"Short Description","field_name":"short_description","data_column":"short_description","type_id":1,"order_index":0},
    {"label":"Date","field_name":"date","data_column":"date","type_id":3,"order_index":1},
    {"label":"Time","field_name":"time","data_column":"time","type_id":1,"order_index":2},
    {"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":3},
    {"label":"Type of Incident","field_name":"env_incident_type","data_column":"env_incident_type","type_id":1,"order_index":4},
    {"label":"Impact Description","field_name":"impact_description","data_column":"impact_description","type_id":1,"order_index":5},
    {"label":"Area Affected","field_name":"area_affected","data_column":"area_affected","type_id":1,"order_index":6},
    {"label":"Volume/Quantity","field_name":"volume","data_column":"volume","type_id":2,"order_index":7},
    {"label":"Remediation Action","field_name":"remediation_action","data_column":"remediation_action","type_id":1,"order_index":8},
    {"label":"Status","field_name":"status_id","data_column":"status_id","type_id":4,"order_index":9,"options_table":"ohs_incident_statuses","options_label_column":"name","options_value_column":"id"},
    {"label":"Investigator","field_name":"investigator_id","data_column":"investigator_id","type_id":4,"order_index":10,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Investigators\"]}}"},
    {"label":"Reviewer","field_name":"reviewer_id","data_column":"reviewer_id","type_id":4,"order_index":11,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Reviewers\"]}}"}, {"label":"Classification","field_name":"classification","data_column":"classification","type_id":16,"order_index":12}]
  $json$::jsonb,
  NULL,
  NULL,
  1
)
ON CONFLICT (id) DO NOTHING;
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id, category_id)
VALUES (
  15,
  'OHS Incident – Near Miss',
  'Report near-miss incidents',
  'ohs_incidents',
  $json$[{"label":"Short Description","field_name":"short_description","data_column":"short_description","type_id":1,"order_index":0},
      {"label":"Date","field_name":"date","data_column":"date","type_id":3,"order_index":1},
      {"label":"Time","field_name":"time","data_column":"time","type_id":1,"order_index":2},
      {"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":3},
      {"label":"Description of Near Miss","field_name":"description","type_id":1,"order_index":4,"json_column":"form_data"},
      {"label":"Hazard Identified","field_name":"hazard_identified","type_id":1,"order_index":5,"json_column":"form_data"},
      {"label":"Potential Consequence","field_name":"potential_consequence","type_id":1,"order_index":6,"json_column":"form_data"},
      {"label":"Immediate Action","field_name":"immediate_action","type_id":1,"order_index":7,"json_column":"form_data"},
      {"label":"Status","field_name":"status","type_id":4,"order_index":8,"options_table":"ohs_incident_statuses","options_label_column":"name","options_value_column":"id","json_column":"form_data"},
      {"label":"Investigator","field_name":"investigator_id","data_column":"investigator_id","type_id":4,"order_index":9,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Investigators\"]}}"},
      {"label":"Reviewer","field_name":"reviewer_id","data_column":"reviewer_id","type_id":4,"order_index":10,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Reviewers\"]}}"}, {"label":"Classification","field_name":"classification","data_column":"classification","type_id":16,"order_index":11}]$json$::jsonb,
  NULL,
  NULL,
  1
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id, category_id)
VALUES (
  16,
  'OHS Incident – First Aid',
  'Report incidents requiring first aid',
  'ohs_incidents',
  $json$[{"label":"Short Description","field_name":"short_description","data_column":"short_description","type_id":1,"order_index":0},
      {"label":"Date","field_name":"date","data_column":"date","type_id":3,"order_index":1},
      {"label":"Time","field_name":"time","data_column":"time","type_id":1,"order_index":2},
      {"label":"Location","field_name":"location","data_column":"location","type_id":17,"order_index":3},
      {"label":"Person Treated","field_name":"person_treated","type_id":1,"order_index":4,"json_column":"form_data"},
      {"label":"Injury Description","field_name":"injury_description","type_id":1,"order_index":5,"json_column":"form_data"},
      {"label":"First Aid Given","field_name":"first_aid_given","type_id":1,"order_index":6,"json_column":"form_data"},
      {"label":"Treated By","field_name":"treated_by","type_id":1,"order_index":7,"json_column":"form_data"},
      {"label":"Follow-Up Required?","field_name":"follow_up_required","type_id":4,"order_index":8,"options":[{"display_text":"Yes","value":"yes"},{"display_text":"No","value":"no"}],"json_column":"form_data"},
      {"label":"Classification","field_name":"classification","data_column":"classification","type_id":17,"order_index":12},
      {"label":"Severity","field_name":"severity","data_column":"severity","type_id":17,"order_index":13},
      {"label":"Status","field_name":"status","type_id":4,"order_index":9,"options_table":"ohs_incident_statuses","options_label_column":"name","options_value_column":"id","json_column":"form_data"},
      {"label":"Investigator","field_name":"investigator_id","data_column":"investigator_id","type_id":4,"order_index":10,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Investigators\"]}}"},
      {"label":"Reviewer","field_name":"reviewer_id","data_column":"reviewer_id","type_id":4,"order_index":11,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Reviewers\"]}}"}]$json$::jsonb,
  NULL,
  NULL,
  1
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id, category_id)
VALUES (
  17,
  'OHS Incident – Medical Treatment',
  'Report incidents requiring medical treatment',
  'ohs_incidents',
  $json$[{"label":"Short Description","field_name":"short_description","data_column":"short_description","type_id":1,"order_index":0},
      {"label":"Date","field_name":"date","data_column":"date","type_id":3,"order_index":1},
      {"label":"Time","field_name":"time","data_column":"time","type_id":1,"order_index":2},
      {"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":3},
      {"label":"Person Treated","field_name":"person_treated","type_id":1,"order_index":4,"json_column":"form_data"},
      {"label":"Injury Description","field_name":"injury_description","type_id":1,"order_index":5,"json_column":"form_data"},
      {"label":"Medical Details","field_name":"medical_details","type_id":1,"order_index":6,"json_column":"form_data"},
      {"label":"Provider","field_name":"medical_provider","type_id":1,"order_index":7,"json_column":"form_data"},
      {"label":"Follow-Up Required?","field_name":"follow_up_required","type_id":4,"order_index":8,"options":[{"display_text":"Yes","value":"yes"},{"display_text":"No","value":"no"}],"json_column":"form_data"},
      {"label":"Classification","field_name":"classification","data_column":"classification","type_id":16,"order_index":12},
      {"label":"Severity","field_name":"severity","data_column":"severity","type_id":16,"order_index":13},
      {"label":"Status","field_name":"status","type_id":4,"order_index":9,"options_table":"ohs_incident_statuses","options_label_column":"name","options_value_column":"id","json_column":"form_data"},
      {"label":"Investigator","field_name":"investigator_id","data_column":"investigator_id","type_id":4,"order_index":10,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Investigators\"]}}"},
      {"label":"Reviewer","field_name":"reviewer_id","data_column":"reviewer_id","type_id":4,"order_index":11,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Reviewers\"]}}"}]$json$::jsonb,
  NULL,
  NULL,
  1
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id, category_id)
VALUES (
  18,
  'OHS Incident – LTI',
  'Report lost-time injuries',
  'ohs_incidents',
  $json$[{"label":"Short Description","field_name":"short_description","data_column":"short_description","type_id":1,"order_index":0},
      {"label":"Date","field_name":"date","data_column":"date","type_id":3,"order_index":1},
      {"label":"Time","field_name":"time","data_column":"time","type_id":1,"order_index":2},
      {"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":3},
      {"label":"Person Injured","field_name":"person_injured","type_id":1,"order_index":4,"json_column":"form_data"},
      {"label":"Injury Description","field_name":"injury_description","type_id":1,"order_index":5,"json_column":"form_data"},
      {"label":"Days Lost","field_name":"days_lost","type_id":2,"order_index":6,"json_column":"form_data"},
        {"label":"Classification","field_name":"classification","data_column":"classification","type_id":16,"order_index":12},
        {"label":"Severity","field_name":"severity","data_column":"severity","type_id":16,"order_index":13},
      {"label":"Return-to-Work Date","field_name":"rtw_date","type_id":3,"order_index":7,"json_column":"form_data"},
      {"label":"Corrective Action","field_name":"corrective_action","type_id":1,"order_index":8,"json_column":"form_data"},
      {"label":"Status","field_name":"status","type_id":4,"order_index":9,"options_table":"ohs_incident_statuses","options_label_column":"name","options_value_column":"id","json_column":"form_data"},
      {"label":"Investigator","field_name":"investigator_id","data_column":"investigator_id","type_id":4,"order_index":10,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Investigators\"]}}"},
      {"label":"Reviewer","field_name":"reviewer_id","data_column":"reviewer_id","type_id":4,"order_index":11,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Reviewers\"]}}"}]$json$::jsonb,
  NULL,
  NULL,
  1
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id, category_id)
VALUES (
  19,
  'OHS Incident – Fatality',
  'Report fatalities',
  'ohs_incidents',
  $json$[{"label":"Short Description","field_name":"short_description","data_column":"short_description","type_id":1,"order_index":0},
      {"label":"Date","field_name":"date","data_column":"date","type_id":3,"order_index":1},
      {"label":"Time","field_name":"time","data_column":"time","type_id":1,"order_index":2},
      {"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":3},
      {"label":"Deceased Name","field_name":"deceased_name","type_id":1,"order_index":4,"json_column":"form_data"},
      {"label":"Cause of Death","field_name":"cause_of_death","type_id":1,"order_index":5,"json_column":"form_data"},
      {"label":"Reported to Authorities","field_name":"reported_authorities","type_id":3,"order_index":6,"json_column":"form_data"},
      {"label":"Coroner Details","field_name":"coroner_details","type_id":1,"order_index":7,"json_column":"form_data"},
      {"label":"Corrective Action","field_name":"corrective_action","type_id":1,"order_index":8,"json_column":"form_data"},
        {"label":"Classification","field_name":"classification","data_column":"classification","type_id":16,"order_index":12},
        {"label":"Severity","field_name":"severity","data_column":"severity","type_id":16,"order_index":13},
      {"label":"Status","field_name":"status","type_id":4,"order_index":9,"options_table":"ohs_incident_statuses","options_label_column":"name","options_value_column":"id","json_column":"form_data"},
      {"label":"Investigator","field_name":"investigator_id","data_column":"investigator_id","type_id":4,"order_index":10,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Investigators\"]}}"},
      {"label":"Reviewer","field_name":"reviewer_id","data_column":"reviewer_id","type_id":4,"order_index":11,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Reviewers\"]}}"}]$json$::jsonb,
  NULL,
  NULL,
  1
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id, category_id)
VALUES (
  20,
  'OHS Incident',
  'Default incident report form',
  'ohs_incidents',
  $json$[{"label":"Short Description","field_name":"short_description","data_column":"short_description","type_id":1,"order_index":0},
      {"label":"Date","field_name":"date","data_column":"date","type_id":3,"order_index":1},
      {"label":"Time","field_name":"time","data_column":"time","type_id":1,"order_index":2},
      {"label":"Classification","field_name":"classification","data_column":"classification","type_id":4,"order_index":3,"options":[{"display_text":"Injury","value":"injury"},{"display_text":"Property Damage","value":"property_damage"},{"display_text":"Environmental","value":"environmental"},{"display_text":"Near Miss","value":"near_miss"},{"display_text":"First Aid","value":"first_aid"},{"display_text":"Medical Treatment","value":"medical_treatment"},{"display_text":"LTI","value":"lti"},{"display_text":"Fatality","value":"fatality"}]},
      {"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":4},
      {"label":"Nature","field_name":"nature","type_id":1,"order_index":5,"json_column":"form_data"},
      {"label":"Workplace Part","field_name":"workplace_part","type_id":1,"order_index":6,"json_column":"form_data"},
      {"label":"Activity","field_name":"activity","type_id":1,"order_index":7,"json_column":"form_data"},
      {"label":"Unexpected","field_name":"unexpected","type_id":1,"order_index":8,"json_column":"form_data"},
      {"label":"How Sustained","field_name":"how_sustained","type_id":1,"order_index":9,"json_column":"form_data"},
      {"label":"Completed By","field_name":"completed_by","type_id":1,"order_index":10,"json_column":"form_data"},
      {"label":"Position","field_name":"position","type_id":1,"order_index":11,"json_column":"form_data"},
      {"label":"Outcome","field_name":"outcome","type_id":1,"order_index":12,"json_column":"form_data"},
      {"label":"Preventative Action","field_name":"preventative_action","type_id":1,"order_index":13,"json_column":"form_data"},
      {"label":"Investigator Id","field_name":"investigator_id","data_column":"investigator_id","type_id":4,"order_index":14,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Investigators\"]}}"},
      {"label":"Reviewer","field_name":"reviewer_id","data_column":"reviewer_id","type_id":4,"order_index":15,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Reviewers\"]}}"},
      {"label":"Status","field_name":"status","type_id":4,"order_index":16,"options_table":"ohs_incident_statuses","options_label_column":"name","options_value_column":"id","json_column":"form_data"},
      {"label":"Days Lost","field_name":"days_lost","type_id":2,"order_index":17,"json_column":"form_data"},
      {"label":"Return-to-Work Date","field_name":"rtw_date","type_id":3,"order_index":18,"json_column":"form_data"},
      {"label":"Restricted Duty Notes","field_name":"restricted_duty_notes","type_id":1,"order_index":19,"json_column":"form_data"},
        {"label":"Severity","field_name":"severity","data_column":"severity","type_id":16,"order_index":20}]$json$::jsonb,
  NULL,
  NULL,
  1
)
ON CONFLICT (id) DO NOTHING;


INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 29, 'Vehicle Registration', 'Capture fleet vehicle details', 'vehicles', '[{"label":"Vehicle Number","field_name":"vehicle_number","data_column":"vehicle_number","type_id":1,"order_index":1,"options_table":" NULL","options_label_column":" NULL","options_value_column":" NULL"},{"label":"Vin","field_name":"vin","data_column":"vin","type_id":1,"order_index":2,"options_table":" NULL","options_label_column":" NULL","options_value_column":" NULL"},{"label":"Engine Number","field_name":"engine_number","data_column":"engine_number","type_id":1,"order_index":3,"options_table":" NULL","options_label_column":" NULL","options_value_column":" NULL"},{"label":"Mileage","field_name":"mileage","data_column":"mileage","type_id":2,"order_index":4,"options_table":" NULL","options_label_column":" NULL","options_value_column":" NULL"},{"label":"Make","field_name":"make","data_column":"make","type_id":9,"order_index":5,"options_table":"vehicle_makes","options_label_column":"name","options_value_column":"id"},{"label":"Model","field_name":"model","data_column":"model","type_id":9,"order_index":6,"options_table":"vehicle_models","options_label_column":"name","options_value_column":"id"},{"label":"Type","field_name":"type","data_column":"type","type_id":9,"order_index":7,"options_table":"vehicle_types","options_label_column":"name","options_value_column":"id"},{"label":"Year","field_name":"year","data_column":"year","type_id":2,"order_index":8,"options_table":" NULL","options_label_column":" NULL","options_value_column":" NULL"},{"label":"Fitness Expires","field_name":"fitness_expires","data_column":"fitness_expires","type_id":3,"order_index":9,"options_table":" NULL","options_label_column":" NULL","options_value_column":" NULL"}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 29);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 30, 'Trip Log', 'Record vehicle trips with mileage', ' NULL', '[{"label":"Vehicle Id","field_name":"vehicle_id","data_column":"vehicle_id","type_id":2,"order_index":1},{"label":"Driver Id","field_name":"driver_id","data_column":"driver_id","type_id":2,"order_index":2},{"label":"Trip Date","field_name":"trip_date","data_column":"trip_date","type_id":3,"order_index":3},{"label":"Start Time","field_name":"start_time","data_column":"start_time","type_id":8,"order_index":4},{"label":"End Time","field_name":"end_time","data_column":"end_time","type_id":8,"order_index":5},{"label":"Start Location","field_name":"start_location","data_column":"start_location","type_id":1,"order_index":6},{"label":"End Location","field_name":"end_location","data_column":"end_location","type_id":1,"order_index":7},{"label":"Start Mileage","field_name":"start_mileage","data_column":"start_mileage","type_id":2,"order_index":8},{"label":"End Mileage","field_name":"end_mileage","data_column":"end_mileage","type_id":2,"order_index":9},{"label":"Distance Km","field_name":"distance_km","data_column":"distance_km","type_id":2,"order_index":10},{"label":"Status","field_name":"status","data_column":"status_id","type_id":1,"order_index":11}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 30);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 31, 'Service Type', 'Define maintenance intervals', 'service_types', '[{"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1,"options_table":" NULL","options_label_column":" NULL","options_value_column":" NULL"},{"label":"Description","field_name":"description","data_column":"description","type_id":7,"order_index":2,"options_table":" NULL","options_label_column":" NULL","options_value_column":" NULL"},{"label":"Make","field_name":"make","data_column":"make","type_id":9,"order_index":3,"options_table":"vehicle_makes","options_label_column":"name","options_value_column":"id"},{"label":"Model","field_name":"model","data_column":"model","type_id":9,"order_index":4,"options_table":"vehicle_models","options_label_column":"name","options_value_column":"id"},{"label":"Vehicle Type","field_name":"type","data_column":"type","type_id":9,"order_index":5,"options_table":"vehicle_types","options_label_column":"name","options_value_column":"id"},{"label":"Km Interval","field_name":"km_interval","data_column":"km_interval","type_id":2,"order_index":6,"options_table":" NULL","options_label_column":" NULL","options_value_column":" NULL"},{"label":"Month Interval","field_name":"month_interval","data_column":"month_interval","type_id":2,"order_index":7,"options_table":" NULL","options_label_column":" NULL","options_value_column":" NULL"}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 31);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 32, 'User Group', 'Manage user groups and permissions', 'groups', '[{"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},{"label":"Description","field_name":"description","data_column":"description","type_id":7,"order_index":2},{"label":"Parent Id","field_name":"parent_id","data_column":"parent_id","type_id":2,"order_index":3}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 32);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 33, 'Group Member', 'Assign users to groups', 'user_group_memberships', '[{"label":"User Id","field_name":"user_id","data_column":"user_id","type_id":2,"order_index":1},{"label":"Group Id","field_name":"group_id","data_column":"group_id","type_id":2,"order_index":2},{"label":"Role","field_name":"role","data_column":"role","type_id":1,"order_index":3}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 33);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 34, 'Vehicle Issue Report', 'Report vehicle issues', 'vehicle_reports', '[{"label":"Vehicle Id","field_name":"vehicle_id","data_column":"vehicle_id","type_id":9,"order_index":1,"options_table":"vehicles","options_label_column":"vehicle_number","options_value_column":"id"},{"label":"Report Type","field_name":"report_type","data_column":"report_type","type_id":4,"order_index":2,"options_table":"\n        NULL","options_label_column":" NULL","options_value_column":" NULL","options":[{"display_text":"minor","value":"minor"},{"display_text":"major","value":"major"},{"display_text":"breakdown","value":"breakdown"}]},{"label":"Description","field_name":"description","data_column":"description","type_id":7,"order_index":3,"options_table":"\n        NULL","options_label_column":" NULL","options_value_column":" NULL"}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 34);
/* ---- Client form (id 35) – JSON format ---- */
INSERT INTO forms
        (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT  35,
        'Client',
        'Client details',
        'clients',
        '[
          {"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1,"validation":"{\"unique\":{\"table\":\"clients\",\"column\":\"name\"}}"},
          {"label":"Phone","field_name":"phone","data_column":"phone","type_id":1,"order_index":2},
          {"label":"Email","field_name":"email","data_column":"email","type_id":1,"order_index":3},
          {"label":"Address","field_name":"address","data_column":"address","type_id":1,"order_index":4},
          {"label":"City","field_name":"city","data_column":"city","type_id":1,"order_index":5},
          {"label":"Country","field_name":"country","data_column":"country","type_id":1,"order_index":6},
          {"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":7},
          {"label":"Comments","field_name":"comments","data_column":"comments","type_id":8,"order_index":8}
         ]',
        NULL,   -- tenant_id
        NULL    -- branch_id
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 35);

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 36, 'Case', 'Case record', 'cases', '[
  {"label":"Client","field_name":"client_id","data_column":"client_id","type_id":4,"options_table":"clients","options_label_column":"name","options_value_column":"client_id","order_index":1},
  {"label":"Case Number","field_name":"case_number","data_column":"case_number","type_id":1,"order_index":2},
  {"label":"Title","field_name":"title","data_column":"title","type_id":1,"order_index":3},
  {"label":"Status","field_name":"status","data_column":"status_id","type_id":4,"order_index":4,"options":[{"display_text":"open","value":"open"},{"display_text":"closed","value":"closed"},{"display_text":"pending","value":"pending"}]},
  {"label":"Assigned Representative","field_name":"lead_rep_id","data_column":"lead_rep_id","type_id":4,"options_table":"users","options_label_column":"full_name","options_value_column":"user_id","order_index":7,"validation":"{\"filter_columns\":{\"groups.name\":[\"lawyer\",\"lawyers\"]}}"},
  {"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":8},
  {"label":"Initial Instructions","field_name":"initial_instructions","data_column":"initial_instructions","type_id":8,"order_index":9}
 ]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 36);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 37, 'Lawyer', 'Lawyer user', 'users', 
'[{"label":"Full Name","field_name":"full_name","data_column":"full_name","type_id":1,"order_index":1},
{"label":"Emails","field_name":"emails","data_column":"emails","type_id":16,"order_index":2},
{"label":"Phones","field_name":"phones","data_column":"phones","type_id":16,"order_index":3},
{"label":"Password","field_name":"password","data_column":"NULL","type_id":0,"order_index":4}]', 
NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 37);

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 38, 'Time Entries', 'Record hours worked on a case', 'time_entries',
'[{"label":"Case","field_name":"case_id","data_column":"reference_id","type_id":4,"options_table":"cases","options_label_column":"title","options_value_column":"case_id","order_index":1},
  {"label":"Date","field_name":"date","data_column":"date","default":"now()","type_id":3,"order_index":2},
  {"label":"Hours","field_name":"hours","data_column":"hours","type_id":2,"order_index":3},
  {"label":"Billable","field_name":"billable","data_column":"billable","type_id":5,"order_index":4},
  {"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":6},
  {"label":"Work Type","field_name":"work_type","data_column":"work_type_id","type_id":13,"options_table":"work_types","options_label_column":"name","options_value_column":"work_type_id","order_index":7},
  {"label":"Instructions","field_name":"instructions","data_column":"instructions","type_id":8,"order_index":9},
  {"label":"Documents","field_name":"documents","data_column":"document_ids","type_id":9,"order_index":10,"options":{"linkTable":"case_hour_documents","linkField":"document_id"}},
  {"field_name":"reference_table","data_column":"reference_table","type_id":17,"default_value":"cases","order_index":11}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 38);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 39, 'Tenant', 'Tenant details', 'tenants', '[{"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},{"label":"Subdomain","field_name":"subdomain","data_column":"subdomain","type_id":1,"order_index":2},{"label":"Custom Domain","field_name":"custom_domain","data_column":"custom_domain","type_id":1,"order_index":3},{"label":"Settings","field_name":"settings","data_column":"settings","type_id":7,"order_index":4},{"label":"Product Name","field_name":"product_name","data_column":"product_name","type_id":1,"order_index":5},{"label":"Dashboard Name","field_name":"dashboard_name","data_column":"dashboard_name","type_id":1,"order_index":6},{"label":"Logo Url","field_name":"logo_url","data_column":"logo_url","type_id":1,"order_index":7}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 39);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 40, 'Admin', 'Admin user', 'users', '[{"label":"Full Name","field_name":"full_name","data_column":"full_name","type_id":1,"order_index":1},{"label":"Email","field_name":"emails","data_column":"emails","type_id":16,"order_index":2},{"label":"Phones","field_name":"phones","data_column":"phones","type_id":16,"order_index":3},{"label":"Password","field_name":"password","data_column":"password","type_id":1,"order_index":4},{"label":"Tenant Id","field_name":"tenant_id","data_column":"tenant_id","type_id":4,"options_table":"tenants","options_label_column":"name","options_value_column":"id","order_index":5}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 40);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 41, 'User', 'User account details', 'users', '[]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 41);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 42, 'Vehicle Downtime', 'Record vehicle downtime periods', 'vehicle_downtime', '[]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 42);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 44, 'Invoice Settings', 'Configure invoice defaults', 'invoice_settings', '[{"label":"Template Id","field_name":"template_id","data_column":"template_id","type_id":0,"order_index":1,"options_table":"invoice_templates","options_label_column":"name","options_value_column":"id"},{"label":"Case Template Id","field_name":"case_template_id","data_column":"case_template_id","type_id":0,"order_index":2,"options_table":"invoice_templates","options_label_column":"name","options_value_column":"id"},{"label":"Logo Document Id","field_name":"logo_document_id","data_column":"logo_document_id","type_id":0,"order_index":3},{"label":"Instructions","field_name":"instructions","data_column":"instructions","type_id":0,"order_index":4},{"label":"Disclaimer","field_name":"disclaimer","data_column":"disclaimer","type_id":0,"order_index":5},{"label":"Bank Details","field_name":"bank_details","data_column":"bank_details","type_id":0,"order_index":6},{"label":"Number Format","field_name":"number_format","data_column":"number_format","type_id":0,"order_index":7},{"label":"Starting Number","field_name":"starting_number","data_column":"starting_number","type_id":0,"order_index":8},{"label":"Rollover Date","field_name":"rollover_date","data_column":"rollover_date","type_id":0,"order_index":9}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 44);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 45, 'Driver', 'Driver user', 'users', '[{"label":"Full Name","field_name":"full_name","data_column":"full_name","type_id":1,"order_index":1},{"label":"Email","field_name":"email","data_column":"NULL","type_id":0,"order_index":2},{"label":"Phone","field_name":"phone","data_column":"NULL","type_id":1,"order_index":3},{"label":"Password","field_name":"password","data_column":"NULL","type_id":0,"order_index":4}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 45);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 46, 'Fleet Engineer', 'Fleet engineer user', 'users', '[{"label":"Full Name","field_name":"full_name","data_column":"full_name","type_id":1,"order_index":1},{"label":"Email","field_name":"email","data_column":"NULL","type_id":0,"order_index":2},{"label":"Phone","field_name":"phone","data_column":"NULL","type_id":1,"order_index":3},{"label":"Password","field_name":"password","data_column":"NULL","type_id":0,"order_index":4}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 46);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 47, 'Case Instruction', 'Matter instruction', 'case_instructions', '[{"label":"Case Id","field_name":"case_id","data_column":"case_id","type_id":0,"order_index":1},{"label":"Instruction Date","field_name":"instruction_date","data_column":"instruction_date","type_id":3,"order_index":2},{"label":"Instruction","field_name":"instruction","data_column":"instruction","type_id":0,"order_index":3},{"label":"Documents","field_name":"document_ids","data_column":"document_ids","type_id":9,"order_index":4}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 47);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
  SELECT 48, 'Case Expense', 'Case related expense', 'expenses', 
  '[{"label":"Case","field_name":"case_id","data_column":"reference_id","type_id":4,"options_table":"cases","options_label_column":"title","options_value_column":"case_id","order_index":1},
    {"label":"Expense Date","field_name":"date","default":"now()","data_column":"date","type_id":3,"order_index":2},
    {"label":"Vendor","field_name":"vendor","data_column":"vendor_id","type_id":13,"options_table":"vendors","options_label_column":"vendorname","options_value_column":"vendor_id","order_index":3},
    {"label":"Invoice Number","field_name":"invoice_number","data_column":"invoice_number","type_id":1,"order_index":4},
    {"label":"Amount","field_name":"amount","data_column":"amount","type_id":2,"order_index":6},
    {"label":"Billable","field_name":"billable","data_column":"billable","type_id":5,"order_index":7},
    {"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":10},
    {"label":"Documents","field_name":"document_ids","data_column":"document_ids","type_id":9,"order_index":14},
    {"field_name":"reference_table","data_column":"reference_table","type_id":17,"default_value":"cases","order_index":15}]',
  NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 48);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
  SELECT 58, 'One Time Cost', 'One time service cost for a case', 'one_time_service_costs', 
  '[{"label":"Case","field_name":"case_id","data_column":"reference_id","type_id":4,"options_table":"cases","options_label_column":"title","options_value_column":"case_id","order_index":1},
    {"label":"Cost Date","field_name":"date","default":"now()","data_column":"date","type_id":3,"order_index":2},
    {"label":"Vendor","field_name":"vendor","data_column":"vendor_id","type_id":13,"options_table":"vendors","options_label_column":"vendorname","options_value_column":"vendor_id","order_index":3},
    {"label":"Invoice Number","field_name":"invoice_number","data_column":"invoice_number","type_id":1,"order_index":4},
    {"label":"Amount","field_name":"amount","data_column":"amount","type_id":2,"order_index":6},
    {"label":"Billable","field_name":"billable","data_column":"billable","type_id":5,"order_index":7},
    {"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":10},
    {"label":"Documents","field_name":"document_ids","data_column":"document_ids","type_id":9,"order_index":14},
    {"field_name":"reference_table","data_column":"reference_table","type_id":17,"default_value":"cases","order_index":15}]',
  NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 58);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 49, 'Case Invoice', 'Invoice for a case', 'invoices', '[{"label":"Reference Id","field_name":"invoice_id","data_column":"invoice_id","type_id":0,"order_index":1},{"label":"Invoice Number","field_name":"invoice_number","data_column":"invoice_number","type_id":0,"order_index":2},{"label":"Invoice Date","field_name":"invoice_date","data_column":"invoice_date","type_id":3,"order_index":3},{"label":"Amount","field_name":"amount","data_column":"amount","type_id":0,"order_index":4},{"label":"Status","field_name":"status","data_column":"status_id","type_id":0,"order_index":5},{"label":"Invoice Template Id","field_name":"invoice_template_id","data_column":"invoice_template_id","type_id":0,"order_index":6,"options_table":"invoice_templates","options_label_column":"name","options_value_column":"id"}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 49);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 50, 'Case Payment', 'Payment for a case invoice', 'payments', '[{"label": "Client", "field_name": "client_id", "data_column": "client_id", "type_id":4, "order_index":1, "options_table": "clients", "options_label_column": "name", "options_value_column": "client_id"}, {"label": "Invoice Id", "field_name": "invoice_id", "data_column": "invoice_id", "type_id":4, "order_index":2, "options_table": "invoices", "options_label_column": "invoice_number", "options_value_column": "id"}, {"label": "Payment Date", "field_name": "payment_date", "data_column": "payment_date", "type_id":3, "order_index":3}, {"label": "Payment Amount", "field_name": "payment_amount", "data_column": "payment_amount", "type_id":2, "order_index":4}, {"label": "Payment Method", "field_name": "payment_method", "data_column": "payment_method", "type_id":4, "order_index":5, "options": [{"display_text": "credit card", "value": "credit_card"}, {"display_text": "bank transfer", "value": "bank_transfer"}, {"display_text": "cash", "value": "cash"}, {"display_text": "check", "value": "check"}]}, {"label": "Reference Id", "field_name": "reference_id", "data_column": "reference_id", "type_id":1, "order_index":6}, {"label": "Reference Table", "field_name": "reference_table", "data_column": "reference_table", "type_id":1, "order_index":7, "default_value": "cases"}, {"label": "Notes", "field_name": "notes", "data_column": "notes", "type_id":8, "order_index":8}, {"label": "Status", "field_name": "status", "data_column": "status", "type_id":4, "order_index":9, "options": [{"display_text": "completed", "value": "completed"}, {"display_text": "pending", "value": "pending"}, {"display_text": "failed", "value": "failed"}]}, {"label": "Screenshots", "field_name": "screenshot_ids", "data_column": "screenshot_ids", "type_id":7, "order_index":10}, {"label": "Documents", "field_name": "document_ids", "data_column": "document_ids", "type_id":9, "order_index":11}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 50);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 51, 'Case Approval', 'Approve or reject case', NULL, '[{"label":"Case Id","field_name":"case_id","data_column":"case_id","type_id":0,"order_index":1},{"label":"Comments","field_name":"comments","data_column":"comments","type_id":0,"order_index":2}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 51);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 52, 'OHS Action', 'Action taken for an incident', 'ohs_actions', '[{"label":"Description","field_name":"description","data_column":"description","type_id":0,"order_index":1},{"label":"Responsible User Id","field_name":"responsible_user_id","data_column":"responsible_user_id","type_id":0,"order_index":2},{"label":"Target Date","field_name":"target_date","data_column":"target_date","type_id":0,"order_index":3},{"label":"Status","field_name":"status","data_column":"status_id","type_id":0,"order_index":4},{"label":"Completed At","field_name":"completed_at","data_column":"completed_at","type_id":0,"order_index":5}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 52);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 53, 'OHS Regulation', 'Record safety regulations', 'ohs_regulations', '[{"label":"Title","field_name":"title","data_column":"title","type_id":0,"order_index":1},{"label":"Description","field_name":"description","data_column":"description","type_id":0,"order_index":2},{"label":"Effective Date","field_name":"effective_date","data_column":"effective_date","type_id":0,"order_index":3}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 53);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 54, 'OHS Compliance Task', 'Task to meet a regulation', 'ohs_compliance_tasks', '[{"label":"Regulation Id","field_name":"regulation_id","data_column":"regulation_id","type_id":0,"order_index":1,"options_table":"ohs_regulations","options_label_column":"title","options_value_column":"id"},{"label":"Title","field_name":"title","data_column":"title","type_id":0,"order_index":2},{"label":"Due Date","field_name":"due_date","data_column":"due_date","type_id":0,"order_index":3},{"label":"Assigned To","field_name":"assigned_to","data_column":"assigned_to","type_id":0,"order_index":4},{"label":"Status","field_name":"status","data_column":"status_id","type_id":0,"order_index":5}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 54);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 55, 'Permit Inspection', 'Record permit inspections', 'permit_inspections', '[]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 55);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 56, 'Hazard Type', 'Manage hazard classifications', 'ohs_hazard_types', '[]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 56);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 57, 'Incident Type', 'Manage incident categories', 'ohs_incident_types',
'[{"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
  {"label":"Form Id","field_name":"form_id","data_column":"form_id","type_id":4,"options_table":"forms","options_label_column":"name","options_value_column":"id","order_index":2}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 57);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 58, 'OHS Assignment Rule', 'Investigator assignment rule', 'ohs_assignment_rules', '[]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 58);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 84, 'Inspection Assignment Rule', 'Inspector assignment rule', 'inspection_assignment_rules', '[]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 84);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 101, 'WorkCOM Inspection Assignment Rule', 'Inspector assignment rule', 'inspection_assignment_rules', '[{"label":"District","field_name":"district_id","data_column":"district_id","type_id":4,"options_table":"districts","options_label_column":"name","options_value_column":"id","order_index":1},
  {"label":"Assign","field_name":"inspector_id","data_column":"inspector_id","type_id":4,"options_table":"users","options_label_column":"full_name","options_value_column":"id","validation":"{\"filter_columns\":{\"groups.name\":[\"Investigators\"]}}","order_index":2},
  {"label":"Email","field_name":"email","data_column":"email","type_id":1,"order_index":3},
  {"label":"Send Email","field_name":"send_email","data_column":"send_email","type_id":5,"order_index":4},
  {"label":"Phone","field_name":"phone","data_column":"phone","type_id":1,"order_index":5},
  {"label":"Send SMS","field_name":"send_sms","data_column":"send_sms","type_id":5,"order_index":6},
  {"label":"Send Push","field_name":"send_push","data_column":"send_push","type_id":5,"order_index":7}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 101);
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  59,
  'OHS Incident Classification',
  'Select incident type and location',
  'ohs_incidents',
  '[
    {
      "label":"Classification",
      "field_name":"classification",
      "data_column":"classification",
      "type_id":4,
      "order_index":1,
      "options_table":"ohs_incident_types",
      "options_label_column":"name",
      "options_value_column":"name"
    },
    {
      "label":"Location",
      "field_name":"location",
      "data_column":"location",
      "type_id":4,
      "order_index":2,
      "options_table":"locations",
      "options_label_column":"name",
      "options_value_column":"name",
      "options":[{"display_text":"Other","value":"Other"}]
    },
    {
      "label":"Address",
      "field_name":"address",
      "data_column":"address",
      "type_id":7,
      "order_index":3,
      "show_when":{"field":"location","value":"Other"}
    },
    {
      "label":"Severity",
      "field_name":"severity",
      "data_column":"severity",
      "type_id":4,
      "order_index":4,
      "options_table":"ohs_severities",
      "options_label_column":"name",
      "options_value_column":"name"
    }
  ]',
  NULL,
  NULL
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 66, 'Issues Log', 'Record safety issues', 'ohs_issues', '[{"label":"Description","field_name":"description","data_column":"description","type_id":0,"order_index":1},{"label":"Type","field_name":"type","data_column":"type","type_id":0,"order_index":2},{"label":"Category","field_name":"category","data_column":"category","type_id":0,"order_index":3},{"label":"Impact","field_name":"impact","data_column":"impact","type_id":0,"order_index":4},{"label":"Risk Or Opportunity","field_name":"risk_or_opportunity","data_column":"risk_or_opportunity","type_id":0,"order_index":5},{"label":"Action Taken","field_name":"action_taken","data_column":"action_taken","type_id":0,"order_index":6},{"label":"Owner","field_name":"owner","data_column":"owner","type_id":0,"order_index":7},{"label":"Review Date","field_name":"review_date","data_column":"review_date","type_id":0,"order_index":8}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 66);

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  67,
  'Needs & Expectations Assessment',
  'Stakeholder needs and expectations',
  'ohs_needs_expectations',
  '[
    {"label":"Interested Party","field_name":"interested_party","data_column":"interested_party","type_id":0,"order_index":1},
    {"label":"Needs","field_name":"needs","data_column":"needs","type_id":0,"order_index":2},
    {"label":"Expectations","field_name":"expectations","data_column":"expectations","type_id":0,"order_index":3},
    {"label":"Compliance Required","field_name":"compliance_required","data_column":"compliance_required","type_id":0,"order_index":4},
    {"label":"Source","field_name":"source","data_column":"source","type_id":0,"order_index":5},
    {"label":"Status","field_name":"status_id","data_column":"status_id","type_id":0,"order_index":6},
    {"label":"Notes","field_name":"notes","data_column":"notes","type_id":0,"order_index":7},
    {"label":"Review Date","field_name":"review_date","data_column":"review_date","type_id":0,"order_index":8},
    {"label":"Review By","field_name":"review_by","data_column":"review_by","type_id":0,"order_index":9}
  ]',
  NULL,
  NULL
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 68, 'Inspection Type', 'Manage inspection categories', 'ohs_inspection_types',
'[{"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
  {"label":"Form Id","field_name":"form_id","data_column":"form_id","type_id":4,"options_table":"forms","options_label_column":"name","options_value_column":"id","order_index":2}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 68);

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 104, 'WorkCOM Inspection Type', 'Manage WorkCOM inspection categories', 'workcom_inspection_types',
 '[{"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
  {"label":"Form Id","field_name":"form_id","data_column":"form_id","type_id":4,"options_table":"forms","options_label_column":"name","options_value_column":"id","order_index":2}]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 104);

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 105, 'OHS Field Incident', 'Report field incidents', 'ohs_incidents', '[
  {"label":"Incident Type","field_name":"incident_type","json_column":"form_data","type_id":1,"order_index":1},
  {"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":2},
  {"label":"Description","field_name":"description","json_column":"form_data","type_id":8,"order_index":3},
  {"label":"Documents","field_name":"document_ids","json_column":"form_data","type_id":9,"order_index":4},
  {"label":"GPS","field_name":"gps","json_column":"form_data","type_id":20,"order_index":5}
]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 105);



INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  71,
  'Form Category',
  'Manage form categories',
  'form_categories',
  $json$
  [
    {"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
    {"label":"Description","field_name":"description","data_column":"description","type_id":7,"order_index":2}
  ]$json$::jsonb,
  NULL,
  NULL
)
ON CONFLICT (id) DO NOTHING;
-- Training Certificate form

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 72, 'Training Certificate', 'Employee training record', 'training_certificates',
'[
  {"label":"Certificate Name","field_name":"certificate_name","data_column":"certificate_name","type_id":1,"options_table":"training_courses","options_label_column":"name","options_value_column":"id","order_index":1},
  {"label":"Category","field_name":"category","data_column":"category","type_id":1,"options_table":"training_categories","options_label_column":"name","options_value_column":"id","order_index":2},
  {"label":"Employee Id","field_name":"employee_id","data_column":"certificate_user_id","type_id":4,"options_table":"users","options_label_column":"full_name","options_value_column":"id","order_index":3},
  {"label":"Issued At","field_name":"issued_at","data_column":"issued_at","type_id":3,"order_index":4},
  {"label":"Expiry Date","field_name":"expiry_date","data_column":"expiry_date","type_id":3,"order_index":5}
]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 72);

INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  73,
  'Investigator',
  'Investigator user',
  'users',
  $json$
  [
    {"label":"Full Name","field_name":"full_name","data_column":"full_name","type_id":1,"order_index":1},
    {"label":"Emails","field_name":"emails","data_column":"emails","type_id":15,"order_index":2},
    {"label":"Phones","field_name":"phones","data_column":"phones","type_id":15,"order_index":3},
    {"label":"Password","field_name":"password","data_column":null,"type_id":0,"order_index":4}
  ]
  $json$::jsonb,
  NULL,
  NULL
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  74,
  'Inspection Template',
  'Define reusable inspection templates',
  'ohs_inspection_templates',
  $json$
  [
    {"label":"Template Name","field_name":"template_name","data_column":"template_name","type_id":1,"order_index":1},
    {"label":"Form Id","field_name":"form_id","data_column":"form_id","type_id":4,"options_table":"forms","options_label_column":"name","options_value_column":"id","order_index":2},
    {"label":"Description","field_name":"description","data_column":"description","type_id":7,"order_index":3}
  ]
  $json$::jsonb,
  NULL,
  NULL
)
ON CONFLICT (id) DO NOTHING;


INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  97,
  'Safety Incident',
  'Record safety incidents',
  'safety_incidents',
  $json$
    [
    {"label":"Incident Type","field_name":"incident_type","data_column":"incident_type","type_id":4,"options_table":"safety_incident_types","options_label_column":"name","options_value_column":"id","order_index":1},
    {"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":2},
    {"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":3},
    {"label":"Documents","field_name":"document_ids","data_column":"document_ids","type_id":9,"order_index":4},
    {"label":"GPS","field_name":"gps","data_column":"gps","type_id":20,"order_index":5}
  ]
  $json$::jsonb,
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;


INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  107,
  'Safety Incident Classification',
  'Select incident type and location',
  'safety_incidents',
  '[{"label":"Incident Type","field_name":"incident_type","data_column":"incident_type","type_id":4,"options_table":"safety_incident_types","options_label_column":"name","options_value_column":"id","order_index":1},{"label":"Location","field_name":"location","data_column":"location","type_id":4,"options_table":"locations","options_label_column":"name","options_value_column":"name","order_index":2}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO form_buttons (form_id,label,action_type,action_payload,order_index)
VALUES (
  107,'Open Form','open-form',
  '{"lookupTable":"safety_incident_types","lookupField":"incident_type","valueColumn":"id","formColumn":"form_id","prefillFields":["incident_type","location"]}',
  1
) ON CONFLICT DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 108, 'Safety Field Incident', 'Report field incidents', 'safety_incidents', '[
  {"label":"Incident Type","field_name":"incident_type","data_column":"incident_type","type_id":4,"options_table":"safety_incident_types","options_label_column":"name","options_value_column":"id","order_index":1},
  {"label":"Location","field_name":"location","data_column":"location","type_id":4,"options_table":"locations","options_label_column":"name","options_value_column":"name","order_index":2},
  {"label":"Description","field_name":"description","json_column":"form_data","type_id":8,"order_index":3},
  {"label":"Documents","field_name":"document_ids","json_column":"form_data","type_id":9,"order_index":4},
  {"label":"GPS","field_name":"gps","json_column":"form_data","type_id":20,"order_index":5}
]', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 108);

INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  98,
  'Incident Alert Rule',
  'Alert configuration per incident type',
  'incident_alert_rules',
  $json$
  [
    {"label":"Incident Type","field_name":"incident_type_id","data_column":"incident_type_id","type_id":4,"options_table":"ohs_incident_types","options_label_column":"name","options_value_column":"id","order_index":1},
    {"label":"Severity","field_name":"severity","data_column":"severity","type_id":4,"order_index":2,"options":[{"display_text":"Minor","value":"Minor"},{"display_text":"Moderate","value":"Moderate"},{"display_text":"Major","value":"Major"},{"display_text":"Severe","value":"Severe"},{"display_text":"Critical","value":"Critical"}]},
    {"label":"Location","field_name":"location_id","data_column":"location_id","type_id":4,"options_table":"locations","options_label_column":"name","options_value_column":"id","options":[{"display_text":"All Locations","value":""}],"order_index":3},
    {"label":"Send Alert To","field_name":"send_alert_to","data_column":"send_alert_to","type_id":4,"options_table":"users","options_label_column":"full_name","options_value_column":"id","order_index":4},
    {"label":"Email","field_name":"email","data_column":"email","type_id":1,"order_index":5},
    {"label":"Send Email","field_name":"send_email","data_column":"send_email","type_id":5,"order_index":6},
    {"label":"Phone","field_name":"phone","data_column":"phone","type_id":1,"order_index":7},
    {"label":"Send SMS","field_name":"send_sms","data_column":"send_sms","type_id":5,"order_index":8},
    {"label":"Send Push","field_name":"send_push","data_column":"send_push","type_id":5,"order_index":9}
  ]
  $json$::jsonb,
  NULL,
  NULL
)
ON CONFLICT (id) DO NOTHING;


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Incident Alert Rules',
  '{"title":"Incident Alert Rules","header":[{"type":"button","label":"Add Rule","formName":"Incident Alert Rule"}],"body":[{"type":"table","options":{"tableName":"incident_alert_rules","showGlobalSearch":false,"addButton":false,"editFormId":98}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-bell',
  'Incident Alert Rules',
  'Incident Alert Rules'
);

-- Workflow to send notifications based on incident alert rules
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
  'Incident Alert Notifications',
  'ohs.incidentCreated',
  '{"conditions": []}',
  TRUE
);

INSERT INTO workflow_steps (workflow_id, step_order, step_type, config) VALUES
  ((SELECT id FROM workflows WHERE name='Incident Alert Notifications' LIMIT 1),1,'load_record','{"table":"ohs_incidents","id_key":"record_id","context_key":"incident"}'),
  ((SELECT id FROM workflows WHERE name='Incident Alert Notifications' LIMIT 1),2,'query_records','{"table":"incident_alert_rules","where":{"incident_type_id":"{classification}","severity":"{severity}","location_id":"{location_id}"},"context_key":"recipients"}'),
  ((SELECT id FROM workflows WHERE name='Incident Alert Notifications' LIMIT 1),3,'for_each_record','{"list_key":"recipients","steps":[{"type":"condition","field":"send_email","operator":"==","value":true},{"type":"email","to":"{email}","message":"Incident {record_id} reported"},{"type":"condition","field":"send_sms","operator":"==","value":true},{"type":"sms","to":"{phone}","message":"Incident {record_id} reported"},{"type":"condition","field":"send_push","operator":"==","value":true},{"type":"alert","user_id":"{send_alert_to}","title":"New Incident","body":"Incident {record_id} reported"}]}');


-- Forms and UI for Risk Assessments



-- Risk Assessment form - administrative details
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  75,
  'Risk Assessment',
  'Record assessment administrative info',
  'risk_assessments',
  '[{"label":"Type","field_name":"risk_assessment_type_id","data_column":"risk_assessment_type_id","type_id":4,"options_table":"risk_assessment_types","options_label_column":"name","options_value_column":"id","order_index":1},
    {"label":"Location","field_name":"location","data_column":"location","type_id":4,"options_table":"locations","options_label_column":"name","options_value_column":"name","order_index":2}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO form_buttons (form_id, label, action_type, action_payload, order_index)
VALUES (
  75,
  'Open Form',
  'open-form',
  '{"lookupTable":"risk_assessment_types","lookupField":"risk_assessment_type_id","valueColumn":"id","formColumn":"form_id","prefillFields":["risk_assessment_type_id","location"]}',
  1
) ON CONFLICT DO NOTHING;

-- Risk Assessment Hazard form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  76,
  'Risk Assessment Hazard',
  'Identify hazards and controls',
  'risk_assessment_hazards',
  '[{"label":"Hazard Description","field_name":"hazard_description","data_column":"hazard_description","type_id":7,"order_index":1},
    {"label":"Who Might Be Harmed","field_name":"who_might_be_harmed","data_column":"who_might_be_harmed","type_id":1,"order_index":2},
    {"label":"Existing Controls","field_name":"existing_control_measures","data_column":"existing_control_measures","type_id":7,"order_index":3},
    {"label":"Risk Rating Before","field_name":"risk_rating_before","data_column":"risk_rating_before","type_id":2,"order_index":4},
    {"label":"Additional Controls","field_name":"additional_controls","data_column":"additional_controls","type_id":7,"order_index":5},
    {"label":"Residual Risk","field_name":"residual_risk_rating","data_column":"residual_risk_rating","type_id":2,"order_index":6},
    {"label":"Responsible Person","field_name":"responsible_person","data_column":"responsible_person","type_id":1,"order_index":7}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Risk Assessment PPE form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  77,
  'Risk Assessment PPE',
  'Personal protective equipment checklist',
  'risk_assessment_ppe',
  '[{"label":"Checklist Item","field_name":"checklist_item","data_column":"checklist_item","type_id":1,"order_index":1}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Risk Assessment Sign-off form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  78,
  'Risk Assessment Sign-off',
  'Record assessment approvals',
  'risk_assessment_signoffs',
  '[{"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
    {"label":"Position","field_name":"position","data_column":"position","type_id":1,"order_index":2},
    {"label":"Signature","field_name":"signature","data_column":"signature","type_id":7,"order_index":3},
    {"label":"Signed At","field_name":"signed_at","data_column":"signed_at","type_id":3,"order_index":4}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Wizard configuration for risk assessments
INSERT INTO form_wizards (id, name, description, tenant_id, branch_id)
VALUES (4, 'Risk Assessment Wizard', 'Guided risk assessment entry', NULL, NULL)
ON CONFLICT DO NOTHING;

INSERT INTO form_wizard_steps (wizard_id, step_number, form_id, condition_field, condition_value)
VALUES
  (4, 1, 75, NULL, NULL)
ON CONFLICT DO NOTHING;


-- Risk Assessment Types modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Risk Assessment Types',
  '{"title":"Risk Assessment Types","header":[{"type":"button","label":"Add Type","formName":"Risk Assessment Type"}],"body":[{"type":"table","options":{"tableName":"risk_assessment_types","showGlobalSearch":false,"addButton":false,"editFormId":74}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-layer-group',
  'Risk Assessment Types',
  'Risk Assessment Types'
);

-- Risk Assessments modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Risk Assessments',
  '{"title":"Risk Assessments","header":[{"type":"button","label":"Add Assessment","wizardId":4}],"body":[{"type":"table","options":{"tableName":"risk_assessments","showGlobalSearch":false,"addButton":false,"editFormLookup":{"table":"risk_assessment_types","field":"risk_assessment_type_id","valueColumn":"id","formColumn":"form_id"}}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-clipboard-list',
  'Risk Assessments',
  'Risk Assessments'
);

-- Risk Identification form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  80,
  'Risk Identification',
  'Identify risks',
  'risk_identifications',
  $json$
  [
    {"label":"Risk Description","field_name":"risk_description","data_column":"risk_description","type_id":7,"order_index":1},
    {"label":"Risk category","field_name":"risk_category_id","data_column":"risk_category_id","type_id":4,"options_table":"risk_categories","options_label_column":"name","options_value_column":"id","order_index":2},
    {"label":"Root Cause","field_name":"root_cause","data_column":"root_cause","type_id":1,"order_index":3},
    {"label":"Likelihood","field_name":"likelihood","data_column":"likelihood","type_id":4,"order_index":4,"options":[{"display_text":"1","value":"1"},{"display_text":"2","value":"2"},{"display_text":"3","value":"3"},{"display_text":"4","value":"4"},{"display_text":"5","value":"5"}]},
    {"label":"Impact","field_name":"impact","data_column":"impact","type_id":4,"order_index":5,"options":[{"display_text":"1","value":"1"},{"display_text":"2","value":"2"},{"display_text":"3","value":"3"},{"display_text":"4","value":"4"},{"display_text":"5","value":"5"}]},
    {"label":"Current Controls","field_name":"current_controls","data_column":"current_controls","type_id":7,"order_index":6}
  ]$json$::jsonb,
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO table_views (form_id, name, columns, sort_by, sort_dir)
VALUES (
  80,
  'Default',
  '["risk_description","risk_category_id","risk_category_name","likelihood","impact",{"field":"risk_rating","formula":"impact * likelihood"}]',
  'risk_description',
  'ASC'
);

/* ---- Risk Category (id 74) – JSON format ---- */
INSERT INTO forms
        (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT  79,
        'Risk Category',
        'Risk classification',
        'risk_categories',
        '[
          {"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
          {"label":"Description","field_name":"description","data_column":"description","type_id":7,"order_index":2}
         ]',
        NULL,   -- tenant_id
        NULL    -- branch_id
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 79);

-- Risk Categories Manager modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description)
SELECT 'Risk Categories Manager',
       '{"title":"Risk Categories Manager","header":[{"type":"button","label":"Add Category","formName":"Risk Category"}],"body":[{"type":"table","options":{"tableName":"risk_categories","showGlobalSearch":false,"addButton":false,"rowClick":"selectRecord","editFormId":74}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
       NULL, NULL, 'fa-tags', 'Risk Categories Manager', 'Risk Categories Manager'
WHERE NOT EXISTS (SELECT 1 FROM modules WHERE name = 'Risk Categories Manager');



-- Risk Identification Manager modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description)
SELECT 'Risk Identification Manager',
       '{"title":"Risk Identification Manager","header":[{"type":"button","label":"Add Risk","formName":"Risk Identification"}],"body":[{"type":"table","options":{"tableName":"risk_identifications","showGlobalSearch":false,"addButton":false,"rowClick":"selectRecord","editFormId":75}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
       NULL, NULL, 'fa-exclamation-triangle', 'Risk Identification Manager', 'Risk Identification Manager'
WHERE NOT EXISTS (SELECT 1 FROM modules WHERE name = 'Risk Identification Manager');





INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  81
  ,
  'Calendar Category',
  'Manage calendar categories',
  'calendar_categories',
  $json$
  [
    {"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
    {"label":"Description","field_name":"description","data_column":"description","type_id":7,"order_index":2}
  ]
  $json$::jsonb,
  NULL,
  NULL
)
ON CONFLICT (id) DO NOTHING;


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Calendar Categories',
  '{"title":"Calendar Categories","header":[{"type":"button","label":"Add Category","formName":"Calendar Category"}],"body":[{"type":"table","options":{"tableName":"calendar_categories","showGlobalSearch":false,"addButton":false,"editFormId":74}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-tags',
  'Calendar Categories',
  'Calendar Categories'
);




INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Inspection Templates',
  '{"title":"Inspection Templates","header":[{"type":"button","label":"Add Template","formName":"Inspection Template"}],"body":[{"type":"table","options":{"tableName":"inspection_templates","showGlobalSearch":false,"addButton":false,"editFormId":70}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-clipboard-list',
  'Inspection Templates',
  'Inspection Templates'
);

-- Incident Status form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 82, 'Incident Status', 'Manage incident status options', 'ohs_incident_statuses',
       '[{"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
         {"label":"Purpose","field_name":"purpose","data_column":"purpose","type_id":7,"order_index":2}]',
       NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 82);


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description)
VALUES (
  'OHS Incident Statuses',
  '{"title":"OHS Incident Statuses","header":[{"type":"button","label":"Add Incident Status","formName":"Incident Status"}],"body":[{"type":"table","options":{"tableName":"ohs_incident_statuses","showGlobalSearch":false,"addButton":false,"editFormId":72}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-clipboard-check',
  'OHS Incident Statuses',
  'OHS Incident Statuses'
);

-- Five Whys form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 83, 'Five Whys', 'Record Five Whys analysis', 'ohs_five_whys',
       $json$
       [
         {"label":"Incident","field_name":"incident_id","data_column":"incident_id","type_id":4,"order_index":1,"options_table":"ohs_incidents","options_label_column":"id","options_value_column":"id"},
         {"label":"Why 1","field_name":"why1","json_column":"whys","type_id":1,"order_index":2},
         {"label":"Why 2","field_name":"why2","json_column":"whys","type_id":1,"order_index":3},
         {"label":"Why 3","field_name":"why3","json_column":"whys","type_id":1,"order_index":4},
         {"label":"Why 4","field_name":"why4","json_column":"whys","type_id":1,"order_index":5},
         {"label":"Why 5","field_name":"why5","json_column":"whys","type_id":1,"order_index":6}
       ]
       $json$::jsonb,
       NULL,
       NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 83);


SELECT setval('calendar_events_id_seq', (SELECT MAX(id) FROM calendar_events));



-- Corrective Action form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 83, 'Corrective Action', 'Track corrective actions for incidents', 'corrective_actions',
       '[{"label":"Incident","field_name":"incident_id","data_column":"incident_id","type_id":4,"order_index":1,"options_table":"ohs_incidents","options_label_column":"short_desc","options_value_column":"id"},
         {"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":2},
         {"label":"Description","field_name":"description","data_column":"description","type_id":7,"order_index":3},
         {"label":"Steps","field_name":"steps","data_column":"steps","type_id":7,"order_index":4},
         {"label":"Responsible Person","field_name":"responsible_user_id","data_column":"responsible_user_id","type_id":4,"order_index":5,"options_table":"users","options_label_column":"full_name","options_value_column":"id"},
         {"label":"Status","field_name":"status","data_column":"status","type_id":1,"order_index":6}]',
       NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 83);


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description)
VALUES (
  'Corrective Action Tracking',
  '{"title":"Corrective Action Tracking","header":[{"type":"button","label":"Add Corrective Action","formName":"Corrective Action"}],"body":[{"type":"table","options":{"tableName":"corrective_actions","columnsToShow":["incident_id","name","description","steps","responsible_user_id","status"],"showGlobalSearch":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-list-check',
  'Corrective Action Tracking',
  'Corrective Action Tracking'
);

-- Risk Assessment Type form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  84,
  'Risk Assessment Type',
  'Manage risk assessment classifications',
  'risk_assessment_types',
  '[{"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
    {"label":"Description","field_name":"description","data_column":"description","type_id":7,"order_index":2},
    {"label":"Form","field_name":"form_id","data_column":"form_id","type_id":4,"options_table":"forms","options_label_column":"name","options_value_column":"id","order_index":3}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Additional risk assessment forms
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  85,
  'Water Treatment Plant Risk Assessment',
  'Assessment of operational and chemical risks in treatment facilities',
  'risk_assessments',
  '[{"field_name":"risk_assessment_type_id","data_column":"risk_assessment_type_id","type_id":1,"order_index":0},
    {"label":"Title","field_name":"title","data_column":"title","type_id":1,"order_index":1},
    {"label":"Assessment Date","field_name":"assessment_date","data_column":"assessment_date","type_id":3,"order_index":2},
    {"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":3},
    {"label":"Assessor Names","field_name":"assessor_names","json_column":"form_data","type_id":1,"order_index":4},
    {"label":"Review Date","field_name":"review_date","data_column":"review_date","type_id":3,"order_index":5},
    {"label":"Approver","field_name":"approver_name","json_column":"form_data","type_id":1,"order_index":6},
    {"label":"Work Description","field_name":"work_description","json_column":"form_data","type_id":7,"order_index":7},
    {"label":"Permit Requirements","field_name":"permit_requirements","json_column":"form_data","type_id":7,"order_index":8},
    {"label":"Chemical Used","field_name":"chemical","json_column":"form_data","type_id":1,"order_index":9},
    {"label":"Hazard Identified","field_name":"hazard","json_column":"form_data","type_id":7,"order_index":10},
    {"label":"Risk Rating","field_name":"risk_rating","json_column":"form_data","type_id":2,"order_index":11},
    {"label":"Control Measures","field_name":"controls","json_column":"form_data","type_id":7,"order_index":12}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  86,
  'Pipeline & Infrastructure Risk Assessment',
  'Evaluation of potential failure points across distribution infrastructure',
  'risk_assessments',
  '[{"field_name":"risk_assessment_type_id","data_column":"risk_assessment_type_id","type_id":1,"order_index":0},
    {"label":"Title","field_name":"title","data_column":"title","type_id":1,"order_index":1},
    {"label":"Assessment Date","field_name":"assessment_date","data_column":"assessment_date","type_id":3,"order_index":2},
    {"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":3},
    {"label":"Assessor Names","field_name":"assessor_names","json_column":"form_data","type_id":1,"order_index":4},
    {"label":"Review Date","field_name":"review_date","data_column":"review_date","type_id":3,"order_index":5},
    {"label":"Approver","field_name":"approver_name","json_column":"form_data","type_id":1,"order_index":6},
    {"label":"Work Description","field_name":"work_description","json_column":"form_data","type_id":7,"order_index":7},
    {"label":"Permit Requirements","field_name":"permit_requirements","json_column":"form_data","type_id":7,"order_index":8},
    {"label":"Pipe Segment","field_name":"pipe_segment","json_column":"form_data","type_id":1,"order_index":9},
    {"label":"Material Type","field_name":"material","json_column":"form_data","type_id":1,"order_index":10},
    {"label":"Identified Risk","field_name":"risk","json_column":"form_data","type_id":7,"order_index":11},
    {"label":"Failure Likelihood","field_name":"likelihood","json_column":"form_data","type_id":2,"order_index":12},
    {"label":"Mitigation Action","field_name":"action","json_column":"form_data","type_id":7,"order_index":13}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  87,
  'Environmental and Climate Risk Assessment',
  'Assesses environmental vulnerabilities to water supply and treatment',
  'risk_assessments',
  '[{"field_name":"risk_assessment_type_id","data_column":"risk_assessment_type_id","type_id":1,"order_index":0},
    {"label":"Title","field_name":"title","data_column":"title","type_id":1,"order_index":1},
    {"label":"Assessment Date","field_name":"assessment_date","data_column":"assessment_date","type_id":3,"order_index":2},
    {"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":3},
    {"label":"Assessor Names","field_name":"assessor_names","json_column":"form_data","type_id":1,"order_index":4},
    {"label":"Review Date","field_name":"review_date","data_column":"review_date","type_id":3,"order_index":5},
    {"label":"Approver","field_name":"approver_name","json_column":"form_data","type_id":1,"order_index":6},
    {"label":"Work Description","field_name":"work_description","json_column":"form_data","type_id":7,"order_index":7},
    {"label":"Permit Requirements","field_name":"permit_requirements","json_column":"form_data","type_id":7,"order_index":8},
    {"label":"Area Assessed","field_name":"area","json_column":"form_data","type_id":1,"order_index":9},
    {"label":"Environmental Risk","field_name":"env_risk","json_column":"form_data","type_id":7,"order_index":10},
    {"label":"Climate Trigger","field_name":"trigger","json_column":"form_data","type_id":1,"order_index":11},
    {"label":"Severity","field_name":"severity","json_column":"form_data","type_id":2,"order_index":12},
    {"label":"Control Plan","field_name":"plan","json_column":"form_data","type_id":7,"order_index":13}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  88,
  'Occupational Health & Safety Risk Assessment',
  'OHS-focused hazard evaluation in operational areas and fieldwork',
  'risk_assessments',
  '[{"field_name":"risk_assessment_type_id","data_column":"risk_assessment_type_id","type_id":1,"order_index":0},
    {"label":"Title","field_name":"title","data_column":"title","type_id":1,"order_index":1},
    {"label":"Assessment Date","field_name":"assessment_date","data_column":"assessment_date","type_id":3,"order_index":2},
    {"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":3},
    {"label":"Assessor Names","field_name":"assessor_names","json_column":"form_data","type_id":1,"order_index":4},
    {"label":"Review Date","field_name":"review_date","data_column":"review_date","type_id":3,"order_index":5},
    {"label":"Approver","field_name":"approver_name","json_column":"form_data","type_id":1,"order_index":6},
    {"label":"Work Description","field_name":"work_description","json_column":"form_data","type_id":7,"order_index":7},
    {"label":"Permit Requirements","field_name":"permit_requirements","json_column":"form_data","type_id":7,"order_index":8},
    {"label":"Job Task","field_name":"task","json_column":"form_data","type_id":1,"order_index":9},
    {"label":"Identified Hazard","field_name":"hazard","json_column":"form_data","type_id":7,"order_index":10},
    {"label":"Risk Level","field_name":"risk_level","json_column":"form_data","type_id":2,"order_index":11},
    {"label":"PPE Required","field_name":"ppe","json_column":"form_data","type_id":1,"order_index":12},
    {"label":"First Aid Measures","field_name":"first_aid","json_column":"form_data","type_id":7,"order_index":13}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  89,
  'Cybersecurity & SCADA Risk Assessment',
  'Review of IT security and SCADA operational risk vulnerabilities',
  'risk_assessments',
  '[{"field_name":"risk_assessment_type_id","data_column":"risk_assessment_type_id","type_id":1,"order_index":0},
    {"label":"Title","field_name":"title","data_column":"title","type_id":1,"order_index":1},
    {"label":"Assessment Date","field_name":"assessment_date","data_column":"assessment_date","type_id":3,"order_index":2},
    {"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":3},
    {"label":"Assessor Names","field_name":"assessor_names","json_column":"form_data","type_id":1,"order_index":4},
    {"label":"Review Date","field_name":"review_date","data_column":"review_date","type_id":3,"order_index":5},
    {"label":"Approver","field_name":"approver_name","json_column":"form_data","type_id":1,"order_index":6},
    {"label":"Work Description","field_name":"work_description","json_column":"form_data","type_id":7,"order_index":7},
    {"label":"Permit Requirements","field_name":"permit_requirements","json_column":"form_data","type_id":7,"order_index":8},
    {"label":"System/Module","field_name":"system","json_column":"form_data","type_id":1,"order_index":9},
    {"label":"Threat Type","field_name":"threat","json_column":"form_data","type_id":1,"order_index":10},
    {"label":"Vulnerability Description","field_name":"vuln","json_column":"form_data","type_id":7,"order_index":11},
    {"label":"Impact Rating","field_name":"impact","json_column":"form_data","type_id":2,"order_index":12},
    {"label":"Mitigation Strategy","field_name":"strategy","json_column":"form_data","type_id":7,"order_index":13}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  90,
  'Community & Stakeholder Risk Assessment',
  'Analyzes public-facing risks and reputational threats from service issues',
  'risk_assessments',
  '[{"field_name":"risk_assessment_type_id","data_column":"risk_assessment_type_id","type_id":1,"order_index":0},
    {"label":"Title","field_name":"title","data_column":"title","type_id":1,"order_index":1},
    {"label":"Assessment Date","field_name":"assessment_date","data_column":"assessment_date","type_id":3,"order_index":2},
    {"label":"Location","field_name":"location","data_column":"location","type_id":1,"order_index":3},
    {"label":"Assessor Names","field_name":"assessor_names","json_column":"form_data","type_id":1,"order_index":4},
    {"label":"Review Date","field_name":"review_date","data_column":"review_date","type_id":3,"order_index":5},
    {"label":"Approver","field_name":"approver_name","json_column":"form_data","type_id":1,"order_index":6},
    {"label":"Work Description","field_name":"work_description","json_column":"form_data","type_id":7,"order_index":7},
    {"label":"Permit Requirements","field_name":"permit_requirements","json_column":"form_data","type_id":7,"order_index":8},
    {"label":"Community Area","field_name":"community","json_column":"form_data","type_id":1,"order_index":9},
    {"label":"Public Concern","field_name":"concern","json_column":"form_data","type_id":7,"order_index":10},
    {"label":"Likelihood of Escalation","field_name":"escalation","json_column":"form_data","type_id":2,"order_index":11},
    {"label":"Communication Strategy","field_name":"communication","json_column":"form_data","type_id":7,"order_index":12},
    {"label":"Resolution Timeline","field_name":"timeline","json_column":"form_data","type_id":1,"order_index":13}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Default risk assessment types
INSERT INTO risk_assessment_types (id, name, description, form_id, tenant_id, branch_id) VALUES
  (1, 'Water Treatment Plant Risk Assessment', 'Assessment of operational and chemical risks in treatment facilities', 85, NULL, NULL),
  (2, 'Pipeline & Infrastructure Risk Assessment', 'Evaluation of potential failure points across distribution infrastructure', 86, NULL, NULL),
  (3, 'Environmental and Climate Risk Assessment', 'Assesses environmental vulnerabilities to water supply and treatment', 87, NULL, NULL),
  (4, 'Occupational Health & Safety Risk Assessment', 'OHS-focused hazard evaluation in operational areas and fieldwork', 88, NULL, NULL),
  (5, 'Cybersecurity & SCADA Risk Assessment', 'Review of IT security and SCADA operational risk vulnerabilities', 89, NULL, NULL),
  (6, 'Community & Stakeholder Risk Assessment', 'Analyzes public-facing risks and reputational threats from service issues', 90, NULL, NULL)
ON CONFLICT (id) DO NOTHING;



-- Insert Incidents by classification modal referencing saved graph
INSERT INTO modules
  (name, config, org_id, branch_id, icon, aria_label, description)
VALUES (
  'Incidents by classification',
  '{"title":"Incidents by classification","body":[{"type":"graph-d3","graphId":1}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL, NULL, 'fa-chart-line', 'Incidents by classification', 'Incidents by classification');

-- Insert Incident Trends modal referencing second graph
INSERT INTO modules
  (name, config, org_id, branch_id, icon, aria_label, description)
VALUES (
  'Incident Trends',
  '{"title":"Incident Trends","body":[{"type":"graph-d3","graphId":2,"options":{"timeUnits":["day","week","month","year"]}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL, NULL, 'fa-chart-bar', 'Incident Trends', 'Incident Trends');

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES ('Workplace Registration','{"title":"Workplace Registration","header":[{"type":"button","label":"New Registration","formName":"Workplace Registration"}],"body":[{"type":"table","options":{"tableName":"workplace_registrations","rowClick":"selectRecord","rowActions":[{"label":"Detail","onClick":"openSelectedDetail"},{"label":"Invoice","onClick":"openWorkplaceInvoiceForm"},{"label":"Review","onClick":"openWorkplaceReview"}],"inlineEdit":true,"editFormId":98}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL,NULL,'fa-building','Workplace Registration','Workplace Registration');

-- Inspection Request form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  99,
  'Inspection Request',
  'Submit request for workplace inspection',
  'inspection_requests',
  $json$
  [
    {"label":"Workplace","field_name":"workplace_id","data_column":"workplace_id","type_id":4,"options_table":"workplace_registrations","options_label_column":"company_name","options_value_column":"id","order_index":1},
    {"label":"Description","field_name":"description","data_column":"description","type_id":7,"order_index":2},
    {"label":"Preferred Date","field_name":"preferred_date","data_column":"preferred_date","type_id":3,"order_index":3}
  ]$json$::jsonb,
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Inspection Request',
  '{"title":"Inspection Request","header":[{"type":"button","label":"Add Request","formName":"Inspection Request"}],"body":[{"type":"table","options":{"tableName":"inspection_requests","rowClick":"selectRecord","rowActions":[{"label":"Detail","onClick":"openSelectedDetail"}],"inlineEdit":true,"editFormId":99}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL, NULL, 'fa-clipboard-list', 'Inspection Request', 'Inspection Request');

-- WorkCOM Inspection Assignment form and modal
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  100,
  'WorkCOM Inspection Assignment',
  'Assign inspector to a workplace inspection',
  'workplace_inspections',
  $json$
  [
    {"label":"Inspection Request","field_name":"inspection_request_id","data_column":"inspection_request_id","type_id":4,"options_table":"inspection_requests","options_label_column":"id","options_value_column":"id","order_index":1},
    {"label":"Inspector","field_name":"inspector_id","data_column":"inspector_id","type_id":4,"options_table":"users","options_label_column":"full_name","options_value_column":"id","order_index":2,"validation":"{\"filter_columns\":{\"groups.name\":[\"Investigators\"]}}"}
  ]$json$::jsonb,
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'WorkCOM Inspection Assignment',
  '{"title":"Assign Inspector","header":[{"type":"button","label":"Add Assignment","formName":"WorkCOM Inspection Assignment"}],"body":[{"type":"table","options":{"tableName":"workplace_inspections","showGlobalSearch":false,"addButton":false,"editFormId":100}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL, NULL, 'fa-user-check', 'WorkCOM Inspection Assignment', 'WorkCOM Inspection Assignment');

-- WorkCOM OHS Inspections modal with Assign Inspector action
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'WorkCOM OHS Inspections',
  '{"title":"WorkCOM OHS Inspections","header":[{"type":"button","label":"New Inspection","wizardId":5}],"body":[{"type":"table","options":{"tableName":"workplace_inspections","showGlobalSearch":false,"addButton":false,"rowActions":[{"label":"Assign Inspector","formName":"WorkCOM Inspection Assignment"}]}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL, NULL, 'fa-clipboard-check', 'WorkCOM OHS Inspections', 'WorkCOM OHS Inspections');

UPDATE modules SET config='{"title":"WorkCOM OHS Inspections","header":[{"type":"button","label":"New Inspection","wizardId":5}],"body":[{"type":"table","options":{"tableName":"workplace_inspections","showGlobalSearch":false,"addButton":false,"rowActions":[{"label":"Assign Inspector","formName":"WorkCOM Inspection Assignment"}]}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}' WHERE name='WorkCOM OHS Inspections';

-- WorkCOM Inspection Start form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  103,
  'WorkCOM Inspection Start',
  'Select inspection type and workplace',
  'workcom_inspections',
  '[{"label":"Workplace","field_name":"workplace_id","data_column":"workplace_id","type_id":4,"options_table":"workplace_registrations","options_label_column":"company_name","options_value_column":"id","order_index":1},
    {"label":"Inspection Request","field_name":"inspection_request_id","data_column":"inspection_request_id","type_id":4,"options_table":"inspection_requests","options_label_column":"id","options_value_column":"id","order_index":2},
    {"label":"Inspection Type","field_name":"workcom_inspection_type_id","data_column":"inspection_type_id","type_id":4,"options_table":"workcom_inspection_types","options_label_column":"name","options_value_column":"id","order_index":3}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO form_buttons (form_id,label,action_type,action_payload,order_index)
VALUES (
  103,'Open Form','open-form',
  '{"lookupTable":"workcom_inspection_types","lookupField":"workcom_inspection_type_id","valueColumn":"id","formColumn":"form_id","prefillFields":["workcom_inspection_type_id","workplace_id","inspection_request_id"]}',
  1
) ON CONFLICT DO NOTHING;

-- Workplace Notice form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  104,
  'Workplace Notice',
  'Notice of certain workplaces',
  'invoices',
  $json$
  [
    {"label":"Business Name","field_name":"business_name","data_column":"details","json_column":"business_name","type_id":1,"order_index":1},
    {"label":"Trading Name","field_name":"trading_name","data_column":"details","json_column":"trading_name","type_id":1,"order_index":2},
    {"label":"Workplace Address","field_name":"workplace_address","data_column":"details","json_column":"workplace_address","type_id":7,"order_index":3},
    {"label":"Postal Address","field_name":"postal_address","data_column":"details","json_column":"postal_address","type_id":7,"order_index":4},
    {"label":"Commencement Date","field_name":"commencement_date","data_column":"details","json_column":"commencement_date","type_id":3,"order_index":5},
    {"label":"Invoice Template","field_name":"invoice_template_id","data_column":"invoice_template_id","type_id":4,"options_table":"invoice_templates","options_label_column":"name","options_value_column":"id","order_index":6}
  ]$json$::jsonb,
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Workplace Notice',
  '{"title":"Workplace Notice","body":[{"type":"form","formName":"Workplace Notice"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL, NULL, 'fa-file-invoice', 'Workplace Notice', 'Workplace Notice');

-- Safety Incident Type form and modal
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  105,
  'Safety Incident Type',
  'Define safety incident type',
  'safety_incident_types',
  $json$
  [
    {"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1}
  ]$json$::jsonb,
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;


INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Safety Incident Types',
  '{"title":"Safety Incident Types","header":[{"type":"button","label":"Add Type","formName":"Safety Incident Type"}],"body":[{"type":"table","options":{"tableName":"safety_incident_types","showGlobalSearch":false,"addButton":false,"editFormId":105}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL, NULL, 'fa-list', 'Safety Incident Types', 'Safety Incident Types'
);


-- Default resource types
INSERT INTO resource_types (code, name, description, tenant_id, branch_id)
VALUES
  ('module', 'Module', 'Application module entry point', NULL, NULL),
  ('table', 'Table', 'Backend table name', NULL, NULL),
  ('form', 'Form', 'Form builder form', NULL, NULL),
  ('wizard', 'Wizard', 'Multi step wizard', NULL, NULL),
  ('chart', 'Chart', 'Chart or report', NULL, NULL),
  ('worksheet', 'Worksheet', 'Spreadsheet worksheet', NULL, NULL),
  ('table_column', 'Table Column', 'Specific table column resource', NULL, NULL)
ON CONFLICT DO NOTHING;



INSERT INTO invoice_templates (name, file_path, tenant_id)
SELECT 'Notice of Certain Workplaces', 'templates/invoices/workplace_notice.php', NULL
WHERE NOT EXISTS (
    SELECT 1 FROM invoice_templates WHERE file_path='templates/invoices/workplace_notice.php'
);

-- Ensure case invoice type exists
INSERT INTO invoice_types (name)
SELECT 'case'
WHERE NOT EXISTS (
    SELECT 1 FROM invoice_types WHERE name='case'
);

-- Ensure workplace invoice type exists
INSERT INTO invoice_types (name)
SELECT 'workplace'
WHERE NOT EXISTS (
    SELECT 1 FROM invoice_types WHERE name='workplace'
);

-- Ensure schedule_of_costs invoice type exists
INSERT INTO invoice_types (name)
SELECT 'schedule_of_costs'
WHERE NOT EXISTS (
    SELECT 1 FROM invoice_types WHERE name='schedule_of_costs'
);


-- Workflow for workplace registration approval
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
  'Workplace Registration Review',
  'workplace.registration.created',
  '{"conditions": []}',
  TRUE
);

INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
VALUES (
  (SELECT id FROM workflows WHERE name='Workplace Registration Review' LIMIT 1),
  1,
  'save_record',
  '{"table":"workplace_registrations","data":{"status":"approved"},"match":{"id":"{id}"}}'
);

-- Map tenant_id then create invoice within Workplace Registration Review workflow
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 2, 'map_fields', '{"map":{"tenant_id":"tenant_id"}}'
FROM workflows w
WHERE w.name='Workplace Registration Review'
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws
    WHERE ws.workflow_id = w.id AND ws.step_order = 2 AND ws.step_type='map_fields'
  );

INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 3, 'create_invoice', '{}'
FROM workflows w
WHERE w.name='Workplace Registration Review'
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws
    WHERE ws.workflow_id = w.id AND ws.step_order = 3 AND ws.step_type='create_invoice'
  );

INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 4, 'save_record', '{"table":"workplace_registrations","data":{"invoice_id":"{invoice_id}"},"match":{"id":"{id}"}}'
FROM workflows w
WHERE w.name='Workplace Registration Review'
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws
    WHERE ws.workflow_id = w.id AND ws.step_order = 4 AND ws.step_type='save_record'
  );

INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 5, 'queue_notification', '{"entity":"workplace_registrations","record_id":"{id}","event_type":"update","payload":{"invoice_id":"{invoice_id}"}}'
FROM workflows w
WHERE w.name='Workplace Registration Review'
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws
    WHERE ws.workflow_id = w.id AND ws.step_order = 5 AND ws.step_type='queue_notification'
  );

-- Open Workplace Invoice Preview after invoice creation
INSERT INTO workflow_steps (workflow_id, step_order, step_type, config)
SELECT w.id, 6, 'open_modal', '{"modal":"Workplace Invoice Preview"}'
FROM workflows w
WHERE w.name='Workplace Registration Review'
  AND NOT EXISTS (
    SELECT 1 FROM workflow_steps ws
    WHERE ws.workflow_id = w.id AND ws.step_order = 6 AND ws.step_type='open_modal'
      AND ws.config->>'modal'='Workplace Invoice Preview'
  );

-- Workflow to generate Workplace Registration certificate
INSERT INTO workflows (name, trigger_event, definition, is_active)
VALUES (
  'Workplace Registration Certificate',
  'workplace.registration.certificate',
  '{"conditions": []}',
  TRUE
);

INSERT INTO workflow_steps (workflow_id, step_order, step_type, config) VALUES
  (
    (SELECT id FROM workflows WHERE name='Workplace Registration Certificate' LIMIT 1),
    1,
    'create_certificate',
    json_build_object(
      'template_id', (SELECT id FROM certificate_templates WHERE name='Basic Certificate' LIMIT 1),
      'submission_id_key', 'id',
      'status', 'issued'
    )
  ),
  ((SELECT id FROM workflows WHERE name='Workplace Registration Certificate' LIMIT 1),2,'generate_certificate_document','{}'),
  ((SELECT id FROM workflows WHERE name='Workplace Registration Certificate' LIMIT 1),3,'queue_notification','{"entity":"form_certificates","record_id":"{certificate_id}","event_type":"update"}'),
  ((SELECT id FROM workflows WHERE name='Workplace Registration Certificate' LIMIT 1),4,'open_modal','{"modal":"Certificates"}');

INSERT INTO workflow_event_triggers (event_name, table_name, trigger_type, operations, field_map)
VALUES (
  'workplace.registration.certificate',
  'workplace_registrations',
  'update',
  '["update"]',
  '{"id":"id"}'
);

SELECT setval('workflow_steps_id_seq', (SELECT MAX(id) FROM workflow_steps));

-- Restore Workplace Registration form and modal

-- Workplace Registration form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  106,
  'Workplace Registration',
  'Record new workplaces',
  'workplace_registrations',
  $json$
 [
  {
    "label": "Name of owner(s) of business",
    "type_id": 16,
    "field_name": "name_of_owners_of_business",
    "order_index": 0,
    "placeholder": "Name of owner(s) of business:"
  },
  {
    "label": "Trading name",
    "type_id": 1,
    "field_name": "trading_name",
    "order_index": 1,
    "placeholder": "Trading name"
  },
  {
    "label": "Address of workplace",
    "type_id": 1,
    "field_name": "address_of_workplace",
    "order_index": 2,
    "placeholder": "Address of workplace"
  },
  {
    "label": "Postal address",
    "type_id": 1,
    "field_name": "postal_address",
    "order_index": 3,
    "placeholder": "Postal address"
  },
  {
    "label": "Date of business commenced operations at these premises",
    "type_id": 3,
    "field_name": "date_of_business_commenced_operations_at_these_premises",
    "order_index": 4,
    "placeholder": "Date of business commenced operations at these premises"
  },
  {
    "label": "Details of ownership of premises",
    "type_id": 8,
    "field_name": "details_of_ownership_of_premises",
    "order_index": 5,
    "placeholder": "Details of ownership of premises"
  },
  {
    "label": "Name & address of Owner of the Building:",
    "type_id": 1,
    "field_name": "name_and_address_of_owner_of_the_building",
    "order_index": 6,
    "placeholder": "Name & address of Owner of the Building"
  },
  {
    "label": "Details of business activity: (Refer to First Schedule)",
    "type_id": 8,
    "field_name": "details_of_business_activiy",
    "order_index": 7,
    "placeholder": "Details of business activity"
  },
  {
    "label": "Main Activity Conducted by Business",
    "type_id": 1,
    "field_name": "main_activity_conducted_by_business",
    "order_index": 8,
    "placeholder": "Main Activity Conducted by Business"
  },
  {
    "label": "Principal Articles to be manufactured repaired or sold",
    "type_id": 1,
    "field_name": "principal_articles_to_be_manufactured_repaired_or_sold",
    "order_index": 9,
    "placeholder": "Principal Articles to be manufactured repaired or sold"
  },
  {
    "label": "Are goods sold at this location:",
    "type_id": 5,
    "field_name": "goods_sold",
    "order_index": 10,
    "placeholder": ""
  },
  {
    "label": "Sales Type",
    "options": [
      {
        "value": "wholesale",
        "display_text": "Wholesale"
      },
      {
        "value": "retail",
        "display_text": "Retail"
      },
      {
        "value": "both",
        "display_text": "Both"
      }
    ],
    "type_id": 4,
    "field_name": "sales_type",
    "order_index": 11,
    "condition_field": "goods_sold",
    "condition_value": "1"
  },
  {
    "label": "Has Site Previously been used as a workplace",
    "type_id": 1,
    "field_name": "has_site_previously_been_used_as_a_workplace",
    "order_index": 12,
    "placeholder": "Has Site Previously been used as a workplace"
  },
  {
    "label": "Name of previous Occupier",
    "type_id": 1,
    "field_name": "name_of_previous_occupier",
    "order_index": 13,
    "placeholder": "Name of previous Occupier"
  },
  {
    "label": "Contact telephone number",
    "type_id": 2,
    "field_name": "contact_telephone_number",
    "order_index": 14,
    "placeholder": "Contact telephone number"
  },
  {
    "label": "Maximum Number of Employees",
    "type_id": 2,
    "field_name": "maximum_number_of_employees",
    "order_index": 14,
    "placeholder": "Maximum Number of Employees"
  },
  {
    "label": "Committee Members",
    "type_id": 8,
    "field_name": "committee_members",
    "order_index": 14,
    "placeholder": "Committee Members"
  },
  {
    "label": "Training Undergone",
    "type_id": 5,
    "field_name": "training_undergone",
    "order_index": 14,
    "placeholder": "Training Undergone"
  },
  {
    "label": "Districts",
    "type_id": 4,
    "field_name": "districts",
    "order_index": 14,
    "placeholder": "Districts",
    "options_table":"districts",
    "options_label_column":"name",
    "options_value_column":"id"
  },
  {
    "label": "OHS Policy",
    "type_id": 9,
    "field_name": "ohs_policy",
    "order_index": 14,
    "placeholder": "OHS Policy"
  },
  {
    "label": "Meeting Minutes",
    "type_id": 9,
    "field_name": "minutes",
    "order_index": 14,
    "placeholder": "Meeting Minutes"
  }
  ]$json$::jsonb,
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Workplace Registration modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES ('Workplace Registration','{"title":"Workplace Registration","header":[{"type":"button","label":"New Registration","formName":"Workplace Registration"}],"body":[{"type":"table","options":{"tableName":"workplace_registrations","rowClick":"selectRecord","rowActions":[{"label":"Detail","onClick":"openSelectedDetail"},{"label":"Invoice","onClick":"openWorkplaceInvoiceForm"},{"label":"Review","onClick":"openWorkplaceReview"}],"inlineEdit":true,"editFormId":106}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',NULL,NULL,'fa-building','Workplace Registration','Workplace Registration');
-- Form Submission Review modal
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Form Submission Review',
  '{"title":"Form Submission Review","body":[{"type":"html","id":"formReviewMount","html":"<div id=\"formReviewMount\" data-submission-id=\"{{submissionId}}\"></div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-clipboard-check',
  'Form Submission Review',
  'Form Submission Review'
);
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Certificates',
  '{"title":"Certificates","body":[{"type":"html","html":"<div id=\"certificatePreview\"></div><div id=\"certificatesMount\"></div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-certificate',
  'Certificates',
  'Certificates'
);

CREATE OR REPLACE FUNCTION notify_notification_queue()
RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('taf_queue', row_to_json(NEW)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS notification_notify ON notification_queue;
CREATE TRIGGER notification_notify
AFTER INSERT ON notification_queue
FOR EACH ROW EXECUTE PROCEDURE notify_notification_queue();

-- Seed legacy modules referenced in FrontEnd/Dashboard.php
-- Ensure unique names via unique index and idempotent inserts

CREATE UNIQUE INDEX IF NOT EXISTS legacy_resources_name_idx ON legacy_resources (name);

INSERT INTO legacy_resources (name, description, isDeleted) VALUES
  ('SystemSettings', 'Legacy module from Dashboard.php', false),
  ('CountryHolidays', 'Legacy module from Dashboard.php', false),
  ('Calendar', 'Legacy module from Dashboard.php', false),
  ('Logout', 'Legacy module from Dashboard.php', false),
  ('WorkflowInstances', 'Legacy module from Dashboard.php', false),
  ('KanbanBoard', 'Legacy module from Dashboard.php', false),
  ('CustomizeDashboard', 'Legacy module from Dashboard.php', false),
  ('FormBuilder', 'Legacy module from Dashboard.php', false),
  ('DetailViewBuilder', 'Legacy module from Dashboard.php', false),
  ('PageCustomizer', 'Legacy module from Dashboard.php', false),
  ('PageConfigBuilder', 'Legacy module from Dashboard.php', false),
  ('ModuleBuilder', 'Legacy module from Dashboard.php', false),
  ('WorkflowBuilder', 'Legacy module from Dashboard.php', false),
  ('TableBuilder', 'Legacy module from Dashboard.php', false),
  ('TableManager', 'Legacy module from Dashboard.php', false),
  ('ModuleManager', 'Legacy module from Dashboard.php', false),
  ('PermissionsManager', 'Legacy module from Dashboard.php', false),
  ('Employees', 'Employee management module', false)
ON CONFLICT (name) DO NOTHING;

-- Vendor Management form
INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  110,
  'Vendor',
  'Manage Vendors',
  'Vendors',
  '[{"label":"Vendor Name","field_name":"vendorName","data_column":"vendorName","type_id":1,"order_index":1},
    {"label":"Business Registration Number","field_name":"businessRegNumber","data_column":"businessRegNumber","type_id":1,"order_index":2},
    {"label":"TIN Number","field_name":"tin","data_column":"tin","type_id":1,"order_index":3}
  ]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

INSERT INTO modules (module_id, name, config, org_id, branch_id, icon, aria_label, description)
VALUES (
  '0194a8b2-0017-7123-8123-123456789abc'::uuid,
  'Vendor Management',
  '{"title":"Vendor Management","header":[{"type":"button","label":"Add Vendor","formName":"Vendor"}],"body":[{"type":"table","options":{"tableName":"Vendors","showGlobalSearch":false,"addButton":false,"editFormId":110}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-store',
  'Vendor Management',
  'Vendor Management'
);

-- Customer Management form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
SELECT 109, 'Customer', 'Manage customer information', 'customers',
'[{"label":"Customer Type","field_name":"customer_type","data_column":"customer_type","type_id":4,"order_index":1,"options":[{"display_text":"Individual","value":"individual"},{"display_text":"Corporate","value":"corporate"},{"display_text":"Government","value":"government"}]},
  {"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":2},
  {"label":"Email","field_name":"email","data_column":"email","type_id":1,"order_index":3},
  {"label":"Phone Number","field_name":"phone_number","data_column":"phone_number","type_id":1,"order_index":4},
  {"label":"Address","field_name":"address","data_column":"address","type_id":1,"order_index":5},
  {"label":"TIN","field_name":"tin","data_column":"tin","type_id":1,"order_index":6},
  {"label":"Risk Rating","field_name":"risk_rating","data_column":"risk_rating","type_id":4,"order_index":7,"options":[{"display_text":"Low","value":"low"},{"display_text":"Medium","value":"medium"},{"display_text":"High","value":"high"}]},
  {"label":"Compliance Status","field_name":"compliance_status","data_column":"compliance_status","type_id":4,"order_index":8,"options":[{"display_text":"Compliant","value":"compliant"},{"display_text":"Non-Compliant","value":"non-compliant"},{"display_text":"Pending","value":"pending"}]}]',
NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 109);

INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
'Customer Management',
'{"title":"Customer Management","header":[{"type":"button","label":"Add Customer","formName":"Customer"}],"body":[{"type":"table","options":{"tableName":"customers","showGlobalSearch":false,"addButton":false,"editFormId":109,"rowActions":[{"label":"Pay","onClick":"payCustomer"}]}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
NULL,
NULL,
'fa-users',
'Customer Management',
'Customer Management'
);

-- Alter products table to make price numeric
ALTER TABLE products ALTER COLUMN price TYPE numeric;

-- Product form
INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  111,
  'Product',
  'Manage Products',
  'products',
  '[{"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
    {"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":2},
    {"label":"Price","field_name":"price","data_column":"price","type_id":1,"order_index":3},
    {"label":"Barcode (GTIN)","field_name":"barcode","data_column":"barcode","type_id":1,"order_index":4},
    {"label":"Product Number","field_name":"product_number","data_column":"product_number","type_id":1,"order_index":5},
    {"label":"Brand","field_name":"brand","data_column":"brand","type_id":1,"order_index":6},
    {"label":"Category","field_name":"category_id","data_column":"category_id","type_id":13,"options_table":"product_categories","options_label_column":"name","options_value_column":"category_id","order_index":7,"required":true},
    {"label":"VAT","field_name":"vat","json_column":"tax_rates","type_id":4,"order_index":8,"options":[{"display_text":"12.5%","value":"G"},{"display_text":"0%","value":"A"}]},
    {"label":"STT","field_name":"stt","json_column":"tax_rates","type_id":4,"order_index":9,"options":[{"display_text":"Not Applicable","value":""},{"display_text":"6%","value":"E"}]}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Product Management module
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description)
VALUES (
  'Products',
  '{"title":"Products","header":[{"type":"button","label":"Add Product","formName":"Product"}],"body":[{"type":"table","options":{"tableName":"products","showGlobalSearch":false,"addButton":false,"editFormId":111}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-box',
  'Products',
  'Products'
);

-- Software Info form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  112,
  'Software Info',
  'Software Information',
  'software_info',
  '[{"label":"Manufacturer Name","field_name":"manufacturer_name","data_column":"manufacturer_name","type_id":1,"order_index":1,"default_value":"Cybex PTE Limited."},
    {"label":"Serial Number","field_name":"serial_number","data_column":"serial_number","type_id":1,"order_index":2,"default_value":"CYBEXPTE-ERP-2025-001."},
    {"label":"Software Version","field_name":"software_version","data_column":"software_version","type_id":1,"order_index":3,"default_value":"2025.08"}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Software Info module
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Software Info',
  '{"title":"Software Info","header":[{"type":"button","label":"Add Software Info","formName":"Software Info"}],"body":[{"type":"table","options":{"tableName":"software_info","showGlobalSearch":false,"addButton":false,"editFormId":112}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-info-circle',
  'Software Info',
  'Software Info'
);

-- Insert default system info record
INSERT INTO software_info (manufacturer_name, serial_number, software_version)
SELECT 'Cybex PTE Limited.', 'CYBEXPTE-ERP-2025-001.', '2025.08'
WHERE NOT EXISTS (SELECT 1 FROM software_info LIMIT 1);

-- POS Settings form
INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  113,
  'POS Settings',
  'Point of Sale Settings',
  'pos_settings',
  '[{"label":"Password","field_name":"password","data_column":"password","type_id":1,"order_index":1,"default_value":"2CBP6HMW"},
    {"label":"PIN","field_name":"pin","data_column":"pin","type_id":1,"order_index":2,"default_value":"3840"}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- POS Settings module
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description)
VALUES (
  'POS Settings',
  '{"title":"POS Settings","header":[{"type":"button","label":"Edit Settings","formName":"POS Settings"}],"body":[{"type":"table","options":{"tableName":"pos_settings","showGlobalSearch":false,"addButton":false,"editFormId":113}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-cash-register',
  'POS Settings',
  'POS Settings'
);

-- Procurement form
INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  114,
  'Restaurant Product',
  'Manage Restaurant Products',
  'products',
  '[{"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
{"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":2},
{"label":"Quantity","field_name":"quantity","data_column":"quantity","type_id":1,"order_index":3},
{"label":"Price","field_name":"price","data_column":"price","type_id":1,"order_index":4},
{"label":"Barcode (GTIN)","field_name":"barcode","data_column":"barcode","type_id":1,"order_index":5},
{"label":"Product Number","field_name":"product_number","data_column":"product_number","type_id":1,"order_index":6},
{"label":"Brand","field_name":"brand","data_column":"brand","type_id":1,"order_index":7},
{"label":"Category","field_name":"category_id","data_column":"category_id","type_id":13,"options_table":"product_categories","options_label_column":"name","options_value_column":"category_id","order_index":8},
{"label":"VAT","field_name":"vat","json_column":"tax_rates","type_id":4,"order_index":10,"options":[{"display_text":"12.5%","value":"G"},{"display_text":"0%","value":"A"}]},
{"label":"STT","field_name":"stt","json_column":"tax_rates","type_id":4,"order_index":11,"options":[{"display_text":"Not Applicable","value":""},{"display_text":"6%","value":"E"}]},
{"label":"Ingredients","field_name":"ingredients","type_id":22,"columns":[{"name":"ingredient_id","label":"Ingredient","type":"select-search","options_table":"ingredients","options_label_column":"name","options_value_column":"id"},{"name":"quantity","label":"Quantity","type":"number"},{"name":"units","label":"Units","type":"select","options":[{"display_text":"Pieces","value":"pieces"},{"display_text":"Kg","value":"kg"},{"display_text":"Grams","value":"grams"},{"display_text":"Liters","value":"liters"},{"display_text":"Ml","value":"ml"}]}],"link_table":"ingredients_to_products","link_columns":{"product_id":"id","ingredient_id":"ingredient_id","quantity":"quantity","units":"unit"},"order_index":9}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Procurement form
INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  115,
  'Procurement',
  'Manage procurement and L.P.O records',
  'procurements',
  '[
    {"label":"L.P.O Vendor","field_name":"vendor_id","data_column":"vendor_id","type_id":4,"options_table":"Vendors","options_label_column":"vendorName","options_value_column":"id","order_index":1},
    {"label":"Quotation Number","field_name":"quotation_number","data_column":"quotation_number","type_id":1,"order_index":3},
    {"label":"Invoice Number","field_name":"invoice_number","data_column":"invoice_number","type_id":1,"order_index":4},
    {"label":"Requested By Date","field_name":"requested_by_date","data_column":"requested_by_date","type_id":3,"order_index":5},
    {"label":"Product Barcode","field_name":"product_barcode","data_column":"product_barcode","type_id":1,"order_index":2,"show_product_below":true}
  ]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Procurement module
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description)
VALUES (
  'Procurement',
  '{"title":"Procurement","header":[{"type":"button","label":"Add Procurement","formName":"Procurement","onSubmit":"closeModal"}],"body":[{"type":"table","options":{"tableName":"procurements","showGlobalSearch":false,"addButton":false,"editFormId":115}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'fa-file-invoice-dollar',
  'Procurement',
  'Procurement Management'
);

-- Ingredients form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  116,
  'Ingredients',
  'Manage Ingredients',
  'ingredients',
  '[{"label":"Ingredient Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
    {"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":2},
    {"label":"Units","field_name":"units","data_column":"units","type_id":4,"order_index":4,"options":[{"display_text":"Pieces","value":"pieces"},{"display_text":"Kg","value":"kg"},{"display_text":"Grams","value":"grams"},{"display_text":"Liters","value":"liters"},{"display_text":"Ml","value":"ml"}]},
    {"label":"Quantity","field_name":"quantity","data_column":"quantity","type_id":1,"order_index":3},
    {"label":"Price","field_name":"price","data_column":"price","type_id":1,"order_index":5}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Restaurant Products module
INSERT INTO modules (name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  'Restaurant Products',
  '{
    "title": "Restaurant Products",
    "header": [
      {
        "type": "button",
        "label": "Add Ingredient",
        "formName": "Ingredients"
      },
      {
        "type": "button",
        "label": "Add Product",
        "formName": "Restaurant Product"
      }
    ],
    "body": [
      {
        "type": "tabs",
        "tabs": [
          {
            "label": "Ingredients",
            "elements": [
              {
                "type": "table",
                "options": {
                  "tableName": "ingredients",
                  "editFormId": 116
                }
              }
            ]
          },
          {
            "label": "Products",
            "elements": [
              {
                "type": "table",
                "options": {
                  "tableName": "products",
                  "editFormId": 114
                }
              }
            ]
          }
        ]
      }
    ],
    "footer": [
      {
        "type": "button",
        "label": "Close",
        "onClick": "closeModal"
      }
    ]
  }',
  NULL,
  NULL,
  'fa-utensils',
  'Restaurant Products',
  'Restaurant Products'
);


