// backend/models/planModel.js
const db = require("../config/db");

const Plan = {
  /**
   * Creates a new subscription plan.
   * @param {object} planData - Data for the new plan.
   * @returns {Promise<object>} The new plan object with its ID.
   */
  create: async ({
    name,
    price,
    billing_interval,
    interval_count = 1,
    student_limit,
    trial_days = 0,
    features,
    status = "active",
  }) => {
    const featuresJson = JSON.stringify(features || {});
    const sql = `
      INSERT INTO plans 
        (name, price, billing_interval, interval_count, student_limit, trial_days, features, status) 
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `;
    const [result] = await db.query(sql, [
      name,
      price,
      billing_interval,
      interval_count,
      student_limit,
      trial_days,
      featuresJson,
      status,
    ]);
    return {
      id: result.insertId,
      name,
      price,
      billing_interval,
      interval_count,
      student_limit,
      trial_days,
      features,
      status,
    };
  },

  /**
   * Retrieves all subscription plans, with optional filtering.
   * @param {object} [filter={}] - Optional filter object, e.g., { status: 'active' }.
   * @returns {Promise<Array<object>>}
   */
  findAll: async (filter = {}) => {
    let sql = "SELECT * FROM plans";
    const params = [];
    const whereClauses = [];

    if (filter.status) {
      whereClauses.push("status = ?");
      params.push(filter.status);
    }

    if (whereClauses.length > 0) {
      sql += " WHERE " + whereClauses.join(" AND ");
    }

    sql += " ORDER BY price ASC, interval_count ASC";

    const [rows] = await db.query(sql, params);
    return rows;
  },

  /**
   * Finds a plan by its ID.
   * @param {number} id
   * @returns {Promise<object|undefined>}
   */
  findById: async (id) => {
    const [rows] = await db.query("SELECT * FROM plans WHERE id = ?", [id]);
    return rows[0];
  },

  /**
   * Updates an existing subscription plan. Only updates fields that are provided.
   * @param {number} id
   * @param {object} planData - The fields to update.
   * @returns {Promise<boolean>} True if the update was successful.
   */
  update: async (id, planData) => {
    const fields = [];
    const values = [];

    // Dynamically build the query based on what's provided
    for (const [key, value] of Object.entries(planData)) {
      if (value !== undefined && key !== "id") {
        // Ensure we don't try to update the ID
        fields.push(`${key} = ?`);
        // Stringify features if it's an object, otherwise use the value as is
        values.push(
          key === "features" && typeof value === "object"
            ? JSON.stringify(value)
            : value
        );
      }
    }

    if (fields.length === 0) {
      return false; // No changes to make
    }

    values.push(id); // Add the ID for the WHERE clause

    const sql = `UPDATE plans SET ${fields.join(", ")} WHERE id = ?`;
    const [result] = await db.query(sql, values);
    return result.affectedRows > 0;
  },

  /**
   * Soft-deletes a plan by setting its status to 'archived'.
   * @param {number} id
   * @returns {Promise<boolean>} True if the update was successful.
   */
  delete: async (id) => {
    // This is now a soft delete, using our flexible update method.
    return Plan.update(id, { status: "archived" });
  },
};

module.exports = Plan;
