import { buildStorefrontUrl } from "@/lib/app-urls";
import { getSupabaseAdminClient } from "@/lib/supabase-admin";
import { createSupabaseServerClient } from "@/lib/supabase-server";

type QueryableClient = {
  from: (table: string) => {
    select: (columns: string) => {
      not?: (column: string, operator: string, value: string | null) => Promise<{ data: unknown; error: { message?: string; code?: string } | null }>;
      order?: (column: string, options?: { ascending?: boolean }) => Promise<{ data: unknown; error: { message?: string; code?: string } | null }>;
      in?: (column: string, values: string[]) => Promise<{ data: unknown; error: { message?: string; code?: string } | null }>;
      eq: (column: string, value: any) => {
        eq: (column: string, value: any) => {
          order: (column: string, options?: { ascending?: boolean }) => {
            limit: (count: number) => Promise<{ data: unknown; error: { message?: string; code?: string } | null }>;
          }
        }
      };
    };
  };
};

type RawProfile = {
  id?: string;
  slug?: string | null;
  store_name?: string | null;
  catalog_name?: string | null;
  logo_url?: string | null;
  catalog_logo_url?: string | null;
  created_at?: string | null;
};

type RawCustomDomain = {
  profile_id?: string | null;
  domain?: string | null;
  verified?: boolean | null;
};

export type MarketplaceStoreSummary = {
  id: string;
  slug: string;
  name: string;
  logoUrl: string | null;
  publicUrl: string;
  customDomain: string | null;
  createdAt: string | null;
};

const PROFILE_COLUMNS = [
  "id",
  "slug",
  "store_name",
  "catalog_name",
  "logo_url",
  "catalog_logo_url",
  "created_at",
];

function extractMissingColumn(errorMessage: string) {
  const match = errorMessage.match(/column\s+["']?(?:[a-zA-Z0-9_]+\.)?([a-zA-Z0-9_]+)["']?.*does not exist/i);
  return match?.[1] || null;
}

async function fetchProfiles(client: QueryableClient): Promise<RawProfile[]> {
  const columns = [...PROFILE_COLUMNS];

  while (columns.length > 0) {
    const hasSlug = columns.includes("slug");
    const hasCreatedAt = columns.includes("created_at");
    
    let query: any = client.from("profiles").select(columns.join(","));
    
    if (hasSlug) {
      query = query.not("slug", "is", null);
    }
    if (hasCreatedAt) {
      query = query.order("created_at", { ascending: false });
    }

    let response;
    try {
      response = await query;
    } catch (error) {
      throw error;
    }

    if (response && !response.error) {
      return Array.isArray(response.data) ? (response.data as RawProfile[]) : [];
    }

    const message = String(response?.error?.message || "");
    const missingColumn = extractMissingColumn(message);
    if (!missingColumn) {
      throw new Error(message || "Não foi possível consultar os lojistas do marketplace.");
    }

    const nextColumns = columns.filter((column) => column !== missingColumn);
    if (nextColumns.length === columns.length) {
      throw new Error(message || "Não foi possível consultar os lojistas do marketplace.");
    }
    columns.splice(0, columns.length, ...nextColumns);
  }

  return [];
}

async function fetchCustomDomains(client: QueryableClient, profileIds: string[]): Promise<RawCustomDomain[]> {
  if (profileIds.length === 0) return [];

  const query = client.from("profile_custom_domains").select("profile_id,domain,verified");
  if (!query.in) return [];

  const response = await query.in("profile_id", profileIds);
  if (!response.error) {
    return Array.isArray(response.data) ? (response.data as RawCustomDomain[]) : [];
  }

  const code = String(response.error.code || "");
  const message = String(response.error.message || "");
  if (code === "42P01" || message.toLowerCase().includes("profile_custom_domains")) {
    return [];
  }

  throw new Error(message || "Não foi possível consultar os domínios personalizados.");
}

export type MarketplaceProduct = {
  id: string;
  name: string;
  description?: string | null;
  price: number;
  stock?: number;
  image_url?: string | null;
  user_id: string;
  unit_type?: string | null;
  price_on_request?: boolean | null;
  use_variation_price_only?: boolean | null;
  min_sale_qty_enabled?: boolean | null;
  min_sale_qty?: number | null;
  max_sale_qty_enabled?: boolean | null;
  max_sale_qty?: number | null;
  allowed_sale_qty_enabled?: boolean | null;
  allowed_sale_quantities?: number[] | null;
  track_inventory?: boolean | null;
  inventory_scope?: "product" | "option" | "variant" | null;
  inventory_attribute_name?: string | null;
  custom_fields_config?: Array<{
    id?: string | null;
    title?: string | null;
    field_type?: "text" | "textarea" | "number" | null;
    placeholder?: string | null;
    required?: boolean | null;
    variation_signature?: string | null;
    position?: number | null;
    active?: boolean | null;
  }> | null;
  store: MarketplaceStoreSummary | null;
  category?: string | null;
  product_attributes?: Array<{
    id?: string;
    name?: string | null;
    allow_multiple?: boolean | null;
    is_required?: boolean | null;
    position?: number | null;
    options?: Array<
      | string
      | {
          id?: string | null;
          label?: string | null;
          price?: number | null;
          price_applies_per_quantity?: boolean | null;
          stock?: number | null;
          is_required?: boolean | null;
          position?: number | null;
          image_id?: string | null;
          minimum_stock?: number | null;
          reorder_point?: number | null;
        }
    > | null;
  }> | null;
  product_variations?: Array<{
    id: string;
    combination: Record<string, string>;
    price: number | null;
    stock: number;
    sku?: string | null;
  }> | null;
  product_price_tiers?: Array<{
    min_qty: number;
    max_qty?: number | null;
    unit_price: number;
    sort_order?: number | null;
    active?: boolean | null;
  }> | null;
  product_images?: Array<{
    id: string;
    image_url: string;
    sort_order?: number | null;
    is_primary?: boolean | null;
  }> | null;
};

const MARKETPLACE_PRODUCT_SUMMARY_SELECT = `
  id,
  name,
  description,
  price,
  stock,
  image_url,
  user_id,
  unit_type,
  price_on_request,
  use_variation_price_only,
  product_attributes(id,name,options,allow_multiple,is_required,position),
  product_price_tiers(min_qty,max_qty,unit_price,sort_order,active),
  product_images(id,image_url,sort_order,is_primary)
`;

const MARKETPLACE_PRODUCT_DETAIL_SELECT = `
  id,
  name,
  description,
  price,
  stock,
  image_url,
  user_id,
  unit_type,
  price_on_request,
  use_variation_price_only,
  min_sale_qty_enabled,
  min_sale_qty,
  max_sale_qty_enabled,
  max_sale_qty,
  allowed_sale_qty_enabled,
  allowed_sale_quantities,
  track_inventory,
  inventory_scope,
  inventory_attribute_name,
  custom_fields_config,
  product_attributes(id,name,options,allow_multiple,is_required,position),
  product_variations(id,combination,price,stock,sku),
  product_price_tiers(min_qty,max_qty,unit_price,sort_order,active),
  product_images(id,image_url,sort_order,is_primary)
`;

export async function getMarketplaceProducts(limit = 20): Promise<MarketplaceProduct[]> {
  const client = (getSupabaseAdminClient() || (await createSupabaseServerClient())) as unknown as QueryableClient;
  
  if (!client) {
    return [];
  }

  try {
    // Buscamos um número maior de produtos recentes para termos amostra suficiente para misturar
    const response = await client
      .from('products')
      .select(MARKETPLACE_PRODUCT_SUMMARY_SELECT)
      .eq('active', true)
      .eq('show_in_catalog', true)
      .order('created_at', { ascending: false })
      .limit(limit * 5); // Pega 5x mais produtos do que precisamos para poder embaralhar de forma justa

    if (response.error || !response.data) {
      return [];
    }

    const rawProducts = response.data as any[];

    // Agrupar produtos por loja, filtrando os que não possuem imagem
    const productsByStore = new Map<string, any[]>();
    for (const p of rawProducts) {
      // Verifica se tem imagem principal no array de product_images OU tem uma image_url legada
      const hasImage = 
        (p.product_images && p.product_images.length > 0 && p.product_images.some((img: any) => img.image_url)) || 
        (p.image_url && p.image_url.trim() !== '');

      // Se não tiver imagem, pula esse produto e ele não entra no leilão
      if (!hasImage) continue;

      if (!productsByStore.has(p.user_id)) {
        productsByStore.set(p.user_id, []);
      }
      productsByStore.get(p.user_id)!.push(p);
    }

    // Distribuição justa (Round-Robin)
    const fairProducts: any[] = [];
    let hasMore = true;
    let round = 0;

    while (hasMore && fairProducts.length < limit) {
      hasMore = false;
      // Para cada loja que encontramos, pegamos o produto correspondente à rodada atual
      for (const [storeId, storeProducts] of productsByStore.entries()) {
        if (fairProducts.length >= limit) break;
        
        if (round < storeProducts.length) {
          fairProducts.push(storeProducts[round]);
          hasMore = true; // Se pelo menos uma loja tinha produto nesta rodada, tentamos a próxima
        }
      }
      round++;
    }

    // Fetch stores to associate
    const storeIds = [...new Set(fairProducts.map(p => p.user_id))];
    const stores = storeIds.length > 0 ? await listMarketplaceStores() : [];
    const storeMap = new Map(stores.map(s => [s.id, s]));

    return fairProducts.map(p => ({
      ...p,
      store: storeMap.get(p.user_id) || null,
      category: 'Produto'
    })) as MarketplaceProduct[];

  } catch (err) {
    console.error('Error fetching marketplace products:', err);
    return [];
  }
}

export async function getMarketplaceStoreBySlug(slug: string): Promise<MarketplaceStoreSummary | null> {
  const stores = await listMarketplaceStores();
  return stores.find(s => s.slug === slug) || null;
}

export async function getMarketplaceProductsByStore(storeId: string, limit = 20): Promise<MarketplaceProduct[]> {
  const client = (getSupabaseAdminClient() || (await createSupabaseServerClient())) as any;
  
  if (!client) return [];

  try {
    const response = await client
      .from('products')
      .select(MARKETPLACE_PRODUCT_SUMMARY_SELECT)
      .eq('user_id', storeId)
      .eq('active', true)
      .eq('show_in_catalog', true)
      .order('created_at', { ascending: false })
      .limit(limit * 3);

    if (response.error || !response.data) return [];

    const rawProducts = response.data as any[];
    
    // Filter products without images
    const productsWithImages = rawProducts.filter(p => {
      return (p.product_images && p.product_images.length > 0 && p.product_images.some((img: any) => img.image_url)) || 
             (p.image_url && p.image_url.trim() !== '');
    });
    
    // Limit back to requested amount after filtering
    const products = productsWithImages.slice(0, limit);

    const stores = await listMarketplaceStores();
    const store = stores.find(s => s.id === storeId) || null;

    return products.map(p => ({
      ...p,
      store,
      category: 'Produto'
    })) as MarketplaceProduct[];

  } catch (err) {
    console.error('Error fetching marketplace products by store:', err);
    return [];
  }
}

export async function getMarketplaceProductById(id: string): Promise<MarketplaceProduct | null> {
  const client = (getSupabaseAdminClient() || (await createSupabaseServerClient())) as any;
  
  if (!client) return null;

  try {
    const response = await client
      .from('products')
      .select(MARKETPLACE_PRODUCT_DETAIL_SELECT)
      .eq('id', id)
      .single();

    if (response.error || !response.data) return null;

    const p = response.data;
    const stores = await listMarketplaceStores();
    const store = stores.find(s => s.id === p.user_id) || null;

    return {
      ...p,
      store,
      category: 'Produto'
    } as MarketplaceProduct;

  } catch (err) {
    console.error('Error fetching marketplace product by id:', err);
    return null;
  }
}

export async function getMarketplaceProductsByCategory(categorySlug: string, limit = 20): Promise<MarketplaceProduct[]> {
  const client = (getSupabaseAdminClient() || (await createSupabaseServerClient())) as any;
  
  if (!client) return [];

  try {
    // Map category slugs to search keywords
    const categoryMap: Record<string, string> = {
      'cartoes-de-visita': 'cartão',
      'canecas': 'caneca',
      'papelaria': 'papel',
      'adesivos': 'adesivo',
      'brindes': 'brinde',
      'convites': 'convite',
      'embalagens': 'embalagem'
    };

    const keyword = categoryMap[categorySlug] || categorySlug;

    const response = await client
      .from('products')
      .select(MARKETPLACE_PRODUCT_SUMMARY_SELECT)
      .eq('active', true)
      .eq('show_in_catalog', true)
      .ilike('name', `%${keyword}%`)
      .order('created_at', { ascending: false })
      .limit(limit * 3);

    if (response.error || !response.data) return [];

    const rawProducts = response.data as any[];
    
    // Filter products without images
    const productsWithImages = rawProducts.filter(p => {
      return (p.product_images && p.product_images.length > 0 && p.product_images.some((img: any) => img.image_url)) || 
             (p.image_url && p.image_url.trim() !== '');
    });
    
    // Limit back to requested amount after filtering
    const products = productsWithImages.slice(0, limit);
    
    const storeIds = [...new Set(products.map(p => p.user_id))];
    const stores = storeIds.length > 0 ? await listMarketplaceStores() : [];
    const storeMap = new Map(stores.map(s => [s.id, s]));

    return products.map(p => ({
      ...p,
      store: storeMap.get(p.user_id) || null,
      category: 'Produto'
    })) as MarketplaceProduct[];

  } catch (err) {
    console.error('Error fetching marketplace products by category:', err);
    return [];
  }
}

export async function getFeaturedStores(limit = 10): Promise<MarketplaceStoreSummary[]> {
  const stores = await listMarketplaceStores();
  const client = (getSupabaseAdminClient() || (await createSupabaseServerClient())) as any;
  
  if (!client) return stores.slice(0, limit);

  try {
    // Fetch recent products to determine store activity
    const response = await client
      .from('products')
      .select('user_id')
      .eq('active', true)
      .eq('show_in_catalog', true)
      .order('created_at', { ascending: false })
      .limit(1000);

    if (!response.error && response.data) {
      const counts: Record<string, number> = {};
      for (const p of response.data as any[]) {
        counts[p.user_id] = (counts[p.user_id] || 0) + 1;
      }
      
      // Sort by activity (number of recent products) and fallback to name
      const sortedStores = [...stores].sort((a, b) => {
        const countA = counts[a.id] || 0;
        const countB = counts[b.id] || 0;
        if (countA !== countB) return countB - countA;
        return a.name.localeCompare(b.name, "pt-BR", { sensitivity: "base" });
      });
      
      // Only return stores that have at least 1 product if possible, otherwise fill with the rest
      const activeStores = sortedStores.filter(s => (counts[s.id] || 0) > 0);
      if (activeStores.length >= limit) {
        return activeStores.slice(0, limit);
      }
      return sortedStores.slice(0, limit);
    }
  } catch (err) {
    console.error('Error fetching featured stores:', err);
  }
  
  return stores.slice(0, limit);
}

export async function searchMarketplaceProducts(params: {
  q?: string;
  minPrice?: number;
  maxPrice?: number;
  storeId?: string;
  limit?: number;
}): Promise<MarketplaceProduct[]> {
  const client = (getSupabaseAdminClient() || (await createSupabaseServerClient())) as any;
  if (!client) return [];

  try {
    let query = client.from('products').select(`
      ${MARKETPLACE_PRODUCT_SUMMARY_SELECT}
    `)
    .eq('active', true)
    .eq('show_in_catalog', true);

    if (params.q) {
      query = query.ilike('name', `%${params.q}%`);
    }
    if (params.storeId) {
      query = query.eq('user_id', params.storeId);
    }

    const limit = params.limit || 40;
    // Pega mais para podermos aplicar os filtros de preço e imagem
    const response = await query.order('created_at', { ascending: false }).limit(limit * 3);

    if (response.error || !response.data) return [];

    let rawProducts = response.data as any[];

    // Filter images
    rawProducts = rawProducts.filter(p => 
      (p.product_images && p.product_images.length > 0 && p.product_images.some((img: any) => img.image_url)) || 
      (p.image_url && p.image_url.trim() !== '')
    );

    // Filter prices
    if (params.minPrice !== undefined || params.maxPrice !== undefined) {
      rawProducts = rawProducts.filter(p => {
        let displayPrice = p.price;
        if (p.product_price_tiers && p.product_price_tiers.length > 0) {
          displayPrice = Math.min(...p.product_price_tiers.map((t: any) => t.unit_price));
        }
        if (params.minPrice !== undefined && displayPrice < params.minPrice) return false;
        if (params.maxPrice !== undefined && displayPrice > params.maxPrice) return false;
        return true;
      });
    }

    const products = rawProducts.slice(0, limit);

    const storeIds = [...new Set(products.map(p => p.user_id))];
    const stores = storeIds.length > 0 ? await listMarketplaceStores() : [];
    const storeMap = new Map(stores.map(s => [s.id, s]));

    return products.map(p => ({
      ...p,
      store: storeMap.get(p.user_id) || null,
      category: 'Produto'
    })) as MarketplaceProduct[];

  } catch (err) {
    console.error('Error searching products:', err);
    return [];
  }
}

export async function listMarketplaceStores(): Promise<MarketplaceStoreSummary[]> {
  const client = (getSupabaseAdminClient() || (await createSupabaseServerClient())) as unknown as QueryableClient;
  const profiles = await fetchProfiles(client);

  const validProfiles = profiles.filter(
    (profile): profile is RawProfile & { id: string; slug: string } =>
      typeof profile.id === "string" &&
      profile.id.trim().length > 0 &&
      typeof profile.slug === "string" &&
      profile.slug.trim().length > 0
  );

  const domains = await fetchCustomDomains(
    client,
    validProfiles.map((profile) => profile.id)
  );

  const domainByProfileId = new Map<string, string>();
  for (const domain of domains) {
    if (!domain.profile_id || !domain.domain || !domain.verified) continue;
    domainByProfileId.set(domain.profile_id, domain.domain);
  }

  return validProfiles
    .map((profile) => {
      const customDomain = domainByProfileId.get(profile.id) || null;
      const slug = profile.slug.trim();
      const name =
        profile.catalog_name?.trim() ||
        profile.store_name?.trim() ||
        slug;

      return {
        id: profile.id,
        slug,
        name,
        logoUrl: profile.catalog_logo_url?.trim() || profile.logo_url?.trim() || null,
        publicUrl: buildStorefrontUrl(slug, customDomain),
        customDomain,
        createdAt: profile.created_at || null,
      };
    })
    .sort((a, b) => a.name.localeCompare(b.name, "pt-BR", { sensitivity: "base" }));
}