import type { RowDataPacket } from "mysql2/promise";
import { pool } from "../db/mysql.js";
import type { AdRow } from "./ads.js";

/** Repositório de config de auto-pricing e histórico de ajustes. */

export interface PricingConfigRow extends RowDataPacket {
  ad_id: number;
  enabled: number;
  follow_strategy: "beat_top" | "match_rank";
  beat_amount: string | null;
  target_rank: number | null;
  competitor_filter: unknown;
  min_profit_price: string | null;
  spot_guard_enabled: number;
  spot_symbol: string | null;
  spot_margin_ratio: string | null;
  min_price_step: string | null;
  last_run_at: Date | null;
}

/** Campos de entrada do CRUD de config (sem o index signature do Row). */
export interface PricingConfigInput {
  enabled?: number;
  follow_strategy?: "beat_top" | "match_rank";
  beat_amount?: string | null;
  target_rank?: number | null;
  competitor_filter?: unknown;
  min_profit_price?: string | null;
  spot_guard_enabled?: number;
  spot_symbol?: string | null;
  spot_margin_ratio?: string | null;
  min_price_step?: string | null;
}

export async function getConfig(adId: number): Promise<PricingConfigRow | null> {
  const [rows] = await pool.query<PricingConfigRow[]>(
    `SELECT * FROM ad_pricing_config WHERE ad_id = :adId`,
    { adId },
  );
  return rows[0] ?? null;
}

/** Cria/atualiza a config de pricing de um anúncio (CRUD do painel). */
export async function upsertConfig(adId: number, cfg: PricingConfigInput): Promise<void> {
  await pool.query(
    `INSERT INTO ad_pricing_config
       (ad_id, enabled, follow_strategy, beat_amount, target_rank, competitor_filter,
        min_profit_price, spot_guard_enabled, spot_symbol, spot_margin_ratio, min_price_step)
     VALUES
       (:adId, :enabled, :followStrategy, :beatAmount, :targetRank, CAST(:competitorFilter AS JSON),
        :minProfitPrice, :spotGuardEnabled, :spotSymbol, :spotMarginRatio, :minPriceStep)
     ON DUPLICATE KEY UPDATE
       enabled = VALUES(enabled), follow_strategy = VALUES(follow_strategy),
       beat_amount = VALUES(beat_amount), target_rank = VALUES(target_rank),
       competitor_filter = VALUES(competitor_filter),
       min_profit_price = VALUES(min_profit_price),
       spot_guard_enabled = VALUES(spot_guard_enabled), spot_symbol = VALUES(spot_symbol),
       spot_margin_ratio = VALUES(spot_margin_ratio), min_price_step = VALUES(min_price_step)`,
    {
      adId,
      enabled: cfg.enabled ?? 0,
      followStrategy: cfg.follow_strategy ?? "beat_top",
      beatAmount: cfg.beat_amount ?? null,
      targetRank: cfg.target_rank ?? null,
      competitorFilter: cfg.competitor_filter ? JSON.stringify(cfg.competitor_filter) : null,
      minProfitPrice: cfg.min_profit_price ?? null,
      spotGuardEnabled: cfg.spot_guard_enabled ?? 1,
      spotSymbol: cfg.spot_symbol ?? null,
      spotMarginRatio: cfg.spot_margin_ratio ?? null,
      minPriceStep: cfg.min_price_step ?? null,
    },
  );
}

/** Anúncios com pricing ligado, já com a config e os dados do anúncio. */
export async function listEnabled(): Promise<Array<{ ad: AdRow; config: PricingConfigRow }>> {
  const [rows] = await pool.query<(AdRow & PricingConfigRow)[]>(
    `SELECT a.*, c.*
       FROM ad_pricing_config c
       JOIN ads a ON a.id = c.ad_id
      WHERE c.enabled = 1`,
  );
  // O join devolve colunas das duas tabelas; reconsultamos cada parte
  // tipada para não confundir colunas homônimas.
  const out: Array<{ ad: AdRow; config: PricingConfigRow }> = [];
  for (const r of rows) {
    const ad = await getAdRow(r.ad_id);
    const config = await getConfig(r.ad_id);
    if (ad && config) out.push({ ad, config });
  }
  return out;
}

async function getAdRow(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 touchRun(adId: number): Promise<void> {
  await pool.query(`UPDATE ad_pricing_config SET last_run_at = NOW() WHERE ad_id = :adId`, {
    adId,
  });
}

export interface RecordAdjustmentInput {
  adId: number;
  oldPrice: string | null;
  newPrice: string | null;
  competitorPrice: string | null;
  spotPrice: string | null;
  reason: string;
  applied: boolean;
}

export async function recordAdjustment(input: RecordAdjustmentInput): Promise<void> {
  await pool.query(
    `INSERT INTO price_adjustments
       (ad_id, old_price, new_price, competitor_price, spot_price, reason, applied)
     VALUES (:adId, :oldPrice, :newPrice, :competitorPrice, :spotPrice, :reason, :applied)`,
    {
      adId: input.adId,
      oldPrice: input.oldPrice,
      newPrice: input.newPrice,
      competitorPrice: input.competitorPrice,
      spotPrice: input.spotPrice,
      reason: input.reason,
      applied: input.applied ? 1 : 0,
    },
  );
}

export interface AdjustmentRow extends RowDataPacket {
  id: number;
  ad_id: number;
  old_price: string | null;
  new_price: string | null;
  competitor_price: string | null;
  spot_price: string | null;
  reason: string | null;
  applied: number;
  created_at: Date;
}

export async function listAdjustments(adId?: number, limit = 100): Promise<AdjustmentRow[]> {
  if (adId) {
    const [rows] = await pool.query<AdjustmentRow[]>(
      `SELECT * FROM price_adjustments WHERE ad_id = :adId ORDER BY id DESC LIMIT :limit`,
      { adId, limit },
    );
    return rows;
  }
  const [rows] = await pool.query<AdjustmentRow[]>(
    `SELECT * FROM price_adjustments ORDER BY id DESC LIMIT :limit`,
    { limit },
  );
  return rows;
}