/**
 * Queries de notificações
 * CRUD completo para a tabela notifications
 */

import { query } from "../index";

export interface Notification {
  notification_id: number;
  user_id: number;
  notification_type:
    | "bid"
    | "sale"
    | "purchase"
    | "escrow"
    | "dispute"
    | "system";
  title: string;
  message: string;
  related_id: number | null;
  is_read: boolean;
  send_whatsapp: boolean;
  whatsapp_sent: boolean;
  created_at: Date;
}

export interface CreateNotificationData {
  user_id: number;
  notification_type: Notification["notification_type"];
  title: string;
  message: string;
  related_id?: number | null;
  send_whatsapp?: boolean;
}

/**
 * Cria uma nova notificação
 */
export async function createNotification(
  data: CreateNotificationData,
): Promise<Notification> {
  const result = await query<Notification>(
    `
    INSERT INTO notifications (user_id, notification_type, title, message, related_id, send_whatsapp)
    VALUES ($1, $2, $3, $4, $5, $6)
    RETURNING *
    `,
    [
      data.user_id,
      data.notification_type,
      data.title,
      data.message,
      data.related_id || null,
      data.send_whatsapp || false,
    ],
  );

  return result.rows[0];
}

/**
 * Busca notificações de um usuário
 */
export async function getUserNotifications(
  userId: number,
  filters: { is_read?: boolean; limit?: number } = {},
): Promise<Notification[]> {
  const { is_read, limit = 50 } = filters;

  const conditions: string[] = [`user_id = $1`];
  const params: any[] = [userId];
  let paramIndex = 2;

  if (is_read !== undefined) {
    conditions.push(`is_read = $${paramIndex}`);
    params.push(is_read);
    paramIndex++;
  }

  const whereClause =
    conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";

  const result = await query<Notification>(
    `
    SELECT * FROM notifications
    ${whereClause}
    ORDER BY created_at DESC
    LIMIT $${paramIndex}
    `,
    [...params, limit],
  );

  return result.rows;
}

/**
 * Conta notificações não lidas de um usuário
 */
export async function getUnreadCount(userId: number): Promise<number> {
  const result = await query<{ count: string }>(
    `
    SELECT COUNT(*) as count FROM notifications
    WHERE user_id = $1 AND is_read = FALSE
    `,
    [userId],
  );

  return parseInt(result.rows[0].count, 10);
}

/**
 * Marca notificação como lida
 */
export async function markAsRead(
  notificationId: number,
  userId: number,
): Promise<boolean> {
  const result = await query(
    `
    UPDATE notifications
    SET is_read = TRUE
    WHERE notification_id = $1 AND user_id = $2
    `,
    [notificationId, userId],
  );

  return (result.rowCount ?? 0) > 0;
}

/**
 * Marca todas as notificações de um usuário como lidas
 */
export async function markAllAsRead(userId: number): Promise<number> {
  const result = await query(
    `
    UPDATE notifications
    SET is_read = TRUE
    WHERE user_id = $1 AND is_read = FALSE
    `,
    [userId],
  );

  return result.rowCount || 0;
}

/**
 * Remove notificação
 */
export async function deleteNotification(
  notificationId: number,
  userId: number,
): Promise<boolean> {
  const result = await query(
    `
    DELETE FROM notifications
    WHERE notification_id = $1 AND user_id = $2
    `,
    [notificationId, userId],
  );

  return (result.rowCount ?? 0) > 0;
}