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

export type FavoriteRow = {
  id: string;
  userId: string;
  propertyId: string;
  createdAt: Date;
};

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

function map(r: RowDataPacket): FavoriteRow {
  return {
    id: String(r.id),
    userId: String(r.user_id),
    propertyId: String(r.property_id),
    createdAt: new Date(r.created_at),
  };
}

export async function listFavoritesMysql(userId: string): Promise<FavoriteRow[]> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM favorites WHERE user_id = ? ORDER BY created_at DESC`,
    [userId],
  );
  return rows.map(map);
}

export async function findFavoriteMysql(
  userId: string,
  propertyId: string,
): Promise<FavoriteRow | null> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM favorites WHERE user_id = ? AND property_id = ? LIMIT 1`,
    [userId, propertyId],
  );
  return rows[0] ? map(rows[0]) : null;
}

export async function createFavoriteMysql(
  userId: string,
  propertyId: string,
): Promise<FavoriteRow> {
  await ensure();
  const id = createId();
  const now = new Date();
  await sqlExecute(
    `INSERT INTO favorites (id, user_id, property_id, created_at) VALUES (?,?,?,?)`,
    [id, userId, propertyId, now],
  );
  const row = { id, userId, propertyId, createdAt: now };
  await dualWriteFavoriteCreate(row);
  return row;
}

export async function deleteFavoriteMysql(id: string) {
  await ensure();
  await sqlExecute(`DELETE FROM favorites WHERE id = ?`, [id]);
  await dualWriteFavoriteDelete(id);
}

/** Published property exists in MySQL (for toggle validation when properties stay on Mongo this may be unused). */
export async function propertyPublishedExistsMysql(propertyId: string): Promise<boolean> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT id FROM properties
     WHERE id = ? AND deleted_at IS NULL AND status = 'PUBLISHED'
     LIMIT 1`,
    [propertyId],
  );
  return rows.length > 0;
}