michangarrito/database/schemas/16-marketplace.sql

-- ============================================
-- MCH-028: Marketplace de Proveedores
-- Schema: marketplace
-- Tablas: suppliers, supplier_products, supplier_orders, supplier_order_items, supplier_reviews
-- ============================================

-- Crear schema
CREATE SCHEMA IF NOT EXISTS marketplace;

-- ============================================
-- SUPPLIERS (Proveedores)
-- ============================================

CREATE TABLE IF NOT EXISTS marketplace.suppliers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Datos basicos
    name VARCHAR(200) NOT NULL,
    legal_name VARCHAR(300),
    rfc VARCHAR(13),

    -- Categorias que maneja (ej: bebidas, botanas, lacteos)
    categories TEXT[] DEFAULT '{}',

    -- Zonas de cobertura (codigos postales o zonas)
    coverage_zones TEXT[] DEFAULT '{}',

    -- Contacto
    contact_name VARCHAR(200),
    contact_phone VARCHAR(20),
    contact_email VARCHAR(200),
    contact_whatsapp VARCHAR(20),

    -- Direccion
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    zip_code VARCHAR(10),

    -- Branding
    logo_url TEXT,
    banner_url TEXT,
    description TEXT,

    -- Configuracion
    min_order_amount DECIMAL(10, 2) DEFAULT 0,
    delivery_fee DECIMAL(10, 2) DEFAULT 0,
    free_delivery_min DECIMAL(10, 2),
    delivery_days TEXT[] DEFAULT '{}', -- Dias de entrega
    lead_time_days INT DEFAULT 1, -- Dias para entrega

    -- Verificacion y rating
    verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMPTZ,
    rating DECIMAL(2, 1) DEFAULT 0,
    total_reviews INT DEFAULT 0,
    total_orders INT DEFAULT 0,

    -- Estado
    status VARCHAR(20) DEFAULT 'pending', -- pending, active, suspended, inactive

    -- Usuario asociado (para portal de proveedor)
    user_id UUID,

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

-- Indices
CREATE INDEX IF NOT EXISTS idx_suppliers_status ON marketplace.suppliers(status);
CREATE INDEX IF NOT EXISTS idx_suppliers_categories ON marketplace.suppliers USING GIN(categories);
CREATE INDEX IF NOT EXISTS idx_suppliers_coverage ON marketplace.suppliers USING GIN(coverage_zones);
CREATE INDEX IF NOT EXISTS idx_suppliers_rating ON marketplace.suppliers(rating DESC);

-- ============================================
-- SUPPLIER_PRODUCTS (Productos de Proveedores)
-- ============================================

CREATE TABLE IF NOT EXISTS marketplace.supplier_products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    supplier_id UUID NOT NULL REFERENCES marketplace.suppliers(id) ON DELETE CASCADE,

    -- Datos del producto
    name VARCHAR(300) NOT NULL,
    description TEXT,
    sku VARCHAR(100),
    barcode VARCHAR(50),

    -- Categoria
    category VARCHAR(100),
    subcategory VARCHAR(100),

    -- Imagen
    image_url TEXT,

    -- Precios (para mayoreo)
    unit_price DECIMAL(10, 2) NOT NULL,
    unit_type VARCHAR(50) DEFAULT 'pieza', -- pieza, caja, paquete, kg, litro

    -- Cantidad minima de pedido
    min_quantity INT DEFAULT 1,

    -- Precios escalonados (JSON)
    tiered_pricing JSONB DEFAULT '[]', -- [{min: 10, price: 15.00}, {min: 50, price: 14.00}]

    -- Disponibilidad
    in_stock BOOLEAN DEFAULT TRUE,
    stock_quantity INT,

    -- Estado
    active BOOLEAN DEFAULT TRUE,

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

-- Indices
CREATE INDEX IF NOT EXISTS idx_supplier_products_supplier ON marketplace.supplier_products(supplier_id);
CREATE INDEX IF NOT EXISTS idx_supplier_products_category ON marketplace.supplier_products(category);
CREATE INDEX IF NOT EXISTS idx_supplier_products_barcode ON marketplace.supplier_products(barcode) WHERE barcode IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_supplier_products_active ON marketplace.supplier_products(active);

-- ============================================
-- SUPPLIER_ORDERS (Pedidos a Proveedores)
-- ============================================

CREATE TABLE IF NOT EXISTS marketplace.supplier_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    supplier_id UUID NOT NULL REFERENCES marketplace.suppliers(id),

    -- Numero de orden
    order_number SERIAL,

    -- Estado del pedido
    status VARCHAR(30) DEFAULT 'pending',
    -- pending, confirmed, preparing, shipped, delivered, cancelled, rejected

    -- Montos
    subtotal DECIMAL(12, 2) NOT NULL,
    delivery_fee DECIMAL(10, 2) DEFAULT 0,
    discount DECIMAL(10, 2) DEFAULT 0,
    total DECIMAL(12, 2) NOT NULL,

    -- Entrega
    delivery_address TEXT NOT NULL,
    delivery_city VARCHAR(100),
    delivery_zip VARCHAR(10),
    delivery_phone VARCHAR(20),
    delivery_contact VARCHAR(200),

    -- Programacion
    requested_date DATE,
    confirmed_date DATE,
    estimated_delivery TIMESTAMPTZ,
    delivered_at TIMESTAMPTZ,

    -- Notas
    notes TEXT,
    supplier_notes TEXT,

    -- Cancelacion
    cancelled_at TIMESTAMPTZ,
    cancel_reason TEXT,
    cancelled_by VARCHAR(20), -- tenant, supplier

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

-- Indices
CREATE INDEX IF NOT EXISTS idx_supplier_orders_tenant ON marketplace.supplier_orders(tenant_id);
CREATE INDEX IF NOT EXISTS idx_supplier_orders_supplier ON marketplace.supplier_orders(supplier_id);
CREATE INDEX IF NOT EXISTS idx_supplier_orders_status ON marketplace.supplier_orders(status);
CREATE INDEX IF NOT EXISTS idx_supplier_orders_created ON marketplace.supplier_orders(created_at DESC);

-- ============================================
-- SUPPLIER_ORDER_ITEMS (Items de Pedidos)
-- ============================================

CREATE TABLE IF NOT EXISTS marketplace.supplier_order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES marketplace.supplier_orders(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES marketplace.supplier_products(id),

    -- Snapshot del producto al momento del pedido
    product_name VARCHAR(300) NOT NULL,
    product_sku VARCHAR(100),

    -- Cantidad y precios
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    total DECIMAL(12, 2) NOT NULL,

    -- Notas
    notes TEXT,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indices
CREATE INDEX IF NOT EXISTS idx_supplier_order_items_order ON marketplace.supplier_order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_supplier_order_items_product ON marketplace.supplier_order_items(product_id);

-- ============================================
-- SUPPLIER_REVIEWS (Resenas de Proveedores)
-- ============================================

CREATE TABLE IF NOT EXISTS marketplace.supplier_reviews (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    supplier_id UUID NOT NULL REFERENCES marketplace.suppliers(id) ON DELETE CASCADE,
    order_id UUID REFERENCES marketplace.supplier_orders(id),

    -- Rating
    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),

    -- Comentario
    title VARCHAR(200),
    comment TEXT,

    -- Aspectos especificos (1-5)
    rating_quality INT CHECK (rating_quality >= 1 AND rating_quality <= 5),
    rating_delivery INT CHECK (rating_delivery >= 1 AND rating_delivery <= 5),
    rating_price INT CHECK (rating_price >= 1 AND rating_price <= 5),

    -- Respuesta del proveedor
    supplier_response TEXT,
    responded_at TIMESTAMPTZ,

    -- Estado
    verified BOOLEAN DEFAULT FALSE, -- Review de compra verificada
    status VARCHAR(20) DEFAULT 'active', -- active, hidden, flagged

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

-- Indices
CREATE INDEX IF NOT EXISTS idx_supplier_reviews_supplier ON marketplace.supplier_reviews(supplier_id);
CREATE INDEX IF NOT EXISTS idx_supplier_reviews_tenant ON marketplace.supplier_reviews(tenant_id);
CREATE INDEX IF NOT EXISTS idx_supplier_reviews_rating ON marketplace.supplier_reviews(rating);

-- Constraint: una review por orden
CREATE UNIQUE INDEX IF NOT EXISTS idx_supplier_reviews_order_unique
ON marketplace.supplier_reviews(order_id) WHERE order_id IS NOT NULL;

-- ============================================
-- FAVORITOS
-- ============================================

CREATE TABLE IF NOT EXISTS marketplace.supplier_favorites (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    supplier_id UUID NOT NULL REFERENCES marketplace.suppliers(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE(tenant_id, supplier_id)
);

CREATE INDEX IF NOT EXISTS idx_supplier_favorites_tenant ON marketplace.supplier_favorites(tenant_id);

-- ============================================
-- FUNCIONES
-- ============================================

-- Actualizar rating del proveedor
CREATE OR REPLACE FUNCTION marketplace.update_supplier_rating()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE marketplace.suppliers
    SET
        rating = (
            SELECT COALESCE(AVG(rating), 0)
            FROM marketplace.supplier_reviews
            WHERE supplier_id = COALESCE(NEW.supplier_id, OLD.supplier_id)
            AND status = 'active'
        ),
        total_reviews = (
            SELECT COUNT(*)
            FROM marketplace.supplier_reviews
            WHERE supplier_id = COALESCE(NEW.supplier_id, OLD.supplier_id)
            AND status = 'active'
        ),
        updated_at = NOW()
    WHERE id = COALESCE(NEW.supplier_id, OLD.supplier_id);

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_update_supplier_rating ON marketplace.supplier_reviews;
CREATE TRIGGER trg_update_supplier_rating
AFTER INSERT OR UPDATE OR DELETE ON marketplace.supplier_reviews
FOR EACH ROW EXECUTE FUNCTION marketplace.update_supplier_rating();

-- Actualizar conteo de ordenes del proveedor
CREATE OR REPLACE FUNCTION marketplace.update_supplier_orders_count()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'delivered' AND (OLD IS NULL OR OLD.status != 'delivered') THEN
        UPDATE marketplace.suppliers
        SET
            total_orders = total_orders + 1,
            updated_at = NOW()
        WHERE id = NEW.supplier_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_update_supplier_orders_count ON marketplace.supplier_orders;
CREATE TRIGGER trg_update_supplier_orders_count
AFTER INSERT OR UPDATE ON marketplace.supplier_orders
FOR EACH ROW EXECUTE FUNCTION marketplace.update_supplier_orders_count();

-- Funcion para buscar proveedores por zona
CREATE OR REPLACE FUNCTION marketplace.find_suppliers_by_zone(
    p_zip_code VARCHAR(10),
    p_category VARCHAR(100) DEFAULT NULL,
    p_limit INT DEFAULT 20
)
RETURNS TABLE (
    id UUID,
    name VARCHAR(200),
    logo_url TEXT,
    rating DECIMAL(2, 1),
    total_reviews INT,
    min_order_amount DECIMAL(10, 2),
    delivery_fee DECIMAL(10, 2),
    categories TEXT[]
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        s.id,
        s.name,
        s.logo_url,
        s.rating,
        s.total_reviews,
        s.min_order_amount,
        s.delivery_fee,
        s.categories
    FROM marketplace.suppliers s
    WHERE s.status = 'active'
    AND (p_zip_code = ANY(s.coverage_zones) OR s.coverage_zones = '{}')
    AND (p_category IS NULL OR p_category = ANY(s.categories))
    ORDER BY s.rating DESC, s.total_orders DESC
    LIMIT p_limit;
END;
$$ LANGUAGE plpgsql;

-- Funcion para obtener estadisticas del marketplace (para admin)
CREATE OR REPLACE FUNCTION marketplace.get_marketplace_stats()
RETURNS TABLE (
    total_suppliers BIGINT,
    active_suppliers BIGINT,
    total_products BIGINT,
    total_orders BIGINT,
    total_gmv DECIMAL(14, 2),
    avg_rating DECIMAL(2, 1)
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        (SELECT COUNT(*) FROM marketplace.suppliers),
        (SELECT COUNT(*) FROM marketplace.suppliers WHERE status = 'active'),
        (SELECT COUNT(*) FROM marketplace.supplier_products WHERE active = true),
        (SELECT COUNT(*) FROM marketplace.supplier_orders),
        (SELECT COALESCE(SUM(total), 0) FROM marketplace.supplier_orders WHERE status = 'delivered'),
        (SELECT COALESCE(AVG(s.rating), 0) FROM marketplace.suppliers s WHERE s.status = 'active');
END;
$$ LANGUAGE plpgsql;

-- Permisos
GRANT USAGE ON SCHEMA marketplace TO michangarrito_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA marketplace TO michangarrito_app;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA marketplace TO michangarrito_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA marketplace TO michangarrito_app;

COMMENT ON SCHEMA marketplace IS 'MCH-028: Marketplace B2B de proveedores';
COMMENT ON TABLE marketplace.suppliers IS 'Proveedores/distribuidores registrados';
COMMENT ON TABLE marketplace.supplier_products IS 'Catalogo de productos de cada proveedor';
COMMENT ON TABLE marketplace.supplier_orders IS 'Pedidos de tiendas a proveedores';
COMMENT ON TABLE marketplace.supplier_order_items IS 'Items de cada pedido';
COMMENT ON TABLE marketplace.supplier_reviews IS 'Resenas y calificaciones de proveedores';