// src/services/db/admin.db.ts
import type { Pool, PoolClient } from 'pg';
import { getPool, withTransaction } from './connection.db';
import { ForeignKeyConstraintError, NotFoundError } from './errors.db';
import type { Logger } from 'pino';
import { SuggestedCorrection, MostFrequentSaleItem, Recipe, RecipeComment, UnmatchedFlyerItem, ActivityLogItem, Receipt, AdminUserView, Profile } from '../../types';

export class AdminRepository {
  private db: Pool | PoolClient;

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

  /**
   * Retrieves all pending suggested corrections from the database.
   * Joins with users and flyer_items to provide context for the admin.
   * @returns A promise that resolves to an array of SuggestedCorrection objects.
   */
  // prettier-ignore
  async getSuggestedCorrections(logger: Logger): Promise<SuggestedCorrection[]> {
    try {
      const query = `
      SELECT
        sc.suggested_correction_id,
        sc.flyer_item_id,
        sc.user_id,
        sc.correction_type,
        sc.suggested_value,
        sc.status,
        sc.created_at,
        fi.item as flyer_item_name,
        fi.price_display as flyer_item_price_display,
        u.email as user_email
      FROM public.suggested_corrections sc
      JOIN public.flyer_items fi ON sc.flyer_item_id = fi.flyer_item_id
      LEFT JOIN public.users u ON sc.user_id = u.user_id
      WHERE sc.status = 'pending'
      ORDER BY sc.created_at ASC;
    `;
      const res = await this.db.query<SuggestedCorrection>(query);
      return res.rows;
    } catch (error) {
      logger.error({ err: error }, 'Database error in getSuggestedCorrections');
      throw new Error('Failed to retrieve suggested corrections.');
    }
  }

  /**
   * Approves a correction and applies the change to the corresponding flyer item.
   * This function runs as a transaction to ensure data integrity.
   * @param correctionId The ID of the correction to approve.
   */
  // prettier-ignore
  async approveCorrection(correctionId: number, logger: Logger): Promise<void> {
      try {
          // The database function `approve_correction` now contains all the logic.
          // It finds the correction, applies the change, and updates the status in a single transaction.
          // This simplifies the application code and keeps the business logic in the database.
          await this.db.query('SELECT public.approve_correction($1)', [correctionId]);
          logger.info(`Successfully approved and applied correction ID: ${correctionId}`);
      } catch (error) {
          logger.error({ err: error, correctionId }, 'Database transaction error in approveCorrection');
          throw new Error('Failed to approve correction.');
      }
  }

  /**
   * Rejects a correction by updating its status.
   * @param correctionId The ID of the correction to reject.
   */
  // prettier-ignore
  async rejectCorrection(correctionId: number, logger: Logger): Promise<void> {
    try {
      const res = await this.db.query(
        "UPDATE public.suggested_corrections SET status = 'rejected' WHERE suggested_correction_id = $1 AND status = 'pending' RETURNING suggested_correction_id",
        [correctionId]
      );
      if (res.rowCount === 0) {
        throw new NotFoundError(`Correction with ID ${correctionId} not found or not in 'pending' state.`);
      }
      logger.info(`Successfully rejected correction ID: ${correctionId}`);
    } catch (error) {
      if (error instanceof NotFoundError) throw error;
      logger.error({ err: error, correctionId }, 'Database error in rejectCorrection');
      throw new Error('Failed to reject correction.');
    }
  }

  /**
   * Updates the suggested value of a pending correction.
   * @param correctionId The ID of the correction to update.
   * @param newSuggestedValue The new value to set for the suggestion.
   * @returns A promise that resolves to the updated SuggestedCorrection object.
   */
  // prettier-ignore
  async updateSuggestedCorrection(correctionId: number, newSuggestedValue: string, logger: Logger): Promise<SuggestedCorrection> {
    try {
      const res = await this.db.query<SuggestedCorrection>(
      "UPDATE public.suggested_corrections SET suggested_value = $1 WHERE suggested_correction_id = $2 AND status = 'pending' RETURNING *",
      [newSuggestedValue, correctionId]
    );
    if (res.rowCount === 0) {
      throw new NotFoundError(`Correction with ID ${correctionId} not found or is not in 'pending' state.`);
    }
    return res.rows[0];
    } catch (error) {
      if (error instanceof NotFoundError) {
        throw error;
      }
      logger.error({ err: error, correctionId }, 'Database error in updateSuggestedCorrection');
      throw new Error('Failed to update suggested correction.');
    }
  }

  /**
   * Retrieves application-wide statistics for the admin dashboard.
   * @returns A promise that resolves to an object containing various application stats.
   */
  // prettier-ignore
  async getApplicationStats(logger: Logger): Promise<{
    flyerCount: number;
    userCount: number;
    flyerItemCount: number;
    storeCount: number;
    pendingCorrectionCount: number;
  }> {
    try {
      // Run count queries in parallel for better performance
      const flyerCountQuery = this.db.query<{ count: string }>('SELECT COUNT(*) FROM public.flyers');
      const userCountQuery = this.db.query<{ count: string }>('SELECT COUNT(*) FROM public.users');
      const flyerItemCountQuery = this.db.query<{ count: string }>('SELECT COUNT(*) FROM public.flyer_items');
      const storeCountQuery = this.db.query<{ count: string }>('SELECT COUNT(*) FROM public.stores');
      const pendingCorrectionCountQuery = this.db.query<{ count: string }>("SELECT COUNT(*) FROM public.suggested_corrections WHERE status = 'pending'");

    const [
      flyerCountRes,
      userCountRes,
      flyerItemCountRes,
      storeCountRes,
      pendingCorrectionCountRes
    ] = await Promise.all([
      flyerCountQuery, userCountQuery, flyerItemCountQuery, storeCountQuery, pendingCorrectionCountQuery
    ]);

    return {
      flyerCount: parseInt(flyerCountRes.rows[0].count, 10),
      userCount: parseInt(userCountRes.rows[0].count, 10),
      flyerItemCount: parseInt(flyerItemCountRes.rows[0].count, 10),
      storeCount: parseInt(storeCountRes.rows[0].count, 10),
      pendingCorrectionCount: parseInt(pendingCorrectionCountRes.rows[0].count, 10),
    };
    } catch (error) {
      logger.error({ err: error }, 'Database error in getApplicationStats');
      throw error; // Re-throw the original error to be handled by the caller
    }
  }

  /**
   * Retrieves daily statistics for user registrations and flyer uploads for the last 30 days.
   * @returns A promise that resolves to an array of daily stats.
   */
  // prettier-ignore
  async getDailyStatsForLast30Days(logger: Logger): Promise<{ date: string; new_users: number; new_flyers: number; }[]> {
    try {
      const query = `
      WITH date_series AS (
        SELECT generate_series(
            (CURRENT_DATE - interval '29 days'),
            CURRENT_DATE,
            '1 day'::interval
        )::date AS day
      ),
      daily_users AS (
        SELECT created_at::date AS day, COUNT(*) AS user_count
        FROM public.users
        WHERE created_at >= (CURRENT_DATE - interval '29 days')
        GROUP BY 1
      ),
      daily_flyers AS (
        SELECT created_at::date AS day, COUNT(*) AS flyer_count
        FROM public.flyers
        WHERE created_at >= (CURRENT_DATE - interval '29 days')
        GROUP BY 1
      )
      SELECT
        to_char(ds.day, 'YYYY-MM-DD') as date,
        COALESCE(du.user_count, 0)::int AS new_users,
        COALESCE(df.flyer_count, 0)::int AS new_flyers
      FROM date_series ds
      LEFT JOIN daily_users du ON ds.day = du.day
      LEFT JOIN daily_flyers df ON ds.day = df.day
      ORDER BY ds.day ASC;
    `;
      const res = await this.db.query(query);
      return res.rows;
    } catch (error) {
      logger.error({ err: error }, 'Database error in getDailyStatsForLast30Days');
      throw new Error('Failed to retrieve daily statistics.');
    }
  }

  /**
   * Calls a database function to get the most frequently advertised items.
   * @param days The number of past days to look back.
   * @param limit The maximum number of items to return.
   * @returns A promise that resolves to an array of the most frequent sale items.
   */
  async getMostFrequentSaleItems(days: number, limit: number, logger: Logger): Promise<MostFrequentSaleItem[]> {
    // This is a secure parameterized query. The values for `days` and `limit` are passed
    // separately from the query string. The database driver safely substitutes the `$1` and `$2`
    // placeholders, preventing SQL injection attacks.
    // Never use template literals like `WHERE f.valid_from >= NOW() - '${days} days'`
    // as that would be a major security vulnerability.
    try {
      const query = `
      SELECT
        fi.master_item_id,
        mi.name as item_name,
        COUNT(fi.flyer_item_id)::int as sale_count
      FROM public.flyer_items fi
      JOIN public.flyers f ON fi.flyer_id = f.flyer_id
      JOIN public.master_grocery_items mi ON fi.master_item_id = mi.master_grocery_item_id
      WHERE
        f.valid_from >= NOW() - ($1 || ' days')::interval
        AND fi.master_item_id IS NOT NULL
      GROUP BY
        fi.master_item_id, mi.name
      ORDER BY
        sale_count DESC, mi.name ASC
      LIMIT $2;
    `;
      const res = await this.db.query<MostFrequentSaleItem>(query, [days, limit]);
      return res.rows;
    } catch (error) {
      logger.error({ err: error }, 'Database error in getMostFrequentSaleItems');
      throw new Error('Failed to get most frequent sale items.');
    }
  }

  /**
   * Updates the status of a recipe comment (e.g., for moderation).
   * @param commentId The ID of the comment to update.
   * @param status The new status ('visible', 'hidden', 'reported').
   * @returns A promise that resolves to the updated RecipeComment object.
   */
  async updateRecipeCommentStatus(commentId: number, status: 'visible' | 'hidden' | 'reported', logger: Logger): Promise<RecipeComment> {
      try {
          const res = await this.db.query<RecipeComment>(
            'UPDATE public.recipe_comments SET status = $1 WHERE recipe_comment_id = $2 RETURNING *',
            [status, commentId]
          );
          if (res.rowCount === 0) {
              throw new NotFoundError(`Recipe comment with ID ${commentId} not found.`);
          }
          return res.rows[0];
      } catch (error) {
          if (error instanceof NotFoundError) {
              throw error;
          }
          logger.error({ err: error, commentId, status }, 'Database error in updateRecipeCommentStatus');
          throw new Error('Failed to update recipe comment status.');
      }
  }

  /**
   * Retrieves all flyer items that could not be automatically matched to a master item.
   * @returns A promise that resolves to an array of unmatched flyer items with context.
   */
  async getUnmatchedFlyerItems(logger: Logger): Promise<UnmatchedFlyerItem[]> {
    try {
      const query = `
      SELECT
        ufi.unmatched_flyer_item_id,
        ufi.status,
        ufi.created_at,
        fi.flyer_item_id as flyer_item_id,
        fi.item as flyer_item_name,
        fi.price_display,
        f.flyer_id as flyer_id,
        s.name as store_name
      FROM public.unmatched_flyer_items ufi
      JOIN public.flyer_items fi ON ufi.flyer_item_id = fi.flyer_item_id
      JOIN public.flyers f ON fi.flyer_id = f.flyer_id
      JOIN public.stores s ON f.store_id = s.store_id
      WHERE ufi.status = 'pending'
      ORDER BY ufi.created_at ASC;
    `;
      const res = await this.db.query<UnmatchedFlyerItem>(query);
      return res.rows;
    } catch (error) {
      logger.error({ err: error }, 'Database error in getUnmatchedFlyerItems');
      throw new Error('Failed to retrieve unmatched flyer items.');
    }
  }

  /**
   * Updates the status of a recipe (e.g., for moderation).
   * @param recipeId The ID of the recipe to update.
   * @param status The new status ('private', 'pending_review', 'public', 'rejected').
   * @returns A promise that resolves to the updated Recipe object.
   */
  async updateRecipeStatus(recipeId: number, status: 'private' | 'pending_review' | 'public' | 'rejected', logger: Logger): Promise<Recipe> {
      try {
          const res = await this.db.query<Recipe>(
            'UPDATE public.recipes SET status = $1 WHERE recipe_id = $2 RETURNING *',
            [status, 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, status }, 'Database error in updateRecipeStatus');
          throw new Error('Failed to update recipe status.'); // Keep generic for other DB errors
      }
  }

  /**
   * Resolves an unmatched flyer item by linking it to a master grocery item.
   * This is a transactional operation.
   * @param unmatchedFlyerItemId The ID from the `unmatched_flyer_items` table.
   * @param masterItemId The ID of the `master_grocery_items` to link to.
   */
  async resolveUnmatchedFlyerItem(unmatchedFlyerItemId: number, masterItemId: number, logger: Logger): Promise<void> {
    try {
      await withTransaction(async (client) => {
        // First, get the flyer_item_id from the unmatched record
        const unmatchedRes = await client.query<{
          flyer_item_id: number;
        }>('SELECT flyer_item_id FROM public.unmatched_flyer_items WHERE unmatched_flyer_item_id = $1 FOR UPDATE', [
          unmatchedFlyerItemId,
        ]);

        if (unmatchedRes.rowCount === 0) {
          throw new NotFoundError(`Unmatched flyer item with ID ${unmatchedFlyerItemId} not found.`);
        }
        const { flyer_item_id } = unmatchedRes.rows[0];

        // Next, update the original flyer_items table with the correct master_item_id
        await client.query('UPDATE public.flyer_items SET master_item_id = $1 WHERE flyer_item_id = $2', [masterItemId, flyer_item_id]);

        // Finally, update the status of the unmatched record to 'resolved'
        await client.query("UPDATE public.unmatched_flyer_items SET status = 'resolved' WHERE unmatched_flyer_item_id = $1", [unmatchedFlyerItemId]);

        logger.info(`Successfully resolved unmatched item ${unmatchedFlyerItemId} to master item ${masterItemId}.`);
      });
    } catch (error) {
      if (error instanceof NotFoundError) {
        throw error;
      }
      logger.error({ err: error, unmatchedFlyerItemId, masterItemId }, 'Database transaction error in resolveUnmatchedFlyerItem');
      throw new Error('Failed to resolve unmatched flyer item.');
    }
  }

  /**
   * Ignores an unmatched flyer item by updating its status.
   * @param unmatchedFlyerItemId The ID from the `unmatched_flyer_items` table.
   */
  async ignoreUnmatchedFlyerItem(unmatchedFlyerItemId: number, logger: Logger): Promise<void> {
    try {
      const res = await this.db.query("UPDATE public.unmatched_flyer_items SET status = 'ignored' WHERE unmatched_flyer_item_id = $1 AND status = 'pending'", [unmatchedFlyerItemId]);
      if (res.rowCount === 0) {
        throw new NotFoundError(`Unmatched flyer item with ID ${unmatchedFlyerItemId} not found or not in 'pending' state.`);
      }
    } catch (error) {
      if (error instanceof NotFoundError) throw error;
      logger.error({ err: error, unmatchedFlyerItemId }, 'Database error in ignoreUnmatchedFlyerItem');
      throw new Error('Failed to ignore unmatched flyer item.');
    }
  }

  /**
   * Retrieves a paginated list of recent activities from the activity log.
   * @param limit The number of log entries to retrieve.
   * @param offset The number of log entries to skip (for pagination).
   * @returns A promise that resolves to an array of ActivityLogItem objects.
   */
  // prettier-ignore
  async getActivityLog(limit: number, offset: number, logger: Logger): Promise<ActivityLogItem[]> {
      try {
          const res = await this.db.query<ActivityLogItem>('SELECT * FROM public.get_activity_log($1, $2)', [limit, offset]);
          return res.rows;
      } catch (error) {
          logger.error({ err: error, limit, offset }, 'Database error in getActivityLog');
          throw new Error('Failed to retrieve activity log.');
      }
  }

  /**
   * Defines a type for JSON-compatible data structures, allowing for nested objects and arrays.
   * This provides a safer alternative to `any` for objects intended for JSON serialization.
   */
  async logActivity(logData: {
    userId?: string | null;
    action: string;
    displayText: string;
    icon?: string | null;
    details?: Record<string, unknown> | null; 
  }, logger: Logger): Promise<void> {
    const { userId, action, displayText, icon, details } = logData;
    try {
      await this.db.query(
        `INSERT INTO public.activity_log (user_id, action, display_text, icon, details)
             VALUES ($1, $2, $3, $4, $5)`,
        [
          userId || null,
          action,
          displayText,
          icon || null,
          details ? JSON.stringify(details) : null,
        ]
      );
    } catch (error) {
      logger.error({ err: error, logData }, 'Database error in logActivity');
      // We don't re-throw here to prevent logging failures from crashing critical paths.
    }
  }

  /**
   * Increments the failed login attempt counter for a user.
   * @param userId The ID of the user.
   */
  async incrementFailedLoginAttempts(userId: string, logger: Logger): Promise<void> {
    try {
      await this.db.query(
        `UPDATE public.users 
             SET failed_login_attempts = failed_login_attempts + 1, last_failed_login = NOW()
             WHERE user_id = $1`,
        [userId]
      );
    } catch (error) {
      logger.error({ err: error, userId }, 'Database error in incrementFailedLoginAttempts');
    }
  }

  /**
   * Resets the failed login attempt counter for a user upon successful login.
   * @param userId The ID of the user.
   * @param ipAddress The IP address from which the successful login occurred.
   */
  async resetFailedLoginAttempts(userId: string, ipAddress: string, logger: Logger): Promise<void> {
    try {
      await this.db.query(
        `UPDATE public.users 
             SET failed_login_attempts = 0, last_failed_login = NULL, last_login_ip = $2, last_login_at = NOW()
             WHERE user_id = $1 AND failed_login_attempts > 0`,
        [userId, ipAddress]
      );
    } catch (error) {
      // This is a non-critical operation, so we just log the error and continue.
      logger.error({ err: error, userId }, 'Database error in resetFailedLoginAttempts');
    }
  }

  /**
   * Updates the logo URL for a specific brand.
   * @param brandId The ID of the brand to update.
   * @param logoUrl The new URL for the brand's logo.
   */
  // prettier-ignore
  async updateBrandLogo(brandId: number, logoUrl: string, logger: Logger): Promise<void> {
    try {
      const res = await this.db.query(
        'UPDATE public.brands SET logo_url = $1 WHERE brand_id = $2',
        [logoUrl, brandId]
      );
      if (res.rowCount === 0) {
        throw new NotFoundError(`Brand with ID ${brandId} not found.`);
      }
    } catch (error) {
      if (error instanceof NotFoundError) throw error;
      logger.error({ err: error, brandId }, 'Database error in updateBrandLogo');
      throw new Error('Failed to update brand logo in database.');
    }
  }

  /**
   * Updates the status of a specific receipt.
   * @param receiptId The ID of the receipt to update.
   * @param status The new status for the receipt.
   * @returns A promise that resolves to the updated Receipt object.
   */
  async updateReceiptStatus(receiptId: number, status: 'pending' | 'processing' | 'completed' | 'failed', logger: Logger): Promise<Receipt> {
    try {
      const res = await this.db.query<Receipt>(
        `UPDATE public.receipts SET status = $1, processed_at = CASE WHEN $1 IN ('completed', 'failed') THEN now() ELSE processed_at END WHERE receipt_id = $2 RETURNING *`,
        [status, receiptId]
      );
      if (res.rowCount === 0) throw new NotFoundError(`Receipt with ID ${receiptId} not found.`);
      return res.rows[0];
    } catch (error) {
      if (error instanceof NotFoundError) throw error;
      logger.error({ err: error, receiptId, status }, 'Database error in updateReceiptStatus');
      throw new Error('Failed to update receipt status.');
    }
  }

  async getAllUsers(logger: Logger): Promise<AdminUserView[]> {
    try {
      const query = `
          SELECT u.user_id, u.email, u.created_at, p.role, p.full_name, p.avatar_url
          FROM public.users u JOIN public.profiles p ON u.user_id = p.user_id ORDER BY u.created_at DESC;
      `;
      const res = await this.db.query<AdminUserView>(query);
      return res.rows;
    } catch (error) {
      logger.error({ err: error }, 'Database error in getAllUsers');
      throw new Error('Failed to retrieve all users.');
    }
  };

  /**
   * Updates the role of a specific user.
   * @param userId The ID of the user to update.
   * @param role The new role to assign ('user' or 'admin').
   * @returns A promise that resolves to the updated Profile object.
   */
  async updateUserRole(userId: string, role: 'user' | 'admin', logger: Logger): Promise<Profile> {
    try {
      const res = await this.db.query<Profile>(
        'UPDATE public.profiles SET role = $1 WHERE user_id = $2 RETURNING *',
        [role, userId]
      );
      if (res.rowCount === 0) {
        throw new NotFoundError(`User with ID ${userId} not found.`);
      }
      return res.rows[0];
    } catch (error) {
      logger.error({ err: error, userId, role }, 'Database error in updateUserRole');
      if (error instanceof Error && 'code' in error && error.code === '23503') {
        throw new ForeignKeyConstraintError('The specified user does not exist.');
      }
      if (error instanceof NotFoundError) {
        throw error;
      }
      throw error; // Re-throw to be handled by the route
    }
  }
}