-- Migration 015: Sistema de Intenções de Compra
-- Criado em: 2025-01-29

CREATE TABLE IF NOT EXISTS purchase_intentions (
  intention_id SERIAL PRIMARY KEY,
  listing_id INTEGER NOT NULL REFERENCES listings(listing_id) ON DELETE CASCADE,
  buyer_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  payment_method VARCHAR(20) NOT NULL CHECK (payment_method IN ('pix', 'tibia_coins', 'gold_coins', 'trade')),
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'accepted', 'rejected', 'expired', 'completed')),
  buyer_message TEXT,
  seller_response TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at TIMESTAMP,
  accepted_at TIMESTAMP,
  rejected_at TIMESTAMP
);

-- Índices para performance
CREATE INDEX IF NOT EXISTS idx_purchase_intentions_listing_id ON purchase_intentions(listing_id);
CREATE INDEX IF NOT EXISTS idx_purchase_intentions_buyer_id ON purchase_intentions(buyer_id);
CREATE INDEX IF NOT EXISTS idx_purchase_intentions_status ON purchase_intentions(status);
CREATE INDEX IF NOT EXISTS idx_purchase_intentions_created_at ON purchase_intentions(created_at DESC);

-- Constraint: apenas uma intenção pendente por listing+buyer
CREATE UNIQUE INDEX IF NOT EXISTS idx_purchase_intentions_unique_pending 
ON purchase_intentions(listing_id, buyer_id) 
WHERE status = 'pending';

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

CREATE TRIGGER trigger_update_purchase_intentions_updated_at
  BEFORE UPDATE ON purchase_intentions
  FOR EACH ROW
  EXECUTE FUNCTION update_purchase_intentions_updated_at();

-- Comentários para documentação
COMMENT ON TABLE purchase_intentions IS 'Intenções de compra de anúncios - permite múltiplos compradores interessados';
COMMENT ON COLUMN purchase_intentions.status IS 'pending: aguardando resposta do vendedor, accepted: aceita, rejected: rejeitada, expired: expirada, completed: concluída';