import { BatchSummary, ProcurementBatch } from '@/types';
import { getSupabaseClient, sourceFilesBucket } from '@/lib/supabase';
import { createUniqueBatchId } from '@/lib/batchId';
import { fetchStandardizationSuccessSummary } from '@/services/standardizedService';
import { getCurrentDataAccessScope } from '@/services/dataAccessScope';

interface CreateBatchInput {
  name?: string;
}

export async function createBatch({ name }: CreateBatchInput = {}): Promise<ProcurementBatch> {
  const supabase = getSupabaseClient();
  const scope = await getCurrentDataAccessScope();

  if (!scope.userId) {
    throw new Error('当前未登录，无法创建批次。');
  }

  async function exists(batchId: string) {
    // 使用 SECURITY DEFINER 函数跨用户全局检查，保证批次编号唯一
    const { data, error } = await supabase.rpc('batch_id_exists', { p_batch_id: batchId });
    if (error) throw error;
    return data === true;
  }

  const batchId = await createUniqueBatchId(exists);

  const { data, error } = await supabase
    .from('procurement_batches')
    .insert({
      batch_id: batchId,
      name: name?.trim() || null,
      created_by: scope.userId,
    })
    .select('*')
    .single();

  if (error) throw error;
  return data as ProcurementBatch;
}

export async function updateBatchName(batchId: string, name: string): Promise<void> {
  const supabase = getSupabaseClient();
  const { error } = await supabase
    .from('procurement_batches')
    .update({ name: name.trim() || null, updated_at: new Date().toISOString() })
    .eq('batch_id', batchId);
  if (error) throw error;
}

export async function archiveBatches(batchIds: string[]): Promise<void> {
  const supabase = getSupabaseClient();
  const { error } = await supabase
    .from('procurement_batches')
    .update({ archived: true, updated_at: new Date().toISOString() })
    .in('batch_id', batchIds);
  if (error) throw error;
}

export async function unarchiveBatches(batchIds: string[]): Promise<void> {
  const supabase = getSupabaseClient();
  const { error } = await supabase
    .from('procurement_batches')
    .update({ archived: false, updated_at: new Date().toISOString() })
    .in('batch_id', batchIds);
  if (error) throw error;
}

export async function deleteBatch(batchId: string): Promise<void> {
  const supabase = getSupabaseClient();

  // Fetch source file paths before deleting records
  const { data: sfData } = await supabase
    .from('source_files')
    .select('storage_path')
    .eq('batch_id', batchId);

  // Delete related data from all associated tables in parallel
  // Note: standardization_task_items CASCADE from standardization_tasks
  // Note: chat_messages CASCADE from customer_service_chats
  await Promise.all([
    supabase.from('source_files').delete().eq('batch_id', batchId),
    supabase.from('procurement_materials').delete().eq('batch_id', batchId),
    supabase.from('contact_logs').delete().eq('batch_id', batchId),
    supabase.from('customer_service_chats').delete().eq('batch_id', batchId),
    supabase.from('keyword_process').delete().eq('batch_id', batchId),
    supabase.from('search_rounds').delete().eq('batch_id', batchId),
    supabase.from('best_match_images').delete().eq('batch_id', batchId),
    supabase.from('extended_best_matches').delete().eq('batch_id', batchId),
    supabase.from('image_search_results').delete().eq('batch_id', batchId),
    supabase.from('keyword_selections').delete().eq('batch_id', batchId),
    supabase.from('final_results').delete().eq('batch_id', batchId),
    supabase.from('final_result_products').delete().eq('batch_id', batchId),
    supabase.from('extended_best_match_records').delete().eq('batch_id', batchId),
    supabase.from('standardization_tasks').delete().eq('batch_id', batchId),
    supabase.from('standardized_results').delete().eq('batch_id', batchId),
    supabase.from('merchant_contacts').delete().eq('source_batch_id', batchId),
  ]);

  // Delete source files from storage bucket
  if (sfData && sfData.length > 0) {
    const paths = sfData.map((r: { storage_path: string }) => r.storage_path);
    await supabase.storage.from(sourceFilesBucket).remove(paths);
  }

  // Finally delete the batch record itself
  const { error } = await supabase
    .from('procurement_batches')
    .delete()
    .eq('batch_id', batchId);
  if (error) throw error;
}

export async function fetchAllBatchIds(): Promise<ProcurementBatch[]> {
  const supabase = getSupabaseClient();
  const scope = await getCurrentDataAccessScope();

  if (!scope.userId) {
    return [];
  }

  let query = supabase
    .from('procurement_batches')
    .select('*')
    .eq('archived', false)
    .order('created_at', { ascending: false });

  if (!scope.isAdmin) {
    query = query.eq('created_by', scope.userId);
  }

  const { data, error } = await query;

  if (error) throw error;
  return (data ?? []) as ProcurementBatch[];
}

export async function fetchBatches(): Promise<BatchSummary[]> {
  const supabase = getSupabaseClient();
  const scope = await getCurrentDataAccessScope();

  if (!scope.userId) {
    return [];
  }

  const standardizationSummaryPromise = fetchStandardizationSuccessSummary().catch((error) => {
    console.warn('Failed to fetch standardization success summary for batches:', error);
    return { totalCount: 0, countByBatchId: {} as Record<string, number> };
  });

  let batchesQuery = supabase
    .from('procurement_batches')
    .select('*')
    .order('created_at', { ascending: false });

  if (!scope.isAdmin) {
    batchesQuery = batchesQuery.eq('created_by', scope.userId);
  }

  const { data: batchData, error: batchError } = await batchesQuery;
  if (batchError) throw batchError;

  const batches = (batchData ?? []) as ProcurementBatch[];
  if (batches.length === 0) {
    return [];
  }

  const batchIds = batches.map((batch) => batch.batch_id);

  const [sourceFilesRes, materialsRes, finalProductsRes, contactRes, standardizationSummary] = await Promise.all([
    supabase
      .from('source_files')
      .select('batch_id, uploaded_at')
      .in('batch_id', batchIds),
    supabase
      .from('procurement_materials')
      .select('batch_id, created_at')
      .in('batch_id', batchIds),
    supabase
      .from('final_result_products')
      .select('batch_id, created_at')
      .in('batch_id', batchIds),
    supabase
      .from('contact_logs')
      .select('batch_id, created_at')
      .in('batch_id', batchIds),
    standardizationSummaryPromise,
  ]);

  if (sourceFilesRes.error) throw sourceFilesRes.error;
  if (materialsRes.error) throw materialsRes.error;
  if (finalProductsRes.error) throw finalProductsRes.error;
  if (contactRes.error) throw contactRes.error;
  const sourceFiles = sourceFilesRes.data ?? [];
  const materials = materialsRes.data ?? [];
  const finalResultProducts = finalProductsRes.data ?? [];
  const contactLogs = contactRes.data ?? [];

  return batches.map(b => {
    const sfs = sourceFiles.filter((file: { batch_id: string }) => file.batch_id === b.batch_id);
    const mats = materials.filter(m => m.batch_id === b.batch_id);
    const finalProducts = finalResultProducts.filter((r: { batch_id: string }) => r.batch_id === b.batch_id);
    const cls = contactLogs.filter(l => l.batch_id === b.batch_id);
    const effectiveSearchCount = finalProducts.length;
    const standardizedCount = standardizationSummary.countByBatchId[b.batch_id] ?? 0;

    let stage = 1;
    if (standardizedCount > 0) stage = 2;
    if (effectiveSearchCount > 0) stage = 3;
    if (cls.length > 0) stage = 4;

    return {
      batch_id: b.batch_id,
      name: b.name,
      archived: b.archived,
      created_at: b.created_at,
      materialsCount: mats.length,
      sourceFilesCount: sfs.length,
      standardizedCount,
      searchResultsCount: effectiveSearchCount,
      contactLogsCount: cls.length,
      stage,
    };
  });
}
