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

const Subscription = {
  /**
   * Creates a new subscription for an institution.
   * @param {object} subData
   * @param {number} subData.institution_id
   * @param {number} subData.plan_id
   * @param {string} subData.start_date - 'YYYY-MM-DD'
   * @param {string} subData.end_date - 'YYYY-MM-DD'
   * @returns {Promise<object>} The new subscription object.
   */
  create: async ({ institution_id, plan_id, start_date, end_date }) => {
    const sql =
      'INSERT INTO subscriptions (institution_id, plan_id, start_date, end_date, status) VALUES (?, ?, ?, ?, "active")';
    const [result] = await db.query(sql, [
      institution_id,
      plan_id,
      start_date,
      end_date,
    ]);
    return { id: result.insertId, institution_id, plan_id };
  },

  findById: async (id) => {
    const [rows] = await db.query("SELECT * FROM subscriptions WHERE id = ?", [
      id,
    ]);
    return rows[0];
  },

  /**
   * Finds the current, active subscription for an institution.
   * This is crucial for the authentication middleware check.
   * @param {number} institution_id
   * @returns {Promise<object|undefined>} The active subscription object.
   */
  findActiveByInstitutionId: async (institution_id) => {
    const sql = `
      SELECT * FROM subscriptions 
      WHERE institution_id = ? 
      AND status = 'active' 
      AND end_date >= CURDATE()
      ORDER BY end_date DESC 
      LIMIT 1
    `;
    const [rows] = await db.query(sql, [institution_id]);
    return rows[0];
  },

  /**
   * Finds all subscriptions (past and present) for an institution.
   * Useful for billing history.
   * @param {number} institution_id
   * @returns {Promise<Array<object>>}
   */
  findAllByInstitutionId: async (institution_id) => {
    const sql = `
      SELECT s.*, p.name as plan_name 
      FROM subscriptions s
      JOIN plans p ON s.plan_id = p.id
      WHERE s.institution_id = ?
      ORDER BY s.start_date DESC
    `;
    const [rows] = await db.query(sql, [institution_id]);
    return rows;
  },

  /**
   * Updates a subscription's status or dates.
   * @param {number} id The ID of the subscription.
   * @param {object} subData The new data.
   * @returns {Promise<boolean>}
   */
  update: async (id, { end_date, status }) => {
    const sql =
      "UPDATE subscriptions SET end_date = ?, status = ? WHERE id = ?";
    const [result] = await db.query(sql, [end_date, status, id]);
    return result.affectedRows > 0;
  },
  countActive: async () => {
    const [rows] = await db.query(
      "SELECT COUNT(*) as count FROM subscriptions WHERE status = 'active' AND end_date >= CURDATE()"
    );
    return rows[0].count;
  },
  findPendingByInstitutionId: async (institution_id) => {
    const sql = `
      SELECT * FROM subscriptions 
      WHERE institution_id = ? 
      AND status = 'pending'
      ORDER BY start_date ASC 
      LIMIT 1
    `;
    const [rows] = await db.query(sql, [institution_id]);
    return rows[0];
  },
};

module.exports = Subscription;
