-- Migration: WooCommerce Scheduled Sync Setup
-- Description: Adds WooCommerce support to existing scheduled sync infrastructure
-- Date: 2025-10-30
-- Prerequisites: 20250129_shoprenter_scheduled_sync.sql must be applied first

-- ============================================================================
-- STEP 1: Create Function to Call WooCommerce Scheduled Sync Edge Function
-- ============================================================================

CREATE OR REPLACE FUNCTION trigger_woocommerce_scheduled_sync()
RETURNS void AS $$
DECLARE
  response_data jsonb;
  internal_secret TEXT;
  supabase_url TEXT;
BEGIN
  -- Get environment variables (these should be set in Supabase dashboard)
  internal_secret := current_setting('app.internal_sync_secret', true);
  supabase_url := current_setting('app.supabase_url', true);

  IF internal_secret IS NULL OR supabase_url IS NULL THEN
    RAISE WARNING 'Missing required settings for WooCommerce scheduled sync';
    RETURN;
  END IF;

  -- Make HTTP request to the WooCommerce scheduled sync Edge Function
  SELECT INTO response_data
    net.http_post(
      url := supabase_url || '/functions/v1/woocommerce-scheduled-sync',
      headers := jsonb_build_object(
        'Content-Type', 'application/json',
        'x-internal-secret', internal_secret
      ),
      body := jsonb_build_object('source', 'pg_cron')
    );

  -- Log the result
  RAISE NOTICE 'WooCommerce scheduled sync triggered: %', response_data;

EXCEPTION
  WHEN OTHERS THEN
    RAISE WARNING 'Error triggering WooCommerce scheduled sync: %', SQLERRM;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================================
-- STEP 2: Schedule WooCommerce Sync Job with pg_cron
-- ============================================================================

-- Remove existing job if it exists
SELECT cron.unschedule('woocommerce-hourly-sync') WHERE true;

-- Schedule the sync to run every hour (5 minutes after ShopRenter to avoid overlap)
-- Cron format: minute hour day month weekday
-- '5 * * * *' = Every hour at minute 5
SELECT cron.schedule(
  'woocommerce-hourly-sync',           -- Job name
  '5 * * * *',                         -- Every hour at minute 5
  $$ SELECT trigger_woocommerce_scheduled_sync(); $$
);

-- ============================================================================
-- STEP 3: Insert Default Sync Configuration for Existing WooCommerce Stores
-- ============================================================================

-- Create default sync config for all existing WooCommerce stores
INSERT INTO store_sync_config (store_id, enabled, sync_frequency)
SELECT
  id,
  true,
  'hourly'
FROM stores
WHERE platform_name = 'woocommerce'
ON CONFLICT (store_id) DO NOTHING;

-- ============================================================================
-- STEP 4: Create Helper Functions for WooCommerce Sync Status
-- ============================================================================

-- Function to get WooCommerce sync status for a store
CREATE OR REPLACE FUNCTION get_woocommerce_sync_status(p_store_id UUID)
RETURNS TABLE(
  last_sync_at TIMESTAMPTZ,
  sync_status TEXT,
  products_count BIGINT,
  orders_count BIGINT,
  customers_count BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    s.alt_data->>'lastSync' AS last_sync_at,
    COALESCE(s.alt_data->>'syncStatus', 'idle') AS sync_status,
    COALESCE((s.alt_data->>'productsCount')::BIGINT, 0) AS products_count,
    COALESCE((s.alt_data->>'ordersCount')::BIGINT, 0) AS orders_count,
    COALESCE((s.alt_data->>'customersCount')::BIGINT, 0) AS customers_count
  FROM stores s
  WHERE s.id = p_store_id
    AND s.platform_name = 'woocommerce';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

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

-- Log migration completion
DO $$
BEGIN
  RAISE NOTICE 'WooCommerce scheduled sync migration completed successfully';
  RAISE NOTICE 'Hourly sync job scheduled: woocommerce-hourly-sync';
  RAISE NOTICE 'Next steps:';
  RAISE NOTICE '1. Ensure INTERNAL_SYNC_SECRET is set in Edge Functions environment';
  RAISE NOTICE '2. Ensure app.internal_sync_secret is set in Supabase settings';
  RAISE NOTICE '3. Ensure app.supabase_url is set in Supabase settings';
  RAISE NOTICE '4. Deploy woocommerce-scheduled-sync Edge Function';
  RAISE NOTICE '5. Monitor sync_logs table for execution results';
END $$;
