-- ============================================================================
-- TIME CLOCK DEMO DATA FOR EMPLOYEES
-- ============================================================================
-- Create time clock entries (ClockIn/ClockOut) for demo employees
-- Simulating a work week with morning clock-in and evening clock-out

-- Kevin Chand (Employee ID: 1) - Regular office worker using biometric device
WITH kevin_user_ref AS (
  SELECT ue.user_id, uom.org_id
  FROM user_emails ue
  JOIN user_org_memberships uom ON ue.user_id = uom.user_id
  WHERE ue.email = 'kevin@cybexpte.com'
  AND uom.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
  LIMIT 1
)
INSERT INTO time_clocks (
  time_clocks_id,
  org_id,
  user_id, 
  clock_type, 
  valid_gps, 
  locationapproved, 
  security_type, 
  clocked_at,
  remote_status,
  ip,
  device,
  geofence_failure,
  on_call
) 
SELECT 
  uuid_generate_v7(),
  org_id,
  user_id,
  clock_type,
  valid_gps,
  locationapproved,
  'biometric',
  clocked_at,
  remote_status,
  ip,
  device,
  geofence_failure,
  on_call
FROM kevin_user_ref
CROSS JOIN (
  VALUES 
    -- 5 days ago - Office biometric
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '5 days' + TIME '08:15:00')::timestamp, '192.168.1.105', 'F500 Biometric EB202509065', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '5 days' + TIME '17:05:00')::timestamp, '192.168.1.105', 'F500 Biometric EB202509065', false, false),
    
    -- 4 days ago - Office biometric
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '4 days' + TIME '08:20:00')::timestamp, '192.168.1.105', 'F500 Biometric EB202509065', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '4 days' + TIME '17:10:00')::timestamp, '192.168.1.105', 'F500 Biometric EB202509065', false, false),
    
    -- 3 days ago - Office biometric
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '3 days' + TIME '08:18:00')::timestamp, '192.168.1.105', 'F500 Biometric EB202509065', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '3 days' + TIME '17:15:00')::timestamp, '192.168.1.105', 'F500 Biometric EB202509065', false, false),
    
    -- 2 days ago - Office biometric
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '2 days' + TIME '08:25:00')::timestamp, '192.168.1.105', 'F500 Biometric EB202509065', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '2 days' + TIME '17:20:00')::timestamp, '192.168.1.105', 'F500 Biometric EB202509065', false, false),
    
    -- 1 day ago - Office biometric
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '1 day' + TIME '08:12:00')::timestamp, '192.168.1.105', 'F500 Biometric EB202509065', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '1 day' + TIME '17:08:00')::timestamp, '192.168.1.105', 'F500 Biometric EB202509065', false, false),
    
    -- Today - Only clocked in
    ('in'::text, true, true, false, (CURRENT_DATE + TIME '08:22:00')::timestamp, '192.168.1.105', 'F500 Biometric EB202509065', false, false)
) AS clock_data(clock_type, valid_gps, locationapproved, remote_status, clocked_at, ip, device, geofence_failure, on_call)
ON CONFLICT DO NOTHING;

-- David Thompson (Employee ID: 2) - Mix of office and remote work
WITH david_user_ref AS (
  SELECT ue.user_id, uom.org_id
  FROM user_emails ue
  JOIN user_org_memberships uom ON ue.user_id = uom.user_id
  WHERE ue.email = 'david.thompson@cybexpte.com'
  AND uom.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
  LIMIT 1
)
INSERT INTO time_clocks (
  time_clocks_id,
  org_id,
  user_id, 
  clock_type, 
  valid_gps, 
  locationapproved, 
  security_type, 
  clocked_at,
  remote_status,
  ip,
  device,
  geofence_failure,
  on_call
) 
SELECT 
  uuid_generate_v7(),
  org_id,
  user_id,
  clock_type,
  valid_gps,
  locationapproved,
  'gps',
  clocked_at,
  remote_status,
  ip,
  device,
  geofence_failure,
  on_call
FROM david_user_ref
CROSS JOIN (
  VALUES 
    -- 5 days ago - Remote work
    ('in'::text, true, false, true, (CURRENT_DATE - INTERVAL '5 days' + TIME '09:00:00')::timestamp, '203.120.45.78', 'iPhone 13', false, false),
    ('out'::text, true, false, true, (CURRENT_DATE - INTERVAL '5 days' + TIME '17:30:00')::timestamp, '203.120.45.78', 'iPhone 13', false, false),
    
    -- 4 days ago - Office biometric
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '4 days' + TIME '08:30:00')::timestamp, '192.168.1.112', 'F500 Biometric EB202509065', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '4 days' + TIME '17:00:00')::timestamp, '192.168.1.112', 'F500 Biometric EB202509065', false, false),
    
    -- 3 days ago - Remote work
    ('in'::text, true, false, true, (CURRENT_DATE - INTERVAL '3 days' + TIME '09:15:00')::timestamp, '203.120.45.78', 'iPhone 13', false, false),
    ('out'::text, true, false, true, (CURRENT_DATE - INTERVAL '3 days' + TIME '17:45:00')::timestamp, '203.120.45.78', 'iPhone 13', false, false),
    
    -- 2 days ago - Office biometric
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '2 days' + TIME '08:35:00')::timestamp, '192.168.1.112', 'F500 Biometric EB202509065', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '2 days' + TIME '17:10:00')::timestamp, '192.168.1.112', 'F500 Biometric EB202509065', false, false),
    
    -- 1 day ago - Remote work
    ('in'::text, true, false, true, (CURRENT_DATE - INTERVAL '1 day' + TIME '09:10:00')::timestamp, '203.120.45.78', 'iPhone 13', false, false),
    ('out'::text, true, false, true, (CURRENT_DATE - INTERVAL '1 day' + TIME '17:40:00')::timestamp, '203.120.45.78', 'iPhone 13', false, false),
    
    -- Today - Office biometric
    ('in'::text, true, true, false, (CURRENT_DATE + TIME '08:28:00')::timestamp, '192.168.1.112', 'F500 Biometric EB202509065', false, false)
) AS clock_data(clock_type, valid_gps, locationapproved, remote_status, clocked_at, ip, device, geofence_failure, on_call)
ON CONFLICT DO NOTHING;

-- Lisa Anderson (Employee ID: 3) - Field worker with on-call duties
WITH lisa_user_ref AS (
  SELECT ue.user_id, uom.org_id
  FROM user_emails ue
  JOIN user_org_memberships uom ON ue.user_id = uom.user_id
  WHERE ue.email = 'lisa.anderson@cybexpte.com'
  AND uom.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
  LIMIT 1
)
INSERT INTO time_clocks (
  time_clocks_id,
  org_id,
  user_id, 
  clock_type, 
  valid_gps, 
  locationapproved, 
  security_type, 
  clocked_at,
  remote_status,
  ip,
  device,
  geofence_failure,
  on_call
) 
SELECT 
  uuid_generate_v7(),
  org_id,
  user_id,
  clock_type,
  valid_gps,
  locationapproved,
  'gps',
  clocked_at,
  remote_status,
  ip,
  device,
  geofence_failure,
  on_call
FROM lisa_user_ref
CROSS JOIN (
  VALUES 
    -- 5 days ago - Field location
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '5 days' + TIME '07:45:00')::timestamp, '172.16.20.143', 'Samsung Galaxy S23', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '5 days' + TIME '16:30:00')::timestamp, '172.16.20.143', 'Samsung Galaxy S23', false, false),
    
    -- 4 days ago - Field location with on-call
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '4 days' + TIME '07:50:00')::timestamp, '172.16.20.143', 'Samsung Galaxy S23', false, true),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '4 days' + TIME '16:45:00')::timestamp, '172.16.20.143', 'Samsung Galaxy S23', false, true),
    
    -- 3 days ago - Office location
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '3 days' + TIME '08:30:00')::timestamp, '192.168.1.120', 'Samsung Galaxy S23', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '3 days' + TIME '17:00:00')::timestamp, '192.168.1.120', 'Samsung Galaxy S23', false, false),
    
    -- 2 days ago - Field location with on-call
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '2 days' + TIME '07:40:00')::timestamp, '172.16.20.143', 'Samsung Galaxy S23', false, true),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '2 days' + TIME '16:35:00')::timestamp, '172.16.20.143', 'Samsung Galaxy S23', false, true),
    
    -- 1 day ago - Field location
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '1 day' + TIME '07:55:00')::timestamp, '172.16.20.143', 'Samsung Galaxy S23', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '1 day' + TIME '16:40:00')::timestamp, '172.16.20.143', 'Samsung Galaxy S23', false, false),
    
    -- Today - Field location with on-call
    ('in'::text, true, true, false, (CURRENT_DATE + TIME '07:48:00')::timestamp, '172.16.20.143', 'Samsung Galaxy S23', false, true)
) AS clock_data(clock_type, valid_gps, locationapproved, remote_status, clocked_at, ip, device, geofence_failure, on_call)
ON CONFLICT DO NOTHING;

-- Michael Chen (Employee ID: 4 - HR) - Regular office hours with biometric
WITH michael_user_ref AS (
  SELECT ue.user_id, uom.org_id
  FROM user_emails ue
  JOIN user_org_memberships uom ON ue.user_id = uom.user_id
  WHERE ue.email = 'michael.chen@cybexpte.com'
  AND uom.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
  LIMIT 1
)
INSERT INTO time_clocks (
  time_clocks_id,
  org_id,
  user_id, 
  clock_type, 
  valid_gps, 
  locationapproved, 
  security_type, 
  clocked_at,
  remote_status,
  ip,
  device,
  geofence_failure,
  on_call
) 
SELECT 
  uuid_generate_v7(),
  org_id,
  user_id,
  clock_type,
  valid_gps,
  locationapproved,
  'biometric',
  clocked_at,
  remote_status,
  ip,
  device,
  geofence_failure,
  on_call
FROM michael_user_ref
CROSS JOIN (
  VALUES 
    -- 5 days ago - Office biometric
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '5 days' + TIME '08:00:00')::timestamp, '192.168.1.130', 'F500 Biometric EB202509065', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '5 days' + TIME '17:00:00')::timestamp, '192.168.1.130', 'F500 Biometric EB202509065', false, false),
    
    -- 4 days ago - Office biometric
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '4 days' + TIME '08:05:00')::timestamp, '192.168.1.130', 'F500 Biometric EB202509065', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '4 days' + TIME '17:05:00')::timestamp, '192.168.1.130', 'F500 Biometric EB202509065', false, false),
    
    -- 3 days ago - Office biometric
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '3 days' + TIME '08:10:00')::timestamp, '192.168.1.130', 'F500 Biometric EB202509065', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '3 days' + TIME '17:10:00')::timestamp, '192.168.1.130', 'F500 Biometric EB202509065', false, false),
    
    -- 2 days ago - Office biometric
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '2 days' + TIME '08:02:00')::timestamp, '192.168.1.130', 'F500 Biometric EB202509065', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '2 days' + TIME '17:02:00')::timestamp, '192.168.1.130', 'F500 Biometric EB202509065', false, false),
    
    -- 1 day ago - Office biometric
    ('in'::text, true, true, false, (CURRENT_DATE - INTERVAL '1 day' + TIME '08:08:00')::timestamp, '192.168.1.130', 'F500 Biometric EB202509065', false, false),
    ('out'::text, true, true, false, (CURRENT_DATE - INTERVAL '1 day' + TIME '17:08:00')::timestamp, '192.168.1.130', 'F500 Biometric EB202509065', false, false),
    
    -- Today - Office biometric
    ('in'::text, true, true, false, (CURRENT_DATE + TIME '08:05:00')::timestamp, '192.168.1.130', 'F500 Biometric EB202509065', false, false)
) AS clock_data(clock_type, valid_gps, locationapproved, remote_status, clocked_at, ip, device, geofence_failure, on_call)
ON CONFLICT DO NOTHING;

-- ============================================================================
-- END TIME CLOCK DEMO DATA
-- ============================================================================

-- Payroll modal
DELETE FROM modules WHERE module_id = '018e3f0a-12d3-7c45-b89a-2f3c4e1d5b6c'::uuid;
INSERT INTO modules (module_id, name, config, org_id, branch_id, icon, aria_label, description) VALUES (
  '018e3f0a-12d3-7c45-b89a-2f3c4e1d5b6c'::uuid, 'Payroll',
  $json$ {
    "title": "Payroll",
    "header": [
      {
        "type": "filter-date-range",
        "options": {
          "data_column": "start_date",
          "label": "Pay Period"
        }
      },
      { "type": "button", "label": "Generate Pay", "onClick": "generatePayForEmployees" },
      { "type": "button", "label": "Export Excel", "onClick": "exportTimeClockExcel" },
      { "type": "button", "label": "Export", "onClick": "showExportDialog" }
    ],
    "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": "Daily Details", "onClick": "viewDailyDetails" },
                    { "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,
  NULL,
  NULL,
  'fa-money-check-dollar',
  'Payroll',
  'Manage payroll and timesheets'
) ON CONFLICT (module_id) DO UPDATE 
  SET config = EXCLUDED.config;