-- Migration: Restore detailed hour breakdown columns in Payroll module
-- Date: 2025-10-10
-- Description: 
--   Restores the correct column configuration for the Payroll module's Worked Hours tab.
--   Previously, columns were simplified to ["name", "start_date", "end_date", "total_hours"]
--   which was incorrect as "total_hours" field doesn't exist in time_clock_aggregates view.
--   
--   This migration:
--   1. Restores all detailed hour breakdown columns from working version (commit 9718b17e)
--   2. Maintains only 2 tabs (Worked Hours and Payslips) as per original design
--   3. Adds new rowActions for "View Details" and "View GPS Map" buttons
--   4. Keeps date range filter and syncFilters functionality
--
-- Related Files:
--   - sql/modules.sql (lines 111-181)
--   - FrontEnd/js/modules/PayrollTimeClockHandler.js
--   - FrontEnd/Dashboard.php (import map entry)

-- Backup current configuration (optional - for rollback purposes)
-- CREATE TABLE IF NOT EXISTS modules_backup_20251010 AS 
-- SELECT * FROM modules WHERE module_id = '018e3f0a-12d3-7c45-b89a-2f3c4e1d5b6c';

-- Update Payroll module with correct configuration
UPDATE modules
SET config = $json$ {
  "title": "Payroll",
  "header": [
    {
      "type": "filter-date-range",
      "options": {
        "data_column": "start_date",
        "label": "Pay Period"
      }
    },
    { "type": "button", "label": "Generate Pay", "onClick": "generatePayForEmployees" }
  ],
  "body": [
    {
      "type": "tabs",
      "tabs": [
        {
          "label": "Worked Hours",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "time_clock_aggregates",
                "showGlobalSearch": false,
                "addButton": false,
                "filterField": "start_date",
                "listenColumn": "start_date",
                "syncFilters": true,
                "columnsToShow": [
                  "name",
                  "start_date",
                  "end_date",
                  "total_net_hours",
                  "total_overtime_hours",
                  "total_regular_hours",
                  "total_payable_hours",
                  "total_days_worked"
                ],
                "rowClick": "selectRecord",
                "rowActions": [
                  { "label": "View Details", "onClick": "viewTimeClockDetails" },
                  { "label": "View GPS Map", "onClick": "viewTimeClockGpsMap" }
                ]
              }
            }
          ]
        },
        {
          "label": "Payslips",
          "elements": [
            {
              "type": "table",
              "options": {
                "tableName": "generated_payslips",
                "showGlobalSearch": false,
                "addButton": false,
                "filterField": "start_date",
                "listenColumn": "start_date",
                "columnsToShow": ["user_name", "org_name", "gross_pay", "net_pay", "start_date", "end_date"]
              }
            }
          ]
        }
      ]
    }
  ],
  "footer": [
    { "type": "button", "label": "Close", "onClick": "closeModal" }
  ]
} $json$::jsonb
WHERE module_id = '018e3f0a-12d3-7c45-b89a-2f3c4e1d5b6c';

-- Verify the update
SELECT 
  name,
  jsonb_pretty(config->'body'->0->'tabs'->0->'elements'->0->'options'->'columnsToShow') as columns_to_show,
  jsonb_array_length(config->'body'->0->'tabs') as tab_count
FROM modules
WHERE module_id = '018e3f0a-12d3-7c45-b89a-2f3c4e1d5b6c';

-- Expected output:
-- name: "Payroll"
-- columns_to_show: [
--   "name",
--   "start_date", 
--   "end_date",
--   "total_net_hours",
--   "total_overtime_hours",
--   "total_regular_hours",
--   "total_payable_hours",
--   "total_days_worked"
-- ]
-- tab_count: 2
