-- Migration: Fix Auto Sync Times to Next Round Hour
-- Description: Updates calculate_next_sync_time to always round up to next full hour
-- Date: 2025-11-20
-- Related Issue: #103

-- ============================================================================
-- STEP 1: Update calculate_next_sync_time Function to Round to Next Hour
-- ============================================================================

CREATE OR REPLACE FUNCTION calculate_next_sync_time(frequency TEXT)
RETURNS TIMESTAMPTZ AS $$
DECLARE
  base_time TIMESTAMPTZ;
  next_time TIMESTAMPTZ;
BEGIN
  -- Start from the next round hour
  base_time := DATE_TRUNC('hour', NOW()) + INTERVAL '1 hour';

  -- Apply frequency on top of the next round hour
  next_time := CASE frequency
    WHEN '15min' THEN
      -- For 15min frequency, find next 15-minute mark (:00, :15, :30, :45)
      DATE_TRUNC('hour', NOW()) +
      (INTERVAL '15 minutes' * CEIL(EXTRACT(MINUTE FROM NOW()) / 15.0))
    WHEN '30min' THEN
      -- For 30min frequency, find next 30-minute mark (:00, :30)
      DATE_TRUNC('hour', NOW()) +
      (INTERVAL '30 minutes' * CEIL(EXTRACT(MINUTE FROM NOW()) / 30.0))
    WHEN 'hourly' THEN base_time
    WHEN '6hours' THEN
      -- Round up to next 6-hour mark (00:00, 06:00, 12:00, 18:00)
      DATE_TRUNC('hour', NOW()) +
      (INTERVAL '6 hours' * CEIL(EXTRACT(HOUR FROM NOW()) / 6.0))
    WHEN 'daily' THEN
      -- Round up to next midnight
      DATE_TRUNC('day', NOW()) + INTERVAL '1 day'
    ELSE base_time
  END;

  -- Ensure we never return a time in the past
  IF next_time <= NOW() THEN
    next_time := next_time + CASE frequency
      WHEN '15min' THEN INTERVAL '15 minutes'
      WHEN '30min' THEN INTERVAL '30 minutes'
      WHEN 'hourly' THEN INTERVAL '1 hour'
      WHEN '6hours' THEN INTERVAL '6 hours'
      WHEN 'daily' THEN INTERVAL '1 day'
      ELSE INTERVAL '1 hour'
    END;
  END IF;

  RETURN next_time;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- STEP 2: Update All Existing Stores to Sync at Next Round Hour
-- ============================================================================

-- Update all existing store sync configs to use the new rounded times
UPDATE store_sync_config
SET
  next_sync_at = calculate_next_sync_time(sync_frequency),
  updated_at = NOW()
WHERE enabled = true;

-- ============================================================================
-- Migration Complete
-- ============================================================================

DO $$
DECLARE
  updated_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO updated_count
  FROM store_sync_config
  WHERE enabled = true;

  RAISE NOTICE 'Auto sync times migration completed successfully';
  RAISE NOTICE 'Updated % active store sync configurations', updated_count;
  RAISE NOTICE 'All stores now scheduled to sync at round hour marks';
  RAISE NOTICE 'Example schedule times:';
  RAISE NOTICE '  - hourly: :00 (e.g., 11:00, 12:00, 13:00)';
  RAISE NOTICE '  - 30min: :00 or :30 (e.g., 11:00, 11:30, 12:00)';
  RAISE NOTICE '  - 15min: :00, :15, :30, :45 (e.g., 11:00, 11:15, 11:30)';
  RAISE NOTICE '  - 6hours: 00:00, 06:00, 12:00, 18:00';
  RAISE NOTICE '  - daily: 00:00 next day';
END $$;
