# импорт библиотек
import pandas as pd
# опции отображения
pd.set_option('display.max_columns', 10)
pd.set_option('display.expand_frame_repr', False)
path_dataset = 'dataset/ecommerce_data.csv'
# Предварительная обработка датасета
def func_main(path_dataset: str):
    # Считываем датасет
    df = pd.read_csv(path_dataset, sep=',')
    # Приводим названия столбцов датасета к нижнему регистру
    list_col = list(map(str.lower, df.columns))
    df.columns = list_col
    # Избавляемся от времени и трансформируем строку-дату в правильный формат
    df['invoicedate'] = df['invoicedate'].apply(lambda x: x.split(' ')[0])
    df['invoicedate'] = pd.to_datetime(df['invoicedate'], format='%m/%d/%Y')
    # Рассчитываем сумму покупки по каждому товару
    df['amount'] = df['quantity'] * df['unitprice']
    # Удаляем ненужные для дальнейшего анализа столбцы
    df_result = df.drop(['invoiceno', 'quantity', 'unitprice', 'customerid'], axis=1)
    # Задаем порядок вывода столбцов для визуального контроля результата
    df_result = df_result[['invoicedate', 'country', 'stockcode', 'description', 'amount']]
    return df_result
# Таблица Продажи
def func_sale():
    tbl = func_main(path_dataset)
    df_sale = tbl.groupby(['invoicedate', 'country', 'stockcode'])['amount'].sum().reset_index()
    return df_sale
# Таблица Страны
def func_country():
    tbl = func_main(path_dataset)
    df_country = pd.DataFrame(sorted(pd.unique(tbl['country'])), columns=['country'])
    return df_country
# Таблица Товары
def func_product():
    tbl = func_main(path_dataset)
    df_product = tbl[['stockcode','description']].\
        drop_duplicates(subset=['stockcode'], keep='first').reset_index(drop=True)
    return df_product

# импорт библиотек
import pandas as pd
import sqlite3 as sq
from etl1 import func_country,func_product,func_sale
con = sq.connect('sale.db')
cur = con.cursor()
## Таблица Страны
# cur.executescript('''DROP TABLE IF EXISTS country;
#                     CREATE TABLE IF NOT EXISTS country (
#                     country_id INTEGER PRIMARY KEY AUTOINCREMENT,
#                     country TEXT NOT NULL UNIQUE);''')
# func_country().to_sql('country',con,index=False,if_exists='append')
## Таблица Товары
# cur.executescript('''DROP TABLE IF EXISTS product;
#                     CREATE TABLE IF NOT EXISTS product (
#                     product_id INTEGER PRIMARY KEY AUTOINCREMENT,
#                     stockcode TEXT NOT NULL UNIQUE,
#                     description TEXT);''')
# func_product().to_sql('product',con,index=False,if_exists='append')
## Таблица Продажи (основная)
# cur.executescript('''DROP TABLE IF EXISTS sale;
#                     CREATE TABLE IF NOT EXISTS sale (
#                     sale_id INTEGER PRIMARY KEY AUTOINCREMENT,
#                     invoicedate TEXT NOT NULL,
#                     country_id INTEGER NOT NULL,
#                     product_id INTEGER NOT NULL,
#                     amount REAL NOT NULL,
#                     FOREIGN KEY(country_id)  REFERENCES country(country_id),
#                     FOREIGN KEY(product_id)  REFERENCES product(product_id));''')
## Таблица Продажи (временная)
# cur.executescript('''DROP TABLE IF EXISTS sale_data_lake;
#                     CREATE TABLE IF NOT EXISTS sale_data_lake (
#                     sale_id INTEGER PRIMARY KEY AUTOINCREMENT,
#                     invoicedate TEXT NOT NULL,
#                     country TEXT NOT NULL,
#                     stockcode TEXT NOT NULL,
#                     amount REAL NOT NULL);''')
# func_sale().to_sql('sale_data_lake',con,index=False,if_exists='append')
## Перегружаем данные из вспомогательной таблицы (sale_data_lake) в основную (sale)
# cur.executescript('''INSERT INTO sale (invoicedate, country_id, product_id, amount)
#                     SELECT  sdl.invoicedate, c.country_id, pr.product_id, sdl.amount
#                     FROM sale_data_lake as sdl LEFT JOIN country as c ON sdl.country = c.country
#                                 LEFT JOIN product as pr ON sdl.stockcode = pr.stockcode
#                     ''')
## Очищаем вспомогательную таблицу
# cur.executescript('''DELETE FROM sale_data_lake''')
def select(sql):
  return pd.read_sql(sql,con)
sql = '''select *
        from (select s.invoicedate,
                      c.country,
                      pr.description,
                      round(s.amount,1) as amount
               from sale as s left join country as c on s.country_id = c.country_id
                            left join product as pr on s.product_id = pr.product_id)'''
print(select(sql))
cur.close()
con.close()

import pandas as pd
import sqlite3 as sq
con = sq.connect('C:/Users/Pavel/PycharmProjects/test/sale.db')
cur = con.cursor()
def select(sql):
  return pd.read_sql(sql,con)
sql = '''select *
        from (select s.invoicedate,
                      c.country,
                      pr.description,
                      replace(round(s.amount,1),'.',',') as amount
               from sale as s left join country as c on s.country_id = c.country_id
                            left join product as pr on s.product_id = pr.product_id)'''
tbl = select(sql)
print(tbl)