// src/services/db/notification.db.ts
import type { Pool, PoolClient } from 'pg';
import { getPool } from './connection.db';
import { ForeignKeyConstraintError, NotFoundError } from './errors.db';
import type { Logger } from 'pino';
import type { Notification } from '../../types';

export class NotificationRepository {
    private db: Pool | PoolClient;

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

    /**
     * Inserts a single notification into the database.
     * @param userId The ID of the user to notify.
     * @param content The text content of the notification.
     * @param linkUrl An optional URL for the notification to link to.
     * @returns A promise that resolves to the newly created Notification object.
     */
    async createNotification(userId: string, content: string, logger: Logger, linkUrl?: string): Promise<Notification> {
        try {
            const res = await this.db.query<Notification>(
                `INSERT INTO public.notifications (user_id, content, link_url) VALUES ($1, $2, $3) RETURNING *`,
                [userId, content, linkUrl || null]
            );
            return res.rows[0];
        } catch (error) {
            logger.error({ err: error, userId, content, linkUrl }, 'Database error in createNotification');
            if (error instanceof Error && 'code' in error && error.code === '23503') {
                throw new ForeignKeyConstraintError('The specified user does not exist.');
            }
            throw new Error('Failed to create notification.');
        }
    }

    /**
     * Inserts multiple notifications into the database in a single query.
     * This is more efficient than inserting one by one.
     * @param notifications An array of notification objects to be inserted.
     */
    async createBulkNotifications(notifications: Omit<Notification, 'notification_id' | 'is_read' | 'created_at'>[], logger: Logger): Promise<void> {
        if (notifications.length === 0) {
            return;
        }
        // This method assumes it might be part of a larger transaction, so it uses `this.db`.
        // The calling service is responsible for acquiring and releasing a client if needed.
        try {
            // This is the secure way to perform bulk inserts.
            // We use the `unnest` function in PostgreSQL to turn arrays of parameters
            // into a set of rows that can be inserted. This avoids string concatenation
            // and completely prevents SQL injection.
            const query = `
                INSERT INTO public.notifications (user_id, content, link_url)
                SELECT * FROM unnest($1::uuid[], $2::text[], $3::text[])
            `;

            const userIds = notifications.map(n => n.user_id);
            const contents = notifications.map(n => n.content);
            const linkUrls = notifications.map(n => n.link_url || null);

            await this.db.query(query, [userIds, contents, linkUrls]);
        } catch (error) {
            logger.error({ err: error }, 'Database error in createBulkNotifications');
            if (error instanceof Error && 'code' in error && error.code === '23503') {
                throw new ForeignKeyConstraintError('One or more of the specified users do not exist.');
            }
            throw new Error('Failed to create bulk notifications.');
        }
    }

    /**
     * Retrieves a paginated list of notifications for a specific user.
     * @param userId The ID of the user whose notifications are to be retrieved.
     * @param limit The maximum number of notifications to return.
     * @param offset The number of notifications to skip for pagination.
     * @returns A promise that resolves to an array of Notification objects.
     */
    async getNotificationsForUser(userId: string, limit: number, offset: number, logger: Logger): Promise<Notification[]> {
        try {
            const res = await this.db.query<Notification>(
                `SELECT * FROM public.notifications 
                 WHERE user_id = $1 
                 ORDER BY created_at DESC 
                 LIMIT $2 OFFSET $3`,
                [userId, limit, offset]
            );
            return res.rows;
        } catch (error) {
            logger.error({ err: error, userId, limit, offset }, 'Database error in getNotificationsForUser');
            throw new Error('Failed to retrieve notifications.');
        }
    }

    /**
     * Marks all unread notifications for a user as read.
     * @param userId The ID of the user whose notifications should be marked as read.
     * @returns A promise that resolves when the operation is complete.
     */
    async markAllNotificationsAsRead(userId: string, logger: Logger): Promise<void> {
        try {
            await this.db.query(
                `UPDATE public.notifications SET is_read = true WHERE user_id = $1 AND is_read = false`,
                [userId]
            );
        } catch (error) {
            logger.error({ err: error, userId }, 'Database error in markAllNotificationsAsRead');
            throw new Error('Failed to mark notifications as read.');
        }
    }

    /**
     * Marks a single notification as read for a specific user.
     * Ensures that a user can only mark their own notifications.
     * @param notificationId The ID of the notification to mark as read.
     * @param userId The ID of the user who owns the notification.
     * @returns A promise that resolves to the updated Notification object.
     * @throws An error if the notification is not found or does not belong to the user.
     */
    async markNotificationAsRead(notificationId: number, userId: string, logger: Logger): Promise<Notification> {
    try {
      const res = await this.db.query<Notification>(
        `UPDATE public.notifications SET is_read = true WHERE notification_id = $1 AND user_id = $2 RETURNING *`,
        [notificationId, userId]
      );
      if (res.rowCount === 0) {
        throw new NotFoundError('Notification not found or user does not have permission.');
      }
      return res.rows[0];
    } catch (error) {
      if (error instanceof NotFoundError) throw error;
      logger.error({ err: error, notificationId, userId }, 'Database error in markNotificationAsRead');
      throw new Error('Failed to mark notification as read.');
    }
   }

    /**
     * Deletes notifications that are older than a specified number of days.
     * This is intended for a periodic cleanup job.
     * @param daysOld The minimum age in days for a notification to be deleted.
     * @returns A promise that resolves to the number of deleted notifications.
     */
    async deleteOldNotifications(daysOld: number, logger: Logger): Promise<number> {
        try {
            const res = await this.db.query(
                `DELETE FROM public.notifications WHERE created_at < NOW() - ($1 * interval '1 day')`,
                [daysOld]
            );
            return res.rowCount ?? 0;
        } catch (error) {
            logger.error({ err: error, daysOld }, 'Database error in deleteOldNotifications');
            throw new Error('Failed to delete old notifications.');
        }
    }
}