import { eq, sql } from 'drizzle-orm';
import { certificateSightings, certificates } from '@rareindex/database';
import { getGrader } from '@rareindex/taxonomy';
import { deterministicId, newId } from '@rareindex/shared';
import { db } from '../lib/db.ts';

/**
 * Certification-number tracking (SPEC §22). Every sale/listing/lot carrying a slab cert number is
 * recorded as a sighting of one (grader, cert) row. Over time the same cert appearing on several
 * marketplaces builds a provenance graph; cert_lookup connectors fill `verification`.
 */
export interface CertSighting {
  grader: string;
  certNumber: string;
  assetId: string | null;
  variantId: string | null;
  grade: string | null;
  qualifier?: string | null;
  kind: 'sale' | 'listing' | 'auction_lot' | 'lookup';
  targetId: string | null;
  sourceId: string;
  connectorId: string | null;
  sourceUrl: string | null;
  priceUsd: number | null;
  price?: number | null;
  currency?: string | null;
  observedAt: Date;
}

/** Normalise a raw cert string: digits and letters only, uppercase, no separators. Returns null when implausible. */
export function normalizeCertNumber(grader: string | null | undefined, raw: string | null | undefined): string | null {
  if (!raw) return null;
  const s = String(raw).trim().toUpperCase().replace(/^(CERT|CERT\.|#|NO\.?|№)\s*/i, '').replace(/[\s.\-_/]/g, '');
  if (!/^[A-Z0-9]{5,20}$/.test(s)) return null;
  // grader-specific sanity: PSA/SGC/CGC/BGS certs are numeric; PCGS/NGC are numeric (NGC may carry -NNN suffix already stripped)
  if (grader && /^(psa|sgc|cgc|bgs|pcgs|ngc|pmg|cbcs|tag|ace|wata|vga)$/.test(grader) && !/^\d{5,12}$/.test(s)) return null;
  return s;
}

export function verifyUrlFor(grader: string, cert: string): string | null {
  const g = getGrader(grader);
  if (!g?.verifyUrl) return null;
  return g.verifyUrl.includes('{cert}') ? g.verifyUrl.replace('{cert}', encodeURIComponent(cert)) : g.verifyUrl;
}

export async function recordCertificate(s: CertSighting): Promise<string | null> {
  const grader = s.grader.toLowerCase();
  const cert = normalizeCertNumber(grader, s.certNumber);
  if (!cert) return null;
  const id = deterministicId('cert', `${grader}|${cert}`);
  await db()
    .insert(certificates)
    .values({ id, grader, certNumber: cert, assetId: s.assetId, variantId: s.variantId, grade: s.grade, qualifier: s.qualifier ?? null, verifyUrl: verifyUrlFor(grader, cert), firstSeenAt: s.observedAt, lastSeenAt: s.observedAt, sightings: 1, sourceIds: [s.sourceId], lastSourceUrl: s.sourceUrl, lastPriceUsd: s.priceUsd })
    .onConflictDoUpdate({
      target: [certificates.grader, certificates.certNumber],
      set: {
        assetId: sql`coalesce(${certificates.assetId}, ${s.assetId})`,
        variantId: sql`coalesce(${certificates.variantId}, ${s.variantId})`,
        grade: sql`coalesce(${certificates.grade}, ${s.grade})`,
        firstSeenAt: sql`least(${certificates.firstSeenAt}, ${s.observedAt})`,
        lastSeenAt: sql`greatest(${certificates.lastSeenAt}, ${s.observedAt})`,
        sightings: sql`${certificates.sightings} + 1`,
        sourceIds: sql`(select array_agg(distinct x) from unnest(${certificates.sourceIds} || ${sql.raw(`'{${s.sourceId.replace(/[^a-z0-9_-]/gi, '')}}'::text[]`)}) x)`,
        lastSourceUrl: sql`case when ${s.observedAt} >= ${certificates.lastSeenAt} then ${s.sourceUrl} else ${certificates.lastSourceUrl} end`,
        lastPriceUsd: sql`case when ${s.priceUsd} is not null and ${s.observedAt} >= ${certificates.lastSeenAt} then ${s.priceUsd} else ${certificates.lastPriceUsd} end`,
      },
    });
  await db()
    .insert(certificateSightings)
    .values({ id: newId('event'), certificateId: id, kind: s.kind, targetId: s.targetId, sourceId: s.sourceId, connectorId: s.connectorId, sourceUrl: s.sourceUrl, priceUsd: s.priceUsd, currency: s.currency ?? null, price: s.price ?? null, observedAt: s.observedAt })
    .onConflictDoNothing();
  return id;
}

/** Cert history for an asset or a cert id (provenance timeline). */
export async function certificateTimeline(certificateId: string) {
  return db().select().from(certificateSightings).where(eq(certificateSightings.certificateId, certificateId)).orderBy(certificateSightings.observedAt);
}