pagodera/lib/db/migrations/016_create_unified_items_table.sql

-- Migration 016: Tabela Unificada de Itens
-- Criado em: 2025-01-29
-- Objetivo: Unificar todos os itens de todas as categorias do SQLite em uma única tabela
--           com IDs únicos e consistentes, permitindo JOINs confiáveis

CREATE TABLE IF NOT EXISTS items (
  item_id SERIAL PRIMARY KEY,
  -- ID original da tabela de origem (para referência)
  source_table_id INTEGER NOT NULL,
  -- Nome da tabela de origem (espadas, armaduras, etc)
  source_table_name VARCHAR(50) NOT NULL,
  -- Nome do item (normalizado)
  item_name VARCHAR(255) NOT NULL,
  -- Categoria (normalizada para inglês)
  category VARCHAR(50) NOT NULL,
  -- Caminho da imagem local (se houver)
  image_local_path TEXT,
  -- URL da imagem externa (se houver)
  image_url TEXT,
  -- Raridade
  rarity VARCHAR(20),
  -- Nível (se aplicável)
  level INTEGER,
  -- Peso (se aplicável)
  weight DECIMAL(10, 2),
  -- Slots (se aplicável)
  slots INTEGER,
  -- Skill para usar (se aplicável)
  skill VARCHAR(50),
  -- Dados adicionais em JSON (para campos extras que variam por categoria)
  metadata JSONB DEFAULT '{}',
  -- Timestamps
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  -- Índices para performance
  CONSTRAINT unique_source_item UNIQUE (source_table_name, source_table_id)
);

-- Índices para busca rápida
CREATE INDEX IF NOT EXISTS idx_items_name ON items(item_name);
CREATE INDEX IF NOT EXISTS idx_items_category ON items(category);
CREATE INDEX IF NOT EXISTS idx_items_rarity ON items(rarity);
CREATE INDEX IF NOT EXISTS idx_items_name_lower ON items(LOWER(item_name));
CREATE INDEX IF NOT EXISTS idx_items_category_name ON items(category, item_name);

-- Índice GIN para busca em JSONB
CREATE INDEX IF NOT EXISTS idx_items_metadata ON items USING GIN (metadata);

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

CREATE TRIGGER trigger_update_items_updated_at
  BEFORE UPDATE ON items
  FOR EACH ROW
  EXECUTE FUNCTION update_items_updated_at();

-- Comentários para documentação
COMMENT ON TABLE items IS 'Tabela unificada de itens - todos os itens de todas as categorias com IDs únicos';
COMMENT ON COLUMN items.source_table_id IS 'ID original do item na tabela de origem (SQLite)';
COMMENT ON COLUMN items.source_table_name IS 'Nome da tabela de origem (espadas, armaduras, etc)';
COMMENT ON COLUMN items.category IS 'Categoria normalizada (weapon, armor, helmet, etc)';
COMMENT ON COLUMN items.metadata IS 'Campos extras que variam por categoria (proteção, bonus, etc)';