-- Migration: Add Time Clock Aggregation Sync Hook
-- Date: 2025-10-08
-- Description: Creates sync hook for automatic time clock aggregation with org_id support
-- Updated: 2025-11-12 - Fixed to use custom controller method instead of declarative SQL

-- Update the time clock aggregation hook to use custom controller method
INSERT INTO sync_hooks (name, description, trigger_table, trigger_operations, hook_type, priority, config, is_active)
VALUES (
    'time_clock_aggregation',
    'Auto-aggregate time_clocks data into time_clock_aggregates by user and org using TimeClockAggregatesController',
    'time_clock_aggregates',
    ARRAY['PULL'],
    'custom',
    100,
    '{
        "controller": "App\\Controllers\\TimeClockAggregatesController",
        "method": "calculate",
        "require_date_filter": true,
        "date_range_source": "filter"
    }'::jsonb,
    true
)
ON CONFLICT (name) DO UPDATE
SET 
    hook_type = 'custom',
    config = '{
        "controller": "App\\Controllers\\TimeClockAggregatesController",
        "method": "calculate",
        "require_date_filter": true,
        "date_range_source": "filter"
    }'::jsonb,
    description = 'Auto-aggregate time_clocks data into time_clock_aggregates by user and org using TimeClockAggregatesController',
    updated_at = NOW();

COMMENT ON TABLE sync_hooks IS 'Declarative sync hooks - time_clock_aggregation uses custom controller method for complex clock pairing logic';
