-- Migração: Criar tabela 'usuarios' para autenticação e perfis de usuário
-- Data: 2026-01-22
-- Descrição: Tabela principal de usuários TudoAqui com campos de verificação KYC

CREATE TABLE IF NOT EXISTS usuarios (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Informações básicas
  nome VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  telefone VARCHAR(15) NOT NULL UNIQUE,
  senha VARCHAR(255) NOT NULL,
  
  -- Tipo de usuário e status
  tipo_usuario ENUM('comprador', 'vendedor', 'prestador_servico') DEFAULT 'comprador',
  status ENUM('ativo', 'suspenso', 'deletado') DEFAULT 'ativo',
  
  -- Verificações de identidade (KYC)
  email_verificado BOOLEAN DEFAULT FALSE,
  telefone_verificado BOOLEAN DEFAULT FALSE,
  kyc_status ENUM('pendente', 'aprovado', 'rejeitado') DEFAULT 'pendente',
  
  -- Informações de perfil
  foto_url VARCHAR(500),
  bio TEXT,
  documento_numero VARCHAR(20) UNIQUE,
  
  -- Integração com Selo de Verificado
  selo_verificado BOOLEAN DEFAULT FALSE,
  data_verificacao TIMESTAMP,
  
  -- Rastreamento de acesso
  ultimo_acesso TIMESTAMP,
  ultimo_ip VARCHAR(45),
  ultimo_user_agent TEXT,
  
  -- Endereço e localização
  provincia VARCHAR(50),
  municipio VARCHAR(50),
  endereco TEXT,
  latitude DECIMAL(10, 8),
  longitude DECIMAL(11, 8),
  
  -- Timestamps
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  -- Constraints
  CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'),
  CONSTRAINT telefone_format CHECK (telefone ~* '^[0-9]{9,15}$')
);

-- Índices para performance
CREATE INDEX idx_usuarios_email ON usuarios(email);
CREATE INDEX idx_usuarios_telefone ON usuarios(telefone);
CREATE INDEX idx_usuarios_tipo_usuario ON usuarios(tipo_usuario);
CREATE INDEX idx_usuarios_status ON usuarios(status);
CREATE INDEX idx_usuarios_kyc_status ON usuarios(kyc_status);
CREATE INDEX idx_usuarios_selo_verificado ON usuarios(selo_verificado);
CREATE INDEX idx_usuarios_created_at ON usuarios(created_at DESC);
CREATE INDEX idx_usuarios_provincia_municipio ON usuarios(provincia, municipio);

-- Índice geoespacial para "perto de mim"
CREATE INDEX idx_usuarios_localizacao ON usuarios USING GIST(
  ll_to_earth(latitude, longitude)
);

-- Trigger para atualizar 'updated_at' automaticamente
CREATE TRIGGER atualizar_updated_at_usuarios
BEFORE UPDATE ON usuarios
FOR EACH ROW
EXECUTE FUNCTION atualizar_updated_at();

-- Tabela de auditoria (falhas de login, etc)
CREATE TABLE IF NOT EXISTS auditoria_usuarios (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  usuario_id UUID REFERENCES usuarios(id) ON DELETE CASCADE,
  
  acao VARCHAR(50) NOT NULL,
  detalhes JSONB,
  
  ip_address VARCHAR(45),
  user_agent TEXT,
  
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_auditoria_usuarios_usuario_id ON auditoria_usuarios(usuario_id);
CREATE INDEX idx_auditoria_usuarios_acao ON auditoria_usuarios(acao);
CREATE INDEX idx_auditoria_usuarios_created_at ON auditoria_usuarios(created_at DESC);

-- Tabela de verificação de telefone (2FA via SMS)
CREATE TABLE IF NOT EXISTS verificacao_telefone (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  telefone VARCHAR(15) NOT NULL,
  codigo VARCHAR(6) NOT NULL,
  tentativas INT DEFAULT 0,
  
  criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expira_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP + INTERVAL '10 minutes',
  
  CONSTRAINT codigo_format CHECK (codigo ~* '^[0-9]{6}$')
);

CREATE INDEX idx_verificacao_telefone_telefone ON verificacao_telefone(telefone);
CREATE INDEX idx_verificacao_telefone_expira_em ON verificacao_telefone(expira_em);

-- Função para atualizar updated_at automaticamente
CREATE OR REPLACE FUNCTION atualizar_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Comentários para documentação
COMMENT ON TABLE usuarios IS 'Tabela principal de usuários com suporte a KYC, verificação e geolocalização';
COMMENT ON COLUMN usuarios.kyc_status IS 'Status da verificação de identidade: pendente, aprovado, rejeitado';
COMMENT ON COLUMN usuarios.selo_verificado IS 'Indica se o usuário tem o Selo de Verificado (aprovação KYC + documento validado)';
COMMENT ON COLUMN usuarios.latitude IS 'Localização em graus decimais para busca "perto de mim"';
COMMENT ON COLUMN usuarios.longitude IS 'Localização em graus decimais para busca "perto de mim"';
