// src/services/db/gamification.db.ts
import type { Pool, PoolClient } from 'pg';
import { getPool } from './connection.db';
import { ForeignKeyConstraintError } from './errors.db';
import type { Logger } from 'pino';
import { Achievement, UserAchievement, LeaderboardUser } from '../../types';

export class GamificationRepository {
  private db: Pool | PoolClient;

  constructor(db: Pool | PoolClient = getPool()) {
    this.db = db;
  }

  /**
   * Retrieves the master list of all available achievements.
   * @returns A promise that resolves to an array of Achievement objects.
   */
  async getAllAchievements(logger: Logger): Promise<Achievement[]> {
    try {
      const res = await this.db.query<Achievement>('SELECT * FROM public.achievements ORDER BY points_value ASC, name ASC');
      return res.rows;
    } catch (error) {
      logger.error({ err: error }, 'Database error in getAllAchievements');
      throw new Error('Failed to retrieve achievements.');
    }
  }

  /**
   * Retrieves all achievements earned by a specific user.
   * Joins with the achievements table to include details like name and description.
   * @param userId The UUID of the user.
   * @returns A promise that resolves to an array of Achievement objects earned by the user.
   */
  async getUserAchievements(userId: string, logger: Logger): Promise<(UserAchievement & Achievement)[]> {
    try {
      const query = `
        SELECT
          ua.user_id,
          ua.achievement_id,
          ua.achieved_at,
          a.name,
          a.description,
          a.icon,
          a.points_value
        FROM public.user_achievements ua
        JOIN public.achievements a ON ua.achievement_id = a.achievement_id
        WHERE ua.user_id = $1
        ORDER BY ua.achieved_at DESC;
      `;
      const res = await this.db.query<(UserAchievement & Achievement)>(query, [userId]);
      return res.rows;
    } catch (error) {
      logger.error({ err: error, userId }, 'Database error in getUserAchievements');
      throw new Error('Failed to retrieve user achievements.');
    }
  }

  /**
   * Manually awards a specific achievement to a user.
   * This calls a database function that handles the logic of checking if the user
   * already has the achievement and awarding points if they don't.
   * @param userId The UUID of the user to award the achievement to.
   * @param achievementName The name of the achievement to award.
   * @returns A promise that resolves when the operation is complete.
   */
  async awardAchievement(userId: string, achievementName: string, logger: Logger): Promise<void> {
    try {
      await this.db.query("SELECT public.award_achievement($1, $2)", [userId, achievementName]); // This was a duplicate, fixed.
    } catch (error) {
      logger.error({ err: error, userId, achievementName }, 'Database error in awardAchievement');
      // Check for a foreign key violation, which would mean the user or achievement name is invalid.
      if (error instanceof Error && 'code' in error && error.code === '23503') {
        throw new ForeignKeyConstraintError('The specified user or achievement does not exist.');
      }
      throw new Error('Failed to award achievement.');
    }
  }

  /**
   * Retrieves a leaderboard of users with the most points.
   * @param limit The number of users to return.
   * @returns A promise that resolves to an array of leaderboard user objects.
   */
  async getLeaderboard(limit: number, logger: Logger): Promise<LeaderboardUser[]> {
    try {
      const query = `
      SELECT
        user_id,
        full_name,
        avatar_url,
        points,
        RANK() OVER (ORDER BY points DESC) as rank
      FROM public.profiles
      ORDER BY points DESC, full_name ASC
      LIMIT $1;
    `;
      const res = await this.db.query<LeaderboardUser>(query, [limit]);
      return res.rows;
    } catch (error) {
      logger.error({ err: error, limit }, 'Database error in getLeaderboard');
      throw new Error('Failed to retrieve leaderboard.');
    }
  }
}