const AUTHORIZATION_TYPE_ID = 9;

function normalizeText(value) {
  return String(value || "").trim().toLowerCase();
}

function isAuthorizationQuestionType(typeId, typeName) {
  const id = Number(typeId);
  if (Number.isFinite(id) && id === AUTHORIZATION_TYPE_ID) {
    return true;
  }
  const name = normalizeText(typeName);
  return name.includes("author") || name.includes("sign");
}

function isHandoverAccepterLabel(questionText, roleOption) {
  const q = normalizeText(questionText);
  const role = normalizeText(roleOption);
  return (
    q.includes("permit accepter") ||
    role.includes("permit accepter") ||
    (q.includes("new") && q.includes("permit")) ||
    (role.includes("new") && role.includes("permit"))
  );
}

function parseSignoffAnswer(raw) {
  if (raw == null || raw === "") {
    return { name: "", company: "", user_id: null };
  }
  let parsed = raw;
  if (typeof raw === "string") {
    try {
      parsed = JSON.parse(raw);
    } catch {
      return { name: String(raw).trim(), company: "", user_id: null };
    }
  }
  if (!parsed || typeof parsed !== "object") {
    return { name: "", company: "", user_id: null };
  }
  const userId = Number(parsed.user_id);
  return {
    name: String(parsed.name ?? "").trim(),
    company: String(parsed.company ?? "").trim(),
    user_id: Number.isInteger(userId) && userId > 0 ? userId : null
  };
}

async function lookupUserIdByName(conn, name) {
  const trimmed = String(name || "").trim();
  if (!trimmed) return null;

  const [rows] = await conn.query(
    `SELECT user_id
     FROM users
     WHERE employee_name = ? OR email_id = ?
     ORDER BY user_id ASC
     LIMIT 1`,
    [trimmed, trimmed]
  );
  const userId = Number(rows[0]?.user_id);
  return Number.isInteger(userId) && userId > 0 ? userId : null;
}

/**
 * Resolve the handover recipient from authorization / sign-off answers.
 * @param {import("mysql2/promise").PoolConnection} conn
 * @param {number[]} questionIds
 * @param {Map<number, string>} answerByQuestionId
 */
export async function resolveHandoverRecipientUserId(conn, questionIds, answerByQuestionId) {
  const ids = [...new Set((questionIds || []).map((id) => Number(id)).filter((id) => id > 0))];
  if (!ids.length) return null;

  const placeholders = ids.map(() => "?").join(", ");
  const [questionRows] = await conn.query(
    `SELECT q.question_id, q.question, q.question_type_id, qt.question_type,
            (
              SELECT o._options
              FROM ptw_question_options o
              WHERE o.question_id = q.question_id
              ORDER BY o.question_options_id ASC
              LIMIT 1
            ) AS role_option
     FROM ptw_questions q
     LEFT JOIN ptw_question_type qt ON qt.question_type_id = q.question_type_id
     WHERE q.question_id IN (${placeholders})`,
    ids
  );

  for (const row of questionRows) {
    if (!isAuthorizationQuestionType(row.question_type_id, row.question_type)) {
      continue;
    }
    if (!isHandoverAccepterLabel(row.question, row.role_option)) {
      continue;
    }

    const qid = Number(row.question_id);
    const signoff = parseSignoffAnswer(answerByQuestionId.get(qid));
    if (signoff.user_id) {
      return signoff.user_id;
    }
    const matched = await lookupUserIdByName(conn, signoff.name);
    if (matched) {
      return matched;
    }
  }

  return null;
}

/**
 * @param {import("mysql2/promise").PoolConnection} conn
 */
export async function createHandoverNotification(conn, {
  recipientUserId,
  permitId,
  submissionId,
  handedByUserId,
  permitNumber
}) {
  const recipientId = Number(recipientUserId);
  const permit = Number(permitId);
  const submission = Number(submissionId);
  const handedBy = Number(handedByUserId);

  if (!Number.isInteger(recipientId) || recipientId <= 0) return null;
  if (!Number.isInteger(permit) || permit <= 0) return null;
  if (!Number.isInteger(submission) || submission <= 0) return null;
  if (Number.isInteger(handedBy) && handedBy > 0 && handedBy === recipientId) {
    return null;
  }

  const label = String(permitNumber || "").trim() || `PTW-${permit}`;
  const message = `Permit ${label} was handed over to you. Open it to review and accept responsibility.`;

  const [result] = await conn.query(
    `INSERT INTO ptw_handover_notifications
      (recipient_user_id, permit_id, submission_id, handed_by_user_id, permit_number, message)
     VALUES (?, ?, ?, ?, ?, ?)`,
    [
      recipientId,
      permit,
      submission,
      Number.isInteger(handedBy) && handedBy > 0 ? handedBy : null,
      String(permitNumber || "").trim() || null,
      message
    ]
  );

  return Number(result.insertId) || null;
}

/**
 * @param {import("mysql2/promise").PoolConnection} conn
 * @param {{ permitId: number, submissionId: number, submitterId: number, permitNumber?: string, answers: object[] }} payload
 */
export async function notifyHandoverRecipient(conn, payload) {
  const answerByQuestionId = new Map();
  for (const ans of payload.answers || []) {
    const qid = Number(ans?.question_id);
    if (!Number.isInteger(qid) || qid <= 0) continue;
    answerByQuestionId.set(qid, ans.answer_value ?? null);
  }

  const questionIds = [...answerByQuestionId.keys()];
  const recipientUserId = await resolveHandoverRecipientUserId(conn, questionIds, answerByQuestionId);
  if (!recipientUserId) return null;

  return createHandoverNotification(conn, {
    recipientUserId,
    permitId: payload.permitId,
    submissionId: payload.submissionId,
    handedByUserId: payload.submitterId,
    permitNumber: payload.permitNumber
  });
}
