-- ============================================================================
-- TIME CLOCKS DEMO DATA WITH DEVICE AND IP INFORMATION
-- Purpose: Populate time_clocks table with realistic device and IP data
-- ============================================================================

-- Create realistic time clock entries with device and IP information
-- This demonstrates various scenarios: office, remote work, field work, etc.

WITH admin_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 = 'admin@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
) 
SELECT 
  uuid_generate_v7(),
  org_id,
  user_id,
  clock_type,
  valid_gps,
  locationapproved,
  'gps',
  clocked_at,
  remote_status,
  ip,
  device,
  geofence_failure
FROM admin_user_ref
CROSS JOIN (
  VALUES 
    -- Current Week: Various work scenarios
    
    -- Monday (Office - Corporate Network)
    ('in'::text, (CURRENT_DATE - INTERVAL '4 days' + TIME '08:15:00')::timestamp, true, true, false, '192.168.1.105', 'iPhone 14 Pro (iOS 17.1.1)', false),
    ('out'::text, (CURRENT_DATE - INTERVAL '4 days' + TIME '17:30:00')::timestamp, true, true, false, '192.168.1.105', 'iPhone 14 Pro (iOS 17.1.1)', false),
    
    -- Tuesday (Remote Work - Home Internet)
    ('in'::text, (CURRENT_DATE - INTERVAL '3 days' + TIME '08:45:00')::timestamp, true, false, true, '203.120.45.78', 'Samsung Galaxy S23 (Android 14)', true),
    ('out'::text, (CURRENT_DATE - INTERVAL '3 days' + TIME '17:15:00')::timestamp, true, false, true, '203.120.45.78', 'Samsung Galaxy S23 (Android 14)', true),
    
    -- Wednesday (Office - Using Tablet)
    ('in'::text, (CURRENT_DATE - INTERVAL '2 days' + TIME '08:20:00')::timestamp, true, true, false, '192.168.1.112', 'iPad Air (iPadOS 17.1)', false),
    ('out'::text, (CURRENT_DATE - INTERVAL '2 days' + TIME '18:00:00')::timestamp, true, true, false, '192.168.1.112', 'iPad Air (iPadOS 17.1)', false),
    
    -- Thursday (Field Work - Mobile Network)
    ('in'::text, (CURRENT_DATE - INTERVAL '1 day' + TIME '07:30:00')::timestamp, true, false, false, '172.16.20.143', 'Google Pixel 8 (Android 14)', false),
    ('out'::text, (CURRENT_DATE - INTERVAL '1 day' + TIME '16:45:00')::timestamp, true, false, false, '172.16.20.143', 'Google Pixel 8 (Android 14)', false),
    
    -- Friday (Current Day - Remote Work, Geofence Issue)
    ('in'::text, (CURRENT_DATE + TIME '08:30:00')::timestamp, true, false, true, '110.174.89.92', 'iPhone 15 (iOS 17.2)', true)
    
) AS clock_data(clock_type, clocked_at, valid_gps, locationapproved, remote_status, ip, device, geofence_failure)
ON CONFLICT DO NOTHING;

-- ============================================================================
-- ADDITIONAL DEMO DATA - Multiple Employees with Different Devices
-- ============================================================================

-- Add entries for other users if they exist
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
) 
SELECT 
  uuid_generate_v7(),
  '0194a8b2-1234-7123-8123-123456789abc'::uuid, -- Default org_id
  u.user_id,
  clock_type,
  valid_gps,
  locationapproved,
  'gps',
  clocked_at,
  remote_status,
  ip,
  device,
  geofence_failure
FROM users u
CROSS JOIN (
  VALUES 
    -- Today's entries for different employees
    
    -- Employee using Android phone from office
    ('in'::text, (CURRENT_DATE + TIME '08:00:00')::timestamp, true, true, false, '192.168.1.156', 'OnePlus 11 (Android 13)', false),
    
    -- Employee using iPhone from home
    ('in'::text, (CURRENT_DATE + TIME '08:15:00')::timestamp, true, false, true, '58.176.234.91', 'iPhone 13 (iOS 16.7)', true),
    
    -- Employee using tablet from coffee shop
    ('in'::text, (CURRENT_DATE + TIME '09:00:00')::timestamp, false, false, true, '203.87.156.42', 'Samsung Galaxy Tab S8 (Android 12)', true),
    
    -- Employee using laptop browser
    ('in'::text, (CURRENT_DATE + TIME '08:45:00')::timestamp, true, true, false, '192.168.1.201', 'Windows 11 - Chrome Browser', false)
    
) AS clock_data(clock_type, clocked_at, valid_gps, locationapproved, remote_status, ip, device, geofence_failure)
WHERE u.isDeleted = false 
AND u.user_id != (SELECT user_id FROM user_emails WHERE email = 'admin@cybexpte.com' LIMIT 1)
LIMIT 4 -- Limit to first 4 non-admin users to avoid too much data
ON CONFLICT DO NOTHING;

-- ============================================================================
-- DEMO DATA SCENARIOS EXPLANATION
-- ============================================================================

/*
IP Address Scenarios:
- 192.168.1.x = Corporate office network (internal IPs)
- 203.120.45.78 = Residential broadband (remote work)
- 110.174.89.92 = Home internet (different provider)
- 172.16.20.143 = Field work location (client network)
- 58.176.234.91 = Home internet (another provider)
- 203.87.156.42 = Public WiFi (coffee shop, etc.)

Device Scenarios:
- iPhone models with iOS versions
- Android phones with version info
- Tablets (iPad, Samsung Galaxy Tab)
- Desktop/laptop browser identification

Remote Status:
- false = On-site/office work
- true = Remote work (home, field, etc.)

Geofence Failure:
- false = Within approved location boundaries
- true = Outside geofence boundaries (may need approval)

Location Approved:
- true = HR/manager approved the location
- false = Pending approval or flagged location
*/