-- Migration 002: Tabela de servidores do Tibia
-- Criado em: 2025-01-27

-- Tornar CPF opcional na tabela users (para compatibilidade)
ALTER TABLE users ALTER COLUMN cpf DROP NOT NULL;

-- Tabela de servidores
CREATE TABLE IF NOT EXISTS servers (
  server_id SERIAL PRIMARY KEY,
  server_name VARCHAR(50) UNIQUE NOT NULL,
  server_type VARCHAR(20) CHECK (server_type IN ('pvp', 'no-pvp', 'retro-pvp', 'hardcore-pvp')),
  location VARCHAR(10) CHECK (location IN ('NA', 'SA', 'EU', 'OC')),
  is_active BOOLEAN DEFAULT TRUE,
  
  -- Estatísticas de tracking
  total_listings INTEGER DEFAULT 0,
  total_users INTEGER DEFAULT 0,
  total_subscribers INTEGER DEFAULT 0,
  total_transactions INTEGER DEFAULT 0,
  total_volume DECIMAL(15, 2) DEFAULT 0,
  average_listing_price DECIMAL(10, 2) DEFAULT 0,
  last_activity_at TIMESTAMP,
  
  -- Metadados
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Índices para performance
CREATE INDEX IF NOT EXISTS idx_servers_name ON servers(server_name);
CREATE INDEX IF NOT EXISTS idx_servers_active ON servers(is_active);
CREATE INDEX IF NOT EXISTS idx_servers_type ON servers(server_type);

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

CREATE TRIGGER trigger_update_servers_updated_at
  BEFORE UPDATE ON servers
  FOR EACH ROW
  EXECUTE FUNCTION update_servers_updated_at();