// src/clients/clients.service.ts
import { ForbiddenException, Injectable, Logger } from '@nestjs/common';
import { InjectRepository, InjectDataSource } from '@nestjs/typeorm';
import { DataSource, Repository, Like } from 'typeorm';
import { ClientDataEntity } from './client-data.entity';
import { NewsurveSurvesEntity } from './newsurve-surves.entity';
import { ListClientsDto } from './dto/list-clients.dto';
import { ClientStandardEntity } from './client-standard.entity';
export type ClientSource = 'QRS' | 'TQS';
export type ClientRecordType = 'Client' | 'Surveillance';

/** Same roles that bypass ownership filtering in the previous-nc module. */
const VIEW_ALL_ROLES = ['super-admin', 'coordinator'];

export interface ClientRowDto {
  id: number;
  /** 🆕 REQUIRED by the frontend: ids collide between QRS and TQS */
  source: ClientSource;
  client_type: ClientRecordType;
  user_id: number;
  company_name: string | null;
  contact_primary: string | null;
  designationpr: string | null;
  telephone: string | null;
  mobile_no: string | null;
  email_id: string | null;
  Address: string | null;
  company_sector: string | null;
  standard_name: string | null;
  standard_names: string[];      // ← ADD
  /** Signed doc — only on clients__clientdatas rows (path + display name). */
  signed_docs?: string | null;
  signeddocsname?: string | null;
  status?: number | null;
  created_at?: Date | null;
  updated_at?: Date | null;
  [key: string]: any;
}

@Injectable()
export class ClientsService {
  private readonly logger = new Logger(ClientsService.name);

  constructor(
    @InjectRepository(ClientDataEntity, 'qrs')
    private readonly qrsClientRepo: Repository<ClientDataEntity>,

    @InjectRepository(NewsurveSurvesEntity, 'qrs')
    private readonly qrsSurveRepo: Repository<NewsurveSurvesEntity>,

    @InjectRepository(ClientStandardEntity, 'qrs')          // ← ADD decorator
    private readonly qrsStandardRepo: Repository<ClientStandardEntity>,

    @InjectRepository(ClientDataEntity, 'tqs')
    private readonly tqsClientRepo: Repository<ClientDataEntity>,

    @InjectRepository(NewsurveSurvesEntity, 'tqs')
    private readonly tqsSurveRepo: Repository<NewsurveSurvesEntity>,

    @InjectRepository(ClientStandardEntity, 'tqs')          // ← ADD decorator
    private readonly tqsStandardRepo: Repository<ClientStandardEntity>,

    @InjectDataSource('qrs')
    private readonly qrsDataSource: DataSource,

    @InjectDataSource('tqs')
    private readonly tqsDataSource: DataSource,

    @InjectDataSource('scheme_dbs')
    private readonly schemeDb: DataSource,
  ) { }

  // ═══════════════════════════════════════════════════════════════
  // ID MAPPING  (identical logic to PreviousNcService.resolveSourceUserId)
  // ═══════════════════════════════════════════════════════════════

  private sourceIdCache = new Map<string, number | null>();

  /**
   * Translate a scheme_dbs s user id → the legacy user id in QRS/TQS.
   * 1st try: EMAIL match (exact, case-insensitive).
   * 2nd try: FULL NAME match — used when the scheme account was
   * created with a different email than the legacy CRM (e.g. gmail
   * in scheme, @qrs.ae in legacy). The name must match EXACTLY ONE
   * legacy user; if two legacy users share the name, we map to
   * nobody rather than risk showing another user's clients.
   */
  async resolveSourceUserId(
    schemeUserId: number,
    source: ClientSource,
  ): Promise<number | null> {
    const cacheKey = `${source}:${schemeUserId}`;
    if (this.sourceIdCache.has(cacheKey)) {
      return this.sourceIdCache.get(cacheKey)!;
    }

    // 1. scheme_dbs s email for this user
    const schemeRows = await this.schemeDb.query(
      `SELECT email FROM users WHERE id = ? LIMIT 1`,
      [schemeUserId],
    );
    const email = (schemeRows[0]?.email || '').trim().toLowerCase();

    const ds = source === 'QRS' ? this.qrsDataSource : this.tqsDataSource;

    // 2. matching legacy user id by EMAIL (primary rule)
    if (email) {
      const match = await ds.query(
        `SELECT id FROM users WHERE LOWER(TRIM(email)) = ? LIMIT 1`,
        [email],
      );
      if (match[0]?.id != null) {
        const id = Number(match[0].id);
        this.sourceIdCache.set(cacheKey, id);
        this.logger.log(
          `[CLIENTS-IDMAP] scheme user ${schemeUserId} (${email}) → ${source} id=${id} (email match)`,
        );
        return id;
      }
    }

    // 3. 🔹 FALLBACK — matching legacy user by FULL NAME.
    // Scheme users table may store the name as first_name/last_name
    // or a single "name" column — try both defensively.
    let fullName = '';
    try {
      const r = await this.schemeDb.query(
        `SELECT first_name, last_name FROM users WHERE id = ? LIMIT 1`,
        [schemeUserId],
      );
      fullName = `${r[0]?.first_name ?? ''} ${r[0]?.last_name ?? ''}`;
    } catch {
      try {
        const r = await this.schemeDb.query(
          `SELECT name FROM users WHERE id = ? LIMIT 1`,
          [schemeUserId],
        );
        fullName = String(r[0]?.name ?? '');
      } catch {
        fullName = '';
      }
    }
    fullName = fullName.replace(/\s+/g, ' ').trim().toLowerCase();

    if (fullName) {
      // LIMIT 2 so we can detect ambiguity — require exactly ONE match
      const byName = await ds.query(
        `SELECT id FROM users
         WHERE LOWER(TRIM(CONCAT(COALESCE(first_name,''), ' ', COALESCE(last_name,'')))) = ?
         LIMIT 2`,
        [fullName],
      );
      if (byName.length === 1 && byName[0]?.id != null) {
        const id = Number(byName[0].id);
        this.sourceIdCache.set(cacheKey, id);
        this.logger.log(
          `[CLIENTS-IDMAP] scheme user ${schemeUserId} ("${fullName}") → ${source} id=${id} (NAME match — email differed)`,
        );
        return id;
      }
      if (byName.length > 1) {
        this.logger.warn(
          `[CLIENTS-IDMAP] scheme user ${schemeUserId} ("${fullName}") → ${source} AMBIGUOUS name (${byName.length}+ legacy users) — not mapped`,
        );
      }
    }

    this.sourceIdCache.set(cacheKey, null);
    this.logger.log(
      `[CLIENTS-IDMAP] scheme user ${schemeUserId} (${email || 'no-email'}) → ${source} id=NONE`,
    );
    return null;
  }

  /** Clear the cache after a user's email changes / a legacy account is linked. */
  clearIdMapCache() {
    this.sourceIdCache.clear();
  }

  // ═══════════════════════════════════════════════════════════════
  // ROLE-BASED ACCESS CONTROL
  // ═══════════════════════════════════════════════════════════════

  async userCanViewAll(userId: number): Promise<boolean> {
    if (!userId) return false;

    const result = await this.schemeDb.query(
      `
      SELECT LOWER(r.name) AS role_name
      FROM users u
      INNER JOIN user_roles ur ON ur.user_id = u.id
      INNER JOIN roles r      ON r.id = ur.role_id
      WHERE u.id = ?
      `,
      [userId],
    );

    const roleNames: string[] = (result as any[]).map((r) => r.role_name);
    const canViewAll = roleNames.some((rn) => VIEW_ALL_ROLES.includes(rn));

    this.logger.log(
      `[CLIENTS-PERM] User ${userId} roles=[${roleNames.join(',')}] → ${canViewAll ? 'VIEW ALL' : 'OWN ONLY'
      }`,
    );
    return canViewAll;
  }

  // ═══════════════════════════════════════════════════════════════
  // LISTING
  // ═══════════════════════════════════════════════════════════════

  private repoFor(source: ClientSource, type: ClientRecordType): Repository<any> {
    if (type === 'Client') {
      return source === 'QRS' ? this.qrsClientRepo : this.tqsClientRepo;
    }
    return source === 'QRS' ? this.qrsSurveRepo : this.tqsSurveRepo;
  }
  private standardCache = new Map<string, Map<number, string>>();

  private async getStandardMap(
    source: ClientSource,
  ): Promise<Map<number, string>> {
    const cached = this.standardCache.get(source);
    if (cached && cached.size > 0) return cached;   // only reuse a good map

    const repo = source === 'QRS' ? this.qrsStandardRepo : this.tqsStandardRepo;
    const map = new Map<number, string>();
    try {
      const all = await repo.find();
      for (const s of all) map.set(Number(s.id), s.name);
      this.logger.log(`[CLIENTS-STD] ${source}: cached ${map.size} standards`);
      if (map.size > 0) this.standardCache.set(source, map);
    } catch (err: any) {
      this.logger.error(`[CLIENTS-STD] ${source} load FAILED: ${err.message}`);
    }

    return map;
  }

  /** ["5","4","6"] → ["ISO 9001:2015", "ISO 14001:2015", ...] */
  private resolveStandardNames(
    raw: string | null | undefined,
    map: Map<number, string>,
  ): string[] {
    if (!raw) return [];
    let ids: number[] = [];
    try {
      const parsed = JSON.parse(String(raw));
      if (Array.isArray(parsed)) {
        ids = parsed.map((x) => Number(x)).filter((x) => !isNaN(x));
      }
    } catch {
      return [];
    }
    return ids.map((id) => map.get(id) ?? `#${id}`);
  }
  /**
   * Fetch the head of ONE source, already scoped to the current user.
   *
   * ⚠️ We take `page * limit` rows with skip = 0 — NOT `skip = (page-1)*limit`.
   * When you merge N sorted sources, the global page [start, start+limit) is
   * always contained in the top (start+limit) rows of each source. Skipping
   * inside each source separately (the old code) silently drops and repeats
   * rows from page 2 onwards.
   */
  private async fetchSlice(
    source: ClientSource,
    type: ClientRecordType,
    q: ListClientsDto,
    canViewAll: boolean,
    currentUserId: number,
  ): Promise<{ rows: ClientRowDto[]; total: number }> {
    const repo = this.repoFor(source, type);
    const qb = repo.createQueryBuilder('c').orderBy('c.id', 'DESC');

    // 🔒 ownership filter — the whole point of this change
    if (!canViewAll) {
      const sourceUid = await this.resolveSourceUserId(currentUserId, source);
      if (sourceUid == null) return { rows: [], total: 0 };
      qb.andWhere('c.user_id = :uid', { uid: sourceUid });
    }

    const search = (q.search || '').trim();
    if (search) {
      const term = `%${search.toLowerCase()}%`;
      qb.andWhere(
        `(
           LOWER(c.company_name)    LIKE :term
        OR LOWER(c.contact_primary) LIKE :term
        OR LOWER(c.email_id)        LIKE :term
        OR LOWER(c.trade_license)   LIKE :term
        )`,
        { term },
      );
    }

    const headCount = (q.page || 1) * (q.limit || 25);
    qb.take(headCount).skip(0);

    const [records, total] = await qb.getManyAndCount();
    if (!records.length) return { rows: [], total };

    const stdMap = await this.getStandardMap(source);

    const rows: ClientRowDto[] = records.map((r: any) => ({
      ...r,
      source,
      client_type: type,
      standard_names: this.resolveStandardNames(r.standard_name, stdMap),
    }));

    return { rows, total };
  }

  /**
   * One entry point for all three list endpoints.
   * Returns { rows, total, page, limit, totalPages }.
   */
  async listPaged(q: ListClientsDto, currentUserId: number) {
    const page = q.page || 1;
    const limit = q.limit || 25;
    const source = q.source || 'All';
    const type = q.type || 'All';

    const canViewAll = await this.userCanViewAll(currentUserId);

    this.logger.log(
      `[CLIENTS-LIST] user=${currentUserId} page=${page} limit=${limit} source=${source} type=${type} search="${q.search ?? ''}" viewAll=${canViewAll}`,
    );

    const wantQrs = source === 'All' || source === 'QRS';
    const wantTqs = source === 'All' || source === 'TQS';
    const wantClients = type === 'All' || type === 'Clients' || type === 'Client';
    const wantSurves = type === 'All' || type === 'Surveillance';

    const jobs: Promise<{ rows: ClientRowDto[]; total: number }>[] = [];
    const empty = Promise.resolve({ rows: [] as ClientRowDto[], total: 0 });

    jobs.push(
      wantQrs && wantClients
        ? this.fetchSlice('QRS', 'Client', q, canViewAll, currentUserId)
        : empty,
    );
    jobs.push(
      wantTqs && wantClients
        ? this.fetchSlice('TQS', 'Client', q, canViewAll, currentUserId)
        : empty,
    );
    jobs.push(
      wantQrs && wantSurves
        ? this.fetchSlice('QRS', 'Surveillance', q, canViewAll, currentUserId)
        : empty,
    );
    jobs.push(
      wantTqs && wantSurves
        ? this.fetchSlice('TQS', 'Surveillance', q, canViewAll, currentUserId)
        : empty,
    );

    const slices = await Promise.all(jobs);

    const merged = slices
      .flatMap((s) => s.rows)
      .sort((a, b) => {
        if (b.id !== a.id) return b.id - a.id;
        return a.source.localeCompare(b.source); // deterministic tie-break
      });

    const total = slices.reduce((sum, s) => sum + s.total, 0);
    const totalPages = Math.ceil(total / limit) || 1;

    const start = (page - 1) * limit;
    const rows = merged.slice(start, start + limit);

    return { rows, total, page, limit, totalPages };
  }

  // ── Backwards-compatible wrappers for the existing three endpoints ──

  async getClientsPagedFiltered(q: ListClientsDto, currentUserId: number) {
    return this.listPaged({ ...q, type: 'Clients' }, currentUserId);
  }

  async getSurvesPagedFiltered(q: ListClientsDto, currentUserId: number) {
    return this.listPaged({ ...q, type: 'Surveillance' }, currentUserId);
  }

  async getAllCombinedPaged(q: ListClientsDto, currentUserId: number) {
    return this.listPaged({ ...q, type: 'All' }, currentUserId);
  }
  // ═══════════════════════════════════════════════════════════════
  // 🔒 SCOPED CLIENT SEARCH  (audit-request client dropdown)
  // PARTIAL, case/symbol-insensitive match on the company name —
  // "al geemi", "AL GEEMI" and "Al Geemi Contracting Co LLC" all
  // find the client. Client master rows only (no Surveillance).
  // Ownership via listPaged → fetchSlice (identical logic to
  // previous-nc: own rows only, super-admin / coordinator see all).
  // ═══════════════════════════════════════════════════════════════

  /** Same normalize rule as the frontend clients.mappers. */
  private normalizeCompanyKey(name?: string | null): string {
    if (!name) return '';
    let s = String(name).toUpperCase();
    s = s.split(/[—–]/)[0];                              // drop "— address" tail
    s = s.replace(/P\.?\s*O\.?\s*BOX[\s:.]*\d*/g, ' ');
    s = s.replace(/\./g, '');                            // "L.L.C" === "LLC"
    s = s.replace(/[^A-Z0-9&\s]/g, ' ');
    return s.replace(/\s+/g, ' ').trim();
  }

  async searchExact(name: string, source: ClientSource, currentUserId: number) {
    const qKey = this.normalizeCompanyKey(name);
    if (!qKey) return { rows: [] };

    // 🔹 Does this scheme user exist in the legacy QRS/TQS DB?
    //   Mapped user   → PARTIAL search over the rows they may see.
    //   Unmapped user → EXACT FULL NAME required: nothing is visible
    //   until the complete company name (as stored on the Trade
    //   License record) is typed. This stops scheme-only accounts
    //   (e.g. admins with no legacy login) from browsing client
    //   names letter by letter.
    const mappedId = await this.resolveSourceUserId(currentUserId, source);
    const exactOnly = mappedId == null;

    // Char-separated LIKE pattern ("A%L%G%E…") so typing "CO LLC" still
    // finds "Co. L.L.C" in SQL; the strict filter below removes over-matches.
    const loose = qKey.replace(/\s+/g, '').split('').join('%');

    const res = await this.listPaged(
      // 🔹 type 'Client' → ONLY the Client master tables are queried;
      // Surveillance rows never appear in this dropdown.
      { page: 1, limit: 100, source, type: 'Client', search: loose } as ListClientsDto,
      currentUserId,
    );

    // 🔹 Mapped users: the typed text just has to appear inside the
    // normalized name (case / dots / symbols / "— address" ignored).
    // Unmapped users: the normalized name must EQUAL the typed text.
    const matches = (res.rows ?? []).filter((r) => {
      const key = this.normalizeCompanyKey(r.company_name);
      return exactOnly ? key === qKey : key.includes(qKey);
    });
    if (!matches.length) return { rows: [] };

    // Rank: exact name first, then names STARTING with the typed text,
    // then names merely containing it — and within the same rank the
    // "cleanest" row first (no "— address" tail, no junk symbols).
    const rank = (r: any): number => {
      const key = this.normalizeCompanyKey(r.company_name);
      if (key === qKey) return 0;
      if (key.startsWith(qKey)) return 1;
      return 2;
    };
    const score = (r: any): number => {
      let s = 0;
      const n = String(r.company_name ?? '');
      if (!/[—–]/.test(n)) s += 30;
      if (!/[./\\·,]/.test(n)) s += 20;
      s -= Math.min(n.length / 10, 10);
      return s;
    };
    matches.sort((a, b) => rank(a) - rank(b) || score(b) - score(a));

    // Flat ranked list. The frontend groups rows per company: the best
    // row of each company is selectable, its other rows show DISABLED
    // ("↳ … duplicate"). Ownership scoping (same logic as previous-nc)
    // already applied inside listPaged → fetchSlice.
    return { rows: matches };
  }
  // ═══════════════════════════════════════════════════════════════
  // DETAILS  (also scoped — otherwise anyone can read any id)
  // ═══════════════════════════════════════════════════════════════

  async getClientDetails(
    id: number,
    source: ClientSource,
    type: ClientRecordType,
    currentUserId: number,
  ) {
    const record: any = await this.repoFor(source, type).findOne({
      where: { id },
    });
    if (!record) return null;

    const canViewAll = await this.userCanViewAll(currentUserId);
    if (!canViewAll) {
      const sourceUid = await this.resolveSourceUserId(currentUserId, source);
      if (sourceUid == null || Number(record.user_id) !== sourceUid) {
        throw new ForbiddenException(
          'This client belongs to another user.',
        );
      }
    }

    // Map old CRM columns → audit request form fields
    return {
      client_ref_id: record.id,
      client_group: source, // maps to ClientGroup enum
      company_source: type, // 'Client' | 'Surveillance'
      company_name: record.company_name ?? '',

      // primary auditee / contact person
      auditee_name: record.contact_primary ?? '',
      auditee_designation: record.designationpr ?? '',
      auditee_contact: record.mobile_no || record.telephone || '',
      auditee_email: record.email_id ?? '',

      // secondary contact (if you show it as an extra auditee row)
      auditees: record.contact_second
        ? [
          {
            name: record.contact_second,
            designation: record.designationsec ?? '',
          },
        ]
        : [],

      // scheduling / scope hints
      location: record.Address ?? '',
      standard_name: record.standard_name ?? '',
      standard_names: this.resolveStandardNames(
        record.standard_name,
        await this.getStandardMap(source),
      ),
      accreditation: '',

      meta: {
        company_sector: record.company_sector ?? null,
        trade_license: record.trade_license ?? null,
        vat_no: record.vat_no ?? null,
        employee_no: record.employee_no ?? null,
        site_no: record.site_no ?? null,
        head_office: record.head_office ?? null,
        contact_finance: record.contact_finance ?? null,
        email_finance: record.email_finance ?? null,
        signed_docs: record.signed_docs ?? null,
        signeddocsname: record.signeddocsname ?? null,
      },
    };
  }

  // ═══════════════════════════════════════════════════════════════
  // DEBUG — confirm the mapping works for the logged-in user
  // GET /clients/my-scope
  // ═══════════════════════════════════════════════════════════════

  async describeScope(currentUserId: number) {
    const [canViewAll, qrsId, tqsId] = await Promise.all([
      this.userCanViewAll(currentUserId),
      this.resolveSourceUserId(currentUserId, 'QRS'),
      this.resolveSourceUserId(currentUserId, 'TQS'),
    ]);

    return {
      scheme_user_id: currentUserId,
      can_view_all: canViewAll,
      qrs_user_id: qrsId,
      tqs_user_id: tqsId,
      note:
        !canViewAll && qrsId == null && tqsId == null
          ? 'No legacy account matched this email in either DB — the list will be empty.'
          : undefined,
    };
  }
}