-- Migration 006: Tabela de transações do sistema de escrow
-- Criado em: 2025-01-27

-- Tabela de transações
CREATE TABLE IF NOT EXISTS transactions (
    transaction_id SERIAL PRIMARY KEY,
    listing_id INTEGER REFERENCES listings(listing_id),
    buyer_id INTEGER NOT NULL REFERENCES users(user_id),
    seller_id INTEGER NOT NULL REFERENCES users(user_id),
    
    -- Detalhes do item
    item_name VARCHAR(255) NOT NULL,
    quantity INTEGER NOT NULL,
    server_name VARCHAR(50) NOT NULL,
    
    -- Valores
    amount DECIMAL(10, 2) NOT NULL,
    payment_method VARCHAR(20) CHECK (payment_method IN ('pix', 'tibia_coins', 'gold_coins')),
    escrow_fee DECIMAL(10, 2), -- 2% da transação
    
    -- Status da transação
    transaction_status VARCHAR(30) DEFAULT 'pending' CHECK (transaction_status IN (
        'pending',           -- aguardando depósito
        'deposited',         -- comprador depositou
        'item_delivered',    -- vendedor entregou
        'completed',         -- pagamento liberado
        'disputed',          -- em disputa
        'cancelled',         -- cancelada
        'refunded'           -- reembolsada
    )),
    
    -- Datas importantes
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deposited_at TIMESTAMP,
    delivered_at TIMESTAMP,
    completed_at TIMESTAMP,
    
    -- Confirmações
    buyer_confirmed BOOLEAN DEFAULT FALSE,
    seller_confirmed BOOLEAN DEFAULT FALSE,
    
    -- Notas
    buyer_notes TEXT,
    seller_notes TEXT
);

-- Índices para performance
CREATE INDEX IF NOT EXISTS idx_transactions_buyer ON transactions(buyer_id);
CREATE INDEX IF NOT EXISTS idx_transactions_seller ON transactions(seller_id);
CREATE INDEX IF NOT EXISTS idx_transactions_status ON transactions(transaction_status);
CREATE INDEX IF NOT EXISTS idx_transactions_listing ON transactions(listing_id);
CREATE INDEX IF NOT EXISTS idx_transactions_created_at ON transactions(created_at DESC);