const pool = require("../config/db");
const { getAllRpnSeverity, getAllRpnLikelihood } = require("./rpnRepository");
const { buildAuditFilter } = require("../utils/auditFilterHelper");

const MONTH_NAMES = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];

/** Legacy rows may have NULL created_at; observed_at is the business date for charts. */
const AUDIT_DATE = "COALESCE(a.created_at, a.observed_at)";
const AUDIT_DATE_BARE = "COALESCE(created_at, observed_at)";
const SUBMISSION_DATE = "COALESCE(s.created_at, COALESCE(a.created_at, a.observed_at))";

/** Normalize DB date (Date or string) to YYYY-MM-DD for map keys. */
const toDateKey = (d) => {
  if (!d) return '';
  if (typeof d === 'string') return d.slice(0, 10);
  if (d instanceof Date) return d.toISOString().slice(0, 10);
  return String(d).slice(0, 10);
};

/** @param {number} months */
const getSubmissionsByMonth = async (months = 6) => {
  const [rows] = await pool.query(
    `
    SELECT 
      YEAR(${SUBMISSION_DATE}) as y,
      MONTH(${SUBMISSION_DATE}) as m,
      COUNT(*) as count
    FROM sat_submissions s
    INNER JOIN sat_audits a ON s.audit_id = a.audit_id
    WHERE ${SUBMISSION_DATE} >= DATE_SUB(CURDATE(), INTERVAL ? MONTH)
    GROUP BY YEAR(${SUBMISSION_DATE}), MONTH(${SUBMISSION_DATE})
    ORDER BY y, m
  `,
    [months]
  );
  return rows;
};

/** @param {number} months */
const getAnswersByMonth = async (months = 6) => {
  const [rows] = await pool.query(
    `
    SELECT 
      YEAR(${SUBMISSION_DATE}) as y,
      MONTH(${SUBMISSION_DATE}) as m,
      COUNT(a2.answers_id) as count
    FROM sat_submissions s
    INNER JOIN sat_audits a ON s.audit_id = a.audit_id
    LEFT JOIN sat_answers a2 ON a2.submission_id = s.submission_id
    WHERE ${SUBMISSION_DATE} >= DATE_SUB(CURDATE(), INTERVAL ? MONTH)
    GROUP BY YEAR(${SUBMISSION_DATE}), MONTH(${SUBMISSION_DATE})
    ORDER BY y, m
  `,
    [months]
  );
  return rows;
};

/** @param {number} months */
const getAuditsByMonth = async (months = 6) => {
  const [rows] = await pool.query(
    `
    SELECT 
      YEAR(${AUDIT_DATE}) as y,
      MONTH(${AUDIT_DATE}) as m,
      COUNT(*) as count
    FROM sat_audits a
    WHERE ${AUDIT_DATE} >= DATE_SUB(CURDATE(), INTERVAL ? MONTH)
    GROUP BY YEAR(${AUDIT_DATE}), MONTH(${AUDIT_DATE})
    ORDER BY y, m
  `,
    [months]
  );
  return rows;
};

/**
 * Submissions per day for the last N days. Optional location filter.
 */
const getSubmissionsByDay = async (days, filter = null) => {
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const joinAudit = filterClause ? 'INNER JOIN sat_audits a ON s.audit_id = a.audit_id' : 'INNER JOIN sat_audits a ON s.audit_id = a.audit_id';
  const [rows] = await pool.query(
    `
    SELECT 
      DATE(${SUBMISSION_DATE}) as d,
      COUNT(*) as count
    FROM sat_submissions s
    ${joinAudit}
    WHERE ${SUBMISSION_DATE} >= DATE_SUB(CURDATE(), INTERVAL ? DAY)${filterClause}
    GROUP BY DATE(${SUBMISSION_DATE})
    ORDER BY d
  `,
    [days, ...filterParams]
  );
  return rows;
};

/**
 * Answers per day for the last N days. Optional location filter.
 */
const getAnswersByDay = async (days, filter = null) => {
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const joinAudit = 'INNER JOIN sat_audits a ON s.audit_id = a.audit_id';
  const [rows] = await pool.query(
    `
    SELECT 
      DATE(${SUBMISSION_DATE}) as d,
      COUNT(ans.answers_id) as count
    FROM sat_submissions s
    LEFT JOIN sat_answers ans ON ans.submission_id = s.submission_id
    ${joinAudit}
    WHERE ${SUBMISSION_DATE} >= DATE_SUB(CURDATE(), INTERVAL ? DAY)${filterClause}
    GROUP BY DATE(${SUBMISSION_DATE})
    ORDER BY d
  `,
    [days, ...filterParams]
  );
  return rows;
};

/**
 * Audits per day for the last N days. Optional location filter.
 */
const getAuditsByDay = async (days, filter = null) => {
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const [rows] = await pool.query(
    `
    SELECT 
      DATE(${AUDIT_DATE}) as d,
      COUNT(*) as count
    FROM sat_audits a
    WHERE ${AUDIT_DATE} >= DATE_SUB(CURDATE(), INTERVAL ? DAY)${filterClause}
    GROUP BY DATE(${AUDIT_DATE})
    ORDER BY d
  `,
    [days, ...filterParams]
  );
  return rows;
};

/**
 * Audit trends for a specific year and period. Optional location filter.
 * @param {number} year - e.g. 2026
 * @param {string} period - 'daily' | 'monthly' | 'quarterly' | 'yearly'
 * @param {object} filter - Optional { country_id, location_id, department_id }.
 */
const getAuditTrends = async (year, period, filter = null) => {
  const y = Number(year) || new Date().getFullYear();
  const p = String(period || 'monthly').toLowerCase();
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const audFrom = filterClause ? 'sat_audits a' : 'sat_audits';
  const audMonth = filterClause ? `MONTH(${AUDIT_DATE})` : `MONTH(${AUDIT_DATE_BARE})`;
  const audYearCol = filterClause ? `YEAR(${AUDIT_DATE})` : `YEAR(${AUDIT_DATE_BARE})`;
  const subJoin = ' INNER JOIN sat_audits a ON s.audit_id = a.audit_id';
  const subWhere = filterClause ? ` WHERE YEAR(${SUBMISSION_DATE}) = ?${filterClause}` : ` WHERE YEAR(${SUBMISSION_DATE}) = ?`;
  const subParams = filterClause ? [y, ...filterParams] : [y];
  const audParams = filterClause ? [y, ...filterParams] : [y];

  if (p === 'monthly') {
    const [audRows, subRows, ansRows] = await Promise.all([
      pool.query(
        `SELECT ${audMonth} as m, COUNT(*) as count FROM ${audFrom} WHERE ${audYearCol} = ?${filterClause} GROUP BY m ORDER BY m`,
        audParams
      ).then(([r]) => r).catch(() => []),
      pool.query(
        `SELECT MONTH(${SUBMISSION_DATE}) as m, COUNT(*) as count FROM sat_submissions s${subJoin}${subWhere} GROUP BY MONTH(${SUBMISSION_DATE}) ORDER BY m`,
        subParams
      ).then(([r]) => r).catch(() => []),
      pool.query(
        `SELECT MONTH(${SUBMISSION_DATE}) as m, COUNT(ans.answers_id) as count
         FROM sat_submissions s LEFT JOIN sat_answers ans ON ans.submission_id = s.submission_id${subJoin}
         ${subWhere} GROUP BY MONTH(${SUBMISSION_DATE}) ORDER BY m`,
        subParams
      ).then(([r]) => r).catch(() => []),
    ]);
    const audMap = new Map((audRows || []).map((r) => [r.m, r.count]));
    const subMap = new Map((subRows || []).map((r) => [r.m, r.count]));
    const ansMap = new Map((ansRows || []).map((r) => [r.m, r.count]));
    return [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12].map((m) => ({
      month: MONTH_NAMES[m - 1],
      sat_audits: audMap.get(m) || 0,
      sat_submissions: subMap.get(m) || 0,
      sat_answers: ansMap.get(m) || 0,
    }));
  }

  if (p === 'quarterly') {
    const audQ = filterClause ? `QUARTER(${AUDIT_DATE})` : `QUARTER(${AUDIT_DATE_BARE})`;
    const [audRows, subRows, ansRows] = await Promise.all([
      pool.query(
        `SELECT ${audQ} as q, COUNT(*) as count FROM ${audFrom} WHERE ${audYearCol} = ?${filterClause} GROUP BY q ORDER BY q`,
        audParams
      ).then(([r]) => r).catch(() => []),
      pool.query(
        `SELECT QUARTER(${SUBMISSION_DATE}) as q, COUNT(*) as count FROM sat_submissions s${subJoin}${subWhere} GROUP BY QUARTER(${SUBMISSION_DATE}) ORDER BY q`,
        subParams
      ).then(([r]) => r).catch(() => []),
      pool.query(
        `SELECT QUARTER(${SUBMISSION_DATE}) as q, COUNT(ans.answers_id) as count
         FROM sat_submissions s LEFT JOIN sat_answers ans ON ans.submission_id = s.submission_id${subJoin}
         ${subWhere} GROUP BY QUARTER(${SUBMISSION_DATE}) ORDER BY q`,
        subParams
      ).then(([r]) => r).catch(() => []),
    ]);
    const audMap = new Map((audRows || []).map((r) => [r.q, r.count]));
    const subMap = new Map((subRows || []).map((r) => [r.q, r.count]));
    const ansMap = new Map((ansRows || []).map((r) => [r.q, r.count]));
    const labels = ['Q1 (Jan–Mar)', 'Q2 (Apr–Jun)', 'Q3 (Jul–Sep)', 'Q4 (Oct–Dec)'];
    return [1, 2, 3, 4].map((q) => ({
      month: labels[q - 1],
      sat_audits: audMap.get(q) || 0,
      sat_submissions: subMap.get(q) || 0,
      sat_answers: ansMap.get(q) || 0,
    }));
  }

  if (p === 'yearly') {
    const startYear = y - 4;
    const audYrCol = filterClause ? `YEAR(${AUDIT_DATE})` : `YEAR(${AUDIT_DATE_BARE})`;
    const yrAudParams = filterClause ? [startYear, y, ...filterParams] : [startYear, y];
    const yrSubWhere = filterClause ? ` WHERE YEAR(${SUBMISSION_DATE}) >= ? AND YEAR(${SUBMISSION_DATE}) <= ?${filterClause}` : ` WHERE YEAR(${SUBMISSION_DATE}) >= ? AND YEAR(${SUBMISSION_DATE}) <= ?`;
    const yrSubParams = filterClause ? [startYear, y, ...filterParams] : [startYear, y];
    const [audRows, subRows, ansRows] = await Promise.all([
      pool.query(
        `SELECT ${audYrCol} as yr, COUNT(*) as count FROM ${audFrom} WHERE ${audYrCol} >= ? AND ${audYrCol} <= ?${filterClause} GROUP BY yr ORDER BY yr`,
        yrAudParams
      ).then(([r]) => r).catch(() => []),
      pool.query(
        `SELECT YEAR(${SUBMISSION_DATE}) as yr, COUNT(*) as count FROM sat_submissions s${subJoin}${yrSubWhere} GROUP BY YEAR(${SUBMISSION_DATE}) ORDER BY yr`,
        yrSubParams
      ).then(([r]) => r).catch(() => []),
      pool.query(
        `SELECT YEAR(${SUBMISSION_DATE}) as yr, COUNT(ans.answers_id) as count
         FROM sat_submissions s LEFT JOIN sat_answers ans ON ans.submission_id = s.submission_id${subJoin}
         ${yrSubWhere} GROUP BY YEAR(${SUBMISSION_DATE}) ORDER BY yr`,
        yrSubParams
      ).then(([r]) => r).catch(() => []),
    ]);
    const audMap = new Map((audRows || []).map((r) => [r.yr, r.count]));
    const subMap = new Map((subRows || []).map((r) => [r.yr, r.count]));
    const ansMap = new Map((ansRows || []).map((r) => [r.yr, r.count]));
    return Array.from({ length: 5 }, (_, i) => {
      const yr = startYear + i;
      return {
        month: String(yr),
        sat_audits: audMap.get(yr) || 0,
        sat_submissions: subMap.get(yr) || 0,
        sat_answers: ansMap.get(yr) || 0,
      };
    });
  }

  if (p === 'daily') {
    const audDateCol = filterClause ? `DATE(${AUDIT_DATE})` : `DATE(${AUDIT_DATE_BARE})`;
    const [audRows, subRows, ansRows] = await Promise.all([
      pool.query(
        `SELECT ${audDateCol} as d, COUNT(*) as count FROM ${audFrom} WHERE ${audYearCol} = ?${filterClause} GROUP BY d ORDER BY d`,
        audParams
      ).then(([r]) => r).catch(() => []),
      pool.query(
        `SELECT DATE(${SUBMISSION_DATE}) as d, COUNT(*) as count FROM sat_submissions s${subJoin}${subWhere} GROUP BY DATE(${SUBMISSION_DATE}) ORDER BY d`,
        subParams
      ).then(([r]) => r).catch(() => []),
      pool.query(
        `SELECT DATE(${SUBMISSION_DATE}) as d, COUNT(ans.answers_id) as count
         FROM sat_submissions s LEFT JOIN sat_answers ans ON ans.submission_id = s.submission_id${subJoin}
         ${subWhere} GROUP BY DATE(${SUBMISSION_DATE}) ORDER BY d`,
        subParams
      ).then(([r]) => r).catch(() => []),
    ]);
    const audMap = new Map((audRows || []).map((r) => [toDateKey(r.d), r.count]));
    const subMap = new Map((subRows || []).map((r) => [toDateKey(r.d), r.count]));
    const ansMap = new Map((ansRows || []).map((r) => [toDateKey(r.d), r.count]));
    const daysInYear = (new Date(y, 11, 31).getTime() - new Date(y, 0, 1).getTime()) / 86400000 + 1;
    const result = [];
    for (let i = 1; i <= daysInYear; i++) {
      const d = new Date(y, 0, i);
      const dateStr = d.toISOString().slice(0, 10);
      const label = `${MONTH_NAMES[d.getMonth()]} ${d.getDate()}`;
      result.push({
        month: label,
        dateStr,
        sat_audits: audMap.get(dateStr) || 0,
        sat_submissions: subMap.get(dateStr) || 0,
        sat_answers: ansMap.get(dateStr) || 0,
      });
    }
    return result;
  }

  return getAuditTrends(y, 'monthly', filter);
};

/**
 * Total counts: sat_submissions, sat_answers, sat_audits. Optionally since a given date and location filter.
 * @param {string|null} sinceDate - If set, only count records created on or after this date.
 * @param {object} filter - Optional { country_id, location_id, department_id }.
 */
const getTotals = async (sinceDate = null, filter = null) => {
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});

  let subSql = 'SELECT COUNT(*) as c FROM sat_submissions s';
  const subWhere = [];
  const subParams = [];
  if (filterClause) {
    subSql += ' INNER JOIN sat_audits a ON s.audit_id = a.audit_id';
    subWhere.push('1=1' + filterClause);
    subParams.push(...filterParams);
  }
  if (sinceDate) {
    if (!filterClause) subSql += ' INNER JOIN sat_audits a ON s.audit_id = a.audit_id';
    subWhere.push(`${SUBMISSION_DATE} >= ?`);
    subParams.push(sinceDate);
  }
  if (subWhere.length) subSql += ' WHERE ' + subWhere.join(' AND ');
  const [subRows] = await pool.query(subSql, subParams);

  let ansSql = 'SELECT COUNT(*) as c FROM sat_answers ans INNER JOIN sat_submissions s ON s.submission_id = ans.submission_id';
  const ansWhere = [];
  const ansParams = [];
  if (filterClause) {
    ansSql += ' INNER JOIN sat_audits a ON s.audit_id = a.audit_id';
    ansWhere.push('1=1' + filterClause);
    ansParams.push(...filterParams);
  }
  if (sinceDate) {
    if (!filterClause) ansSql += ' INNER JOIN sat_audits a ON s.audit_id = a.audit_id';
    ansWhere.push(`${SUBMISSION_DATE} >= ?`);
    ansParams.push(sinceDate);
  }
  if (ansWhere.length) ansSql += ' WHERE ' + ansWhere.join(' AND ');
  const [ansRows] = await pool.query(ansSql, ansParams);

  let audSql = filterClause ? 'SELECT COUNT(*) as c FROM sat_audits a WHERE 1=1' + filterClause : 'SELECT COUNT(*) as c FROM sat_audits';
  const audParams = [...filterParams];
  if (sinceDate) {
    audSql = filterClause
      ? `SELECT COUNT(*) as c FROM sat_audits a WHERE ${AUDIT_DATE} >= ?` + filterClause
      : `SELECT COUNT(*) as c FROM sat_audits WHERE ${AUDIT_DATE_BARE} >= ?`;
    audParams.unshift(sinceDate);
  }
  const [audRows] = await pool.query(audSql, audParams);

  let closedSql = filterClause ? `SELECT COUNT(*) as c FROM sat_audits a WHERE 1=1${filterClause} AND a.audit_status_id = 2` : 'SELECT COUNT(*) as c FROM sat_audits WHERE audit_status_id = 2';
  const closedParams = [...filterParams];
  if (sinceDate) {
    closedSql = filterClause ? `SELECT COUNT(*) as c FROM sat_audits a WHERE ${AUDIT_DATE} >= ?${filterClause} AND a.audit_status_id = 2` : `SELECT COUNT(*) as c FROM sat_audits WHERE ${AUDIT_DATE_BARE} >= ? AND audit_status_id = 2`;
    closedParams.unshift(sinceDate);
  }
  const [closedAuditRows] = await pool.query(closedSql, closedParams).catch(() => [{ c: 0 }]);

  let openSql = filterClause ? `SELECT COUNT(*) as c FROM sat_audits a WHERE 1=1${filterClause} AND a.audit_status_id <> 2` : 'SELECT COUNT(*) as c FROM sat_audits WHERE audit_status_id <> 2';
  const openParams = [...filterParams];
  if (sinceDate) {
    openSql = filterClause ? `SELECT COUNT(*) as c FROM sat_audits a WHERE ${AUDIT_DATE} >= ?${filterClause} AND a.audit_status_id <> 2` : `SELECT COUNT(*) as c FROM sat_audits WHERE ${AUDIT_DATE_BARE} >= ? AND audit_status_id <> 2`;
    openParams.unshift(sinceDate);
  }
  const [openAuditRows] = await pool.query(openSql, openParams).catch(() => [{ c: 0 }]);

  const sat_submissions = subRows?.[0] ?? { c: 0 };
  const sat_answers = ansRows?.[0] ?? { c: 0 };
  const sat_audits = audRows?.[0] ?? { c: 0 };
  const [ticketRows] = await pool.query(
    'SELECT COUNT(*) as c FROM tickets WHERE closed_at IS NULL'
  ).catch(() => [{ c: 0 }]);
  const ticketsCritical = ticketRows?.[0] ?? { c: 0 };
  const [userRows] = await pool.query('SELECT COUNT(*) as c FROM users').catch(() => [{ c: 0 }]);
  const usersTotal = (userRows && userRows[0] && userRows[0].c != null) ? userRows[0].c : 0;
  return {
    sat_submissions: (sat_submissions && sat_submissions.c) != null ? sat_submissions.c : 0,
    sat_answers: (sat_answers && sat_answers.c) != null ? sat_answers.c : 0,
    sat_audits: (sat_audits && sat_audits.c) != null ? sat_audits.c : 0,
    openAudits: (openAuditRows && openAuditRows[0] && openAuditRows[0].c != null) ? openAuditRows[0].c : 0,
    closedAudits: (closedAuditRows && closedAuditRows[0] && closedAuditRows[0].c != null) ? closedAuditRows[0].c : 0,
    criticalFindings: (ticketsCritical && ticketsCritical.c) != null ? ticketsCritical.c : 0,
    totalUsers: usersTotal,
  };
};

/**
 * Count distinct sat_audits that have at least one answer with numeric value between minRpn and maxRpn (inclusive).
 * @param {number} minRpn
 * @param {number} maxRpn
 * @param {string|null} sinceDate - If set, only sat_submissions on or after this date.
 * @param {object} filter - Optional location filter.
 */
const getAuditsWithRpnInRangeCount = async (minRpn, maxRpn, sinceDate = null, filter = null) => {
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const subCond = sinceDate ? 'AND s.created_at >= ?' : '';
  const params = [minRpn, maxRpn, ...(sinceDate ? [sinceDate] : []), ...filterParams];
  const [rows] = await pool.query(
    `
    SELECT COUNT(DISTINCT s.audit_id) as c
    FROM sat_submissions s
    INNER JOIN sat_audits a ON s.audit_id = a.audit_id
    INNER JOIN sat_answers ans ON ans.submission_id = s.submission_id
    WHERE ans.answer_value REGEXP '^[0-9]+$'
      AND CAST(ans.answer_value AS UNSIGNED) BETWEEN ? AND ?
      ${subCond}${filterClause}
    `.trim(),
    params
  ).catch(() => [{ c: 0 }]);
  return (rows && rows[0] && rows[0].c != null) ? Number(rows[0].c) : 0;
};

/**
 * List sat_audits that have at least one answer with numeric value between minRpn and maxRpn (inclusive).
 * @param {number} minRpn
 * @param {number} maxRpn
 * @param {object} filter - Optional location filter.
 */
const getAuditsWithRpnInRange = async (minRpn, maxRpn, filter = null) => {
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const [rows] = await pool.query(
    `
    SELECT DISTINCT
      a.audit_id,
      a.audit_name,
      a.created_at,
      a.observed_at,
      u.employee_name AS auditor_name,
      l.location_name,
      ast.audit_status_name
    FROM sat_audits a
    LEFT JOIN users u ON a.auditor_id = u.user_id
    LEFT JOIN locations l ON a.location = l.location_id
    LEFT JOIN sat_audit_status ast ON a.audit_status_id = ast.audit_status_id
    INNER JOIN sat_submissions s ON s.audit_id = a.audit_id
    INNER JOIN sat_answers ans ON ans.submission_id = s.submission_id
    WHERE ans.answer_value REGEXP '^[0-9]+$'
      AND CAST(ans.answer_value AS UNSIGNED) BETWEEN ? AND ?${filterClause}
    ORDER BY ${AUDIT_DATE} DESC
    `,
    [minRpn, maxRpn, ...filterParams]
  ).catch(() => []);
  return rows || [];
};

/** Bar chart buckets: 1, 2, 4, 8, 16, 32, 64, 128 (total RPN ranges) */
const RPN_AUDIT_CHART_VALUES = [1, 2, 4, 8, 16, 32, 64, 128];

/**
 * Bar chart based on total RPN (sum of all RPN-type sat_answers per audit). Optional location filter.
 */
const getRpnAuditCounts = async (filter = null) => {
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const joinAudit = filterClause ? 'INNER JOIN sat_audits a ON s.audit_id = a.audit_id' : '';
  const [rows] = await pool.query(
    `
    WITH audit_total_rpn AS (
      SELECT s.audit_id,
             SUM(CAST(TRIM(ans.answer_value) AS UNSIGNED)) AS total_rpn
      FROM sat_submissions s
      ${joinAudit}
      INNER JOIN sat_answers ans ON ans.submission_id = s.submission_id
      INNER JOIN sat_questions q ON q.question_id = ans.question_id
      WHERE ans.answer_value REGEXP '^[0-9]+$'
        AND LOWER(q.question) LIKE '%rpn%'${filterClause}
      GROUP BY s.audit_id
    ),
    audit_bucket AS (
      SELECT audit_id,
             CASE
               WHEN total_rpn <= 1 THEN 1
               WHEN total_rpn <= 3 THEN 2
               WHEN total_rpn <= 7 THEN 4
               WHEN total_rpn <= 15 THEN 8
               WHEN total_rpn <= 31 THEN 16
               WHEN total_rpn <= 63 THEN 32
               WHEN total_rpn <= 127 THEN 64
               ELSE 128
             END AS bucket
      FROM audit_total_rpn
    )
    SELECT bucket AS rpnValue, COUNT(*) AS count
    FROM audit_bucket
    GROUP BY bucket
    `,
    filterParams
  ).catch(() => []);
  const countByValue = new Map((rows || []).map((r) => [Number(r.rpnValue), Number(r.count)]));
  return RPN_AUDIT_CHART_VALUES.map((v) => ({
    rpnValue: v,
    name: String(v),
    count: countByValue.get(v) || 0,
  }));
};

/**
 * List sat_audits whose total RPN falls in the bucket for the given value. Optional location filter.
 */
const getAuditsWithTotalRpnInBucket = async (rpnValue, filter = null) => {
  const ranges = [
    [1, 1], [2, 3], [4, 7], [8, 15], [16, 31], [32, 63], [64, 127], [128, 999999],
  ];
  const validValues = [1, 2, 4, 8, 16, 32, 64, 128];
  const idx = validValues.indexOf(rpnValue);
  if (idx === -1) return [];
  const [minTotal, maxTotal] = ranges[idx];
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const joinAudit = filterClause ? 'INNER JOIN sat_audits a ON s.audit_id = a.audit_id' : '';
  const [rows] = await pool.query(
    `
    WITH audit_total_rpn AS (
      SELECT s.audit_id,
             SUM(CAST(TRIM(ans.answer_value) AS UNSIGNED)) AS total_rpn
      FROM sat_submissions s
      ${joinAudit}
      INNER JOIN sat_answers ans ON ans.submission_id = s.submission_id
      INNER JOIN sat_questions q ON q.question_id = ans.question_id
      WHERE ans.answer_value REGEXP '^[0-9]+$'
        AND LOWER(q.question) LIKE '%rpn%'${filterClause}
      GROUP BY s.audit_id
    )
    SELECT
      a.audit_id,
      a.audit_name,
      a.created_at,
      a.observed_at,
      u.employee_name AS auditor_name,
      l.location_name,
      ast.audit_status_name
    FROM sat_audits a
    LEFT JOIN users u ON a.auditor_id = u.user_id
    LEFT JOIN locations l ON a.location = l.location_id
    LEFT JOIN sat_audit_status ast ON a.audit_status_id = ast.audit_status_id
    INNER JOIN audit_total_rpn ar ON ar.audit_id = a.audit_id
      AND ar.total_rpn >= ?
      AND ar.total_rpn <= ?
    ORDER BY ${AUDIT_DATE} DESC
    `,
    [...filterParams, minTotal, maxTotal]
  ).catch(() => []);
  return rows || [];
};

/**
 * RPN distribution from answer_value (numeric): Low 1-25, Medium 26-80, High 81-125. Optional location filter.
 * @param {string|null} sinceDate - If set, only sat_answers from sat_submissions on or after this date.
 */
const getRpnDistribution = async (sinceDate = null, filter = null) => {
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const needSubJoin = sinceDate || filterClause;
  const joinClause = needSubJoin
    ? 'INNER JOIN sat_submissions s ON s.submission_id = a.submission_id' +
      (sinceDate ? ' AND s.created_at >= ?' : '') +
      (filterClause ? ' INNER JOIN sat_audits aud ON s.audit_id = aud.audit_id' : '')
    : '';
  const whereFilter = filterClause ? filterClause.replace(/\ba\./g, 'aud.') : '';
  const params = [...(sinceDate ? [sinceDate] : []), ...filterParams];
  const [rows] = await pool.query(
    `
    SELECT 
      CASE 
        WHEN CAST(a.answer_value AS UNSIGNED) BETWEEN 1 AND 25 THEN 'Low (1–25)'
        WHEN CAST(a.answer_value AS UNSIGNED) BETWEEN 26 AND 80 THEN 'Medium (26–80)'
        WHEN CAST(a.answer_value AS UNSIGNED) BETWEEN 81 AND 125 THEN 'High (81–125)'
        ELSE NULL
      END as band,
      COUNT(*) as value
    FROM sat_answers a
    ${joinClause}
    WHERE a.answer_value REGEXP '^[0-9]+$'
      AND CAST(a.answer_value AS UNSIGNED) BETWEEN 1 AND 125${whereFilter}
    GROUP BY band
    ORDER BY MIN(CAST(a.answer_value AS UNSIGNED))
  `.trim(),
    params
  );
  return rows.filter(r => r.band).map(r => ({ name: r.band, value: r.value }));
};

/**
 * Answer types: by sat_question_type name and Media (is_media).
 */
const getAnswerTypes = async () => {
  const [byType] = await pool.query(`
    SELECT 
      COALESCE(qt.question_type, 'Other') as name,
      COUNT(*) as count
    FROM sat_answers a
    LEFT JOIN sat_questions q ON a.question_id = q.question_id
    LEFT JOIN sat_question_type qt ON q.question_type_id = qt.question_type_id
    GROUP BY qt.question_type_id, qt.question_type
    ORDER BY count DESC
  `).catch(() => [[]]);
  const [[mediaCount]] = await pool.query(
    'SELECT COUNT(*) as c FROM sat_answers WHERE is_media = 1'
  ).catch(() => [{ c: 0 }]);
  const hasMedia = (mediaCount?.c ?? 0) > 0;
  const list = byType.map((r) => ({ name: r.name || 'Other', count: r.count }));
  if (hasMedia) {
    const idx = list.findIndex((r) => r.name === 'Media');
    if (idx >= 0) list[idx].count = mediaCount.c;
    else list.push({ name: 'Media', count: mediaCount.c });
  }
  return list;
};

/**
 * Recent activities: latest 5 sat_submissions with submitter, audit, form.
 * @param {string|null} sinceDate - If set, only sat_submissions on or after this date.
 */
const getRecentActivities = async (sinceDate = null, filter = null) => {
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const whereParts = [];
  const params = [];
  if (sinceDate) {
    whereParts.push(`${SUBMISSION_DATE} >= ?`);
    params.push(sinceDate);
  }
  if (filterClause) {
    whereParts.push('1=1' + filterClause);
    params.push(...filterParams);
  }
  const whereClause = whereParts.length ? 'WHERE ' + whereParts.join(' AND ') : '';
  const [rows] = await pool.query(
    `
    SELECT 
      s.submission_id as id,
      ${SUBMISSION_DATE} as created_at,
      u.employee_name as user_name,
      a.audit_name as site_name,
      f.title as form_title
    FROM sat_submissions s
    LEFT JOIN users u ON s.submitter_id = u.user_id
    LEFT JOIN sat_audits a ON s.audit_id = a.audit_id
    LEFT JOIN sat_form_details f ON s.form_id = f.form_id
    ${whereClause}
    ORDER BY ${SUBMISSION_DATE} DESC
    LIMIT 5
  `.trim(),
    params
  );
  return rows;
};

/**
 * Top 5 locations by audit count (sites audited). Optional location filter.
 * @param {string|null} sinceDate - If set, only sat_audits/sat_submissions on or after this date.
 */
const getTopSitesByAudits = async (sinceDate = null, filter = null) => {
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const whereExtra = sinceDate ? `AND ${AUDIT_DATE} >= ?` : '';
  const params = [...(sinceDate ? [sinceDate, sinceDate] : []), ...filterParams];
  const [rows] = await pool.query(
    `
    SELECT 
      COALESCE(l.location_name, 'Unknown') AS name,
      COALESCE(c.country_name, '—') AS country,
      TRIM(GROUP_CONCAT(DISTINCT d.department_name)) AS workarea,
      COUNT(DISTINCT a.audit_id) AS audit_count,
      COUNT(ss.submission_id) AS submission_count
    FROM sat_audits a
    LEFT JOIN locations l ON a.location = l.location_id
    LEFT JOIN country c ON l.country_id = c.country_id
    LEFT JOIN departments d ON a.department_id = d.department_id
    LEFT JOIN sat_submissions ss ON ss.audit_id = a.audit_id${sinceDate ? ` AND COALESCE(ss.created_at, ${AUDIT_DATE}) >= ?` : ''}
    WHERE 1=1 ${whereExtra}${filterClause}
    GROUP BY a.location, l.location_name, c.country_id, c.country_name
    ORDER BY audit_count DESC
    LIMIT 5
  `.trim(),
    params
  ).catch(() => []);
  return rows.map((r) => ({
    name: r.name,
    country: r.country || '—',
    workarea: r.workarea || '—',
    score: r.audit_count > 0 ? Math.min(100, 70 + Math.min(30, Number(r.submission_count) || 0)) : 0,
    findings: 0,
    trend: 'up',
  }));
};

/**
 * Build last N months labels for charts (current month backwards).
 * @param {number} n - Number of months (default 6).
 */
const getLastNMonthsLabels = (n = 6) => {
  const now = new Date();
  const months = [];
  for (let i = n - 1; i >= 0; i--) {
    const d = new Date(now.getFullYear(), now.getMonth() - i, 1);
    months.push({
      month: MONTH_NAMES[d.getMonth()],
      monthNum: d.getMonth() + 1,
      year: d.getFullYear(),
    });
  }
  return months;
};

/**
 * Build last 7 days labels (day names / short dates).
 */
const getLast7DaysLabels = () => {
  const days = [];
  for (let i = 6; i >= 0; i--) {
    const d = new Date();
    d.setDate(d.getDate() - i);
    const y = d.getFullYear();
    const m = String(d.getMonth() + 1).padStart(2, '0');
    const day = String(d.getDate()).padStart(2, '0');
    days.push({
      dateStr: `${y}-${m}-${day}`,
      month: `${MONTH_NAMES[d.getMonth()]} ${d.getDate()}`,
    });
  }
  return days;
};

/**
 * Build 6 bucket labels for last 30 days (e.g. "Jan 1-5", "Jan 6-10", ...).
 */
const getLast30DaysBucketLabels = () => {
  const now = new Date();
  const buckets = [];
  for (let b = 0; b < 6; b++) {
    const start = new Date(now);
    start.setDate(start.getDate() - 30 + b * 5);
    const end = new Date(now);
    end.setDate(end.getDate() - 30 + (b + 1) * 5 - 1);
    const startM = MONTH_NAMES[start.getMonth()];
    const endM = MONTH_NAMES[end.getMonth()];
    const label = start.getMonth() === end.getMonth()
      ? `${startM} ${start.getDate()}-${end.getDate()}`
      : `${startM} ${start.getDate()}-${endM} ${end.getDate()}`;
    buckets.push({
      month: label,
      startDate: start.toISOString().slice(0, 10),
      endDate: end.toISOString().slice(0, 10),
      bucketIndex: b,
    });
  }
  return buckets;
};

/**
 * Get start date (YYYY-MM-DD) for a period: week (7d), month (30d), year (12 months).
 * @param {string} period - 'week' | 'month' | 'year'
 * @returns {{ sinceDate: string, trendMonths?: number, trendDays?: number }}
 */
const getPeriodRange = (period) => {
  const now = new Date();
  const d = new Date(now);
  if (period === 'week') {
    d.setDate(d.getDate() - 7);
    return { sinceDate: d.toISOString().slice(0, 10), trendDays: 7 };
  }
  if (period === 'month') {
    d.setDate(d.getDate() - 30);
    return { sinceDate: d.toISOString().slice(0, 10), trendDays: 30 };
  }
  if (period === 'year') {
    d.setMonth(d.getMonth() - 12);
    d.setDate(1);
    return { sinceDate: d.toISOString().slice(0, 10), trendMonths: 12 };
  }
  return { sinceDate: null, trendMonths: 6 };
}

/**
 * Dashboard stats: one object for frontend. Optional location filter.
 * @param {string} [period] - 'week' | 'month' | 'year' for filtered data; otherwise last 6 months.
 * @param {object} [filter] - Optional { country_id, location_id, department_id }.
 */
const getDashboardStats = async (period, filter = null) => {
  const { sinceDate, trendMonths = 6, trendDays } = getPeriodRange(period || '');

  if (trendDays === 7) {
    // Last 7 days: by day
    const [subByDay, ansByDay, audByDay, totals, rpnDistribution, answerTypes, recentActivities, topSites, auditsRpn32To128] =
      await Promise.all([
        getSubmissionsByDay(7, filter),
        getAnswersByDay(7, filter),
        getAuditsByDay(7, filter),
        getTotals(sinceDate, filter),
        getRpnDistribution(sinceDate, filter),
        getAnswerTypes(),
        getRecentActivities(sinceDate, filter),
        getTopSitesByAudits(sinceDate, filter),
        getAuditsWithRpnInRangeCount(32, 128, sinceDate, filter),
      ]);
    const labels = getLast7DaysLabels();
    const subMap = new Map(subByDay.map((r) => [toDateKey(r.d), r.count]));
    const ansMap = new Map(ansByDay.map((r) => [toDateKey(r.d), r.count]));
    const audMap = new Map(audByDay.map((r) => [toDateKey(r.d), r.count]));
    const answersTrend = labels.map(({ dateStr, month }) => ({
      month,
      sat_submissions: subMap.get(dateStr) || 0,
      sat_answers: ansMap.get(dateStr) || 0,
      sat_audits: audMap.get(dateStr) || 0,
    }));
    // Audit completion rate based on closed vs total sat_audits (by status)
    let complianceRate = 0;
    const closedAudits = totals.closedAudits ?? 0;
    const totalAuditsForRate = totals.sat_audits || (totals.openAudits ?? 0) + closedAudits;
    if (totalAuditsForRate > 0) {
      complianceRate = Math.round((closedAudits / totalAuditsForRate) * 100);
    }
    const last6 = labels;
    const complianceLine = last6.map(({ month }) => ({ month, rate: complianceRate }));
    const auditCompletion = labels.map(({ month, dateStr }) => ({
      month,
      completed: audMap.get(dateStr) || 0,
      scheduled: Math.max(audMap.get(dateStr) || 0, 1),
    }));
    const recent = recentActivities.map((r) => ({
      id: r.id,
      action: `Submission: ${r.form_title || 'Form'}`,
      user: r.user_name || 'Unknown',
      site: r.site_name || '—',
      time: formatTimeAgo(r.created_at),
      type: 'audit',
    }));
    return {
      stats: {
        totalAudits: totals.sat_audits,
        totalAnswers: totals.sat_answers,
        totalSubmissions: totals.sat_submissions,
        complianceRate,
        criticalFindings: totals.criticalFindings,
        auditsWithRpn32To128: auditsRpn32To128,
        totalUsers: totals.totalUsers,
      },
      auditCompletion,
      answersTrend,
      complianceLine,
      rpnDistribution: rpnDistribution.length ? rpnDistribution : [
        { name: 'Low (1–25)', value: 0 },
        { name: 'Medium (26–80)', value: 0 },
        { name: 'High (81–125)', value: 0 },
      ],
      answerTypes: answerTypes.length ? answerTypes : [
        { name: 'Text', count: 0 },
        { name: 'Yes/No', count: 0 },
        { name: 'Number', count: 0 },
      ],
      topSites,
      recentActivities: recent,
    };
  }

  if (trendDays === 30) {
    // Last 30 days: 6 buckets of 5 days
    const [subByDay, ansByDay, audByDay, totals, rpnDistribution, answerTypes, recentActivities, topSites, auditsRpn32To128] =
      await Promise.all([
        getSubmissionsByDay(30, filter),
        getAnswersByDay(30, filter),
        getAuditsByDay(30, filter),
        getTotals(sinceDate, filter),
        getRpnDistribution(sinceDate, filter),
        getAnswerTypes(),
        getRecentActivities(sinceDate, filter),
        getTopSitesByAudits(sinceDate, filter),
        getAuditsWithRpnInRangeCount(32, 128, sinceDate, filter),
      ]);
    const buckets = getLast30DaysBucketLabels();
    const subMap = new Map();
    const ansMap = new Map();
    const audMap = new Map();
    subByDay.forEach((r) => {
      const d = toDateKey(r.d);
      const b = buckets.findIndex((bk) => d >= bk.startDate && d <= bk.endDate);
      if (b >= 0) {
        subMap.set(b, (subMap.get(b) || 0) + (r.count || 0));
      }
    });
    ansByDay.forEach((r) => {
      const d = toDateKey(r.d);
      const b = buckets.findIndex((bk) => d >= bk.startDate && d <= bk.endDate);
      if (b >= 0) {
        ansMap.set(b, (ansMap.get(b) || 0) + (r.count || 0));
      }
    });
    audByDay.forEach((r) => {
      const d = toDateKey(r.d);
      const b = buckets.findIndex((bk) => d >= bk.startDate && d <= bk.endDate);
      if (b >= 0) {
        audMap.set(b, (audMap.get(b) || 0) + (r.count || 0));
      }
    });
    const answersTrend = buckets.map((b) => ({
      month: b.month,
      sat_submissions: subMap.get(b.bucketIndex) || 0,
      sat_answers: ansMap.get(b.bucketIndex) || 0,
      sat_audits: audMap.get(b.bucketIndex) || 0,
    }));
    // Audit completion rate based on closed vs total sat_audits (by status)
    let complianceRate = 0;
    const closedAudits = totals.closedAudits ?? 0;
    const totalAuditsForRate = totals.sat_audits || (totals.openAudits ?? 0) + closedAudits;
    if (totalAuditsForRate > 0) {
      complianceRate = Math.round((closedAudits / totalAuditsForRate) * 100);
    }
    const complianceLine = buckets.map((b) => ({ month: b.month, rate: complianceRate }));
    const auditCompletion = buckets.map((b) => ({
      month: b.month,
      completed: audMap.get(b.bucketIndex) || 0,
      scheduled: Math.max(audMap.get(b.bucketIndex) || 0, 1),
    }));
    const recent = recentActivities.map((r) => ({
      id: r.id,
      action: `Submission: ${r.form_title || 'Form'}`,
      user: r.user_name || 'Unknown',
      site: r.site_name || '—',
      time: formatTimeAgo(r.created_at),
      type: 'audit',
    }));
    return {
      stats: {
        totalAudits: totals.sat_audits,
        totalAnswers: totals.sat_answers,
        totalSubmissions: totals.sat_submissions,
        complianceRate,
        criticalFindings: totals.criticalFindings,
        auditsWithRpn32To128: auditsRpn32To128,
        totalUsers: totals.totalUsers,
      },
      auditCompletion,
      answersTrend,
      complianceLine,
      rpnDistribution: rpnDistribution.length ? rpnDistribution : [
        { name: 'Low (1–25)', value: 0 },
        { name: 'Medium (26–80)', value: 0 },
        { name: 'High (81–125)', value: 0 },
      ],
      answerTypes: answerTypes.length ? answerTypes : [
        { name: 'Text', count: 0 },
        { name: 'Yes/No', count: 0 },
        { name: 'Number', count: 0 },
      ],
      topSites,
      recentActivities: recent,
    };
  }

  // Default: last 6 or 12 months by month
  const [submissionsByMonth, answersByMonth, auditsByMonth, totals, rpnDistribution, answerTypes, recentActivities, topSites, auditsRpn32To128] =
    await Promise.all([
      getSubmissionsByMonth(trendMonths),
      getAnswersByMonth(trendMonths),
      getAuditsByMonth(trendMonths),
      getTotals(sinceDate, filter),
      getRpnDistribution(sinceDate, filter),
      getAnswerTypes(),
      getRecentActivities(sinceDate, filter),
      getTopSitesByAudits(sinceDate, filter),
      getAuditsWithRpnInRangeCount(32, 128, sinceDate, filter),
    ]);

  const last6 = getLastNMonthsLabels(trendMonths);
  const submissionsMap = new Map(submissionsByMonth.map((r) => [`${r.y}-${r.m}`, r.count]));
  const answersMap = new Map(answersByMonth.map((r) => [`${r.y}-${r.m}`, r.count]));
  const auditsMap = new Map(auditsByMonth.map((r) => [`${r.y}-${r.m}`, r.count]));

  const auditCompletion = last6.map(({ month, monthNum, year }) => {
    const key = `${year}-${monthNum}`;
    const completed = auditsMap.get(key) || 0;
    return { month, completed, scheduled: Math.max(completed, 1) };
  });

  const answersTrend = last6.map(({ month, monthNum, year }) => {
    const key = `${year}-${monthNum}`;
    return {
      month,
      sat_submissions: submissionsMap.get(key) || 0,
      sat_answers: answersMap.get(key) || 0,
      sat_audits: auditsMap.get(key) || 0,
    };
  });

  let complianceRate = 0;
  // Audit completion rate based on closed vs total sat_audits (by status)
  const closedAudits = totals.closedAudits ?? 0;
  const totalAuditsForRate = totals.sat_audits || (totals.openAudits ?? 0) + closedAudits;
  if (totalAuditsForRate > 0) {
    complianceRate = Math.round((closedAudits / totalAuditsForRate) * 100);
  }
  const complianceLine = last6.map(({ month, monthNum, year }) => {
    const key = `${year}-${monthNum}`;
    const subCount = submissionsMap.get(key) || 0;
    const rate = totals.sat_audits > 0 ? Math.min(100, Math.round((subCount / Math.max(totals.sat_audits / trendMonths, 1)) * 100)) : 0;
    return { month, rate: rate || complianceRate };
  });

  const recent = recentActivities.map((r) => ({
    id: r.id,
    action: `Submission: ${r.form_title || 'Form'}`,
    user: r.user_name || 'Unknown',
    site: r.site_name || '—',
    time: formatTimeAgo(r.created_at),
    type: 'audit',
  }));

  return {
    stats: {
      totalAudits: totals.sat_audits,
      totalAnswers: totals.sat_answers,
      totalSubmissions: totals.sat_submissions,
      complianceRate,
      criticalFindings: totals.criticalFindings,
      auditsWithRpn32To128: auditsRpn32To128,
      totalUsers: totals.totalUsers,
    },
    auditCompletion,
    answersTrend,
    complianceLine,
    rpnDistribution: rpnDistribution.length ? rpnDistribution : [
      { name: 'Low (1–25)', value: 0 },
      { name: 'Medium (26–80)', value: 0 },
      { name: 'High (81–125)', value: 0 },
    ],
    answerTypes: answerTypes.length ? answerTypes : [
      { name: 'Text', count: 0 },
      { name: 'Yes/No', count: 0 },
      { name: 'Number', count: 0 },
    ],
    topSites,
    recentActivities: recent,
  };
};

function formatTimeAgo(dateStr) {
  if (!dateStr) return '—';
  const d = new Date(dateStr);
  const now = new Date();
  const diffMs = now - d;
  const diffMins = Math.floor(diffMs / 60000);
  const diffHours = Math.floor(diffMs / 3600000);
  const diffDays = Math.floor(diffMs / 86400000);
  if (diffMins < 60) return `${diffMins} min ago`;
  if (diffHours < 24) return `${diffHours} hour${diffHours !== 1 ? 's' : ''} ago`;
  if (diffDays < 7) return `${diffDays} day${diffDays !== 1 ? 's' : ''} ago`;
  return d.toLocaleDateString();
}

/**
 * Distribution of sat_answers by severity of harm label. Optional location filter.
 */
const getSeverityOfHarmDistribution = async (filter = null) => {
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const subJoin = filterClause ? ' INNER JOIN sat_submissions s ON s.submission_id = a.submission_id INNER JOIN sat_audits a2 ON s.audit_id = a2.audit_id' : '';
  const whereFilter = filterClause ? filterClause.replace(/\ba\./g, 'a2.') : '';
  const [countRows] = await pool.query(
    `
    SELECT r.severity_of_harm AS name, COUNT(*) AS value
    FROM sat_answers a
    INNER JOIN sat_questions q ON q.question_id = a.question_id
    INNER JOIN rpn_severity_of_harm r ON (
      r.value = CAST(TRIM(a.answer_value) AS UNSIGNED)
      OR r.severity_of_harm = TRIM(a.answer_value)
    )${subJoin}
    WHERE (LOWER(q.question) LIKE '%severity%harm%' OR LOWER(q.question) LIKE '%severity of harm%')
      AND a.answer_value IS NOT NULL
      AND TRIM(a.answer_value) != ''${whereFilter}
    GROUP BY r.severity_of_harm, r.value
    ORDER BY r.value
    `,
    filterParams
  ).catch(() => []);
  const masterList = await getAllRpnSeverity().catch(() => []);
  const countMap = new Map((countRows || []).map((r) => [r.name, Number(r.value)]));
  return (masterList || []).map((r) => ({
    name: r.severity_of_harm,
    value: countMap.get(r.severity_of_harm) || 0,
  }));
};

/**
 * List sat_audits grouped by severity of harm.
 * Optional severityFilter limits to a single master severity label; otherwise
 * returns all sat_audits that have at least one severity-of-harm answer.
 */
const getAuditsBySeverityOfHarm = async (severityFilter = null, filter = null) => {
  const hasFilter = Boolean(severityFilter && String(severityFilter).trim());
  const { clause: locClause, params: locParams } = buildAuditFilter(filter || {});
  const params = [...(hasFilter ? [String(severityFilter).trim()] : []), ...locParams];

  const [rows] = await pool.query(
    `
    SELECT DISTINCT
      a.audit_id,
      a.audit_name,
      a.created_at,
      a.observed_at,
      u.employee_name AS auditor_name,
      l.location_name,
      ast.audit_status_name,
      r.severity_of_harm AS severity_label
    FROM sat_answers ans
    INNER JOIN sat_questions q ON q.question_id = ans.question_id
    INNER JOIN rpn_severity_of_harm r ON (
      r.value = CAST(TRIM(ans.answer_value) AS UNSIGNED)
      OR r.severity_of_harm = TRIM(ans.answer_value)
    )
    INNER JOIN sat_submissions s ON s.submission_id = ans.submission_id
    INNER JOIN sat_audits a ON a.audit_id = s.audit_id
    LEFT JOIN users u ON a.auditor_id = u.user_id
    LEFT JOIN locations l ON a.location = l.location_id
    LEFT JOIN sat_audit_status ast ON a.audit_status_id = ast.audit_status_id
    WHERE (LOWER(q.question) LIKE '%severity%harm%' OR LOWER(q.question) LIKE '%severity of harm%')
      AND ans.answer_value IS NOT NULL
      AND TRIM(ans.answer_value) != ''
      ${hasFilter ? 'AND r.severity_of_harm = ?' : ''}${locClause}
    ORDER BY ${AUDIT_DATE} DESC
    `.trim(),
    params
  ).catch(() => []);

  return rows || [];
};

/**
 * Distribution of sat_answers by likelihood of harm label. Optional location filter.
 */
const getLikelihoodOfHarmDistribution = async (filter = null) => {
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const subJoin = filterClause ? ' INNER JOIN sat_submissions s ON s.submission_id = a.submission_id INNER JOIN sat_audits a2 ON s.audit_id = a2.audit_id' : '';
  const whereFilter = filterClause ? filterClause.replace(/\ba\./g, 'a2.') : '';
  const [countRows] = await pool.query(
    `
    SELECT r.likelihood_of_harm AS name, COUNT(*) AS value
    FROM sat_answers a
    INNER JOIN sat_questions q ON q.question_id = a.question_id
    INNER JOIN rpn_likelihood_of_harm r ON (
      r.value = CAST(TRIM(a.answer_value) AS UNSIGNED)
      OR r.likelihood_of_harm = TRIM(a.answer_value)
    )${subJoin}
    WHERE (LOWER(q.question) LIKE '%likelihood%harm%' OR LOWER(q.question) LIKE '%likelihood of harm%')
      AND a.answer_value IS NOT NULL
      AND TRIM(a.answer_value) != ''${whereFilter}
    GROUP BY r.likelihood_of_harm, r.value
    ORDER BY r.value
    `,
    filterParams
  ).catch(() => []);
  const masterList = await getAllRpnLikelihood().catch(() => []);
  const countMap = new Map((countRows || []).map((r) => [r.name, Number(r.value)]));
  return (masterList || []).map((r) => ({
    name: r.likelihood_of_harm,
    value: countMap.get(r.likelihood_of_harm) || 0,
  }));
};

/**
 * List sat_audits grouped by likelihood of harm.
 * Optional likelihoodFilter limits to a single master likelihood label; otherwise
 * returns all sat_audits that have at least one likelihood-of-harm answer.
 */
const getAuditsByLikelihoodOfHarm = async (likelihoodFilter = null, filter = null) => {
  const hasFilter = Boolean(likelihoodFilter && String(likelihoodFilter).trim());
  const { clause: locClause, params: locParams } = buildAuditFilter(filter || {});
  const params = [...(hasFilter ? [String(likelihoodFilter).trim()] : []), ...locParams];

  const [rows] = await pool.query(
    `
    SELECT DISTINCT
      a.audit_id,
      a.audit_name,
      a.created_at,
      a.observed_at,
      u.employee_name AS auditor_name,
      l.location_name,
      ast.audit_status_name,
      r.likelihood_of_harm AS likelihood_label
    FROM sat_answers ans
    INNER JOIN sat_questions q ON q.question_id = ans.question_id
    INNER JOIN rpn_likelihood_of_harm r ON (
      r.value = CAST(TRIM(ans.answer_value) AS UNSIGNED)
      OR r.likelihood_of_harm = TRIM(ans.answer_value)
    )
    INNER JOIN sat_submissions s ON s.submission_id = ans.submission_id
    INNER JOIN sat_audits a ON a.audit_id = s.audit_id
    LEFT JOIN users u ON a.auditor_id = u.user_id
    LEFT JOIN locations l ON a.location = l.location_id
    LEFT JOIN sat_audit_status ast ON a.audit_status_id = ast.audit_status_id
    WHERE (LOWER(q.question) LIKE '%likelihood%harm%' OR LOWER(q.question) LIKE '%likelihood of harm%')
      AND ans.answer_value IS NOT NULL
      AND TRIM(ans.answer_value) != ''
      ${hasFilter ? 'AND r.likelihood_of_harm = ?' : ''}${locClause}
    ORDER BY ${AUDIT_DATE} DESC
    `.trim(),
    params
  ).catch(() => []);

  return rows || [];
};

/**
 * Parse period (7d, 30d, 90d, all) into fromDate for SQL. Returns null for 'all'.
 */
const getObservationsDateFilter = (period) => {
  if (!period || String(period).toLowerCase() === 'all') return { fromDate: null };
  const match = String(period).toLowerCase().match(/^(\d+)d$/);
  if (!match) return { fromDate: null };
  const days = Math.min(365, Math.max(1, parseInt(match[1], 10)));
  const from = new Date();
  from.setDate(from.getDate() - days);
  from.setHours(0, 0, 0, 0);
  return { fromDate: from.toISOString().slice(0, 19).replace('T', ' ') };
};

/**
 * Count observations (opening form sat_submissions) that are open vs closed. Optional location filter.
 * Total = opening forms in scope (date + location). Closed = those with a closing form linked, in same scope. Open = total - closed.
 */
const getObservationOpenClosedCounts = async (options = {}, filter = null) => {
  const { fromDate } = getObservationsDateFilter(options.period);
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const joinAudit = filterClause ? ' INNER JOIN sat_audits a ON s.audit_id = a.audit_id' : '';
  const dateClause = fromDate ? 'AND s.created_at >= ?' : '';
  const totalParams = [...(fromDate ? [fromDate] : []), ...filterParams];
  const [[totalRow]] = await pool.query(
    `SELECT COUNT(*) AS c
     FROM sat_submissions s
     INNER JOIN sat_form_details f ON s.form_id = f.form_id${joinAudit}
     WHERE f.form_type_id = 1 ${dateClause}${filterClause}`,
    totalParams
  ).catch(() => [{ c: 0 }]);
  const closedParams = [...(fromDate ? [fromDate] : []), ...filterParams];
  const [[closedRow]] = await pool.query(
    `SELECT COUNT(DISTINCT ocm.opening_sub_id) AS c
     FROM sat_opening_closing_map ocm
     INNER JOIN sat_submissions s ON s.submission_id = ocm.opening_sub_id AND s.audit_id = ocm.audit_id
     INNER JOIN sat_form_details f ON s.form_id = f.form_id AND f.form_type_id = 1
     ${joinAudit}
     WHERE ocm.closing_sub_id IS NOT NULL ${dateClause}${filterClause}`,
    closedParams
  ).catch(() => [{ c: 0 }]);
  const total = Number(totalRow?.c ?? 0);
  const closed = Number(closedRow?.c ?? 0);
  const open = Math.max(0, total - closed);
  return [
    { name: 'Open', count: open, key: 'open' },
    { name: 'Closed', count: closed, key: 'closed' },
  ];
};

/**
 * List open and closed observations with audit and form details for the detail page.
 * Open = opening submission with no map row or map row has closing_sub_id IS NULL.
 * Closed = opening submission with map row and closing_sub_id IS NOT NULL.
 */
const getObservationOpenClosedList = async (options = {}, filter = null) => {
  const { fromDate } = getObservationsDateFilter(options.period);
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const joinAudit = filterClause ? ' INNER JOIN sat_audits a ON a.audit_id = s.audit_id' : ' LEFT JOIN sat_audits a ON a.audit_id = s.audit_id';
  const dateClause = fromDate ? 'AND s.created_at >= ?' : '';
  const openParams = [...(fromDate ? [fromDate] : []), ...filterParams];
  const [openRows] = await pool.query(
    `SELECT s.submission_id, s.audit_id, s.created_at,
            a.audit_name, f.title AS form_title,
            u.employee_name AS submitter_name
     FROM sat_submissions s
     INNER JOIN sat_form_details f ON s.form_id = f.form_id
     ${joinAudit}
     LEFT JOIN users u ON u.user_id = s.submitter_id
     WHERE f.form_type_id = 1
       AND (
         NOT EXISTS (
           SELECT 1 FROM sat_opening_closing_map ocm
           WHERE ocm.audit_id = s.audit_id AND ocm.opening_sub_id = s.submission_id
         )
         OR EXISTS (
           SELECT 1 FROM sat_opening_closing_map ocm
           WHERE ocm.audit_id = s.audit_id AND ocm.opening_sub_id = s.submission_id AND ocm.closing_sub_id IS NULL
         )
       )
       ${dateClause}${filterClause}
     ORDER BY s.created_at DESC`,
    openParams
  ).catch(() => []);
  const closedParams = [...(fromDate ? [fromDate] : []), ...filterParams];
  const [closedRows] = await pool.query(
    `SELECT s.submission_id, s.audit_id, s.created_at,
            a.audit_name, f.title AS form_title,
            u.employee_name AS submitter_name,
            ocm.closing_sub_id
     FROM sat_submissions s
     INNER JOIN sat_form_details f ON s.form_id = f.form_id
     INNER JOIN sat_opening_closing_map ocm ON ocm.audit_id = s.audit_id AND ocm.opening_sub_id = s.submission_id AND ocm.closing_sub_id IS NOT NULL
     ${joinAudit}
     LEFT JOIN users u ON u.user_id = s.submitter_id
     WHERE f.form_type_id = 1
       ${dateClause}${filterClause}
     ORDER BY s.created_at DESC`,
    closedParams
  ).catch(() => []);
  return {
    open: openRows || [],
    closed: closedRows || [],
  };
};

/**
 * SIO / OIO / CA stats. Optional location filter.
 */
const getSioOioCaStats = async (filter = null) => {
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const joinSubAudit = filterClause ? ' INNER JOIN sat_submissions s ON s.submission_id = a.submission_id INNER JOIN sat_audits a2 ON s.audit_id = a2.audit_id' : '';
  const whereFilter = filterClause ? filterClause.replace(/\ba\./g, 'a2.') : '';
  const [[sioRow]] = await pool.query(
    `SELECT COUNT(DISTINCT a.submission_id) AS c
     FROM sat_answers a${joinSubAudit}
     WHERE TRIM(a.answer_value) = 'SIO'${whereFilter}`,
    filterParams
  ).catch(() => [{ c: 0 }]);
  const [[oioRow]] = await pool.query(
    `SELECT COUNT(DISTINCT a.submission_id) AS c
     FROM sat_answers a${joinSubAudit}
     WHERE TRIM(a.answer_value) = 'OIO'${whereFilter}`,
    filterParams
  ).catch(() => [{ c: 0 }]);
  const [[caRow]] = await pool.query(
    `SELECT COUNT(DISTINCT a.submission_id) AS c
     FROM sat_answers a${joinSubAudit}
     WHERE TRIM(a.answer_value) = 'CA'${whereFilter}`,
    filterParams
  ).catch(() => [{ c: 0 }]);
  const joinAudit = filterClause ? ' INNER JOIN sat_audits a ON s.audit_id = a.audit_id' : '';
  const [[sioClosedRow]] = await pool.query(
    `SELECT COUNT(DISTINCT s.submission_id) AS c
     FROM sat_submissions s
     INNER JOIN sat_form_details f ON s.form_id = f.form_id
     INNER JOIN sat_answers ans ON ans.submission_id = s.submission_id AND TRIM(ans.answer_value) = 'SIO'
     INNER JOIN sat_opening_closing_map ocm ON ocm.audit_id = s.audit_id AND ocm.opening_sub_id = s.submission_id${joinAudit}
     WHERE f.form_type_id = 1${filterClause}`,
    filterParams
  ).catch(() => [{ c: 0 }]);

  const sioCount = Number(sioRow?.c ?? 0);
  const oioCount = Number(oioRow?.c ?? 0);
  const caCount = Number(caRow?.c ?? 0);
  const sioClosedCount = Number(sioClosedRow?.c ?? 0);
  const total = sioCount + oioCount + caCount;
  const sioPercentage = total > 0 ? (sioCount / total) * 100 : 0;
  const oioPercentage = total > 0 ? (oioCount / total) * 100 : 0;
  const caPercentage = total > 0 ? (caCount / total) * 100 : 0;
  const sioPaPercentage = sioCount > 0 ? (sioClosedCount / sioCount) * 100 : 0;

  return {
    sioCount,
    oioCount,
    caCount,
    total,
    sioPercentage,
    oioPercentage,
    caPercentage,
    sioClosedCount,
    sioPaPercentage,
  };
};

const SIO_OIO_CA_TYPES = ['SIO', 'OIO', 'CA', 'SIO_CLOSED'];

/**
 * List sat_submissions (with audit details) for a given SIO/OIO/CA type. Optional location filter.
 */
const getSioOioCaList = async (type, filter = null) => {
  const t = String(type || '').trim().toUpperCase();
  if (!SIO_OIO_CA_TYPES.includes(t)) return [];
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const whereFilter = filterClause || '';
  const joinAuditA = filterClause ? ' INNER JOIN sat_audits a ON s.audit_id = a.audit_id' : ' LEFT JOIN sat_audits a ON a.audit_id = s.audit_id';
  if (t === 'SIO_CLOSED') {
    const [rows] = await pool.query(
      `SELECT s.submission_id, s.audit_id, s.created_at,
              a.audit_name, f.title AS form_title,
              u.employee_name AS submitter_name,
              ocm.closing_sub_id
       FROM sat_submissions s
       INNER JOIN sat_form_details f ON s.form_id = f.form_id
       INNER JOIN sat_answers ans ON ans.submission_id = s.submission_id AND TRIM(ans.answer_value) = 'SIO'
       INNER JOIN sat_opening_closing_map ocm ON ocm.audit_id = s.audit_id AND ocm.opening_sub_id = s.submission_id
       ${joinAuditA}
       LEFT JOIN users u ON u.user_id = s.submitter_id
       WHERE f.form_type_id = 1${whereFilter}
       ORDER BY s.created_at DESC`,
      filterParams
    ).catch(() => []);
    return rows || [];
  }

  const joinAud = filterClause ? ' INNER JOIN sat_audits aud ON aud.audit_id = s.audit_id' : ' LEFT JOIN sat_audits aud ON aud.audit_id = s.audit_id';
  const whereAud = filterClause ? ' WHERE 1=1' + filterClause.replace(/\ba\./g, 'aud.') : '';
  const [rows] = await pool.query(
    `SELECT s.submission_id, s.audit_id, s.created_at,
            aud.audit_name, f.title AS form_title,
            u.employee_name AS submitter_name
     FROM sat_submissions s
     INNER JOIN sat_answers ans ON ans.submission_id = s.submission_id AND TRIM(ans.answer_value) = ?
     ${joinAud}
     LEFT JOIN sat_form_details f ON f.form_id = s.form_id
     LEFT JOIN users u ON u.user_id = s.submitter_id
     ${whereAud}
     GROUP BY s.submission_id, s.audit_id, s.created_at, aud.audit_name, f.title, u.employee_name
     ORDER BY s.created_at DESC`,
    [t, ...filterParams]
  ).catch(() => []);

  if (!rows || !rows.length) return [];
  return rows.map((r) => ({
    submission_id: r.submission_id,
    audit_id: r.audit_id,
    created_at: r.created_at,
    audit_name: r.audit_name,
    form_title: r.form_title,
    submitter_name: r.submitter_name,
  }));
};

module.exports = {
  getDashboardStats,
  getRpnDistribution,
  getAnswerTypes,
  getRecentActivities,
  getTotals,
  getAuditsWithRpnInRange,
  getAuditsWithTotalRpnInBucket,
  getAuditTrends,
  getSeverityOfHarmDistribution,
  getLikelihoodOfHarmDistribution,
  getRpnAuditCounts,
  getObservationOpenClosedCounts,
  getObservationOpenClosedList,
  getSioOioCaStats,
  getSioOioCaList,
  getAuditsBySeverityOfHarm,
  getAuditsByLikelihoodOfHarm,
};
