"""
# DATA VISUALIZATION & DATA AGGREGATION WITH PYTHON & SQL
[Muhammad Rifki](https://www.linkedin.com/in/muhammadrifki/) - January 2021
"""
"""
The objective of this notebook is merely an exercise to showcase some required skills for a Business Intelligence like SQL query and Python language. Thus, I do some simple data visualization & aggregation of e-commerce in Brazil.
"""
# Standard libraries
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import glob
import plotly.express as px
import os
import warnings

# Local server SQL database
import sqlite3 as sq

# Setting of Large numbers format
pd.options.display.float_format = '{:,.2f}'.format

# Set data frame display max 10 rows
pd.set_option('display.max_rows', 10)

# Warning is suppressed
warnings.simplefilter(action='ignore', category=FutureWarning)
# Set-up a connection to a newly named project1.db
con = sq.connect('project1.db')
cur = con.cursor()
# Get the count of customers_dataset table
cur.execute(''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='../input/brazilian-ecommerce/olist_customers_dataset.csv' OR name='customers_dataset' ''')

# If the count is 1, then customers_dataset table already exists
if cur.fetchone()[0]==1 : {
	print('Table already created.')
}
else: # Read all files from csv to db format
    path = '../input/brazilian-ecommerce'
    all_files = glob.glob(path + "/*.csv")
    for file in all_files: # For all files in our directory
        df = pd.read_csv(file, index_col=0) # Read each CSV file
        df.to_sql(file, con) # Create the read file as a table in the database.
# Get the count of customers_dataset table
cur.execute(''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='customers_dataset' ''')

# If the count is 1, then customers_dataset table already renamed
if cur.fetchone()[0]==1:
	print('Table already renamed.')

else:
    # Rename all tables
    rename_tables_query1 = 'ALTER TABLE "../input/brazilian-ecommerce/olist_customers_dataset.csv" RENAME TO "customers_dataset"'
    rename_tables_query2 = 'ALTER TABLE "../input/brazilian-ecommerce/olist_geolocation_dataset.csv" RENAME TO "geolocation_dataset"'
    rename_tables_query3 = 'ALTER TABLE "../input/brazilian-ecommerce/olist_orders_dataset.csv" RENAME TO "orders_dataset"'
    rename_tables_query4 = 'ALTER TABLE "../input/brazilian-ecommerce/olist_order_items_dataset.csv" RENAME TO "order_items_dataset"'
    rename_tables_query5 = 'ALTER TABLE "../input/brazilian-ecommerce/olist_order_payments_dataset.csv" RENAME TO "order_payments_dataset"'
    rename_tables_query6 = 'ALTER TABLE "../input/brazilian-ecommerce/olist_order_reviews_dataset.csv" RENAME TO "order_reviews_dataset"'
    rename_tables_query7 = 'ALTER TABLE "../input/brazilian-ecommerce/olist_products_dataset.csv" RENAME TO "products_dataset"'
    rename_tables_query8 = 'ALTER TABLE "../input/brazilian-ecommerce/olist_sellers_dataset.csv" RENAME TO "sellers_dataset"'
    rename_tables_query9 = 'ALTER TABLE "../input/brazilian-ecommerce/product_category_name_translation.csv" RENAME TO "product_category_name_translation"'

    # The function read_sql takes a query string and a database connection, and performs the query.
    rename_tables1 = pd.read_sql(rename_tables_query1, con)
    rename_tables2 = pd.read_sql(rename_tables_query2, con)
    rename_tables3 = pd.read_sql(rename_tables_query3, con)
    rename_tables4 = pd.read_sql(rename_tables_query4, con)
    rename_tables5 = pd.read_sql(rename_tables_query5, con)
    rename_tables6 = pd.read_sql(rename_tables_query6, con)
    rename_tables7 = pd.read_sql(rename_tables_query7, con)
    rename_tables8 = pd.read_sql(rename_tables_query8, con)
    rename_tables9 = pd.read_sql(rename_tables_query9, con)
# Read all table names
table_list = [a for a in cur.execute("SELECT name FROM sqlite_master WHERE type = 'table'")]

# Table list
print(table_list)
# Write a SQL query of Total Orders and Total Sales
q1 = (
      'SELECT count(a.order_id) AS Total_Orders, '
      '       sum(b.price + b.freight_value) AS Total_Sales '
      'FROM orders_dataset AS a '
      'INNER JOIN order_items_dataset AS b '
      'ON a.order_id = b.order_id '
      )

# Convert the SQL query to Pandas data Frame
r1 = pd.read_sql(q1, con)
r1
# Write a SQL query of Orders by Time
q2 = (
      'SELECT strftime("%Y-%m", order_approved_at) AS date, '
      '       COUNT(order_id) AS order_qty '
      'FROM orders_dataset '
      'GROUP BY date '
      'ORDER BY date '
      )

# Convert the SQL query to Pandas data Frame
r2 = pd.read_sql(q2, con)
r2.head(5)
# Plotting orders by time data 
fig1 = px.bar(r2, x="date", y="order_qty", orientation='v', title='Orders by Date in Brazilian E-Commerce (2016-2018)')
fig1.show()
# Write a SQL query of Sales by Time
q3 = (
      'SELECT strftime("%Y-%m", a.order_approved_at) AS date, '
      '       SUM(b.price) AS sales '
      'FROM orders_dataset AS a '
      'INNER JOIN order_items_dataset AS b '
      'ON a.order_id = b.order_id '
      'GROUP BY date '
      'ORDER BY date '
      )

# Convert the SQL query to Pandas data Frame
r3 = pd.read_sql(q3, con)
r3.head(5)
# Plotting sales by time data 
fig2 = px.line(r3, x="date", y="sales", title='Sales by Time in Brazilian E-Commerce (2016-2018)')
fig2.show()
# Write a SQL query of Selling Product Categories Quantity
q4 = (
      'SELECT a.product_category_name_english AS product, '
      '       COUNT(b.product_category_name) AS qty '
      'FROM product_category_name_translation AS a '
      'INNER JOIN products_dataset AS b '
      'ON a.product_category_name = b.product_category_name '
      'GROUP BY product '
      'ORDER BY qty DESC '
      )

# Convert the SQL query to Pandas data Frame
r4 = pd.read_sql(q4, con)
r4_top = r4.head(5)
r4_top
# Plotting selling product categories quantity data
fig3 = px.bar(r4_top, x="qty", y="product", orientation='h', barmode="group", title='Top 5 Selling Product Categories in Brazilian E-Commerce (2016-2018)')
fig3.update_layout(yaxis={'categoryorder':'total ascending'})
fig3.show()
# Write a SQL query of Sellers by City
q5 = (
      'WITH temp_sellers AS '
      '( '
      '      SELECT a.seller_city AS seller_city, '
      '      COUNT(b.order_id) AS sales_qty, '
      '      COUNT(b.order_id) * 100.0 / SUM(COUNT(b.order_id)) OVER () AS temp_sales_percentage '
      '      FROM sellers_dataset AS a '
      '      INNER JOIN order_items_dataset AS b '
      '      ON a.seller_id = b.seller_id '
      '      GROUP BY seller_city '
      '      ORDER BY sales_qty DESC '
      ') '
      'SELECT seller_city, '
      '       sales_qty, '
      '       printf("%.2f", temp_sales_percentage) AS sales_percentage '
      'FROM temp_sellers '
      )

# Convert the SQL query to Pandas data Frame
r5 = pd.read_sql(q5, con)
r5_top = r5.head(5)
r5_top
# Plotting selling sellers by city data
fig4 = px.bar(r5_top, x="sales_qty", y="seller_city", orientation='h', hover_data=['sales_percentage'], color='sales_percentage', title='Top 5 Sellers by City in Brazilian E-Commerce (2016-2018)')
fig4.update_layout(yaxis={'categoryorder':'total ascending'})
fig4.show()
# Write a SQL query of Customers by City
q6 = (
      'WITH temp_customer AS '
      '( '
      '      SELECT a.customer_city AS customer_city, '
      '      COUNT(b.order_id) AS order_qty, '
      '      COUNT(b.order_id) * 100.0 / SUM(COUNT(b.order_id)) OVER () AS temp_order_percentage '
      '      FROM customers_dataset AS a '
      '      INNER JOIN orders_dataset AS b '
      '      ON a.customer_id = b.customer_id '
      '      GROUP BY customer_city '
      '      ORDER BY order_qty DESC '
      ') '
      'SELECT customer_city, '
      '       order_qty, '
      '       printf("%.2f", temp_order_percentage) AS order_percentage '
      'FROM temp_customer '
      )

# Convert the SQL query to Pandas data Frame
r6 = pd.read_sql(q6, con)
r6_top = r6.head(5)
r6_top
# Plotting selling customer by city data
fig5 = px.bar(r6_top, x="order_qty", y="customer_city", orientation='h', hover_data=['order_percentage'], color='order_percentage', title='Top 5 Customers by City in Brazilian E-Commerce (2016-2018)')
fig5.update_layout(yaxis={'categoryorder':'total ascending'})
fig5.show()
# Write a SQL query of Average, Max, and Min Products
q7 = (
      'SELECT AVG(price) AS Average_Price, '
      '       MAX(price) AS Max_Price, '
      '       MIN(price) AS Min_Price '
      'FROM order_items_dataset '
      )

# Convert the SQL query to Pandas data Frame
r7 = pd.read_sql(q7, con)
r7
q8 = (
      'WITH average_time AS '
      '( '
      '     SELECT julianday(order_estimated_delivery_date) - julianday(order_delivered_customer_date) AS delivery_time '
      '     FROM orders_dataset '
      '     WHERE order_status = "delivered" '
      ') '
      'SELECT AVG(delivery_time) AS "Average_Delivery_Time_Interval_(Estimated_vs_Actual)" '
      'FROM average_time '
      )

# Convert the SQL query to Pandas data Frame
r8 = pd.read_sql(q8, con)
r8
# Write a SQL query of Delivery Interval (Estimated vs Actual) per Month
q9 = (
      'WITH average_time AS '
      '( '
      '     SELECT strftime("%Y-%m", order_delivered_customer_date) AS date, '
      '            julianday(order_estimated_delivery_date) - julianday(order_delivered_customer_date) AS delivery_time, '
      '            COUNT(order_id) AS qty'
      '     FROM orders_dataset '
      '     WHERE order_status = "delivered" '
      '     GROUP BY date '
      '     ORDER BY date '
      ') '
      'SELECT date AS Date, delivery_time AS Day, qty AS Qty '
      'FROM average_time '
      )

# Convert the SQL query to Pandas data Frame
r9 = pd.read_sql(q9, con)
r9
# Plotting Delivery Interval (Estimated vs Actual) per Month data 
fig6 = px.bar(r9, x="Date", y="Day", orientation='v', hover_data=['Qty'], color='Qty', title='Delivery Interval (Estimated vs Actual) per Month in<br>Brazilian E-Commerce (2016-2018)')
fig6.show()
# Close connection to Database
con.close()