// This file contains the UPDATED store-data endpoints for the API Edge Function
// These replace the existing endpoints that used store_data_exclusions table
//
// INSTRUCTIONS: Replace the corresponding sections in api/index.ts with these implementations
//
// Changes:
// 1. Use 'excluded' column in *_products_cache tables instead of store_data_exclusions
// 2. Add category exclusion support
// 3. Add new endpoints: GET /categories, PUT /categories/:id/exclude
// 4. Update responses to include exclusion_reason (individual vs. category)

    // =========================================================================
    // 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 {
        // Use the helper function to get categories
        const { data: categories, error: categoriesError } = await supabase
          .rpc('get_store_categories', {
            p_store_id: storeId,
            p_platform: store.platform_name
          })

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

        return new Response(
          JSON.stringify({
            success: true,
            categories: categories || []
          }),
          { 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 view with computed exclusion status
        const viewName = platform === 'woocommerce'
          ? 'woocommerce_products_with_exclusion'
          : platform === 'shopify'
          ? 'shopify_products_with_exclusion'
          : 'shoprenter_products_with_exclusion'

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

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

        // Apply category filter
        if (categoryFilter) {
          // Filter products that have this category
          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' } }
          )
        }

        // Transform to response format
        let results = products.map(p => ({
          id: p[platform === 'woocommerce' ? 'wc_product_id' : platform === 'shopify' ? 'shopify_product_id' : 'shoprenter_product_id'],
          name: p.name || '',
          sku: p.sku || '',
          price: p.price || '0',
          currency: p.currency || 'USD',
          categories: p.categories || [],
          enabled_in_context: !p.effectively_excluded, // Inverted logic: enabled = NOT excluded
          excluded_by_individual: p.excluded,
          excluded_by_category: p.exclusion_reason === 'category',
          exclusion_reason: p.exclusion_reason
        }))

        // 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'

        const idColumn = platform === 'woocommerce'
          ? 'wc_product_id'
          : platform === 'shopify'
          ? 'shopify_product_id'
          : 'shoprenter_product_id'

        // 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
            updated_at: new Date().toISOString()
          })
          .eq('store_id', store_id)
          .eq(idColumn, 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'

        const idColumn = platform === 'woocommerce'
          ? 'wc_product_id'
          : platform === 'shopify'
          ? 'shopify_product_id'
          : 'shoprenter_product_id'

        // Update all products in bulk
        for (const itemId of item_ids) {
          await supabase
            .from(tableName)
            .update({
              excluded: !enabled, // Inverted logic
              updated_at: new Date().toISOString()
            })
            .eq('store_id', store_id)
            .eq(idColumn, 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,
            updated_at: new Date().toISOString()
          })
          .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,
            updated_at: new Date().toISOString()
          })
          .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' } }
        )
      }
    }
