OctusTech/cartalogo-api

1-- Extensões necessárias2CREATE EXTENSION IF NOT EXISTS "uuid-ossp";3CREATE EXTENSION IF NOT EXISTS "vector";4 5-- Tabela principal de peças de joias6CREATE TABLE IF NOT EXISTS pieces (7    sku VARCHAR(50) PRIMARY KEY,8    name VARCHAR(255) NOT NULL,9    collection VARCHAR(100),10    material VARCHAR(255),11    predominant_metal VARCHAR(100),12    stone VARCHAR(100),13    stone_color VARCHAR(50),14    size VARCHAR(50),15    weight_grams DECIMAL(8,2),16    price_brl DECIMAL(10,2),17    description TEXT,18    category VARCHAR(50), -- anel, colar, brinco, pulseira, etc19    gender VARCHAR(20), -- feminino, masculino, unissex20    style VARCHAR(100), -- moderno, clássico, vintage, etc21    is_active BOOLEAN DEFAULT true,22    created_at TIMESTAMP DEFAULT NOW(),23    updated_at TIMESTAMP DEFAULT NOW()24);25 26-- Tabela de imagens das peças27CREATE TABLE IF NOT EXISTS piece_images (28    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),29    sku VARCHAR(50) REFERENCES pieces(sku) ON DELETE CASCADE,30    image_type VARCHAR(20) CHECK (image_type IN ('product', 'lifestyle', 'detail', 'packaging')),31    original_url VARCHAR(500),32    thumbnail_url VARCHAR(500),33    file_name VARCHAR(255),34    file_size_bytes INTEGER,35    image_width INTEGER,36    image_height INTEGER,37    embeddings_vector_id VARCHAR(100) UNIQUE,38    technical_analysis JSONB,39    ai_description TEXT, -- Descrição gerada por IA40    dominant_colors JSONB, -- Array de cores dominantes41    visual_features JSONB, -- Características visuais extraídas42    quality_score DECIMAL(3,2), -- Score de qualidade da imagem (0-1)43    is_primary BOOLEAN DEFAULT false, -- Imagem principal da peça44    created_at TIMESTAMP DEFAULT NOW()45);46 47-- Tabela de características visuais extraídas48CREATE TABLE IF NOT EXISTS visual_features (49    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),50    image_id UUID REFERENCES piece_images(id) ON DELETE CASCADE,51    feature_type VARCHAR(50), -- 'color', 'shape', 'texture', 'reflection', etc52    feature_value VARCHAR(255),53    confidence_score DECIMAL(3,2),54    created_at TIMESTAMP DEFAULT NOW()55);56 57-- Tabela de tags automáticas58CREATE TABLE IF NOT EXISTS image_tags (59    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),60    image_id UUID REFERENCES piece_images(id) ON DELETE CASCADE,61    tag VARCHAR(100),62    confidence DECIMAL(3,2),63    tag_type VARCHAR(50), -- 'color', 'material', 'style', 'shape'64    created_at TIMESTAMP DEFAULT NOW()65);66 67-- Tabela de histórico de processamento68CREATE TABLE IF NOT EXISTS processing_log (69    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),70    sku VARCHAR(50),71    image_id UUID,72    process_type VARCHAR(50), -- 'embedding', 'analysis', 'upload'73    status VARCHAR(20), -- 'success', 'error', 'processing'74    error_message TEXT,75    processing_time_ms INTEGER,76    created_at TIMESTAMP DEFAULT NOW()77);78 79-- Índices para otimização80CREATE INDEX IF NOT EXISTS idx_piece_images_sku ON piece_images(sku);81CREATE INDEX IF NOT EXISTS idx_piece_images_type ON piece_images(image_type);82CREATE INDEX IF NOT EXISTS idx_piece_images_primary ON piece_images(is_primary);83CREATE INDEX IF NOT EXISTS idx_visual_features_image_id ON visual_features(image_id);84CREATE INDEX IF NOT EXISTS idx_visual_features_type ON visual_features(feature_type);85CREATE INDEX IF NOT EXISTS idx_image_tags_image_id ON image_tags(image_id);86CREATE INDEX IF NOT EXISTS idx_image_tags_tag ON image_tags(tag);87CREATE INDEX IF NOT EXISTS idx_processing_log_sku ON processing_log(sku);88CREATE INDEX IF NOT EXISTS idx_pieces_category ON pieces(category);89CREATE INDEX IF NOT EXISTS idx_pieces_collection ON pieces(collection);90CREATE INDEX IF NOT EXISTS idx_pieces_active ON pieces(is_active);91 92-- Função para atualizar updated_at automaticamente93CREATE OR REPLACE FUNCTION update_updated_at_column()94RETURNS TRIGGER AS $$95BEGIN96    NEW.updated_at = NOW();97    RETURN NEW;98END;99$$ language 'plpgsql';100 101-- Trigger para atualizar updated_at na tabela pieces102CREATE TRIGGER update_pieces_updated_at 103    BEFORE UPDATE ON pieces 104    FOR EACH ROW 105    EXECUTE FUNCTION update_updated_at_column();106 107-- View para busca completa de peças com imagens108CREATE OR REPLACE VIEW pieces_with_images AS109SELECT 110    p.*,111    pi.id as image_id,112    pi.image_type,113    pi.original_url,114    pi.thumbnail_url,115    pi.ai_description,116    pi.dominant_colors,117    pi.visual_features,118    pi.quality_score,119    pi.is_primary120FROM pieces p121LEFT JOIN piece_images pi ON p.sku = pi.sku122WHERE p.is_active = true;123 124-- View para estatísticas de processamento125CREATE OR REPLACE VIEW processing_stats AS126SELECT 127    DATE(created_at) as process_date,128    process_type,129    status,130    COUNT(*) as count,131    AVG(processing_time_ms) as avg_processing_time_ms132FROM processing_log133GROUP BY DATE(created_at), process_type, status134ORDER BY process_date DESC;135