// 2026-11-11 — Main CRM (crm.qrsyst.com, scheme_dbs) → scheme.qrs.ae (certification_db)
//
// 1. STANDARDS, both ways: a standard that exists in one database and not in the other (matched by NAME,
//    never by id — the ids differ between the two databases) is added to the other one. Nothing is
//    deleted or renamed. Soft-deleted standards count as "existing" so they are not re-created.
// 2. CLIENTS: every company that has an audit in the Audit Schedule is created in certification_db.companies
//    if it is not there yet (matched by look-alike name, so "ABC LLC" = "ABC L.L.C" is not duplicated).
//    Contact details come from the company, else from its audit request (real auditee).
//    Created rows carry legacy_source = 'crm_schedule', legacy_ref_id = scheme_dbs company id.
//    Their standards (from the schedule rows + the company) are linked in certification_db.company_standards.
//
// Runs: 20 s after start-up, every 3 minutes, and a few seconds after a schedule / row is created
// (requestCertSync() is called from AuditSchedulesService). Set CERT_SYNC_DISABLED=1 to switch it off.
import { Injectable, Logger, OnApplicationBootstrap, OnModuleDestroy } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import { looseKey, dupKey } from './duplicate-index';
import { fromAuditRequest, fromCompany, merge, clean } from '../../company-documents/auditee';

let instance: CertSyncService | null = null;
let timer: NodeJS.Timeout | null = null;

/** ask for a sync soon (debounced 5 s) — safe to call from anywhere, even inside a transaction */
export function requestCertSync(): void {
  if (!instance) return;
  if (timer) clearTimeout(timer);
  timer = setTimeout(() => {
    timer = null;
    instance?.run('schedule').catch(() => undefined);
  }, 5000);
}

/** standard name → match key ("ISO/IEC 20000-1:2018" = "IS0 20000-1:2018" = "20000-1:2018") */
export function stdKey(name: string): string {
  return String(name || '')
    .toLowerCase()
    .replace(/\bis0\b/g, 'iso')
    .replace(/\b(iso|iec)\b/g, '')
    .replace(/[^a-z0-9]/g, '');
}

type Std = { id: number; name: string; title: string | null; deleted_at: any };

@Injectable()
export class CertSyncService implements OnApplicationBootstrap, OnModuleDestroy {
  private readonly logger = new Logger('CertSync');
  private running: Promise<any> | null = null;
  private interval: NodeJS.Timeout | null = null;
  last: any = null;

  constructor(
    @InjectDataSource('scheme_dbs') private readonly crm: DataSource,
    @InjectDataSource('certification_db') private readonly cert: DataSource,
  ) {}

  onApplicationBootstrap() {
    if (process.env.CERT_SYNC_DISABLED === '1') {
      this.logger.warn('disabled (CERT_SYNC_DISABLED=1)');
      return;
    }
    instance = this;
    setTimeout(() => this.run('startup').catch(() => undefined), 20_000);
    this.interval = setInterval(() => this.run('interval').catch(() => undefined), 3 * 60_000);
  }
  onModuleDestroy() {
    if (this.interval) clearInterval(this.interval);
    instance = null;
  }

  /** one sync pass (never two at the same time) */
  async run(reason: string, opts: { dryRun?: boolean; all?: boolean } = {}) {
    if (this.running && !opts.dryRun) return this.running;
    const p = (async () => {
      const t = Date.now();
      try {
        const standards = await this.syncStandards(!!opts.dryRun);
        const clients = await this.syncClients(!!opts.dryRun, !!opts.all);
        const res = { at: new Date().toISOString(), reason, dryRun: !!opts.dryRun, all: !!opts.all, ms: Date.now() - t, standards, clients };
        if (!opts.dryRun) this.last = res;
        if (standards.added_to_cert.length || standards.added_to_crm.length || clients.created.length || clients.linked_standards) {
          this.logger.log(`[${reason}] ${JSON.stringify({ ...res, clients: { ...clients, created: clients.created.length } })}`);
        }
        return res;
      } catch (e: any) {
        this.logger.error(`[${reason}] sync failed: ${e?.message}`);
        if (!opts.dryRun) this.last = { at: new Date().toISOString(), reason, error: e?.message };
        throw e;
      }
    })();
    if (!opts.dryRun) this.running = p.finally(() => (this.running = null));
    return p;
  }

  // ───────────────────────── standards ─────────────────────────
  private async loadStd(db: DataSource): Promise<Std[]> {
    return db.query(`SELECT id, name, title, deleted_at FROM standards`);
  }

  /** does `list` already hold a standard matching `s`? (same key, or one key starts with the other + same title) */
  private findStd(list: Std[], s: Std): Std | undefined {
    const k = stdKey(s.name);
    if (!k) return undefined;
    const exact = list.find((x) => stdKey(x.name) === k);
    if (exact) return exact;
    const t = String(s.title || '').trim().toLowerCase();
    return list.find((x) => {
      const xk = stdKey(x.name);
      return !!t && xk && (xk.startsWith(k) || k.startsWith(xk)) && String(x.title || '').trim().toLowerCase() === t;
    });
  }

  async syncStandards(dryRun: boolean) {
    const [crm, cert] = await Promise.all([this.loadStd(this.crm), this.loadStd(this.cert)]);
    const added_to_cert: string[] = [];
    const added_to_crm: string[] = [];
    const copy = async (db: DataSource, s: Std) => {
      const name = String(s.name).replace(/\s*\n\s*/g, ' ').trim();
      if (!dryRun) {
        await db.query(`INSERT INTO standards (name, title, created_at, updated_at) VALUES (?, ?, NOW(6), NOW(6))`, [name, s.title ?? null]);
      }
      return name;
    };
    for (const s of crm.filter((x) => !x.deleted_at)) {
      if (!this.findStd(cert, s)) {
        added_to_cert.push(await copy(this.cert, s));
        cert.push({ ...s, id: -1 });
      }
    }
    for (const s of cert.filter((x) => !x.deleted_at && x.id !== -1)) {
      if (!this.findStd(crm, s)) {
        added_to_crm.push(await copy(this.crm, s));
        crm.push({ ...s, id: -1 });
      }
    }
    return { added_to_cert, added_to_crm };
  }

  // ───────────────────────── clients ─────────────────────────
  private async nextCompanyCode(name: string): Promise<string> {
    // same format as scheme.qrs.ae: <3 initials>-<YYYY>-<MM>-<6-digit sequence from company_sequence id 1>
    return this.cert.transaction(async (m) => {
      const seq = (await m.query(`SELECT last_number FROM company_sequence WHERE id = 1 FOR UPDATE`))[0];
      if (!seq) throw new Error('certification_db.company_sequence row 1 missing');
      let n = Number(seq.last_number);
      let initials = name
        .split(' ')
        .filter((w) => w.length > 2)
        .map((w) => w[0].toUpperCase())
        .join('')
        .substring(0, 3);
      if (initials.length < 3) initials = name.replace(/[^A-Za-z]/g, '').substring(0, 3).toUpperCase();
      initials = initials.replace(/[^A-Z0-9]/g, 'X') || 'CMP';
      const now = new Date();
      const ym = `${now.getFullYear()}-${String(now.getMonth() + 1).padStart(2, '0')}`;
      let code = '';
      for (;;) {
        n = n >= 999999 ? 1 : n + 1;
        code = `${initials}-${ym}-${String(n).padStart(6, '0')}`;
        const ex = await m.query(`SELECT 1 FROM companies WHERE company_code = ? LIMIT 1`, [code]);
        if (!ex.length) break;
      }
      await m.query(`UPDATE company_sequence SET last_number = ? WHERE id = 1`, [n]);
      return code;
    });
  }

  /**
   * Automatic passes only look at audit schedule rows created since CERT_SYNC_SINCE (default: the day this
   * went live, 2026-11-11). The older backlog is pushed once by an admin: POST /companies/cert-sync?all=1.
   */
  async syncClients(dryRun: boolean, all = false) {
    const since = process.env.CERT_SYNC_SINCE || '2026-11-11 00:00:00';
    // companies with at least one scheduled audit
    const scheduled: any[] = await this.crm.query(
      all
        ? `SELECT c.* FROM companies c WHERE c.id IN (SELECT DISTINCT company_id FROM audit_schedule_rows)`
        : `SELECT c.* FROM companies c WHERE c.id IN (SELECT DISTINCT company_id FROM audit_schedule_rows WHERE created_at >= ?)`,
      all ? [] : [since],
    );
    const certCos: { id: number; name: string; legacy_source: string | null; legacy_ref_id: number | null }[] = await this.cert.query(
      `SELECT id, name, legacy_source, legacy_ref_id FROM companies`,
    );
    const byRef = new Map<number, number>();
    const byKey = new Map<string, number>();
    for (const c of certCos) {
      if (c.legacy_source === 'crm_schedule' && c.legacy_ref_id) byRef.set(Number(c.legacy_ref_id), c.id);
      const k1 = dupKey(c.name);
      const k2 = looseKey(c.name);
      const k0 = String(c.name || '').trim().toLowerCase();
      if (k0) byKey.set(`x:${k0}`, c.id);
      if (k1.length >= 4) byKey.set(`n:${k1}`, c.id);
      if (k2.length >= 5) byKey.set(`l:${k2}`, c.id);
    }
    const remember = (name: string, id: number) => {
      const k1 = dupKey(name);
      const k2 = looseKey(name);
      byKey.set(`x:${name.trim().toLowerCase()}`, id);
      if (k1.length >= 4) byKey.set(`n:${k1}`, id);
      if (k2.length >= 5) byKey.set(`l:${k2}`, id);
    };
    const findCert = (c: any): number | undefined => {
      const r = byRef.get(Number(c.id));
      if (r) return r;
      const k0 = String(c.name || '').replace(/\s+/g, ' ').trim().toLowerCase();
      const k1 = dupKey(c.name);
      const k2 = looseKey(c.name);
      return byKey.get(`x:${k0}`) ?? (k1.length >= 4 ? byKey.get(`n:${k1}`) : undefined) ?? (k2.length >= 5 ? byKey.get(`l:${k2}`) : undefined);
    };

    const missing = scheduled.filter((c) => c.name && !findCert(c));
    const created: { crm_id: number; cert_id?: number; name: string; code?: string }[] = [];
    let linked = 0;
    if (!missing.length) return { scheduled: scheduled.length, missing: 0, created, linked_standards: 0 };

    const ids = missing.map((c) => Number(c.id));
    // latest audit request per company → real auditee details
    const ars: any[] = await this.crm.query(
      `SELECT ar.* FROM audit_requests ar
         JOIN (SELECT company_id, MAX(id) mid FROM audit_requests WHERE company_id IN (?) GROUP BY company_id) x ON x.mid = ar.id`,
      [ids],
    );
    const arBy = new Map<number, any>(ars.map((a) => [Number(a.company_id), a]));
    // standards per company: schedule rows + company_standards (scheme_dbs ids → names)
    const stdRows: { company_id: number; name: string; title: string | null }[] = await this.crm.query(
      `SELECT x.company_id, s.name, s.title FROM (
          SELECT r.company_id, rs.standard_id FROM audit_schedule_rows r
            JOIN audit_schedule_row_standards rs ON rs.row_id = r.id WHERE r.company_id IN (?)
          UNION SELECT company_id, standard_id FROM company_standards WHERE company_id IN (?)
        ) x JOIN standards s ON s.id = x.standard_id`,
      [ids, ids],
    );
    const certStd = await this.loadStd(this.cert);

    const cut = (v: any, n: number) => String(v ?? '').slice(0, n);
    for (const c of missing) {
      const a = merge(fromCompany(c), fromAuditRequest(arBy.get(Number(c.id))));
      const name = String(c.name).replace(/\s+/g, ' ').trim().slice(0, 255); // no line breaks / double spaces
      // a second CRM copy of a company created earlier in this same pass → skip (no duplicate in scheme.qrs.ae)
      if (findCert({ id: c.id, name })) continue;
      if (dryRun) {
        created.push({ crm_id: Number(c.id), name });
        remember(name, -Number(c.id));
        continue;
      }
      try {
        const code = await this.nextCompanyCode(name);
        const r = await this.cert.query(
          `INSERT INTO companies (company_code, name, trade_license_no, normalized_name, client_group, address, city,
              contact_person, designation, email, mobile, telephone, validity, certification_body, accreditation,
              scope_of_work, legacy_source, legacy_ref_id, created_at, updated_at)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'crm_schedule', ?, NOW(6), NOW(6))`,
          [
            code,
            name,
            clean(c.trade_license_no) ? cut(c.trade_license_no, 100) : null,
            cut(c.normalized_name || dupKey(name), 255) || null,
            c.client_group ? cut(c.client_group, 50) : null,
            a.address || clean(c.address) || '',
            cut(clean(c.city), 100),
            cut(a.contact_person, 100),
            cut(a.designation, 255),
            cut(a.email, 150),
            cut(a.mobile, 20),
            a.telephone ? cut(a.telephone, 20) : null,
            cut(clean(c.validity), 50),
            cut(clean(c.certification_body), 100),
            cut(clean(c.accreditation), 100),
            a.scope_of_work || '',
            Number(c.id),
          ],
        );
        const certId = Number(r.insertId);
        created.push({ crm_id: Number(c.id), cert_id: certId, name, code });
        byRef.set(Number(c.id), certId);
        remember(name, certId);
        // link standards (by name)
        const want = new Set<number>();
        for (const s of stdRows.filter((x) => Number(x.company_id) === Number(c.id))) {
          const m = this.findStd(certStd, { id: 0, name: s.name, title: s.title, deleted_at: null });
          if (m && m.id > 0) want.add(m.id);
        }
        for (const sid of want) {
          const u = await this.cert.query(`INSERT IGNORE INTO company_standards (company_id, standard_id) VALUES (?, ?)`, [certId, sid]);
          linked += u?.affectedRows ?? 0;
        }
      } catch (e: any) {
        // e.g. the exact name already exists (UNIQUE) — skip, try again next pass
        this.logger.warn(`[CLIENT] "${name}" (#${c.id}) not created: ${e?.message}`);
      }
    }
    return { scheduled: scheduled.length, missing: missing.length, created, linked_standards: linked };
  }
}