/**
 * Queries de listings (anúncios)
 * CRUD completo para a tabela listings
 */

import { query } from "../index";
import { itemExists } from "./items";

export interface Listing {
  listing_id: number;
  seller_id: number;
  item_id: number; // ID legado (SQLite) - mantido para compatibilidade
  item_name: string;
  quantity: number;
  server_name: string;
  price_pix: number | null;
  price_tibia_coins: number | null;
  price_gold_coins: number | null;
  is_auction: boolean;
  current_bid_amount: number | null;
  current_bidder_id: number | null;
  auction_ends_at: Date | null;
  description: string | null;
  listing_status: "active" | "sold" | "cancelled" | "in_escrow" | "expired";
  created_at: Date;
  updated_at: Date;
  sold_at: Date | null;
  views_count: number;
  // Campos adicionados na migração 012
  is_featured?: boolean;
  favorites_count?: number;
  inquiries_count?: number;
  price_history?: any; // JSONB
  expires_at?: Date | null;
  // Campos adicionados na migração 013 (troca por item)
  trade_item_id?: number | null;
  trade_item_name?: string | null;
  trade_item_quantity?: number | null;
  // Campos adicionados na migração 014 (leilões)
  min_bid_increment?: number | null;
  initial_price_pix?: number | null;
  initial_price_tibia_coins?: number | null;
  initial_price_gold_coins?: number | null;
  // Campos do JOIN com items (unificado)
  item_id_unified?: number | null; // ID unificado da tabela items
  image_local_path?: string | null;
  image_url?: string | null;
  item_category?: string | null;
  // Campo calculado (não vem do banco diretamente)
  seller_character_name?: string | null;
}

export interface CreateListingData {
  seller_id: number;
  item_id?: number; // ID legado (SQLite) - opcional, mantido para compatibilidade
  item_id_unified?: number; // ID unificado da tabela items - preferido
  item_name: string;
  quantity: number;
  server_name: string;
  price_pix?: number;
  price_tibia_coins?: number;
  price_gold_coins?: number;
  is_auction?: boolean;
  auction_ends_at?: Date;
  description?: string;
  trade_item_id?: number | null;
  trade_item_name?: string | null;
  trade_item_quantity?: number | null;
  min_bid_increment?: number;
}

export interface UpdateListingData {
  quantity?: number;
  price_pix?: number;
  price_tibia_coins?: number;
  price_gold_coins?: number;
  description?: string;
  listing_status?: Listing["listing_status"];
  current_bid_amount?: number;
  current_bidder_id?: number;
}

export interface ListingFilters {
  status?: Listing["listing_status"];
  server_name?: string;
  item_id?: number;
  seller_id?: number;
  is_auction?: boolean;
  search?: string;
  page?: number;
  pageSize?: number;
  sortBy?:
    | "created_at"
    | "price_pix"
    | "price_tibia_coins"
    | "price_gold_coins"
    | "views_count";
  sortOrder?: "ASC" | "DESC";
}

/**
 * Cria um novo anúncio
 */
export async function createListing(data: CreateListingData): Promise<Listing> {
  // Validar item_id_unified se fornecido
  if (data.item_id_unified) {
    const exists = await itemExists(data.item_id_unified);
    if (!exists) {
      throw new Error(
        `Item com ID unificado ${data.item_id_unified} não encontrado na tabela items`,
      );
    }
  }

  // Calcular incremento mínimo se for leilão e não foi fornecido
  let minBidIncrement = data.min_bid_increment;
  if (data.is_auction && !minBidIncrement) {
    // 5% do preço inicial ou mínimo de R$ 10,00
    const initialPrice = data.price_pix || 0;
    minBidIncrement = Math.max(initialPrice * 0.05, 10.0);
  }

  // Usar item_id_unified se fornecido, senão usar item_id legado
  const itemIdUnified = data.item_id_unified || null;
  const itemIdLegacy = data.item_id || null;

  let result;
  try {
    // Tentar INSERT com todas as colunas (se migração 014 foi executada)
    result = await query<Listing>(
      `
      INSERT INTO listings (
        seller_id, item_id, item_id_unified, item_name, quantity, server_name,
        price_pix, price_tibia_coins, price_gold_coins,
        is_auction, auction_ends_at, description,
        trade_item_id, trade_item_name, trade_item_quantity,
        initial_price_pix, initial_price_tibia_coins, initial_price_gold_coins,
        min_bid_increment
      )
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19)
      RETURNING 
        listing_id, seller_id, item_id, item_name, quantity, server_name,
        price_pix, price_tibia_coins, price_gold_coins,
        is_auction, current_bid_amount, current_bidder_id, auction_ends_at,
        description, listing_status, created_at, updated_at, sold_at, views_count,
        COALESCE(is_featured, false) as is_featured,
        COALESCE(favorites_count, 0) as favorites_count,
        COALESCE(inquiries_count, 0) as inquiries_count,
        COALESCE(price_history, '[]'::jsonb) as price_history,
        expires_at,
        trade_item_id, trade_item_name, trade_item_quantity,
        min_bid_increment, initial_price_pix, initial_price_tibia_coins, initial_price_gold_coins
      `,
      [
        data.seller_id,
        itemIdLegacy,
        itemIdUnified,
        data.item_name,
        data.quantity,
        data.server_name,
        data.price_pix || null,
        data.price_tibia_coins || null,
        data.price_gold_coins || null,
        data.is_auction || false,
        data.auction_ends_at || null,
        data.description || null,
        data.trade_item_id || null,
        data.trade_item_name || null,
        data.trade_item_quantity || null,
        data.is_auction ? data.price_pix || null : null, // initial_price_pix
        data.is_auction ? data.price_tibia_coins || null : null, // initial_price_tibia_coins
        data.is_auction ? data.price_gold_coins || null : null, // initial_price_gold_coins
        minBidIncrement || null,
      ],
    );
  } catch (error) {
    // Se as colunas da migração 014 ou item_id_unified não existirem, usar INSERT sem elas
    if (error instanceof Error && error.message.includes("does not exist")) {
      try {
        // Tentar com item_id_unified mas sem colunas de migração 014
        result = await query<Listing>(
          `
          INSERT INTO listings (
            seller_id, item_id, item_id_unified, item_name, quantity, server_name,
            price_pix, price_tibia_coins, price_gold_coins,
            is_auction, auction_ends_at, description,
            trade_item_id, trade_item_name, trade_item_quantity
          )
          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
          RETURNING 
            listing_id, seller_id, item_id, item_name, quantity, server_name,
            price_pix, price_tibia_coins, price_gold_coins,
            is_auction, current_bid_amount, current_bidder_id, auction_ends_at,
            description, listing_status, created_at, updated_at, sold_at, views_count,
            COALESCE(is_featured, false) as is_featured,
            COALESCE(favorites_count, 0) as favorites_count,
            COALESCE(inquiries_count, 0) as inquiries_count,
            COALESCE(price_history, '[]'::jsonb) as price_history,
            expires_at,
            trade_item_id, trade_item_name, trade_item_quantity
          `,
          [
            data.seller_id,
            itemIdLegacy,
            itemIdUnified,
            data.item_name,
            data.quantity,
            data.server_name,
            data.price_pix || null,
            data.price_tibia_coins || null,
            data.price_gold_coins || null,
            data.is_auction || false,
            data.auction_ends_at || null,
            data.description || null,
            data.trade_item_id || null,
            data.trade_item_name || null,
            data.trade_item_quantity || null,
          ],
        );
      } catch (error2) {
        // Se item_id_unified também não existir, usar apenas item_id legado
        if (
          error2 instanceof Error &&
          error2.message.includes("does not exist")
        ) {
          result = await query<Listing>(
            `
            INSERT INTO listings (
              seller_id, item_id, item_name, quantity, server_name,
              price_pix, price_tibia_coins, price_gold_coins,
              is_auction, auction_ends_at, description,
              trade_item_id, trade_item_name, trade_item_quantity
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
            RETURNING 
              listing_id, seller_id, item_id, item_name, quantity, server_name,
              price_pix, price_tibia_coins, price_gold_coins,
              is_auction, current_bid_amount, current_bidder_id, auction_ends_at,
              description, listing_status, created_at, updated_at, sold_at, views_count,
              COALESCE(is_featured, false) as is_featured,
              COALESCE(favorites_count, 0) as favorites_count,
              COALESCE(inquiries_count, 0) as inquiries_count,
              COALESCE(price_history, '[]'::jsonb) as price_history,
              expires_at,
              trade_item_id, trade_item_name, trade_item_quantity
            `,
            [
              data.seller_id,
              itemIdLegacy,
              data.item_name,
              data.quantity,
              data.server_name,
              data.price_pix || null,
              data.price_tibia_coins || null,
              data.price_gold_coins || null,
              data.is_auction || false,
              data.auction_ends_at || null,
              data.description || null,
              data.trade_item_id || null,
              data.trade_item_name || null,
              data.trade_item_quantity || null,
            ],
          );
        } else {
          throw error2;
        }
      }
    } else {
      throw error;
    }
  }

  return result.rows[0];
}

/**
 * Busca anúncio por ID
 */
export async function getListingById(
  listingId: number,
): Promise<Listing | null> {
  let result;
  try {
    // Tentar query com todas as colunas (se migração 014 foi executada)
    result = await query<Listing & { seller_character_name: string }>(
      `
      SELECT 
        l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
        l.price_pix, l.price_tibia_coins, l.price_gold_coins,
        l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
        l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
        COALESCE(l.is_featured, false) as is_featured,
        COALESCE(l.favorites_count, 0) as favorites_count,
        COALESCE(l.inquiries_count, 0) as inquiries_count,
        COALESCE(l.price_history, '[]'::jsonb) as price_history,
        l.expires_at,
        l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
        l.min_bid_increment, l.initial_price_pix, l.initial_price_tibia_coins, l.initial_price_gold_coins,
        i.item_id as item_id_unified,
        i.image_local_path,
        i.image_url,
        i.category as item_category,
        uc.character_name as seller_character_name
      FROM listings l
      LEFT JOIN items i ON i.item_id = l.item_id_unified
      LEFT JOIN user_characters uc ON uc.user_id = l.seller_id 
        AND uc.server_name = l.server_name 
        AND uc.is_main = TRUE
      WHERE l.listing_id = $1
      `,
      [listingId],
    );
  } catch (error) {
    // Se as colunas da migração 014 não existirem, usar query sem elas
    if (error instanceof Error && error.message.includes("does not exist")) {
      result = await query<Listing & { seller_character_name: string }>(
        `
        SELECT 
          l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
          l.price_pix, l.price_tibia_coins, l.price_gold_coins,
          l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
          l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
          COALESCE(l.is_featured, false) as is_featured,
          COALESCE(l.favorites_count, 0) as favorites_count,
          COALESCE(l.inquiries_count, 0) as inquiries_count,
          COALESCE(l.price_history, '[]'::jsonb) as price_history,
          l.expires_at,
          l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
          NULL::numeric as min_bid_increment,
          NULL::numeric as initial_price_pix,
          NULL::integer as initial_price_tibia_coins,
          NULL::bigint as initial_price_gold_coins,
          i.item_id as item_id_unified,
          i.image_local_path,
          i.image_url,
          i.category as item_category,
          uc.character_name as seller_character_name
        FROM listings l
        LEFT JOIN items i ON i.item_id = l.item_id_unified
        LEFT JOIN user_characters uc ON uc.user_id = l.seller_id 
          AND uc.server_name = l.server_name 
          AND uc.is_main = TRUE
        WHERE l.listing_id = $1
        `,
        [listingId],
      );
    } else {
      throw error;
    }
  }

  if (result.rows.length === 0) {
    return null;
  }

  const row = result.rows[0];
  return {
    ...row,
    seller_character_name: row.seller_character_name || null,
  };
}

/**
 * Lista anúncios com filtros e paginação
 */
export async function getListings(
  filters: ListingFilters = {},
): Promise<{ listings: Listing[]; total: number }> {
  const {
    status,
    server_name,
    item_id,
    seller_id,
    is_auction,
    search,
    page = 1,
    pageSize = 20,
    sortBy = "created_at",
    sortOrder = "DESC",
  } = filters;

  const conditions: string[] = [];
  const params: any[] = [];
  let paramIndex = 1;

  if (status) {
    conditions.push(`l.listing_status = $${paramIndex}`);
    params.push(status);
    paramIndex++;
  }

  if (server_name) {
    conditions.push(`l.server_name = $${paramIndex}`);
    params.push(server_name);
    paramIndex++;
  }

  if (item_id) {
    conditions.push(`l.item_id = $${paramIndex}`);
    params.push(item_id);
    paramIndex++;
  }

  if (seller_id) {
    conditions.push(`l.seller_id = $${paramIndex}`);
    params.push(seller_id);
    paramIndex++;
  }

  if (is_auction !== undefined) {
    conditions.push(`l.is_auction = $${paramIndex}`);
    params.push(is_auction);
    paramIndex++;
  }

  if (search) {
    conditions.push(
      `(l.item_name ILIKE $${paramIndex} OR l.description ILIKE $${paramIndex})`,
    );
    params.push(`%${search}%`);
    paramIndex++;
  }

  const whereClause =
    conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "WHERE 1=1";

  // Contar total
  const countResult = await query<{ count: string }>(
    `SELECT COUNT(*) as count FROM listings l ${whereClause}`,
    params,
  );
  const total = parseInt(countResult.rows[0].count, 10);

  // Validar sortBy - garantir que seja uma coluna válida
  const validSortColumns = [
    "created_at",
    "price_pix",
    "price_tibia_coins",
    "price_gold_coins",
    "views_count",
  ];
  const sortColumn = validSortColumns.includes(sortBy) ? sortBy : "created_at";
  const sortDirection = sortOrder === "ASC" ? "ASC" : "DESC";

  // Mapear colunas para usar alias 'l.' quando necessário
  const sortColumnWithAlias = `l.${sortColumn}`;

  // Buscar listings
  const offset = (page - 1) * pageSize;

  // Incrementar paramIndex para os parâmetros de LIMIT e OFFSET
  const limitParamIndex = paramIndex;
  const offsetParamIndex = paramIndex + 1;
  const finalParams = [...params, pageSize, offset];

  // Tentar query com todas as colunas (se migração 014 foi executada)
  // Se falhar, usar query sem as novas colunas
  let result;
  try {
    const querySql = `
      SELECT 
        l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
        l.price_pix, l.price_tibia_coins, l.price_gold_coins,
        l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
        l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
        COALESCE(l.is_featured, false) as is_featured,
        COALESCE(l.favorites_count, 0) as favorites_count,
        COALESCE(l.inquiries_count, 0) as inquiries_count,
        COALESCE(l.price_history, '[]'::jsonb) as price_history,
        l.expires_at,
        l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
        l.min_bid_increment, l.initial_price_pix, l.initial_price_tibia_coins, l.initial_price_gold_coins,
        i.item_id as item_id_unified,
        i.image_local_path,
        i.image_url,
        i.category as item_category,
        uc.character_name as seller_character_name
      FROM listings l
      LEFT JOIN items i ON i.item_id = l.item_id_unified
      LEFT JOIN user_characters uc ON uc.user_id = l.seller_id 
        AND uc.server_name = l.server_name 
        AND uc.is_main = TRUE
      ${whereClause}
      ORDER BY ${sortColumnWithAlias} ${sortDirection}
      LIMIT $${limitParamIndex} OFFSET $${offsetParamIndex}
    `;

    result = await query<Listing & { seller_character_name: string }>(
      querySql,
      finalParams,
    );
  } catch (error) {
    // Se as colunas da migração 014 não existirem, usar query sem elas
    if (error instanceof Error && error.message.includes("does not exist")) {
      const fallbackQuerySql = `
        SELECT 
          l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
          l.price_pix, l.price_tibia_coins, l.price_gold_coins,
          l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
          l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
          COALESCE(l.is_featured, false) as is_featured,
          COALESCE(l.favorites_count, 0) as favorites_count,
          COALESCE(l.inquiries_count, 0) as inquiries_count,
          COALESCE(l.price_history, '[]'::jsonb) as price_history,
          l.expires_at,
          l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
          NULL::numeric as min_bid_increment,
          NULL::numeric as initial_price_pix,
          NULL::integer as initial_price_tibia_coins,
          NULL::bigint as initial_price_gold_coins,
          i.item_id as item_id_unified,
          i.image_local_path,
          i.image_url,
          i.category as item_category,
          uc.character_name as seller_character_name
        FROM listings l
        LEFT JOIN items i ON i.item_id = l.item_id_unified
        LEFT JOIN user_characters uc ON uc.user_id = l.seller_id 
          AND uc.server_name = l.server_name 
          AND uc.is_main = TRUE
        ${whereClause}
        ORDER BY ${sortColumnWithAlias} ${sortDirection}
        LIMIT $${limitParamIndex} OFFSET $${offsetParamIndex}
      `;

      result = await query<Listing & { seller_character_name: string }>(
        fallbackQuerySql,
        finalParams,
      );
    } else {
      throw error;
    }
  }

  // Mapear resultados para garantir que seller_character_name seja null se não existir
  const listings = result.rows.map((row) => ({
    ...row,
    seller_character_name: row.seller_character_name || null,
  }));

  return {
    listings,
    total,
  };
}

/**
 * Atualiza um anúncio
 */
export async function updateListing(
  listingId: number,
  data: UpdateListingData,
): Promise<Listing | null> {
  const updates: string[] = [];
  const params: any[] = [];
  let paramIndex = 1;

  if (data.quantity !== undefined) {
    updates.push(`quantity = $${paramIndex}`);
    params.push(data.quantity);
    paramIndex++;
  }

  if (data.price_pix !== undefined) {
    updates.push(`price_pix = $${paramIndex}`);
    params.push(data.price_pix);
    paramIndex++;
  }

  if (data.price_tibia_coins !== undefined) {
    updates.push(`price_tibia_coins = $${paramIndex}`);
    params.push(data.price_tibia_coins);
    paramIndex++;
  }

  if (data.price_gold_coins !== undefined) {
    updates.push(`price_gold_coins = $${paramIndex}`);
    params.push(data.price_gold_coins);
    paramIndex++;
  }

  if (data.description !== undefined) {
    updates.push(`description = $${paramIndex}`);
    params.push(data.description);
    paramIndex++;
  }

  if (data.listing_status !== undefined) {
    updates.push(`listing_status = $${paramIndex}`);
    params.push(data.listing_status);
    paramIndex++;
  }

  if (data.current_bid_amount !== undefined) {
    updates.push(`current_bid_amount = $${paramIndex}`);
    params.push(data.current_bid_amount);
    paramIndex++;
  }

  if (data.current_bidder_id !== undefined) {
    updates.push(`current_bidder_id = $${paramIndex}`);
    params.push(data.current_bidder_id);
    paramIndex++;
  }

  if (updates.length === 0) {
    return getListingById(listingId);
  }

  params.push(listingId);

  let result;
  try {
    // Tentar query com todas as colunas (se migração 014 foi executada)
    result = await query<Listing & { seller_character_name: string }>(
      `
      UPDATE listings l
      SET ${updates.join(", ")}
      WHERE l.listing_id = $${paramIndex}
      RETURNING 
        l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
        l.price_pix, l.price_tibia_coins, l.price_gold_coins,
        l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
        l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
        COALESCE(l.is_featured, false) as is_featured,
        COALESCE(l.favorites_count, 0) as favorites_count,
        COALESCE(l.inquiries_count, 0) as inquiries_count,
        COALESCE(l.price_history, '[]'::jsonb) as price_history,
        l.expires_at,
        l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
        l.min_bid_increment, l.initial_price_pix, l.initial_price_tibia_coins, l.initial_price_gold_coins
      `,
      params,
    );

    // Buscar dados do JOIN após o UPDATE
    if (result.rows.length > 0) {
      const listingId = result.rows[0].listing_id;
      const joinResult = await query<
        Listing & { seller_character_name: string }
      >(
        `
        SELECT 
          l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
          l.price_pix, l.price_tibia_coins, l.price_gold_coins,
          l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
          l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
          COALESCE(l.is_featured, false) as is_featured,
          COALESCE(l.favorites_count, 0) as favorites_count,
          COALESCE(l.inquiries_count, 0) as inquiries_count,
          COALESCE(l.price_history, '[]'::jsonb) as price_history,
          l.expires_at,
          l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
          l.min_bid_increment, l.initial_price_pix, l.initial_price_tibia_coins, l.initial_price_gold_coins,
          i.item_id as item_id_unified,
          i.image_local_path,
          i.image_url,
          i.category as item_category,
          uc.character_name as seller_character_name
        FROM listings l
        LEFT JOIN items i ON i.item_id = l.item_id_unified
        LEFT JOIN user_characters uc ON uc.user_id = l.seller_id 
          AND uc.server_name = l.server_name 
          AND uc.is_main = TRUE
        WHERE l.listing_id = $1
        `,
        [listingId],
      );

      if (joinResult.rows.length > 0) {
        result.rows[0] = joinResult.rows[0];
      }
    }
  } catch (error) {
    // Se as colunas da migração 014 não existirem, usar query sem elas
    if (error instanceof Error && error.message.includes("does not exist")) {
      result = await query<Listing & { seller_character_name: string }>(
        `
        UPDATE listings l
        SET ${updates.join(", ")}
        WHERE l.listing_id = $${paramIndex}
        RETURNING 
          l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
          l.price_pix, l.price_tibia_coins, l.price_gold_coins,
          l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
          l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
          COALESCE(l.is_featured, false) as is_featured,
          COALESCE(l.favorites_count, 0) as favorites_count,
          COALESCE(l.inquiries_count, 0) as inquiries_count,
          COALESCE(l.price_history, '[]'::jsonb) as price_history,
          l.expires_at,
          l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
          NULL::numeric as min_bid_increment,
          NULL::numeric as initial_price_pix,
          NULL::integer as initial_price_tibia_coins,
          NULL::bigint as initial_price_gold_coins
        `,
        params,
      );

      // Buscar dados do JOIN após o UPDATE
      if (result.rows.length > 0) {
        const listingId = result.rows[0].listing_id;
        const joinResult = await query<
          Listing & { seller_character_name: string }
        >(
          `
          SELECT 
            l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
            l.price_pix, l.price_tibia_coins, l.price_gold_coins,
            l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
            l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
            COALESCE(l.is_featured, false) as is_featured,
            COALESCE(l.favorites_count, 0) as favorites_count,
            COALESCE(l.inquiries_count, 0) as inquiries_count,
            COALESCE(l.price_history, '[]'::jsonb) as price_history,
            l.expires_at,
            l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
            NULL::numeric as min_bid_increment,
            NULL::numeric as initial_price_pix,
            NULL::integer as initial_price_tibia_coins,
            NULL::bigint as initial_price_gold_coins,
            i.item_id as item_id_unified,
            i.image_local_path,
            i.image_url,
            i.category as item_category,
            uc.character_name as seller_character_name
          FROM listings l
          LEFT JOIN items i ON i.item_id = l.item_id_unified
          LEFT JOIN user_characters uc ON uc.user_id = l.seller_id 
            AND uc.server_name = l.server_name 
            AND uc.is_main = TRUE
          WHERE l.listing_id = $1
          `,
          [listingId],
        );

        if (joinResult.rows.length > 0) {
          result.rows[0] = joinResult.rows[0];
        }
      }
    } else {
      throw error;
    }
  }

  if (result.rows.length === 0) {
    return null;
  }

  const row = result.rows[0];
  return {
    ...row,
    seller_character_name: row.seller_character_name || null,
  };
}

/**
 * Cancela um anúncio (soft delete - muda status)
 */
export async function deleteListing(listingId: number): Promise<boolean> {
  const result = await query(
    `
    UPDATE listings
    SET listing_status = 'cancelled'
    WHERE listing_id = $1
    `,
    [listingId],
  );

  return (result.rowCount ?? 0) > 0;
}

/**
 * Incrementa contador de views
 */
export async function incrementViews(listingId: number): Promise<void> {
  await query(
    `
    UPDATE listings
    SET views_count = views_count + 1
    WHERE listing_id = $1
    `,
    [listingId],
  );
}

/**
 * Busca anúncios de um vendedor
 */
export async function getListingsBySeller(
  sellerId: number,
  filters: {
    status?: Listing["listing_status"];
    page?: number;
    pageSize?: number;
  } = {},
): Promise<{ listings: Listing[]; total: number }> {
  return getListings({
    seller_id: sellerId,
    ...filters,
  });
}

/**
 * Busca apenas anúncios ativos
 */
export async function getActiveListings(
  filters: Omit<ListingFilters, "status"> = {},
): Promise<{ listings: Listing[]; total: number }> {
  return getListings({
    ...filters,
    status: "active",
  });
}

/**
 * Coloca um lance em um leilão
 * Valida se o lance é maior que o lance atual + incremento mínimo
 */
export async function placeBid(
  listingId: number,
  bidderId: number,
  bidAmount: number,
  _paymentMethod: "pix" | "tc" | "gp",
): Promise<{
  success: boolean;
  listing: Listing | null;
  previousBidderId: number | null;
  error?: string;
}> {
  const { getPool } = await import("../index");
  const pool = getPool();
  const client = await pool.connect();

  try {
    await client.query("BEGIN");

    // Buscar listing atual com JOIN
    const listingResult = await client.query<Listing>(
      `
      SELECT 
        l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
        l.price_pix, l.price_tibia_coins, l.price_gold_coins,
        l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
        l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
        COALESCE(l.is_featured, false) as is_featured,
        COALESCE(l.favorites_count, 0) as favorites_count,
        COALESCE(l.inquiries_count, 0) as inquiries_count,
        COALESCE(l.price_history, '[]'::jsonb) as price_history,
        l.expires_at,
        l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
        l.min_bid_increment, l.initial_price_pix, l.initial_price_tibia_coins, l.initial_price_gold_coins,
        i.item_id as item_id_unified,
        i.image_local_path,
        i.image_url,
        i.category as item_category
      FROM listings l
      LEFT JOIN items i ON i.item_id = l.item_id_unified
      WHERE l.listing_id = $1
      `,
      [listingId],
    );

    if (listingResult.rows.length === 0) {
      await client.query("ROLLBACK");
      return {
        success: false,
        listing: null,
        previousBidderId: null,
        error: "Anúncio não encontrado",
      };
    }

    const listing = listingResult.rows[0];

    // Validações
    if (listing.listing_status !== "active") {
      await client.query("ROLLBACK");
      return {
        success: false,
        listing: null,
        previousBidderId: null,
        error: "Anúncio não está ativo",
      };
    }

    if (!listing.is_auction) {
      await client.query("ROLLBACK");
      return {
        success: false,
        listing: null,
        previousBidderId: null,
        error: "Este anúncio não é um leilão",
      };
    }

    if (listing.seller_id === bidderId) {
      await client.query("ROLLBACK");
      return {
        success: false,
        listing: null,
        previousBidderId: null,
        error: "Você não pode dar lance no seu próprio anúncio",
      };
    }

    if (
      listing.auction_ends_at &&
      new Date(listing.auction_ends_at) < new Date()
    ) {
      await client.query("ROLLBACK");
      return {
        success: false,
        listing: null,
        previousBidderId: null,
        error: "Leilão expirou",
      };
    }

    // Calcular lance mínimo
    const currentBid = listing.current_bid_amount || 0;
    const minIncrement = listing.min_bid_increment || 10; // Padrão de R$ 10,00
    const minBid = currentBid + minIncrement;

    if (bidAmount < minBid) {
      await client.query("ROLLBACK");
      return {
        success: false,
        listing: null,
        previousBidderId: listing.current_bidder_id,
        error: `Lance mínimo é ${minBid.toFixed(2)} (lance atual: ${currentBid.toFixed(2)} + incremento: ${minIncrement.toFixed(2)})`,
      };
    }

    // Guardar ID do lanceador anterior
    const previousBidderId = listing.current_bidder_id;

    // Atualizar listing com novo lance
    await client.query(
      `
      UPDATE listings
      SET 
        current_bid_amount = $1,
        current_bidder_id = $2,
        updated_at = CURRENT_TIMESTAMP
      WHERE listing_id = $3
      `,
      [bidAmount, bidderId, listingId],
    );

    // Buscar listing atualizado com JOIN
    const updateResult = await client.query<Listing>(
      `
      SELECT 
        l.listing_id, l.seller_id, l.item_id, l.item_name, l.quantity, l.server_name,
        l.price_pix, l.price_tibia_coins, l.price_gold_coins,
        l.is_auction, l.current_bid_amount, l.current_bidder_id, l.auction_ends_at,
        l.description, l.listing_status, l.created_at, l.updated_at, l.sold_at, l.views_count,
        COALESCE(l.is_featured, false) as is_featured,
        COALESCE(l.favorites_count, 0) as favorites_count,
        COALESCE(l.inquiries_count, 0) as inquiries_count,
        COALESCE(l.price_history, '[]'::jsonb) as price_history,
        l.expires_at,
        l.trade_item_id, l.trade_item_name, l.trade_item_quantity,
        l.min_bid_increment, l.initial_price_pix, l.initial_price_tibia_coins, l.initial_price_gold_coins,
        i.item_id as item_id_unified,
        i.image_local_path,
        i.image_url,
        i.category as item_category
      FROM listings l
      LEFT JOIN items i ON i.item_id = l.item_id_unified
      WHERE l.listing_id = $1
      `,
      [listingId],
    );

    await client.query("COMMIT");

    return {
      success: true,
      listing: updateResult.rows[0],
      previousBidderId,
    };
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}