-- ============================================================================
-- TIME CLOCK REPORTS DEMO DATA (2 WEEKS WITH GPS/IP/DEVICE FAILURES)
-- ============================================================================

-- Create comprehensive time clock demo data for 5 employees over 2 weeks 
-- with various GPS, IP, and device failures to show in Payroll reports

-- First, let's get our 5 demo employees
WITH cybex_employees AS (
    SELECT u.user_id, u.full_name, ue.email,
           ROW_NUMBER() OVER (ORDER BY u.user_id) as emp_rank
    FROM users u
    JOIN user_emails ue ON u.user_id = ue.user_id
    JOIN user_org_memberships uom ON u.user_id = uom.user_id
    WHERE uom.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
    AND ue.email IN ('admin@cybexpte.com', 'kevin@cybexpte.com', 'bhaskaran@cybexpte.com', 'mishika@cybexpte.com', 'mahek@cybexpte.com')
    LIMIT 5
),
-- Generate 2 weeks of work days (Monday-Friday)
date_range AS (
    SELECT d::date as work_date, 
           EXTRACT(DOW FROM d) as day_of_week,
           d - CURRENT_DATE as days_from_today
    FROM generate_series(
        CURRENT_DATE - INTERVAL '13 days',  -- 2 weeks ago
        CURRENT_DATE,                       -- today
        INTERVAL '1 day'
    ) d
    WHERE EXTRACT(DOW FROM d) IN (1,2,3,4,5) -- Monday to Friday only
),
-- Create time patterns with various work scenarios and failures
work_schedule AS (
    SELECT ce.user_id, ce.full_name, ce.emp_rank, dr.work_date,
           -- Morning clock in times (8:00-9:00 AM with variations)
           (dr.work_date + TIME '08:00:00' + (RANDOM() * INTERVAL '60 minutes'))::timestamp AS clock_in_time,
           -- Evening clock out times (5:00-6:30 PM with variations)  
           (dr.work_date + TIME '17:00:00' + (RANDOM() * INTERVAL '90 minutes'))::timestamp AS clock_out_time,
           
           -- Work patterns and failure scenarios
           CASE WHEN RANDOM() < 0.15 THEN true ELSE false END as is_remote_work,
           CASE WHEN RANDOM() < 0.10 THEN true ELSE false END as has_gps_failure,
           CASE WHEN RANDOM() < 0.08 THEN true ELSE false END as has_ip_failure,
           CASE WHEN RANDOM() < 0.05 THEN true ELSE false END as has_device_failure,
           CASE WHEN RANDOM() < 0.12 THEN true ELSE false END as is_on_call_duty
    FROM cybex_employees ce
    CROSS JOIN date_range dr
),
-- Generate detailed entries with realistic data
detailed_work_data AS (
    SELECT ws.*,
           -- IP addresses based on location and failure scenarios
           CASE 
               WHEN has_ip_failure THEN '192.168.999.999'  -- Invalid IP for failures
               WHEN is_remote_work THEN 
                   CASE emp_rank
                       WHEN 1 THEN '203.120.45.78'    -- Admin remote
                       WHEN 2 THEN '110.174.89.92'    -- Kevin remote
                       WHEN 3 THEN '158.123.67.45'    -- Bhaskaran remote
                       WHEN 4 THEN '192.168.1.200'    -- Mishika remote
                       WHEN 5 THEN '172.16.10.150'    -- Mahek remote
                   END
               ELSE '192.168.1.' || (100 + emp_rank)::text  -- Office IP range
           END as ip_address,
           
           -- Device information with potential failures
           CASE 
               WHEN has_device_failure THEN 'DEVICE_AUTH_ERROR: Unregistered device detected'
               ELSE 
                   CASE emp_rank
                       WHEN 1 THEN 'iPhone 15 Pro (iOS 17.2)'
                       WHEN 2 THEN 'Samsung Galaxy S24 (Android 14)'
                       WHEN 3 THEN 'iPhone 14 (iOS 17.1)'
                       WHEN 4 THEN 'Google Pixel 8 Pro (Android 14)'
                       WHEN 5 THEN 'iPad Air M2 (iPadOS 17.2)'
                   END
           END as device_info,
           
           -- GPS and location data
           CASE WHEN has_gps_failure THEN false ELSE true END as gps_is_valid,
           CASE 
               WHEN has_gps_failure THEN true
               WHEN is_remote_work AND RANDOM() < 0.1 THEN true
               ELSE false
           END as geofence_violation,
           
           -- Location approval logic
           CASE 
               WHEN has_gps_failure OR has_ip_failure THEN false
               WHEN is_remote_work THEN false  -- Remote needs manual approval
               ELSE true
           END as location_is_approved,
           
           -- Security authentication method
           CASE 
               WHEN has_device_failure THEN 'manual'
               WHEN has_gps_failure THEN 'ip'
               WHEN has_ip_failure THEN 'manual'
               ELSE 'gps'
           END as security_method,
           
           -- Detailed failure descriptions
           CASE 
               WHEN has_gps_failure AND has_ip_failure THEN 'CRITICAL: GPS unavailable + IP validation failed - location cannot be verified'
               WHEN has_gps_failure AND has_device_failure THEN 'CRITICAL: GPS unavailable + device authentication failed - manual verification required'
               WHEN has_ip_failure AND has_device_failure THEN 'CRITICAL: IP validation failed + unregistered device detected - security breach possible'
               WHEN has_gps_failure THEN 'GPS signal unavailable - indoor location or poor satellite reception'
               WHEN has_ip_failure THEN 'IP address not in approved company range - potential security risk'
               WHEN has_device_failure THEN 'Device not registered in company MDM system - unauthorized access attempt'
               WHEN is_remote_work AND RANDOM() < 0.2 THEN 'Remote work location outside approved geofence - requires manager approval'
               ELSE NULL
           END as failure_description
    FROM work_schedule ws
)

-- Insert Clock IN entries for all employees over 2 weeks
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,
    ip_failure,
    device_failure,
    failure_reason,
    isdeleted,
    updatedat
)
SELECT 
    uuid_generate_v7(),
    '0194a8b2-1234-7123-8123-123456789abc'::uuid,
    dwd.user_id,
    'in',
    dwd.gps_is_valid,
    dwd.location_is_approved,
    dwd.security_method,
    dwd.clock_in_time,
    dwd.is_remote_work,
    dwd.ip_address,
    dwd.device_info,
    dwd.geofence_violation,
    dwd.is_on_call_duty,
    dwd.has_ip_failure,
    dwd.has_device_failure,
    dwd.failure_description,
    false,
    dwd.clock_in_time + INTERVAL '1 minute'
FROM detailed_work_data dwd
WHERE dwd.clock_in_time <= NOW()  -- Only past/current entries

UNION ALL

-- Insert Clock OUT entries (only if clock in was successful and it's not today)
SELECT 
    uuid_generate_v7(),
    '0194a8b2-1234-7123-8123-123456789abc'::uuid,
    dwd.user_id,
    'out',
    dwd.gps_is_valid,
    dwd.location_is_approved,
    dwd.security_method,
    dwd.clock_out_time,
    dwd.is_remote_work,
    dwd.ip_address,
    dwd.device_info,
    CASE WHEN RANDOM() < 0.03 THEN true ELSE dwd.geofence_violation END,  -- Small chance of different failure on clock out
    dwd.is_on_call_duty,
    CASE WHEN RANDOM() < 0.02 THEN true ELSE dwd.has_ip_failure END,  
    CASE WHEN RANDOM() < 0.01 THEN true ELSE dwd.has_device_failure END,
    CASE 
        WHEN RANDOM() < 0.02 THEN 'Clock out verification failed - manual override applied by supervisor'
        ELSE dwd.failure_description
    END,
    false,
    dwd.clock_out_time + INTERVAL '2 minutes'
FROM detailed_work_data dwd
WHERE dwd.clock_out_time <= NOW() - INTERVAL '30 minutes'  -- Only if not too recent
AND dwd.work_date < CURRENT_DATE                            -- Don't auto clock out today
AND NOT (dwd.has_gps_failure AND dwd.has_ip_failure AND dwd.has_device_failure)  -- Must have had some success

ON CONFLICT (time_clocks_id) DO NOTHING;

-- Create updated time_clock_aggregates for the 2-week payroll period
WITH payroll_summary AS (
    SELECT 
        tc.user_id,
        u.full_name,
        CURRENT_DATE - INTERVAL '13 days' as period_start,
        CURRENT_DATE as period_end,
        
        -- Count successful work days (had valid clock in/out)
        COUNT(DISTINCT CASE 
            WHEN tc.clock_type = 'in' 
            AND NOT (tc.geofence_failure AND tc.ip_failure AND tc.device_failure) 
            THEN tc.clocked_at::date 
        END) as actual_days_worked,
        
        -- Calculate total minutes worked (8 hours per successful day minus failure recovery time)
        (COUNT(DISTINCT CASE 
            WHEN tc.clock_type = 'in' 
            AND NOT (tc.geofence_failure AND tc.ip_failure AND tc.device_failure) 
            THEN tc.clocked_at::date 
        END) * 480) as total_duration_minutes,
        
        -- Net minutes (total minus time lost due to failures)
        (COUNT(DISTINCT CASE 
            WHEN tc.clock_type = 'in' 
            AND NOT (tc.geofence_failure AND tc.ip_failure AND tc.device_failure) 
            THEN tc.clocked_at::date 
        END) * 480) - 
        (COUNT(CASE WHEN (tc.geofence_failure OR tc.ip_failure OR tc.device_failure) THEN 1 END) * 15) as net_minutes,
        
        -- Regular time (standard working hours)
        (COUNT(DISTINCT CASE 
            WHEN tc.clock_type = 'in' 
            AND NOT (tc.geofence_failure AND tc.ip_failure AND tc.device_failure) 
            THEN tc.clocked_at::date 
        END) * 480) as regular_minutes,
        
        -- Overtime (on-call duty and weekend work)
        (COUNT(CASE WHEN tc.on_call THEN 1 END) * 45) as overtime_minutes
        
    FROM time_clocks tc
    JOIN users u ON tc.user_id = u.user_id
    WHERE tc.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
    AND tc.clocked_at >= CURRENT_DATE - INTERVAL '13 days'
    AND tc.clocked_at <= CURRENT_DATE
    GROUP BY tc.user_id, u.full_name
)
INSERT INTO time_clock_aggregates (
    time_clock_aggregate_id,
    org_id,
    user_id,
    start_date,
    end_date,
    total_duration_minutes,
    total_net_minutes,
    total_regular_minutes,
    total_overtime_minutes,
    total_payable_minutes,
    total_days_worked
)
SELECT 
    uuid_generate_v7(),
    '0194a8b2-1234-7123-8123-123456789abc'::uuid,
    ps.user_id,
    ps.period_start::date,
    ps.period_end::date,
    ps.total_duration_minutes,
    ps.net_minutes,
    ps.regular_minutes,
    ps.overtime_minutes,
    GREATEST(ps.net_minutes, 0), -- Payable minutes (can't be negative)
    ps.actual_days_worked
FROM payroll_summary ps
WHERE ps.actual_days_worked > 0
ON CONFLICT DO NOTHING;

-- Create time_clock_failure_reports table for detailed failure tracking
CREATE TABLE IF NOT EXISTS time_clock_failure_reports (
    report_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    org_id UUID REFERENCES orgs(org_id),
    user_id UUID REFERENCES users(user_id),
    full_name VARCHAR(255),
    report_date DATE NOT NULL,
    failure_type VARCHAR(50) NOT NULL, -- 'gps', 'ip', 'device', 'geofence', 'multiple'
    failure_count INTEGER DEFAULT 1,
    severity VARCHAR(20) DEFAULT 'medium', -- 'low', 'medium', 'high', 'critical'
    time_lost_minutes INTEGER DEFAULT 0,
    resolution_status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'resolved', 'escalated'
    details JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Insert detailed failure reports for the past 2 weeks
INSERT INTO time_clock_failure_reports (
    org_id, user_id, full_name, report_date, failure_type, failure_count, 
    severity, time_lost_minutes, resolution_status, details
)
SELECT 
    '0194a8b2-1234-7123-8123-123456789abc'::uuid,
    tc.user_id,
    u.full_name,
    tc.clocked_at::date,
    
    CASE 
        WHEN (tc.geofence_failure AND tc.ip_failure) OR 
             (tc.geofence_failure AND tc.device_failure) OR 
             (tc.ip_failure AND tc.device_failure) THEN 'multiple'
        WHEN tc.geofence_failure THEN 'geofence'
        WHEN tc.ip_failure THEN 'ip'  
        WHEN tc.device_failure THEN 'device'
        WHEN NOT tc.valid_gps THEN 'gps'
        ELSE 'other'
    END as failure_type,
    
    COUNT(*) as failure_count,
    
    CASE 
        WHEN COUNT(*) >= 4 THEN 'critical'
        WHEN COUNT(*) >= 3 THEN 'high'  
        WHEN COUNT(*) >= 2 THEN 'medium'
        ELSE 'low'
    END as severity,
    
    -- Estimate time lost due to failures (15 mins per failure)
    COUNT(*) * 15 as time_lost_minutes,
    
    CASE 
        WHEN RANDOM() < 0.6 THEN 'resolved'
        WHEN RANDOM() < 0.8 THEN 'pending'
        ELSE 'escalated'
    END as resolution_status,
    
    jsonb_build_object(
        'employee_name', u.full_name,
        'failure_locations', array_agg(DISTINCT tc.ip) FILTER (WHERE tc.ip IS NOT NULL),
        'devices_affected', array_agg(DISTINCT tc.device) FILTER (WHERE tc.device IS NOT NULL),
        'failure_details', array_agg(DISTINCT tc.failure_reason) FILTER (WHERE tc.failure_reason IS NOT NULL),
        'remote_work_incidents', COUNT(*) FILTER (WHERE tc.remote_status = true),
        'on_call_incidents', COUNT(*) FILTER (WHERE tc.on_call = true),
        'total_incidents', COUNT(*)
    ) as details
    
FROM time_clocks tc
JOIN users u ON tc.user_id = u.user_id
WHERE tc.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
AND tc.clocked_at >= CURRENT_DATE - INTERVAL '14 days'
AND (tc.geofence_failure OR tc.ip_failure OR tc.device_failure OR NOT tc.valid_gps)
GROUP BY tc.user_id, tc.clocked_at::date, u.full_name
HAVING COUNT(*) > 0
ON CONFLICT DO NOTHING;

-- Summary report: Show failure statistics for management
SELECT 
    'TIME CLOCK FAILURE SUMMARY - LAST 2 WEEKS' as report_title,
    COUNT(DISTINCT user_id) as employees_with_failures,
    COUNT(*) as total_failure_incidents,
    SUM(failure_count) as total_failures,
    SUM(time_lost_minutes) as total_time_lost_minutes,
    ROUND(SUM(time_lost_minutes) / 60.0, 1) as total_time_lost_hours,
    COUNT(*) FILTER (WHERE severity = 'critical') as critical_incidents,
    COUNT(*) FILTER (WHERE severity = 'high') as high_severity_incidents,
    COUNT(*) FILTER (WHERE resolution_status = 'pending') as unresolved_incidents
FROM time_clock_failure_reports
WHERE report_date >= CURRENT_DATE - INTERVAL '14 days';