/**
 * One-off / periodic repair (§84, §176): sales and listings whose grader was read but whose grade was
 * not (or that carry a grader token in the title the connector missed) were attached to the RAW
 * variant of their asset, polluting raw valuations and making graded asks look 80× "above RIV".
 *
 * `regradeRecords` re-parses the raw title with the current parser, moves the row to the proper
 * variant (creating it if needed; "grader · grade unknown" when the grade still cannot be read) and
 * marks the touched assets for revaluation by bumping `updated_at`/`created_at` watermarks the
 * valuation scheduler already watches. Nothing is deleted; every move is audited.
 */
import { and, eq, inArray, isNotNull, isNull, or, sql } from 'drizzle-orm';
import { assetVariants, listings, sales } from '@rareindex/database';
import { parseGradeFromTitle } from '@rareindex/taxonomy';
import { logger } from '@rareindex/shared';
import { db } from '../lib/db.ts';
import { auditMany } from '../lib/audit.ts';
import { ensureVariant } from './resolver.ts';

const log = logger.child({ component: 'regrade' });

export interface RegradeResult {
  scanned: number;
  moved: number;
  unknownGrade: number;
  unchanged: number;
  assets: number;
}

interface Row {
  id: string;
  assetId: string;
  variantId: string | null;
  grader: string | null;
  grade: string | null;
  rawTitle: string;
  variantKey: string | null;
  vGrader: string | null;
  vGrade: string | null;
  condition: string | null;
  completeness: string | null;
  sizeLabel: string | null;
}

async function candidates(kind: 'sale' | 'listing', limit: number, connectorId?: string): Promise<Row[]> {
  const t = kind === 'sale' ? sales : listings;
  const where = and(
    connectorId ? eq(t.connectorId, connectorId) : undefined,
    kind === 'listing' ? eq(listings.availability, 'available') : undefined,
    or(
      // grader known, grade unreadable → currently in the raw variant
      and(isNotNull(t.grader), isNull(t.grade)),
      // no grader recorded but the title carries a grader token (heuristic prefilter; parser decides)
      and(isNull(t.grader), sql`${t.rawTitle} ~* '\\m(PSA|BGS|BECKETT|CGC|SGC|CBCS|PGX|WATA|VGA|PCGS|NGC|ANACS|ICCS|PMG)\\M'`),
    ),
  );
  const rows = await db()
    .select({ id: t.id, assetId: t.assetId, variantId: t.variantId, grader: t.grader, grade: t.grade, rawTitle: t.rawTitle, variantKey: assetVariants.variantKey, vGrader: assetVariants.grader, vGrade: assetVariants.grade, condition: assetVariants.condition, completeness: assetVariants.completeness, sizeLabel: assetVariants.sizeLabel })
    .from(t)
    .leftJoin(assetVariants, eq(assetVariants.id, t.variantId))
    .where(where)
    .limit(limit);
  return rows as Row[];
}

/** Rows touched by an earlier regrade pass (audit_log action = regraded), to re-verify with the current parser. */
async function regradedRows(kind: 'sale' | 'listing', limit: number): Promise<Row[]> {
  const t = kind === 'sale' ? sales : listings;
  const rows = await db()
    .select({ id: t.id, assetId: t.assetId, variantId: t.variantId, grader: t.grader, grade: t.grade, rawTitle: t.rawTitle, variantKey: assetVariants.variantKey, vGrader: assetVariants.grader, vGrade: assetVariants.grade, condition: assetVariants.condition, completeness: assetVariants.completeness, sizeLabel: assetVariants.sizeLabel })
    .from(t)
    .leftJoin(assetVariants, eq(assetVariants.id, t.variantId))
    .where(sql`${t.id} in (select entity_id from audit_log where action = 'regraded' and entity_type = ${kind})`)
    .limit(limit);
  return rows as Row[];
}

export async function regradeRecords(opts: { kind?: 'sale' | 'listing' | 'both'; limit?: number; connectorId?: string; dryRun?: boolean; recheck?: boolean } = {}): Promise<RegradeResult> {
  const kinds: Array<'sale' | 'listing'> = opts.kind === 'sale' ? ['sale'] : opts.kind === 'listing' ? ['listing'] : ['sale', 'listing'];
  const limit = opts.limit ?? 50_000;
  const res: RegradeResult = { scanned: 0, moved: 0, unknownGrade: 0, unchanged: 0, assets: 0 };
  const touched = new Set<string>();
  for (const kind of kinds) {
    const rows = opts.recheck ? await regradedRows(kind, limit) : await candidates(kind, limit, opts.connectorId);
    res.scanned += rows.length;
    const audits: Array<{ entityType: string; entityId: string; action: string; reason: string; details: Record<string, unknown> }> = [];
    for (const r of rows) {
      const parsed = parseGradeFromTitle(r.rawTitle);
      const grader = r.grader && r.grader !== 'raw' ? r.grader : parsed.grader && parsed.grader !== 'raw' ? parsed.grader : null;
      if (!grader) {
        res.unchanged++;
        continue;
      }
      // grade recorded WITH a grader is trusted; a grade recorded without one is a condition value → title wins
      const titleGrade = parsed.grader === grader ? parsed.grade : null;
      const grade = opts.recheck ? (titleGrade ?? r.grade) : r.grader && r.grader !== 'raw' ? (r.grade ?? titleGrade) : titleGrade;
      const target = await ensureVariant(r.assetId, { grader, grade, qualifier: parsed.qualifier, certificationNumber: null }, { condition: r.condition, completeness: r.completeness }, r.sizeLabel);
      if (target.id === r.variantId && r.grader === grader && r.grade === grade) {
        res.unchanged++;
        continue;
      }
      if (!grade) res.unknownGrade++;
      res.moved++;
      touched.add(r.assetId);
      if (opts.dryRun) continue;
      const t = kind === 'sale' ? sales : listings;
      await db().update(t).set({ variantId: target.id, grader, grade, ...(kind === 'listing' ? { discountToRiv: null } : {}) }).where(eq(t.id, r.id));
      audits.push({ entityType: kind, entityId: r.id, action: 'regraded', reason: 'grade_reparsed_from_title', details: { from: r.variantKey, to: target.key, grader, grade, title: r.rawTitle } });
    }
    if (audits.length) await auditMany(audits);
    log.info({ kind, scanned: rows.length, moved: res.moved, dryRun: Boolean(opts.dryRun) }, 'regrade pass');
  }
  res.assets = touched.size;
  if (!opts.dryRun && touched.size) {
    // Make the valuation scheduler pick these assets up (it watches max(listings.updated_at) / sales.created_at).
    const ids = [...touched];
    for (let i = 0; i < ids.length; i += 1000) await db().update(listings).set({ updatedAt: new Date() }).where(and(inArray(listings.assetId, ids.slice(i, i + 1000)), eq(listings.availability, 'available')));
    // assets without an available listing: touch their stats watermark backwards so they re-qualify
    for (let i = 0; i < ids.length; i += 1000) await db().execute(sql`update asset_stats set updated_at = '1970-01-01' where asset_id in ${ids.slice(i, i + 1000)}`);
  }
  return res;
}