michangarrito/database/schemas/05-catalog.sql

-- =============================================================================
-- MICHANGARRITO - 05 CATALOG
-- =============================================================================
-- Productos y categorías
-- =============================================================================

-- Categorías
CREATE TABLE IF NOT EXISTS catalog.categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,

    name VARCHAR(50) NOT NULL,
    description TEXT,
    icon VARCHAR(50),
    color VARCHAR(7),
    sort_order INTEGER DEFAULT 0,

    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, name)
);

CREATE INDEX idx_categories_tenant ON catalog.categories(tenant_id);

CREATE TRIGGER update_categories_updated_at
    BEFORE UPDATE ON catalog.categories
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Productos
CREATE TABLE IF NOT EXISTS catalog.products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
    category_id UUID REFERENCES catalog.categories(id) ON DELETE SET NULL,

    -- Identificación
    name VARCHAR(100) NOT NULL,
    description TEXT,
    sku VARCHAR(50),
    barcode VARCHAR(50),

    -- Precios
    price DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(10,2),
    compare_price DECIMAL(10,2),

    -- Inventario
    track_inventory BOOLEAN DEFAULT true,
    stock_quantity INTEGER DEFAULT 0,
    low_stock_threshold INTEGER DEFAULT 5,

    -- Presentación
    unit VARCHAR(20) DEFAULT 'pieza',

    -- Multimedia
    image_url TEXT,

    -- Estado
    status VARCHAR(20) DEFAULT 'active',
    is_featured BOOLEAN DEFAULT false,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_products_tenant ON catalog.products(tenant_id);
CREATE INDEX idx_products_category ON catalog.products(category_id);
CREATE INDEX idx_products_barcode ON catalog.products(tenant_id, barcode);
CREATE INDEX idx_products_name ON catalog.products USING gin(to_tsvector('spanish', name));

CREATE TRIGGER update_products_updated_at
    BEFORE UPDATE ON catalog.products
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Templates de productos (proveedores)
CREATE TABLE IF NOT EXISTS catalog.product_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    provider_name VARCHAR(50) NOT NULL,
    provider_logo_url TEXT,

    name VARCHAR(100) NOT NULL,
    description TEXT,
    barcode VARCHAR(50),
    suggested_price DECIMAL(10,2),
    category_suggestion VARCHAR(50),

    unit VARCHAR(20) DEFAULT 'pieza',
    image_url TEXT,
    business_types TEXT[],
    popularity INTEGER DEFAULT 0,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_templates_provider ON catalog.product_templates(provider_name);
CREATE INDEX idx_templates_barcode ON catalog.product_templates(barcode);