CentralBackend/repositories/referral/ReferralTokenRepository.js

const ReferralToken = require('../../models/ReferralToken');
const crypto = require('crypto');
const { logger } = require('../../middleware/logger');

class ReferralTokenRepository {
  constructor(unitOfWork) {
    this.unitOfWork = unitOfWork;
  }

  async createToken({ createdByUserId, expiresAt, maxUses }) {
    logger.info('ReferralTokenRepository.createToken:start', { createdByUserId, expiresAt, maxUses });
    try {
      const conn = this.unitOfWork.connection;
      const token = crypto.randomBytes(24).toString('hex');
      const unlimited = (maxUses === -1);
      const usesRemaining = unlimited ? -1 : maxUses;
      const query = `
        INSERT INTO referral_tokens
          (token, created_by_user_id, expires_at, max_uses, uses_remaining, status)
        VALUES (?, ?, ?, ?, ?, 'active')
      `;
      const [result] = await conn.query(query, [
        token,
        createdByUserId,
        expiresAt,
        maxUses,
        usesRemaining
      ]);
      logger.info('ReferralTokenRepository.createToken:success', {
        id: result.insertId, token, unlimited, usesRemaining
      });
      return new ReferralToken({
        id: result.insertId,
        token,
        createdByUserId,
        expiresAt,
        maxUses,
        usesRemaining,
        status: 'active'
      });
    } catch (error) {
      logger.error('ReferralTokenRepository.createToken:error', { createdByUserId, error: error.message });
      throw error;
    }
  }

  async findByToken(token) {
    logger.info('ReferralTokenRepository.findByToken:start', { token });
    try {
      const conn = this.unitOfWork.connection;
      const [rows] = await conn.query(
        `SELECT * FROM referral_tokens WHERE token = ? LIMIT 1`,
        [token]
      );
      logger.info('ReferralTokenRepository.findByToken:success', { token, found: !!rows.length });
      return rows.length ? new ReferralToken(rows[0]) : null;
    } catch (error) {
      logger.error('ReferralTokenRepository.findByToken:error', { token, error: error.message });
      throw error;
    }
  }

  async getTokensByUser(userId) {
    logger.info('ReferralTokenRepository.getTokensByUser:start', { userId });
    try {
      const conn = this.unitOfWork.connection;
      const sql = `
        SELECT
          rt.id,
          rt.token,
          rt.created_by_user_id,
          rt.expires_at,
          rt.max_uses AS max_uses,
          rt.uses_remaining AS uses_remaining,
          rt.status,
          rt.created_at,
          rt.updated_at,
          (SELECT COUNT(*) FROM referral_token_usage rtu WHERE rtu.referral_token_id = rt.id) AS usage_count,
          CASE
            WHEN rt.max_uses = -1 THEN 0
            WHEN rt.max_uses IS NULL OR rt.uses_remaining IS NULL THEN 0
            ELSE GREATEST(rt.max_uses - rt.uses_remaining, 0)
          END AS used_count
        FROM referral_tokens rt
        WHERE rt.created_by_user_id = ?
        ORDER BY rt.created_at DESC
      `;
      logger.debug('ReferralTokenRepository.getTokensByUser:sql', { sql, params: [userId] });
      const [rows] = await conn.query(sql, [userId]);
      rows.slice(0, 10).forEach(r => {
        logger.debug('ReferralTokenRepository.getTokensByUser:row', {
          id: r.id,
          max_uses: r.max_uses,
          uses_remaining: r.uses_remaining,
          used_count: r.used_count
        });
      });
      logger.info('ReferralTokenRepository.getTokensByUser:success', { userId, count: rows.length });
      return rows;
    } catch (error) {
      logger.error('ReferralTokenRepository.getTokensByUser:error', { userId, error: error.message });
      throw error;
    }
  }

  async getStatsByUser(userId) {
    logger.info('ReferralTokenRepository.getStatsByUser:start', { userId });
    try {
      const conn = this.unitOfWork.connection;
      // Total links generated
      const [[{ totalLinks }]] = await conn.query(
        `SELECT COUNT(*) AS totalLinks FROM referral_tokens WHERE created_by_user_id = ?`,
        [userId]
      );
      // Total active links
      const [[{ activeLinks }]] = await conn.query(
        `SELECT COUNT(*) AS activeLinks FROM referral_tokens WHERE created_by_user_id = ? AND status = 'active' AND expires_at > NOW()`,
        [userId]
      );
      // Total links used (sum of all usages)
      const [[{ linksUsed }]] = await conn.query(
        `SELECT COUNT(*) AS linksUsed FROM referral_token_usage rtu
         JOIN referral_tokens rt ON rtu.referral_token_id = rt.id
         WHERE rt.created_by_user_id = ?`,
        [userId]
      );
      // Count personal users referred (from users table)
      const [[{ personalUsersReferred }]] = await conn.query(
        `SELECT COUNT(*) AS personalUsersReferred
         FROM referral_token_usage rtu
         JOIN referral_tokens rt ON rtu.referral_token_id = rt.id
         JOIN users u ON rtu.used_by_user_id = u.id
         WHERE rt.created_by_user_id = ? AND u.user_type = 'personal'`,
        [userId]
      );
      // Count company users referred (from users table)
      const [[{ companyUsersReferred }]] = await conn.query(
        `SELECT COUNT(*) AS companyUsersReferred
         FROM referral_token_usage rtu
         JOIN referral_tokens rt ON rtu.referral_token_id = rt.id
         JOIN users u ON rtu.used_by_user_id = u.id
         WHERE rt.created_by_user_id = ? AND u.user_type = 'company'`,
        [userId]
      );

      logger.info('ReferralTokenRepository.getStatsByUser:success', { userId, totalLinks, activeLinks, linksUsed, personalUsersReferred, companyUsersReferred });
      return {
        totalLinks,
        activeLinks,
        linksUsed,
        personalUsersReferred,
        companyUsersReferred
      };
    } catch (error) {
      logger.error('ReferralTokenRepository.getStatsByUser:error', { userId, error: error.message });
      throw error;
    }
  }

  async deactivateToken(tokenId, userId) {
    logger.info('ReferralTokenRepository.deactivateToken:start', { tokenId, userId });
    try {
      const conn = this.unitOfWork.connection;
      // Only allow deactivation if the token belongs to the user
      const [result] = await conn.query(
        `UPDATE referral_tokens SET status = 'inactive', deactivation_reason = 'user_deactivated', updated_at = NOW()
         WHERE id = ? AND created_by_user_id = ? AND status = 'active'`,
        [tokenId, userId]
      );
      logger.info('ReferralTokenRepository.deactivateToken:success', { tokenId, userId, deactivated: result.affectedRows > 0 });
      return result.affectedRows > 0;
    } catch (error) {
      logger.error('ReferralTokenRepository.deactivateToken:error', { tokenId, userId, error: error.message });
      throw error;
    }
  }

  async getReferrerInfoByToken(token) {
    logger.info('ReferralTokenRepository.getReferrerInfoByToken:start', { token });
    try {
      const conn = this.unitOfWork.connection;
      const sql = `
        SELECT
          rt.id AS token_id,
          rt.token,
          rt.status,
          rt.expires_at,
          rt.max_uses AS max_uses,
          rt.uses_remaining AS uses_remaining,
          CASE
            WHEN rt.max_uses = -1 THEN 0
            WHEN rt.max_uses IS NULL OR rt.uses_remaining IS NULL THEN 0
            ELSE GREATEST(rt.max_uses - rt.uses_remaining, 0)
          END AS used_count,
          (SELECT COUNT(*) FROM referral_token_usage rtu WHERE rtu.referral_token_id = rt.id) AS usage_count,
          u.id AS referrer_id,
          u.email AS referrer_email,
          u.user_type AS referrer_user_type
        FROM referral_tokens rt
        JOIN users u ON rt.created_by_user_id = u.id
        WHERE rt.token = ?
        LIMIT 1
      `;
      logger.debug('ReferralTokenRepository.getReferrerInfoByToken:sql', { sql, params: [token] });
      const [rows] = await conn.query(sql, [token]);
      if (rows.length) {
        const r = rows[0];
        logger.debug('ReferralTokenRepository.getReferrerInfoByToken:row', {
          token: r.token,
          max_uses: r.max_uses,
          uses_remaining: r.uses_remaining,
          used_count: r.used_count
        });
        logger.info('ReferralTokenRepository.getReferrerInfoByToken:success', { token });
      } else {
        logger.warn('ReferralTokenRepository.getReferrerInfoByToken:not_found', { token });
      }
      return rows.length ? rows[0] : null;
    } catch (error) {
      logger.error('ReferralTokenRepository.getReferrerInfoByToken:error', { token, error: error.message });
      throw error;
    }
  }

  async markReferralTokenUsed(tokenId, usedByUserId, unitOfWork) {
    logger.info('ReferralTokenRepository.markReferralTokenUsed:start', { tokenId, usedByUserId });
    try {
      const conn = this.unitOfWork.connection;
      await conn.query(
        `INSERT INTO referral_token_usage (referral_token_id, used_by_user_id) VALUES (?, ?)`,
        [tokenId, usedByUserId]
      );
      // Decrement only for limited tokens
      await conn.query(
        `UPDATE referral_tokens 
           SET uses_remaining = uses_remaining - 1
         WHERE id = ?
           AND uses_remaining > 0
           AND max_uses <> -1
           AND uses_remaining <> -1`,
        [tokenId]
      );
      // Exhaust only when reaches 0 and not unlimited
      await conn.query(
        `UPDATE referral_tokens
           SET status = 'exhausted'
         WHERE id = ?
           AND uses_remaining = 0
           AND max_uses <> -1`,
        [tokenId]
      );
      logger.info('ReferralTokenRepository.markReferralTokenUsed:success', { tokenId, usedByUserId });
    } catch (error) {
      logger.error('ReferralTokenRepository.markReferralTokenUsed:error', { tokenId, usedByUserId, error: error.message });
      throw error;
    }
  }

  async countActiveTokensByUser(userId) {
    logger.info('ReferralTokenRepository.countActiveTokensByUser:start', { userId });
    try {
      const conn = this.unitOfWork.connection;
      const [[{ count }]] = await conn.query(
        `SELECT COUNT(*) AS count
         FROM referral_tokens
         WHERE created_by_user_id = ?
           AND status = 'active'
           AND expires_at > NOW()`,
        [userId]
      );
      logger.info('ReferralTokenRepository.countActiveTokensByUser:success', { userId, count });
      return count;
    } catch (error) {
      logger.error('ReferralTokenRepository.countActiveTokensByUser:error', { userId, error: error.message });
      throw error;
    }
  }

  async getReferredUsersByCreator(createdByUserId) {
    logger.info('ReferralTokenRepository.getReferredUsersByCreator:start', { createdByUserId });
    try {
      const conn = this.unitOfWork.connection;
      const sql = `
        SELECT
          u.id,
          u.email,
          u.user_type,
          u.created_at AS registered_at,
          us.status,
          COALESCE(
            NULLIF(TRIM(CONCAT(pp.first_name, ' ', pp.last_name)), ''),
            cp.company_name,
            cp.contact_person_name,
            u.email
          ) AS name
        FROM users u
        LEFT JOIN user_status us ON us.user_id = u.id
        LEFT JOIN personal_profiles pp ON pp.user_id = u.id
        LEFT JOIN company_profiles cp ON cp.user_id = u.id
        WHERE EXISTS (
          SELECT 1
          FROM referral_token_usage rtu
          JOIN referral_tokens rt ON rtu.referral_token_id = rt.id
          WHERE rt.created_by_user_id = ?
            AND rtu.used_by_user_id = u.id
        )
        ORDER BY u.created_at DESC
      `;
      logger.debug('ReferralTokenRepository.getReferredUsersByCreator:sql', { sql, params: [createdByUserId] });
      const [rows] = await conn.query(sql, [createdByUserId]);
      logger.info('ReferralTokenRepository.getReferredUsersByCreator:success', { createdByUserId, count: rows.length });
      return rows;
    } catch (error) {
      logger.error('ReferralTokenRepository.getReferredUsersByCreator:error', { createdByUserId, error: error.message });
      throw error;
    }
  }

  // ...add more methods as needed (e.g., update, usage)...
}

module.exports = ReferralTokenRepository;