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

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

export type OtpRow = {
  id: string;
  userId: string;
  channel: string;
  codeHash: string;
  expiresAt: Date;
  attempts: number;
  consumedAt: Date | null;
  createdAt: Date;
};

function map(r: RowDataPacket): OtpRow {
  return {
    id: String(r.id),
    userId: String(r.user_id),
    channel: String(r.channel),
    codeHash: String(r.code_hash),
    expiresAt: new Date(r.expires_at),
    attempts: Number(r.attempts || 0),
    consumedAt: r.consumed_at ? new Date(r.consumed_at) : null,
    createdAt: new Date(r.created_at),
  };
}

export async function findLatestOpenEmailOtpMysql(userId: string): Promise<OtpRow | null> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM otp_challenges
     WHERE user_id = ? AND channel = 'EMAIL' AND consumed_at IS NULL
     ORDER BY created_at DESC LIMIT 1`,
    [userId],
  );
  return rows[0] ? map(rows[0]) : null;
}

export async function consumeOpenEmailOtpsMysql(userId: string) {
  await ensure();
  await sqlExecute(
    `UPDATE otp_challenges SET consumed_at = ?
     WHERE user_id = ? AND channel = 'EMAIL' AND consumed_at IS NULL`,
    [new Date(), userId],
  );
}

export async function createEmailOtpMysql(input: {
  userId: string;
  codeHash: string;
  expiresAt: Date;
}) {
  await ensure();
  const id = createId();
  const now = new Date();
  await sqlExecute(
    `INSERT INTO otp_challenges
      (id, user_id, channel, code_hash, expires_at, attempts, consumed_at, created_at, updated_at)
     VALUES (?,?,?,?,?,0,NULL,?,?)`,
    [id, input.userId, "EMAIL", input.codeHash, input.expiresAt, now, now],
  );
  const { dualWriteOtpCreate } = await import("@/db/dualWrite");
  await dualWriteOtpCreate({
    id,
    userId: input.userId,
    codeHash: input.codeHash,
    expiresAt: input.expiresAt,
  });
  return id;
}

export async function bumpOtpAttemptsMysql(id: string, attempts: number) {
  await ensure();
  await sqlExecute(
    `UPDATE otp_challenges SET attempts = ?, updated_at = ? WHERE id = ?`,
    [attempts, new Date(), id],
  );
}

export async function consumeOtpMysql(id: string) {
  await ensure();
  await sqlExecute(
    `UPDATE otp_challenges SET consumed_at = ?, updated_at = ? WHERE id = ?`,
    [new Date(), new Date(), id],
  );
  const { dualWriteOtpConsume } = await import("@/db/dualWrite");
  await dualWriteOtpConsume(id);
}