
CREATE TABLE ProductSearch (
  id                  INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  productId           INT(10) UNSIGNED NOT NULL,
  productVariantId    INT(10) UNSIGNED NOT NULL,
  productCode         VARCHAR(255)     NOT NULL,
  langId              INT(10) UNSIGNED NOT NULL,
  productBrandName    VARCHAR(255)              DEFAULT NULL,
  productColorName    VARCHAR(255)              DEFAULT NULL,
  productName         VARCHAR(255)              DEFAULT NULL,
  productDescription  TEXT                      DEFAULT NULL,
  productItemno       VARCHAR(255)              DEFAULT NULL,
  productCategoryName VARCHAR(255)              DEFAULT NULL,
  PRIMARY KEY (id),
  INDEX ProductSearch_langId_idx (langId),
  FULLTEXT INDEX ProductSearch_ft_idx (productBrandName, productColorName, productName, productDescription, productItemno, productCategoryName, productCode)
) ENGINE = InnoDb CHARSET = utf8 COLLATE = utf8_general_ci;

TRUNCATE TABLE ProductSearch;
INSERT INTO ProductSearch SELECT
                            NULL,
                            p.id                                  AS productId,
                            p.productVariantId                    AS productVariantId,
                            CONCAT(p.id, '-', p.productVariantId) AS productCode,
                            pn.langId                             AS langId,
                            pb.name                               AS productBrandName,
                            pcg.name                              AS productColorName,
                            pn.name                               AS productName,
                            pd.description                        AS productDescription,
                            p.itemno                              AS productItemno,
                            pc.name                               AS productCategoryName
                          FROM Product p,
                            ProductNameTranslation pn,
                            ProductDescriptionTranslation pd,
                            Lang l,
                            ProductCategoryTranslation pc,
                            ProductHasProductCategory phpc,
                            ProductBrand pb,
                            ProductColorGroup pcg,
                            ProductHasProductColorGroup phpcg
                          WHERE
                            p.id = pd.productId AND p.productVariantId = pd.productVariantId AND l.id = pd.langId
                            AND
                            p.id = pn.productId AND p.productVariantId = pn.productVariantId AND l.id = pn.langId
                            AND
                            p.id = phpc.productId AND p.productVariantId = phpc.productVariantId AND
                            phpc.productCategoryId = pc.productCategoryId AND pc.langId = l.id AND
                            p.productBrandId = pb.id AND
                            p.id = phpcg.productId AND p.productVariantId = phpcg.productVariantId AND
                            pcg.id = phpcg.productColorGroupId
                          GROUP BY productId, productVariantId, langId;