import mysql from "mysql2/promise";
import { Sequelize } from "sequelize";
import { PostService } from "./post.service";
import { CategoryService } from "./category.service";
import { PostDao } from "../dao/post.dao";
import { CommentDao } from "../dao/comment.dao";
import { UserDao } from "../dao/user.dao";
import { RoleDao } from "../dao/role.dao";
import { AttachmentService } from "./attachment.service";
import { RoleService } from "./role.service";
import { AppError } from "../../utils/errors";
import { Common } from "../../utils/common";
import { POST, COMMENT, CATEGORY } from "../config/constants";
import Models from "../models";

interface WpDbConfig {
  host: string;
  port: number;
  user: string;
  password: string;
  database: string;
  prefix: string;
}

interface WpPostRow {
  ID: number;
  post_author: number;
  post_date: string;
  post_date_gmt: string;
  post_content: string;
  post_title: string;
  post_excerpt: string;
  post_status: string;
  post_name: string;
  post_type: string;
  post_password?: string;
}

interface WpPostMetaRow {
  post_id: number;
  meta_key: string;
  meta_value: string;
}

interface WpUserRow {
  ID: number;
  user_login: string;
  user_nicename: string;
  user_email: string;
  display_name: string;
  user_url: string;
}

interface WpUserMetaRow {
  user_id: number;
  meta_key: string;
  meta_value: string;
}

interface WpCommentRow {
  comment_ID: number;
  comment_post_ID: number;
  comment_parent_id: number;
  comment_content: string;
  comment_approved: string;
  user_id: number;
  comment_date: string;
  comment_author_email: string;
  comment_author: string;
  comment_type?: string;
}

interface WpCommentMetaRow {
  comment_id: number;
  meta_key: string;
  meta_value: string;
}

interface WpTermRelationshipRow {
  object_id: number;
  term_taxonomy_id: number;
  taxonomy: string;
  term_id: number;
  name: string;
  slug: string;
  parent: number;
  description: string;
}

interface WpAttachmentRow {
  ID: number;
  post_parent: number;
  guid: string;
  post_mime_type: string;
}

interface WpCategoryTermInfo {
  termId: number;
  parentTermId: number;
  name: string;
  description: string | null;
  categoryId: number;
}

interface EnsureCategoryResult {
  id: number | null;
  code: string | null;
  created: boolean;
}

const REMOTE_RANK_MAP: Record<number, string> = {
  1: "master",
  2: "chief_officer",
  3: "second_officer",
  4: "third_officer",
  5: "radio_officer",
  6: "deck_cadet",
  7: "trainee_cadet",
  8: "bosun",
  9: "deck_fitter",
  10: "ab",
  11: "os",
  12: "gp",
  13: "crane_operator",
  14: "junior_officer",
  15: "pumpman",
  16: "fitters_oilers",
  17: "fitter",
  18: "chief_engineer",
  19: "second_engineer",
  20: "third_engineer",
  21: "fourth_engineer",
  22: "fifth_engineer",
  23: "electrical_engineer",
  24: "electrical_officer",
  25: "electro_technical_officer",
  26: "junior_engineer",
  27: "asst_electrical_officer",
  28: "trainee_marine_engineer",
  29: "reefer_engineer",
  30: "reefer_mechanic",
  31: "gas_engineer",
  32: "engine_fitter",
  33: "motorman",
  34: "wiper",
  35: "travel_fitter",
  36: "travel_wiper",
  37: "pielstik_engineer",
  44: "cadetnautical_science",
  45: "other_senior_engineering_officers",
  46: "deck_rating",
  47: "seaman",
  48: "a_b",
  49: "ordinary_seaman",
  50: "second_officer_ncv",
  51: "other_engineering_officers",
  52: "other_decknautical_officers",
  53: "trainee_deck_rating",
  54: "watchkeeping_engineer_officer_ncv_watchkeeping_engineer_officer_ncv_oicew",
  55: "watch_keeping_deck_officer_oinw_or_third_officer",
  56: "watch_keeping_deck_officer_ncv_oinw",
  57: "other_senior_decknautical_officers",
  58: "trainee_engine_rating",
  59: "watch_keeping_motor_man",
  60: "electricalelectronics_officer",
  61: "assitant_electricalelectronics_officer",
  62: "deck_watchkeeping_rating",
  63: "chief_engineer_ncv",
  64: "oiler",
  65: "engine_rating",
};

export class WordpressImportService {
  private accountId: number | null;
  private userId: number | null;
  private language: string;
  private scope: string[] | null;
  private config: userConfig | null;

  private postService: PostService | null = null;
  private categoryService: CategoryService | null = null;
  private postDao: PostDao | null = null;
  private commentDao: CommentDao | null = null;
  private userDao: UserDao | null = null;
  private roleDao: RoleDao | null = null;
  private attachmentService: AttachmentService | null = null;
  private roleService: RoleService | null = null;

  private categoryCodeCache: Map<string, number> = new Map();
  private tagCodeCache: Map<string, number> = new Map();
  private termTaxonomyToCategoryId: Map<number, number> = new Map();
  private termTaxonomyToCategoryCode: Map<number, string> = new Map();
  private termTaxonomyToTagId: Map<number, number> = new Map();
  private categoryTermById: Map<number, WpCategoryTermInfo> = new Map();
  private categoryParentApplied: Set<number> = new Set();
  private taxonomyLoaded: boolean = false;
  private authorRoleId: number | null = null;
  private wpAttachmentIdCache: Map<number, number> = new Map();
  private wpAttachmentUrlCache: Map<string, number> = new Map();
  private wpAttachmentPublicUrlCache: Map<string, string> = new Map();
  private wpBaseUploadUrl: string | null = null;

  constructor(
    private options: {
      language?: string;
      scope?: string[] | null;
      accountId?: number | null;
      userId?: number | null;
      config?: userConfig | null;
    } = {},
  ) {
    this.language =
      options.language ?? process.env.DEFAULT_LANGUAGE_CODE ?? "en";
    this.scope = options.scope ?? [];
    this.config = options.config ?? null;
    this.userId = options.userId ?? null;
    this.accountId = options.accountId ?? null;
  }

  private buildWpConfig = (
    override?: WordpressDbConfigOverride | null,
  ): WpDbConfig => {
    const host = override?.host ?? process.env.WP_DB_HOST ?? "127.0.0.1";
    const portValue = override?.port ?? process.env.WP_DB_PORT ?? 3306;
    const port = Number(portValue);
    const user = override?.user ?? process.env.WP_DB_USER ?? "wp_user";
    const password =
      override?.password ?? process.env.WP_DB_PASSWORD ?? "wp_password";
    const database =
      override?.database ?? process.env.WP_DB_NAME ?? "wordpress";
    const prefix = override?.prefix ?? process.env.WP_DB_PREFIX ?? "wp_";
    return {
      host,
      port: Number.isFinite(port) ? port : 3306,
      user,
      password,
      database,
      prefix,
    };
  };

  private resolveDefaultAccountId = async (): Promise<number | null> => {
    const envAccount =
      process.env.IMPORT_ACCOUNT_ID || process.env.DEFAULT_ACCOUNT_ID;
    const accountId = envAccount ? Number(envAccount) : NaN;
    if (Number.isFinite(accountId) && accountId > 0) return accountId;

    const account = await Models.Account.findOne({
      attributes: ["id"],
      order: [["id", "ASC"]],
    });
    return account?.id ?? null;
  };

  private resolveDefaultUserId = async (): Promise<number | null> => {
    const envUser = process.env.IMPORT_USER_ID || process.env.ADMIN_USER_ID;
    const userId = envUser ? Number(envUser) : NaN;
    if (Number.isFinite(userId) && userId > 0) return userId;

    const user = await Models.User.findOne({
      attributes: ["id"],
      order: [["id", "ASC"]],
    });
    return user?.id ?? null;
  };

  private ensureServices = async (defaultAuthorId?: number): Promise<void> => {
    if (!this.accountId) {
      this.accountId = await this.resolveDefaultAccountId();
    }
    if (!this.userId) {
      this.userId = defaultAuthorId ?? (await this.resolveDefaultUserId());
    }
    if (!this.config) {
      this.config = {
        isPremium: false,
        timeZone: "UTC",
      };
    }

    // Ensure 'seaqa' CategoryType exists dynamically
    const [seaqaType] = await Models.CategoryType.findOrCreate({
      where: { code: "seaqa" },
      defaults: { code: "seaqa", status: 1, sortOrder: 7, userId: this.userId || 1 }
    });
    await Models.CategoryTypeContent.findOrCreate({
      where: { categoryTypeId: seaqaType.id, languageId: 1 },
      defaults: {
        categoryTypeId: seaqaType.id,
        languageId: 1,
        name: "SeaQA Topic",
        description: "Category to manage SeaQA topics" as unknown as Text,
        descriptionText: "Category to manage SeaQA topics" as unknown as Text,
      }
    });

    if (!this.postService) {
      this.postService = new PostService({
        userId: this.userId,
        accountId: this.accountId,
        language: this.language,
        scope: this.scope,
        config: this.config,
      });
    }
    if (!this.categoryService) {
      this.categoryService = new CategoryService({
        userId: this.userId,
        accountId: this.accountId,
        language: this.language,
        scope: this.scope,
        config: this.config,
      });
    }
    if (!this.postDao) {
      this.postDao = new PostDao({
        userId: this.userId,
        accountId: this.accountId,
        language: this.language,
        scope: this.scope,
        config: this.config,
      });
    }
    if (!this.commentDao) {
      this.commentDao = new CommentDao();
    }
    if (!this.userDao) {
      this.userDao = new UserDao({
        userId: this.userId,
        accountId: this.accountId,
        language: this.language,
        scope: this.scope,
        config: this.config,
      });
    }
    if (!this.roleDao) {
      this.roleDao = new RoleDao({
        userId: this.userId,
        accountId: this.accountId,
        language: this.language,
        scope: this.scope,
        config: this.config,
      });
    }
    if (!this.attachmentService) {
      this.attachmentService = new AttachmentService({
        userId: this.userId,
        accountId: this.accountId,
        language: this.language,
        scope: this.scope,
        config: this.config,
      });
    }
    if (!this.roleService) {
      this.roleService = new RoleService({
        userId: this.userId,
        accountId: this.accountId,
        language: this.language,
        scope: this.scope,
        config: this.config,
      });
    }
  };

  private mapPostStatus = (
    status: string | null,
  ): { status: string; visibility: string } => {
    const lower = (status || "").toLowerCase();
    let mappedStatus = POST.STATUS.DRAFT;
    let visibility = POST.VISIBILITY.LOGGED_IN;

    if (lower === "publish" || lower === "future") {
      mappedStatus = POST.STATUS.PUBLISH;
    } else if (lower === "trash") {
      mappedStatus = POST.STATUS.TRASH;
    } else if (lower === "private") {
      mappedStatus = POST.STATUS.PUBLISH;
      visibility = POST.VISIBILITY.LOGGED_IN;
    } else if (
      lower === "pending" ||
      lower === "auto-draft" ||
      lower === "inherit"
    ) {
      mappedStatus = POST.STATUS.DRAFT;
    } else if (lower === "draft") {
      mappedStatus = POST.STATUS.DRAFT;
    }

    return { status: mappedStatus, visibility };
  };

  private mapCommentStatus = (approved: string | number | null): string => {
    const value =
      typeof approved === "number"
        ? approved.toString()
        : (approved || "").toString();
    if (value === "1") return COMMENT.STATUS.APPROVED;
    if (value === "0") return COMMENT.STATUS.PENDING;
    if (value.toLowerCase() === "spam" || value.toLowerCase() === "trash")
      return COMMENT.STATUS.SPAM;
    return COMMENT.STATUS.PENDING;
  };

  private buildMetaMap = <T extends { [key: string]: any }>(
    rows: T[],
    idKey: string,
  ): Map<number, Record<string, string[]>> => {
    const map = new Map<number, Record<string, string[]>>();
    for (const row of rows) {
      const id = Number(row[idKey]);
      if (!Number.isFinite(id)) continue;
      if (!map.has(id)) map.set(id, {});
      const bucket = map.get(id)!;
      const metaKey = String(row.meta_key || "");
      if (!bucket[metaKey]) bucket[metaKey] = [];
      bucket[metaKey].push(row.meta_value ?? "");
    }
    return map;
  };

  private resolveAuthorRoleId = async (): Promise<number | null> => {
    if (this.authorRoleId !== null) return this.authorRoleId;
    if (!this.roleDao) return null;
    const authorRole = await this.roleDao.getRoleByCode({
      code: "author",
      expanded: false,
      paranoid: true,
    });
    if (authorRole?.id) {
      this.authorRoleId = authorRole.id;
      return authorRole.id;
    }
    const fallbackRole = await this.roleDao.getRoleByCode({
      code: "user",
      expanded: false,
      paranoid: true,
    });
    this.authorRoleId = fallbackRole?.id ?? null;
    return this.authorRoleId;
  };

  private fetchUsers = async (
    conn: mysql.PoolConnection,
    prefix: string,
    userIds: number[],
  ): Promise<WpUserRow[]> => {
    if (!userIds.length) return [];
    const placeholders = userIds.map(() => "?").join(",");
    const [rows] = await conn.query(
      `SELECT ID, user_login, user_nicename, user_email, display_name, user_url
             FROM ${prefix}users
             WHERE ID IN (${placeholders})`,
      [...userIds],
    );
    return rows as WpUserRow[];
  };

  private fetchUserMeta = async (
    conn: mysql.PoolConnection,
    prefix: string,
    userIds: number[],
  ): Promise<Map<number, Record<string, string[]>>> => {
    if (!userIds.length) return new Map();
    const placeholders = userIds.map(() => "?").join(",");
    const [rows] = await conn.query(
      `SELECT user_id, meta_key, meta_value FROM ${prefix}usermeta WHERE user_id IN (${placeholders})`,
      [...userIds],
    );
    return this.buildMetaMap(rows as WpUserMetaRow[], "user_id");
  };

  private getLocalRankIdMap = async (
    conn: mysql.PoolConnection,
    remoteUserIds: number[],
  ): Promise<Map<number, number>> => {
    const userToLocalRankId = new Map<number, number>();
    if (!remoteUserIds.length) return userToLocalRankId;

    const placeholders = remoteUserIds.map(() => "?").join(",");
    const [rows] = await conn.query(
      `SELECT user_id, currentRank FROM user_settings WHERE user_id IN (${placeholders})`,
      [...remoteUserIds],
    );
    const settingsRows = rows as { user_id: number; currentRank: number }[];
    if (!settingsRows.length) return userToLocalRankId;

    const localCategories = await Models.Category.findAll({
      where: { categoryTypeId: 6 },
      attributes: ["id", "code"],
    });

    const localRankCodeToId = new Map<string, number>();
    for (const cat of localCategories) {
      if (cat.code) {
        localRankCodeToId.set(cat.code, cat.id);
      }
    }

    for (const row of settingsRows) {
      const remoteRankId = row.currentRank;
      if (remoteRankId && REMOTE_RANK_MAP[remoteRankId]) {
        const slugCode = REMOTE_RANK_MAP[remoteRankId];
        const localId = localRankCodeToId.get(slugCode);
        if (localId) {
          userToLocalRankId.set(row.user_id, localId);
        }
      }
    }

    return userToLocalRankId;
  };

  private fetchAttachments = async (
    conn: mysql.PoolConnection,
    prefix: string,
    postIds: number[],
    attachmentIds: number[],
  ): Promise<WpAttachmentRow[]> => {
    const postPlaceholders = postIds.map(() => "?").join(",");
    const attachmentPlaceholders = attachmentIds.map(() => "?").join(",");
    const conditions: string[] = [];
    const params: number[] = [];
    if (postIds.length) {
      conditions.push(`post_parent IN (${postPlaceholders})`);
      params.push(...postIds);
    }
    if (attachmentIds.length) {
      conditions.push(`ID IN (${attachmentPlaceholders})`);
      params.push(...attachmentIds);
    }
    if (!conditions.length) return [];
    const [rows] = await conn.query(
      `SELECT ID, post_parent, guid, post_mime_type
             FROM ${prefix}posts
             WHERE post_type = 'attachment' AND (${conditions.join(" OR ")})`,
      params,
    );
    return rows as WpAttachmentRow[];
  };

  private ensureWpUploadBaseUrl = async (
    conn: mysql.PoolConnection,
    prefix: string,
  ): Promise<void> => {
    if (this.wpBaseUploadUrl !== null) return;
    const [rows] = await conn.query(
      `SELECT option_name, option_value FROM ${prefix}options WHERE option_name IN ('upload_url_path','siteurl','home')`,
    );
    const options = new Map<string, string>();
    for (const row of rows as Array<{ option_name: string; option_value: string }>) {
      if (row.option_name) options.set(row.option_name, row.option_value);
    }
    const uploadUrl = options.get("upload_url_path") || "";
    if (uploadUrl) {
      this.wpBaseUploadUrl = uploadUrl.replace(/\/+$/, "");
      return;
    }
    const siteUrl = options.get("siteurl") || options.get("home") || "";
    if (siteUrl) {
      this.wpBaseUploadUrl = `${siteUrl.replace(/\/+$/, "")}/wp-content/uploads`;
      return;
    }
    this.wpBaseUploadUrl = null;
  };

  private normalizeText = (text: string | null | undefined): string => {
    return (text || "").toString().trim();
  };

  private validateUserEmail = async (email: string | null | undefined): Promise<boolean> => {
    if (!email) return false;
    const cleanEmail = email.trim();
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    return emailRegex.test(cleanEmail);
  };

  private isContentEmptyOrPlaceholder = (html: string | null | undefined): boolean => {
    if (!html) return true;
    const plainText = Common.htmlToText(html);
    const cleanText = plainText.replace(/[\s?]+/g, '').trim();
    return cleanText.length === 0;
  };

  private extractFirstLines = (text: string, count: number): string | null => {
    const lines = text
      .split(/\r?\n/)
      .map((line) => line.trim())
      .filter(Boolean);
    if (!lines.length) return null;
    return lines.slice(0, count).join("\n");
  };

  private buildKeywordsFromText = (
    title: string,
    descriptionText: string,
  ): string | null => {
    const stopWords = new Set([
      "a",
      "an",
      "the",
      "and",
      "or",
      "but",
      "if",
      "then",
      "so",
      "to",
      "of",
      "in",
      "on",
      "for",
      "with",
      "at",
      "by",
      "from",
      "as",
      "is",
      "are",
      "was",
      "were",
      "be",
      "been",
      "this",
      "that",
      "these",
      "those",
      "it",
      "its",
      "we",
      "you",
      "your",
      "our",
      "they",
      "their",
      "i",
      "me",
      "my",
      "us",
    ]);
    const combined = `${title} ${descriptionText}`.toLowerCase();
    const tokens = combined
      .replace(/[^a-z0-9\s]/g, " ")
      .split(/\s+/)
      .filter((word) => word.length > 2 && !stopWords.has(word));
    if (!tokens.length) return null;
    const freq = new Map<string, number>();
    for (const token of tokens) {
      freq.set(token, (freq.get(token) || 0) + 1);
    }
    const keywords = Array.from(freq.entries())
      .sort((a, b) => b[1] - a[1])
      .slice(0, 10)
      .map(([word]) => word);
    return keywords.length ? keywords.join(", ") : null;
  };

  private resolveAttachmentUrl = (
    attachment: WpAttachmentRow,
    meta: Record<string, string[]> | undefined,
  ): string | null => {
    if (attachment.guid && attachment.guid.startsWith("http")) {
      return attachment.guid;
    }
    const attachedFile = this.getMetaValue(meta, ["_wp_attached_file"]);
    if (attachedFile) {
      if (attachedFile.startsWith("http")) return attachedFile;
      if (this.wpBaseUploadUrl) {
        return `${this.wpBaseUploadUrl.replace(/\/+$/, "")}/${attachedFile.replace(
          /^\/+/,
          "",
        )}`;
      }
    }
    return null;
  };

  private importWpAttachment = async (
    attachment: WpAttachmentRow,
    attachmentMetaMap: Map<number, Record<string, string[]>>,
  ): Promise<number | null> => {
    if (this.wpAttachmentIdCache.has(attachment.ID)) {
      return this.wpAttachmentIdCache.get(attachment.ID) || null;
    }
    const meta = attachmentMetaMap.get(attachment.ID);
    const url = this.resolveAttachmentUrl(attachment, meta);
    if (!url) return null;
    if (this.wpAttachmentUrlCache.has(url)) {
      const cachedId = this.wpAttachmentUrlCache.get(url) || null;
      if (cachedId) this.wpAttachmentIdCache.set(attachment.ID, cachedId);
      return cachedId;
    }
    if (!this.attachmentService) return null;
    try {
      const uploaded = await this.attachmentService.uploadImageFromUrl(url);
      if (uploaded?.id) {
        this.wpAttachmentIdCache.set(attachment.ID, uploaded.id);
        this.wpAttachmentUrlCache.set(url, uploaded.id);
        return uploaded.id;
      }
    } catch (err) {
      return null;
    }
    return null;
  };

  private importAttachmentFromUrl = async (
    url: string,
  ): Promise<number | null> => {
    const normalizedUrl = url.trim();
    if (!normalizedUrl) return null;
    if (this.wpAttachmentUrlCache.has(normalizedUrl)) {
      return this.wpAttachmentUrlCache.get(normalizedUrl) || null;
    }
    if (!this.attachmentService) return null;
    try {
      const uploaded = await this.attachmentService.uploadImageFromUrl(
        normalizedUrl,
      );
      if (uploaded?.id) {
        this.wpAttachmentUrlCache.set(normalizedUrl, uploaded.id);
        if (uploaded.filePath) {
          this.wpAttachmentPublicUrlCache.set(normalizedUrl, uploaded.filePath);
        }
        return uploaded.id;
      }
    } catch (err) {
      return null;
    }
    return null;
  };

  private getAttachmentFromUrl = async (url: string): Promise<{ id: number; url: string } | null> => {
    const normalizedUrl = url.trim();
    if (!normalizedUrl) return null;

    if (this.wpAttachmentPublicUrlCache.has(normalizedUrl) && this.wpAttachmentUrlCache.has(normalizedUrl)) {
      return {
        id: this.wpAttachmentUrlCache.get(normalizedUrl)!,
        url: this.wpAttachmentPublicUrlCache.get(normalizedUrl)!
      };
    }

    if (!this.attachmentService) return null;
    try {
      const uploaded = await this.attachmentService.uploadImageFromUrl(normalizedUrl);
      if (uploaded?.filePath && uploaded?.id) {
        this.wpAttachmentPublicUrlCache.set(normalizedUrl, uploaded.filePath);
        this.wpAttachmentUrlCache.set(normalizedUrl, uploaded.id);
        return { id: uploaded.id, url: uploaded.filePath };
      }
    } catch (err) {
      return null;
    }
    return null;
  };

  private parseImageMetaValue = (
    value: string | null,
  ): { url?: string; id?: number } | null => {
    if (!value) return null;
    const trimmed = value.trim();
    if (!trimmed) return null;
    if (/^\d+$/.test(trimmed)) {
      return { id: Number(trimmed) };
    }
    const urlMatch = trimmed.match(/https?:\/\/[^\s"'<>]+/);
    if (urlMatch) return { url: urlMatch[0] };
    return null;
  };

  private ensureAuthors = async (
    conn: mysql.PoolConnection,
    prefix: string,
    authorIds: number[],
  ): Promise<Map<number, number>> => {
    const authorMap = new Map<number, number>();
    if (!this.userDao) return authorMap;
    const uniqueAuthorIds = Array.from(new Set(authorIds.filter((id) => id > 0)));
    if (!uniqueAuthorIds.length) return authorMap;

    const users = await this.fetchUsers(conn, prefix, uniqueAuthorIds);
    const userMetaMap = await this.fetchUserMeta(conn, prefix, uniqueAuthorIds);
    const localRankIdMap = await this.getLocalRankIdMap(conn, uniqueAuthorIds);

    const avatarMetaKeys = [
      "wp_user_avatar",
      "simple_local_avatar",
      "profile_picture",
      "profile_pic",
      "profile_image",
      "user_avatar",
      "avatar",
    ];

    const avatarAttachmentIds: number[] = [];
    for (const user of users) {
      const meta = userMetaMap.get(user.ID);
      const avatarValue = this.getMetaValue(meta, avatarMetaKeys);
      const parsed = this.parseImageMetaValue(avatarValue);
      if (parsed?.id) avatarAttachmentIds.push(parsed.id);
    }

    const attachmentRows = await this.fetchAttachments(
      conn,
      prefix,
      [],
      avatarAttachmentIds,
    );
    const attachmentById = new Map<number, WpAttachmentRow>();
    for (const row of attachmentRows) attachmentById.set(row.ID, row);
    const attachmentMetaMap = await this.fetchPostMeta(
      conn,
      prefix,
      avatarAttachmentIds,
    );
    await this.ensureWpUploadBaseUrl(conn, prefix);

    const authorRoleId = await this.resolveAuthorRoleId();

    for (const user of users) {
      let localUserId: number | null = null;
      let email = this.normalizeText(user.user_email);
      email = email.replace(/["'`\-;<>\\]/g, "").replace(/--/g, "").trim();

      if (!email || !(await this.validateUserEmail(email))) {
        continue;
      }

      const username =
        this.normalizeText(user.user_login) ||
        this.normalizeText(user.user_nicename) ||
        null;

      const existing = await Models.User.findByPk(user.ID, { attributes: ["id"] });
      if (existing) {
        localUserId = existing.id;
        const mappedRankId = localRankIdMap.get(user.ID);
        if (mappedRankId) {
          await Models.UserProfile.update(
            { rankId: mappedRankId },
            { where: { userId: localUserId } }
          );
        }
      }

      if (!localUserId) {
        const meta = userMetaMap.get(user.ID);
        const displayName =
          this.normalizeText(user.display_name) ||
          this.normalizeText(user.user_nicename) ||
          this.normalizeText(user.user_login) ||
          `wp-author-${user.ID}`;
        const firstName = this.getMetaValue(meta, ["first_name"]);
        const lastName = this.getMetaValue(meta, ["last_name"]);
        const bio = this.getMetaValue(meta, ["description"]);

        const mappedRankId = localRankIdMap.get(user.ID) || null;

        const createInput: UserCreateDaoInput = {
          userInfo: {
            id: user.ID,
            email,
            username,
            password: null,
            referralCode: null,
            name: displayName,
            firstName: firstName || null,
            lastName: lastName || null,
            accountId: this.accountId,
            aboutMe: bio || null,
            rankId: mappedRankId,
          },
          roles: authorRoleId ? [authorRoleId] : [],
        };

        try {
          localUserId = await this.userDao.createUser(createInput, {});
        } catch (err: any) {
          if (err.name === "SequelizeUniqueConstraintError" || err.message === "USER_ALREADY_EXISTS") {
            const existingUserByEmail = email ? await Models.User.findOne({ where: { email } }) : null;
            if (existingUserByEmail) {
              localUserId = existingUserByEmail.id;
            } else {
              const existingUserByUsername = username ? await Models.User.findOne({ where: { username } }) : null;
              if (existingUserByUsername) {
                localUserId = existingUserByUsername.id;
              }
            }
            if (localUserId && mappedRankId) {
              await Models.UserProfile.update(
                { rankId: mappedRankId },
                { where: { userId: localUserId } }
              );
            }
          }
          if (!localUserId) {
            continue;
          }
        }

        const avatarValue = this.getMetaValue(
          meta,
          avatarMetaKeys,
        );
        const parsedAvatar = this.parseImageMetaValue(avatarValue);
        let profileImageId: number | null = null;
        if (parsedAvatar?.id && attachmentById.has(parsedAvatar.id)) {
          profileImageId = await this.importWpAttachment(
            attachmentById.get(parsedAvatar.id)!,
            attachmentMetaMap,
          );
        } else if (parsedAvatar?.url) {
          profileImageId = await this.importAttachmentFromUrl(parsedAvatar.url);
        }
        if (profileImageId) {
          const profileDao = new UserDao({
            userId: localUserId,
            accountId: this.accountId,
            language: this.language,
            scope: this.scope,
            config: this.config,
          });
          await profileDao.updateUserProfileFields(
            { fields: { profileImageId } },
            {},
          );
        }
      }

      if (localUserId) {
        authorMap.set(user.ID, localUserId);
      }
    }

    return authorMap;
  };

  private ensureSeaQAUsers = async (
    conn: mysql.PoolConnection,
    authorIds: number[],
  ): Promise<Map<number, number>> => {
    const authorMap = new Map<number, number>();
    if (!this.userDao || !this.roleDao || !this.roleService) return authorMap;
    const uniqueAuthorIds = Array.from(new Set(authorIds.filter((id) => id > 0)));
    if (!uniqueAuthorIds.length) return authorMap;

    // Check which users exist locally by ID
    const localUsers = await Models.User.findAll({
      where: { id: uniqueAuthorIds },
      attributes: ["id"],
    });
    const localUserIds = new Set<number>(localUsers.map((u: any) => Number(u.id)));

    const idsToFetch = uniqueAuthorIds.filter((id) => !localUserIds.has(id));
    for (const id of localUserIds) {
      authorMap.set(id, id);
    }

    if (!idsToFetch.length) {
      return authorMap;
    }

    // Fetch the missing users from the remote database
    const placeholders = idsToFetch.map(() => "?").join(",");
    const [rows] = await conn.query(
      `SELECT u.id, u.user_type, u.slug, u.email, u.password, u.created_at, u.status, u.date_of_birth, u.area_of_expertise, us.currentRank, us.currentCompany 
       FROM user u
       LEFT JOIN user_settings us ON u.id = us.user_id
       WHERE u.id IN (${placeholders})`,
      [...idsToFetch],
    );

    const sourceUsers = rows as any[];
    if (!sourceUsers.length) {
      return authorMap;
    }

    // Pre-fetch existing local users by email in bulk to avoid sequential checks
    const sanitizedEmails = sourceUsers.map(user => {
      let email = (user.email || "").toString();
      return email.replace(/["'`\-;<>\\]/g, "").replace(/--/g, "").trim().toLowerCase();
    }).filter(Boolean);

    const existingUsersByEmail = sanitizedEmails.length > 0
      ? await Models.User.findAll({
        where: { email: sanitizedEmails },
        attributes: ["id", "email"]
      })
      : [];

    const existingEmailsMap = new Map<string, number>();
    for (const u of existingUsersByEmail) {
      if (u.email) {
        existingEmailsMap.set(u.email.toLowerCase(), u.id);
      }
    }

    // Get admin and user roles
    let adminRoleId: number | null = null;
    let userRoleId: number | null = null;
    const adminRole = await this.roleDao.getRoleByCode({ code: "admin", expanded: false });
    if (adminRole?.id) {
      adminRoleId = adminRole.id;
    }
    const userRoleObj = await this.roleDao.getRoleByCode({ code: "user", expanded: false });
    if (userRoleObj?.id) {
      userRoleId = userRoleObj.id;
    }

    // Get default fallback roles
    const defaultRoles = await this.roleService.getDafaultRoles();

    const localCategories = await Models.Category.findAll({
      where: { categoryTypeId: 6 },
      attributes: ["id", "code"],
    });

    const localRankCodeToId = new Map<string, number>();
    for (const cat of localCategories) {
      if (cat.code) {
        localRankCodeToId.set(cat.code, cat.id);
      }
    }

    for (const user of sourceUsers) {
      try {
        let email = (user.email || "").toString();
        // remove SQL injection attempts and quotes (spam check as in PHP script)
        email = email.replace(/["'`\-;<>\\]/g, "").replace(/--/g, "").trim();

        if (!email || !(await this.validateUserEmail(email))) {
          continue;
        }

        let mappedRankId: number | null = null;
        const remoteRankId = user.currentRank;
        if (remoteRankId && REMOTE_RANK_MAP[remoteRankId]) {
          const slugCode = REMOTE_RANK_MAP[remoteRankId];
          mappedRankId = localRankCodeToId.get(slugCode) || null;
        }

        // Check if exists by email/username as a fallback/guard using pre-fetched map
        const existingId = existingEmailsMap.get(email.toLowerCase());
        if (existingId) {
          const profileUpdates: any = {};
          if (mappedRankId) profileUpdates.rankId = mappedRankId;
          if (user.currentCompany) profileUpdates.presentCompany = user.currentCompany;
          if (Object.keys(profileUpdates).length > 0) {
            await Models.UserProfile.update(
              profileUpdates,
              { where: { userId: existingId } }
            );
          }
          authorMap.set(user.id, existingId);
          continue;
        }

        const username = user.slug || email.split("@")[0];
        const displayName = user.slug
          ? user.slug
            .split(/[-_]/)
            .map(
              (s: string) => s.charAt(0).toUpperCase() + s.slice(1),
            )
            .join(" ")
          : email.split("@")[0];

        let userRoleIds: number[] = [];
        if (user.user_type === 1 || user.user_type === '1') {
          userRoleIds = adminRoleId ? [adminRoleId] : defaultRoles;
        } else {
          userRoleIds = userRoleId ? [userRoleId] : defaultRoles;
        }

        const userTypeMap: Record<string, string> = {
          "1": "admin",
          "2": "employee",
          "3": "employer",
          "4": "institute",
          "5": "Seafarers working As",
          "6": "Others",
          "7": "EmployerTeam",
        };
        const mappedUserType = user.user_type !== null && user.user_type !== undefined
          ? (userTypeMap[user.user_type.toString()] || null)
          : null;

        const createInput: UserCreateDaoInput = {
          userInfo: {
            id: user.id,
            email,
            username,
            password: user.password, // Maintain same hash (MD5 or Bcrypt)
            name: displayName,
            accountId: this.accountId,
            referralCode: null,
            dob: user.date_of_birth || null,
            areaOfExpertise: user.area_of_expertise || null,
            userType: mappedUserType,
            rankId: mappedRankId,
            presentCompany: user.currentCompany || null,
          },
          roles: userRoleIds,
        };

        const createdId = await this.userDao.createUser(createInput, {});
        authorMap.set(user.id, createdId);
      } catch (err: any) {
        // Log or handle error
      }
    }

    return authorMap;
  };

  private ensureCategory = async (
    categoryTypeCode: string,
    name: string,
    description: string | null,
    allowCreate: boolean,
  ): Promise<EnsureCategoryResult> => {
    const code = Common.slugify(name || "");
    const dbCode = `${categoryTypeCode}-${code}`;
    const cache =
      categoryTypeCode === "tag" ? this.tagCodeCache : this.categoryCodeCache;
    const cachedId = cache.get(dbCode);
    if (cachedId) {
      return { id: cachedId, code, created: false };
    }

    if (!this.categoryService) {
      throw new AppError(500, "CATEGORY_SERVICE_NOT_AVAILABLE", {});
    }

    try {
      const id = await this.categoryService.getCategoryId({ code: dbCode });
      if (id) {
        cache.set(dbCode, id);
        return { id, code, created: false };
      }
    } catch (err) {
      if (!(err instanceof AppError && err.statusCode === 404)) {
        throw err;
      }
    }

    if (!allowCreate) {
      return { id: null, code, created: false };
    }

    try {
      const created = await this.categoryService.createCategory({
        categoryTypeCode,
        imageId: null,
        parentId: null,
        status: CATEGORY.STATUS.ACTIVE,
        refLink: null,
        name,
        description: description as unknown as Text,
      });
      const newId = created.id;
      cache.set(dbCode, newId);
      return { id: newId, code, created: true };
    } catch (err) {
      if (
        err instanceof AppError &&
        err.messageKey === "CATEGORY_ALREADY_EXISTS"
      ) {
        const id = await this.categoryService.getCategoryId({ code: dbCode });
        cache.set(dbCode, id);
        return { id, code, created: false };
      }
      throw err;
    }
  };

  private ensureTaxonomyMaps = async (
    conn: mysql.PoolConnection,
    prefix: string,
    allowCategories: boolean,
    allowTags: boolean,
    termTaxonomyIds?: number[],
  ): Promise<{ createdCategories: number; createdTags: number }> => {
    if (this.taxonomyLoaded) {
      return { createdCategories: 0, createdTags: 0 };
    }
    if (!this.categoryService) {
      throw new AppError(500, "CATEGORY_SERVICE_NOT_AVAILABLE", {});
    }

    let createdCategories = 0;
    let createdTags = 0;
    let data: WpTermRelationshipRow[] = [];

    if (termTaxonomyIds && termTaxonomyIds.length > 0) {
      const uniqueIds = Array.from(new Set(termTaxonomyIds.filter(id => id > 0)));
      if (uniqueIds.length > 0) {
        const placeholders = uniqueIds.map(() => "?").join(",");
        const [rows] = await conn.query(
          `SELECT tt.term_taxonomy_id, tt.term_id, tt.taxonomy, tt.parent, tt.description, t.name, t.slug
           FROM ${prefix}term_taxonomy tt
           JOIN ${prefix}terms t ON tt.term_id = t.term_id
           WHERE tt.term_taxonomy_id IN (${placeholders}) AND tt.taxonomy NOT IN ('nav_menu', 'link_category', 'post_format', 'action_monitor')`,
          [...uniqueIds],
        );
        data = rows as WpTermRelationshipRow[];

        const parentTermIds = Array.from(new Set(data.map(r => r.parent).filter(pid => pid && pid > 0)));
        if (parentTermIds.length > 0) {
          const parentPlaceholders = parentTermIds.map(() => "?").join(",");
          const [parentRows] = await conn.query(
            `SELECT tt.term_taxonomy_id, tt.term_id, tt.taxonomy, tt.parent, tt.description, t.name, t.slug
             FROM ${prefix}term_taxonomy tt
             JOIN ${prefix}terms t ON tt.term_id = t.term_id
             WHERE tt.term_id IN (${parentPlaceholders}) AND tt.taxonomy NOT IN ('nav_menu', 'link_category', 'post_format', 'action_monitor')`,
            [...parentTermIds],
          );
          const existingTaxonomyIds = new Set(data.map(r => r.term_taxonomy_id));
          for (const pRow of parentRows as WpTermRelationshipRow[]) {
            if (!existingTaxonomyIds.has(pRow.term_taxonomy_id)) {
              data.push(pRow);
              existingTaxonomyIds.add(pRow.term_taxonomy_id);
            }
          }
        }
      }
    } else {
      let lastTaxonomyId = 0;
      const chunkSize = Number(process.env.WP_IMPORT_TAXONOMY_CHUNK_SIZE || 500);

      while (true) {
        const [rows] = await conn.query(
          `SELECT tt.term_taxonomy_id, tt.term_id, tt.taxonomy, tt.parent, tt.description, t.name, t.slug
                   FROM ${prefix}term_taxonomy tt
                   JOIN ${prefix}terms t ON tt.term_id = t.term_id
                   WHERE tt.term_taxonomy_id > ? AND tt.taxonomy NOT IN ('nav_menu', 'link_category', 'post_format', 'action_monitor')
                   ORDER BY tt.term_taxonomy_id ASC
                   LIMIT ?`,
          [lastTaxonomyId, chunkSize],
        );

        const chunkData = rows as WpTermRelationshipRow[];
        if (!chunkData.length) break;
        data.push(...chunkData);
        lastTaxonomyId = chunkData[chunkData.length - 1].term_taxonomy_id;
      }
    }

    for (const row of data) {
      if (row.taxonomy !== "post_tag") {
        const result = await this.ensureCategory(
          "post",
          row.name,
          row.description ?? null,
          allowCategories,
        );
        if (result.created) createdCategories += 1;
        if (result.id) {
          this.termTaxonomyToCategoryId.set(row.term_taxonomy_id, result.id);
          if (result.code)
            this.termTaxonomyToCategoryCode.set(
              row.term_taxonomy_id,
              result.code,
            );
          this.categoryTermById.set(row.term_id, {
            termId: row.term_id,
            parentTermId: row.parent ?? 0,
            name: row.name,
            description: row.description ?? null,
            categoryId: result.id,
          });
        }
      } else if (row.taxonomy === "post_tag") {
        const result = await this.ensureCategory(
          "tag",
          row.name,
          row.description ?? null,
          allowTags,
        );
        if (result.created) createdTags += 1;
        if (result.id) {
          this.termTaxonomyToTagId.set(row.term_taxonomy_id, result.id);
        }
      }
    }

    await this.applyCategoryParents();
    this.taxonomyLoaded = true;
    return { createdCategories, createdTags };
  };

  private applyCategoryParents = async (): Promise<void> => {
    if (!this.categoryService) return;

    for (const info of this.categoryTermById.values()) {
      if (!info.parentTermId || info.parentTermId <= 0) continue;
      if (this.categoryParentApplied.has(info.categoryId)) continue;
      const parent = this.categoryTermById.get(info.parentTermId);
      if (!parent || parent.categoryId === info.categoryId) continue;

      if (parent.name.toLowerCase() === "blog") continue;

      try {
        await this.categoryService.updateCategory({
          id: info.categoryId,
          categoryTypeCode: "post",
          imageId: null,
          parentId: parent.categoryId,
          status: CATEGORY.STATUS.ACTIVE,
          refLink: null,
          name: info.name,
          description: info.description as unknown as Text,
        });
        this.categoryParentApplied.add(info.categoryId);
      } catch (err) {
        // ignore parent update errors to keep import flowing
        continue;
      }
    }
  };

  private fetchPosts = async (
    conn: mysql.PoolConnection,
    prefix: string,
    page: number,
    chunkSize: number,
    postTypes: string[],
  ): Promise<WpPostRow[]> => {
    const filteredTypes = postTypes.length ? postTypes : ["post"];
    const placeholders = filteredTypes.map(() => "?").join(",");
    const offset = (page - 1) * chunkSize;
    const [rows] = await conn.query(
      `SELECT ID, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, post_name, post_type, post_password
             FROM ${prefix}posts
             WHERE post_type IN (${placeholders}) AND post_status = 'publish'
             ORDER BY ID ASC
             LIMIT ?, ?`,
      [...filteredTypes, offset, chunkSize],
    );
    return rows as WpPostRow[];
  };

  private fetchPostMeta = async (
    conn: mysql.PoolConnection,
    prefix: string,
    postIds: number[],
  ): Promise<Map<number, Record<string, string[]>>> => {
    if (!postIds.length) return new Map();
    const placeholders = postIds.map(() => "?").join(",");
    const [rows] = await conn.query(
      `SELECT post_id, meta_key, meta_value FROM ${prefix}postmeta WHERE post_id IN (${placeholders})`,
      [...postIds],
    );
    return this.buildMetaMap(rows as WpPostMetaRow[], "post_id");
  };

  private fetchComments = async (
    conn: mysql.PoolConnection,
    prefix: string,
    postIds: number[],
  ): Promise<WpCommentRow[]> => {
    if (!postIds.length) return [];
    const placeholders = postIds.map(() => "?").join(",");
    const [rows] = await conn.query(
      `SELECT comment_ID, comment_post_ID, comment_parent_id, comment_content, comment_approved, user_id, comment_author_email, comment_author, comment_date, comment_date_gmt, comment_type
             FROM ${prefix}comments
             WHERE comment_post_ID IN (${placeholders}) AND (comment_type = '' OR comment_type = 'comment')
             ORDER BY comment_ID ASC`,
      [...postIds],
    );
    return rows as WpCommentRow[];
  };

  private fetchCommentMeta = async (
    conn: mysql.PoolConnection,
    prefix: string,
    commentIds: number[],
  ): Promise<Map<number, Record<string, string[]>>> => {
    if (!commentIds.length) return new Map();
    const placeholders = commentIds.map(() => "?").join(",");
    const [rows] = await conn.query(
      `SELECT comment_id, meta_key, meta_value FROM ${prefix}commentmeta WHERE comment_id IN (${placeholders})`,
      [...commentIds],
    );
    return this.buildMetaMap(rows as WpCommentMetaRow[], "comment_id");
  };

  private fetchTermRelationships = async (
    conn: mysql.PoolConnection,
    prefix: string,
    postIds: number[],
  ): Promise<WpTermRelationshipRow[]> => {
    if (!postIds.length) return [];
    const placeholders = postIds.map(() => "?").join(",");
    const [rows] = await conn.query(
      `SELECT tr.object_id, tr.term_taxonomy_id, tt.taxonomy, tt.term_id, tt.parent, tt.description, t.name, t.slug
             FROM ${prefix}term_relationships tr
             JOIN ${prefix}term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
             JOIN ${prefix}terms t ON tt.term_id = t.term_id
             WHERE tr.object_id IN (${placeholders}) AND tt.taxonomy NOT IN ('nav_menu', 'link_category', 'post_format', 'action_monitor')`,
      [...postIds],
    );
    return rows as WpTermRelationshipRow[];
  };

  private getMetaValue = (
    meta: Record<string, string[]> | undefined,
    keys: string[],
  ): string | null => {
    if (!meta) return null;
    for (const key of keys) {
      const val = meta[key];
      if (val && val.length) {
        return val[0];
      }
    }
    return null;
  };

  private importPosts = async (
    posts: WpPostRow[],
    postMetaMap: Map<number, Record<string, string[]>>,
    termRelationships: WpTermRelationshipRow[],
    attachments: WpAttachmentRow[],
    attachmentMetaMap: Map<number, Record<string, string[]>>,
    targetPostType: string,
    authorIdMap: Map<number, number>,
    defaultAuthorId: number,
    allowCategories: boolean,
    allowTags: boolean,
  ): Promise<{
    importedPosts: number;
    skippedPosts: number;
    postIdMap: Map<number, number>;
    errors: WordpressImportErrorItem[];
  }> => {
    if (!this.postService || !this.postDao) {
      throw new AppError(500, "POST_SERVICE_NOT_AVAILABLE", {});
    }

    const postCategoryIds = new Map<number, Set<number>>();
    const postTagIds = new Map<number, Set<number>>();

    for (const rel of termRelationships) {
      if (rel.taxonomy !== "post_tag") {
        const categoryId = this.termTaxonomyToCategoryId.get(rel.term_taxonomy_id);
        if (!categoryId) continue;
        if (!postCategoryIds.has(rel.object_id))
          postCategoryIds.set(rel.object_id, new Set());
        postCategoryIds.get(rel.object_id)!.add(categoryId);
      } else if (rel.taxonomy === "post_tag") {
        const tagId = this.termTaxonomyToTagId.get(rel.term_taxonomy_id);
        if (!tagId) continue;
        if (!postTagIds.has(rel.object_id))
          postTagIds.set(rel.object_id, new Set());
        postTagIds.get(rel.object_id)!.add(tagId);
      }
    }

    const attachmentsByParent = new Map<number, WpAttachmentRow[]>();
    const attachmentById = new Map<number, WpAttachmentRow>();
    for (const attachment of attachments) {
      attachmentById.set(attachment.ID, attachment);
      if (!attachmentsByParent.has(attachment.post_parent)) {
        attachmentsByParent.set(attachment.post_parent, []);
      }
      attachmentsByParent.get(attachment.post_parent)!.push(attachment);
    }

    let importedPosts = 0;
    let skippedPosts = 0;
    const errors: WordpressImportErrorItem[] = [];
    const postIdMap = new Map<number, number>();

    // Batch query existing posts by wpPostId
    const wpPostIds = posts.map(p => p.ID);
    const existingPosts = await Models.Post.findAll({
      where: { wpPostId: wpPostIds, postType: targetPostType },
      attributes: ["id", "wpPostId"]
    });
    const wpPostIdToIdMap = new Map<number, number>();
    for (const ep of existingPosts) {
      if (ep.wpPostId) wpPostIdToIdMap.set(ep.wpPostId, ep.id);
    }

    for (const post of posts) {
      // Check for duplicate by wpPostId first (the most reliable way)
      let existingId = wpPostIdToIdMap.get(post.ID) || null;

      const meta = postMetaMap.get(post.ID);
      const { status, visibility } = this.mapPostStatus(post.post_status);
      const categoryIds = allowCategories
        ? Array.from(postCategoryIds.get(post.ID) || [])
        : [];
      const tagIds = allowTags ? Array.from(postTagIds.get(post.ID) || []) : [];
      const title = post.post_title || post.post_name || `wp-post-${post.ID}`;
      const descriptionText = post.post_content
        ? (((await Common.convertHtmlToText(
          post.post_content.toString(),
        )) as string) || "")
        : "";
      const excerptText = this.extractFirstLines(descriptionText, 2);
      const metaTitle = title || null;
      const metaDescription = excerptText || null;
      const metaKeywordsRaw =
        this.getMetaValue(meta, ["_aioseop_keywords"]) || null;
      const metaKeywords =
        this.normalizeText(metaKeywordsRaw) ||
        this.buildKeywordsFromText(title, descriptionText) ||
        null;
      const postCode =
        this.normalizeText(post.post_name) || Common.slugify(title);
      const canonicalUrl = postCode || null;

      let publishedAt: Date | null = null;
      if (post.post_date && post.post_date !== "0000-00-00 00:00:00") {
        publishedAt = new Date(post.post_date);
      } else if (
        post.post_date_gmt &&
        post.post_date_gmt !== "0000-00-00 00:00:00"
      ) {
        publishedAt = new Date(post.post_date_gmt + "Z");
      }

      const authorId = authorIdMap.get(post.post_author) || defaultAuthorId;
      const wpAttachments = attachmentsByParent.get(post.ID) || [];
      const postAttachmentIds: number[] = [];
      for (const attachment of wpAttachments) {
        const attachmentId = await this.importWpAttachment(
          attachment,
          attachmentMetaMap,
        );
        if (attachmentId) {
          postAttachmentIds.push(attachmentId);
        } else {
          errors.push({
            itemId: attachment.ID,
            type: "attachment",
            message: "ATTACHMENT_IMPORT_FAILED",
          });
        }
      }
      const featuredWpId = Number(
        this.getMetaValue(meta, ["_thumbnail_id"]) || 0,
      );
      let featuredImageId: number | null = null;
      if (featuredWpId && attachmentById.has(featuredWpId)) {
        featuredImageId = await this.importWpAttachment(
          attachmentById.get(featuredWpId)!,
          attachmentMetaMap,
        );
      }
      if (
        featuredImageId &&
        !postAttachmentIds.includes(featuredImageId)
      ) {
        postAttachmentIds.push(featuredImageId);
      }

      let contentHtml = post.post_content ? post.post_content.toString() : "";

      // Process <a><img></a> pattern where <a> tag contains the original image url in its href
      const linkedImgRegex = /<a\s+[^>]*?href=\\?["']?([^\\"' >]+)\\?["']?[^>]*?>\s*<img\s+[^>]*?src=\\?["']?([^\\"' >]+)\\?["']?[^>]*?>\s*<\/a>/gi;
      const linkedMatches = [...contentHtml.matchAll(linkedImgRegex)];

      for (const m of linkedMatches) {
        const fullMatch = m[0];
        const hrefUrl = m[1];
        const srcUrl = m[2];
        if (hrefUrl && hrefUrl.includes('wp-content/uploads')) {
          const uploadedImage = await this.getAttachmentFromUrl(hrefUrl);
          if (uploadedImage) {
            let updatedSegment = fullMatch;
            updatedSegment = updatedSegment.split(hrefUrl).join(uploadedImage.url);
            updatedSegment = updatedSegment.split(srcUrl).join(uploadedImage.url);
            contentHtml = contentHtml.split(fullMatch).join(updatedSegment);
            if (!postAttachmentIds.includes(uploadedImage.id)) {
              postAttachmentIds.push(uploadedImage.id);
            }
          }
        }
      }

      const imgRegex = /<img[^>]+src="([^">]+)"/gi;
      const matches = [...contentHtml.matchAll(imgRegex)];

      for (const m of matches) {
        const url = m[1];
        if (url && url.includes('wp-content/uploads')) {
          const uploadedImage = await this.getAttachmentFromUrl(url);
          if (uploadedImage) {
            contentHtml = contentHtml.split(url).join(uploadedImage.url);
            if (!postAttachmentIds.includes(uploadedImage.id)) {
              postAttachmentIds.push(uploadedImage.id);
            }
          }
        }
      }

      const createPostInput: PostCreateServiceInput = {
        postType: targetPostType,
        authorId,
        categoryIds: categoryIds.length ? categoryIds : null,
        title,
        description: contentHtml as unknown as Text,
        exerpt: excerptText ? (excerptText as unknown as Text) : null,
        isFeatured: false,
        status,
        visibility,
        isExclusive: false,
        publishedAt,
        postAttachmentIds: postAttachmentIds.length ? postAttachmentIds : null,
        featuredImageId,
        tags: tagIds.length ? tagIds : null,
        metaTitle,
        metaDescription,
        metaKeywords,
        canonicalUrl,
        code: postCode || undefined,
        wpPostId: post.ID,
      };

      try {
        let createdId: number;
        if (existingId) {
          await this.postService.updatePost({
            ...createPostInput,
            id: existingId,
          });
          createdId = existingId;
        } else {
          const created = await this.postService.createPost(createPostInput);
          createdId = created.id;
        }
        importedPosts += 1;
        postIdMap.set(post.ID, createdId);
      } catch (err) {
        if (
          err instanceof AppError &&
          err.messageKey === "POST_CONTENT_ALREADY_EXISTS"
        ) {
          try {
            const code =
              createPostInput.code || Common.slugify(createPostInput.title);
            const existingId = await this.postDao.getIdFromCode(
              code,
              targetPostType,
            );
            await this.postService.updatePost({
              ...createPostInput,
              id: existingId,
            });
            importedPosts += 1;
            postIdMap.set(post.ID, existingId);
          } catch (innerErr) {
            skippedPosts += 1;
            errors.push({
              itemId: post.ID,
              type: "post",
              message: "POST_CONTENT_ALREADY_EXISTS",
            });
          }
        } else if (err instanceof AppError) {
          skippedPosts += 1;
          errors.push({
            itemId: post.ID,
            type: "post",
            message: err.messageKey,
          });
        } else {
          skippedPosts += 1;
          errors.push({
            itemId: post.ID,
            type: "post",
            message: "SOMETHING_WENT_WRONG_IN_SERVICE",
          });
        }
      }
    }

    return { importedPosts, skippedPosts, postIdMap, errors };
  };

  private importComments = async (
    conn: mysql.PoolConnection,
    prefix: string,
    comments: WpCommentRow[],
    commentMetaMap: Map<number, Record<string, string[]>>,
    postIdMap: Map<number, number>,
    defaultUserId: number,
  ): Promise<{ imported: number; errors: WordpressImportErrorItem[] }> => {
    if (!this.commentDao || !this.userDao) {
      throw new AppError(500, "COMMENT_SERVICE_NOT_AVAILABLE", {});
    }

    let imported = 0;
    const errors: WordpressImportErrorItem[] = [];
    const commentIdMap = new Map<number, number>();
    const pending: WpCommentRow[] = [];
    const userEmailToIdMap = new Map<string, number>();

    const authorIds = comments.map((c) => c.user_id).filter((id) => id > 0);
    const authorMap = await this.ensureAuthors(conn, prefix, authorIds);

    // 1. Batch fetch existing comments by wpCommentId
    const wpCommentIds = comments.map(c => c.comment_ID);
    const existingDbComments = await Models.Comment.findAll({
      where: { wpCommentId: wpCommentIds },
      attributes: ["id", "wpCommentId", "parentId"]
    });
    const existingCommentParentIdMap = new Map<number, number | null>();
    for (const ec of existingDbComments) {
      if (ec.wpCommentId) {
        commentIdMap.set(ec.wpCommentId, ec.id);
        existingCommentParentIdMap.set(ec.wpCommentId, ec.parentId || null);
      }
    }

    // 2. Batch fetch parent comments by wpCommentId
    const parentWpCommentIds = Array.from(new Set(comments.map(c => c.comment_parent_id).filter(pid => pid && pid > 0)));
    if (parentWpCommentIds.length > 0) {
      const existingParents = await Models.Comment.findAll({
        where: { wpCommentId: parentWpCommentIds },
        attributes: ["id", "wpCommentId"]
      });
      for (const ep of existingParents) {
        if (ep.wpCommentId) commentIdMap.set(ep.wpCommentId, ep.id);
      }
    }

    // 3. Batch fetch comment author user IDs by email
    const commentEmails = comments
      .map(c => c.comment_author_email ? c.comment_author_email.toString().replace(/["'`\-;<>\\]/g, "").replace(/--/g, "").trim().toLowerCase() : "")
      .filter(Boolean);
    if (commentEmails.length > 0) {
      const existingUsers = await Models.User.findAll({
        where: { email: commentEmails, accountId: this.accountId },
        attributes: ["id", "email"]
      });
      for (const u of existingUsers) {
        if (u.email) {
          userEmailToIdMap.set(u.email.toLowerCase(), u.id);
        }
      }
    }

    const getUserIdByEmail = async (comment: WpCommentRow): Promise<number | null> => {
      if (comment.user_id && Number(comment.user_id) > 0) {
        const localUser = await Models.User.findByPk(Number(comment.user_id), { attributes: ["id"] });
        if (localUser) {
          return localUser.id;
        }
      }

      if (!comment.user_id || Number(comment.user_id) === 0) {
        return null;
      }

      let email = comment.comment_author_email ? comment.comment_author_email.toString() : "";
      email = email.replace(/["'`\-;<>\\]/g, "").replace(/--/g, "").trim();
      const emailLower = email.toLowerCase();

      if (!email) {
        if (authorMap.has(comment.user_id)) {
          return authorMap.get(comment.user_id)!;
        }
        return null;
      }

      if (!(await this.validateUserEmail(email))) {
        return null;
      }

      if (userEmailToIdMap.has(emailLower)) return userEmailToIdMap.get(emailLower)!;

      if (authorMap.has(comment.user_id)) {
        const id = authorMap.get(comment.user_id)!;
        userEmailToIdMap.set(emailLower, id);
        return id;
      }

      const user = await this.userDao!.getUserByEmail(email, null, false, true);
      if (user && user.id) {
        userEmailToIdMap.set(emailLower, user.id);
        return user.id;
      }

      try {
        const roleId = (await this.resolveAuthorRoleId()) || 2;
        const createInput: UserCreateDaoInput = {
          userInfo: {
            email,
            username: email.split("@")[0],
            password: null,
            name: comment.comment_author || email.split("@")[0],
            accountId: this.accountId,
            referralCode: null,
          },
          roles: [roleId],
        };
        const newId = await this.userDao!.createUser(createInput, {});
        userEmailToIdMap.set(emailLower, newId);
        return newId;
      } catch (e) {
        return null;
      }
    };

    const createComment = async (
      comment: WpCommentRow,
      parentId: number | null,
    ): Promise<number | null> => {
      const postId = postIdMap.get(comment.comment_post_ID);
      if (!postId) return null;

      const userId = await getUserIdByEmail(comment);

      const commentObj: CommentInterface & {
        createdAt?: Date;
        updatedAt?: Date;
        userId?: number | null;
      } = {
        userId: userId,
        postId,
        parentId,
        content: comment.comment_content,
        status: this.mapCommentStatus(comment.comment_approved),
        commentAuthorName: comment.comment_author || null,
        commentAuthorEmail: comment.comment_author_email || null,
        wpCommentId: comment.comment_ID,
        createdAt: comment.comment_date ? new Date(comment.comment_date) : undefined,
      };
      try {
        const created = await this.commentDao!.create(
          commentObj as CommentInterface,
        );
        imported += 1;
        return created.id || null;
      } catch (err) {
        errors.push({
          itemId: comment.comment_ID,
          type: "comment",
          message: "ERROR_WHILE_CREATING_COMMENT",
        });
        return null;
      }
    };

    const getParentIdByWpId = async (wpParentId: number): Promise<number | null> => {
      if (!wpParentId || wpParentId === 0) return null;
      if (commentIdMap.has(wpParentId)) return commentIdMap.get(wpParentId)!;

      const existing = await this.commentDao!.getByWpCommentId(wpParentId);
      if (existing?.id) {
        commentIdMap.set(wpParentId, existing.id);
        return existing.id;
      }
      return null;
    };

    for (const comment of comments) {
      const postId = postIdMap.get(comment.comment_post_ID);
      if (!postId) continue;

      if (this.isContentEmptyOrPlaceholder(comment.comment_content)) {
        continue;
      }

      // Check for duplicate using the map instead of a query
      let existingId = commentIdMap.get(comment.comment_ID);
      if (existingId) {
        // Add missing parent ID if available in WP but not in our system
        const wpParentId = comment.comment_parent_id;
        const currentParentId = existingCommentParentIdMap.get(comment.comment_ID);
        if (wpParentId && wpParentId > 0 && !currentParentId) {
          const parentId = await getParentIdByWpId(wpParentId);
          if (parentId) {
            await this.commentDao!.update(existingId, { parentId });
            existingCommentParentIdMap.set(comment.comment_ID, parentId);
          }
        }

        imported += 1;
        continue;
      }

      const wpParentId = comment.comment_parent_id;
      if (!wpParentId || wpParentId === 0) {
        const newId = await createComment(comment, null);
        if (newId) commentIdMap.set(comment.comment_ID, newId);
      } else {
        const parentId = await getParentIdByWpId(wpParentId);
        if (parentId) {
          const newId = await createComment(comment, parentId);
          if (newId) commentIdMap.set(comment.comment_ID, newId);
        } else {
          pending.push(comment);
        }
      }
    }

    let safety = 0;
    while (pending.length && safety < 5) {
      safety += 1;
      const nextPending: WpCommentRow[] = [];
      for (const comment of pending) {
        const parentId = await getParentIdByWpId(comment.comment_parent_id);
        if (parentId) {
          const newId = await createComment(comment, parentId);
          if (newId) commentIdMap.set(comment.comment_ID, newId);
        } else {
          nextPending.push(comment);
        }
      }
      if (nextPending.length === pending.length) break;
      pending.length = 0;
      pending.push(...nextPending);
    }

    for (const comment of pending) {
      // Final attempt: check if duplicate first
      const existingId = commentIdMap.get(comment.comment_ID);
      if (existingId) continue;

      const finalParentId = await getParentIdByWpId(comment.comment_parent_id);
      const newId = await createComment(comment, finalParentId);
      if (newId) commentIdMap.set(comment.comment_ID, newId);
    }

    return { imported, errors };
  };

  /**
   * Recalculate all 5 UserProfile activity count fields from scratch for the given userIds.
   * Used after bulk imports to ensure accuracy.
   */
  private recalculateUserProfileCounts = async (userIds: number[]): Promise<void> => {
    const uniqueIds = Array.from(new Set(userIds.filter(id => id > 0)));
    if (!uniqueIds.length) return;

    for (const userId of uniqueIds) {
      try {
        const [questionCount, answerCount, answerCommentCount, postCommentCount, seaQALikesCount] = await Promise.all([
          // Approved SeaQA questions
          Models.SeaQAQuestion.count({ where: { userId, status: 'approved' } }),
          // Published SeaQA answers
          Models.SeaQAAnswer.count({ where: { userId, status: 'published' } }),
          // SeaQA answer comments
          Models.SeaQAComment.count({ where: { userId } }),
          // Blog post comments (approved)
          Models.Comment.count({ where: { userId, status: 'approved' } }),
          // SeaQA likes (questions + answers) made by this user
          Models.SeaQALike.count({ where: { userId } }),
        ]);

        await Models.UserProfile.update(
          { questionCount, answerCount, answerCommentCount, postCommentCount, seaQALikesCount },
          { where: { userId } }
        );
      } catch (err) {
        // Non-fatal: log but don't fail the import
        console.error(`[recalculateUserProfileCounts] Failed for userId=${userId}:`, err);
      }
    }
  };

  importChunk = async (
    request: WordpressImportRequestObject,
  ): Promise<WordpressImportResponseData> => {

    try {
      const chunkSize = Number(
        request.chunkSize ?? process.env.WP_IMPORT_CHUNK_SIZE ?? 50,
      );
      const page = Number(request.page ?? 1);
      const postTypes =
        request.postTypes && request.postTypes.length
          ? request.postTypes
          : ["post"];
      const targetPostType = request.postType || "post";
      const importPostsFlag = request.importPosts !== false;
      const importCommentsFlag = request.importComments !== false;
      const allowCategories = request.importCategories !== false;
      const allowTags = request.importTags !== false;
      const defaultAuthorId =
        request.authorId ?? (await this.resolveDefaultUserId()) ?? 1;
      const defaultCommentUserId = request.commentUserId ?? defaultAuthorId;

      await this.ensureServices(defaultAuthorId);

      const wpConfig = this.buildWpConfig(request.wpDb);
      const pool = mysql.createPool({
        host: wpConfig.host,
        port: wpConfig.port,
        user: wpConfig.user,
        password: wpConfig.password,
        database: wpConfig.database,
        waitForConnections: true,
        connectionLimit: 5,
        charset: "utf8mb4",
      });

      const conn = await pool.getConnection();

      try {
        const placeholders = postTypes.map(() => "?").join(",");
        const [countRows] = await conn.query(
          `SELECT COUNT(*) as count FROM ${wpConfig.prefix}posts WHERE post_type IN (${placeholders}) AND post_status = 'publish'`,
          [...postTypes],
        );
        const totalRecords = (countRows as any)[0].count;
        const totalPages = Math.ceil(totalRecords / chunkSize);
        const currentPage = page;

        const posts = await this.fetchPosts(
          conn,
          wpConfig.prefix,
          page,
          chunkSize,
          postTypes,
        );
        if (!posts.length) {
          return {
            importedPosts: 0,
            skippedPosts: 0,
            importedComments: 0,
            importedCategories: 0,
            importedTags: 0,
            lastPostId: null,
            hasMore: false,
            totalPages,
            currentPage,
            errors: [],
          };
        }

        console.log(
          `Fetched ${posts.length} posts from WordPress page ${page} (chunk size: ${chunkSize})`,
        );

        const postIds = posts.map((post) => post.ID);
        const postMetaMap = await this.fetchPostMeta(
          conn,
          wpConfig.prefix,
          postIds,
        );
        let authorIdMap = new Map<number, number>();
        let attachments: WpAttachmentRow[] = [];
        let attachmentMetaMap = new Map<number, Record<string, string[]>>();

        if (importPostsFlag) {
          const authorIds = posts.map((post) => post.post_author || 0);
          authorIdMap = await this.ensureAuthors(
            conn,
            wpConfig.prefix,
            authorIds,
          );

          const thumbnailIds: number[] = [];
          for (const postId of postIds) {
            const meta = postMetaMap.get(postId);
            const thumbId = Number(
              this.getMetaValue(meta, ["_thumbnail_id"]) || 0,
            );
            if (thumbId) thumbnailIds.push(thumbId);
          }

          attachments = await this.fetchAttachments(
            conn,
            wpConfig.prefix,
            postIds,
            Array.from(new Set(thumbnailIds)),
          );
          const attachmentIds = Array.from(
            new Set(attachments.map((attachment) => attachment.ID)),
          );
          attachmentMetaMap = await this.fetchPostMeta(
            conn,
            wpConfig.prefix,
            attachmentIds,
          );
          await this.ensureWpUploadBaseUrl(conn, wpConfig.prefix);
        }

        const termRelationships = await this.fetchTermRelationships(
          conn,
          wpConfig.prefix,
          postIds,
        );
        const termTaxonomyIds = termRelationships.map((r) => r.term_taxonomy_id);
        const taxonomyCounts = await this.ensureTaxonomyMaps(
          conn,
          wpConfig.prefix,
          allowCategories,
          allowTags,
          termTaxonomyIds,
        );

        let importedPosts = 0;
        let skippedPosts = 0;
        let importedComments = 0;
        const errors: WordpressImportErrorItem[] = [];
        let postIdMap = new Map<number, number>();

        if (importPostsFlag) {
          const postResult = await this.importPosts(
            posts,
            postMetaMap,
            termRelationships,
            attachments,
            attachmentMetaMap,
            targetPostType,
            authorIdMap,
            defaultAuthorId,
            allowCategories,
            allowTags,
          );
          importedPosts = postResult.importedPosts;
          skippedPosts = postResult.skippedPosts;
          postIdMap = postResult.postIdMap;
          errors.push(...postResult.errors);
        }

        if (importCommentsFlag) {
          const importedPostIds = Array.from(postIdMap.keys());
          if (importedPostIds.length > 0) {
            const comments = await this.fetchComments(
              conn,
              wpConfig.prefix,
              importedPostIds,
            );
            const commentIds = comments.map((comment) => comment.comment_ID);
            const commentMetaMap = await this.fetchCommentMeta(
              conn,
              wpConfig.prefix,
              commentIds,
            );
            const commentResult = await this.importComments(
              conn,
              wpConfig.prefix,
              comments,
              commentMetaMap,
              postIdMap,
              defaultCommentUserId,
            );
            importedComments = commentResult.imported;
            errors.push(...commentResult.errors);
          }
        }

        const lastPostId = posts[posts.length - 1].ID;
        const hasMore = posts.length >= chunkSize;

        // Recalculate UserProfile counts for all authors affected in this chunk
        const affectedUserIds = Array.from(new Set(posts.map(p => p.post_author).filter(Boolean)));
        await this.recalculateUserProfileCounts(
          affectedUserIds.map(id => authorIdMap.get(id) || 0).filter(Boolean)
        );

        return {
          importedPosts,
          skippedPosts,
          importedComments,
          importedCategories: taxonomyCounts.createdCategories,
          importedTags: taxonomyCounts.createdTags,
          lastPostId,
          hasMore,
          totalPages,
          currentPage,
          errors,
        };

      } finally {
        conn.release();
        await pool.end();
      }
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_IN_SERVICE", err);
    }
  };

  public importUsers = async (
    request: UserImportRequestObject,
  ): Promise<UserImportResponseData> => {
    try {
      const { chunkSize = 100, page = 1, wpDb, defaultRoleId, fetchAll } = request;
      const wpConfig = this.buildWpConfig(wpDb);

      const pool = mysql.createPool({
        host: wpConfig.host,
        port: wpConfig.port,
        user: wpConfig.user,
        password: wpConfig.password,
        database: wpConfig.database,
      });

      const conn = await pool.getConnection();

      try {
        await this.ensureServices();
        if (!this.userDao || !this.roleDao || !this.roleService) {
          throw new AppError(500, "USER_SERVICE_NOT_AVAILABLE", {});
        }

        const offset = (page - 1) * chunkSize;
        let totalRecords = 0;
        if (!fetchAll) {
          const [countRows] = await conn.query(
            `SELECT COUNT(*) as count FROM user WHERE deleted_at IS NULL AND status = '1'`
          );
          totalRecords = (countRows as any)[0].count;
        }

        let queryStr = `SELECT u.id, u.user_type, u.slug, u.email, u.password, u.created_at, u.status, u.date_of_birth, u.area_of_expertise, u.firstName, u.lastName, u.profileImage, us.currentRank, us.currentCompany 
           FROM user u
           LEFT JOIN user_settings us ON u.id = us.user_id
           WHERE u.deleted_at IS NULL AND u.status = '1'
           ORDER BY u.id`;
        let queryParams: any[] = [];

        if (!fetchAll) {
          queryStr += ` LIMIT ?, ?`;
          queryParams = [offset, chunkSize];
        }

        const [rows] = await conn.query(queryStr, queryParams);

        const sourceUsers = rows as any[];
        const totalPages = fetchAll ? 1 : Math.ceil(totalRecords / chunkSize);
        const currentPage = fetchAll ? 1 : page;

        if (!sourceUsers.length) {
          return {
            imported: 0,
            skipped: 0,
            errors: 0,
            lastUserId: null,
            hasMore: false,
            totalPages,
            currentPage,
            errorLog: [],
          };
        }

        let imported = 0;
        let skipped = 0;
        let errors = 0;
        const errorLog: { email: string; error: string }[] = [];

        // Get admin and user roles
        let adminRoleId: number | null = null;
        let userRoleId: number | null = null;
        const adminRole = await this.roleDao.getRoleByCode({ code: "admin", expanded: false });
        if (adminRole?.id) {
          adminRoleId = adminRole.id;
        }
        const userRoleObj = await this.roleDao.getRoleByCode({ code: "user", expanded: false });
        if (userRoleObj?.id) {
          userRoleId = userRoleObj.id;
        }

        // Get default fallback roles
        let defaultRoles: number[] = [];
        if (defaultRoleId) {
          defaultRoles = [defaultRoleId];
        } else {
          defaultRoles = await this.roleService.getDafaultRoles();
        }

        const localCategories = await Models.Category.findAll({
          where: { categoryTypeId: 6 },
          attributes: ["id", "code"],
        });

        const localRankCodeToId = new Map<string, number>();
        for (const cat of localCategories) {
          if (cat.code) {
            localRankCodeToId.set(cat.code, cat.id);
          }
        }

        const sanitizedEmails = sourceUsers.map(user => {
          let email = (user.email || "").toString();
          return email.replace(/["'`\-;<>\\]/g, "").replace(/--/g, "").trim().toLowerCase();
        }).filter(Boolean);

        const existingUsers = sanitizedEmails.length > 0
          ? await Models.User.findAll({
            where: { email: sanitizedEmails, accountId: this.accountId },
            attributes: ["id", "email"]
          })
          : [];

        const existingUsersMap = new Map<string, number>();
        for (const u of existingUsers) {
          if (u.email) {
            existingUsersMap.set(u.email.toLowerCase(), u.id);
          }
        }

        for (const user of sourceUsers) {
          try {
            if (user.id === 1) {
              skipped++;
              continue;
            }

            let email = (user.email || "").toString();
            // remove SQL injection attempts and quotes (spam check as in PHP script)
            email = email.replace(/["'`\-;<>\\]/g, "").replace(/--/g, "").trim();

            if (!email || !(await this.validateUserEmail(email))) {
              skipped++;
              continue;
            }

            let mappedRankId: number | null = null;
            const remoteRankId = user.currentRank;
            if (remoteRankId && REMOTE_RANK_MAP[remoteRankId]) {
              const slugCode = REMOTE_RANK_MAP[remoteRankId];
              mappedRankId = localRankCodeToId.get(slugCode) || null;
            }

            // Check if exists
            const existingId = existingUsersMap.get(email.toLowerCase());
            if (existingId) {
              const profileUpdates: any = {};
              if (mappedRankId) profileUpdates.rankId = mappedRankId;
              if (user.currentCompany) profileUpdates.presentCompany = user.currentCompany;
              if (Object.keys(profileUpdates).length > 0) {
                await Models.UserProfile.update(
                  profileUpdates,
                  { where: { userId: existingId } }
                );
              }
              skipped++;
              continue;
            }

            const username = user.slug || email.split("@")[0];
            let displayName = "";
            if (user.firstName && user.firstName.trim()) {
              displayName = user.firstName.trim();
              if (user.lastName && user.lastName.trim()) {
                const lastNameParts = user.lastName.trim().split(/\s+/);
                const lastWord = lastNameParts[lastNameParts.length - 1];
                if (lastWord) {
                  displayName += " " + lastWord;
                }
              }
            } else {
              displayName = user.slug
                ? user.slug
                  .split(/[-_]/)
                  .map(
                    (s: string) => s.charAt(0).toUpperCase() + s.slice(1),
                  )
                  .join(" ")
                : email.split("@")[0];
            }

            let profileImageId: number | null = null;
            if (user.profileImage && user.profileImage.trim()) {
              const profileImageUrl = `https://myseatime.com/upload/profile/${user.profileImage.trim()}`;
              profileImageId = await this.importAttachmentFromUrl(profileImageUrl);
            }

            let userRoleIds: number[] = [];
            if (user.user_type === 1 || user.user_type === '1') {
              userRoleIds = adminRoleId ? [adminRoleId] : defaultRoles;
            } else {
              userRoleIds = userRoleId ? [userRoleId] : defaultRoles;
            }

            const userTypeMap: Record<string, string> = {
              "1": "admin",
              "2": "employee",
              "3": "employer",
              "4": "institute",
              "5": "Seafarers working As",
              "6": "Others",
              "7": "EmployerTeam",
            };
            const mappedUserType = user.user_type !== null && user.user_type !== undefined
              ? (userTypeMap[user.user_type.toString()] || null)
              : null;

            const createInput: UserCreateDaoInput = {
              userInfo: {
                id: user.id,
                email,
                username,
                password: user.password, // Maintain same hash (MD5 or Bcrypt)
                name: displayName,
                firstName: user.firstName ? user.firstName.trim() : null,
                lastName: user.lastName ? user.lastName.trim() : null,
                accountId: this.accountId,
                referralCode: null,
                dob: user.date_of_birth || null,
                areaOfExpertise: user.area_of_expertise || null,
                userType: mappedUserType,
                rankId: mappedRankId,
                presentCompany: user.currentCompany || null,
                profileImageId: profileImageId,
              },
              roles: userRoleIds,
            };

            await this.userDao.createUser(createInput, {});
            imported++;
          } catch (err: any) {
            errors++;
            errorLog.push({ email: user.email, error: err.message });
          }
        }

        const lastUserId = sourceUsers[sourceUsers.length - 1].id;
        const hasMore = fetchAll ? false : sourceUsers.length >= chunkSize;

        return {
          imported,
          skipped,
          errors,
          lastUserId,
          hasMore,
          totalPages,
          currentPage,
          errorLog,
        };
      } finally {
        conn.release();
        await pool.end();
      }
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_IN_SERVICE", err);
    }
  };

  public importSeaQA = async (
    request: SeaQAImportRequestObject,
  ): Promise<SeaQAImportResponseData> => {
    try {
      const { chunkSize = 100, page = 1, wpDb } = request;
      const wpConfig = this.buildWpConfig(wpDb);

      const pool = mysql.createPool({
        host: wpConfig.host,
        port: wpConfig.port,
        user: wpConfig.user,
        password: wpConfig.password,
        database: wpConfig.database,
      });

      const conn = await pool.getConnection();

      try {
        await this.ensureServices();

        let importedQuestions = 0;
        let skippedQuestions = 0;
        let importedAnswers = 0;
        let skippedAnswers = 0;
        let importedComments = 0;
        let skippedComments = 0;
        let errors = 0;
        const errorLog: any[] = [];

        const [countRows] = await conn.query(
          `SELECT COUNT(*) as count FROM knowledge_base_questions WHERE status IN ('0', '1')`
        );
        const totalRecords = (countRows as any)[0].count;
        const totalPages = Math.ceil(totalRecords / chunkSize);
        const currentPage = page;

        const offset = (page - 1) * chunkSize;
        const [rows] = await conn.query(
          `SELECT id, user_id, title, slug, detail, created, viewcounter, status 
           FROM knowledge_base_questions 
           WHERE status IN ('0', '1')
           ORDER BY id 
           LIMIT ?, ?`,
          [offset, chunkSize]
        );

        const sourceQuestions = rows as any[];
        if (!sourceQuestions.length) {
          return {
            importedQuestions, skippedQuestions,
            importedAnswers, skippedAnswers,
            importedComments, skippedComments,
            errors, hasMore: false,
            totalPages, currentPage,
            errorLog
          };
        }

        const authorIdsSet = new Set<number>();
        for (const q of sourceQuestions) {
          if (q.user_id) authorIdsSet.add(q.user_id);
        }

        const qIds = sourceQuestions.map(q => q.id);
        if (qIds.length > 0) {
          const qIdsPlaceholders = qIds.map(() => "?").join(",");
          const [ansUserRows] = await conn.query(
            `SELECT DISTINCT user_id FROM knowledge_base_answers WHERE question_id IN (${qIdsPlaceholders})`,
            [...qIds]
          );
          for (const row of ansUserRows as any[]) {
            if (row.user_id) authorIdsSet.add(row.user_id);
          }

          const [commentUserRows] = await conn.query(
            `SELECT DISTINCT user_id FROM knowledgebase_comments WHERE answer_id IN (
              SELECT id FROM knowledge_base_answers WHERE question_id IN (${qIdsPlaceholders})
            )`,
            [...qIds]
          );
          for (const row of commentUserRows as any[]) {
            if (row.user_id) authorIdsSet.add(row.user_id);
          }

          const [likeUserRows] = await conn.query(
            `SELECT DISTINCT user_id FROM knowledge_like_dislike_status WHERE answer_id IN (
              SELECT id FROM knowledge_base_answers WHERE question_id IN (${qIdsPlaceholders})
            ) AND likestatus = '1'`,
            [...qIds]
          );
          for (const row of likeUserRows as any[]) {
            if (row.user_id) authorIdsSet.add(row.user_id);
          }
        }

        const authorIds = Array.from(authorIdsSet).filter(id => id > 0);
        const authorIdMap = await this.ensureSeaQAUsers(conn, authorIds);

        const affectedUserIds = new Set<number>();
        const resolveUserId = (remoteId: number | null | undefined): number => {
          if (!remoteId) return this.userId || 1;
          const mapped = authorIdMap.get(remoteId);
          if (mapped) {
            affectedUserIds.add(mapped);
            return mapped;
          }
          const fallback = this.userId || 1;
          affectedUserIds.add(fallback);
          return fallback;
        };

        const languageId = 1;

        // Warm up categoryCodeCache for SeaQA
        const seaqaTypeObj = await Models.CategoryType.findOne({ where: { code: "seaqa" }, attributes: ["id"] });
        if (seaqaTypeObj) {
          const existingSeaQACategories = await Models.Category.findAll({
            where: { categoryTypeId: seaqaTypeObj.id },
            attributes: ["id", "code"]
          });
          for (const cat of existingSeaQACategories) {
            if (cat.code) {
              this.categoryCodeCache.set(cat.code, cat.id);
            }
          }
        }

        // Batch lookup existing local questions by slug/code
        const questionSlugs = sourceQuestions.map(q => {
          let s = q.slug || `question-${q.id}`;
          if (s.length > 200) {
            s = `${s.substring(0, 200)}-${q.id}`;
          }
          return s;
        });
        const existingQuestions = await Models.SeaQAQuestion.findAll({
          where: { code: questionSlugs },
          attributes: ["id", "code"]
        });
        const existingQuestionsMap = new Map<string, number>();
        for (const eq of existingQuestions) {
          existingQuestionsMap.set(eq.code, eq.id);
        }

        // Batch lookup remote topics for all chunk questions
        const remoteQuestionTopicsMap = new Map<number, { id: number, title: string, slug: string }[]>();
        if (qIds.length > 0) {
          const qIdsPlaceholders = qIds.map(() => "?").join(",");
          const [topicRows] = await conn.query(
            `SELECT qt.q_id, t.id, t.title, t.slug 
             FROM knowledge_base_topics t
             JOIN knowledge_base_question_topics qt ON t.id = qt.topic_id
             WHERE qt.q_id IN (${qIdsPlaceholders})`,
            [...qIds]
          );
          for (const row of topicRows as any[]) {
            if (!remoteQuestionTopicsMap.has(row.q_id)) {
              remoteQuestionTopicsMap.set(row.q_id, []);
            }
            remoteQuestionTopicsMap.get(row.q_id)!.push({
              id: row.id,
              title: row.title,
              slug: row.slug
            });
          }
        }

        // Batch lookup remote answers for all chunk questions
        const remoteAnswersMap = new Map<number, any[]>();
        if (qIds.length > 0) {
          const qIdsPlaceholders = qIds.map(() => "?").join(",");
          const [ansRows] = await conn.query(
            `SELECT id, question_id, user_id, answer, created, featured, likes, dislikes, status 
             FROM knowledge_base_answers 
             WHERE question_id IN (${qIdsPlaceholders})
             ORDER BY id ASC`,
            [...qIds]
          );
          for (const row of ansRows as any[]) {
            if (!remoteAnswersMap.has(row.question_id)) {
              remoteAnswersMap.set(row.question_id, []);
            }
            remoteAnswersMap.get(row.question_id)!.push(row);
          }
        }

        // Collect remote answer IDs to fetch remote comments
        const remoteAnswerIds: number[] = [];
        for (const [qId, ansList] of remoteAnswersMap.entries()) {
          for (const ans of ansList) {
            remoteAnswerIds.push(ans.id);
          }
        }

        // Batch lookup remote comments for all chunk answers
        const remoteCommentsMap = new Map<number, any[]>();
        if (remoteAnswerIds.length > 0) {
          const ansIdsPlaceholders = remoteAnswerIds.map(() => "?").join(",");
          const [commentRows] = await conn.query(
            `SELECT id, answer_id, parent_id, user_id, detail, created 
             FROM knowledgebase_comments 
             WHERE answer_id IN (${ansIdsPlaceholders})
             ORDER BY id ASC`,
            [...remoteAnswerIds]
          );
          for (const row of commentRows as any[]) {
            if (!remoteCommentsMap.has(row.answer_id)) {
              remoteCommentsMap.set(row.answer_id, []);
            }
            remoteCommentsMap.get(row.answer_id)!.push(row);
          }
        }

        // Batch lookup remote likes for all chunk answers
        const remoteLikes: any[] = [];
        if (remoteAnswerIds.length > 0) {
          const ansIdsPlaceholders = remoteAnswerIds.map(() => "?").join(",");
          const [likeRows] = await conn.query(
            `SELECT user_id, answer_id FROM knowledge_like_dislike_status WHERE answer_id IN (${ansIdsPlaceholders}) AND likestatus = '1'`,
            [...remoteAnswerIds]
          );
          remoteLikes.push(...(likeRows as any[]));
        }

        const remoteToLocalAnswerIdMap = new Map<number, number>();

        // Batch lookup existing local answers for existing local questions
        const existingQuestionIds = Array.from(existingQuestionsMap.values());
        const existingLocalAnswersMap = new Map<string, number>(); // key: `${questionId}-${userId}-${createdAtTime}` -> answerId
        if (existingQuestionIds.length > 0) {
          const localAnswers = await Models.SeaQAAnswer.findAll({
            where: { questionId: existingQuestionIds },
            attributes: ["id", "questionId", "userId", "createdAt"]
          });
          for (const la of localAnswers) {
            const timeStr = la.createdAt instanceof Date ? la.createdAt.getTime() : new Date(la.createdAt).getTime();
            const key = `${la.questionId}-${la.userId}-${timeStr}`;
            existingLocalAnswersMap.set(key, la.id);
          }
        }

        // Batch lookup existing local comments for all existing local answers
        const existingLocalAnswerIds = Array.from(existingLocalAnswersMap.values());
        const existingLocalCommentsMap = new Map<string, number>(); // key: `${answerId}-${userId}-${createdAtTime}` -> commentId
        if (existingLocalAnswerIds.length > 0) {
          const localComments = await Models.SeaQAComment.findAll({
            where: { answerId: existingLocalAnswerIds },
            attributes: ["id", "answerId", "userId", "createdAt"]
          });
          for (const lc of localComments) {
            const timeStr = lc.createdAt instanceof Date ? lc.createdAt.getTime() : new Date(lc.createdAt).getTime();
            const key = `${lc.answerId}-${lc.userId}-${timeStr}`;
            existingLocalCommentsMap.set(key, lc.id);
          }
        }

        // Keep track of questions that had answers added or updated so we can update answersCount at the end
        const affectedQuestionIdsForCountUpdate = new Set<number>();

        for (const q of sourceQuestions) {
          try {
            let slug = q.slug || `question-${q.id}`;
            if (slug.length > 200) {
              slug = `${slug.substring(0, 200)}-${q.id}`;
            }
            let existingQId = existingQuestionsMap.get(slug);
            let newQuestionId: number;

            if (existingQId) {
              skippedQuestions++;
              newQuestionId = existingQId;
            } else {
              const descriptionText = q.detail ? await Common.convertHtmlToText(q.detail) : '';

              let qStatus = 'pending';
              if (q.status === 1 || q.status === '1') qStatus = 'approved';

              const newQ = await Models.SeaQAQuestion.create({
                userId: resolveUserId(q.user_id),
                accountId: this.accountId,
                code: slug,
                status: qStatus,
                isGoodQuestion: false,
                views: q.viewcounter || 0,
                createdAt: q.created,
              });

              await Models.SeaQAQuestionContent.create({
                questionId: newQ.id,
                languageId,
                title: q.title || '',
                description: q.detail || '',
                descriptionText
              });

              importedQuestions++;
              newQuestionId = newQ.id;

              const topics = remoteQuestionTopicsMap.get(q.id) || [];
              const categoryIdsToLink: number[] = [];

              for (const topic of topics) {
                const catResult = await this.ensureCategory(
                  "seaqa",
                  topic.title,
                  "",
                  true
                );
                if (catResult.id) {
                  categoryIdsToLink.push(catResult.id);
                }
              }

              if (categoryIdsToLink.length > 0) {
                await newQ.setCategories(categoryIdsToLink);
              }
            }

            const answers = remoteAnswersMap.get(q.id) || [];
            for (const ans of answers) {
              try {
                if (!ans.user_id || !authorIdMap.has(ans.user_id)) {
                  skippedAnswers++;
                  continue;
                }
                if (this.isContentEmptyOrPlaceholder(ans.answer)) {
                  skippedAnswers++;
                  continue;
                }
                const mappedAnsUserId = resolveUserId(ans.user_id);
                const ansTime = ans.created instanceof Date ? ans.created.getTime() : new Date(ans.created).getTime();
                const localAnsKey = `${newQuestionId}-${mappedAnsUserId}-${ansTime}`;

                let existingAnsId = existingLocalAnswersMap.get(localAnsKey);
                let newAnswerId: number;

                if (existingAnsId) {
                  skippedAnswers++;
                  newAnswerId = existingAnsId;
                  await Models.SeaQAAnswer.update(
                    { likesCount: ans.likes || 0 },
                    { where: { id: existingAnsId } }
                  );
                } else {
                  let ansStatus = 'pending_approval';
                  if (ans.status === 1 || ans.status === '1') ansStatus = 'published';
                  else if (ans.status === 2 || ans.status === '2') ansStatus = 'draft';

                  const newA = await Models.SeaQAAnswer.create({
                    questionId: newQuestionId,
                    userId: mappedAnsUserId,
                    accountId: this.accountId,
                    status: ansStatus,
                    isFeatured: ans.featured == 1,
                    likesCount: ans.likes || 0,
                    createdAt: ans.created
                  });

                  await Models.SeaQAAnswerContent.create({
                    answerId: newA.id,
                    languageId,
                    body: ans.answer || ''
                  });

                  importedAnswers++;
                  newAnswerId = newA.id;
                  existingLocalAnswersMap.set(localAnsKey, newA.id);
                }

                remoteToLocalAnswerIdMap.set(ans.id, newAnswerId);

                const comments = remoteCommentsMap.get(ans.id) || [];
                const commentIdMap = new Map<number, number>();

                for (const c of comments) {
                  try {
                    if (this.isContentEmptyOrPlaceholder(c.detail)) {
                      skippedComments++;
                      continue;
                    }
                    const mappedCommentUserId = resolveUserId(c.user_id);
                    const commentTime = c.created instanceof Date ? c.created.getTime() : new Date(c.created).getTime();
                    const localCommentKey = `${newAnswerId}-${mappedCommentUserId}-${commentTime}`;

                    let existingCommentId = existingLocalCommentsMap.get(localCommentKey);

                    if (existingCommentId) {
                      skippedComments++;
                      commentIdMap.set(c.id, existingCommentId);
                    } else {
                      let mappedParentId: number | null = null;
                      if (c.parent_id && commentIdMap.has(c.parent_id)) {
                        mappedParentId = commentIdMap.get(c.parent_id) || null;
                      }

                      const newC = await Models.SeaQAComment.create({
                        answerId: newAnswerId,
                        userId: mappedCommentUserId,
                        content: c.detail || '',
                        parentId: mappedParentId,
                        createdAt: c.created
                      });
                      importedComments++;
                      commentIdMap.set(c.id, newC.id);
                      existingLocalCommentsMap.set(localCommentKey, newC.id);
                    }
                  } catch (cErr: any) {
                    errors++;
                    errorLog.push({ entityId: c.id, type: 'comment', error: cErr.message });
                  }
                }
              } catch (aErr: any) {
                errors++;
                errorLog.push({ entityId: ans.id, type: 'answer', error: aErr.message });
              }
            }

            if (answers.length > 0) {
              affectedQuestionIdsForCountUpdate.add(newQuestionId);
            }
          } catch (qErr: any) {
            errors++;
            errorLog.push({ entityId: q.id, type: 'question', error: qErr.message });
          }
        }

        // After the loop, batch update answersCount for all affected questions
        const questionsToUpdate = Array.from(affectedQuestionIdsForCountUpdate);
        if (questionsToUpdate.length > 0) {
          const counts = await Models.SeaQAAnswer.findAll({
            where: { questionId: questionsToUpdate, status: "published" },
            attributes: ["questionId", [Sequelize.fn("COUNT", Sequelize.col("id")), "count"]],
            group: ["questionId"]
          });
          const countMap = new Map<number, number>();
          for (const item of counts as any[]) {
            countMap.set(Number(item.questionId), Number(item.getDataValue("count")));
          }
          for (const qId of questionsToUpdate) {
            const count = countMap.get(qId) || 0;
            await Models.SeaQAQuestion.update(
              { answersCount: count },
              { where: { id: qId } }
            );
          }
        }

        // Import likes for the answers
        const localAnswerIds = Array.from(remoteToLocalAnswerIdMap.values());
        if (localAnswerIds.length > 0 && remoteLikes.length > 0) {
          const existingLikes = await Models.SeaQALike.findAll({
            where: {
              entityId: localAnswerIds,
              entityType: "answer"
            },
            attributes: ["userId", "entityId"]
          });

          const existingLikesSet = new Set<string>();
          for (const el of existingLikes) {
            existingLikesSet.add(`${el.userId}-${el.entityId}`);
          }

          for (const like of remoteLikes) {
            const localAnsId = remoteToLocalAnswerIdMap.get(like.answer_id);
            if (!localAnsId) continue;

            const localUId = resolveUserId(like.user_id);
            const key = `${localUId}-${localAnsId}`;
            if (!existingLikesSet.has(key)) {
              await Models.SeaQALike.create({
                userId: localUId,
                entityId: localAnsId,
                entityType: "answer"
              });
              existingLikesSet.add(key);
            }
          }
        }

        const hasMore = sourceQuestions.length >= chunkSize;

        // Recalculate UserProfile counts for all authors affected in this SeaQA import chunk
        const allAffectedUserIds = Array.from(affectedUserIds);
        await this.recalculateUserProfileCounts(allAffectedUserIds);

        return {
          importedQuestions, skippedQuestions,
          importedAnswers, skippedAnswers,
          importedComments, skippedComments,
          errors, hasMore,
          totalPages, currentPage,
          errorLog
        };

      } finally {
        conn.release();
        await pool.end();
      }
    } catch (err) {
      if (err instanceof AppError) {
        throw err;
      }
      throw new AppError(500, "SOMETHING_WENT_WRONG_IN_SERVICE", err);
    }
  };
}