import psycopg2
import pandas as pd
import numpy as np

files = ('customer_research.csv', 'user_orders_log.csv', 'user_activity_log.csv')

# df_order_log = pd.read_csv("https://storage.yandexcloud.net/s3-sprint3/cohort_1/aa-tolmachev/TWpBeU1pMHdOQzB5T1ZReE1Eb3lNam93TmdsaFlTMTBiMnh0WVdOb1pYWT0=/user_order_log.csv") )

for FILE_NAME in files:
    storage_url = f"https://storage.yandexcloud.net/s3-sprint3/cohort_2/pmkhlv/TWpBeU1pMHdOaTB4TUZReE56b3hNem8wT1Fsd2JXdG9iSFk9/{FILE_NAME}"
    df = pd.read_csv(storage_url)
    df.to_csv("/lessons/2. Анализ вводных по задаче/7. Использование файлов и подключение к БД/Задание 1/stage/" + FILE_NAME)





conn = psycopg2.connect("host='localhost' port='5432' dbname='de' user='jovyan' password='jovyan' ")
cur = conn.cursor()

customer_research = pd.read_csv('/lessons/2. Анализ вводных по задаче/7. Использование файлов и подключение к БД/Задание 1/stage/customer_research.csv').iloc[: , 1:]
user_activity_log = pd.read_csv('/lessons/2. Анализ вводных по задаче/7. Использование файлов и подключение к БД/Задание 1/stage/user_activity_log.csv').iloc[: , 1:]
user_order_log = pd.read_csv('/lessons/2. Анализ вводных по задаче/7. Использование файлов и подключение к БД/Задание 1/stage/user_order_log.csv').iloc[: , 1:]

print(customer_research.shape)

#insert_uol = "insert into staging.user_order_log (id, date_time, city_id, city_name, customer_id, first_name, last_name, item_id, item_name, quantity, payment_amount) VALUES {s_val};"
insert_cr = "insert into staging.customer_research (id, date_id, category_id, geo_id, sales_qty, sales_amt) VALUES {s_val};"
customer_research.insert(0, 'id', range(1, len(customer_research)+1))

#insert_ual = "insert into staging.user_activity_log (id, date_time, action_id, customer_id, quantity) VALUES {s_val};"


step = int(user_orders_log.shape[0] / 100)
while i <= user_orders_log.shape[0]:
    print(i, end='\r')
    s_val = str([tuple(x) for x in user_orders_log.loc[i:i + step].to_numpy()])[1:-1]
    cur.execute(insert_s.replace('{s_val}',s_val))
    conn.commit()
    i += step+1

CREATE TABLE staging.customer_research (
    id serial4 NOT NULL,
    date_id timestamp NULL,
    category_id int4 NULL,
    geo_id int4 NULL,
    sales_qty int4 NULL,
    sales_amt numeric(14, 2) NULL,
    PRIMARY KEY (id)
);-- staging.user_activity_log definition

-- Drop table

-- DROP TABLE staging.user_activity_log;

CREATE TABLE staging.user_activity_log (
    id serial4 NOT NULL,
    date_time timestamp NULL,
    action_id int8 NULL,
    customer_id int8 NULL,
    quantity int8 NULL,
    PRIMARY KEY (id)
);-- staging.user_order_log definition

-- Drop table

-- DROP TABLE staging.user_order_log;

CREATE TABLE staging.user_order_log (
    id serial4 NOT NULL,
    date_time timestamp NULL,
    city_id int4 NULL,
    city_name varchar(100) NULL,
    customer_id int8 NULL,
    first_name varchar(100) NULL,
    last_name varchar(100) NULL,
    item_id int4 NULL,
    item_name varchar(100) NULL,
    quantity int8 NULL,
    payment_amount numeric(14, 2) NULL,
    PRIMARY KEY (id)
);

CREATE TABLE prod.custom_research (
  date_id timestamp not null,
  geo_id bigint not null,
  sales_qty bigint not null,
  sales_amt bigint not null);

insert into prod.custom_research (date_id, geo_id, sales_qty, sales_amt)
select date_id, geo_id, sales_qty, sales_amt from staging.customer_research;
ALTER TABLE prod.custom_research RENAME TO customer_research;

CREATE TABLE prod.user_activity_log (
  Date_time timestamp not null,
  Customer_id bigint not null);

insert into prod.user_activity_log (Date_time, Customer_id)
select Date_time, Customer_id from staging.user_activity_log;

CREATE TABLE prod.user_order_log (
  Date_time timestamp not null,
  Customer_id bigint not null,
  Quantity bigint not null,
  Payment_amount numeric(14, 2) not null);

insert into prod.user_order_log (Date_time, Customer_id, Quantity, Payment_amount)
select Date_time, Customer_id, Quantity, Payment_amount from staging.user_order_log;