import type { PoolConnection } from 'mysql2/promise';
import { queryWithConn, executeWithConn } from '../../config/database.js';
import type { Notification } from '../types/index.js';

function rowToNotification(row: Record<string, unknown>): Notification {
  return {
    id: row.id as string,
    user_id: row.user_id as string,
    order_id: (row.order_id as string) ?? undefined,
    type: row.type as string,
    title: row.title as string,
    message: (row.message as string) ?? undefined,
    is_read: Boolean(row.is_read),
    read_at: row.read_at ? (row.read_at as Date).toISOString() : undefined,
    created_at: (row.created_at as Date).toISOString(),
  };
}

export async function findByUserId(
  userId: string,
  page = 1,
  limit = 20,
  conn?: PoolConnection,
): Promise<{ rows: Notification[]; total: number }> {
  const offset = (Number(page) - 1) * Number(limit);

  const countRows = await queryWithConn(
    'SELECT COUNT(*) as total FROM notifications WHERE user_id = ?',
    [userId],
    conn,
  ) as unknown as { total: number }[];
  const total = countRows[0]?.total ?? 0;

  const sql = `
    SELECT id as id, user_id as user_id,
           order_id as order_id, type, title, message, is_read, read_at, created_at
    FROM notifications
    WHERE user_id = ?
    ORDER BY created_at DESC
    LIMIT ? OFFSET ?
  `;
  const rows = await queryWithConn(sql, [userId, Number(limit), Number(offset)], conn) as unknown as Record<string, unknown>[];
  return { rows: rows.map(rowToNotification), total };
}

export async function getUnreadCount(userId: string, conn?: PoolConnection): Promise<number> {
  const rows = await queryWithConn(
    'SELECT COUNT(*) as count FROM notifications WHERE user_id = ? AND is_read = FALSE',
    [userId],
    conn,
  ) as unknown as { count: number }[];
  return rows[0]?.count ?? 0;
}

export async function markAsRead(id: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn('UPDATE notifications SET is_read = TRUE, read_at = NOW() WHERE id = ?', [id], conn);
}

export async function markAllAsRead(userId: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn('UPDATE notifications SET is_read = TRUE, read_at = NOW() WHERE user_id = ? AND is_read = FALSE', [userId], conn);
}

export async function remove(id: string, userId: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn('DELETE FROM notifications WHERE id = ? AND user_id = ?', [id, userId], conn);
}

export async function removeAllForUser(userId: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn('DELETE FROM notifications WHERE user_id = ?', [userId], conn);
}

export async function insert(
  data: { user_id: string; order_id?: string; type: string; title: string; message?: string },
  id: string,
  conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    'INSERT INTO notifications (id, user_id, order_id, type, title, message) VALUES (?, ?, ?, ?, ?, ?)',
    [id, data.user_id, data.order_id ?? null, data.type, data.title, data.message ?? null],
    conn,
  );
}