CREATE OR REPLACE VIEW vBluesealProductDetailByLang AS
  SELECT
    ProductDetail.id,
    ProductDetail.slug,
    Lang.id   AS langId,
    Lang.lang AS langCode
  FROM ProductDetail, Lang
  WHERE ProductDetail.slug <> '';


CREATE OR REPLACE VIEW vBluesealProductDetailTranslationStatus AS
  SELECT
    sq1.id,
    sq1.slug,
    GROUP_CONCAT(IFNULL(sq1.langCode, 'x') ORDER BY sq1.langId SEPARATOR '|')                    AS translatedLangCode,
    GROUP_CONCAT(IFNULL(ProductDetailTranslation.langId, 'x') ORDER BY sq1.langId SEPARATOR '|') AS translatedLangId,
    GROUP_CONCAT(IFNULL(ProductDetailTranslation.name, 'x') ORDER BY sq1.langId SEPARATOR '|')   AS translatedName
  FROM ProductDetailTranslation
    RIGHT JOIN vBluesealProductDetailByLang AS sq1
      ON ProductDetailTranslation.productDetailId = sq1.id AND ProductDetailTranslation.langId = sq1.langId
  GROUP BY id;

CREATE OR REPLACE VIEW vBluesealProductDetailTranslation AS
  SELECT
    view.id,
    view.slug,
    view.translatedLangId,
    view.translatedName,
    IF(SUM(ProductSku.stockQty) > 0, TRUE, FALSE) AS hasQuantity,
    COUNT(DISTINCT ProductSku.productVariantId)   AS timesDetailIsUsedInProduct
  FROM vBluesealProductDetailTranslationStatus AS view
    INNER JOIN ProductSheetActual ON ProductSheetActual.productDetailId = view.id
    INNER JOIN ProductSku ON ProductSheetActual.productId = ProductSku.productId AND
                             ProductSheetActual.productVariantId = ProductSku.productVariantId
  GROUP BY view.id;

DROP VIEW vBluesealProductDetailList;