// src/services/db/recipe.db.ts
import type { Pool, PoolClient } from 'pg';
import { getPool } from './connection.db';
import { ForeignKeyConstraintError, NotFoundError, UniqueConstraintError } from './errors.db';
import type { Logger } from 'pino';
import type { Recipe, FavoriteRecipe, RecipeComment } from '../../types';

export class RecipeRepository {
  private db: Pool | PoolClient;

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

  /**
   * Calls a database function to get recipes based on the percentage of their ingredients on sale.
   * @param minPercentage The minimum percentage of ingredients that must be on sale.
   * @returns A promise that resolves to an array of recipes.
   */
  async getRecipesBySalePercentage(minPercentage: number, logger: Logger): Promise<Recipe[]> {
    try {
      const res = await this.db.query<Recipe>('SELECT * FROM public.get_recipes_by_sale_percentage($1)', [minPercentage]);
      return res.rows;
    } catch (error) {
      logger.error({ err: error, minPercentage }, 'Database error in getRecipesBySalePercentage');
      throw new Error('Failed to get recipes by sale percentage.');
    }
  }

  /**
   * Calls a database function to get recipes by the minimum number of sale ingredients.
   * @param minIngredients The minimum number of ingredients that must be on sale.
   * @returns A promise that resolves to an array of recipes.
   */
  async getRecipesByMinSaleIngredients(minIngredients: number, logger: Logger): Promise<Recipe[]> {
    try {
      const res = await this.db.query<Recipe>('SELECT * FROM public.get_recipes_by_min_sale_ingredients($1)', [minIngredients]);
      return res.rows;
    } catch (error) {
      logger.error({ err: error, minIngredients }, 'Database error in getRecipesByMinSaleIngredients');
      throw new Error('Failed to get recipes by minimum sale ingredients.');
    }
  }

  /**
   * Calls a database function to find recipes by a specific ingredient and tag.
   * @param ingredient The name of the ingredient to search for.
   * @param tag The name of the tag to search for.
   * @returns A promise that resolves to an array of matching recipes.
   */
  async findRecipesByIngredientAndTag(ingredient: string, tag: string, logger: Logger): Promise<Recipe[]> {
    try {
      const res = await this.db.query<Recipe>('SELECT * FROM public.find_recipes_by_ingredient_and_tag($1, $2)', [ingredient, tag]);
      return res.rows;
    } catch (error) {
      logger.error({ err: error, ingredient, tag }, 'Database error in findRecipesByIngredientAndTag');
      throw new Error('Failed to find recipes by ingredient and tag.');
    }
  }

  /**
   * Calls a database function to get a user's favorite recipes.
   * @param userId The ID of the user.
   * @returns A promise that resolves to an array of the user's favorite recipes.
   */
  async getUserFavoriteRecipes(userId: string, logger: Logger): Promise<Recipe[]> {
    try {
      const res = await this.db.query<Recipe>('SELECT * FROM public.get_user_favorite_recipes($1)', [userId]);
      return res.rows;
    } catch (error) {
      logger.error({ err: error, userId }, 'Database error in getUserFavoriteRecipes');
      throw new Error('Failed to get favorite recipes.');
    }
  }

  /**
   * Adds a recipe to a user's favorites.
   * @param userId The ID of the user.
   * @param recipeId The ID of the recipe to favorite.
   * @returns A promise that resolves to the created favorite record.
   */
  async addFavoriteRecipe(userId: string, recipeId: number, logger: Logger): Promise<FavoriteRecipe> {
    // The ON CONFLICT DO NOTHING clause prevents duplicates but also causes the query to return
    // zero rows if the favorite already exists. We need to handle this case.
    try {
      const res = await this.db.query<FavoriteRecipe>(
        'INSERT INTO public.favorite_recipes (user_id, recipe_id) VALUES ($1, $2) ON CONFLICT (user_id, recipe_id) DO NOTHING RETURNING *',
        [userId, recipeId]
      );
      if (res.rowCount === 0) {
        throw new UniqueConstraintError('This recipe is already in the user\'s favorites.');
      }
      return res.rows[0];
    } catch (error) {
      if (error instanceof UniqueConstraintError) {
        throw error;
      }
      logger.error({ err: error, userId, recipeId }, 'Database error in addFavoriteRecipe');
      if (error instanceof Error && 'code' in error && error.code === '23503') {
        throw new ForeignKeyConstraintError('The specified user or recipe does not exist.');
      }
      throw new Error('Failed to add favorite recipe.');
    }
  }

  /**
   * Removes a recipe from a user's favorites.
   * @param userId The ID of the user.
   * @param recipeId The ID of the recipe to unfavorite.
   */
  async removeFavoriteRecipe(userId: string, recipeId: number, logger: Logger): Promise<void> {
    try {
      const res = await this.db.query('DELETE FROM public.favorite_recipes WHERE user_id = $1 AND recipe_id = $2', [userId, recipeId]);
      if (res.rowCount === 0) {
        throw new NotFoundError('Favorite recipe not found for this user.');
      }
    } catch (error) {
      if (error instanceof NotFoundError) {
        throw error;
      }
      logger.error({ err: error, userId, recipeId }, 'Database error in removeFavoriteRecipe');
      throw new Error('Failed to remove favorite recipe.');
    }
  }

  /**
   * Deletes a recipe, ensuring ownership.
   * @param recipeId The ID of the recipe to delete.
   * @param userId The ID of the user attempting to delete the recipe.
   * @param isAdmin A boolean indicating if the user is an administrator.
   */
  async deleteRecipe(recipeId: number, userId: string, isAdmin: boolean, logger: Logger): Promise<void> {
    try {
      let query = 'DELETE FROM public.recipes WHERE recipe_id = $1 AND user_id = $2';
      const params = [recipeId, userId];

      if (isAdmin) {
        query = 'DELETE FROM public.recipes WHERE recipe_id = $1';
        params.pop(); // Remove userId from params if admin
      }

      const res = await this.db.query(query, params);
      if (res.rowCount === 0) {
        throw new NotFoundError('Recipe not found or user does not have permission to delete.');
      }
    } catch (error) {
      if (error instanceof NotFoundError) throw error;
      logger.error({ err: error, recipeId, userId, isAdmin }, 'Database error in deleteRecipe');
      throw new Error('Failed to delete recipe.');
    }
  }

  /**
   * Updates a recipe, ensuring ownership.
   * @param recipeId The ID of the recipe to update.
   * @param userId The ID of the user attempting the update.
   * @param updates An object containing the fields to update.
   * @returns A promise that resolves to the updated Recipe object.
   */
  async updateRecipe(recipeId: number, userId: string, updates: Partial<Pick<Recipe, 'name' | 'description' | 'instructions' | 'prep_time_minutes' | 'cook_time_minutes' | 'servings' | 'photo_url'>>, logger: Logger): Promise<Recipe> {
    try {
      const setClauses = [];
      const values = [];
      let paramIndex = 1;

      // Dynamically build the SET clause based on provided updates
      for (const [key, value] of Object.entries(updates)) {
        if (value !== undefined) {
          setClauses.push(`${key} = $${paramIndex++}`);
          values.push(value);
        }
      }

      if (setClauses.length === 0) {
        throw new Error('No fields provided to update.');
      }

      values.push(recipeId, userId);
      const query = `
        UPDATE public.recipes
        SET ${setClauses.join(', ')}
        WHERE recipe_id = $${paramIndex++} AND user_id = $${paramIndex++}
        RETURNING *;
      `;

      const res = await this.db.query<Recipe>(query, values);
      if (res.rowCount === 0) {
        throw new NotFoundError('Recipe not found or user does not have permission to update.');
      }
      return res.rows[0];
    } catch (error) {
      // Re-throw specific, known errors to allow for more precise error handling in the calling code.
      if (error instanceof NotFoundError || (error instanceof Error && error.message.includes('No fields provided'))) {
        throw error;
      }
      logger.error({ err: error, recipeId, userId, updates }, 'Database error in updateRecipe');
      throw new Error('Failed to update recipe.');
    }
  }

  /**
   * Retrieves a single recipe by its ID, including its ingredients and tags.
   * @param recipeId The ID of the recipe to retrieve.
   * @returns A promise that resolves to the Recipe object or undefined if not found.
   */
  async getRecipeById(recipeId: number, logger: Logger): Promise<Recipe> {
    try {
      const query = `
        SELECT
          r.*,
          COALESCE(json_agg(DISTINCT jsonb_build_object('recipe_ingredient_id', ri.recipe_ingredient_id, 'master_item_name', mgi.name, 'quantity', ri.quantity, 'unit', ri.unit)) FILTER (WHERE ri.recipe_ingredient_id IS NOT NULL), '[]') AS ingredients,
          COALESCE(json_agg(DISTINCT jsonb_build_object('tag_id', t.tag_id, 'name', t.name)) FILTER (WHERE t.tag_id IS NOT NULL), '[]') AS tags
        FROM public.recipes r
        LEFT JOIN public.recipe_ingredients ri ON r.recipe_id = ri.recipe_id
        LEFT JOIN public.master_grocery_items mgi ON ri.master_item_id = mgi.master_grocery_item_id
        LEFT JOIN public.recipe_tags rt ON r.recipe_id = rt.recipe_id
        LEFT JOIN public.tags t ON rt.tag_id = t.tag_id
        WHERE r.recipe_id = $1
        GROUP BY r.recipe_id;
      `;
      const res = await this.db.query<Recipe>(query, [recipeId]);
      if (res.rowCount === 0) {
        throw new NotFoundError(`Recipe with ID ${recipeId} not found`);
      }
      return res.rows[0];
    } catch (error) {
      if (error instanceof NotFoundError) {
        throw error;
      }
      logger.error({ err: error, recipeId }, 'Database error in getRecipeById');
      throw new Error('Failed to retrieve recipe.');
    }
  }

  /**
   * Retrieves all comments for a specific recipe.
   * @param recipeId The ID of the recipe.
   * @returns A promise that resolves to an array of RecipeComment objects.
   */
  async getRecipeComments(recipeId: number, logger: Logger): Promise<RecipeComment[]> {
    try {
      const query = `
        SELECT
          rc.*,
          p.full_name as user_full_name,
          p.avatar_url as user_avatar_url
        FROM public.recipe_comments rc
        LEFT JOIN public.profiles p ON rc.user_id = p.user_id
        WHERE rc.recipe_id = $1
        ORDER BY rc.created_at ASC;
      `;
      const res = await this.db.query<RecipeComment>(query, [recipeId]);
      return res.rows;
    } catch (error) {
      logger.error({ err: error, recipeId }, 'Database error in getRecipeComments');
      throw new Error('Failed to get recipe comments.');
    }
  }

  /**
   * Adds a new comment to a recipe.
   * @param recipeId The ID of the recipe to comment on.
   * @param userId The ID of the user posting the comment.
   * @param content The text content of the comment.
   * @param parentCommentId Optional ID of the parent comment for threaded replies.
   * @returns A promise that resolves to the newly created RecipeComment object.
   */
  async addRecipeComment(recipeId: number, userId: string, content: string, logger: Logger, parentCommentId?: number): Promise<RecipeComment> {
    try {
      const res = await this.db.query<RecipeComment>(
        'INSERT INTO public.recipe_comments (recipe_id, user_id, content, parent_comment_id) VALUES ($1, $2, $3, $4) RETURNING *',
        [recipeId, userId, content, parentCommentId]
      );
      return res.rows[0];
    } catch (error) {
      logger.error({ err: error, recipeId, userId, parentCommentId }, 'Database error in addRecipeComment');
      // Check for specific PostgreSQL error codes
      if (error instanceof Error && 'code' in error && error.code === '23503') { // foreign_key_violation
        throw new ForeignKeyConstraintError('The specified recipe, user, or parent comment does not exist.');
      }
      throw new Error('Failed to add recipe comment.');
    }
  }

  /**
   * Creates a personal, editable copy (a "fork") of a public recipe for a user.
   * @param userId The ID of the user forking the recipe.
   * @param originalRecipeId The ID of the recipe to fork.
   * @returns A promise that resolves to the newly created forked Recipe object.
   */
  async forkRecipe(userId: string, originalRecipeId: number, logger: Logger): Promise<Recipe> {
      try {
          const res = await this.db.query<Recipe>('SELECT * FROM public.fork_recipe($1, $2)', [userId, originalRecipeId]);
          return res.rows[0];
      } catch (error) {
          logger.error({ err: error, userId, originalRecipeId }, 'Database error in forkRecipe');
          // The fork_recipe function could fail if the original recipe doesn't exist or isn't public.
          if (error instanceof Error && 'code' in error && error.code === 'P0001') { // raise_exception
            throw new Error(error.message); // Re-throw the user-friendly message from the DB function.
          }
          throw new Error('Failed to fork recipe.');
      }
  }
}