-- Package 01B
-- Seed and backfill West Coast Pest based on current dump.

INSERT INTO companies (
    company_name, trading_name, slug, contact_email, timezone, country_code, status
)
SELECT
    'West Coast Pest',
    'West Coast Pest',
    'west-coast-pest',
    'info@westcoastpest.co.za',
    'Africa/Johannesburg',
    'ZA',
    'ACTIVE'
WHERE NOT EXISTS (
    SELECT 1 FROM companies WHERE slug = 'west-coast-pest'
);

SET @company_id := (SELECT id FROM companies WHERE slug = 'west-coast-pest' LIMIT 1);

INSERT INTO company_settings (
    company_id,
    default_radius_meters,
    clock_in_geofence_mode,
    clock_out_geofence_mode,
    track_only_while_clocked_in,
    allow_supervisor_override,
    require_override_reason,
    policy_scope_mode,
    allow_multiple_sessions_per_day,
    require_primary_active_worksite,
    enable_midnight_auto_close,
    auto_close_time,
    auto_close_reason_text
)
SELECT
    @company_id,
    150,
    'SOFT',
    'SOFT',
    1,
    0,
    0,
    'STAFF',
    1,
    1,
    1,
    '23:59:59',
    'Auto-closed at midnight'
WHERE NOT EXISTS (
    SELECT 1 FROM company_settings WHERE company_id = @company_id
);

UPDATE users
SET company_id = @company_id
WHERE company_id IS NULL;

UPDATE users
SET staff_code = employee_code
WHERE (staff_code IS NULL OR staff_code = '');

UPDATE users
SET full_name = TRIM(CONCAT(first_name, ' ', last_name))
WHERE (full_name IS NULL OR full_name = '');

UPDATE worksites SET company_id = @company_id WHERE company_id IS NULL;
UPDATE user_worksites SET company_id = @company_id WHERE company_id IS NULL;
UPDATE work_sessions SET company_id = @company_id WHERE company_id IS NULL;
UPDATE work_session_events SET company_id = @company_id WHERE company_id IS NULL;
UPDATE company_holidays SET company_id = @company_id WHERE company_id IS NULL;
UPDATE payroll_rule_sets SET company_id = @company_id WHERE company_id IS NULL;
UPDATE payroll_rule_set_days SET company_id = @company_id WHERE company_id IS NULL;
UPDATE staff_payroll_rules SET company_id = @company_id WHERE company_id IS NULL;
UPDATE location_logs SET company_id = @company_id WHERE company_id IS NULL;
UPDATE location_stops SET company_id = @company_id WHERE company_id IS NULL;
UPDATE admin_audit_log SET company_id = @company_id WHERE company_id IS NULL;
UPDATE api_sessions s
JOIN users u ON u.id = s.user_id
SET s.company_id = u.company_id
WHERE s.company_id IS NULL;

UPDATE work_sessions
SET auto_closed_flag = 1
WHERE session_status = 'CLOSED'
  AND closed_by_type IN ('auto', 'system');

UPDATE work_sessions
SET needs_review = 1
WHERE session_status = 'OPEN'
   OR clock_in_status = 'OUTSIDE_RADIUS'
   OR clock_out_status = 'OUTSIDE_RADIUS'
   OR auto_closed_flag = 1
   OR (clock_out_time IS NOT NULL AND TIMESTAMPDIFF(MINUTE, clock_in_time, clock_out_time) <= 0);

UPDATE work_sessions
SET worked_minutes = CASE
    WHEN clock_out_time IS NOT NULL THEN GREATEST(TIMESTAMPDIFF(MINUTE, clock_in_time, clock_out_time), 0)
    ELSE 0
END
WHERE worked_minutes = 0;

UPDATE work_sessions ws
LEFT JOIN (
    SELECT work_session_id, COUNT(*) * 60 AS lunch_minutes
    FROM work_session_events
    WHERE event_type = 'WORKING_LUNCH'
    GROUP BY work_session_id
) x ON x.work_session_id = ws.id
SET ws.lunch_minutes = COALESCE(x.lunch_minutes, 0);