CREATE OR REPLACE VIEW vBluesealMarketplaceProductList AS
  SELECT
    concat(p.id, '-', p.productVariantId) AS code,
    p.id,
    p.productVariantId,
    p.itemno,
    pb.name as brand,
    p.creationDate,
    concat(ifnull(m.name,''),ifnull(ma.name, ''))               AS marketplaceAccountName,
    s.name                                AS shop
  FROM
    Product p
    JOIN ProductStatus ps ON p.productStatusId = ps.id
    JOIN ShopHasProduct shp ON p.id = shp.productId AND p.productVariantId = shp.productVariantId
    JOIN Shop s ON s.id = shp.shopId
      JOIN ProductBrand pb ON p.productBrandId = pb.id
    LEFT JOIN MarketplaceAccountHasProduct mahp ON mahp.productId = p.id AND mahp.productVariantId = p.productVariantId
    LEFT JOIN MarketplaceAccount ma ON ma.marketplaceId = mahp.marketplaceId AND ma.id = mahp.marketplaceAccountId
    LEFT JOIN Marketplace m ON m.id = ma.marketplaceId
  WHERE ps.isReady = 1 AND qty > 0;