-- Estrutura do banco para catalogação de joias
-- Execute este script no SQL Editor do Supabase se o setup automático falhar

-- Extensões necessárias
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Tabela principal de peças
CREATE TABLE IF NOT EXISTS pieces (
    sku VARCHAR(50) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    collection VARCHAR(100),
    material VARCHAR(255),
    predominant_metal VARCHAR(100),
    stone VARCHAR(100),
    stone_color VARCHAR(50),
    size VARCHAR(50),
    weight_grams DECIMAL(8,2),
    price_brl DECIMAL(10,2),
    description TEXT,
    category VARCHAR(50),
    gender VARCHAR(20),
    style VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Tabela de imagens das peças
CREATE TABLE IF NOT EXISTS piece_images (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    sku VARCHAR(50) REFERENCES pieces(sku) ON DELETE CASCADE,
    image_type VARCHAR(20) CHECK (image_type IN ('product', 'lifestyle', 'detail', 'packaging')),
    original_url VARCHAR(500),
    thumbnail_url VARCHAR(500),
    file_name VARCHAR(255),
    file_size_bytes INTEGER,
    image_width INTEGER,
    image_height INTEGER,
    embeddings_vector_id VARCHAR(100) UNIQUE,
    technical_analysis JSONB,
    ai_description TEXT,
    dominant_colors JSONB,
    visual_features JSONB,
    quality_score DECIMAL(3,2),
    is_primary BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Tabela de características visuais extraídas
CREATE TABLE IF NOT EXISTS visual_features (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    image_id UUID REFERENCES piece_images(id) ON DELETE CASCADE,
    feature_type VARCHAR(50),
    feature_value VARCHAR(255),
    confidence_score DECIMAL(3,2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Tabela de tags automáticas
CREATE TABLE IF NOT EXISTS image_tags (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    image_id UUID REFERENCES piece_images(id) ON DELETE CASCADE,
    tag VARCHAR(100),
    confidence DECIMAL(3,2),
    tag_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Tabela de histórico de processamento
CREATE TABLE IF NOT EXISTS processing_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    sku VARCHAR(50),
    image_id UUID,
    process_type VARCHAR(50),
    status VARCHAR(20),
    error_message TEXT,
    processing_time_ms INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Índices para otimização
CREATE INDEX IF NOT EXISTS idx_piece_images_sku ON piece_images(sku);
CREATE INDEX IF NOT EXISTS idx_piece_images_type ON piece_images(image_type);
CREATE INDEX IF NOT EXISTS idx_piece_images_primary ON piece_images(is_primary);
CREATE INDEX IF NOT EXISTS idx_visual_features_image_id ON visual_features(image_id);
CREATE INDEX IF NOT EXISTS idx_image_tags_image_id ON image_tags(image_id);
CREATE INDEX IF NOT EXISTS idx_processing_log_sku ON processing_log(sku);
CREATE INDEX IF NOT EXISTS idx_pieces_category ON pieces(category);
CREATE INDEX IF NOT EXISTS idx_pieces_collection ON pieces(collection);

-- Função para atualizar updated_at automaticamente
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Trigger para atualizar updated_at na tabela pieces
DROP TRIGGER IF EXISTS update_pieces_updated_at ON pieces;
CREATE TRIGGER update_pieces_updated_at 
    BEFORE UPDATE ON pieces 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- Views úteis
CREATE OR REPLACE VIEW pieces_with_images AS
SELECT 
    p.*,
    pi.id as image_id,
    pi.image_type,
    pi.original_url,
    pi.thumbnail_url,
    pi.ai_description,
    pi.dominant_colors,
    pi.quality_score,
    pi.is_primary
FROM pieces p
LEFT JOIN piece_images pi ON p.sku = pi.sku
WHERE p.is_active = true;