-- Migration 008: Tabela de disputas
-- Criado em: 2025-01-27

-- Tabela de disputas
CREATE TABLE IF NOT EXISTS disputes (
    dispute_id SERIAL PRIMARY KEY,
    transaction_id INTEGER NOT NULL REFERENCES transactions(transaction_id) ON DELETE CASCADE,
    raised_by_user_id INTEGER NOT NULL REFERENCES users(user_id),
    dispute_reason TEXT NOT NULL,
    dispute_status VARCHAR(20) DEFAULT 'open' CHECK (dispute_status IN ('open', 'investigating', 'resolved', 'closed')),
    resolution_notes TEXT,
    resolved_by_admin_id INTEGER REFERENCES users(user_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP
);

-- Índices para performance
CREATE INDEX IF NOT EXISTS idx_disputes_transaction ON disputes(transaction_id);
CREATE INDEX IF NOT EXISTS idx_disputes_raised_by ON disputes(raised_by_user_id);
CREATE INDEX IF NOT EXISTS idx_disputes_status ON disputes(dispute_status);