cmd

var ImportCmd = &cobra.Command{
	Use:     "index",
	Aliases: []string{"i"},
	Short:   "index prestashop products and categories to manticoresearch.",
	Long:    "index prestashop products and categories to manticoresearch real-time indices",
	Run: func(cmd *cobra.Command, args []string) {

		if !dryRun {
			var err error

			dsn := fmt.Sprintf("%v:%v@tcp(%v:%v)/%v?charset=utf8mb4&collation=utf8mb4_unicode_ci&parseTime=True&loc=Local", dbUser, dbPass, dbHost, dbPort, dbName)
			db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
				NamingStrategy: schema.NamingStrategy{
					TablePrefix:   dbTablePrefix,
					SingularTable: true,
					NameReplacer:  strings.NewReplacer("ID", "Id"),
				},
			})
			if err != nil {
				log.Fatal(err)
			}
		}

		cl, err := sql.Open("mysql", "@tcp(127.0.0.1:9306)/")
		if err != nil {
			panic(err)
		}

		switch indexName {
		case "products", "product":

			limit := 1000000
			offset := 0

			for {

				var products []*Product

				sqlResult := bytes.NewBufferString("")
				sqlTemplate, _ := template.New("").Parse(sqlAdminCatalogProducts)
				sqlTemplate.Execute(sqlResult, map[string]string{"prefix": dbTablePrefix, "offset": fmt.Sprintf("%d", offset), "limit": fmt.Sprintf("%d", limit)})

				err = db.Raw(sqlResult.String()).Scan(&products).Error
				if err != nil {
					log.Fatal(err)
				}

				offset = offset + limit

				if len(products) == 0 {
					break
				}

				t := throttler.New(6, len(products))

				for _, product := range products {

					go func(p *Product) error {

						defer t.Done(nil)

						query := `SELECT eg_feature_product.id_product, eg_feature_value_lang.id_feature_value as id_feature_value, eg_feature_value_lang.value as value, eg_feature_lang.name as name, eg_feature_lang.id_feature as id_feature
						FROM eg_feature_product
						INNER JOIN eg_feature_value ON eg_feature_product.id_feature_value = eg_feature_value.id_feature_value
						INNER JOIN eg_feature_value_lang ON eg_feature_value_lang.id_feature_value = eg_feature_value.id_feature_value
						INNER JOIN eg_feature ON eg_feature_value.id_feature = eg_feature.id_feature
						INNER JOIN eg_feature_lang ON eg_feature.id_feature = eg_feature_lang.id_feature
						WHERE eg_feature_lang.id_lang = 1 
						AND eg_feature_value_lang.id_lang = 1 
						AND eg_feature_product.id_product = ` + fmt.Sprintf("%d", p.IdProduct)

						// create sub query for features
						var features []*Feature
						db.Raw(query).Scan(&features)

						var engine, make, model, size, color, season string
						var engine_id, make_id, model_id, size_id, season_id, color_id int
						for _, feat := range features {
							switch feat.Name {
							case "Marque", "brand", "brand_name", "marque", "fabricant", "name_of_brand", "nom_marque", "make", "manufacturer":
								make = feat.Value
								engine_id = feat.IdFeatureValue
							case "Modèle", "model", "modele", "vehicule":
								model = feat.Value
								model_id = feat.IdFeatureValue
							case "Motorisation", "engine", "motor", "moteur":
								engine = feat.Value
								engine_id = feat.IdFeatureValue
							case "size", "taille":
								size = feat.Value
								size_id = feat.IdFeatureValue
							case "color", "couleur", "matiere":
								color = feat.Value
								color_id = feat.IdFeatureValue

							case "saison", "season":
								season = feat.Value
								season_id = feat.IdFeatureValue
							}
						}

						query = fmt.Sprintf(`REPLACE into rt_products (
					id,
					date_add,
					date_upd,
					id_product,
					price,
					id_shop_default,
					is_virtual,
					link_rewrite,
					active,
					shop_name,
					shop_id,
					id_image,
					price_final,
					nb_downloadable,
					sav_quantity,
					badge_danger,
					features,
					feature_values,
					name,
					reference,
					description,
					description_short,
					name_category,
					engine,
					id_engine,
					model,
					id_model,
					make,
					id_make,
					size,
					id_size,
					color,
					id_color,
					season,
					id_season,
					ft_engine,
					ft_model,
					ft_make,
					id_category
					) VALUES ('%s','%d','%d','%d','%.2f','%d','%t','%s','%t','%s','%d','%d','%.2f','%d','%d','%t',(%s),(%s),'%s','%s','%s','%s','%s','%s','%d','%s','%d','%s','%d','%s','%d','%s','%d','%s','%d','%s','%s','%s','%d')`,
							fmt.Sprintf("%d-%d", p.IdProduct, p.ShopId),
							p.DateAdd.Unix(),
							p.DateUpd.Unix(),
							p.IdProduct,
							p.Price,
							p.IdShopDefault,
							p.IsVirtual,
							p.LinkRewrite,
							p.Active,
							escape(p.ShopName),
							p.ShopId,
							p.IdImage,
							p.PriceFinal,
							p.NbDownloadable,
							p.SavQuantity,
							p.BadgeDanger,
							p.Features,
							p.FeatureValues,
							escape(p.Name),
							escape(p.Reference),
							escape(p.Description),
							escape(p.DescriptionShort),
							escape(p.NameCategory),
							escape(make),
							make_id,
							escape(engine),
							engine_id,
							escape(model),
							model_id,
							escape(size),
							size_id,
							escape(color),
							color_id,
							escape(season),
							season_id,
							escape(engine),
							escape(model),
							escape(make),
							p.IdCategory,
						)

						_, err = cl.Exec(query)
						if err != nil {
							log.Infoln(query)
							log.Fatal(err)
							return err
						}
						log.Infoln("Index product >> ", p.IdProduct, "==", p.Name)
						return nil
					}(product)

					t.Throttle()

				}

				if t.Err() != nil {

					for i, err := range t.Errs() {
						log.Printf("error #%d: %s", i, err)
					}
					log.Fatal(t.Err())
				}
			}

		case "orders", "order":

			limit := 10000
			offset := 0

			for {

				sqlResult := bytes.NewBufferString("")
				sqlTemplate, _ := template.New("").Parse(sqlOrdersExtended)
				sqlTemplate.Execute(sqlResult, map[string]string{"prefix": dbTablePrefix, "offset": fmt.Sprintf("%d", offset), "limit": fmt.Sprintf("%d", limit)})

				var orders []*Order
				err = db.Raw(sqlResult.String()).Scan(&orders).Error
				if err != nil {
					log.Fatal(err)
				}

				offset = offset + limit

				if len(orders) == 0 {
					break
				}

				t := throttler.New(1, len(orders))

				for _, order := range orders {

					go func(o *Order) error {

						defer t.Done(nil)

						query := fmt.Sprintf(`REPLACE into rt_orders (
											id,
											id_order,
											id_currency,
											id_pdf,
											id_shop,
											id_lang,
											order_payment,
											module_payment,
											total_paid,
											total_discounts,
											total_discounts_tax_incl,
											total_discounts_tax_excl,
											total_paid_tax_incl,
											total_paid_tax_excl,
											total_paid_real,
											total_products,
											total_products_wt,
											total_shipping,
											total_shipping_tax_incl,
											total_shipping_tax_excl,
											carrier_tax_rate,
											total_wrapping,
											total_wrapping_tax_incl,
											total_wrapping_tax_excl,
											round_mode,
											round_type,
											invoice_number,
											delivery_number,
											invoice_date,
											delivery_date,
											valid,
											date_add,
											date_upd,
											id_address_delivery,
											customer_name,
											order_status_name,
											order_status_color,
											order_new,
											id_country,
											country_name,
											company,
											vat_number,
											phone_mobile,
											phone,
											delivery_lastname,
											delivery_firstname,
											address_1,
											address_2,
											postcode,
											city,
											badge_success				
							) VALUES ('%d','%d','%d','%d','%d','%d','%s','%s','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%s','%s','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%d','%d','%d','%s','%d','%d','%d','%d','%d','%.2f','%d','%s','%s','%s','%d','%d','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%d')`,
							o.IdOrder,
							o.IdOrder,
							o.IdCurrency,
							o.IdPdf,
							o.IdShop,
							o.IdLang,
							escape(o.OrderPayment),
							escape(o.ModulePayment),
							o.TotalPaid,
							o.TotalDiscounts,
							o.TotalDiscountsTaxIncl,
							o.TotalDiscountsTaxExcl,
							o.TotalPaidTaxIncl,
							o.TotalPaidTaxExcl,
							o.TotalPaidReal,
							fmt.Sprintf(string(o.TotalProducts)),
							fmt.Sprintf(string(o.TotalProductsWt)),
							o.TotalShipping,
							o.TotalShippingTaxIncl,
							o.TotalShippingTaxExcl,
							o.TotalWrapping,
							o.TotalWrappingTaxIncl,
							o.TotalWrappingTaxExcl,
							o.RoundMode,
							o.RoundType,
							o.InvoiceNumber,
							escape(o.DeliveryNumber),
							o.InvoiceDate.Unix(),
							o.DeliveryDate.Unix(),
							o.Valid,
							o.DateAdd.Unix(),
							o.DateUpd.Unix(),
							o.CarrierTaxRate,
							o.IdAddressDelivery,
							escape(o.CustomerName),
							escape(o.OrderStatusName),
							escape(o.OrderStatusColor),
							o.OrderNew,
							o.IdCountry,
							escape(o.CountryName),
							escape(o.Company),
							escape(o.VatNumber),
							escape(o.PhoneMobile),
							escape(o.Phone),
							escape(o.DeliveryLastname),
							escape(o.DeliveryFirstname),
							escape(o.Address1),
							escape(o.Address2),
							escape(o.Postcode),
							escape(o.City),
							o.BadgeSuccess,
						)

						_, err = cl.Exec(query)
						if err != nil {
							log.Infoln(query)
							log.Fatal(err)
							return err
						}
						log.Infoln("Index order >> ", o.IdOrder, "==", o.CustomerName)
						return nil
					}(order)

					t.Throttle()

				}

				if t.Err() != nil {

					for i, err := range t.Errs() {
						log.Printf("error #%d: %s", i, err)
					}
					log.Fatal(t.Err())
				}

			}
		case "customers", "customer":

			limit := 10000
			offset := 0

			for {

				sqlResult := bytes.NewBufferString("")
				sqlTemplate, _ := template.New("").Parse(sqlCustomerExtended)
				sqlTemplate.Execute(sqlResult, map[string]string{"prefix": dbTablePrefix, "offset": fmt.Sprintf("%d", offset), "limit": fmt.Sprintf("%d", limit)})

				var customers []*Customer
				err = db.Raw(sqlResult.String()).Scan(&customers).Error
				if err != nil {
					log.Fatal(err)
				}

				offset = offset + limit

				if len(customers) == 0 {
					break
				}

				t := throttler.New(1, len(customers))

				for _, customer := range customers {

					go func(c *Customer) error {

						defer t.Done(nil)

						query := fmt.Sprintf(`REPLACE into rt_customers (
							id,
							id_customer, 
							firstname, 
							lastname, 
							fullname,
							note,				
							email, 
							active, 
							deleted,
							newsletter, 
							optin, 
							ip_registration_newsletter,
							birthday, 
							date_add, 
							social_title,
							id_group,
							group_name,
							shop_name, 
							company, 
							total_spent, 
							connect				
							) VALUES ('%d','%d','%s','%s','%s','%s','%s','%d','%d','%d','%d','%s','%d','%d','%s','%d','%s','%s','%s','%.2f','%d')`,
							c.IdCustomer,
							c.IdCustomer,
							escape(c.Firstname),
							escape(c.Lastname),
							escape(c.Fullname),
							escape(c.Note),
							escape(c.Email),
							c.Active,
							c.Deleted,
							c.Newsletter,
							c.Optin,
							escape(c.IpRegistrationNewsletter),
							c.Birthday.Unix(),
							c.DateAdd.Unix(),
							escape(c.SocialTitle),
							c.IdGroup,
							escape(c.GroupName),
							escape(c.ShopeName),
							escape(c.Company),
							c.TotalSpent,
							c.Connect.Unix(),
						)

						_, err = cl.Exec(query)
						if err != nil {
							log.Infoln(query)
							log.Fatal(err)
							return err
						}
						log.Infoln("Index customer >> ", c.IdCustomer, "==", c.Fullname)
						return nil
					}(customer)

					t.Throttle()

				}

				if t.Err() != nil {

					for i, err := range t.Errs() {
						log.Printf("error #%d: %s", i, err)
					}
					log.Fatal(t.Err())
				}
			}

		}

	},
}

var RootCmd = &cobra.Command{
	Use:   "ps2manticore",
	Short: "ps2manticore is an helper to load prestashop data into manticoresearch.",
	Long:  `ps2manticore is an helper to load prestashop data into manticoresearch.`,
}

var SearchCmd = &cobra.Command{
	Use:     "search",
	Aliases: []string{"s"},
	Short:   "search query on manticoresearch real-time index.",
	Long:    "search query on manticoresearch real-time index with facets",
	Run: func(cmd *cobra.Command, args []string) {

		query := "SELECT id,date_add,date_upd,shop_id,lang_id,category_id,category_link,manufacturer_id,afo,available_for_order,price,manufacturer,id_product,link_rewrite,HIGHLIGHT({passage_boundary='sentence'},'description') FROM rt_products WHERE MATCH('" + escape(searchTerm) + "') LIMIT 0,20 FACET shop_id FACET lang_id FACET price FACET manufacturer_id;"

		scan := func(rcount int, rows []mmysql.Row, res mmysql.Result) error {
			if res.StatusOnly() {
				return nil
			}

			pp.Println("rcount=", rcount)

			for idx, row := range rows {
				fmt.Print("#", rcount, "-", idx, ") ")
				for i := range row {
					fmt.Print(row.Str(i))
					fmt.Print(" ")
				}
				fmt.Println("")
			}
			return nil
		}

		pp.Println("query:", query)

		if err := RunSQL("127.0.0.1:9306", "root", "", "rt_products", query, scan); err != nil {
			fmt.Println(err)
		}

		os.Exit(1)

	},
}