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

export type ContactMessageRow = {
  id: string;
  userId: string | null;
  name: string;
  email: string;
  subject: string;
  message: string;
  readAt: Date | null;
  createdAt: Date;
};

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

function map(r: RowDataPacket): ContactMessageRow {
  return {
    id: String(r.id),
    userId: r.user_id == null ? null : String(r.user_id),
    name: String(r.name),
    email: String(r.email),
    subject: String(r.subject),
    message: String(r.message),
    readAt: r.read_at ? new Date(r.read_at) : null,
    createdAt: new Date(r.created_at),
  };
}

export function contactToApi(c: ContactMessageRow) {
  return {
    _id: c.id,
    id: c.id,
    userId: c.userId ?? undefined,
    name: c.name,
    email: c.email,
    subject: c.subject,
    message: c.message,
    readAt: c.readAt,
    createdAt: c.createdAt,
  };
}

export async function createContactMysql(input: {
  name: string;
  email: string;
  subject: string;
  message: string;
  userId?: string;
}): Promise<ContactMessageRow> {
  await ensure();
  const id = createId();
  const now = new Date();
  await sqlExecute(
    `INSERT INTO contact_messages (id, user_id, name, email, subject, message, read_at, created_at)
     VALUES (?,?,?,?,?,?,NULL,?)`,
    [id, input.userId ?? null, input.name, input.email, input.subject, input.message, now],
  );
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM contact_messages WHERE id = ? LIMIT 1`,
    [id],
  );
  const row = map(rows[0]);
  const { dualWriteContactCreate } = await import("@/db/dualWrite");
  await dualWriteContactCreate(row);
  return row;
}

export async function listContactMysql(limit = 100): Promise<ContactMessageRow[]> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM contact_messages ORDER BY created_at DESC LIMIT ?`,
    [limit],
  );
  return rows.map(map);
}

export async function findContactByIdMysql(id: string): Promise<ContactMessageRow | null> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM contact_messages WHERE id = ? LIMIT 1`,
    [id],
  );
  return rows[0] ? map(rows[0]) : null;
}

export async function setContactReadMysql(
  id: string,
  read: boolean,
): Promise<ContactMessageRow | null> {
  await ensure();
  await sqlExecute(`UPDATE contact_messages SET read_at = ? WHERE id = ?`, [
    read ? new Date() : null,
    id,
  ]);
  return findContactByIdMysql(id);
}

/** Admin user ids for notify — still from MySQL users if present. */
export async function listAdminUserIdsMysql(): Promise<string[]> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT id FROM users
     WHERE deleted_at IS NULL AND role IN ('ADMIN','SUPER_ADMIN')`,
  );
  return rows.map((r) => String(r.id));
}