-- Migration: ShopRenter Scheduled Sync Setup
-- Description: Creates tables and pg_cron jobs for automated background synchronization
-- Date: 2025-01-29

-- ============================================================================
-- STEP 1: Enable Required Extensions
-- ============================================================================

-- Enable pg_cron for scheduled jobs
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Enable pg_net for HTTP requests from database
CREATE EXTENSION IF NOT EXISTS pg_net;

-- ============================================================================
-- STEP 2: Create Sync Logs Table
-- ============================================================================

-- Table to store sync execution logs and statistics
CREATE TABLE IF NOT EXISTS sync_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sync_type TEXT NOT NULL CHECK (sync_type IN ('manual', 'scheduled', 'webhook')),
  platform TEXT NOT NULL CHECK (platform IN ('shopify', 'woocommerce', 'shoprenter')),
  stores_processed INTEGER NOT NULL DEFAULT 0,
  results JSONB, -- Detailed results for each store
  started_at TIMESTAMPTZ NOT NULL,
  completed_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for querying sync logs by platform and date
CREATE INDEX IF NOT EXISTS idx_sync_logs_platform_created
  ON sync_logs(platform, created_at DESC);

-- Index for querying sync logs by type
CREATE INDEX IF NOT EXISTS idx_sync_logs_type_created
  ON sync_logs(sync_type, created_at DESC);

-- ============================================================================
-- STEP 3: Create Sync Configuration Table
-- ============================================================================

-- Table to configure sync schedules per store
CREATE TABLE IF NOT EXISTS store_sync_config (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  store_id UUID NOT NULL REFERENCES stores(id) ON DELETE CASCADE,
  enabled BOOLEAN DEFAULT true,
  sync_frequency TEXT NOT NULL DEFAULT 'hourly' CHECK (
    sync_frequency IN ('15min', '30min', 'hourly', '6hours', 'daily')
  ),
  last_sync_at TIMESTAMPTZ,
  next_sync_at TIMESTAMPTZ,
  sync_products BOOLEAN DEFAULT true,
  sync_orders BOOLEAN DEFAULT true,
  sync_customers BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(store_id)
);

-- Index for querying enabled stores that need syncing
CREATE INDEX IF NOT EXISTS idx_store_sync_config_enabled_next_sync
  ON store_sync_config(enabled, next_sync_at)
  WHERE enabled = true;

-- ============================================================================
-- STEP 4: Create Function to Calculate Next Sync Time
-- ============================================================================

CREATE OR REPLACE FUNCTION calculate_next_sync_time(frequency TEXT)
RETURNS TIMESTAMPTZ AS $$
BEGIN
  RETURN CASE frequency
    WHEN '15min' THEN NOW() + INTERVAL '15 minutes'
    WHEN '30min' THEN NOW() + INTERVAL '30 minutes'
    WHEN 'hourly' THEN NOW() + INTERVAL '1 hour'
    WHEN '6hours' THEN NOW() + INTERVAL '6 hours'
    WHEN 'daily' THEN NOW() + INTERVAL '1 day'
    ELSE NOW() + INTERVAL '1 hour'
  END;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- STEP 5: Create Trigger to Update next_sync_at
-- ============================================================================

CREATE OR REPLACE FUNCTION update_next_sync_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.next_sync_at := calculate_next_sync_time(NEW.sync_frequency);
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_next_sync_at
  BEFORE INSERT OR UPDATE OF sync_frequency, last_sync_at
  ON store_sync_config
  FOR EACH ROW
  EXECUTE FUNCTION update_next_sync_at();

-- ============================================================================
-- STEP 6: Create Function to Call Scheduled Sync Edge Function
-- ============================================================================

CREATE OR REPLACE FUNCTION trigger_shoprenter_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)
  -- Note: In production, use Supabase Vault for secrets
  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 scheduled sync';
    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;

-- ============================================================================
-- STEP 7: Schedule the Sync Job with pg_cron
-- ============================================================================

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

-- Schedule the sync to run every hour
-- Cron format: minute hour day month weekday
-- '0 * * * *' = Every hour at minute 0
SELECT cron.schedule(
  'shoprenter-hourly-sync',           -- Job name
  '0 * * * *',                         -- Every hour
  $$ SELECT trigger_shoprenter_scheduled_sync(); $$
);

-- ============================================================================
-- STEP 8: Create View for Sync Statistics
-- ============================================================================

CREATE OR REPLACE VIEW sync_statistics AS
SELECT
  platform,
  sync_type,
  DATE_TRUNC('day', created_at) as sync_date,
  COUNT(*) as total_syncs,
  SUM(stores_processed) as total_stores_processed,
  AVG(EXTRACT(EPOCH FROM (completed_at - started_at))) as avg_duration_seconds,
  MAX(completed_at) as last_sync_completed
FROM sync_logs
GROUP BY platform, sync_type, DATE_TRUNC('day', created_at)
ORDER BY sync_date DESC, platform, sync_type;

-- ============================================================================
-- STEP 9: Grant Necessary Permissions
-- ============================================================================

-- Grant permissions to authenticated users to view their sync logs
ALTER TABLE sync_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE store_sync_config ENABLE ROW LEVEL SECURITY;

-- Policy: Users can view sync logs for their stores
CREATE POLICY "Users can view their sync logs"
  ON sync_logs FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM stores s
      WHERE s.user_id = auth.uid()
      AND s.platform_name = sync_logs.platform
    )
  );

-- Policy: Users can view and update their store sync config
CREATE POLICY "Users can view their store sync config"
  ON store_sync_config FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM stores s
      WHERE s.id = store_sync_config.store_id
      AND s.user_id = auth.uid()
    )
  );

CREATE POLICY "Users can update their store sync config"
  ON store_sync_config FOR UPDATE
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM stores s
      WHERE s.id = store_sync_config.store_id
      AND s.user_id = auth.uid()
    )
  );

-- ============================================================================
-- STEP 10: Create Helper Functions for Manual Sync Configuration
-- ============================================================================

-- Function to enable/disable sync for a store
CREATE OR REPLACE FUNCTION set_store_sync_enabled(
  p_store_id UUID,
  p_enabled BOOLEAN
)
RETURNS void AS $$
BEGIN
  INSERT INTO store_sync_config (store_id, enabled)
  VALUES (p_store_id, p_enabled)
  ON CONFLICT (store_id)
  DO UPDATE SET
    enabled = p_enabled,
    updated_at = NOW();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to set sync frequency for a store
CREATE OR REPLACE FUNCTION set_store_sync_frequency(
  p_store_id UUID,
  p_frequency TEXT
)
RETURNS void AS $$
BEGIN
  INSERT INTO store_sync_config (store_id, sync_frequency)
  VALUES (p_store_id, p_frequency)
  ON CONFLICT (store_id)
  DO UPDATE SET
    sync_frequency = p_frequency,
    updated_at = NOW();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ============================================================================
-- STEP 11: Insert Default Sync Configuration for Existing Stores
-- ============================================================================

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

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

-- Log migration completion
DO $$
BEGIN
  RAISE NOTICE 'ShopRenter scheduled sync migration completed successfully';
  RAISE NOTICE 'Hourly sync job scheduled: shoprenter-hourly-sync';
  RAISE NOTICE 'Next steps:';
  RAISE NOTICE '1. Set app.internal_sync_secret in Supabase settings';
  RAISE NOTICE '2. Set app.supabase_url in Supabase settings';
  RAISE NOTICE '3. Deploy shoprenter-scheduled-sync Edge Function';
  RAISE NOTICE '4. Monitor sync_logs table for execution results';
END $$;
