## pip3 install mysql-connector-python
import mysql.connector as mysql

# enter your server IP address/domain name
HOST = "127.0.0.1" # or "domain.com"
# database name, if you want just to connect to MySQL server, leave it empty
DATABASE = "dev_pythia_db"
# this is the user you create
USER = "pythia_db_user_dev" ## "pythia_db_user_dev@cloudsqlproxy~35.203.252.44"
# user password
PASSWORD = "VnEP0eysjiXDHcfM"
# connect to MySQL server
_dbc = mysql.connect(host=HOST, database=DATABASE, user=USER, password=PASSWORD)
print("Connected to:", _dbc.get_server_info())
# enter your code here!


def get_data_from_db(cursor, sql):
    output = []
    cursor.execute(sql)
    row = cursor.fetchone()
    while row is not None:
        row_to_return = row.decode('utf-8') if isinstance(row, bytearray) else row
        output.append(row_to_return)
        row = cursor.fetchone()
    
    return output



cursor_ = _dbc.cursor()
#### cursor_.execute('''
####         SELECT count(dop.order_id) as FREQuency,
####             dop.product_id as product_id,
####             pb.brand_name,
####             pl.barcode, pl.skl_code, pl.eys_code,
####             IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description
####         FROM delivery_orders_products AS dop
####         LEFT JOIN delivery_orders AS do ON dop.order_id = do.id
####         LEFT JOIN product_list AS pl ON dop.product_id = pl.eys_code
####         LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code
####         LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code
####         INNER JOIN product_brands pb ON pl.brand_id = pb.id
####         GROUP BY dop.product_id
####         ORDER BY FREQuency DESC
####     ''')
#### 
#### results = cursor_.fetchall()
#### 
#### for rec in results :
####     print(rec)


sqlq = '''
    SELECT count(dop.order_id) as FREQuency,
        dop.product_id as product_id,
        pb.brand_name,
        pl.barcode, pl.skl_code, pl.eys_code,
        IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description
    FROM delivery_orders_products AS dop
    LEFT JOIN delivery_orders AS do ON dop.order_id = do.id
    LEFT JOIN product_list AS pl ON dop.product_id = pl.eys_code
    LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code
    LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code
    INNER JOIN product_brands pb ON pl.brand_id = pb.id
    GROUP BY dop.product_id
    ORDER BY FREQuency DESC
'''


results = get_data_from_db(cursor_, sqlq)

for r in results :
    if r[1] in [1176465, 1430400, 1220273] :
        print(r[1], r[6].split())














## prepare cloud-sql-proxy
## ---
## * install cloud-sql-proxy
##   : sudo wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O /usr/local/cloud_sql_proxy
##   : sudo chmod +x /usr/local/cloud_sql_proxy
##
## * prepare/export/publish/copy credentials ...
##   : sudo cp /some/path/to/cloudsqlproxy.json /usr/local
##
## * finaly run the database instance
##   : /usr/local/cloud_sql_proxy -instances=pythia-251711:europe-west4:pythia-db-eu=tcp:3306 -credential_file=cloudsqlproxy.json

## Queries
## ---
'''
-- ORDERS PER PRODUCT
SELECT count(dop.order_id) as FREQuency,
    dop.product_id as product_id,
    pb.brand_name,
    pl.barcode, pl.skl_code, pl.eys_code,
    IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description,
    pcs4.description AS productGroup
FROM delivery_orders_products AS dop
LEFT JOIN delivery_orders AS do ON dop.order_id = do.id
LEFT JOIN product_list AS pl ON dop.product_id = pl.eys_code
LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code
LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code
LEFT JOIN product_categories_sap_4 pcs4 ON pcs4.id = pl.product_category_sap_4
INNER JOIN product_brands pb ON pl.brand_id = pb.id
GROUP BY dop.product_id
ORDER BY FREQuency DESC
'''