const db = require("../config/db");

const Institution = {
  /**
   * Creates a new institution, now including the logo_url.
   */
  create: async ({ name, contact_email, contact_phone, address, logo_url }) => {
    const sql =
      'INSERT INTO institutions (name, contact_email, contact_phone, address, logo_url, status) VALUES (?, ?, ?, ?, ?, "inactive")';
    const [result] = await db.query(sql, [
      name,
      contact_email,
      contact_phone,
      address,
      logo_url, // Include new field
    ]);
    return { id: result.insertId, name, contact_email };
  },

  /**
   * Finds an institution by its ID, now joining with settings for a complete object.
   * This is more efficient for the 'getMyInstitution' controller.
   */
  findById: async (id) => {
    const sql = `
      SELECT i.*, s.sms_base_url, s.sms_sender_id,s.sms_api_key, s.timezone
      FROM institutions i
      LEFT JOIN settings s ON i.id = s.institution_id
      WHERE i.id = ?
    `;
    const [rows] = await db.query(sql, [id]);
    return rows[0];
  },

  /**
   * Retrieves all institutions.
   */
  findAll: async () => {
    const [rows] = await db.query(
      "SELECT * FROM institutions ORDER BY name ASC"
    );
    return rows;
  },

  /**
   * Updates an institution's details, now including the logo_url.
   */
  updateById: async (
    id,
    { name, contact_email, contact_phone, address, logo_url }
  ) => {
    // Build the query dynamically to only update fields that are provided
    const fieldsToUpdate = [];
    const values = [];

    if (name) {
      fieldsToUpdate.push("name = ?");
      values.push(name);
    }
    if (contact_email) {
      fieldsToUpdate.push("contact_email = ?");
      values.push(contact_email);
    }
    if (contact_phone) {
      fieldsToUpdate.push("contact_phone = ?");
      values.push(contact_phone);
    }
    if (address) {
      fieldsToUpdate.push("address = ?");
      values.push(address);
    }
    if (logo_url) {
      fieldsToUpdate.push("logo_url = ?");
      values.push(logo_url);
    }

    if (fieldsToUpdate.length === 0) return false; // Nothing to update

    const sql = `UPDATE institutions SET ${fieldsToUpdate.join(
      ", "
    )} WHERE id = ?`;
    values.push(id);

    const [result] = await db.query(sql, values);
    return result.affectedRows > 0;
  },

  /**
   * Updates the status of an institution.
   */
  setStatus: async (id, status) => {
    const sql = "UPDATE institutions SET status = ? WHERE id = ?";
    const [result] = await db.query(sql, [status, id]);
    return result.affectedRows > 0;
  },

  /**
   * Counts all active institutions.
   */
  countAllActive: async () => {
    const [rows] = await db.query(
      "SELECT COUNT(*) as count FROM institutions WHERE status = 'active'"
    );
    return rows[0].count;
  },

  // NOTE: The findActiveByInstitutionId function was for subscriptions and does not belong in this model.
  // It should be in the subscriptionModel.js. I've removed it for correctness.
};

module.exports = Institution;
