-- Migration: Add Time Clock Aggregation Sync Hook
-- Date: 2025-10-08
-- Description: Creates sync hook for automatic time clock aggregation with org_id support

-- Insert time clock aggregation hook
-- This hook automatically calculates time clock aggregates when time_clock_aggregates are requested
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',
    'time_clock_aggregates',
    ARRAY['PULL'],
    'aggregate',
    100,
    '{
        "source_table": "time_clocks",
        "target_table": "time_clock_aggregates",
        "group_by": ["user_id", "org_id"],
        "date_field": "clocked_at",
        "date_range_source": "filter",
        "require_date_filter": true,
        "primary_key": "time_clock_aggregate_id",
        "upsert_keys": ["user_id", "org_id", "start_date", "end_date"],
        "conditions": {
            "isdeleted": false
        },
        "aggregations": {
            "start_date": "FROM_FILTER",
            "end_date": "FROM_FILTER",
            "total_net_hours": "0.0",
            "total_overtime_hours": "0.0",
            "total_regular_hours": "0.0",
            "total_payable_hours": "0.0",
            "total_days_worked": "COUNT(DISTINCT clocked_at::date)"
        }
    }'::jsonb,
    true
)
ON CONFLICT (name) DO UPDATE
SET 
    config = jsonb_set(
        sync_hooks.config,
        '{group_by}',
        '["user_id", "org_id"]'::jsonb
    ),
    description = EXCLUDED.description,
    updated_at = NOW();

COMMENT ON TABLE sync_hooks IS 'Declarative sync hooks - time_clock_aggregation groups by user_id AND org_id for multi-tenant support';
