const pool = require("../config/db");
const { getOptionsByQuestionIds } = require("./questionOptionsRepository");
const { getCombinedQuestionsByQuestionIds } = require("./combinedQuestionRepository");

/** Build action string: "who did the action" from sat_form_logs.created_by (user_id) maps to users.user_id; resolve employee_id for display. */
const buildFormLogActionWithPerformer = async (db, actionType, userId) => {
  if (!userId) return actionType;
  const [rows] = await db.query(
    'SELECT employee_id FROM users WHERE user_id = ?',
    [userId]
  );
  const employeeId = rows[0]?.employee_id;
  return employeeId
    ? `${actionType} by ${employeeId}`
    : `${actionType} by (user_id: ${userId})`;
};

const FORM_LOG_ACTION_CREATED = 'CREATED';
const FORM_LOG_ACTION_UPDATED = 'UPDATED';

/**
 * Sub-API helper: insert one row into sat_form_logs. Used by update form flow and by POST /api/forms/logs.
 * @param {number} formId - form_id
 * @param {number} whoDidAction - user_id (maps to users.user_id)
 * @param {string} actionType - 'CREATED' or 'UPDATED'
 * @param {object} connection - optional (for transaction)
 */
/**
 * Get form logs by joining sat_form_logs with users directly (no view dependency).
 */
const getFormLogsFromView = async (limit = 500) => {
  const limitNum = Math.min(Math.max(1, parseInt(limit, 10) || 500), 1000);
  const [rows] = await pool.query(
    `SELECT
       fl.user_id,
       COALESCE(u.employee_name, u.employee_id, CONCAT('user_id:', fl.user_id)) AS user_name,
       fl.title        AS form_name,
       fl.form_type,
       fl.description,
       fl.created_by,
       fl.location_name AS location,
       fl.created_at
     FROM sat_form_logs fl
     LEFT JOIN users u ON fl.user_id = u.user_id
     ORDER BY fl.created_at DESC
     LIMIT ?`,
    [limitNum]
  );
  return rows || [];
};

const insertFormLog = async (formId, whoDidAction, actionType, connection = null) => {
  const db = connection || pool;
  const [formRows] = await db.query(
    'SELECT f.form_type_id, f.form_parent_id, f.title, f.description, f.created_by, f.created_date FROM sat_form_details f WHERE f.form_id = ?',
    [formId]
  );
  if (formRows.length === 0) return;
  const f = formRows[0];
  const [ftRows] = await db.query('SELECT form_type FROM sat_forms_type WHERE form_type_id = ?', [f.form_type_id]);
  const formTypeName = ftRows[0]?.form_type || null;
  let locationName = null;
  const [lfdRows] = await db.query('SELECT location_id FROM sat_location_forms_details WHERE form_id = ?', [formId]);
  if (lfdRows.length > 0) {
    const ids = lfdRows.map(r => r.location_id);
    const [locRows] = await db.query('SELECT location_name FROM locations WHERE location_id IN (?)', [ids]);
    locationName = (locRows || []).map(r => r.location_name).filter(Boolean).join(', ');
  }
  const actionValue = await buildFormLogActionWithPerformer(db, actionType, whoDidAction);
  await db.query(
    `INSERT INTO sat_form_logs (user_id, form_id, form_type, form_parent_id, title, description, created_by, created_date, location_name, action, created_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())`,
    [whoDidAction, formId, formTypeName, f.form_parent_id, f.title, f.description || null, whoDidAction, f.created_date || new Date(), locationName, actionValue]
  );
};

const getAllForms = async () => {
  const [rows] = await pool.query(`
    WITH RECURSIVE form_tree AS (
      SELECT form_id, form_parent_id, 1 AS version
      FROM sat_form_details
      WHERE form_parent_id IS NULL
      UNION ALL
      SELECT f.form_id, f.form_parent_id, ft.version + 1
      FROM sat_form_details f
      INNER JOIN form_tree ft ON f.form_parent_id = ft.form_id
    )
    SELECT 
      f.form_id, f.form_type_id, f.form_parent_id, f.title, f.description, f.created_by, f.created_date, f.end_date,
      ft.form_type, 
      u.employee_name as created_by_name,
      COUNT(DISTINCT fqm.question_id) as question_count,
      (af.form_id IS NOT NULL) as is_archived,
      COALESCE(fvt.version, 1) AS version
    FROM sat_form_details f
    LEFT JOIN form_tree fvt ON f.form_id = fvt.form_id
    LEFT JOIN sat_forms_type ft ON f.form_type_id = ft.form_type_id
    LEFT JOIN users u ON f.created_by = u.user_id
    LEFT JOIN sat_form_question_map fqm ON f.form_id = fqm.form_id
    LEFT JOIN sat_archive_form af ON f.form_id = af.form_id
    GROUP BY f.form_id, f.form_type_id, f.form_parent_id, f.title, f.description, f.created_by, f.created_date, f.end_date, ft.form_type, u.employee_name, af.form_id, fvt.version
  `);
  
  return rows.map(row => ({
    ...row,
    question_count: parseInt(row.question_count) || 0,
    sat_questions: [],
    is_archived: Boolean(row.is_archived),
    version: parseInt(row.version) || 1
  }));
};

/** Returns only forms that are not in sat_archive_form (for Update Form dropdown and audit form selection). */
const getFormsExcludeArchived = async () => {
  const [rows] = await pool.query(`
    WITH RECURSIVE form_tree AS (
      SELECT form_id, form_parent_id, 1 AS version
      FROM sat_form_details
      WHERE form_parent_id IS NULL
      UNION ALL
      SELECT f.form_id, f.form_parent_id, ft.version + 1
      FROM sat_form_details f
      INNER JOIN form_tree ft ON f.form_parent_id = ft.form_id
    )
    SELECT 
      f.form_id, f.form_type_id, f.form_parent_id, f.title, f.description, f.created_by, f.created_date, f.end_date,
      ft.form_type, 
      u.employee_name as created_by_name,
      COUNT(DISTINCT fqm.question_id) as question_count,
      COALESCE(fvt.version, 1) AS version
    FROM sat_form_details f
    LEFT JOIN form_tree fvt ON f.form_id = fvt.form_id
    LEFT JOIN sat_forms_type ft ON f.form_type_id = ft.form_type_id
    LEFT JOIN users u ON f.created_by = u.user_id
    LEFT JOIN sat_form_question_map fqm ON f.form_id = fqm.form_id
    LEFT JOIN sat_archive_form af ON f.form_id = af.form_id
    WHERE af.form_id IS NULL
      AND f.form_id NOT IN (SELECT form_parent_id FROM sat_form_details WHERE form_parent_id IS NOT NULL)
    GROUP BY f.form_id, f.form_type_id, f.form_parent_id, f.title, f.description, f.created_by, f.created_date, f.end_date, ft.form_type, u.employee_name, fvt.version
  `);
  return rows.map(row => ({
    ...row,
    question_count: parseInt(row.question_count) || 0,
    sat_questions: [],
    is_archived: false,
    version: parseInt(row.version) || 1
  }));
};

const getFormById = async (id) => {
  const [formRows] = await pool.query(`
    SELECT f.*, ft.form_type, u.employee_name as created_by_name,
           (af.form_id IS NOT NULL) as is_archived
    FROM sat_form_details f
    LEFT JOIN sat_forms_type ft ON f.form_type_id = ft.form_type_id
    LEFT JOIN users u ON f.created_by = u.user_id
    LEFT JOIN sat_archive_form af ON f.form_id = af.form_id
    WHERE f.form_id = ?
  `, [id]);
  
  if (formRows.length === 0) return null;
  
  const [questionRows] = await pool.query(`
    SELECT q.* 
    FROM sat_questions q
    INNER JOIN sat_form_question_map fqm ON q.question_id = fqm.question_id
    WHERE fqm.form_id = ?
  `, [id]);

  const questionIds = questionRows.map((r) => r.question_id).filter(Boolean);
  const optionsMap = questionIds.length ? await getOptionsByQuestionIds(questionIds) : {};
  const combinedRows = await getCombinedQuestionsByQuestionIds(questionIds);
  const mainToChildren = new Map();
  const childToMain = new Map();
  combinedRows.forEach((row) => {
    const mainId = row.main_question_id;
    const childId = row.child_question_id;
    if (!mainToChildren.has(mainId)) mainToChildren.set(mainId, []);
    mainToChildren.get(mainId).push(childId);
    childToMain.set(childId, mainId);
  });
  const questionsWithOptions = questionRows.map((q) => ({
    ...q,
    allows_media: Boolean(q.allows_media),
    ai_fill: Boolean(q.ai_fill),
    options: optionsMap[q.question_id] || [],
    child_question_ids: mainToChildren.get(q.question_id) || [],
    main_question_id: childToMain.get(q.question_id) ?? null
  }));
  
  const [locationRows] = await pool.query(`
    SELECT l.* 
    FROM locations l
    INNER JOIN sat_location_forms_details lfd ON l.location_id = lfd.location_id
    WHERE lfd.form_id = ?
  `, [id]);
  
  return {
    ...formRows[0],
    is_archived: Boolean(formRows[0].is_archived),
    sat_questions: questionsWithOptions,
    locations: locationRows
  };
};

const createForm = async (formData, questionIds, locationIds, connection = null) => {
  const db = connection || pool;
  const { form_type_id, form_parent_id, title, description, created_by, created_date, end_date } = formData;
  
  const [result] = await db.query(
    'INSERT INTO sat_form_details (form_type_id, form_parent_id, title, description, created_by, created_date, end_date) VALUES (?, ?, ?, ?, ?, ?, ?)',
    [form_type_id, form_parent_id || null, title, description || null, created_by, created_date || new Date(), end_date || null]
  );
  
  const formId = result.insertId;

  // sat_form_logs: user_id, form_id, form_type, form_parent_id, title, description, created_by, created_date, location_name, action, created_at (no delete logging)
  const [formTypeRows] = await db.query('SELECT form_type FROM sat_forms_type WHERE form_type_id = ?', [form_type_id]);
  const formTypeName = formTypeRows[0]?.form_type || null;
  let locationName = null;
  if (locationIds && Array.isArray(locationIds) && locationIds.length > 0) {
    const [locRows] = await db.query('SELECT location_name FROM locations WHERE location_id IN (?)', [locationIds]);
    locationName = (locRows || []).map(r => r.location_name).filter(Boolean).join(', ');
  }
  const actionValue = await buildFormLogActionWithPerformer(db, FORM_LOG_ACTION_CREATED, created_by);
  await db.query(
    `INSERT INTO sat_form_logs (user_id, form_id, form_type, form_parent_id, title, description, created_by, created_date, location_name, action, created_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())`,
    [created_by, formId, formTypeName, form_parent_id || null, title, description || null, created_by, created_date || new Date(), locationName, actionValue]
  );

  // When creating a child form, add the parent to sat_archive_form (parent forms are view-only, not updatable, not addable to sat_audits)
  if (form_parent_id) {
    await db.query('INSERT IGNORE INTO sat_archive_form (form_id) VALUES (?)', [form_parent_id]);
  }
  
  if (questionIds && Array.isArray(questionIds) && questionIds.length > 0) {
    const uniqueQuestionIds = [...new Set(questionIds)];
    const questionValues = uniqueQuestionIds.map(qId => [formId, qId]);
    await db.query('INSERT INTO sat_form_question_map (form_id, question_id) VALUES ?', [questionValues]);
  }
  
  if (locationIds && Array.isArray(locationIds) && locationIds.length > 0) {
    const uniqueLocationIds = [...new Set(locationIds)];
    const locationValues = uniqueLocationIds.map(lId => [formId, lId]);
    await db.query('INSERT INTO sat_location_forms_details (form_id, location_id) VALUES ?', [locationValues]);
  }
  
  return formId;
};

const updateForm = async (id, updateData, questionIds, locationIds, connection = null, updatedByUserId = null) => {
  const db = connection || pool;

  const [archived] = await db.query('SELECT 1 FROM sat_archive_form WHERE form_id = ?', [id]);
  if (archived.length > 0) return false; // Parent/archived forms cannot be updated

  const { form_type_id, form_parent_id, title, description, created_by, created_date, end_date } = updateData;

  const updateFields = [];
  const updateValues = [];

  if (form_type_id !== undefined) { updateFields.push('form_type_id = ?'); updateValues.push(form_type_id); }
  if (form_parent_id !== undefined) { updateFields.push('form_parent_id = ?'); updateValues.push(form_parent_id); }
  if (title !== undefined) { updateFields.push('title = ?'); updateValues.push(title); }
  if (description !== undefined) { updateFields.push('description = ?'); updateValues.push(description); }
  if (created_by !== undefined) { updateFields.push('created_by = ?'); updateValues.push(created_by); }
  if (created_date !== undefined) { updateFields.push('created_date = ?'); updateValues.push(created_date); }
  if (end_date !== undefined) { updateFields.push('end_date = ?'); updateValues.push(end_date); }

  if (updateFields.length > 0) {
    updateValues.push(id);
    const [result] = await db.query(
      `UPDATE sat_form_details SET ${updateFields.join(', ')} WHERE form_id = ?`,
      updateValues
    );
    if (result.affectedRows === 0) return false;
  }

  if (questionIds && Array.isArray(questionIds)) {
    await db.query('DELETE FROM sat_form_question_map WHERE form_id = ?', [id]);
    if (questionIds.length > 0) {
      const uniqueQuestionIds = [...new Set(questionIds)];
      const questionValues = uniqueQuestionIds.map(qId => [id, qId]);
      await db.query('INSERT INTO sat_form_question_map (form_id, question_id) VALUES ?', [questionValues]);
    }
  }

  if (locationIds && Array.isArray(locationIds)) {
    await db.query('DELETE FROM sat_location_forms_details WHERE form_id = ?', [id]);
    if (locationIds.length > 0) {
      const uniqueLocationIds = [...new Set(locationIds)];
      const locationValues = uniqueLocationIds.map(lId => [id, lId]);
      await db.query('INSERT INTO sat_location_forms_details (form_id, location_id) VALUES ?', [locationValues]);
    }
  }

  // Log update to sat_form_logs so it appears in form logs (e.g. location/metadata changes)
  if (updatedByUserId != null) {
    const [formRows] = await db.query(
      'SELECT f.form_type_id, f.form_parent_id, f.title, f.description, f.created_by, f.created_date FROM sat_form_details f WHERE f.form_id = ?',
      [id]
    );
    if (formRows.length > 0) {
      const f = formRows[0];
      const [formTypeRows] = await db.query('SELECT form_type FROM sat_forms_type WHERE form_type_id = ?', [f.form_type_id]);
      const formTypeName = formTypeRows[0]?.form_type || null;
      let locationName = null;
      if (locationIds && Array.isArray(locationIds) && locationIds.length > 0) {
        const [locRows] = await db.query('SELECT location_name FROM locations WHERE location_id = ?', [locationIds[0]]);
        locationName = locRows[0]?.location_name || null;
      }
      await db.query(
        `INSERT INTO sat_form_logs (user_id, form_id, form_type, form_parent_id, title, description, created_by, created_date, location_name, action, created_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, NULL, ?, 'UPDATED', NOW())`,
        [updatedByUserId, id, formTypeName, f.form_parent_id || null, f.title, f.description || null, f.created_by, locationName]
      );
    }
  }

  return true;
};

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

module.exports = {
  getAllForms,
  getFormsExcludeArchived,
  getFormById,
  createForm,
  updateForm,
  deleteForm,
  insertFormLog,
  getFormLogsFromView
};

