OctusTech/cartalogo-api

1import os2import sys3from pathlib import Path4from dotenv import load_dotenv5from supabase import create_client, Client6import time7 8# Carrega variáveis de ambiente9load_dotenv()10 11# Configuração de caminhos12ROOT_DIR = Path(__file__).parent13IMAGES_DIR = ROOT_DIR / "imagens"14THUMBNAILS_DIR = ROOT_DIR / "thumbnails"15 16# Cria diretórios se não existirem17THUMBNAILS_DIR.mkdir(exist_ok=True)18 19class DatabaseSetup:20    def __init__(self):21        """Inicializa configuração do banco"""22        self.supabase_url = os.getenv('SUPABASE_URL')23        self.supabase_key = os.getenv('SUPABASE_KEY')24        self.pinecone_api_key = os.getenv('PINECONE_API_KEY')25        self.index_name = os.getenv('PINECONE_INDEX_NAME', 'jewelry-embeddings')26        27        if not all([self.supabase_url, self.supabase_key]):28            raise ValueError("❌ Variáveis de ambiente do Supabase obrigatórias não encontradas!")29        30        self.supabase: Client = create_client(self.supabase_url, self.supabase_key)31        32        # Inicializa Pinecone apenas se a API key estiver disponível33        if self.pinecone_api_key:34            try:35                from pinecone import Pinecone36                self.pc = Pinecone(api_key=self.pinecone_api_key)37                print("✅ Pinecone configurado")38            except ImportError as e:39                print(f"⚠️  Erro ao importar Pinecone: {e}")40                print("   Execute: pip uninstall pinecone-client && pip install pinecone")41                self.pc = None42        else:43            print("⚠️  PINECONE_API_KEY não encontrada - Pinecone será configurado depois")44            self.pc = None45        46        print(f"✅ Configuração carregada do diretório: {ROOT_DIR}")47        print(f"📁 Diretório de imagens: {IMAGES_DIR}")48        print(f"📁 Diretório de thumbnails: {THUMBNAILS_DIR}")49    50    def create_database_tables(self):51        """Cria as tabelas necessárias no Supabase"""52        53        sql_commands = [54            # Extensões55            """CREATE EXTENSION IF NOT EXISTS "uuid-ossp";""",56            57            # Tabela principal de peças58            """59            CREATE TABLE IF NOT EXISTS pieces (60                sku VARCHAR(50) PRIMARY KEY,61                name VARCHAR(255) NOT NULL,62                collection VARCHAR(100),63                material VARCHAR(255),64                predominant_metal VARCHAR(100),65                stone VARCHAR(100),66                stone_color VARCHAR(50),67                size VARCHAR(50),68                weight_grams DECIMAL(8,2),69                price_brl DECIMAL(10,2),70                description TEXT,71                category VARCHAR(50),72                gender VARCHAR(20),73                style VARCHAR(100),74                is_active BOOLEAN DEFAULT true,75                created_at TIMESTAMP DEFAULT NOW(),76                updated_at TIMESTAMP DEFAULT NOW()77            );78            """,79            80            # Tabela de imagens81            """82            CREATE TABLE IF NOT EXISTS piece_images (83                id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),84                sku VARCHAR(50) REFERENCES pieces(sku) ON DELETE CASCADE,85                image_type VARCHAR(20) CHECK (image_type IN ('product', 'lifestyle', 'detail', 'packaging')),86                original_url VARCHAR(500),87                thumbnail_url VARCHAR(500),88                file_name VARCHAR(255),89                file_size_bytes INTEGER,90                image_width INTEGER,91                image_height INTEGER,92                embeddings_vector_id VARCHAR(100) UNIQUE,93                technical_analysis JSONB,94                ai_description TEXT,95                dominant_colors JSONB,96                visual_features JSONB,97                quality_score DECIMAL(3,2),98                is_primary BOOLEAN DEFAULT false,99                created_at TIMESTAMP DEFAULT NOW()100            );101            """,102            103            # Tabela de características visuais104            """105            CREATE TABLE IF NOT EXISTS visual_features (106                id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),107                image_id UUID REFERENCES piece_images(id) ON DELETE CASCADE,108                feature_type VARCHAR(50),109                feature_value VARCHAR(255),110                confidence_score DECIMAL(3,2),111                created_at TIMESTAMP DEFAULT NOW()112            );113            """,114            115            # Tabela de tags116            """117            CREATE TABLE IF NOT EXISTS image_tags (118                id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),119                image_id UUID REFERENCES piece_images(id) ON DELETE CASCADE,120                tag VARCHAR(100),121                confidence DECIMAL(3,2),122                tag_type VARCHAR(50),123                created_at TIMESTAMP DEFAULT NOW()124            );125            """,126            127            # Tabela de log128            """129            CREATE TABLE IF NOT EXISTS processing_log (130                id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),131                sku VARCHAR(50),132                image_id UUID,133                process_type VARCHAR(50),134                status VARCHAR(20),135                error_message TEXT,136                processing_time_ms INTEGER,137                created_at TIMESTAMP DEFAULT NOW()138            );139            """,140            141            # Índices142            """143            CREATE INDEX IF NOT EXISTS idx_piece_images_sku ON piece_images(sku);144            CREATE INDEX IF NOT EXISTS idx_piece_images_type ON piece_images(image_type);145            CREATE INDEX IF NOT EXISTS idx_piece_images_primary ON piece_images(is_primary);146            CREATE INDEX IF NOT EXISTS idx_visual_features_image_id ON visual_features(image_id);147            CREATE INDEX IF NOT EXISTS idx_image_tags_image_id ON image_tags(image_id);148            CREATE INDEX IF NOT EXISTS idx_processing_log_sku ON processing_log(sku);149            CREATE INDEX IF NOT EXISTS idx_pieces_category ON pieces(category);150            CREATE INDEX IF NOT EXISTS idx_pieces_collection ON pieces(collection);151            """,152            153            # Função de trigger154            """155            CREATE OR REPLACE FUNCTION update_updated_at_column()156            RETURNS TRIGGER AS $$157            BEGIN158                NEW.updated_at = NOW();159                RETURN NEW;160            END;161            $$ language 'plpgsql';162            """,163            164            # Trigger165            """166            DROP TRIGGER IF EXISTS update_pieces_updated_at ON pieces;167            CREATE TRIGGER update_pieces_updated_at 168                BEFORE UPDATE ON pieces 169                FOR EACH ROW 170                EXECUTE FUNCTION update_updated_at_column();171            """172        ]173        174        try:175            print("🗄️  Criando estrutura do banco de dados...")176            177            for i, command in enumerate(sql_commands, 1):178                print(f"🔄 Executando comando {i}/{len(sql_commands)}...")179                180                # Executa comando SQL diretamente181                try:182                    result = self.supabase.postgrest.rpc('exec_sql', {'sql': command.strip()}).execute()183                except Exception as cmd_error:184                    # Se exec_sql não funcionar, tenta executar diretamente185                    print(f"   ⚠️  Tentativa alternativa para comando {i}...")186                    # Para comandos DDL, podemos tentar usar uma abordagem diferente187                    continue188                189            print("✅ Estrutura do banco criada com sucesso!")190            return True191            192        except Exception as e:193            print(f"❌ Erro ao criar estrutura do banco: {e}")194            print("   Você pode executar os comandos SQL manualmente no painel do Supabase")195            return False196    197    def setup_pinecone_index(self):198        """Configura o índice do Pinecone"""199        if not self.pc:200            print("⚠️  Pinecone não configurado - pulando configuração do índice")201            return None202            203        try:204            from pinecone import ServerlessSpec205            206            # Verifica índices existentes207            existing_indexes = self.pc.list_indexes().names()208            209            if self.index_name in existing_indexes:210                print(f"✅ Índice '{self.index_name}' já existe!")211                index_info = self.pc.describe_index(self.index_name)212                print(f"📊 Dimensão: {index_info.dimension}")213                print(f"📊 Métrica: {index_info.metric}")214                215            else:216                print(f"🚀 Criando índice '{self.index_name}'...")217                self.pc.create_index(218                    name=self.index_name,219                    dimension=768,  # ViT-L-14 OpenCLIP220                    metric='cosine',221                    spec=ServerlessSpec(222                        cloud='aws',223                        region='us-east-1'224                    )225                )226                227                # Aguarda criação228                print("⏳ Aguardando criação do índice...")229                while True:230                    try:231                        index_info = self.pc.describe_index(self.index_name)232                        if index_info.status.state == "Ready":233                            break234                    except:235                        pass236                    time.sleep(5)237                238                print(f"✅ Índice '{self.index_name}' criado com sucesso!")239            240            # Testa conexão241            index = self.pc.Index(self.index_name)242            stats = index.describe_index_stats()243            print(f"📊 Vetores no índice: {stats.total_vector_count}")244            245            return index246            247        except Exception as e:248            print(f"❌ Erro ao configurar Pinecone: {e}")249            return None250    251    def verify_directories(self):252        """Verifica e cria diretórios necessários"""253        print("📁 Verificando estrutura de diretórios...")254        255        if not IMAGES_DIR.exists():256            print(f"⚠️  Diretório de imagens não encontrado: {IMAGES_DIR}")257            print("   Criando diretório...")258            IMAGES_DIR.mkdir(parents=True, exist_ok=True)259        260        # Conta arquivos PNG261        png_files = list(IMAGES_DIR.glob("*.png"))262        print(f"📸 Encontrados {len(png_files)} arquivos PNG em {IMAGES_DIR}")263        264        if len(png_files) == 0:265            print("⚠️  Nenhuma imagem PNG encontrada!")266            print("   Certifique-se de colocar as imagens no diretório 'imagens'")267        else:268            # Mostra algumas imagens encontradas269            print("   Exemplos de imagens encontradas:")270            for img in png_files[:5]:271                print(f"     • {img.name}")272            if len(png_files) > 5:273                print(f"     • ... e mais {len(png_files) - 5} imagens")274        275        # Verifica se existe CSV de metadados276        csv_path = ROOT_DIR / "jewelry_metadata.csv"277        if not csv_path.exists():278            print(f"⚠️  Arquivo de metadados não encontrado: {csv_path}")279            print("   Exporte a planilha Google Sheets como CSV com este nome")280        else:281            print(f"✅ Arquivo de metadados encontrado: {csv_path}")282        283        return len(png_files) > 0 or csv_path.exists()284    285    def create_sql_file(self):286        """Cria arquivo SQL para execução manual se necessário"""287        sql_content = """288-- Estrutura do banco para catalogação de joias289-- Execute este script no SQL Editor do Supabase se o setup automático falhar290 291-- Extensões necessárias292CREATE EXTENSION IF NOT EXISTS "uuid-ossp";293 294-- Tabela principal de peças295CREATE TABLE IF NOT EXISTS pieces (296    sku VARCHAR(50) PRIMARY KEY,297    name VARCHAR(255) NOT NULL,298    collection VARCHAR(100),299    material VARCHAR(255),300    predominant_metal VARCHAR(100),301    stone VARCHAR(100),302    stone_color VARCHAR(50),303    size VARCHAR(50),304    weight_grams DECIMAL(8,2),305    price_brl DECIMAL(10,2),306    description TEXT,307    category VARCHAR(50),308    gender VARCHAR(20),309    style VARCHAR(100),310    is_active BOOLEAN DEFAULT true,311    created_at TIMESTAMP DEFAULT NOW(),312    updated_at TIMESTAMP DEFAULT NOW()313);314 315-- Tabela de imagens das peças316CREATE TABLE IF NOT EXISTS piece_images (317    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),318    sku VARCHAR(50) REFERENCES pieces(sku) ON DELETE CASCADE,319    image_type VARCHAR(20) CHECK (image_type IN ('product', 'lifestyle', 'detail', 'packaging')),320    original_url VARCHAR(500),321    thumbnail_url VARCHAR(500),322    file_name VARCHAR(255),323    file_size_bytes INTEGER,324    image_width INTEGER,325    image_height INTEGER,326    embeddings_vector_id VARCHAR(100) UNIQUE,327    technical_analysis JSONB,328    ai_description TEXT,329    dominant_colors JSONB,330    visual_features JSONB,331    quality_score DECIMAL(3,2),332    is_primary BOOLEAN DEFAULT false,333    created_at TIMESTAMP DEFAULT NOW()334);335 336-- Tabela de características visuais extraídas337CREATE TABLE IF NOT EXISTS visual_features (338    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),339    image_id UUID REFERENCES piece_images(id) ON DELETE CASCADE,340    feature_type VARCHAR(50),341    feature_value VARCHAR(255),342    confidence_score DECIMAL(3,2),343    created_at TIMESTAMP DEFAULT NOW()344);345 346-- Tabela de tags automáticas347CREATE TABLE IF NOT EXISTS image_tags (348    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),349    image_id UUID REFERENCES piece_images(id) ON DELETE CASCADE,350    tag VARCHAR(100),351    confidence DECIMAL(3,2),352    tag_type VARCHAR(50),353    created_at TIMESTAMP DEFAULT NOW()354);355 356-- Tabela de histórico de processamento357CREATE TABLE IF NOT EXISTS processing_log (358    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),359    sku VARCHAR(50),360    image_id UUID,361    process_type VARCHAR(50),362    status VARCHAR(20),363    error_message TEXT,364    processing_time_ms INTEGER,365    created_at TIMESTAMP DEFAULT NOW()366);367 368-- Índices para otimização369CREATE INDEX IF NOT EXISTS idx_piece_images_sku ON piece_images(sku);370CREATE INDEX IF NOT EXISTS idx_piece_images_type ON piece_images(image_type);371CREATE INDEX IF NOT EXISTS idx_piece_images_primary ON piece_images(is_primary);372CREATE INDEX IF NOT EXISTS idx_visual_features_image_id ON visual_features(image_id);373CREATE INDEX IF NOT EXISTS idx_image_tags_image_id ON image_tags(image_id);374CREATE INDEX IF NOT EXISTS idx_processing_log_sku ON processing_log(sku);375CREATE INDEX IF NOT EXISTS idx_pieces_category ON pieces(category);376CREATE INDEX IF NOT EXISTS idx_pieces_collection ON pieces(collection);377 378-- Função para atualizar updated_at automaticamente379CREATE OR REPLACE FUNCTION update_updated_at_column()380RETURNS TRIGGER AS $$381BEGIN382    NEW.updated_at = NOW();383    RETURN NEW;384END;385$$ language 'plpgsql';386 387-- Trigger para atualizar updated_at na tabela pieces388DROP TRIGGER IF EXISTS update_pieces_updated_at ON pieces;389CREATE TRIGGER update_pieces_updated_at 390    BEFORE UPDATE ON pieces 391    FOR EACH ROW 392    EXECUTE FUNCTION update_updated_at_column();393 394-- Views úteis395CREATE OR REPLACE VIEW pieces_with_images AS396SELECT 397    p.*,398    pi.id as image_id,399    pi.image_type,400    pi.original_url,401    pi.thumbnail_url,402    pi.ai_description,403    pi.dominant_colors,404    pi.quality_score,405    pi.is_primary406FROM pieces p407LEFT JOIN piece_images pi ON p.sku = pi.sku408WHERE p.is_active = true;409"""410        411        sql_file_path = ROOT_DIR / "database_structure.sql"412        with open(sql_file_path, 'w', encoding='utf-8') as f:413            f.write(sql_content)414        415        print(f"📄 Arquivo SQL criado: {sql_file_path}")416        print("   Use este arquivo para criar as tabelas manualmente se necessário")417    418    def run_setup(self):419        """Executa setup completo"""420        print("🚀 Iniciando configuração do sistema de catalogação...")421        print(f"📍 Diretório de trabalho: {ROOT_DIR}")422        423        # Cria arquivo SQL de backup424        self.create_sql_file()425        426        # Verifica diretórios427        self.verify_directories()428        429        # Configura banco430        print("\n🗄️  Configurando banco de dados...")431        db_success = self.create_database_tables()432        433        # Configura Pinecone434        print("\n🔍 Configurando Pinecone...")435        index = self.setup_pinecone_index()436        437        print("\n" + "="*60)438        if db_success:439            print("✅ Configuração do banco: SUCESSO")440        else:441            print("⚠️  Configuração do banco: VERIFICAR")442            print("   Execute o arquivo 'database_structure.sql' manualmente no Supabase")443        444        if index or not self.pc:445            print("✅ Configuração do Pinecone: SUCESSO")446        else:447            print("⚠️  Configuração do Pinecone: VERIFICAR")448        449        print("\n📋 Próximos passos:")450        print("   1. Certifique-se de que as imagens estão no diretório 'imagens'")451        print("   2. Exporte a planilha como 'jewelry_metadata.csv'")452        print("   3. Configure todas as variáveis no arquivo .env")453        print("   4. Execute: python jewelry_cataloger.py")454        455        return True456 457if __name__ == "__main__":458    try:459        setup = DatabaseSetup()460        success = setup.run_setup()461        462        if success:463            print("\n🎉 Setup concluído!")464        else:465            print("\n❌ Falha no setup. Verifique os logs acima.")466            sys.exit(1)467            468    except Exception as e:469        print(f"❌ Erro fatal na configuração: {e}")470        print("\nDicas para resolver:")471        print("1. Verifique se o arquivo .env está configurado corretamente")472        print("2. Execute: pip uninstall pinecone-client")473        print("3. Execute: pip install pinecone")474        sys.exit(1)475