import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'
import { wrapHandler } from '../_shared/error-handler.ts'

const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
  'Access-Control-Allow-Methods': 'GET, POST, PUT, DELETE, OPTIONS',
}

// Handle OPTIONS requests BEFORE wrapping with error handler to avoid timeouts
serve(async (req) => {
  // Fast-path for OPTIONS requests
  if (req.method === 'OPTIONS') {
    return new Response('ok', { headers: corsHeaders })
  }

  // Wrap all other requests with error handler
  return wrapHandler('api', async (req) => {

  try {
    const url = new URL(req.url)
    const path = url.pathname.replace('/api/', '')

    // Get user from authorization header
    const authHeader = req.headers.get('authorization')
    if (!authHeader) {
      return new Response(
        JSON.stringify({ error: 'No authorization header' }),
        { status: 401, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
      )
    }

    const token = authHeader.replace('Bearer ', '')

    // Create Supabase client with the user's token for proper RLS
    const supabaseUrl = Deno.env.get('SUPABASE_URL')!
    const supabaseKey = Deno.env.get('SUPABASE_ANON_KEY')!
    const supabase = createClient(supabaseUrl, supabaseKey, {
      global: {
        headers: {
          Authorization: authHeader
        }
      }
    })

    // Verify the token and get user
    const { data: { user }, error: userError } = await supabase.auth.getUser(token)

    if (userError || !user) {
      return new Response(
        JSON.stringify({ error: 'Invalid token' }),
        { status: 401, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
      )
    }

    // GET /api/stores - List all stores for the user
    if (path === 'stores' && req.method === 'GET') {
      // Always include sync config since Web UI needs last_sync_at
      let query = supabase
        .from('stores')
        .select(`
          *,
          phone_numbers!stores_phone_number_id_fkey (
            id,
            phone_number,
            country_code,
            country_name,
            location,
            phone_type,
            price
          ),
          store_sync_config (
            enabled,
            sync_frequency,
            products_access_policy,
            customers_access_policy,
            orders_access_policy,
            last_sync_at,
            next_sync_at
          )
        `)
        .eq('user_id', user.id)
        .order('created_at', { ascending: false })

      const { data: stores, error } = await query

      if (error) {
        console.error('Error fetching stores:', error)
        return new Response(
          JSON.stringify({ error: 'Failed to fetch stores' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Fetch exclusion stats for all stores
      const storeIds = stores?.map(store => store.id) || [];
      let exclusionStats: Record<string, any> = {};

      if (storeIds.length > 0) {
        // Initialize stats for all stores
        storeIds.forEach(storeId => {
          exclusionStats[storeId] = {
            excluded_categories: 0,
            excluded_products: 0
          };
        });

        // Get excluded categories for all stores
        const { data: excludedCategories } = await supabase
          .from('excluded_categories')
          .select('store_id')
          .in('store_id', storeIds);

        if (excludedCategories) {
          excludedCategories.forEach(cat => {
            exclusionStats[cat.store_id].excluded_categories += 1;
          });
        }

        // Get excluded products for each store by platform
        for (const store of stores || []) {
          const platform = store.platform_name;
          let tableName = '';

          if (platform === 'woocommerce') {
            tableName = 'woocommerce_products_cache';
          } else if (platform === 'shopify') {
            tableName = 'shopify_products_cache';
          } else if (platform === 'shoprenter') {
            tableName = 'shoprenter_products_cache';
          }

          if (tableName) {
            const { data: excludedProducts } = await supabase
              .from(tableName)
              .select('id')
              .eq('store_id', store.id)
              .eq('excluded', true);

            if (excludedProducts) {
              exclusionStats[store.id].excluded_products = excludedProducts.length;
            }
          }
        }
      }

      // Transform phone_numbers to phone_number string for backward compatibility
      // Note: For many-to-one relationships, Supabase returns an object, not an array
      const transformedStores = stores?.map(store => {
        // Handle both object (many-to-one) and array (one-to-many) formats
        const phoneData = Array.isArray(store.phone_numbers)
          ? store.phone_numbers[0]
          : store.phone_numbers;

        const storeExclusionStats = exclusionStats[store.id] || {
          excluded_categories: 0,
          excluded_products: 0
        };

        return {
          ...store,
          phone_number: phoneData?.phone_number || null,
          phone_number_details: phoneData || null,
          exclusion_stats: storeExclusionStats
        };
      }) || []

      return new Response(
        JSON.stringify({
          success: true,
          stores: transformedStores
        }),
        { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
      )
    }

    // POST /api/stores/finalize-shoprenter - Finalize ShopRenter installation
    if (path === 'stores/finalize-shoprenter' && req.method === 'POST') {
      const { installation_id } = await req.json()

      if (!installation_id) {
        return new Response(
          JSON.stringify({ error: 'installation_id is required' }),
          { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      const supabaseServiceKey = Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
      const supabaseAdmin = createClient(supabaseUrl, supabaseServiceKey)

      // Get pending installation
      const { data: installation, error: installError } = await supabaseAdmin
        .from('pending_shoprenter_installs')
        .select('*')
        .eq('installation_id', installation_id)
        .single()

      if (installError || !installation) {
        return new Response(
          JSON.stringify({ error: 'Installation not found or expired' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Check if expired
      if (new Date(installation.expires_at) < new Date()) {
        await supabaseAdmin.from('pending_shoprenter_installs').delete().eq('installation_id', installation_id)
        return new Response(
          JSON.stringify({ error: 'Installation expired. Please try again.' }),
          { status: 410, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Verify phone number
      const phoneNumberId = installation.phone_number_id
      if (!phoneNumberId) {
        await supabaseAdmin.from('pending_shoprenter_installs').delete().eq('installation_id', installation_id)
        return new Response(
          JSON.stringify({ error: 'Phone number selection missing. Please reconnect your store.' }),
          { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Verify phone number is still available
      const { data: phoneNumber, error: phoneError } = await supabaseAdmin
        .from('phone_numbers')
        .select('id, is_available')
        .eq('id', phoneNumberId)
        .single()

      if (phoneError || !phoneNumber || !phoneNumber.is_available) {
        await supabaseAdmin.from('pending_shoprenter_installs').delete().eq('installation_id', installation_id)
        return new Response(
          JSON.stringify({ error: 'Selected phone number is no longer available. Please reconnect your store.' }),
          { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Get client credentials from environment (app-level credentials)
      const clientId = Deno.env.get('SHOPRENTER_APP_CLIENT_ID')
      const clientSecret = Deno.env.get('SHOPRENTER_APP_CLIENT_SECRET')

      // Create store record with phone_number_id
      // IMPORTANT: Store client credentials in api_key/api_secret for token renewal
      // Store OAuth tokens in access_token/refresh_token columns
      const { data: newStore, error: storeError } = await supabaseAdmin
        .from('stores')
        .insert({
          user_id: user.id,
          platform_name: 'shoprenter',
          store_name: installation.shopname,
          store_url: `https://${installation.shopname}.shoprenter.hu`,
          api_key: clientId,
          api_secret: clientSecret,
          access_token: installation.access_token,
          refresh_token: installation.refresh_token,
          token_expires_at: new Date(Date.now() + (installation.expires_in * 1000)).toISOString(),
          scopes: installation.scopes || [],
          phone_number_id: phoneNumberId,
          alt_data: {
            token_type: installation.token_type,
            expires_in: installation.expires_in,
            connectedAt: new Date().toISOString(),
            client_id: clientId,
            client_secret: clientSecret
          }
        })
        .select('id')
        .single()

      if (storeError || !newStore) {
        console.error('[API] Error creating store:', storeError)
        return new Response(
          JSON.stringify({ error: 'Failed to save store credentials' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Update phone number to mark as assigned
      const { error: phoneUpdateError } = await supabaseAdmin
        .from('phone_numbers')
        .update({
          is_available: false,
          store_id: newStore.id,
          updated_at: new Date().toISOString()
        })
        .eq('id', phoneNumberId)

      if (phoneUpdateError) {
        console.error('[API] Error updating phone number:', phoneUpdateError)
        // Rollback: delete the store
        await supabaseAdmin.from('stores').delete().eq('id', newStore.id)
        await supabaseAdmin.from('pending_shoprenter_installs').delete().eq('installation_id', installation_id)
        return new Response(
          JSON.stringify({ error: 'Phone number assignment failed. Please try again.' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Delete pending installation
      await supabaseAdmin.from('pending_shoprenter_installs').delete().eq('installation_id', installation_id)

      console.log(`[API] ShopRenter store finalized: ${installation.shopname} with phone number ${phoneNumberId}`)

      // Trigger auto-sync in background
      const triggerSyncUrl = `${supabaseUrl}/functions/v1/trigger-sync`
      fetch(triggerSyncUrl, {
        method: 'POST',
        headers: {
          'Authorization': `Bearer ${supabaseServiceKey}`,
          'Content-Type': 'application/json'
        },
        body: JSON.stringify({ store_id: newStore.id })
      })
        .then(() => console.log(`[API] Auto-sync triggered for ShopRenter store ${newStore.id}`))
        .catch(err => console.error(`[API] Failed to trigger auto-sync:`, err))

      return new Response(
        JSON.stringify({
          success: true,
          message: 'Store connected successfully',
          store_id: newStore.id
        }),
        { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
      )
    }

    // GET /api/stores/:id/sync-status - Get sync status for a store
    if (path.match(/^stores\/[^\/]+\/sync-status$/) && req.method === 'GET') {
      const storeId = path.split('/')[1]

      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('sync_status, sync_started_at, sync_completed_at, sync_error')
        .eq('id', storeId)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      return new Response(
        JSON.stringify({
          success: true,
          status: store.sync_status || 'idle',
          started_at: store.sync_started_at,
          completed_at: store.sync_completed_at,
          error: store.sync_error
        }),
        { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
      )
    }

    // POST /api/stores/:id/sync - Trigger manual sync for a store
    if (path.match(/^stores\/[^\/]+\/sync$/) && req.method === 'POST') {
      const storeId = path.split('/')[1]

      // Verify store ownership
      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('id, platform_name, sync_status')
        .eq('id', storeId)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Check if sync is already running
      if (store.sync_status === 'syncing') {
        return new Response(
          JSON.stringify({
            success: false,
            error: 'Sync already in progress',
            status: 'syncing'
          }),
          { status: 409, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Trigger sync via trigger-sync function
      const supabaseServiceKey = Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
      const triggerSyncUrl = `${supabaseUrl}/functions/v1/trigger-sync`

      const syncResponse = await fetch(triggerSyncUrl, {
        method: 'POST',
        headers: {
          'Authorization': `Bearer ${supabaseServiceKey}`,
          'Content-Type': 'application/json'
        },
        body: JSON.stringify({ store_id: storeId })
      })

      const syncResult = await syncResponse.json()

      if (!syncResponse.ok) {
        return new Response(
          JSON.stringify({
            success: false,
            error: syncResult.error || 'Failed to trigger sync'
          }),
          { status: syncResponse.status, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      return new Response(
        JSON.stringify({
          success: true,
          message: 'Sync triggered successfully',
          status: 'syncing'
        }),
        { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
      )
    }

    // PUT /api/stores/:id/enable - Enable or disable background sync for a store
    if (path.match(/^stores\/[^\/]+\/enable$/) && req.method === 'PUT') {
      const storeId = path.split('/')[1]
      const { enabled } = await req.json()

      if (typeof enabled !== 'boolean') {
        return new Response(
          JSON.stringify({ error: 'enabled must be a boolean value' }),
          { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Verify store ownership
      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('id')
        .eq('id', storeId)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Update or insert store_sync_config
      const { data: existingConfig } = await supabase
        .from('store_sync_config')
        .select('id')
        .eq('store_id', storeId)
        .single()

      if (existingConfig) {
        // Update existing config
        const { error: updateError } = await supabase
          .from('store_sync_config')
          .update({ enabled, updated_at: new Date().toISOString() })
          .eq('store_id', storeId)

        if (updateError) {
          console.error('Error updating store sync config:', updateError)
          return new Response(
            JSON.stringify({ error: 'Failed to update sync configuration' }),
            { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }
      } else {
        // Create new config
        const { error: insertError } = await supabase
          .from('store_sync_config')
          .insert({
            store_id: storeId,
            enabled,
            sync_frequency: 'hourly'
          })

        if (insertError) {
          console.error('Error creating store sync config:', insertError)
          return new Response(
            JSON.stringify({ error: 'Failed to create sync configuration' }),
            { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }
      }

      return new Response(
        JSON.stringify({
          success: true,
          message: `Background sync ${enabled ? 'enabled' : 'disabled'} successfully`,
          enabled
        }),
        { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
      )
    }

    // PUT /api/stores/:id/ai-config - Update AI configuration for a store
    if (path.match(/^stores\/[^\/]+\/ai-config$/) && req.method === 'PUT') {
      const storeId = path.split('/')[1]
      const { ai_config } = await req.json()

      if (!ai_config) {
        return new Response(
          JSON.stringify({ error: 'ai_config is required' }),
          { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Verify store ownership
      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('id, alt_data')
        .eq('id', storeId)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Update store's alt_data with AI config
      const updatedAltData = {
        ...(store.alt_data || {}),
        ai_config
      }

      const { error: updateError } = await supabase
        .from('stores')
        .update({ alt_data: updatedAltData })
        .eq('id', storeId)
        .eq('user_id', user.id)

      if (updateError) {
        console.error('Error updating AI config:', updateError)
        return new Response(
          JSON.stringify({ error: 'Failed to update AI configuration' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      return new Response(
        JSON.stringify({
          success: true,
          message: 'AI configuration updated successfully'
        }),
        { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
      )
    }

    // PUT /api/stores/:id/access-policies - Update data access policies for a store (GDPR compliance)
    if (path.match(/^stores\/[^\/]+\/access-policies$/) && req.method === 'PUT') {
      const storeId = path.split('/')[1]
      const body = await req.json()

      // Validate the request body
      const validPolicies = ['products_access_policy', 'customers_access_policy', 'orders_access_policy']
      const validPolicyValues = ['sync', 'api_only', 'not_allowed']

      for (const key of validPolicies) {
        if (!(key in body)) {
          return new Response(
            JSON.stringify({ error: `Missing required field: ${key}` }),
            { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        if (!validPolicyValues.includes(body[key])) {
          return new Response(
            JSON.stringify({ error: `Invalid value for ${key}. Must be one of: ${validPolicyValues.join(', ')}` }),
            { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }
      }

      // Verify the store belongs to the user
      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('id')
        .eq('id', storeId)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Use service role client to call the helper function
      const supabaseAdmin = createClient(
        Deno.env.get('SUPABASE_URL')!,
        Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
      )

      // Update the access policies for each data type
      const updates = []
      for (const dataType of ['products', 'customers', 'orders']) {
        const policyKey = `${dataType}_access_policy`
        const policy = body[policyKey]

        const { error: updateError } = await supabaseAdmin
          .rpc('set_data_access_policy', {
            p_store_id: storeId,
            p_data_type: dataType,
            p_policy: policy
          })

        if (updateError) {
          console.error(`Error updating ${dataType} access policy:`, updateError)
          updates.push({ dataType, success: false, error: updateError.message })
        } else {
          updates.push({ dataType, success: true })
        }
      }

      // Check if all updates succeeded
      const allSuccess = updates.every(u => u.success)

      if (!allSuccess) {
        return new Response(
          JSON.stringify({
            error: 'Some access policy updates failed',
            details: updates
          }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      return new Response(
        JSON.stringify({
          success: true,
          message: 'Access policies updated successfully',
          policies: body
        }),
        { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
      )
    }

    // DELETE /api/stores/{id} - Delete a store
    if (path.startsWith('stores/') && req.method === 'DELETE') {
      const storeId = path.replace('stores/', '')

      // Verify the store belongs to the user
      const { data: store, error: fetchError } = await supabase
        .from('stores')
        .select('id, platform_name, store_name')
        .eq('id', storeId)
        .eq('user_id', user.id)
        .single()

      if (fetchError || !store) {
        console.error('Error fetching store for deletion:', fetchError)
        return new Response(
          JSON.stringify({
            error: 'Store not found or access denied',
            details: fetchError ? fetchError.message : 'Store does not exist or you do not have permission to delete it'
          }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      console.log(`[API] Attempting to delete store: ${store.store_name} (${store.platform_name}) - ID: ${storeId}`)

      // Delete the store - cascading will handle related records
      const { error: deleteError } = await supabase
        .from('stores')
        .delete()
        .eq('id', storeId)
        .eq('user_id', user.id)

      if (deleteError) {
        console.error('Error deleting store:', {
          storeId,
          userId: user.id,
          error: deleteError
        })
        return new Response(
          JSON.stringify({
            error: 'Failed to delete store',
            details: deleteError.message || 'An unknown error occurred while deleting the store',
            code: deleteError.code || 'unknown'
          }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      console.log(`[API] Store deleted successfully: ${storeId}`)

      return new Response(
        JSON.stringify({
          success: true,
          message: 'Store deleted successfully'
        }),
        { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
      )
    }

    // GET /api/stores/:id/knowledge-data - List knowledge data items
    if (path.match(/^stores\/[^\/]+\/knowledge-data$/) && req.method === 'GET') {
      const storeId = path.split('/')[1]

      // Verify store ownership
      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('id, platform_name')
        .eq('id', storeId)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Get query parameters
      const dataType = url.searchParams.get('data_type')
      const search = url.searchParams.get('search')
      const page = parseInt(url.searchParams.get('page') || '1')
      const limit = Math.min(parseInt(url.searchParams.get('limit') || '50'), 100)
      const offset = (page - 1) * limit

      try {
        // Build queries based on platform and data type
        const results: any[] = []
        const platform = store.platform_name

        // Helper to get table name and ID column
        const getTableInfo = (type: string) => {
          if (platform === 'woocommerce') {
            return { table: `woocommerce_${type}s_cache`, idCol: `wc_${type}_id` }
          } else if (platform === 'shopify') {
            return { table: `shopify_${type}s_cache`, idCol: `shopify_${type}_id` }
          } else if (platform === 'shoprenter') {
            return { table: `shoprenter_${type}s_cache`, idCol: `shoprenter_${type}_id` }
          }
          return null
        }

        // Determine which data types to query
        const typesToQuery = dataType ? [dataType] : ['product', 'order', 'customer']

        for (const type of typesToQuery) {
          const tableInfo = getTableInfo(type)
          if (!tableInfo) continue

          // First, fetch all cache items
          let cacheQuery = supabase
            .from(tableInfo.table)
            .select('*')
            .eq('store_id', storeId)
            .order('created_at', { ascending: false })

          // Apply search filter
          if (search) {
            if (type === 'product') {
              if (platform === 'shoprenter') {
                // For ShopRenter, search in product_data jsonb
                cacheQuery = cacheQuery.or(`product_data->>name.ilike.%${search}%,product_data->>sku.ilike.%${search}%`)
              } else {
                cacheQuery = cacheQuery.or(`name.ilike.%${search}%,sku.ilike.%${search}%`)
              }
            } else if (type === 'order') {
              cacheQuery = cacheQuery.or(`order_number.ilike.%${search}%,customer_name.ilike.%${search}%,customer_email.ilike.%${search}%`)
            } else if (type === 'customer') {
              cacheQuery = cacheQuery.or(`email.ilike.%${search}%,first_name.ilike.%${search}%,last_name.ilike.%${search}%,phone.ilike.%${search}%`)
            }
          }

          const { data: cacheItems, error: cacheError } = await cacheQuery

          if (cacheError) {
            console.error(`Error fetching ${type}s from cache:`, cacheError)
            continue
          }

          if (!cacheItems || cacheItems.length === 0) continue

          // Get all exclusions for this store and type
          const { data: exclusions } = await supabase
            .from('store_data_exclusions')
            .select('data_id, is_enabled, metadata')
            .eq('store_id', storeId)
            .eq('data_type', type)

          // Create a map of exclusions for quick lookup
          const exclusionMap = new Map()
          if (exclusions) {
            exclusions.forEach(e => {
              exclusionMap.set(e.data_id, { is_enabled: e.is_enabled, metadata: e.metadata })
            })
          }

          // Transform cache items to result format
          for (const item of cacheItems) {
            const itemId = item[tableInfo.idCol]
            const exclusionData = exclusionMap.get(itemId)

            let resultItem: any = {
              data_type: type,
              data_id: itemId,
              is_enabled: exclusionData ? exclusionData.is_enabled : true
            }

            // Add type-specific fields
            if (type === 'product') {
              if (platform === 'shoprenter') {
                resultItem.name = item.product_data?.name
                resultItem.sku = item.product_data?.sku
                resultItem.price = item.product_data?.price
              } else {
                resultItem.name = item.name
                resultItem.sku = item.sku
                resultItem.price = item.price
              }
            } else if (type === 'order') {
              resultItem.order_number = item.order_number
              resultItem.customer_name = item.customer_name
              resultItem.customer_email = item.customer_email
              resultItem.total = item.total
            } else if (type === 'customer') {
              resultItem.email = item.email
              resultItem.first_name = item.first_name
              resultItem.last_name = item.last_name
              resultItem.phone = item.phone || ''
            }

            results.push(resultItem)
          }
        }

        // Apply pagination to combined results
        const totalCount = results.length
        const paginatedResults = results.slice(offset, offset + limit)

        return new Response(
          JSON.stringify({
            success: true,
            data: paginatedResults,
            pagination: {
              page,
              limit,
              total: totalCount,
              totalPages: Math.ceil(totalCount / limit)
            }
          }),
          { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      } catch (error) {
        console.error('Error fetching knowledge data:', error)
        return new Response(
          JSON.stringify({ error: 'Failed to fetch knowledge data' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }
    }

    // PUT /api/stores/:id/knowledge-data/:dataType/:dataId - Toggle item state
    if (path.match(/^stores\/[^\/]+\/knowledge-data\/[^\/]+\/[^\/]+$/) && req.method === 'PUT') {
      const pathParts = path.split('/')
      const storeId = pathParts[1]
      const dataType = pathParts[3]
      const dataId = pathParts[4]

      // Validate data_type
      if (!['product', 'order', 'customer'].includes(dataType)) {
        return new Response(
          JSON.stringify({ error: 'Invalid data_type. Must be product, order, or customer' }),
          { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Verify store ownership
      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('id, platform_name')
        .eq('id', storeId)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Get request body
      const body = await req.json()
      const isEnabled = body.is_enabled !== undefined ? body.is_enabled : true

      try {
        // Extract metadata from cache tables
        const platform = store.platform_name
        const tableName = platform === 'woocommerce'
          ? `woocommerce_${dataType}s_cache`
          : platform === 'shopify'
          ? `shopify_${dataType}s_cache`
          : `shoprenter_${dataType}s_cache`

        const idColumn = platform === 'woocommerce'
          ? `wc_${dataType}_id`
          : platform === 'shopify'
          ? `shopify_${dataType}_id`
          : `shoprenter_${dataType}_id`

        // Fetch item from cache to extract metadata
        let metadata = {}
        const { data: cacheItem } = await supabase
          .from(tableName)
          .select('*')
          .eq('store_id', storeId)
          .eq(idColumn, dataId)
          .single()

        if (cacheItem) {
          if (dataType === 'product') {
            if (platform === 'shoprenter') {
              metadata = {
                name: cacheItem.product_data?.name,
                sku: cacheItem.product_data?.sku,
                price: cacheItem.product_data?.price
              }
            } else {
              metadata = {
                name: cacheItem.name,
                sku: cacheItem.sku,
                price: cacheItem.price
              }
            }
          } else if (dataType === 'order') {
            metadata = {
              order_number: cacheItem.order_number,
              customer_name: cacheItem.customer_name,
              customer_email: cacheItem.customer_email,
              total: cacheItem.total
            }
          } else if (dataType === 'customer') {
            metadata = {
              email: cacheItem.email,
              first_name: cacheItem.first_name,
              last_name: cacheItem.last_name,
              phone: cacheItem.phone
            }
          }
        }

        // Upsert into store_data_exclusions
        const { data: exclusion, error: upsertError } = await supabase
          .from('store_data_exclusions')
          .upsert({
            store_id: storeId,
            data_type: dataType,
            data_id: dataId,
            is_enabled: isEnabled,
            metadata,
            updated_at: new Date().toISOString()
          }, {
            onConflict: 'store_id,data_type,data_id'
          })
          .select()
          .single()

        if (upsertError) {
          console.error('Error upserting exclusion:', upsertError)
          return new Response(
            JSON.stringify({ error: 'Failed to update item state' }),
            { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        return new Response(
          JSON.stringify({
            success: true,
            message: `Item ${isEnabled ? 'enabled' : 'disabled'} successfully`,
            data: exclusion
          }),
          { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      } catch (error) {
        console.error('Error updating item state:', error)
        return new Response(
          JSON.stringify({ error: 'Failed to update item state' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }
    }

    // POST /api/stores/:id/knowledge-data/batch - Bulk enable/disable items
    if (path.match(/^stores\/[^\/]+\/knowledge-data\/batch$/) && req.method === 'POST') {
      const storeId = path.split('/')[1]

      // Verify store ownership
      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('id, platform_name')
        .eq('id', storeId)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Get request body
      const body = await req.json()
      const { items, data_type, is_enabled } = body

      if (!items || !Array.isArray(items) || items.length === 0) {
        return new Response(
          JSON.stringify({ error: 'items array is required and must not be empty' }),
          { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      try {
        const platform = store.platform_name
        const results = []

        for (const item of items) {
          const itemDataType = item.data_type || data_type
          const itemDataId = item.data_id
          const itemIsEnabled = item.is_enabled !== undefined ? item.is_enabled : is_enabled

          if (!itemDataType || !itemDataId) {
            continue
          }

          // Validate data_type
          if (!['product', 'order', 'customer'].includes(itemDataType)) {
            continue
          }

          // Extract metadata from cache tables
          const tableName = platform === 'woocommerce'
            ? `woocommerce_${itemDataType}s_cache`
            : platform === 'shopify'
            ? `shopify_${itemDataType}s_cache`
            : `shoprenter_${itemDataType}s_cache`

          const idColumn = platform === 'woocommerce'
            ? `wc_${itemDataType}_id`
            : platform === 'shopify'
            ? `shopify_${itemDataType}_id`
            : `shoprenter_${itemDataType}_id`

          // Fetch item from cache to extract metadata
          let metadata = {}
          const { data: cacheItem } = await supabase
            .from(tableName)
            .select('*')
            .eq('store_id', storeId)
            .eq(idColumn, itemDataId)
            .single()

          if (cacheItem) {
            if (itemDataType === 'product') {
              if (platform === 'shoprenter') {
                metadata = {
                  name: cacheItem.product_data?.name,
                  sku: cacheItem.product_data?.sku,
                  price: cacheItem.product_data?.price
                }
              } else {
                metadata = {
                  name: cacheItem.name,
                  sku: cacheItem.sku,
                  price: cacheItem.price
                }
              }
            } else if (itemDataType === 'order') {
              metadata = {
                order_number: cacheItem.order_number,
                customer_name: cacheItem.customer_name,
                customer_email: cacheItem.customer_email,
                total: cacheItem.total
              }
            } else if (itemDataType === 'customer') {
              metadata = {
                email: cacheItem.email,
                first_name: cacheItem.first_name,
                last_name: cacheItem.last_name,
                phone: cacheItem.phone
              }
            }
          }

          // Upsert into store_data_exclusions
          const { error: upsertError } = await supabase
            .from('store_data_exclusions')
            .upsert({
              store_id: storeId,
              data_type: itemDataType,
              data_id: itemDataId,
              is_enabled: itemIsEnabled,
              metadata,
              updated_at: new Date().toISOString()
            }, {
              onConflict: 'store_id,data_type,data_id'
            })

          if (!upsertError) {
            results.push({
              data_type: itemDataType,
              data_id: itemDataId,
              success: true
            })
          } else {
            results.push({
              data_type: itemDataType,
              data_id: itemDataId,
              success: false,
              error: upsertError.message
            })
          }
        }

        const successCount = results.filter(r => r.success).length
        const failCount = results.filter(r => !r.success).length

        return new Response(
          JSON.stringify({
            success: true,
            message: `Batch operation completed: ${successCount} succeeded, ${failCount} failed`,
            results
          }),
          { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      } catch (error) {
        console.error('Error in batch operation:', error)
        return new Response(
          JSON.stringify({ error: 'Failed to complete batch operation' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }
    }

    // Helper functions for category format transformation
    // =========================================================================

    /**
     * Extract numeric category ID from base64-encoded category ID
     * Example: "Y2F0ZWdvcnktY2F0ZWdvcnlfaWQ9MjA2" -> "206"
     */
    function extractNumericCategoryId(base64CategoryId: string): string | null {
      try {
        const decoded = atob(base64CategoryId)
        // Format: "category-category_id=206"
        const match = decoded.match(/category_id=(\d+)/)
        return match ? match[1] : null
      } catch (error) {
        console.error('Failed to decode category ID:', base64CategoryId, error)
        return null
      }
    }

    /**
     * Transform old category format (base64 strings) to new format (objects with id and name)
     * If already in new format, return as-is
     */
    function transformCategoriesToNewFormat(categories: any[]): Array<{ id: string; name: string }> {
      if (!categories || categories.length === 0) return []

      return categories.map(cat => {
        // If already in new format (has id and name properties)
        if (cat && typeof cat === 'object' && (cat.id || cat.category_id) && cat.name) {
          return {
            id: cat.id || cat.category_id,
            name: cat.name || cat.category_name || 'Unknown Category'
          }
        }

        // If it's old format (base64 string)
        if (typeof cat === 'string') {
          const numericId = extractNumericCategoryId(cat)
          return {
            id: numericId || cat,
            name: 'Legacy Category ' + (numericId || 'Unknown') // Temporary name until sync updates it
          }
        }

        // Fallback for other formats
        return {
          id: cat?.id || cat?.category_id || cat || 'unknown',
          name: cat?.name || cat?.category_name || 'Unknown Category'
        }
      }).filter(cat => cat.id) // Remove invalid categories
    }

    // =========================================================================
    // NEW ENDPOINT: GET /api/store-data/categories - Get categories for a store
    // =========================================================================
    if (path === 'store-data/categories' && req.method === 'GET') {
      const storeId = url.searchParams.get('store_id')

      if (!storeId) {
        return new Response(
          JSON.stringify({ error: 'store_id is required' }),
          { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Verify store ownership
      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('id, platform_name')
        .eq('id', storeId)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      try {
        // Get all products for this store to extract categories
        const platform = store.platform_name
        const tableName = platform === 'woocommerce'
          ? 'woocommerce_products_cache'
          : platform === 'shopify'
          ? 'shopify_products_cache'
          : 'shoprenter_products_cache'

        const { data: products, error: productsError } = await supabase
          .from(tableName)
          .select('categories')
          .eq('store_id', storeId)

        if (productsError) {
          console.error('Error fetching products for categories:', productsError)
          return new Response(
            JSON.stringify({ error: 'Failed to fetch products' }),
            { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        // Extract and count categories
        const categoryMap = new Map<string, { name: string; count: number }>()

        for (const product of products || []) {
          const transformedCategories = transformCategoriesToNewFormat(product.categories || [])
          for (const cat of transformedCategories) {
            if (categoryMap.has(cat.id)) {
              categoryMap.get(cat.id)!.count++
            } else {
              categoryMap.set(cat.id, { name: cat.name, count: 1 })
            }
          }
        }

        // Get excluded categories
        const { data: excludedCategories } = await supabase
          .from('excluded_categories')
          .select('category_id')
          .eq('store_id', storeId)
          .eq('platform', platform)

        const excludedCategoryIds = new Set(excludedCategories?.map(ec => ec.category_id) || [])

        // Build response format
        const categories = Array.from(categoryMap.entries()).map(([categoryId, { name, count }]) => ({
          category_id: categoryId,
          category_name: name,
          product_count: count,
          is_excluded: excludedCategoryIds.has(categoryId)
        }))

        return new Response(
          JSON.stringify({
            success: true,
            categories: categories.sort((a, b) => a.category_name.localeCompare(b.category_name))
          }),
          { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      } catch (error) {
        console.error('Error fetching categories:', error)
        return new Response(
          JSON.stringify({ error: 'Failed to fetch categories' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }
    }

    // =========================================================================
    // NEW ENDPOINT: PUT /api/store-data/categories/:categoryId/exclude
    // =========================================================================
    if (path.match(/^store-data\/categories\/[^\/]+\/exclude$/) && req.method === 'PUT') {
      const categoryId = path.split('/')[2]
      const { store_id, exclude, category_name } = await req.json()

      if (!store_id) {
        return new Response(
          JSON.stringify({ error: 'store_id is required' }),
          { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Verify store ownership
      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('id, platform_name')
        .eq('id', store_id)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      try {
        // Use the helper function to toggle category exclusion
        const { error: toggleError } = await supabase.rpc('toggle_category_exclusion', {
          p_store_id: store_id,
          p_category_id: categoryId,
          p_category_name: category_name || categoryId,
          p_platform: store.platform_name,
          p_exclude: exclude
        })

        if (toggleError) {
          console.error('Error toggling category exclusion:', toggleError)
          return new Response(
            JSON.stringify({ error: 'Failed to toggle category exclusion' }),
            { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        return new Response(
          JSON.stringify({
            success: true,
            message: `Category ${exclude ? 'excluded' : 'included'} successfully`
          }),
          { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      } catch (error) {
        console.error('Error toggling category exclusion:', error)
        return new Response(
          JSON.stringify({ error: 'Failed to toggle category exclusion' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }
    }

    // =========================================================================
    // UPDATED: GET /api/store-data/products - Get products with exclusion info
    // =========================================================================
    if (path.match(/^store-data\/(products)$/) && req.method === 'GET') {
      const dataType = path.split('/')[1]
      const storeId = url.searchParams.get('store_id')
      const page = parseInt(url.searchParams.get('page') || '1')
      const limit = parseInt(url.searchParams.get('limit') || '25')
      const search = url.searchParams.get('search')
      const enabledFilter = url.searchParams.get('enabled')
      const categoryFilter = url.searchParams.get('category')

      if (!storeId) {
        return new Response(
          JSON.stringify({ error: 'store_id is required' }),
          { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Verify store ownership
      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('id, platform_name')
        .eq('id', storeId)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      try {
        const platform = store.platform_name

        // Use the cache table directly
        const tableName = platform === 'woocommerce'
          ? 'woocommerce_products_cache'
          : platform === 'shopify'
          ? 'shopify_products_cache'
          : 'shoprenter_products_cache'

        // Build query
        let query = supabase
          .from(tableName)
          .select('*')
          .eq('store_id', storeId)
          .order('last_synced_at', { ascending: false })

        // Apply search filter
        if (search) {
          query = query.or(`name.ilike.%${search}%,sku.ilike.%${search}%`)
        }

        // Apply category filter - disabled temporarily because of format mismatch
        // Will be re-enabled after sync function deployment updates data format
        // if (categoryFilter) {
        //   query = query.contains('categories', [{ id: categoryFilter }])
        // }

        const { data: products, error: productsError } = await query

        if (productsError) {
          console.error('Error fetching products:', productsError)
          return new Response(
            JSON.stringify({ error: 'Failed to fetch products' }),
            { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        if (!products || products.length === 0) {
          return new Response(
            JSON.stringify({
              success: true,
              data: [],
              total: 0,
              enabled_count: 0,
              disabled_count: 0
            }),
            { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        // Get excluded categories for this store
        const { data: excludedCategories } = await supabase
          .from('excluded_categories')
          .select('category_id')
          .eq('store_id', storeId)
          .eq('platform', platform)

        const excludedCategoryIds = new Set(excludedCategories?.map(ec => ec.category_id) || [])

        // Check if product is excluded by category
        const isExcludedByCategory = (categories: any[]): boolean => {
          if (!categories || categories.length === 0) return false
          return categories.some(cat => {
            const catId = cat.id || cat.category_id || cat
            return excludedCategoryIds.has(catId.toString())
          })
        }

        // Transform to response format
        let results = products.map(p => {
          // Transform categories to consistent format
          const transformedCategories = transformCategoriesToNewFormat(p.categories || [])

          const excludedByCategory = isExcludedByCategory(transformedCategories)
          const excludedByIndividual = p.excluded === true
          const effectivelyExcluded = excludedByIndividual || excludedByCategory

          return {
            id: p.product_id,
            inner_id: p.inner_id, // For ShopRenter
            name: p.name || '',
            sku: p.sku || '',
            categories: transformedCategories, // Now consistently formatted
            enabled_in_context: !effectivelyExcluded, // Inverted logic: enabled = NOT excluded
            excluded_by_individual: excludedByIndividual,
            excluded_by_category: excludedByCategory,
            exclusion_reason: excludedByIndividual ? 'individual' : excludedByCategory ? 'category' : null
          }
        })

        // Apply category filter after transformation
        if (categoryFilter && categoryFilter !== 'all') {
          results = results.filter(r =>
            r.categories.some(cat => cat.id === categoryFilter)
          )
        }

        // Apply enabled filter if specified
        if (enabledFilter !== null) {
          const filterEnabled = enabledFilter === 'true'
          results = results.filter(r => r.enabled_in_context === filterEnabled)
        }

        // Calculate counts
        const enabledCount = results.filter(r => r.enabled_in_context).length
        const disabledCount = results.filter(r => !r.enabled_in_context).length
        const totalCount = results.length

        // Apply pagination
        const offset = (page - 1) * limit
        const paginatedResults = results.slice(offset, offset + limit)

        return new Response(
          JSON.stringify({
            success: true,
            data: paginatedResults,
            total: totalCount,
            enabled_count: enabledCount,
            disabled_count: disabledCount
          }),
          { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      } catch (error) {
        console.error('Error fetching products:', error)
        return new Response(
          JSON.stringify({ error: 'Failed to fetch products' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }
    }

    // =========================================================================
    // UPDATED: PUT /api/store-data/products/:id/toggle - Toggle individual product
    // =========================================================================
    if (path.match(/^store-data\/products\/[^\/]+\/toggle$/) && req.method === 'PUT') {
      const itemId = path.split('/')[2]
      const { store_id, enabled } = await req.json()

      if (!store_id) {
        return new Response(
          JSON.stringify({ error: 'store_id is required' }),
          { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Verify store ownership
      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('id, platform_name')
        .eq('id', store_id)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      try {
        const platform = store.platform_name
        const tableName = platform === 'woocommerce'
          ? 'woocommerce_products_cache'
          : platform === 'shopify'
          ? 'shopify_products_cache'
          : 'shoprenter_products_cache'

        // Update excluded column directly in cache table
        // Note: enabled in UI = NOT excluded in DB
        const { error: updateError } = await supabase
          .from(tableName)
          .update({
            excluded: !enabled // Inverted logic
          })
          .eq('store_id', store_id)
          .eq('product_id', itemId)

        if (updateError) {
          console.error('Error updating product:', updateError)
          return new Response(
            JSON.stringify({ error: 'Failed to update product' }),
            { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        return new Response(
          JSON.stringify({
            success: true,
            message: `Product ${enabled ? 'enabled' : 'disabled'} successfully`
          }),
          { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      } catch (error) {
        console.error('Error toggling product:', error)
        return new Response(
          JSON.stringify({ error: 'Failed to update product' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }
    }

    // =========================================================================
    // UPDATED: PUT /api/store-data/products/bulk-toggle - Bulk toggle products
    // =========================================================================
    if (path.match(/^store-data\/products\/bulk-toggle$/) && req.method === 'PUT') {
      const { store_id, item_ids, enabled } = await req.json()

      if (!store_id || !item_ids || !Array.isArray(item_ids)) {
        return new Response(
          JSON.stringify({ error: 'store_id and item_ids array are required' }),
          { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Verify store ownership
      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('id, platform_name')
        .eq('id', store_id)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      try {
        const platform = store.platform_name
        const tableName = platform === 'woocommerce'
          ? 'woocommerce_products_cache'
          : platform === 'shopify'
          ? 'shopify_products_cache'
          : 'shoprenter_products_cache'

        // Update all products in bulk
        for (const itemId of item_ids) {
          await supabase
            .from(tableName)
            .update({
              excluded: !enabled // Inverted logic
            })
            .eq('store_id', store_id)
            .eq('product_id', itemId)
        }

        return new Response(
          JSON.stringify({
            success: true,
            message: `${item_ids.length} products ${enabled ? 'enabled' : 'disabled'} successfully`
          }),
          { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      } catch (error) {
        console.error('Error bulk toggling products:', error)
        return new Response(
          JSON.stringify({ error: 'Failed to update products' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }
    }

    // =========================================================================
    // UPDATED: PUT /api/store-data/products/enable-all - Enable all products
    // =========================================================================
    if (path.match(/^store-data\/products\/enable-all$/) && req.method === 'PUT') {
      const { store_id } = await req.json()

      if (!store_id) {
        return new Response(
          JSON.stringify({ error: 'store_id is required' }),
          { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Verify store ownership
      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('id, platform_name')
        .eq('id', store_id)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      try {
        const platform = store.platform_name
        const tableName = platform === 'woocommerce'
          ? 'woocommerce_products_cache'
          : platform === 'shopify'
          ? 'shopify_products_cache'
          : 'shoprenter_products_cache'

        // Set excluded = false for all products (enable all)
        const { error: updateError } = await supabase
          .from(tableName)
          .update({
            excluded: false
          })
          .eq('store_id', store_id)

        if (updateError) {
          console.error('Error enabling all products:', updateError)
          return new Response(
            JSON.stringify({ error: 'Failed to enable all products' }),
            { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        // Also clear all excluded categories for this store
        await supabase
          .from('excluded_categories')
          .delete()
          .eq('store_id', store_id)
          .eq('platform', platform)

        return new Response(
          JSON.stringify({
            success: true,
            message: 'All products enabled successfully'
          }),
          { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      } catch (error) {
        console.error('Error enabling all products:', error)
        return new Response(
          JSON.stringify({ error: 'Failed to enable all products' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }
    }

    // =========================================================================
    // UPDATED: PUT /api/store-data/products/disable-all - Disable all products
    // =========================================================================
    if (path.match(/^store-data\/products\/disable-all$/) && req.method === 'PUT') {
      const { store_id } = await req.json()

      if (!store_id) {
        return new Response(
          JSON.stringify({ error: 'store_id is required' }),
          { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      // Verify store ownership
      const { data: store, error: storeError } = await supabase
        .from('stores')
        .select('id, platform_name')
        .eq('id', store_id)
        .eq('user_id', user.id)
        .single()

      if (storeError || !store) {
        return new Response(
          JSON.stringify({ error: 'Store not found or access denied' }),
          { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }

      try {
        const platform = store.platform_name
        const tableName = platform === 'woocommerce'
          ? 'woocommerce_products_cache'
          : platform === 'shopify'
          ? 'shopify_products_cache'
          : 'shoprenter_products_cache'

        // Set excluded = true for all products (disable all)
        const { error: updateError } = await supabase
          .from(tableName)
          .update({
            excluded: true
          })
          .eq('store_id', store_id)

        if (updateError) {
          console.error('Error disabling all products:', updateError)
          return new Response(
            JSON.stringify({ error: 'Failed to disable all products' }),
            { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        return new Response(
          JSON.stringify({
            success: true,
            message: 'All products disabled successfully'
          }),
          { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      } catch (error) {
        console.error('Error disabling all products:', error)
        return new Response(
          JSON.stringify({ error: 'Failed to disable all products' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }
    }
    // GET /api/dashboard/stats - Get dashboard statistics
    if (path === 'dashboard/stats' && req.method === 'GET') {
      // TODO: Implement real dashboard stats calculation
      // For now, returning mock/empty data to unblock the frontend
      return new Response(
        JSON.stringify({
          success: true,
          stats: {
            totalCalls: { value: 0, change: '0%', changeType: 'neutral' },
            resolvedCalls: { value: 0, change: '0%', changeType: 'neutral' },
            avgDuration: { value: 0, formatted: '0:00', change: '0%', changeType: 'neutral' },
            totalCost: { value: 0, formatted: '$0.00', change: '0%', changeType: 'neutral' },
            timeSaved: { value: '0h', formatted: '0 hours', change: '0%', changeType: 'neutral' },
            humanCostSaved: { value: 0, formatted: '$0.00', change: '0%', changeType: 'neutral' },
            resolutionRate: { value: 0, dailyChange: '0%', weeklyChange: '0%' },
            topIntents: []
          }
        }),
        { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
      )
    }

    // GET /api/call-logs - Get call logs
    if (path === 'call-logs' && req.method === 'GET') {
      try {
        // Fetch call logs from database
        const { data: callLogs, error: callLogsError } = await supabase
          .from('call_logs')
          .select('*')
          .eq('user_id', user.id)
          .order('created_at', { ascending: false })

        if (callLogsError) {
          console.error('Error fetching call logs:', callLogsError)
          return new Response(
            JSON.stringify({ error: 'Failed to fetch call logs' }),
            { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        // Transform call logs to match frontend format
        const transformedCallLogs = (callLogs || []).map(log => {
          // Format duration
          const duration = log.duration_seconds
            ? `${Math.floor(log.duration_seconds / 60)}:${String(log.duration_seconds % 60).padStart(2, '0')}`
            : '0:00'

          // Determine outcome color
          let outcomeColor = 'text-slate-300'
          if (log.call_outcome === 'resolved' || log.call_outcome === 'interested') {
            outcomeColor = 'text-green-500'
          } else if (log.call_outcome === 'not_interested' || log.call_outcome === 'failed') {
            outcomeColor = 'text-red-500'
          } else if (log.call_outcome === 'pending') {
            outcomeColor = 'text-yellow-500'
          }

          // Determine sentiment from analysis or summary
          let sentiment = 'Neutral'
          let sentimentColor = 'text-slate-300'

          if (log.analysis?.successEvaluation === 'true' || log.analysis?.successEvaluation === true) {
            sentiment = 'Positive'
            sentimentColor = 'text-green-500'
          } else if (log.analysis?.successEvaluation === 'false' || log.analysis?.successEvaluation === false) {
            sentiment = 'Negative'
            sentimentColor = 'text-red-500'
          }

          // Format cost
          const cost = log.cost_total ? `$${Number(log.cost_total).toFixed(4)}` : '$0.00'

          // Format time
          const time = new Date(log.created_at).toLocaleString()

          return {
            id: log.id,
            time,
            customer: log.customer_number || 'Unknown',
            intent: log.summary || 'N/A',
            outcome: log.call_outcome || 'pending',
            duration,
            sentiment,
            cost,
            outcomeColor,
            sentimentColor,
            // Include full log data for details modal
            fullData: log
          }
        })

        return new Response(
          JSON.stringify({
            success: true,
            call_logs: transformedCallLogs
          }),
          { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      } catch (error) {
        console.error('Error in call-logs endpoint:', error)
        return new Response(
          JSON.stringify({ error: 'Internal server error' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }
    }

    // GET /api/phone-numbers - Get available phone numbers for user's country
    if (path === 'phone-numbers' && req.method === 'GET') {
      try {
        // Get user's profile to find their country
        const { data: profile, error: profileError } = await supabase
          .from('profiles')
          .select('country_code, country_name')
          .eq('id', user.id)
          .single()

        if (profileError) {
          console.error('Error fetching user profile:', profileError)
          return new Response(
            JSON.stringify({ error: 'Failed to fetch user profile' }),
            { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        // Check query parameters
        const availableOnly = url.searchParams.get('available') === 'true'
        const allCountries = url.searchParams.get('all_countries') === 'true'
        const countryFilter = url.searchParams.get('country')
        const cityFilter = url.searchParams.get('city')
        const groupBy = url.searchParams.get('group_by') // 'countries' or 'cities'

        // Special endpoint: Get list of countries with available phone numbers
        if (groupBy === 'countries') {
          const { data: countries, error: countriesError } = await supabase
            .from('phone_numbers')
            .select('country_code, country_name')
            .eq('is_active', true)
            .eq('is_available', true)
            .is('assigned_to_store_id', null)
            .order('country_name', { ascending: true })

          if (countriesError) {
            console.error('Error fetching countries:', countriesError)
            return new Response(
              JSON.stringify({ error: 'Failed to fetch countries' }),
              { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
            )
          }

          // Get unique countries
          const uniqueCountries = Array.from(
            new Map((countries || []).map(c => [c.country_code, c])).values()
          )

          return new Response(
            JSON.stringify({
              success: true,
              countries: uniqueCountries,
              user_country: profile?.country_code || null
            }),
            { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        // Special endpoint: Get list of cities for a country
        if (groupBy === 'cities' && countryFilter) {
          const { data: cities, error: citiesError } = await supabase
            .from('phone_numbers')
            .select('location')
            .eq('is_active', true)
            .eq('is_available', true)
            .eq('country_code', countryFilter)
            .is('assigned_to_store_id', null)
            .order('location', { ascending: true })

          if (citiesError) {
            console.error('Error fetching cities:', citiesError)
            return new Response(
              JSON.stringify({ error: 'Failed to fetch cities' }),
              { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
            )
          }

          // Get unique cities
          const uniqueCities = Array.from(
            new Set((cities || []).map(c => c.location))
          ).filter(Boolean)

          return new Response(
            JSON.stringify({
              success: true,
              cities: uniqueCities
            }),
            { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        // Build query for phone numbers
        let query = supabase
          .from('phone_numbers')
          .select('*')
          .eq('is_active', true)
          .order('country_name', { ascending: true })
          .order('location', { ascending: true })

        // Filter by availability if requested
        if (availableOnly) {
          query = query.eq('is_available', true)
          query = query.is('assigned_to_store_id', null)
        }

        // Filter by specific country if provided
        if (countryFilter) {
          query = query.eq('country_code', countryFilter)
        } else if (profile?.country_code && !allCountries) {
          // Filter by user's country if set (unless all_countries=true)
          query = query.eq('country_code', profile.country_code)
        }

        // Filter by specific city if provided
        if (cityFilter) {
          query = query.eq('location', cityFilter)
        }

        const { data: phoneNumbers, error: phoneError } = await query

        if (phoneError) {
          console.error('Error fetching phone numbers:', phoneError)
          return new Response(
            JSON.stringify({ error: 'Failed to fetch phone numbers' }),
            { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        return new Response(
          JSON.stringify({
            success: true,
            phone_numbers: phoneNumbers || [],
            user_country: profile?.country_code || null
          }),
          { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      } catch (error) {
        console.error('Error in phone-numbers endpoint:', error)
        return new Response(
          JSON.stringify({ error: 'Internal server error' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }
    }

    // PUT /api/profile/country - Update user's country
    if (path === 'profile/country' && req.method === 'PUT') {
      try {
        const { country_code, country_name } = await req.json()

        if (!country_code || !country_name) {
          return new Response(
            JSON.stringify({ error: 'country_code and country_name are required' }),
            { status: 400, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        // Update user's profile with country information
        const { error: updateError } = await supabase
          .from('profiles')
          .update({
            country_code,
            country_name,
            updated_at: new Date().toISOString()
          })
          .eq('id', user.id)

        if (updateError) {
          console.error('Error updating user country:', updateError)
          return new Response(
            JSON.stringify({ error: 'Failed to update country' }),
            { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
          )
        }

        return new Response(
          JSON.stringify({
            success: true,
            message: 'Country updated successfully'
          }),
          { status: 200, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      } catch (error) {
        console.error('Error in profile/country endpoint:', error)
        return new Response(
          JSON.stringify({ error: 'Internal server error' }),
          { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
        )
      }
    }

    return new Response(
      JSON.stringify({ error: 'Not found' }),
      { status: 404, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
    )

  } catch (error) {
    console.error('[API] Unexpected error:', error)
    return new Response(
      JSON.stringify({ error: 'Internal server error', details: error.message }),
      { status: 500, headers: { ...corsHeaders, 'Content-Type': 'application/json' } }
    )
  }
  })(req)
})
