OctusTech/cartalogo-api

1 2-- Estrutura do banco para catalogação de joias3-- Execute este script no SQL Editor do Supabase se o setup automático falhar4 5-- Extensões necessárias6CREATE EXTENSION IF NOT EXISTS "uuid-ossp";7 8-- Tabela principal de peças9CREATE TABLE IF NOT EXISTS pieces (10    sku VARCHAR(50) PRIMARY KEY,11    name VARCHAR(255) NOT NULL,12    collection VARCHAR(100),13    material VARCHAR(255),14    predominant_metal VARCHAR(100),15    stone VARCHAR(100),16    stone_color VARCHAR(50),17    size VARCHAR(50),18    weight_grams DECIMAL(8,2),19    price_brl DECIMAL(10,2),20    description TEXT,21    category VARCHAR(50),22    gender VARCHAR(20),23    style VARCHAR(100),24    is_active BOOLEAN DEFAULT true,25    created_at TIMESTAMP DEFAULT NOW(),26    updated_at TIMESTAMP DEFAULT NOW()27);28 29-- Tabela de imagens das peças30CREATE TABLE IF NOT EXISTS piece_images (31    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),32    sku VARCHAR(50) REFERENCES pieces(sku) ON DELETE CASCADE,33    image_type VARCHAR(20) CHECK (image_type IN ('product', 'lifestyle', 'detail', 'packaging')),34    original_url VARCHAR(500),35    thumbnail_url VARCHAR(500),36    file_name VARCHAR(255),37    file_size_bytes INTEGER,38    image_width INTEGER,39    image_height INTEGER,40    embeddings_vector_id VARCHAR(100) UNIQUE,41    technical_analysis JSONB,42    ai_description TEXT,43    dominant_colors JSONB,44    visual_features JSONB,45    quality_score DECIMAL(3,2),46    is_primary BOOLEAN DEFAULT false,47    created_at TIMESTAMP DEFAULT NOW()48);49 50-- Tabela de características visuais extraídas51CREATE TABLE IF NOT EXISTS visual_features (52    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),53    image_id UUID REFERENCES piece_images(id) ON DELETE CASCADE,54    feature_type VARCHAR(50),55    feature_value VARCHAR(255),56    confidence_score DECIMAL(3,2),57    created_at TIMESTAMP DEFAULT NOW()58);59 60-- Tabela de tags automáticas61CREATE TABLE IF NOT EXISTS image_tags (62    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),63    image_id UUID REFERENCES piece_images(id) ON DELETE CASCADE,64    tag VARCHAR(100),65    confidence DECIMAL(3,2),66    tag_type VARCHAR(50),67    created_at TIMESTAMP DEFAULT NOW()68);69 70-- Tabela de histórico de processamento71CREATE TABLE IF NOT EXISTS processing_log (72    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),73    sku VARCHAR(50),74    image_id UUID,75    process_type VARCHAR(50),76    status VARCHAR(20),77    error_message TEXT,78    processing_time_ms INTEGER,79    created_at TIMESTAMP DEFAULT NOW()80);81 82-- Índices para otimização83CREATE INDEX IF NOT EXISTS idx_piece_images_sku ON piece_images(sku);84CREATE INDEX IF NOT EXISTS idx_piece_images_type ON piece_images(image_type);85CREATE INDEX IF NOT EXISTS idx_piece_images_primary ON piece_images(is_primary);86CREATE INDEX IF NOT EXISTS idx_visual_features_image_id ON visual_features(image_id);87CREATE INDEX IF NOT EXISTS idx_image_tags_image_id ON image_tags(image_id);88CREATE INDEX IF NOT EXISTS idx_processing_log_sku ON processing_log(sku);89CREATE INDEX IF NOT EXISTS idx_pieces_category ON pieces(category);90CREATE INDEX IF NOT EXISTS idx_pieces_collection ON pieces(collection);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 pieces102DROP TRIGGER IF EXISTS update_pieces_updated_at ON pieces;103CREATE TRIGGER update_pieces_updated_at 104    BEFORE UPDATE ON pieces 105    FOR EACH ROW 106    EXECUTE FUNCTION update_updated_at_column();107 108-- Views úteis109CREATE OR REPLACE VIEW pieces_with_images AS110SELECT 111    p.*,112    pi.id as image_id,113    pi.image_type,114    pi.original_url,115    pi.thumbnail_url,116    pi.ai_description,117    pi.dominant_colors,118    pi.quality_score,119    pi.is_primary120FROM pieces p121LEFT JOIN piece_images pi ON p.sku = pi.sku122WHERE p.is_active = true;123