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

export type ReviewRow = {
  id: string;
  bookingId: string;
  propertyId: string;
  authorId: string;
  rating: number;
  comment: string | null;
  ownerReply: string | null;
  ownerReplyBy: string | null;
  createdAt: Date;
  updatedAt: Date;
};

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

function map(r: RowDataPacket): ReviewRow {
  return {
    id: String(r.id),
    bookingId: String(r.booking_id),
    propertyId: String(r.property_id),
    authorId: String(r.author_id),
    rating: Number(r.rating),
    comment: r.comment == null ? null : String(r.comment),
    ownerReply: r.owner_reply == null ? null : String(r.owner_reply),
    ownerReplyBy: r.owner_reply_by == null ? null : String(r.owner_reply_by),
    createdAt: new Date(r.created_at),
    updatedAt: new Date(r.updated_at),
  };
}

export function reviewToApi(r: ReviewRow) {
  return {
    _id: r.id,
    id: r.id,
    bookingId: r.bookingId,
    propertyId: r.propertyId,
    authorId: r.authorId,
    rating: r.rating,
    comment: r.comment ?? undefined,
    ownerReply: r.ownerReply ?? undefined,
    ownerReplyBy: r.ownerReplyBy ?? undefined,
    createdAt: r.createdAt,
    updatedAt: r.updatedAt,
  };
}

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

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

export async function createReviewMysql(input: {
  bookingId: string;
  propertyId: string;
  authorId: string;
  rating: number;
  comment?: string;
}): Promise<ReviewRow> {
  await ensure();
  const id = createId();
  const now = new Date();
  await sqlExecute(
    `INSERT INTO reviews
      (id, booking_id, property_id, author_id, rating, comment, owner_reply, owner_reply_by, created_at, updated_at)
     VALUES (?,?,?,?,?,?,NULL,NULL,?,?)`,
    [
      id,
      input.bookingId,
      input.propertyId,
      input.authorId,
      input.rating,
      input.comment ?? null,
      now,
      now,
    ],
  );
  const row = await findReviewByIdMysql(id);
  if (!row) throw new Error("failed to create review");
  await dualWriteReviewUpsert(row);
  return row;
}

export async function setOwnerReplyMysql(
  id: string,
  ownerReply: string,
  ownerReplyBy: string,
): Promise<ReviewRow | null> {
  await ensure();
  await sqlExecute(
    `UPDATE reviews SET owner_reply = ?, owner_reply_by = ?, updated_at = ? WHERE id = ?`,
    [ownerReply, ownerReplyBy, new Date(), id],
  );
  const row = await findReviewByIdMysql(id);
  if (row) {
    await dualWriteReviewUpsert(row);
  }
  return row;
}

export async function listReviewsMysql(opts?: { take?: number }): Promise<ReviewRow[]> {
  await ensure();
  const take = Math.min(Math.max(opts?.take ?? 100, 1), 500);
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM reviews ORDER BY created_at DESC LIMIT ?`,
    [take],
  );
  return rows.map(map);
}

export async function listReviewsByPropertyMysql(propertyId: string): Promise<ReviewRow[]> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM reviews WHERE property_id = ? ORDER BY created_at DESC`,
    [propertyId],
  );
  return rows.map(map);
}

export async function listReviewsByPropertyIdsMysql(
  propertyIds: string[],
): Promise<ReviewRow[]> {
  await ensure();
  if (propertyIds.length === 0) return [];
  const ph = propertyIds.map(() => "?").join(",");
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM reviews WHERE property_id IN (${ph}) ORDER BY created_at DESC`,
    propertyIds,
  );
  return rows.map(map);
}

export async function reviewStatsByPropertyIdsMysql(propertyIds: string[]) {
  await ensure();
  if (propertyIds.length === 0) return [] as { propertyId: string; count: number; averageRating: number }[];
  const ph = propertyIds.map(() => "?").join(",");
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT property_id AS propertyId,
            COUNT(*) AS count,
            AVG(rating) AS averageRating
     FROM reviews
     WHERE property_id IN (${ph})
     GROUP BY property_id`,
    propertyIds,
  );
  return rows.map((r) => ({
    propertyId: String(r.propertyId),
    count: Number(r.count),
    averageRating: Number(r.averageRating),
  }));
}

export async function latestCommentedReviewsMysql(propertyIds: string[]) {
  await ensure();
  if (propertyIds.length === 0) return [] as ReviewRow[];
  const ph = propertyIds.map(() => "?").join(",");
  // Latest non-empty comment per property
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT r.*
     FROM reviews r
     INNER JOIN (
       SELECT property_id, MAX(created_at) AS max_created
       FROM reviews
       WHERE property_id IN (${ph})
         AND comment IS NOT NULL AND comment <> ''
       GROUP BY property_id
     ) t ON t.property_id = r.property_id AND t.max_created = r.created_at
     WHERE r.comment IS NOT NULL AND r.comment <> ''`,
    propertyIds,
  );
  return rows.map(map);
}

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