import type { RowDataPacket } from "mysql2/promise";
import { pool } from "../db/mysql.js";
import type { MyAd } from "../exchange/types.js";

/** Repositório de anúncios espelhados (`ads`). */

export interface AdRow extends RowDataPacket {
  id: number;
  account_id: number;
  adv_no: string;
  asset: string;
  fiat: string;
  trade_type: "BUY" | "SELL";
  price: string | null;
  price_type: number | null;
  status: number | null;
  surplus_amount: string | null;
  min_amount: string | null;
  max_amount: string | null;
  synced_at: Date | null;
}

/** Insere/atualiza um anúncio pela chave (account_id, adv_no). Devolve o id. */
export async function upsertAd(accountId: number, ad: MyAd): Promise<number> {
  await pool.query(
    `INSERT INTO ads
       (account_id, adv_no, asset, fiat, trade_type, price, price_type, status,
        surplus_amount, min_amount, max_amount, payload_raw, synced_at)
     VALUES
       (:accountId, :advNo, :asset, :fiat, :tradeType, :price, :priceType, :status,
        :surplus, :minAmount, :maxAmount, CAST(:payload AS JSON), NOW())
     ON DUPLICATE KEY UPDATE
       asset = VALUES(asset), fiat = VALUES(fiat), trade_type = VALUES(trade_type),
       price = VALUES(price), price_type = VALUES(price_type), status = VALUES(status),
       surplus_amount = VALUES(surplus_amount), min_amount = VALUES(min_amount),
       max_amount = VALUES(max_amount), payload_raw = VALUES(payload_raw),
       synced_at = NOW()`,
    {
      accountId,
      advNo: ad.advNo,
      asset: ad.asset,
      fiat: ad.fiat,
      tradeType: ad.tradeType,
      price: ad.price,
      priceType: ad.priceType,
      status: ad.status,
      surplus: ad.surplusAmount,
      minAmount: ad.minAmount,
      maxAmount: ad.maxAmount,
      payload: JSON.stringify(ad.raw ?? null),
    },
  );
  const [rows] = await pool.query<AdRow[]>(
    `SELECT id FROM ads WHERE account_id = :accountId AND adv_no = :advNo`,
    { accountId, advNo: ad.advNo },
  );
  return rows[0]?.id ?? 0;
}

export async function getAdById(id: number): Promise<AdRow | null> {
  const [rows] = await pool.query<AdRow[]>(`SELECT * FROM ads WHERE id = :id`, { id });
  return rows[0] ?? null;
}

export async function listAds(accountId?: number): Promise<AdRow[]> {
  if (accountId) {
    const [rows] = await pool.query<AdRow[]>(
      `SELECT * FROM ads WHERE account_id = :accountId ORDER BY id`,
      { accountId },
    );
    return rows;
  }
  const [rows] = await pool.query<AdRow[]>(`SELECT * FROM ads ORDER BY id`);
  return rows;
}

/** Atualiza só o preço espelhado (após aplicar um ajuste). */
export async function setAdPrice(id: number, price: string): Promise<void> {
  await pool.query(`UPDATE ads SET price = :price WHERE id = :id`, { id, price });
}