import streamlit as st
import pandas as pd
import base64
import hashlib
import hmac
import base64
import datetime
import requests
import json
import time as t


#githubテストコメント

# DB Managment
import sqlite3

#conn_log = sqlite3.connect('data.db')
#c = conn_log.cursor()

#conn = sqlite3.connect('Jdream_url.db')
#c_jd = conn.cursor()

# def create_usertable():
#     c.execute('CREATE TABLE IF NOT EXISTS userstable(username TEXT, password TEXT)')

# def add_userdata(username, password):
#     c.execute('INSERT INTO userstable(username, password) VALUES (?,?)', (username,password))
#     conn_log.commit()

# def login_user(username, password):
#     c.execute('SELECT * FROM userstable WHERE username = ? AND password = ?', (username,password))
#     data = c.fetchall()
#     return data

# def view_all_users():
#     c.execute('SELECT * FROM userstable')
#     data = c.fetchall()
#     return data

# def file_downloader(filename, file_label='File'):
#     with open(filename, 'rb') as f:
#         data = f.read()

def JDream_api_response(name, id):
    dt_now = datetime.datetime.now()
    time = dt_now.strftime('%Y%m%d%H%M%S')

    secretKey = 'HJD0scmQgboBkOp6nzu9aVEJryLYVXh3'
    #date = '20221128162000'

    signature = hmac.new(key=bytes(secretKey,'utf-8'), msg=bytes(id+':'+time,'utf-8'), digestmod=hashlib.sha256).digest()
    base64_signature = base64.b64encode(signature).decode()

    url = "https://expert.jdream3.com/app/external_api/supply/auth/info"
    payload = {
        "user_id":id, 
        "token":base64_signature, 
        "time":time,
        "authors":[{"name" : name}]
        }
    headers = {'Content-type':"application/json"}

    t.sleep(1)
    r = requests.post(url, data=json.dumps(payload), headers=headers)

    return r

def main():
    """ Simple Login App """

    st.title("Excel for FileMaker データ更新APP")


    #menu  = ["Home","Login","SignUp"]
    menu = ['Update']
    choice = st.sidebar.selectbox("Menu", menu)

    #JD_data_df = pd.read_sql_query('SELECT * FROM Jdream_url', conn)
    #submit_btn_DB = st.button('DBのカラム')

    #ボタンが押されたら処理を実行する
    #if submit_btn_DB:
        #st.table(JD_data_df.columns)

    if choice == "Update":
        #st.subheader("")
        #username = st.sidebar.text_input("User Name")
        # #password = st.sidebar.text_input("Password", type='password')
        # if st.sidebar.checkbox("Login"):
        #     create_usertable()
        #     result = login_user(username, password)
        #     if result:
        #         st.success("Logged In as {}".format(username))


        #         task = st.selectbox("Please Select Task", ["Update DB"])
        #         if task == "Add Post":
        #             st.subheader("Add Your Post")

        #         elif task == "Analytics":
        #             st.subheader("Analytics")

        #         elif task == "Profiles":
        #             st.subheader("Profiles")
        #             user_result = view_all_users()
        #             clean_db = pd.DataFrame(user_result, columns=["username","Password"])
        #             st.dataframe(clean_db)

        #if task == "Update DB":
        id = st.text_input('JDreamIDを入力', 'xxx')
        old_uploaded_excel = st.file_uploader('古いファイルをアップロード', type='xlsx')
        new_uploaded_excel = st.file_uploader('新しいファイルをアップロード', type='xlsx')
        submit_btn_xlsx = st.button('処理実行')
        #ボタンが押されたら処理を実行する
        if submit_btn_xlsx:
            #st.write(pd.read_excel(uploaded_excel))
            old_df = pd.read_excel(old_uploaded_excel)
            #old_df = old_df[['職員番号','氏名','名前カナ','部局名','職名','リンクコピー']]
            new_df = pd.read_excel(new_uploaded_excel)

            new_researcher_df = new_df[~(new_df['職員番号'].isin(old_df['職員番号'].unique()))]

            #JDのAPIでリンクを作成
            JDream_url = 'https://expert.jdream3.com/app/author/detail/'
            JDream_user_url_list = []

            for name in new_researcher_df['氏名']:
                #API接続
                try:
                    r = JDream_api_response(name, id)
                    author_id = json.loads(r.text)['data'][0]['authorId']
                    organization = json.loads(r.text)['data'][0]['organization']
                    if organization == '北海道大学':
                    #JDream接続リンクを作成
                        JDream_user_url = JDream_url + author_id
                    else:
                        JDream_user_url = ''
                except:
                    JDream_user_url = ''
                
                JDream_user_url_list.append(JDream_user_url)

            new_researcher_df['リンクコピー'] = JDream_user_url_list

            st.write('追加される研究者')
            st.dataframe(new_researcher_df)

            
            csv = new_researcher_df.to_csv(index=False)
            #st.table(csv_file)
            #csv = df.to_csv(index=False) 
            b64 = base64.b64encode(csv.encode('utf-8-sig')).decode()
            href = f'<a href="data:application/octet-stream;base64,{b64}" download="result_utf-8-sig.csv">CSVファイルをダウンロード</a>'
            #st.markdown(f"CSVファイルのダウンロードはこちら:  {href}", unsafe_allow_html=True)
            st.markdown(f"{href}", unsafe_allow_html=True)

                    
            # else:
            #     st.warning("Incorrect Username/Password")


        



    # elif choice == "Login":
    #     st.subheader("Login Section")

    #     username = st.sidebar.text_input("User Name")
    #     password = st.sidebar.text_input("Password", type='password')
    #     if st.sidebar.checkbox("Login"):
    #         #if password == '12345':
    #         create_usertable()
    #         result = login_user(username, password)
    #         if result:
    #             st.success("Logged In as {}".format(username))

    #             task = st.selectbox("Task", ["Add Post", "Analytics","Profiles"])
    #             if task == "Add Post":
    #                 st.subheader("Add Your Post")

    #             elif task == "Analytics":
    #                 st.subheader("Analytics")

    #             elif task == "Profiles":
    #                 st.subheader("Profiles")
    #                 user_result = view_all_users()
    #                 clean_db = pd.DataFrame(user_result, columns=["username","Password"])
    #                 st.dataframe(clean_db)
    #         else:
    #             st.warning("Incorrect Username/Password")


    # elif choice == "SignUp":
    #     st.subheader("Create New Account")
    #     new_user = st.text_input("Username")
    #     new_password = st.text_input("Password", type='password')

    #     if st.button("Signup"):
    #         create_usertable()
    #         add_userdata(new_user, new_password)

    #         st.success("You have successfully created a valid Account")
    #         st.info("Go to Login Menu to login")


if __name__ == '__main__':
    main()