import pandas as pd
import numpy as np

"""
Explanation: Translation of products
This is the code for translation of products. In Prestashop, we have products in Thai and English description. We need to move the translation database to Woocommerce. Woocommerce uses "WPML Multilingual CMS" plugin. After activate, this plugin will create a database called "wp_icl_translation". The "trid" column is used to match the Thai and English version of the same product.
Our process is to create a English description product in "wp_posts" and "wp_postmeta" then match the "trid" of Thai and English product in "wp_icl_translations".
There are 3 parts.


Creating the "wp_posts" for English description products.


Creating the "wp_postmeta" for English description products.


Matching the "trid" of Thai and English product in the "wp_icl_translations"


Creating the "wp_posts"
Our process here is to create wp_posts for English description product. We create new "wp_posts" with contain English product by copy the Thai products and fill them with English descriptions. Change ID and index and concatenate it with the old "wp_posts". Upload new "wp_posts" to Woocommerce. Woocommerce will show you more products (because we copy from Thai product, backend will show 2 x number of products).
Filling the English "Description" and "Short description".
End of explanation
"""


#Description from Prestashop
df_description = pd.read_csv('sql_prestashop/ps_product_lang.csv', index_col=False)
#wp_posts from Woocommerce
wp_posts = pd.read_csv('sql_prestashop/wp_posts.csv', index_col=False)

"""
Explanation: We load the data. There are 
1) "ps_product_lang.csv" the description and short description in Thai and English from Prestashop.
2) "wp_posts.csv" Woocommerce database consists of everything posting on the site (products, images and more).
End of explanation
"""


#Use only English "Description" & "Short description" from Prestashop.
df_description = df_description[df_description['id_lang'] == 1]

"""
Explanation: We select only the English description from Prestashop database.
End of explanation
"""


eng_des = wp_posts.sort_values('ID')

"""
Explanation: Next step, we fill the "Description" & "Short description" in "wp_posts".
Sorting the ID first.
End of explanation
"""


eng_des = eng_des[eng_des['post_type'] == 'product']

"""
Explanation: "wp_posts" has many post types. We extract only "product" type.
End of explanation
"""


#Change column name for merging dataframe.
eng_des = eng_des.rename(columns = {'post_title':'name'})

#merge.
eng_des = pd.merge(eng_des, df_description[['name', 'description', 'description_short']], how='left', on='name')

"""
Explanation: Change column name for merge. Then we merge "eng_des" and "df_description" by name of a products. This will ensure that we match the right description to the right product.
End of explanation
"""


eng_des['post_excerpt'] = eng_des['description_short']
eng_des['post_content'] = eng_des['description']

#Drop unused column.
eng_des = eng_des.drop(['description', 'description_short'], axis=1)

"""
Explanation: Fill wp_post new "Description and "Short description". Then drop the unuse
End of explanation
"""


#Check for duplicate entry.
count = eng_des['name'].value_counts()
#Drop duplicate name of product.
eng_des = eng_des.drop_duplicates(subset='name')

#Create series for Thai ID.
th_id = eng_des['ID']

"""
Explanation: There are some duplicate entry because some entry share the same name.
End of explanation
"""


#Changing "name" column back to "post_title". Protect the error when concatenating
#(After concatenate, it will have 24 column).
eng_des = eng_des.rename(columns = {'name':'post_title'})

"""
Explanation: Changing "name" column back to "post_title". Protect the error when concatenating (after concatenate, it will have 24 column).
End of explanation
"""


empty = np.empty(eng_des.shape[0])
description = pd.Series(empty)
description[0] = ''

"""
Explanation: Change some error strings.
Some of the strings in English description are not in English. We need to change them.
First, we create empty value series and use it to collect new description data.
End of explanation
"""


for i in range(0, eng_des.shape[0]):
    string = eng_des['post_content'].iloc[i]
    if pd.isnull(string) == False:
        string = string.replace("เรื่องย่อ", "Sypnosis")
        string = string.replace("ส่วนที่อยากบอก", "Artist said")   
        string = string.replace("ส่วนที่ผู้จัดทำอยากบอก", "Artist said")  
        string = string.replace("ช่องทางการติดต่อ", "Contact")   
        string = string.replace("ผู้จัดทำ", "Artist") 
        string = string.replace("จากเรื่อง", "Parody")  
        string = string.replace("อ้างอิงจากเรื่อง", "Parody") 
        string = string.replace("ลักษณะของสินค้า", "Details of the product") 
        string = string.replace("ออกขายครั้งแรก", "Publication date")  
        string = string.replace("ชื่อสินค้า", "Product") 
        string = string.replace("ชื่อผลงาน", "Product")
        description[i] = string

eng_des['post_content'] = description

"""
Explanation: Then we run a for loop to replace a string.
End of explanation
"""


#Find max "ID" of wp_posts. Use max ID + 1 to be the starting ID of English product.
max_id = wp_posts['ID'].max()
#Create Series of number as a new "ID".
eng_start = max_id + 1
eng_end = eng_start + eng_des.shape[0]
number = pd.Series(range(eng_start, eng_end))

#Reset eng_des index
eng_des = eng_des.reset_index()
#Drop old index column.
eng_des = eng_des.drop(['index'], axis=1)
eng_des['ID'] = number

#Create Series od number as a new index.
max_index = wp_posts.index.values.max()
eng_index_start = max_index + 1
eng_index_end = eng_index_start + eng_des.shape[0]
number = pd.Series(range(eng_index_start, eng_index_end))
eng_des['number'] = number
eng_des = eng_des.set_index(number)
#Drop unused column.
eng_des = eng_des.drop(['number'], axis=1)

"""
Explanation: Change ID and Index number.
End of explanation
"""


#Generate wp_posts for images
image = wp_posts[wp_posts['post_type'] == 'attachment']

"""
Explanation: Create "wp_posts" entry for images.
We have already made "wp_posts" entry for products. If we concatenate this to the old "wp_posts" and upload to Woocommerce. The new English description product can't find the images. We solve this problem by create the images entry for "wp_posts".
The process here is to copy the old images "wp_posts", change their "post_parent" to match image to the right product. Then concatenate to the old "wp_posts".
First, we extract only "attachment" post_type.
End of explanation
"""


image = image[image['post_parent'] != 0]

"""
Explanation: Some images entry don't have a parent product. We select only the entries that have "post_parent" value.
End of explanation
"""


#Check type of "post_mime_type"
count = image['post_mime_type'].value_counts()
#Check how many products.
count = image['post_parent'].value_counts()

"""
Explanation: Let's check by counting "post_mime_type" and "post_parent". "post_mime_type" is a type of file such as ".jpg", ".png". Use value_counts() will show the type of each file in the data and how number of them in each category. Counting the "post_parent" show nymber of images in each ID.
End of explanation
"""


new = eng_des['ID'].reset_index()
new = new.drop(['index'], axis=1)
new = new['ID']
old = th_id.reset_index()
old = old.drop(['index'], axis=1)
old = old['ID']

"""
Explanation: Now we have to change "post_parent" to match the image to the right product. We use Python's dictionary to translate from old "post_parent" to a new one.
Extract old and new ID into variable.
End of explanation
"""


#reset index
image = image.reset_index()
image = image.drop(['index'], axis=1)

image['number'] = image['post_parent']
image = image.set_index('number')
image = image.loc[th_id]

"""
Explanation: Some post_parent doesn't appear in dictionary. Pick only the one we have in Thai ID.
End of explanation
"""


#Check for nan value.
image['post_parent'].isnull().sum()
#Drop rows that have nan English ID.
image = image[np.isfinite(image['post_parent'])]

"""
Explanation: Check for nan value and drop rows that have nan English ID.
End of explanation
"""


dic = {}
for i in range(len(old)):
    dic[old[i]] = new[i]

"""
Explanation: Create a dictionary.
End of explanation
"""


image['post_parent'] = [dic[x] for x in image['post_parent']]

"""
Explanation: Translate old to new "ID"
End of explanation
"""


max_id = eng_des['ID'].max()
#Create Series of number as a new "ID".
start = max_id + 1
end = start + image.shape[0]
number = pd.Series(range(start, end))

#Reset eng_des index
image = image.reset_index()
#Drop old index column.
image = image.drop(['number'], axis=1)
image['ID'] = number

#Create Series od number as a new index.
max_index = eng_des.index.values.max()
index_start = max_index + 1
index_end = index_start + image.shape[0]
number = pd.Series(range(index_start, index_end))
image['number'] = number
image = image.set_index(number)
#Drop unused column.
image = image.drop(['number'], axis=1)

"""
Explanation: Set new ID and index number.
End of explanation
"""


wp_posts_with_eng = pd.concat([wp_posts, eng_des, image], axis=0)

"""
Explanation: Concatenate to "wp_posts"
Concatenate "wp_posts" and "eng_des" to the old wp_posts.
End of explanation
"""


wp_posts_with_eng = wp_posts_with_eng.sort_values('ID') 

"""
Explanation: Arrange index.
End of explanation
"""


wp_posts_with_eng.to_csv('product_import_to_woo/wp_posts_with_eng.csv', encoding='utf-8', index=False)

"""
Explanation: Export new raw_product and upload to Woocommerce.
End of explanation
"""


wp_postmeta = pd.read_csv('sql_prestashop/wp_postmeta.csv', index_col=False)
meta = pd.read_csv('sql_prestashop/wp_postmeta.csv', index_col=False)

"""
Explanation: Creating the "wp_postmeta"
Our process here is to create "wp_postmeta". "wp_post_meta" is a table collecting the detail of each post. In example "wp_postmeta" of product stores a SKU, price, weight and other information about product. Woocommerce isn't working properly without "wp_postmeta", so after we have already create "wp_posts", we have to generate "wp_postmeta".
We load "wp_postmeta.csv" that is the detail of each wp_post ID. We load wp_postmeta into 2 variable. 
1) "wp_postmeta" is use in the end to concatenate with a new English product's "wp_postmeta" or "meta" variable. 
2) "meta" is a new English product's "wp_postmeta"
End of explanation
"""


meta['number'] = meta['post_id']
meta = meta.set_index('number')
meta = meta.loc[th_id]

"""
Explanation: "meta" still have other posts. We need to extract only product, so we bring "meta" with the same ID as Thai products.
End of explanation
"""


meta['post_id'] = [dic[x] for x in meta['post_id']]

"""
Explanation: Translate old to new "ID".
End of explanation
"""


max_id = wp_postmeta['meta_id'].max()
#Create Series of number as a new "ID".
start = max_id + 1
end = start + meta.shape[0]
number = pd.Series(range(start, end))

#Reset eng_des index
meta = meta.reset_index()
#Drop old index column.
meta = meta.drop(['number'], axis=1)
meta['meta_id'] = number

#Set new index
#Create Series od number as a new index.
max_index = wp_postmeta.index.values.max()
index_start = max_index + 1
index_end = index_start + meta.shape[0]
number = pd.Series(range(index_start, index_end))
meta['number'] = number
meta = meta.set_index(number)
#Drop unused column.
meta = meta.drop(['number'], axis=1)

"""
Explanation: Set new ID and index number.
End of explanation
"""


wp_postmeta_with_eng = pd.concat([wp_postmeta, meta], axis=0)

"""
Explanation: Concatenate with the old wp_posts.
End of explanation
"""


wp_postmeta_with_eng = wp_postmeta_with_eng.sort_values('post_id') 

"""
Explanation: Arrange index.
End of explanation
"""


#Change SKU format.
for i in range(wp_postmeta_with_eng.shape[0]):
    if wp_postmeta_with_eng['meta_key'].iloc[i] == '_sku':
        wp_postmeta_with_eng['meta_value'].iloc[i] = 'A' + str(wp_postmeta_with_eng['meta_value'].iloc[i]).zfill(5)
        x = wp_postmeta_with_eng['meta_value'].iloc[i]

"""
Explanation: For a comfortable sorting the product in backend, We change the "SKU" into new format.
End of explanation
"""


wp_postmeta_with_eng.to_csv('product_import_to_woo/wp_postmeta_with_eng.csv', encoding='utf-8', index=False)

"""
Explanation: Export to .csv
End of explanation
"""


translations = pd.read_csv('sql_prestashop/wp_icl_translations.csv', index_col=False)

"""
Explanation: Creating the "wp_icl_translations"
Loading the "wp_icl_translations". Use wp_icl_translations to link Thai & Eng product description.
End of explanation
"""


thai_des = wp_posts[wp_posts['post_type'] == 'product']
thai_des = thai_des[['ID', 'post_title']]
eng_des = eng_des[['ID', 'post_title']]
tr = translations[translations['element_type'] == 'post_product']

"""
Explanation: Merging Thai & English Description to wp_icl_translations to make sure we match the correct ID.
End of explanation
"""


#Change column name for merging dataframe.
tr = tr.rename(columns = {'element_id':'ID'})

#Merge thai_des first. The result dataframe will have new "post_title" column.
tr = pd.merge(tr, thai_des, how='left', on='ID')

"""
Explanation: First, we merge Thai product to "wp_icl_translations".
End of explanation
"""


#Merge English description. Now we use "post_title" as a key.
tr = pd.merge(tr, eng_des, how='left', on='post_title')

#Rename ID_x and ID_y for comfortable use.
tr = tr.rename(columns = {'ID_x':'ID_th', 'ID_y':'ID_en'})

"""
Explanation: Then we merge it  with English product description by "post_title". The reason we use "post_title" as a key because Thai and English product don't share the same ID.
End of explanation
"""


tr = tr[np.isfinite(tr['ID_en'])]

"""
Explanation: Drop rows that English ID has a nan value.
End of explanation
"""


#Check for duplicate entry.
count = tr['ID_en'].value_counts()
#Drop duplicate name of product.
tr = tr.drop_duplicates(subset='ID_en')

"""
Explanation: Using the value_counts() to find duplicate values. If there are duplicate values, we drop it.
End of explanation
"""


#Find max "ID" of wp_icl_translations. Use max ID + 1 to be the starting ID of English description.
max_id = translations['translation_id'].max()
#Create Series of number as a new "ID".
start = max_id + 1
end = start + tr.shape[0]
number = pd.Series(range(start, end))

"""
Explanation: Create a series with the size of English products.
End of explanation
"""


#Create new dataframe in wp_icl_translations form. Then collect the processed
#English id's wp_icl_translations data.
dataframe = pd.DataFrame({
'translation_id': number,
'element_type' : 'post_product',
'element_id': tr['ID_en'].values,
'trid': tr['trid'].values,
'language_code': 'en',
'source_language_code': 'th'}, 
columns=['translation_id', 'element_type', 'element_id', 'trid', 'language_code', 'source_language_code'])

"""
Explanation: Creating a new dataframe in the format of wp_icl_translations.
End of explanation
"""


#Set new index, so it will continue from the last index in wp_icl_translations.
start = translations.shape[0]
end = start + dataframe.shape[0]
number = pd.Series(range(start,end))
dataframe = dataframe.set_index(number)

"""
Explanation: Set new index for dataframe.
End of explanation
"""


wp_icl_translations = pd.concat([translations, dataframe])

"""
Explanation: Concatenating dataframe to "translations" and name it as wp_icl_translations
End of explanation
"""


wp_icl_translations["source_language_code"] = wp_icl_translations["source_language_code"].fillna('NULL')

"""
Explanation: "source_language_code" must fill with "NULL" in order to make Woocommerce working properly.
End of explanation
"""


wp_icl_translations.to_csv('product_import_to_woo/wp_icl_translations_to_import.csv', encoding='utf-8', index=False)

"""
Explanation: Export to .csv
End of explanation
"""


