const pool = require("../config/db");
const { getMappingsByAuditId, ensureOpeningRowsForAudit } = require("./openingClosingMapRepository");

/** Legacy DB column name in sat_answers_media (imported schemas use this typo). */
const MEDIA_OCCURRENCE_COL = "ocurrence";

const submissionListQuery = `
  SELECT s.*, 
         u.employee_name as submitter_name,
         f.title as form_title,
         f.form_type_id,
         a.audit_name
  FROM sat_submissions s
  LEFT JOIN users u ON s.submitter_id = u.user_id
  LEFT JOIN sat_form_details f ON s.form_id = f.form_id
  LEFT JOIN sat_audits a ON s.audit_id = a.audit_id
`;

const getAllSubmissions = async () => {
  const [rows] = await pool.query(submissionListQuery);
  return rows;
};

const getSubmissionsByAuditId = async (auditId) => {
  const [rows] = await pool.query(submissionListQuery + ' WHERE s.audit_id = ? ORDER BY s.created_at DESC', [auditId]);
  return rows;
};

/** Same as getSubmissionsByAuditId but adds linked_opening_sub_id (for closing subs) and linked_closing_sub_id (for opening subs) from sat_opening_closing_map. */
const getSubmissionsByAuditIdWithMapping = async (auditId) => {
  const rows = await getSubmissionsByAuditId(auditId);
  try {
    await ensureOpeningRowsForAudit(auditId);
  } catch (e) {
    // sat_opening_closing_map table may not exist yet
  }
  let mappings = [];
  try {
    mappings = await getMappingsByAuditId(auditId);
  } catch (e) {
    // sat_opening_closing_map table may not exist yet
  }
  const openingToClosing = new Map(mappings.map((m) => [Number(m.opening_sub_id), m.closing_sub_id]));
  const closingToOpening = new Map(mappings.map((m) => [Number(m.closing_sub_id), m.opening_sub_id]));
  return rows.map((s) => {
    const sid = Number(s.submission_id);
    const linked_closing_sub_id = openingToClosing.get(sid) ?? openingToClosing.get(s.submission_id) ?? null;
    const linked_opening_sub_id = closingToOpening.get(sid) ?? closingToOpening.get(s.submission_id) ?? null;
    return { ...s, linked_opening_sub_id, linked_closing_sub_id };
  });
};

const getSubmissionById = async (id) => {
  const [submissionRows] = await pool.query(`
    SELECT s.*, 
           u.employee_name as submitter_name,
           f.title as form_title,
           a.audit_name
    FROM sat_submissions s
    LEFT JOIN users u ON s.submitter_id = u.user_id
    LEFT JOIN sat_form_details f ON s.form_id = f.form_id
    LEFT JOIN sat_audits a ON s.audit_id = a.audit_id
    WHERE s.submission_id = ?
  `, [id]);
  
  if (submissionRows.length === 0) return null;
  
  const [answerRows] = await pool.query(`
    SELECT a.*, q.question
    FROM sat_answers a
    LEFT JOIN sat_questions q ON a.question_id = q.question_id
    WHERE a.submission_id = ?
  `, [id]);
  
  for (let answer of answerRows) {
    if (answer.is_media) {
      // Read from sat_answers_media table: images are stored per answer (answers_id) with occurrence 1, 2, 3...
      const [mediaRows] = await pool.query(
        `SELECT ${MEDIA_OCCURRENCE_COL} AS occurrence, media FROM sat_answers_media WHERE answers_id = ?`,
        [answer.answers_id]
      );
      answer.media = mediaRows.map((row) => {
        let mediaValue = null;
        if (row.media) {
          if (Buffer.isBuffer(row.media)) {
            // Client sends data URL or base64 string; we store it as UTF-8, so read it back as string
            mediaValue = row.media.toString('utf8');
          } else if (typeof row.media === 'string') {
            mediaValue = row.media;
          }
        }
        return { occurrence: row.occurrence, media: mediaValue };
      });
    }
  }
  
  return {
    ...submissionRows[0],
    sat_answers: answerRows
  };
};

const createSubmission = async (submissionData, sat_answers, connection = null, skipStatusUpdate = false) => {
  const db = connection || pool;
  const { audit_id, submitter_id, form_id, submission_type_id } = submissionData;
  const typeId = submission_type_id != null ? Number(submission_type_id) : 1;

  const [result] = await db.query(
    'INSERT INTO sat_submissions (audit_id, submitter_id, form_id, submission_type_id, created_at) VALUES (?, ?, ?, ?, NOW())',
    [audit_id, submitter_id, form_id, typeId]
  );

  const submissionId = result.insertId;

  if (sat_answers && Array.isArray(sat_answers) && sat_answers.length > 0) {
    for (const answer of sat_answers) {
      const { question_id, is_media, answer_value, media } = answer;
      if (!question_id) continue;

      const [answerResult] = await db.query(
        'INSERT INTO sat_answers (submission_id, question_id, is_media, answer_value) VALUES (?, ?, ?, ?)',
        [submissionId, question_id, is_media || false, answer_value || null]
      );

      const answerId = answerResult.insertId;

      if (is_media && media && Array.isArray(media) && media.length > 0) {
        const mediaValues = media.map((m, index) => [answerId, index + 1, m]);
        await db.query(
          `INSERT INTO sat_answers_media (answers_id, ${MEDIA_OCCURRENCE_COL}, media) VALUES ?`,
          [mediaValues]
        );
      }
    }
  }

  if (!skipStatusUpdate) {
    const { updateAuditStatusBasedOnForms } = require("./auditsRepository");
    await updateAuditStatusBasedOnForms(audit_id, db);
  }

  return submissionId;
};

const updateSubmission = async (id, updateData, sat_answers, connection = null) => {
  const db = connection || pool;
  const { audit_id, submitter_id, form_id, submission_type_id } = updateData;
  
  const updateFields = [];
  const updateValues = [];
  
  if (audit_id !== undefined) { updateFields.push('audit_id = ?'); updateValues.push(audit_id); }
  if (submitter_id !== undefined) { updateFields.push('submitter_id = ?'); updateValues.push(submitter_id); }
  if (form_id !== undefined) { updateFields.push('form_id = ?'); updateValues.push(form_id); }
  if (submission_type_id !== undefined) { updateFields.push('submission_type_id = ?'); updateValues.push(submission_type_id); }
  
  if (updateFields.length > 0) {
    updateValues.push(id);
    const [result] = await db.query(
      `UPDATE sat_submissions SET ${updateFields.join(', ')} WHERE submission_id = ?`,
      updateValues
    );
    if (result.affectedRows === 0) return false;
  }
  
  if (sat_answers && Array.isArray(sat_answers)) {
    const [existingAnswers] = await db.query('SELECT answers_id FROM sat_answers WHERE submission_id = ?', [id]);
    const answerIds = existingAnswers.map(a => a.answers_id);
    
    if (answerIds.length > 0) {
      await db.query('DELETE FROM sat_answers_media WHERE answers_id IN (?)', [answerIds]);
      await db.query('DELETE FROM sat_answers WHERE submission_id = ?', [id]);
    }
    
    if (sat_answers.length > 0) {
      for (const answer of sat_answers) {
        const { question_id, is_media, answer_value, media } = answer;
        if (!question_id) continue;
        
        const [answerResult] = await db.query(
          'INSERT INTO sat_answers (submission_id, question_id, is_media, answer_value) VALUES (?, ?, ?, ?)',
          [id, question_id, is_media || false, answer_value || null]
        );
        
        const answerId = answerResult.insertId;
        
        if (is_media && media && Array.isArray(media) && media.length > 0) {
          const mediaValues = media.map((m, index) => [answerId, index + 1, m]);
          await db.query(
          `INSERT INTO sat_answers_media (answers_id, ${MEDIA_OCCURRENCE_COL}, media) VALUES ?`,
          [mediaValues]
        );
        }
      }
    }
  }
  
  // Automatically update audit status based on attached forms if form_id was updated
  if (form_id !== undefined) {
    // Get the audit_id from the submission
    const [submissionRows] = await db.query('SELECT audit_id FROM sat_submissions WHERE submission_id = ?', [id]);
    if (submissionRows.length > 0) {
      const { updateAuditStatusBasedOnForms } = require("./auditsRepository");
      await updateAuditStatusBasedOnForms(submissionRows[0].audit_id, db);
    }
  }
  
  return true;
};

const deleteSubmission = async (id, connection = null) => {
  const db = connection || pool;
  
  const [existingAnswers] = await db.query('SELECT answers_id FROM sat_answers WHERE submission_id = ?', [id]);
  const answerIds = existingAnswers.map(a => a.answers_id);
  
  if (answerIds.length > 0) {
    await db.query('DELETE FROM sat_answers_media WHERE answers_id IN (?)', [answerIds]);
  }
  
  await db.query('DELETE FROM sat_answers WHERE submission_id = ?', [id]);
  
  const [result] = await db.query('DELETE FROM sat_submissions WHERE submission_id = ?', [id]);
  return result.affectedRows > 0;
};

/**
 * Get a single image/media from sat_answers_media table by answers_id and occurrence.
 * Returns { buffer, contentType } for serving as binary (used by GET /:id/media/:answersId/:occurrence).
 * sat_answers_media.media is LONGBLOB; we store the client's data URL as UTF-8, so read with toString('utf8') then parse.
 */
const getMediaByAnswerAndOccurrence = async (answersId, occurrence) => {
  const [rows] = await pool.query(
    `SELECT media FROM sat_answers_media WHERE answers_id = ? AND ${MEDIA_OCCURRENCE_COL} = ?`,
    [answersId, occurrence]
  );
  if (!rows.length || !rows[0].media) return null;
  const row = rows[0];
  let dataUrl = null;
  if (Buffer.isBuffer(row.media)) {
    dataUrl = row.media.toString('utf8');
  } else if (typeof row.media === 'string') {
    dataUrl = row.media;
  }
  if (!dataUrl || typeof dataUrl !== 'string') return null;

  let base64 = null;
  let contentType = 'image/jpeg';

  if (dataUrl.startsWith('data:')) {
    const commaIdx = dataUrl.indexOf(',');
    if (commaIdx === -1) return null;
    const mimeMatch = dataUrl.match(/^data:([^;]+);/);
    if (mimeMatch) contentType = mimeMatch[1].trim();
    base64 = dataUrl.slice(commaIdx + 1).replace(/\s/g, '');
  } else {
    // Stored as raw base64 string (no data: prefix)
    base64 = dataUrl.replace(/\s/g, '');
  }

  if (!base64) return null;
  try {
    const buffer = Buffer.from(base64, 'base64');
    if (buffer.length === 0) return null;
    return { buffer, contentType };
  } catch (e) {
    return null;
  }
};

/** Check that an answer with answers_id belongs to the given submission_id */
const isAnswerInSubmission = async (submissionId, answersId) => {
  const [rows] = await pool.query(
    'SELECT 1 FROM sat_answers WHERE submission_id = ? AND answers_id = ?',
    [submissionId, answersId]
  );
  return rows.length > 0;
};

module.exports = {
  getAllSubmissions,
  getSubmissionsByAuditId,
  getSubmissionsByAuditIdWithMapping,
  getSubmissionById,
  getMediaByAnswerAndOccurrence,
  isAnswerInSubmission,
  createSubmission,
  updateSubmission,
  deleteSubmission
};

