const pool = require('../config/db');

/**
 * Get all opening-closing mappings for an audit.
 * Ensures every opening form submission for this audit has a map row (backfill) before returning.
 * @param {number} auditId
 * @returns {Promise<Array<{ audit_id, opening_sub_id, closing_sub_id }>>}
 */
const getMappingsByAuditId = async (auditId) => {
  await ensureOpeningRowsForAudit(Number(auditId));
  const [rows] = await pool.query(
    `SELECT audit_id, opening_sub_id, closing_sub_id
     FROM sat_opening_closing_map
     WHERE audit_id = ?
     ORDER BY opening_sub_id`,
    [auditId]
  );
  return rows;
};

/**
 * Get mapping by opening submission (for an audit).
 * @param {number} auditId
 * @param {number} openingSubId
 */
const getMappingByOpening = async (auditId, openingSubId) => {
  const [rows] = await pool.query(
    'SELECT audit_id, opening_sub_id, closing_sub_id FROM sat_opening_closing_map WHERE audit_id = ? AND opening_sub_id = ?',
    [auditId, openingSubId]
  );
  return rows[0] || null;
};

/**
 * Get mapping by closing submission (for an audit).
 * @param {number} auditId
 * @param {number} closingSubId
 */
const getMappingByClosing = async (auditId, closingSubId) => {
  const [rows] = await pool.query(
    'SELECT audit_id, opening_sub_id, closing_sub_id FROM sat_opening_closing_map WHERE audit_id = ? AND closing_sub_id = ?',
    [auditId, closingSubId]
  );
  return rows[0] || null;
};

/**
 * Insert a row for an opening observation when an opening form is submitted.
 * (audit_id, opening_sub_id, closing_sub_id=NULL). Does not overwrite if row already exists with a closing.
 * @param {number} auditId
 * @param {number} openingSubId - submission_id of the opening form
 * @param {object} connection - optional pool connection for transactions
 */
const insertOpening = async (auditId, openingSubId, connection = null) => {
  const db = connection || pool;
  await db.query(
    `INSERT INTO sat_opening_closing_map (audit_id, opening_sub_id, closing_sub_id)
     VALUES (?, ?, NULL)
     ON DUPLICATE KEY UPDATE closing_sub_id = IFNULL(closing_sub_id, VALUES(closing_sub_id))`,
    [auditId, openingSubId]
  );
};

/**
 * Create or replace mapping: one opening_sub_id -> one closing_sub_id per audit.
 * When a closing form is submitted against an opening, sets closing_sub_id for that opening's row.
 * @param {number} auditId
 * @param {number} openingSubId
 * @param {number} closingSubId
 * @param {object} connection - optional pool connection for transactions
 */
const upsertMapping = async (auditId, openingSubId, closingSubId, connection = null) => {
  const db = connection || pool;
  await db.query(
    `INSERT INTO sat_opening_closing_map (audit_id, opening_sub_id, closing_sub_id)
     VALUES (?, ?, ?)
     ON DUPLICATE KEY UPDATE closing_sub_id = VALUES(closing_sub_id)`,
    [auditId, openingSubId, closingSubId]
  );
};

/**
 * Get the first open observation for an audit (row with closing_sub_id IS NULL), by opening_sub_id order.
 * Used when a closing form is submitted without opening_sub_id to assign to the oldest open observation.
 * @param {number} auditId
 * @param {object} connection - optional
 * @returns {Promise<{ opening_sub_id: number } | null>}
 */
const getFirstOpenOpeningByAuditId = async (auditId, connection = null) => {
  const db = connection || pool;
  const [rows] = await db.query(
    `SELECT opening_sub_id FROM sat_opening_closing_map
     WHERE audit_id = ? AND closing_sub_id IS NULL
     ORDER BY opening_sub_id ASC LIMIT 1`,
    [auditId]
  );
  return rows[0] || null;
};

/**
 * Ensure every opening form submission for this audit has a row in sat_opening_closing_map.
 * Backfills missing rows (audit_id, opening_sub_id, closing_sub_id=NULL) so all observations are tracked.
 * Inserts a row for each opening submission; existing rows are left unchanged (ON DUPLICATE KEY UPDATE).
 * @param {number} auditId
 * @param {object} connection - optional
 */
const ensureOpeningRowsForAudit = async (auditId, connection = null) => {
  const db = connection || pool;
  const aid = Number(auditId);
  if (Number.isNaN(aid)) return;
  const [openingSubs] = await db.query(
    `SELECT s.submission_id
     FROM sat_submissions s
     INNER JOIN sat_form_details f ON s.form_id = f.form_id
     WHERE s.audit_id = ? AND f.form_type_id = 1`,
    [aid]
  );
  for (const s of openingSubs) {
    const openingSubId = Number(s.submission_id);
    if (Number.isNaN(openingSubId)) continue;
    await db.query(
      `INSERT INTO sat_opening_closing_map (audit_id, opening_sub_id, closing_sub_id)
       VALUES (?, ?, NULL)
       ON DUPLICATE KEY UPDATE closing_sub_id = IFNULL(closing_sub_id, VALUES(closing_sub_id))`,
      [aid, openingSubId]
    );
  }
};

/**
 * Delete mapping by opening submission.
 * @param {number} auditId
 * @param {number} openingSubId
 * @param {object} connection - optional
 */
const deleteByOpening = async (auditId, openingSubId, connection = null) => {
  const db = connection || pool;
  const [result] = await db.query(
    'DELETE FROM sat_opening_closing_map WHERE audit_id = ? AND opening_sub_id = ?',
    [auditId, openingSubId]
  );
  return result.affectedRows > 0;
};

/**
 * Delete mapping by closing submission.
 * @param {number} auditId
 * @param {number} closingSubId
 * @param {object} connection - optional
 */
const deleteByClosing = async (auditId, closingSubId, connection = null) => {
  const db = connection || pool;
  const [result] = await db.query(
    'DELETE FROM sat_opening_closing_map WHERE audit_id = ? AND closing_sub_id = ?',
    [auditId, closingSubId]
  );
  return result.affectedRows > 0;
};

/**
 * Get opening-closing pairs for an audit with submission details, and list openings without closing.
 * Used to enforce "every opening has a closing" and for UI display.
 * @param {number} auditId
 * @returns {Promise<{ pairs: Array<{ opening_sub_id, closing_sub_id, opening_title?, closing_title? }>, openingsWithoutClosing: Array<{ submission_id, form_title }>, allOpeningsHaveClosing: boolean }>}
 */
const getPairsAndGapsByAuditId = async (auditId) => {
  const aid = Number(auditId);
  if (Number.isNaN(aid)) return { pairs: [], openingsWithoutClosing: [], allOpeningsHaveClosing: true, openingCount: 0, closingCount: 0, openingSubmissions: [] };
  await ensureOpeningRowsForAudit(aid);
  const [openingSubs] = await pool.query(
    `SELECT s.submission_id, f.title as form_title
     FROM sat_submissions s
     INNER JOIN sat_form_details f ON s.form_id = f.form_id
     WHERE s.audit_id = ? AND f.form_type_id = 1
     ORDER BY s.submission_id`,
    [aid]
  );
  const [closingSubs] = await pool.query(
    `SELECT s.submission_id, f.title as form_title
     FROM sat_submissions s
     INNER JOIN sat_form_details f ON s.form_id = f.form_id
     WHERE s.audit_id = ? AND f.form_type_id = 2
     ORDER BY s.submission_id`,
    [aid]
  );
  const [mapRows] = await pool.query(
    'SELECT opening_sub_id, closing_sub_id FROM sat_opening_closing_map WHERE audit_id = ?',
    [aid]
  );
  // opening_sub_id -> closing_sub_id (null means observation is still open)
  const closingByOpening = new Map(mapRows.map((r) => [r.opening_sub_id, r.closing_sub_id]));

  const openingMap = new Map(openingSubs.map((s) => [s.submission_id, s]));
  const closingMap = new Map(closingSubs.map((s) => [s.submission_id, s]));

  const pairs = mapRows
    .filter((r) => r.closing_sub_id != null)
    .map((r) => ({
      opening_sub_id: r.opening_sub_id,
      closing_sub_id: r.closing_sub_id,
      opening_title: openingMap.get(r.opening_sub_id)?.form_title,
      closing_title: closingMap.get(r.closing_sub_id)?.form_title
    }));

  // Open = no row in map, or row has closing_sub_id IS NULL
  const openingsWithoutClosing = openingSubs.filter((o) => {
    const closingId = closingByOpening.get(o.submission_id);
    return closingId == null;
  }).map((o) => ({ submission_id: o.submission_id, form_title: o.form_title }));

  const allOpeningsHaveClosing = openingsWithoutClosing.length === 0;

  return {
    pairs,
    openingsWithoutClosing,
    allOpeningsHaveClosing,
    openingCount: openingSubs.length,
    closingCount: closingSubs.length,
    openingSubmissions: openingSubs.map((s) => ({ submission_id: s.submission_id, form_title: s.form_title }))
  };
};

module.exports = {
  getMappingsByAuditId,
  getMappingByOpening,
  getMappingByClosing,
  insertOpening,
  upsertMapping,
  getFirstOpenOpeningByAuditId,
  ensureOpeningRowsForAudit,
  deleteByOpening,
  deleteByClosing,
  getPairsAndGapsByAuditId
};
