const pool = require("../config/db");
const { createSubmission } = require("./submissionsRepository");
const {
  ensureOpeningRowsForAudit,
  insertOpening,
  upsertMapping,
  getFirstOpenOpeningByAuditId
} = require("./openingClosingMapRepository");
const { buildAuditFilter } = require("../utils/auditFilterHelper");
const { combineObservedDateAndTime } = require("../utils/auditDateTimeHelper");

const getAllAudits = async (filter = null) => {
  const { clause: filterClause, params: filterParams } = buildAuditFilter(filter || {});
  const [rows] = await pool.query(
    `SELECT a.*, 
           u.employee_id as auditor_employee_id,
           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
    WHERE 1=1${filterClause}`,
    filterParams
  );
  return rows;
};

const getAuditById = async (id) => {
  const [auditRows] = await pool.query(`
    SELECT a.*, 
           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
    WHERE a.audit_id = ?
  `, [id]);
  
  if (auditRows.length === 0) return null;
  
  const [coauditorRows] = await pool.query(`
    SELECT c.audit_id, c.is_external AS co_auditor_type, c.co_auditor_user_id, c.external_auditor_id,
           COALESCE(u.employee_name, ea.full_name, c.co_auditor_name) as co_auditor_name
    FROM sat_coauditors c
    LEFT JOIN users u ON c.co_auditor_user_id = u.user_id
    LEFT JOIN sat_external_auditors ea ON c.external_auditor_id = ea.external_auditor_id
    WHERE c.audit_id = ?
  `, [id]);
  const [auditeeRows] = await pool.query(`
    SELECT u.* 
    FROM users u
    INNER JOIN sat_auditees a ON u.user_id = a.auditee_id
    WHERE a.audit_id = ?
  `, [id]);
  const [openingFormRows] = await pool.query(`
    SELECT COUNT(*) as opening_form_count
    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
  `, [id]);
  const opening_form_count = openingFormRows[0]?.opening_form_count || 0;
  
  return {
    ...auditRows[0],
    sat_coauditors: coauditorRows,
    sat_auditees: auditeeRows,
    opening_form_count,
    has_multiple_opening_forms: opening_form_count > 1
  };
};

const createAudit = async (auditData, sat_coauditors, sat_auditees, observations, connection = null) => {
  const db = connection || pool;
  const { auditor_id, audit_name, country, location, work_area, observed_at, start_time, end_time } = auditData;

  const auditorId = Number(auditor_id);
  const locationId = Number(location);
  const countryId = country != null && country !== '' ? Number(country) : null;
  const departmentId = work_area != null && work_area !== '' ? Number(work_area) : null;
  if (isNaN(auditorId) || isNaN(locationId)) {
    const err = new Error('auditor_id and location must be valid numbers');
    err.status = 400;
    throw err;
  }
  if (countryId !== null && isNaN(countryId)) {
    const err = new Error('country must be a valid number');
    err.status = 400;
    throw err;
  }
  if (departmentId !== null && isNaN(departmentId)) {
    const err = new Error('work_area must be a valid number');
    err.status = 400;
    throw err;
  }

  // Automatically set audit_status_id to 1 (open) when creating a new audit
  const audit_status_id = 1;

  // Build dynamic insert: always include department_id (work area) and country_id
  let query = 'INSERT INTO sat_audits (auditor_id, audit_name, country_id, location, audit_status_id, department_id';
  const values = [auditorId, String(audit_name || '').trim(), countryId, locationId, audit_status_id, departmentId];

  let observedAtValue = null;
  if (observed_at && String(observed_at).trim()) {
    const raw = String(observed_at).trim();
    // MySQL TIMESTAMP accepts 'YYYY-MM-DD' or 'YYYY-MM-DD HH:MM:SS'
    observedAtValue = raw.length === 10 ? `${raw} 00:00:00` : raw;
  }
  // Always persist created_at so dashboard period filters and audit-trend charts work.
  const createdAtValue =
    observedAtValue ||
    new Date().toISOString().slice(0, 19).replace("T", " ");
  query += ", created_at";
  values.push(createdAtValue);

  if (observedAtValue) {
    query += ', observed_at';
    values.push(observedAtValue);
  }
  const startTimeValue = combineObservedDateAndTime(observed_at, start_time);
  const endTimeValue = combineObservedDateAndTime(observed_at, end_time);
  if (startTimeValue) {
    query += ", start_time";
    values.push(startTimeValue);
  }
  if (endTimeValue) {
    query += ", end_time";
    values.push(endTimeValue);
  }

  const placeholders = values.map(() => '?').join(', ');
  query += ') VALUES (' + placeholders + ')';
  
  const [result] = await db.query(query, values);
  
  const auditId = result.insertId;
  
  if (sat_coauditors && Array.isArray(sat_coauditors) && sat_coauditors.length > 0) {
    const typeIsExternal = (t) => t === 1 || t === '1';
    const userIds = [...new Set(sat_coauditors.filter(co => typeIsExternal(co.co_auditor_type) === false && (co.co_auditor_user_id != null)).map(co => Number(co.co_auditor_user_id)))];
    const extIds = [...new Set(sat_coauditors.filter(co => typeIsExternal(co.co_auditor_type) && (co.external_auditor_id != null)).map(co => Number(co.external_auditor_id)))];
    let userNames = {}; let extNames = {};
    if (userIds.length > 0) {
      const [uRows] = await db.query('SELECT user_id, employee_name FROM users WHERE user_id IN (?)', [userIds]);
      uRows.forEach(r => { userNames[r.user_id] = r.employee_name; });
    }
    if (extIds.length > 0) {
      const [eRows] = await db.query('SELECT external_auditor_id, full_name FROM sat_external_auditors WHERE external_auditor_id IN (?)', [extIds]);
      eRows.forEach(r => { extNames[r.external_auditor_id] = r.full_name; });
    }
    const coauditorValues = sat_coauditors.map(co => {
      const type = typeIsExternal(co.co_auditor_type) ? 1 : 0;
      const userId = co.co_auditor_user_id != null && type === 0 ? Number(co.co_auditor_user_id) : null;
      const extId = co.external_auditor_id != null && type === 1 ? Number(co.external_auditor_id) : null;
      const name = type === 0 ? (userNames[userId] || co.co_auditor_name || '') : (extNames[extId] || co.co_auditor_name || '');
      return [auditId, name, type, userId, extId];
    });
    await db.query(
      'INSERT INTO sat_coauditors (audit_id, co_auditor_name, is_external, co_auditor_user_id, external_auditor_id) VALUES ?',
      [coauditorValues]
    );
  }
  
  if (sat_auditees && Array.isArray(sat_auditees) && sat_auditees.length > 0) {
    const auditeeValues = sat_auditees
      .map((auditeeId) => [auditId, Number(auditeeId)])
      .filter(([, id]) => !isNaN(id) && id > 0);
    if (auditeeValues.length > 0) {
      await db.query('INSERT INTO sat_auditees (audit_id, auditee_id) VALUES ?', [auditeeValues]);
    }
  }
  
  // Create sat_submissions (observations) if provided
  if (observations && Array.isArray(observations) && observations.length > 0) {
    const openingSubmissionIds = [];
    
    for (const obs of observations) {
      const submissionData = {
        audit_id: auditId,
        submitter_id: obs.submitter_id,
        form_id: obs.form_id,
        submission_type_id: 1 // Opening observation by default
      };
      // Pass skipStatusUpdate=true to avoid circular dependency during audit creation
      const submissionId = await createSubmission(submissionData, obs.sat_answers || [], db, true);
      openingSubmissionIds.push(submissionId);
    }
    
    // Insert into sat_opening_closing_map table for each opening observation
    // closing_sub_id will be NULL initially (added later when closing form is submitted)
    for (const subId of openingSubmissionIds) {
      try {
        await insertOpening(auditId, subId, db);
      } catch (e) {
        console.error('Error inserting into sat_opening_closing_map:', e.message);
      }
    }
    
    // Update audit status once after all sat_submissions are created
    await updateAuditStatusBasedOnForms(auditId, db);
  }
  
  return auditId;
};

// Helper function to update audit status based on opening-closing mapping.
// Per audit, compare opening observations (form_type_id = 1) to linked closings:
// - 1 = Open — no observation has a closing yet (closed count = 0), or no opening observations
// - 2 = Closed — closed count equals open observation count (every opening has a closing)
// - 3 = In Progress — at least one closing exists, but closed count < open observation count
const updateAuditStatusBasedOnForms = async (auditId, connection = null) => {
  const db = connection || pool;

  let totalOpenings = 0;
  let openingsWithClosing = 0;
  try {
    await ensureOpeningRowsForAudit(auditId, db);

    const [totalRows] = await db.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
       WHERE s.audit_id = ? AND f.form_type_id = 1`,
      [auditId]
    );
    totalOpenings = Number(totalRows[0]?.c ?? 0);

    const [closedRows] = await db.query(
      'SELECT COUNT(*) AS c FROM sat_opening_closing_map WHERE audit_id = ? AND closing_sub_id IS NOT NULL',
      [auditId]
    );
    openingsWithClosing = Number(closedRows[0]?.c ?? 0);
  } catch (e) {
    // sat_opening_closing_map may not exist
  }

  // Closed: at least one opening observation and every opening has a linked closing
  if (totalOpenings > 0 && openingsWithClosing === totalOpenings) {
    await db.query('UPDATE sat_audits SET audit_status_id = 2 WHERE audit_id = ?', [auditId]);
    return;
  }

  // In Progress: some but not all observations closed (0 < closed < total openings)
  if (totalOpenings > 0 && openingsWithClosing > 0 && openingsWithClosing < totalOpenings) {
    await db.query('UPDATE sat_audits SET audit_status_id = 3 WHERE audit_id = ?', [auditId]);
    return;
  }

  // Open: no closings yet against this audit’s openings, or no opening observations mapped
  await db.query('UPDATE sat_audits SET audit_status_id = 1 WHERE audit_id = ?', [auditId]);
};

const updateAudit = async (id, updateData, sat_coauditors, sat_auditees, connection = null) => {
  const db = connection || pool;
  const { auditor_id, audit_name, location, audit_status_id, observed_at } = updateData;
  
  const updateFields = [];
  const updateValues = [];
  
  if (auditor_id !== undefined) { updateFields.push('auditor_id = ?'); updateValues.push(auditor_id); }
  if (audit_name !== undefined) { updateFields.push('audit_name = ?'); updateValues.push(audit_name); }
  if (location !== undefined) { updateFields.push('location = ?'); updateValues.push(location); }
  if (audit_status_id !== undefined) { updateFields.push('audit_status_id = ?'); updateValues.push(audit_status_id); }
  if (observed_at !== undefined) { updateFields.push('observed_at = ?'); updateValues.push(observed_at); }
  
  if (updateFields.length > 0) {
    updateValues.push(id);
    const [result] = await db.query(
      `UPDATE sat_audits SET ${updateFields.join(', ')} WHERE audit_id = ?`,
      updateValues
    );
    if (result.affectedRows === 0) return false;
  }
  
  if (sat_coauditors && Array.isArray(sat_coauditors)) {
    await db.query('DELETE FROM sat_coauditors WHERE audit_id = ?', [id]);
    if (sat_coauditors.length > 0) {
      const typeIsExternal = (t) => t === 1 || t === '1';
      const userIds = [...new Set(sat_coauditors.filter(co => !typeIsExternal(co.co_auditor_type) && co.co_auditor_user_id != null).map(co => Number(co.co_auditor_user_id)))];
      const extIds = [...new Set(sat_coauditors.filter(co => typeIsExternal(co.co_auditor_type) && co.external_auditor_id != null).map(co => Number(co.external_auditor_id)))];
      let userNames = {}; let extNames = {};
      if (userIds.length > 0) {
        const [uRows] = await db.query('SELECT user_id, employee_name FROM users WHERE user_id IN (?)', [userIds]);
        uRows.forEach(r => { userNames[r.user_id] = r.employee_name; });
      }
      if (extIds.length > 0) {
        const [eRows] = await db.query('SELECT external_auditor_id, full_name FROM sat_external_auditors WHERE external_auditor_id IN (?)', [extIds]);
        eRows.forEach(r => { extNames[r.external_auditor_id] = r.full_name; });
      }
      const coauditorValues = sat_coauditors.map(co => {
        const type = typeIsExternal(co.co_auditor_type) ? 1 : 0;
        const userId = co.co_auditor_user_id != null && type === 0 ? Number(co.co_auditor_user_id) : null;
        const extId = co.external_auditor_id != null && type === 1 ? Number(co.external_auditor_id) : null;
        const name = type === 0 ? (userNames[userId] || co.co_auditor_name || '') : (extNames[extId] || co.co_auditor_name || '');
        return [id, name, type, userId, extId];
      });
      await db.query(
        'INSERT INTO sat_coauditors (audit_id, co_auditor_name, is_external, co_auditor_user_id, external_auditor_id) VALUES ?',
        [coauditorValues]
      );
    }
  }
  
  if (sat_auditees && Array.isArray(sat_auditees)) {
    await db.query('DELETE FROM sat_auditees WHERE audit_id = ?', [id]);
    if (sat_auditees.length > 0) {
      const auditeeValues = sat_auditees.map(auditeeId => [id, auditeeId]);
      await db.query('INSERT INTO sat_auditees (audit_id, auditee_id) VALUES ?', [auditeeValues]);
    }
  }
  
  // Automatically update audit status based on attached forms
  // Only update if audit_status_id was not explicitly provided in updateData
  if (audit_status_id === undefined) {
    await updateAuditStatusBasedOnForms(id, db);
  }
  
  return true;
};

const deleteAudit = async (id) => {
  const [result] = await pool.query('DELETE FROM sat_audits WHERE audit_id = ?', [id]);
  return result.affectedRows > 0;
};

/** Update only the audit status (e.g. manual "Set to In Progress"). */
const updateAuditStatus = async (id, audit_status_id) => {
  const [result] = await pool.query('UPDATE sat_audits SET audit_status_id = ? WHERE audit_id = ?', [audit_status_id, id]);
  return result.affectedRows > 0;
};

/** Get distinct forms added to an audit (from sat_submissions joined with sat_form_details). */
const getFormsByAuditId = async (auditId) => {
  const [rows] = await pool.query(
    `SELECT DISTINCT f.form_id, f.title, ft.form_type_id, ft.form_type
     FROM sat_submissions s
     INNER JOIN sat_form_details f ON s.form_id = f.form_id
     LEFT JOIN sat_forms_type ft ON f.form_type_id = ft.form_type_id
     WHERE s.audit_id = ?
     ORDER BY ft.form_type_id, f.title`,
    [auditId]
  );
  return rows;
};

/**
 * Returns true if the user is allowed to add observations (Opening Forms) to this audit.
 * Only the auditor or internal co-auditors can add observations; external co-auditors cannot.
 * @param {number} auditId
 * @param {number} userId
 * @returns {Promise<boolean>}
 */
const canUserAddObservations = async (auditId, userId) => {
  if (!auditId || !userId) return false;
  const audit = await getAuditById(auditId);
  if (!audit) return false;
  if (Number(audit.auditor_id) === Number(userId)) return true;
  const internalCoauditor = (audit.sat_coauditors || []).find(
    (c) => Number(c.co_auditor_user_id) === Number(userId) && (Number(c.co_auditor_type) === 0 || c.co_auditor_type === false || c.co_auditor_type === '0')
  );
  return !!internalCoauditor;
};

/**
 * Add observations (sat_submissions) to an existing audit
 * @param {number} auditId - The audit ID to add observations to
 * @param {Array} observations - Array of observation objects with submitter_id, form_id, sat_answers
 * @param {number} opening_sub_id - Optional opening submission ID (for closing observations)
 * @param {object} connection - Optional database connection
 * @returns {Promise<Array>} - Array of created submission IDs
 */
const addObservationsToAudit = async (auditId, observations, opening_sub_id = null, connection = null) => {
  const db = connection || pool;
  
  if (!observations || !Array.isArray(observations) || observations.length === 0) {
    throw new Error('At least one observation is required');
  }
  
  const submissionIds = [];
  let closingOpeningSubId = opening_sub_id != null ? Number(opening_sub_id) : null;

  for (const obs of observations) {
    const [formTypeRows] = await db.query(
      'SELECT form_type_id FROM sat_form_details WHERE form_id = ?',
      [obs.form_id]
    );
    const formTypeId = Number(formTypeRows[0]?.form_type_id ?? obs.submission_type_id ?? 1);

    const submissionData = {
      audit_id: auditId,
      submitter_id: obs.submitter_id,
      form_id: obs.form_id,
      submission_type_id: formTypeId
    };
    const submissionId = await createSubmission(submissionData, obs.sat_answers || [], db, true);
    submissionIds.push(submissionId);

    if (formTypeId === 1) {
      try {
        await insertOpening(auditId, submissionId, db);
      } catch (e) {
        console.error('Error inserting opening into sat_opening_closing_map:', e.message);
      }
    } else if (formTypeId === 2) {
      let openingSubIdToUse = closingOpeningSubId;
      if (openingSubIdToUse == null || Number.isNaN(openingSubIdToUse)) {
        const firstOpen = await getFirstOpenOpeningByAuditId(auditId, db);
        if (firstOpen) openingSubIdToUse = Number(firstOpen.opening_sub_id);
      }
      if (openingSubIdToUse != null && !Number.isNaN(openingSubIdToUse)) {
        try {
          await upsertMapping(auditId, openingSubIdToUse, submissionId, db);
        } catch (e) {
          console.error('Error updating sat_opening_closing_map for closing observation:', e.message);
        }
      }
    }
  }
  
  // Update audit status after all observations are added
  await updateAuditStatusBasedOnForms(auditId, db);
  
  return submissionIds;
};

module.exports = {
  getAllAudits,
  getAuditById,
  createAudit,
  updateAudit,
  deleteAudit,
  updateAuditStatus,
  updateAuditStatusBasedOnForms,
  getFormsByAuditId,
  canUserAddObservations,
  addObservationsToAudit
};

