import sharp from 'sharp';
import { existsSync } from 'fs';
import { resolve, sep } from 'path';
import { db, getListing, getListings } from './db.mjs';
import { calculateFees } from './fees.mjs';

const PLATFORM_PROFILES = {
  'ebay-de': { name: 'eBay', titleMin: 12, titleMax: 80, descriptionMin: 80, recommendedPhotos: 4 },
  vinted: { name: 'Vinted', titleMin: 8, titleMax: 100, descriptionMin: 45, recommendedPhotos: 3 },
  'ebay-ka': { name: 'Kleinanzeigen', titleMin: 10, titleMax: 65, descriptionMin: 60, recommendedPhotos: 4 },
  etsy: { name: 'Etsy', titleMin: 20, titleMax: 140, descriptionMin: 120, recommendedPhotos: 5 },
};

function clamp(value, min, max) { return Math.max(min, Math.min(max, value)); }
function normalizeText(value) { return String(value || '').replace(/\s+/g, ' ').trim(); }
function wordTokens(value) {
  return new Set(normalizeText(value).toLowerCase().replace(/[^a-z0-9äöüß ]/gi, ' ').split(/\s+/).filter(token => token.length > 2));
}
function jaccard(a, b) {
  const left = wordTokens(a); const right = wordTokens(b);
  if (!left.size || !right.size) return 0;
  let intersection = 0;
  for (const token of left) if (right.has(token)) intersection += 1;
  return intersection / (left.size + right.size - intersection);
}
function gradeFor(score) {
  if (score >= 92) return 'A';
  if (score >= 82) return 'B';
  if (score >= 70) return 'C';
  if (score >= 55) return 'D';
  return 'E';
}
function issue({ code, severity = 'recommendation', category, title, detail, points = 0, field = null, action = null }) {
  return { code, severity, category, title, detail, points, field, action };
}
function safeParseJson(value, fallback) {
  try { const parsed = JSON.parse(value); return parsed ?? fallback; } catch { return fallback; }
}

function findDuplicates(listing, allListings) {
  const candidates = [];
  const title = normalizeText(listing.title);
  for (const other of allListings) {
    if (Number(other.id) === Number(listing.id)) continue;
    const similarity = jaccard(title, other.title);
    const sameSku = listing.sku && other.sku && String(listing.sku).toLowerCase() === String(other.sku).toLowerCase();
    if (sameSku || similarity >= 0.72) {
      candidates.push({ id: other.id, title: other.title, sku: other.sku, similarity: Math.round(similarity * 100), same_sku: !!sameSku });
    }
  }
  return candidates.sort((a, b) => Number(b.same_sku) - Number(a.same_sku) || b.similarity - a.similarity).slice(0, 5);
}

function inspectCore(listing, allListings) {
  const issues = [];
  const title = normalizeText(listing.title);
  const description = normalizeText(listing.description);
  const photos = Array.isArray(listing.photos) ? listing.photos.filter(Boolean) : [];
  const tags = Array.isArray(listing.tags) ? listing.tags.filter(Boolean) : [];
  const profile = PLATFORM_PROFILES[listing.platform] || { name: listing.platform || 'Allgemein', titleMin: 10, titleMax: 100, descriptionMin: 60, recommendedPhotos: 3 };

  if (!title) issues.push(issue({ code: 'missing_title', severity: 'error', category: 'Inhalt', title: 'Titel fehlt', detail: 'Ohne Titel kann der Artikel nicht zuverlässig veröffentlicht werden.', points: 20, field: 'title' }));
  else {
    if (title.length < profile.titleMin) issues.push(issue({ code: 'title_too_short', severity: 'warning', category: 'Inhalt', title: 'Titel ist sehr kurz', detail: `Für das ${profile.name}-Profil werden mindestens ${profile.titleMin} Zeichen empfohlen.`, points: 6, field: 'title', action: 'ai_title' }));
    if (title.length > profile.titleMax) issues.push(issue({ code: 'title_too_long', severity: 'error', category: 'Plattform', title: 'Titel ist zu lang', detail: `Das aktive Vendoo-Profil sieht höchstens ${profile.titleMax} Zeichen vor.`, points: 10, field: 'title', action: 'ai_title' }));
    if (/\b(l@@k|mega|hammer|wow|!!!)\b/i.test(title) || /!{2,}/.test(title)) issues.push(issue({ code: 'title_spam', severity: 'recommendation', category: 'Inhalt', title: 'Titel wirkt werblich', detail: 'Sachliche Titel sind leichter lesbar und wirken vertrauenswürdiger.', points: 2, field: 'title', action: 'ai_title' }));
  }

  if (!description) issues.push(issue({ code: 'missing_description', severity: 'error', category: 'Inhalt', title: 'Beschreibung fehlt', detail: 'Zustand, Lieferumfang und relevante Besonderheiten sollten beschrieben werden.', points: 16, field: 'description', action: 'ai_description' }));
  else {
    if (description.length < profile.descriptionMin) issues.push(issue({ code: 'description_too_short', severity: 'warning', category: 'Inhalt', title: 'Beschreibung ist knapp', detail: `Für das ${profile.name}-Profil werden mindestens ${profile.descriptionMin} Zeichen empfohlen.`, points: 6, field: 'description', action: 'ai_description' }));
    if (!/[.!?]$/.test(description)) issues.push(issue({ code: 'description_unfinished', severity: 'recommendation', category: 'Inhalt', title: 'Beschreibung wirkt unvollständig', detail: 'Ein sauberer Abschluss verbessert die Lesbarkeit.', points: 1, field: 'description' }));
  }

  if (!photos.length) issues.push(issue({ code: 'missing_photos', severity: 'error', category: 'Bilder', title: 'Keine Bilder vorhanden', detail: 'Mindestens ein aussagekräftiges Originalbild ist erforderlich.', points: 20, field: 'photos' }));
  else if (photos.length < profile.recommendedPhotos) issues.push(issue({ code: 'few_photos', severity: 'warning', category: 'Bilder', title: 'Wenige Bilder', detail: `${profile.recommendedPhotos} oder mehr Perspektiven werden für dieses Profil empfohlen.`, points: clamp((profile.recommendedPhotos - photos.length) * 2, 2, 7), field: 'photos' }));

  if (!(Number(listing.price) > 0)) issues.push(issue({ code: 'missing_price', severity: 'error', category: 'Preis', title: 'Preis fehlt', detail: 'Ein positiver Verkaufspreis ist erforderlich.', points: 12, field: 'price' }));
  if (!normalizeText(listing.condition)) issues.push(issue({ code: 'missing_condition', severity: 'error', category: 'Metadaten', title: 'Zustand fehlt', detail: 'Der Zustand ist für Käufer und Plattformformulare wichtig.', points: 8, field: 'condition' }));
  if (!normalizeText(listing.category)) issues.push(issue({ code: 'missing_category', severity: 'error', category: 'Metadaten', title: 'Kategorie fehlt', detail: 'Eine passende Kategorie verbessert Auffindbarkeit und Publishing.', points: 8, field: 'category' }));
  if (!normalizeText(listing.brand)) issues.push(issue({ code: 'missing_brand', severity: 'recommendation', category: 'Metadaten', title: 'Marke nicht angegeben', detail: 'Marke ergänzen, sofern sie am Produkt sicher erkennbar ist.', points: 3, field: 'brand' }));
  if (!normalizeText(listing.color)) issues.push(issue({ code: 'missing_color', severity: 'recommendation', category: 'Metadaten', title: 'Farbe nicht angegeben', detail: 'Eine Farbangabe erleichtert Suche und Filterung.', points: 2, field: 'color' }));
  if (!tags.length) issues.push(issue({ code: 'missing_tags', severity: 'recommendation', category: 'Reichweite', title: 'Keine Suchbegriffe', detail: 'Passende Tags oder Suchbegriffe können die Auffindbarkeit verbessern.', points: 3, field: 'tags', action: 'ai_tags' }));
  if (!normalizeText(listing.sku)) issues.push(issue({ code: 'missing_sku', severity: 'warning', category: 'Organisation', title: 'SKU fehlt', detail: 'Eine eindeutige Artikelnummer erleichtert Lager und Publishing.', points: 4, field: 'sku' }));
  if (!normalizeText(listing.storage_location)) issues.push(issue({ code: 'missing_storage', severity: 'recommendation', category: 'Organisation', title: 'Kein Lagerort', detail: 'Ein Lagerort verhindert Suchaufwand nach dem Verkauf.', points: 2, field: 'storage_location' }));

  const duplicates = findDuplicates(listing, allListings);
  if (duplicates.some(item => item.same_sku)) issues.push(issue({ code: 'duplicate_sku', severity: 'error', category: 'Duplikate', title: 'Doppelte SKU möglich', detail: 'Mindestens ein anderer Artikel verwendet dieselbe SKU.', points: 10, field: 'sku' }));
  else if (duplicates.length) issues.push(issue({ code: 'possible_duplicate', severity: 'warning', category: 'Duplikate', title: 'Ähnlicher Artikel gefunden', detail: 'Titel und Produktdaten ähneln mindestens einem bestehenden Artikel.', points: 5 }));

  const penalty = issues.reduce((sum, entry) => sum + Number(entry.points || 0), 0);
  const score = clamp(100 - penalty, 0, 100);
  const blockers = issues.filter(entry => entry.severity === 'error');
  return {
    listing_id: listing.id,
    score,
    grade: gradeFor(score),
    blockers: blockers.length,
    warnings: issues.filter(entry => entry.severity === 'warning').length,
    recommendations: issues.filter(entry => entry.severity === 'recommendation').length,
    ready: blockers.length === 0 && score >= 70,
    platform: { id: listing.platform || '', name: profile.name, profile },
    issues,
    duplicates,
    facts: {
      title_length: title.length,
      description_length: description.length,
      photo_count: photos.length,
      tag_count: tags.length,
    },
    listing: {
      id: listing.id, title: listing.title, sku: listing.sku, platform: listing.platform, status: listing.status,
      price: listing.price, photo: photos[0] || null, updated_at: listing.updated_at, created_at: listing.created_at,
    },
  };
}

async function inspectImages(report, listing, uploadRoot) {
  const details = [];
  for (const photo of (listing.photos || []).slice(0, 12)) {
    const relative = String(photo || '').replace(/^[/\\]+/, '');
    const absolute = resolve(uploadRoot, relative);
    if (absolute === uploadRoot || !absolute.startsWith(uploadRoot + sep) || !existsSync(absolute)) {
      details.push({ path: relative, missing: true });
      report.issues.push(issue({ code: 'photo_missing_file', severity: 'error', category: 'Bilder', title: 'Bilddatei fehlt', detail: `Die Datei ${relative || 'unbekannt'} wurde nicht gefunden.`, points: 8, field: 'photos' }));
      continue;
    }
    try {
      const meta = await sharp(absolute).metadata();
      const width = Number(meta.width || 0); const height = Number(meta.height || 0);
      const megapixels = Math.round((width * height / 1_000_000) * 10) / 10;
      details.push({ path: relative, width, height, megapixels, format: meta.format || '' });
      if (Math.min(width, height) < 700) report.issues.push(issue({ code: 'photo_low_resolution', severity: 'warning', category: 'Bilder', title: 'Niedrige Bildauflösung', detail: `${relative}: ${width} × ${height} px.`, points: 3, field: 'photos' }));
      if (width === height && width < 1000) report.issues.push(issue({ code: 'photo_small_square', severity: 'recommendation', category: 'Bilder', title: 'Kleines quadratisches Bild', detail: `${relative} ist quadratisch, aber relativ klein.`, points: 1, field: 'photos' }));
    } catch {
      details.push({ path: relative, unreadable: true });
      report.issues.push(issue({ code: 'photo_unreadable', severity: 'error', category: 'Bilder', title: 'Bild nicht lesbar', detail: `${relative} konnte nicht analysiert werden.`, points: 6, field: 'photos' }));
    }
  }
  report.images = details;
  const penalty = report.issues.reduce((sum, entry) => sum + Number(entry.points || 0), 0);
  report.score = clamp(100 - penalty, 0, 100);
  report.grade = gradeFor(report.score);
  report.blockers = report.issues.filter(entry => entry.severity === 'error').length;
  report.warnings = report.issues.filter(entry => entry.severity === 'warning').length;
  report.recommendations = report.issues.filter(entry => entry.severity === 'recommendation').length;
  report.ready = report.blockers === 0 && report.score >= 70;
  return report;
}

function persistReport(report) {
  const result = db.prepare(`INSERT INTO quality_reports (listing_id, score, grade, ready, blockers, warnings, recommendations, report_json, analyzed_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))`).run(
    report.listing_id, report.score, report.grade, report.ready ? 1 : 0, report.blockers, report.warnings, report.recommendations, JSON.stringify(report)
  );
  return Number(result.lastInsertRowid);
}

export function getLatestQualityReport(listingId) {
  const row = db.prepare('SELECT * FROM quality_reports WHERE listing_id = ? ORDER BY id DESC LIMIT 1').get(listingId);
  if (!row) return null;
  const report = safeParseJson(row.report_json, null);
  return report ? { ...report, report_id: row.id, analyzed_at: row.analyzed_at } : null;
}

export async function analyzeListingQuality(listingId, { uploadRoot, persist = true } = {}) {
  const listing = getListing(listingId);
  if (!listing) return null;
  const allListings = getListings();
  let report = inspectCore(listing, allListings);
  if (uploadRoot) report = await inspectImages(report, listing, uploadRoot);
  const fee = calculateFees(listing.platform, Number(listing.price) || 0);
  report.financials = { price: Number(listing.price) || 0, fees: fee.fees || 0, estimated_net: fee.net || 0, note: fee.note || '', breakdown: fee.breakdown || '' };
  report.analyzed_at = new Date().toISOString();
  if (persist) report.report_id = persistReport(report);
  return report;
}

export function listQualityCenter({ query = '', status = 'all', grade = 'all', page = 1, pageSize = 10 } = {}) {
  const allListings = getListings();
  const allReports = allListings.map(listing => {
    const latest = getLatestQualityReport(listing.id);
    const listingChangedAt = Date.parse(String(listing.updated_at || listing.created_at || '').replace(' ', 'T') + 'Z') || 0;
    const analyzedAt = Date.parse(String(latest?.analyzed_at || '').replace(' ', 'T') + 'Z') || 0;
    return latest && analyzedAt >= listingChangedAt ? latest : inspectCore(listing, allListings);
  });
  let reports = [...allReports];
  const q = normalizeText(query).toLowerCase();
  if (q) reports = reports.filter(item => [item.listing?.title, item.listing?.sku, item.platform?.name].some(value => String(value || '').toLowerCase().includes(q)));
  if (status === 'ready') reports = reports.filter(item => item.ready);
  if (status === 'blocked') reports = reports.filter(item => item.blockers > 0);
  if (status === 'review') reports = reports.filter(item => !item.ready && item.blockers === 0);
  if (grade !== 'all') reports = reports.filter(item => item.grade === grade);
  reports.sort((a, b) => a.score - b.score || String(b.listing?.updated_at || b.listing?.created_at || '').localeCompare(String(a.listing?.updated_at || a.listing?.created_at || '')));
  const safeSize = [5, 10, 15, 25].includes(Number(pageSize)) ? Number(pageSize) : 10;
  const total = reports.length;
  const totalPages = Math.max(1, Math.ceil(total / safeSize));
  const safePage = clamp(Number(page) || 1, 1, totalPages);
  const items = reports.slice((safePage - 1) * safeSize, safePage * safeSize);
  const summary = {
    total: allReports.length,
    ready: allReports.filter(report => report.ready).length,
    blocked: allReports.filter(report => report.blockers > 0).length,
    average_score: allReports.length ? Math.round(allReports.reduce((sum, report) => sum + report.score, 0) / allReports.length) : 0,
  };
  return { items, pagination: { page: safePage, page_size: safeSize, total, total_pages: totalPages }, summary };
}

export function getQualityHistory(listingId, limit = 12) {
  return db.prepare('SELECT id, score, grade, ready, blockers, warnings, recommendations, analyzed_at FROM quality_reports WHERE listing_id = ? ORDER BY id DESC LIMIT ?').all(listingId, Math.min(Math.max(Number(limit) || 12, 1), 50));
}

export function compareQualityReports(listingId) {
  const rows = db.prepare('SELECT id, score, grade, analyzed_at FROM quality_reports WHERE listing_id = ? ORDER BY id DESC LIMIT 2').all(listingId);
  return { latest: rows[0] || null, previous: rows[1] || null, delta: rows.length > 1 ? rows[0].score - rows[1].score : 0 };
}