-- Migration 004: Tabela de anúncios (listings) do marketplace
-- Criado em: 2025-01-27

-- Tabela de anúncios
CREATE TABLE IF NOT EXISTS listings (
    listing_id SERIAL PRIMARY KEY,
    seller_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    item_id INTEGER NOT NULL, -- referência ao banco de itens do TibiaWiki
    item_name VARCHAR(255) NOT NULL,
    quantity INTEGER DEFAULT 1 CHECK (quantity > 0),
    server_name VARCHAR(50) NOT NULL,
    
    -- Preços aceitos
    price_pix DECIMAL(10, 2),
    price_tibia_coins INTEGER,
    price_gold_coins BIGINT,
    
    -- Leilão
    is_auction BOOLEAN DEFAULT FALSE,
    current_bid_amount DECIMAL(10, 2),
    current_bidder_id INTEGER REFERENCES users(user_id),
    auction_ends_at TIMESTAMP,
    
    -- Metadados
    description TEXT,
    listing_status VARCHAR(20) DEFAULT 'active' CHECK (listing_status IN ('active', 'sold', 'cancelled', 'in_escrow', 'expired')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    sold_at TIMESTAMP,
    views_count INTEGER DEFAULT 0
);

-- Índices para performance
CREATE INDEX IF NOT EXISTS idx_listings_status ON listings(listing_status);
CREATE INDEX IF NOT EXISTS idx_listings_item_id ON listings(item_id);
CREATE INDEX IF NOT EXISTS idx_listings_seller_id ON listings(seller_id);
CREATE INDEX IF NOT EXISTS idx_listings_server ON listings(server_name);
CREATE INDEX IF NOT EXISTS idx_listings_created_at ON listings(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_listings_auction ON listings(is_auction, auction_ends_at) WHERE is_auction = TRUE;

-- Trigger para atualizar updated_at automaticamente
CREATE OR REPLACE FUNCTION update_listings_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_listings_updated_at
  BEFORE UPDATE ON listings
  FOR EACH ROW
  EXECUTE FUNCTION update_listings_updated_at();