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

export type LoyaltyAccountRow = {
  id: string;
  userId: string;
  points: number;
  createdAt: Date;
  updatedAt: Date;
};

export type LoyaltyTxnRow = {
  id: string;
  userId: string;
  type: string;
  delta: number;
  pointsAfter: number;
  bookingId: string | null;
  note: string | null;
  createdAt: Date;
};

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

function mapAccount(r: RowDataPacket): LoyaltyAccountRow {
  return {
    id: String(r.id),
    userId: String(r.user_id),
    points: Number(r.points),
    createdAt: new Date(r.created_at),
    updatedAt: new Date(r.updated_at),
  };
}

function mapTxn(r: RowDataPacket): LoyaltyTxnRow {
  return {
    id: String(r.id),
    userId: String(r.user_id),
    type: String(r.type),
    delta: Number(r.delta),
    pointsAfter: Number(r.points_after),
    bookingId: r.booking_id == null ? null : String(r.booking_id),
    note: r.note == null ? null : String(r.note),
    createdAt: new Date(r.created_at),
  };
}

export function loyaltyAccountToApi(a: LoyaltyAccountRow) {
  return {
    _id: a.id,
    id: a.id,
    userId: a.userId,
    points: a.points,
    createdAt: a.createdAt,
    updatedAt: a.updatedAt,
  };
}

export function loyaltyTxnToApi(t: LoyaltyTxnRow) {
  return {
    _id: t.id,
    id: t.id,
    userId: t.userId,
    type: t.type,
    delta: t.delta,
    pointsAfter: t.pointsAfter,
    bookingId: t.bookingId ?? undefined,
    note: t.note ?? undefined,
    createdAt: t.createdAt,
  };
}

export async function findLoyaltyByUserMysql(userId: string): Promise<LoyaltyAccountRow | null> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM loyalty_accounts WHERE user_id = ? LIMIT 1`,
    [userId],
  );
  return rows[0] ? mapAccount(rows[0]) : null;
}

export async function ensureLoyaltyMysql(userId: string): Promise<LoyaltyAccountRow> {
  await ensure();
  const existing = await findLoyaltyByUserMysql(userId);
  if (existing) return existing;
  const id = createId();
  const now = new Date();
  await sqlExecute(
    `INSERT INTO loyalty_accounts (id, user_id, points, created_at, updated_at) VALUES (?,?,0,?,?)`,
    [id, userId, now, now],
  );
  const row = (await findLoyaltyByUserMysql(userId))!;
  await dualWriteLoyaltyAccount(row);
  return row;
}

export async function listLoyaltyTxnsMysql(userId: string, limit = 40): Promise<LoyaltyTxnRow[]> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM loyalty_txns WHERE user_id = ? ORDER BY created_at DESC LIMIT ?`,
    [userId, limit],
  );
  return rows.map(mapTxn);
}

export async function adjustLoyaltyMysql(params: {
  userId: string;
  delta: number;
  type: string;
  bookingId?: string;
  note?: string;
}): Promise<{ account: LoyaltyAccountRow; txn: LoyaltyTxnRow }> {
  await ensure();
  return withMysqlTxn(async (conn) => {
    const [accRows] = await conn.query<RowDataPacket[]>(
      `SELECT * FROM loyalty_accounts WHERE user_id = ? LIMIT 1 FOR UPDATE`,
      [params.userId],
    );
    let account = accRows[0] ? mapAccount(accRows[0]) : null;
    if (!account) {
      const id = createId();
      const now = new Date();
      await conn.execute(
        `INSERT INTO loyalty_accounts (id, user_id, points, created_at, updated_at) VALUES (?,?,0,?,?)`,
        [id, params.userId, now, now],
      );
      account = {
        id,
        userId: params.userId,
        points: 0,
        createdAt: now,
        updatedAt: now,
      };
    }

    const next = account.points + params.delta;
    if (next < 0) {
      const err = new Error("Insufficient loyalty points");
      (err as any).status = 400;
      throw err;
    }

    const now = new Date();
    await conn.execute(
      `UPDATE loyalty_accounts SET points = ?, updated_at = ? WHERE id = ?`,
      [next, now, account.id],
    );

    const txnId = createId();
    await conn.execute(
      `INSERT INTO loyalty_txns
        (id, user_id, type, delta, points_after, booking_id, note, created_at)
       VALUES (?,?,?,?,?,?,?,?)`,
      [
        txnId,
        params.userId,
        params.type,
        params.delta,
        next,
        params.bookingId ?? null,
        params.note ?? null,
        now,
      ],
    );

    return {
      account: { ...account, points: next, updatedAt: now },
      txn: {
        id: txnId,
        userId: params.userId,
        type: params.type,
        delta: params.delta,
        pointsAfter: next,
        bookingId: params.bookingId ?? null,
        note: params.note ?? null,
        createdAt: now,
      },
    };
  }).then(async (out) => {
    await dualWriteLoyaltyAccount(out.account);
    await dualWriteLoyaltyTxn(out.txn);
    return out;
  });
}