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

export class GamificationRepository {
  // The repository only needs an object with a `query` method, matching the Pool/PoolClient interface.
  // Using `Pick` makes this dependency explicit and simplifies testing by reducing the mock surface.
  private db: Pick<Pool | PoolClient, 'query'>;

  constructor(db: Pick<Pool | PoolClient, 'query'> = 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) {
      handleDbError(
        error,
        logger,
        'Database error in getAllAchievements',
        {},
        {
          defaultMessage: '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,
          a.created_at
        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) {
      handleDbError(
        error,
        logger,
        'Database error in getUserAchievements',
        { userId },
        {
          defaultMessage: '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]);
    } catch (error) {
      handleDbError(
        error,
        logger,
        'Database error in awardAchievement',
        { userId, achievementName },
        {
          fkMessage: 'The specified user or achievement does not exist.',
          defaultMessage: '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) {
      handleDbError(
        error,
        logger,
        'Database error in getLeaderboard',
        { limit },
        {
          defaultMessage: 'Failed to retrieve leaderboard.',
        },
      );
    }
  }
}
