-- Migration: Restore Scheduled Sync Configuration
-- Date: 2025-11-25
-- Purpose: Restore database configuration settings for pg_cron scheduled sync

-- =============================================================================
-- CRITICAL: Set these environment variables in Supabase Edge Functions:
--   INTERNAL_SYNC_SECRET = your_secret_key_here
-- =============================================================================

-- ============================================================================
-- Option 1: Using SET for session-level (not persistent, testing only)
-- ============================================================================
-- Uncomment for testing:
-- SET app.internal_sync_secret = 'your_secret_here';
-- SET app.supabase_url = 'https://ztklqodcdjeqpsvhlpud.supabase.co';

-- ============================================================================
-- Option 2: Using a configuration table (WORKAROUND)
-- ============================================================================
-- Since ALTER DATABASE requires superuser, we use a config table instead

CREATE TABLE IF NOT EXISTS system_config (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE system_config ENABLE ROW LEVEL SECURITY;

-- Only allow service role to modify
CREATE POLICY "Service role can manage system config"
  ON system_config
  FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

-- Insert/update configuration values
INSERT INTO system_config (key, value, description)
VALUES
  ('internal_sync_secret', 'REPLACE_WITH_YOUR_SECRET', 'Secret key for scheduled sync authentication'),
  ('supabase_url', 'https://ztklqodcdjeqpsvhlpud.supabase.co', 'Supabase project URL')
ON CONFLICT (key)
DO UPDATE SET
  value = EXCLUDED.value,
  updated_at = NOW();

-- ============================================================================
-- Update trigger function to read from system_config table
-- ============================================================================

CREATE OR REPLACE FUNCTION trigger_shoprenter_scheduled_sync()
RETURNS void AS $$
DECLARE
  response_data jsonb;
  internal_secret TEXT;
  supabase_url TEXT;
BEGIN
  -- Try to get from PostgreSQL settings first (if they exist)
  internal_secret := current_setting('app.internal_sync_secret', true);
  supabase_url := current_setting('app.supabase_url', true);

  -- If not set, fallback to system_config table
  IF internal_secret IS NULL OR supabase_url IS NULL THEN
    SELECT value INTO internal_secret
    FROM system_config
    WHERE key = 'internal_sync_secret';

    SELECT value INTO supabase_url
    FROM system_config
    WHERE key = 'supabase_url';
  END IF;

  IF internal_secret IS NULL OR supabase_url IS NULL THEN
    RAISE WARNING 'Missing required settings for scheduled sync (checked both current_setting and system_config)';
    RETURN;
  END IF;

  -- Make HTTP request to the scheduled sync Edge Function
  SELECT INTO response_data
    net.http_post(
      url := supabase_url || '/functions/v1/shoprenter-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 'Scheduled sync triggered: %', response_data;

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

-- ============================================================================
-- Apply same fix to WooCommerce trigger
-- ============================================================================

CREATE OR REPLACE FUNCTION trigger_woocommerce_scheduled_sync()
RETURNS void AS $$
DECLARE
  response_data jsonb;
  internal_secret TEXT;
  supabase_url TEXT;
BEGIN
  -- Try to get from PostgreSQL settings first
  internal_secret := current_setting('app.internal_sync_secret', true);
  supabase_url := current_setting('app.supabase_url', true);

  -- Fallback to system_config table
  IF internal_secret IS NULL OR supabase_url IS NULL THEN
    SELECT value INTO internal_secret
    FROM system_config
    WHERE key = 'internal_sync_secret';

    SELECT value INTO supabase_url
    FROM system_config
    WHERE key = 'supabase_url';
  END IF;

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

  -- Make HTTP request
  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')
    );

  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;

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

DO $$
BEGIN
  RAISE NOTICE '✅ Sync configuration restored using system_config table';
  RAISE NOTICE '⚠️  IMPORTANT: Update system_config.internal_sync_secret with your actual secret';
  RAISE NOTICE '💡 Run: UPDATE system_config SET value = ''your_secret'' WHERE key = ''internal_sync_secret'';';
END $$;
