import { getSupabaseClient } from '@/lib/supabase';
import type {
  ProcurementDictionaryCreatePayload,
  ProcurementDictionaryEntry,
  ProcurementDictionaryDuplicateLookupPayload,
  ProcurementDictionaryUpdatePayload,
} from '@/types';

const TABLE = 'procurement_dictionary_entries';

export class ProcurementDictionaryDuplicateError extends Error {
  duplicate?: ProcurementDictionaryEntry;

  constructor(duplicate?: ProcurementDictionaryEntry) {
    super('已存在同术语、类型和语境的采购字典条目');
    this.name = 'ProcurementDictionaryDuplicateError';
    this.duplicate = duplicate;
  }
}

function normalizeDictionaryTerm(value: string): string {
  return value.trim().replace(/\s+/g, ' ').toUpperCase();
}

function normalizeDictionaryContext(value: string | undefined): string {
  return (value ?? '').trim().replace(/\s+/g, ' ').toLowerCase();
}

function isUniqueViolation(error: unknown): boolean {
  if (!error || typeof error !== 'object') return false;
  const maybeError = error as { code?: string; message?: string; details?: string };
  return maybeError.code === '23505'
    || /procurement_dictionary_entries_unique_term_context|duplicate key value/i.test(
      `${maybeError.message ?? ''} ${maybeError.details ?? ''}`,
    );
}

export async function fetchProcurementDictionaryEntries(): Promise<ProcurementDictionaryEntry[]> {
  const supabase = getSupabaseClient();
  const { data, error } = await supabase
    .from(TABLE)
    .select('*')
    .order('updated_at', { ascending: false });

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

export async function findProcurementDictionaryDuplicate(
  payload: ProcurementDictionaryDuplicateLookupPayload,
): Promise<ProcurementDictionaryEntry | null> {
  const supabase = getSupabaseClient();
  let query = supabase
    .from(TABLE)
    .select('*')
    .eq('normalized_term', normalizeDictionaryTerm(payload.term))
    .eq('term_type', payload.term_type)
    .eq('normalized_context', normalizeDictionaryContext(payload.product_context));

  if (payload.excludeId) {
    query = query.neq('id', payload.excludeId);
  }

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

  if (error) throw error;
  return data ?? null;
}

export async function createProcurementDictionaryEntry(
  payload: ProcurementDictionaryCreatePayload,
  createdBy?: string,
): Promise<ProcurementDictionaryEntry> {
  const supabase = getSupabaseClient();
  const { data, error } = await supabase
    .from(TABLE)
    .insert({
      term: payload.term.trim(),
      term_type: payload.term_type,
      product_context: payload.product_context?.trim() ?? '',
      interpretation: payload.interpretation.trim(),
      suggested_keyword: payload.suggested_keyword?.trim() ?? '',
      evidence: payload.evidence?.trim() ?? '',
      note: payload.note?.trim() ?? null,
      created_by: createdBy ?? null,
    })
    .select()
    .single();

  if (error) {
    if (isUniqueViolation(error)) {
      throw new ProcurementDictionaryDuplicateError();
    }
    throw error;
  }
  return data;
}

export async function updateProcurementDictionaryEntry(
  id: string,
  payload: ProcurementDictionaryUpdatePayload,
): Promise<ProcurementDictionaryEntry> {
  const supabase = getSupabaseClient();
  const { data, error } = await supabase
    .from(TABLE)
    .update({
      ...payload,
      ...(payload.term !== undefined && { term: payload.term.trim() }),
      ...(payload.product_context !== undefined && { product_context: payload.product_context.trim() }),
      ...(payload.interpretation !== undefined && { interpretation: payload.interpretation.trim() }),
      ...(payload.suggested_keyword !== undefined && { suggested_keyword: payload.suggested_keyword.trim() }),
      ...(payload.evidence !== undefined && { evidence: payload.evidence.trim() }),
      ...(payload.note !== undefined && { note: payload.note.trim() || null }),
      updated_at: new Date().toISOString(),
    })
    .eq('id', id)
    .select()
    .single();

  if (error) {
    if (isUniqueViolation(error)) {
      throw new ProcurementDictionaryDuplicateError();
    }
    throw error;
  }
  return data;
}

export async function deleteProcurementDictionaryEntry(id: string): Promise<void> {
  const supabase = getSupabaseClient();
  const { error } = await supabase
    .from(TABLE)
    .delete()
    .eq('id', id);

  if (error) throw error;
}
