import { serve } from "https://deno.land/std@0.168.0/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
};

serve(async (req) => {
  if (req.method === 'OPTIONS') {
    return new Response(null, { headers: corsHeaders });
  }

  try {
    const supabaseUrl = Deno.env.get('SUPABASE_URL')!;
    const supabaseKey = Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!;
    const supabase = createClient(supabaseUrl, supabaseKey);

    const { query_type, params = {} } = await req.json();

    console.log(`[service-query] type=${query_type}, params=`, params);

    let result: any = null;

    switch (query_type) {
      // ==================== 售后工单查询 ====================
      case 'after_sales_detail': {
        const { id } = params;
        if (!id) throw new Error('缺少工单ID');

        const { data, error } = await supabase
          .from('after_sales_requests')
          .select(`
            *,
            orders:order_id (
              id, order_number, status, total_amount, created_at,
              shipping_address, tracking_number
            ),
            products:product_id (
              id, name, price, image_url, brand, category
            )
          `)
          .eq('id', id)
          .maybeSingle();

        if (error) throw error;
        result = data;
        break;
      }

      case 'after_sales_list': {
        const { status, type, limit = 20, offset = 0 } = params;

        let query = supabase
          .from('after_sales_requests')
          .select(`
            id, type, reason, status, created_at, updated_at,
            refund_amount, tracking_number,
            orders:order_id (order_number, total_amount),
            products:product_id (name, price, image_url)
          `, { count: 'exact' })
          .order('created_at', { ascending: false })
          .range(offset, offset + limit - 1);

        if (status) query = query.eq('status', status);
        if (type) query = query.eq('type', type);

        const { data, error, count } = await query;
        if (error) throw error;

        result = { items: data, total: count, limit, offset };
        break;
      }

      case 'after_sales_stats': {
        const { data: all, error: allErr } = await supabase
          .from('after_sales_requests')
          .select('id, status, type, refund_amount');

        if (allErr) throw allErr;

        const stats = {
          total: all?.length || 0,
          by_status: {} as Record<string, number>,
          by_type: {} as Record<string, number>,
          total_refund_amount: 0,
        };

        all?.forEach((item: any) => {
          stats.by_status[item.status] = (stats.by_status[item.status] || 0) + 1;
          stats.by_type[item.type] = (stats.by_type[item.type] || 0) + 1;
          if (item.refund_amount) {
            stats.total_refund_amount += Number(item.refund_amount);
          }
        });

        result = stats;
        break;
      }

      // ==================== 订单查询 ====================
      case 'order_detail': {
        const { id, order_number } = params;
        if (!id && !order_number) throw new Error('缺少订单ID或订单号');

        let query = supabase
          .from('orders')
          .select(`
            *,
            order_items (
              id, quantity, unit_price,
              products:product_id (id, name, price, image_url, brand)
            ),
            user_coupons (
              id, status, used_at,
              coupons:coupon_id (name, code, type, discount_value)
            )
          `);

        if (id) query = query.eq('id', id);
        else query = query.eq('order_number', order_number);

        const { data, error } = await query.maybeSingle();
        if (error) throw error;

        result = data;
        break;
      }

      case 'orders_list': {
        const { status, user_id, limit = 20, offset = 0, date_from, date_to } = params;

        let query = supabase
          .from('orders')
          .select(`
            id, order_number, status, total_amount, created_at, updated_at,
            shipping_address, tracking_number, shipped_at
          `, { count: 'exact' })
          .order('created_at', { ascending: false })
          .range(offset, offset + limit - 1);

        if (status) query = query.eq('status', status);
        if (user_id) query = query.eq('user_id', user_id);
        if (date_from) query = query.gte('created_at', date_from);
        if (date_to) query = query.lte('created_at', date_to);

        const { data, error, count } = await query;
        if (error) throw error;

        result = { items: data, total: count, limit, offset };
        break;
      }

      case 'order_stats': {
        const { data: orders, error } = await supabase
          .from('orders')
          .select('id, status, total_amount, created_at');

        if (error) throw error;

        const stats = {
          total: orders?.length || 0,
          by_status: {} as Record<string, number>,
          total_amount: 0,
          today_count: 0,
          today_amount: 0,
        };

        const today = new Date().toISOString().split('T')[0];

        orders?.forEach((order: any) => {
          stats.by_status[order.status] = (stats.by_status[order.status] || 0) + 1;
          stats.total_amount += Number(order.total_amount);

          if (order.created_at?.startsWith(today)) {
            stats.today_count++;
            stats.today_amount += Number(order.total_amount);
          }
        });

        result = stats;
        break;
      }

      // ==================== 客户查询 ====================
      case 'customer_detail': {
        const { user_id } = params;
        if (!user_id) throw new Error('缺少用户ID');

        // 获取用户基本信息
        const { data: profile, error: profileErr } = await supabase
          .from('profiles')
          .select('*')
          .eq('user_id', user_id)
          .maybeSingle();

        if (profileErr) throw profileErr;

        // 获取订单统计
        const { data: orders, error: ordersErr } = await supabase
          .from('orders')
          .select('id, status, total_amount, created_at')
          .eq('user_id', user_id)
          .order('created_at', { ascending: false });

        if (ordersErr) throw ordersErr;

        // 获取售后记录
        const { data: afterSales, error: asErr } = await supabase
          .from('after_sales_requests')
          .select('id, type, status, created_at, refund_amount')
          .eq('user_id', user_id)
          .order('created_at', { ascending: false });

        if (asErr) throw asErr;

        // 获取余额
        const { data: balance, error: balErr } = await supabase
          .from('user_balances')
          .select('*')
          .eq('user_id', user_id)
          .maybeSingle();

        if (balErr) throw balErr;

        // 获取优惠券
        const { data: coupons, error: couponErr } = await supabase
          .from('user_coupons')
          .select(`
            id, status, received_at, used_at,
            coupons:coupon_id (name, code, type, discount_value, end_date)
          `)
          .eq('user_id', user_id);

        if (couponErr) throw couponErr;

        result = {
          profile,
          orders_summary: {
            total_orders: orders?.length || 0,
            total_spent: orders?.reduce((sum: number, o: any) => sum + Number(o.total_amount), 0) || 0,
            recent_orders: orders?.slice(0, 5) || [],
          },
          after_sales_summary: {
            total_requests: afterSales?.length || 0,
            pending_count: afterSales?.filter((a: any) => a.status === 'pending').length || 0,
            recent_requests: afterSales?.slice(0, 5) || [],
          },
          balance,
          coupons: {
            total: coupons?.length || 0,
            unused: coupons?.filter((c: any) => c.status === 'unused').length || 0,
            items: coupons?.slice(0, 10) || [],
          },
        };
        break;
      }

      case 'customers_list': {
        const { limit = 20, offset = 0, has_orders } = params;

        // 获取有订单的用户
        let query = supabase
          .from('profiles')
          .select('id, user_id, display_name, avatar_url, phone, created_at', { count: 'exact' })
          .order('created_at', { ascending: false })
          .range(offset, offset + limit - 1);

        const { data: profiles, error, count } = await query;
        if (error) throw error;

        // 为每个用户获取订单统计
        const enrichedProfiles = await Promise.all(
          (profiles || []).map(async (profile: any) => {
            const { count: orderCount } = await supabase
              .from('orders')
              .select('id', { count: 'exact', head: true })
              .eq('user_id', profile.user_id);

            const { data: lastOrder } = await supabase
              .from('orders')
              .select('created_at, total_amount')
              .eq('user_id', profile.user_id)
              .order('created_at', { ascending: false })
              .limit(1)
              .maybeSingle();

            return {
              ...profile,
              order_count: orderCount || 0,
              last_order_at: lastOrder?.created_at,
              last_order_amount: lastOrder?.total_amount,
            };
          })
        );

        // 如果需要筛选有订单的用户
        const filteredProfiles = has_orders
          ? enrichedProfiles.filter((p: any) => p.order_count > 0)
          : enrichedProfiles;

        result = { items: filteredProfiles, total: count, limit, offset };
        break;
      }

      // ==================== 产品查询 ====================
      case 'product_detail': {
        const { id } = params;
        if (!id) throw new Error('缺少产品ID');

        const { data, error } = await supabase
          .from('products')
          .select('*')
          .eq('id', id)
          .maybeSingle();

        if (error) throw error;
        result = data;
        break;
      }

      case 'products_list': {
        const { brand, category, is_active, limit = 20, offset = 0 } = params;

        let query = supabase
          .from('products')
          .select('id, name, brand, category, price, stock_quantity, is_active, image_url', { count: 'exact' })
          .order('created_at', { ascending: false })
          .range(offset, offset + limit - 1);

        if (brand) query = query.eq('brand', brand);
        if (category) query = query.eq('category', category);
        if (is_active !== undefined) query = query.eq('is_active', is_active);

        const { data, error, count } = await query;
        if (error) throw error;

        result = { items: data, total: count, limit, offset };
        break;
      }

      default:
        throw new Error(`未知的查询类型: ${query_type}`);
    }

    console.log(`[service-query] 查询成功, type=${query_type}`);

    return new Response(JSON.stringify({
      success: true,
      query_type,
      data: result,
      timestamp: new Date().toISOString(),
    }), {
      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
    });

  } catch (error) {
    console.error('[service-query] Error:', error);

    return new Response(JSON.stringify({
      success: false,
      error: (error instanceof Error ? error.message : '查询失败'),
      timestamp: new Date().toISOString(),
    }), {
      status: 400,
      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
    });
  }
});
