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

export type UserRow = {
  id: string;
  email: string;
  passwordHash?: string;
  googleSub?: string;
  fullName: string;
  phone?: string;
  avatarUrl?: string;
  role: string;
  status: string;
  locale: string;
  emailVerifiedAt?: Date | null;
  phoneVerifiedAt?: Date | null;
  passportUrl?: string | null;
  ownerVerificationStatus: string;
  trustedOwner: boolean;
  ownerVerifiedAt?: Date | null;
  deletedAt?: Date | null;
  createdAt: Date;
  updatedAt: Date;
};

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

function mapUser(r: RowDataPacket): UserRow {
  return {
    id: String(r.id),
    email: String(r.email),
    passwordHash: r.password_hash == null ? undefined : String(r.password_hash),
    googleSub: r.google_sub == null ? undefined : String(r.google_sub),
    fullName: String(r.full_name),
    phone: r.phone == null ? undefined : String(r.phone),
    avatarUrl: r.avatar_url == null ? undefined : String(r.avatar_url),
    role: String(r.role),
    status: String(r.status),
    locale: String(r.locale || "ar"),
    emailVerifiedAt: r.email_verified_at ? new Date(r.email_verified_at) : null,
    phoneVerifiedAt: r.phone_verified_at ? new Date(r.phone_verified_at) : null,
    passportUrl: r.passport_url == null ? null : String(r.passport_url),
    ownerVerificationStatus: String(r.owner_verification_status || "NONE"),
    trustedOwner: Boolean(r.trusted_owner),
    ownerVerifiedAt: r.owner_verified_at ? new Date(r.owner_verified_at) : null,
    deletedAt: r.deleted_at ? new Date(r.deleted_at) : null,
    createdAt: new Date(r.created_at),
    updatedAt: new Date(r.updated_at),
  };
}

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

export async function findUserByEmailMysql(email: string): Promise<UserRow | null> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM users WHERE email = ? LIMIT 1`,
    [email.toLowerCase()],
  );
  return rows[0] ? mapUser(rows[0]) : null;
}

export async function findActiveUserByEmailMysql(email: string): Promise<UserRow | null> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM users WHERE email = ? AND deleted_at IS NULL LIMIT 1`,
    [email.toLowerCase()],
  );
  return rows[0] ? mapUser(rows[0]) : null;
}

export async function findUserByPhoneMysql(phone: string): Promise<UserRow | null> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM users WHERE phone = ? LIMIT 1`,
    [phone],
  );
  return rows[0] ? mapUser(rows[0]) : null;
}

export async function countUsersMysql(): Promise<number> {
  await ensure();
  const rows = await sqlQuery<RowDataPacket[]>(`SELECT COUNT(*) AS c FROM users`);
  return Number(rows[0]?.c || 0);
}

export async function findUsersByIdsMysql(ids: string[]): Promise<UserRow[]> {
  await ensure();
  if (ids.length === 0) return [];
  const ph = ids.map(() => "?").join(",");
  const rows = await sqlQuery<RowDataPacket[]>(
    `SELECT * FROM users WHERE id IN (${ph})`,
    ids,
  );
  return rows.map(mapUser);
}

export async function createUserMysql(input: {
  fullName: string;
  email: string;
  phone?: string | null;
  passwordHash?: string | null;
  role: string;
  locale?: string;
  status?: string;
  emailVerifiedAt?: Date | null;
}): Promise<UserRow> {
  await ensure();
  const id = createId();
  const now = new Date();
  await sqlExecute(
    `INSERT INTO users (
      id, email, password_hash, google_sub, full_name, phone, avatar_url, role, status, locale,
      email_verified_at, phone_verified_at, passport_url, owner_verification_status, trusted_owner,
      owner_verified_at, deleted_at, created_at, updated_at
    ) VALUES (?,?,?,NULL,?,?,NULL,?,?,?,?,NULL,NULL,'NONE',0,NULL,NULL,?,?)`,
    [
      id,
      input.email.toLowerCase(),
      input.passwordHash ?? null,
      input.fullName,
      input.phone ?? null,
      input.role,
      input.status ?? "PENDING_EMAIL_VERIFICATION",
      input.locale ?? "ar",
      input.emailVerifiedAt ?? null,
      now,
      now,
    ],
  );
  const row = (await findUserByIdMysql(id))!;
  await dualWriteUserUpsert(row);
  return row;
}

export async function updateUserMysql(
  id: string,
  patch: Partial<{
    fullName: string;
    phone: string | null;
    locale: string;
    avatarUrl: string | null;
    passwordHash: string;
    role: string;
    status: string;
    emailVerifiedAt: Date | null;
    passportUrl: string | null;
    ownerVerificationStatus: string;
    trustedOwner: boolean;
    ownerVerifiedAt: Date | null;
    deletedAt: Date | null;
  }>,
): Promise<UserRow | null> {
  await ensure();
  const fields: string[] = [];
  const params: unknown[] = [];
  const map: Record<string, string> = {
    fullName: "full_name",
    phone: "phone",
    locale: "locale",
    avatarUrl: "avatar_url",
    passwordHash: "password_hash",
    role: "role",
    status: "status",
    emailVerifiedAt: "email_verified_at",
    passportUrl: "passport_url",
    ownerVerificationStatus: "owner_verification_status",
    trustedOwner: "trusted_owner",
    ownerVerifiedAt: "owner_verified_at",
    deletedAt: "deleted_at",
  };
  for (const [k, col] of Object.entries(map)) {
    if (k in patch) {
      fields.push(`${col} = ?`);
      let v = (patch as any)[k];
      if (k === "trustedOwner") v = v ? 1 : 0;
      params.push(v);
    }
  }
  if (fields.length === 0) return findUserByIdMysql(id);
  fields.push("updated_at = ?");
  params.push(new Date(), id);
  await sqlExecute(`UPDATE users SET ${fields.join(", ")} WHERE id = ?`, params);
  const row = await findUserByIdMysql(id);
  if (row) {
    await dualWriteUserUpsert(row);
  }
  return row;
}