import type { RowDataPacket } from "mysql2/promise";
import { createId } from "@/db/ids";
import { dualWriteNotificationCreate, dualWriteNotificationRead, dualWriteNotificationReadAll } from "@/db/dualWrite";
import { connectMysql, mysqlPool, sqlExecute, sqlQuery } from "./pool";

export type NotificationRow = {
  id: string;
  userId: string;
  titleAr: string;
  titleEn: string;
  messageAr: string;
  messageEn: string;
  link: string | null;
  readAt: Date | null;
  createdAt: Date;
};

async function ensure() {
  try {
    mysqlPool();
  } catch {
    await connectMysql();
  }
}

function map(r: RowDataPacket): NotificationRow {
  return {
    id: String(r.id),
    userId: String(r.user_id),
    titleAr: String(r.title_ar),
    titleEn: String(r.title_en),
    messageAr: String(r.message_ar),
    messageEn: String(r.message_en),
    link: r.link == null ? null : String(r.link),
    readAt: r.read_at ? new Date(r.read_at) : null,
    createdAt: new Date(r.created_at),
  };
}

export function notificationToApi(n: NotificationRow) {
  return {
    _id: n.id,
    id: n.id,
    userId: n.userId,
    titleAr: n.titleAr,
    titleEn: n.titleEn,
    messageAr: n.messageAr,
    messageEn: n.messageEn,
    link: n.link ?? undefined,
    readAt: n.readAt,
    createdAt: n.createdAt,
  };
}

export async function createNotificationMysql(input: {
  userId: string;
  titleAr: string;
  titleEn: string;
  messageAr: string;
  messageEn: string;
  link?: string;
}): Promise<NotificationRow> {
  await ensure();
  const id = createId();
  const now = new Date();
  await sqlExecute(
    `INSERT INTO notifications
      (id, user_id, title_ar, title_en, message_ar, message_en, link, read_at, created_at)
     VALUES (?,?,?,?,?,?,?,NULL,?)`,
    [
      id,
      input.userId,
      input.titleAr,
      input.titleEn,
      input.messageAr,
      input.messageEn,
      input.link ?? null,
      now,
    ],
  );
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM notifications WHERE id = ? LIMIT 1`,
    [id],
  );
  const row = map(rows[0]);
  await dualWriteNotificationCreate(row);
  return row;
}

export async function listNotificationsMysql(
  userId: string,
  limit = 50,
): Promise<{ notifications: NotificationRow[]; unreadCount: number }> {
  await ensure();
  const notifications = (
    await sqlQuery<RowDataPacket[]>(
      `SELECT * FROM notifications WHERE user_id = ? ORDER BY created_at DESC LIMIT ?`,
      [userId, limit],
    )
  ).map(map);
  const countRows = await sqlQuery<RowDataPacket[]>(
    `SELECT COUNT(*) AS c FROM notifications WHERE user_id = ? AND read_at IS NULL`,
    [userId],
  );
  return { notifications, unreadCount: Number(countRows[0]?.c ?? 0) };
}

export async function markNotificationReadMysql(id: string, userId: string) {
  await ensure();
  const now = new Date();
  await sqlExecute(
    `UPDATE notifications SET read_at = ? WHERE id = ? AND user_id = ? AND read_at IS NULL`,
    [now, id, userId],
  );
  await dualWriteNotificationRead(id, now);
}

export async function markAllNotificationsReadMysql(userId: string) {
  await ensure();
  const now = new Date();
  await sqlExecute(
    `UPDATE notifications SET read_at = ? WHERE user_id = ? AND read_at IS NULL`,
    [now, userId],
  );
  await dualWriteNotificationReadAll(userId, now);
}