const pool = require('../config/db');
const { getOptionsByQuestionIds } = require('./questionOptionsRepository');

const getFormWithQuestions = async () => {
  const [rows] = await pool.query(`
    SELECT 
      f.form_id,
      ft.form_type as form_type_name,
      f.title,
      f.description,
      q.question_id,
      q.question,
      q.question_type_id,
      lfd.location_id,
      l.location_name
    FROM sat_form_details f
    LEFT JOIN sat_forms_type ft ON f.form_type_id = ft.form_type_id
    LEFT JOIN sat_form_question_map fqm ON f.form_id = fqm.form_id
    LEFT JOIN sat_questions q ON fqm.question_id = q.question_id
    LEFT JOIN sat_location_forms_details lfd ON f.form_id = lfd.form_id
    LEFT JOIN locations l ON lfd.location_id = l.location_id
    ORDER BY f.form_id, q.question_id, lfd.location_id
  `);
  
  // Group the results by form_id to create nested structure
  const formsMap = new Map();
  const locationsMap = new Map(); // Track unique locations per form
  
  rows.forEach(row => {
    const formId = row.form_id;
    
    if (!formsMap.has(formId)) {
      formsMap.set(formId, {
        form_id: row.form_id,
        form_type_name: row.form_type_name,
        title: row.title,
        description: row.description,
        sat_questions: [],
        locations: []
      });
      locationsMap.set(formId, new Set()); // Track unique location IDs for this form
    }
    
    // Add question if it exists (not null)
    if (row.question_id) {
      const questionExists = formsMap.get(formId).sat_questions.some(
        q => q.question_id === row.question_id
      );
      if (!questionExists) {
        formsMap.get(formId).sat_questions.push({
          question_id: row.question_id,
          question: row.question,
          question_type_id: row.question_type_id
        });
      }
    }
    
    // Add location if it exists (not null) and not already added
    if (row.location_id && !locationsMap.get(formId).has(row.location_id)) {
      locationsMap.get(formId).add(row.location_id);
      formsMap.get(formId).locations.push({
        location_id: row.location_id,
        location_name: row.location_name
      });
    }
  });
  
  return Array.from(formsMap.values());
};

const getFormById = async (id) => {
  const [rows] = await pool.query(`
    SELECT 
      f.form_id,
      ft.form_type as form_type_name,
      f.title,
      f.description,
      q.question_id,
      q.question,
      q.question_type_id,
      q.allows_media,
      lfd.location_id,
      l.location_name
    FROM sat_form_details f
    LEFT JOIN sat_forms_type ft ON f.form_type_id = ft.form_type_id
    LEFT JOIN sat_form_question_map fqm ON f.form_id = fqm.form_id
    LEFT JOIN sat_questions q ON fqm.question_id = q.question_id
    LEFT JOIN sat_location_forms_details lfd ON f.form_id = lfd.form_id
    LEFT JOIN locations l ON lfd.location_id = l.location_id
    WHERE f.form_id = ?
    ORDER BY q.question_id, lfd.location_id
  `, [id]);
  
  if (rows.length === 0) {
    return null;
  }
  
  // Build the form object with sat_questions array
  const form = {
    form_id: rows[0].form_id,
    form_type_name: rows[0].form_type_name,
    title: rows[0].title,
    description: rows[0].description,
    sat_questions: [],
    locations: []
  };
  
  const locationIds = new Set(); // Track unique location IDs
  const questionIds = [...new Set(rows.map(r => r.question_id).filter(Boolean))];
  const optionsMap = questionIds.length ? await getOptionsByQuestionIds(questionIds) : {};
  
  // Add sat_questions and locations to the form
  rows.forEach(row => {
    if (row.question_id) {
      const questionExists = form.sat_questions.some(
        q => q.question_id === row.question_id
      );
      if (!questionExists) {
        form.sat_questions.push({
          question_id: row.question_id,
          question: row.question,
          question_type_id: row.question_type_id,
          allows_media: Boolean(row.allows_media),
          options: optionsMap[row.question_id] || []
        });
      }
    }
    
    // Add location if it exists (not null) and not already added
    if (row.location_id && !locationIds.has(row.location_id)) {
      locationIds.add(row.location_id);
      form.locations.push({
        location_id: row.location_id,
        location_name: row.location_name
      });
    }
  });
  
  return form;
};

module.exports = {
  getFormWithQuestions,
  getFormById
};
