import 'dotenv/config';

import { createClient } from '@supabase/supabase-js';
import { existsSync } from 'node:fs';
import { readFile } from 'node:fs/promises';
import path from 'node:path';

const BUCKET = 'screenshots';

function parseArgs(argv) {
  const args = {
    username: '',
    taskIds: [],
    dryRun: false,
    force: false,
    limit: 0,
  };

  for (const arg of argv) {
    if (arg === '--dry-run') {
      args.dryRun = true;
      continue;
    }
    if (arg === '--force') {
      args.force = true;
      continue;
    }
    if (arg.startsWith('--username=')) {
      args.username = arg.slice('--username='.length).trim();
      continue;
    }
    if (arg.startsWith('--task-id=')) {
      args.taskIds.push(arg.slice('--task-id='.length).trim());
      continue;
    }
    if (arg.startsWith('--limit=')) {
      args.limit = Number(arg.slice('--limit='.length)) || 0;
    }
  }

  return args;
}

function normalizeEnvValue(value) {
  return typeof value === 'string' ? value.trim() : '';
}

function getSupabaseJwtRole(token) {
  const parts = token.split('.');
  if (parts.length < 2) return '';

  try {
    const payload = JSON.parse(Buffer.from(parts[1], 'base64url').toString('utf8'));
    return typeof payload.role === 'string' ? payload.role : '';
  } catch {
    return '';
  }
}

function resolveSupabaseKey() {
  const candidates = [
    process.env.SUPABASE_SERVICE_ROLE_KEY,
    process.env.VITE_SUPABASE_SERVICE_ROLE_KEY,
    process.env.SUPABASE_service_key,
    process.env.SUPABASE_ANON_KEY,
    process.env.VITE_SUPABASE_ANON_KEY,
  ].map(normalizeEnvValue).filter(Boolean);

  return candidates.find((candidate) => getSupabaseJwtRole(candidate) === 'service_role') ?? candidates[0] ?? '';
}

function requireSupabaseClient() {
  const url = normalizeEnvValue(process.env.SUPABASE_URL) || normalizeEnvValue(process.env.VITE_SUPABASE_URL);
  const key = resolveSupabaseKey();
  if (!url || !key) {
    throw new Error('缺少 SUPABASE_URL/VITE_SUPABASE_URL 或 Supabase key');
  }

  return createClient(url, key, { auth: { persistSession: false } });
}

function normalizeText(value) {
  return String(value ?? '').replace(/\s+/g, ' ').trim();
}

function normalizeOfferUrlForLookup(value) {
  const raw = normalizeText(value);
  if (!raw) return '';

  try {
    const parsed = new URL(raw.startsWith('//') ? `https:${raw}` : raw);
    const offerIdMatch = parsed.pathname.match(/\/offer\/(\d+)\.html/i);
    const offerId = offerIdMatch?.[1] || parsed.searchParams.get('offerId') || parsed.searchParams.get('id') || '';
    if (offerId) {
      return `https://detail.1688.com/offer/${offerId}.html`;
    }
    parsed.search = '';
    parsed.hash = '';
    return parsed.toString();
  } catch {
    return raw.split(/[?#]/, 1)[0] ?? raw;
  }
}

function looksLikeGeneratedResourceTitle(value) {
  const text = normalizeText(value);
  return /^[A-Za-z0-9_-]{8,}(?:\|[A-Za-z0-9_.-]+){2,}$/.test(text);
}

function sanitizeSearchResultTitle(value) {
  return normalizeText(value)
    .replace(/\b(?:https?:)?\/\/\S+/gi, '')
    .replace(/\s*[|｜]\s*aet[_-][^|｜]+(?:[|｜].*)?$/i, '')
    .trim();
}

function isUnsafeProductTitle(value) {
  const title = normalizeText(value);
  if (!title) return true;
  if (looksLikeGeneratedResourceTitle(title)) return true;
  if (/^(?:https?:)?\/\//i.test(title)) return true;
  if (/^data:[a-z]+\/[a-z0-9.+-]+;base64,/i.test(title)) return true;
  return false;
}

function safeProductTitleCandidate(value) {
  if (isUnsafeProductTitle(value)) return '';
  const title = sanitizeSearchResultTitle(value);
  return title && !isUnsafeProductTitle(title) ? title : '';
}

function resolveProductTitle(product, fallbackLookup) {
  const sourceProduct = product.source_product && typeof product.source_product === 'object'
    ? product.source_product
    : {};
  const directTitle = safeProductTitleCandidate(product.title);
  if (directTitle) return directTitle;

  const inlineFallback = safeProductTitleCandidate(product['title-text'])
    || safeProductTitleCandidate(sourceProduct.title);
  if (inlineFallback) return inlineFallback;

  const offerUrl = normalizeOfferUrlForLookup(product.offer_url ?? product.offerUrl ?? product.url);
  return fallbackLookup.get(offerUrl) ?? '';
}

function buildTitleFallbackLookup(products) {
  const lookup = new Map();

  for (const product of products) {
    const offerUrl = normalizeOfferUrlForLookup(product.offer_url ?? product.offerUrl ?? product.url);
    if (!offerUrl || lookup.has(offerUrl)) continue;

    const sourceProduct = product.source_product && typeof product.source_product === 'object'
      ? product.source_product
      : {};
    const title = safeProductTitleCandidate(product.title)
      || safeProductTitleCandidate(product['title-text'])
      || safeProductTitleCandidate(sourceProduct.title);

    if (title) {
      lookup.set(offerUrl, title);
    }
  }

  return lookup;
}

function mimeFromExt(filePath) {
  const ext = path.extname(filePath).toLowerCase();
  if (ext === '.webp') return 'image/webp';
  if (ext === '.png') return 'image/png';
  if (ext === '.gif') return 'image/gif';
  return 'image/jpeg';
}

function materialPrefix(materialId) {
  return `material-${materialId}`;
}

async function uploadScreenshot(supabase, filePath, storagePath, dryRun) {
  if (!filePath) return '';
  if (!existsSync(filePath)) {
    throw new Error(`截图文件不存在: ${filePath}`);
  }

  if (!dryRun) {
    const fileBuffer = await readFile(filePath);
    const { error } = await supabase.storage.from(BUCKET).upload(storagePath, fileBuffer, {
      contentType: mimeFromExt(filePath),
      upsert: true,
    });
    if (error) {
      throw new Error(`[storage] 上传失败 (${storagePath}): ${error.message}`);
    }
  }

  const { data } = supabase.storage.from(BUCKET).getPublicUrl(storagePath);
  return data.publicUrl;
}

function getFinalSelectedProductPaths(task) {
  const paths = task.result?.searchArtifacts?.finalSelectedProducts;
  return Array.isArray(paths) ? paths.map(String).filter(Boolean) : [];
}

async function fetchTasks(supabase, args) {
  let query = supabase
    .from('search_tasks')
    .select('id, batch_id, account_id, status, result, created_at, updated_at, finished_at')
    .order('created_at', { ascending: true });

  if (args.taskIds.length > 0) {
    query = query.in('id', args.taskIds);
  } else if (args.username) {
    const { data: profile, error: profileError } = await supabase
      .from('profiles')
      .select('id, username')
      .eq('username', args.username)
      .maybeSingle();
    if (profileError) throw profileError;
    if (!profile) throw new Error(`未找到用户 ${args.username}`);

    const { data: accounts, error: accountError } = await supabase
      .from('procurement_accounts_1688')
      .select('id')
      .eq('user_id', profile.id);
    if (accountError) throw accountError;

    const accountIds = (accounts ?? []).map((row) => row.id).filter(Boolean);
    if (accountIds.length === 0) return [];
    query = query.in('account_id', accountIds);
  }

  query = query.eq('status', 'completed');
  if (args.limit > 0) {
    query = query.limit(args.limit);
  }

  const { data, error } = await query;
  if (error) throw error;
  return (data ?? []).filter((task) => getFinalSelectedProductPaths(task).length > 0);
}

async function fetchTaskItemsByTaskId(supabase, taskIds) {
  if (taskIds.length === 0) return new Map();

  const { data, error } = await supabase
    .from('search_task_items')
    .select('task_id, batch_id, material_id, material_name')
    .in('task_id', taskIds)
    .order('sort_order', { ascending: true });
  if (error) throw error;

  const lookup = new Map();
  for (const item of data ?? []) {
    const rows = lookup.get(item.task_id) ?? [];
    rows.push(item);
    lookup.set(item.task_id, rows);
  }
  return lookup;
}

async function fetchExistingProductCounts(supabase, taskIds) {
  if (taskIds.length === 0) return new Map();

  const { data, error } = await supabase
    .from('final_result_products')
    .select('task_id')
    .in('task_id', taskIds);
  if (error) throw error;

  const counts = new Map();
  for (const row of data ?? []) {
    const taskId = String(row.task_id ?? '');
    counts.set(taskId, (counts.get(taskId) ?? 0) + 1);
  }
  return counts;
}

async function buildProductRows(supabase, params) {
  const { task, item, products, finishedAt, dryRun } = params;
  const titleFallbackLookup = buildTitleFallbackLookup(products);
  const rows = [];

  for (const [index, product] of products.entries()) {
    const rank = Number(product.stage_rank ?? product.index ?? index + 1);
    const source = String(product.source ?? 'fixed_runtime_search');
    const mainKeyword = String(product.main_keyword ?? product.input ?? '');
    const screenshotLocalPath = String(product.offer_screenshot_path ?? '');
    const screenshotExt = path.extname(screenshotLocalPath) || '.jpg';
    const stageOrder = Number(product.stage_order ?? 0);
    let offerScreenshotUrl = '';

    if (screenshotLocalPath) {
      offerScreenshotUrl = await uploadScreenshot(
        supabase,
        screenshotLocalPath,
        `${materialPrefix(item.material_id)}/offer-screenshots/${stageOrder > 0 ? `stage-${stageOrder}-rank-${rank}` : `final-rank-${rank}`}${screenshotExt}`,
        dryRun,
      );
    }

    rows.push({
      task_id: task.id,
      batch_id: item.batch_id,
      material_id: item.material_id,
      source,
      main_keyword: mainKeyword,
      stage_order: stageOrder > 0 ? stageOrder : null,
      stage_label: String(product.stage_label ?? ''),
      stage_kind: String(product.stage_kind ?? ''),
      stage_main_keyword: String(product.stage_main_keyword ?? mainKeyword),
      stage_keyword_hit_rate: product.stage_keyword_hit_rate != null ? Number(product.stage_keyword_hit_rate) : null,
      is_placeholder: Boolean(product.is_placeholder),
      placeholder_reason: String(product.placeholder_reason ?? ''),
      finished_at: finishedAt,
      rank,
      title: resolveProductTitle(product, titleFallbackLookup),
      offer_url: String(product.offer_url ?? product.url ?? ''),
      image_url: String(product.image_url ?? ''),
      merchant_name: String(product.merchant_name ?? ''),
      merchant_url: String(product.merchant_url ?? ''),
      wangwang_url: String(product.wangwang_url ?? ''),
      offer_screenshot_url: offerScreenshotUrl,
      contact_page_url: String(product.contact_page_url ?? ''),
      detected_contacts: Array.isArray(product.detected_contacts) ? product.detected_contacts : [],
      score: product.score != null ? Number(product.score) : null,
      is_match: product.is_match == null ? null : Boolean(product.is_match),
      reason: String(product.reason ?? ''),
      has_phone_contact: Boolean(product.has_phone_contact),
      qualification_status: String(product.qualification_status ?? (product.is_placeholder ? 'placeholder' : 'qualified')),
      contact_collection_reason: String(product.contact_collection_reason ?? ''),
    });
  }

  return rows;
}

async function backfillTask(supabase, task, item, finalSelectedPath, dryRun) {
  const raw = await readFile(finalSelectedPath, 'utf8');
  const products = JSON.parse(raw);
  if (!Array.isArray(products)) {
    throw new Error(`final-selected-products 不是数组: ${finalSelectedPath}`);
  }

  const finishedAt = task.finished_at || task.updated_at || new Date().toISOString();
  const source = String(products[0]?.source ?? 'fixed_runtime_search');
  const mainKeyword = String(products[0]?.main_keyword ?? products[0]?.input ?? '');
  const finalResultRow = {
    task_id: task.id,
    batch_id: item.batch_id,
    material_id: item.material_id,
    source,
    main_keyword: mainKeyword,
    product_count: products.length,
    products,
    finished_at: finishedAt,
  };
  const productRows = await buildProductRows(supabase, {
    task,
    item,
    products,
    finishedAt,
    dryRun,
  });

  if (!dryRun) {
    const { error: finalError } = await supabase
      .from('final_results')
      .upsert(finalResultRow, { onConflict: 'batch_id,material_id' });
    if (finalError) {
      throw new Error(`[final_results] ${task.id} 写入失败: ${finalError.message}`);
    }

    const { error: deleteError } = await supabase
      .from('final_result_products')
      .delete()
      .eq('batch_id', item.batch_id)
      .eq('material_id', item.material_id);
    if (deleteError) {
      throw new Error(`[final_result_products] ${task.id} 清理旧记录失败: ${deleteError.message}`);
    }

    if (productRows.length > 0) {
      const { error: insertError } = await supabase
        .from('final_result_products')
        .insert(productRows);
      if (insertError) {
        throw new Error(`[final_result_products] ${task.id} 写入失败: ${insertError.message}`);
      }
    }
  }

  return {
    taskId: task.id,
    batchId: item.batch_id,
    materialId: item.material_id,
    productCount: products.length,
    rowCount: productRows.length,
  };
}

async function main() {
  const args = parseArgs(process.argv.slice(2));
  const supabase = requireSupabaseClient();
  const tasks = await fetchTasks(supabase, args);
  const taskIds = tasks.map((task) => task.id);
  const itemsByTaskId = await fetchTaskItemsByTaskId(supabase, taskIds);
  const existingCounts = await fetchExistingProductCounts(supabase, taskIds);

  let skipped = 0;
  let backfilled = 0;

  for (const task of tasks) {
    const finalPaths = getFinalSelectedProductPaths(task).filter((filePath) => existsSync(filePath));
    const items = itemsByTaskId.get(task.id) ?? [];
    const existingCount = existingCounts.get(task.id) ?? 0;

    if (existingCount > 0 && !args.force) {
      skipped += 1;
      console.log(`[skip] ${task.id}: final_result_products 已有 ${existingCount} 行`);
      continue;
    }
    if (finalPaths.length === 0) {
      skipped += 1;
      console.log(`[skip] ${task.id}: final-selected-products.json 不存在`);
      continue;
    }
    if (items.length !== 1 || finalPaths.length !== 1) {
      skipped += 1;
      console.log(`[skip] ${task.id}: 暂不处理多物料/多产物任务 items=${items.length} finalFiles=${finalPaths.length}`);
      continue;
    }

    const summary = await backfillTask(supabase, task, items[0], finalPaths[0], args.dryRun);
    backfilled += 1;
    console.log(`[ok] ${summary.taskId}: batch=${summary.batchId} material=${summary.materialId} products=${summary.productCount} rows=${summary.rowCount}${args.dryRun ? ' dry-run' : ''}`);
  }

  console.log(`done: backfilled=${backfilled} skipped=${skipped} dryRun=${args.dryRun}`);
}

main().catch((error) => {
  console.error(error);
  process.exitCode = 1;
});
