import requests
import streamlit as st
import sqlite3
import pandas as pd

st.set_page_config('Price',menu_items={'About': "This is an *extremely* cool app!"})

con = sqlite3.connect("mydb.db")
cur = con.cursor()


menu=['Find price']
ch=st.sidebar.selectbox('Menu',menu)


if 'Find price' in ch:
    with st.container():
        st.title('Search for price')
        ti=st.text_input('Search')
    if len(ti)>0:
        r=requests.get('https://www.pricerunner.dk/public/search/v3/dk?q='+ti)
        x=r.json()
        for i in x['products']:
            with st.container():
                lc , rc = st.columns(2)
                with lc:
                    add='https:/www.pricerunner.dk'+i['image']['path']
                    st.image(add)
                with rc:
                    id=i['id']
                    comp=i['url']
                    des=i['description']
                    low=i['lowestPrice']['amount']
                    name=i['name']


                    r2=requests.get('https://www.pricerunner.dk/public/productlistings/v3/pl/2-'+id+'/dk/filter')
                    x2=r2.json()
                    x3=x2['filteredOfferList']['merchantOffers'][0]['offers'][0]['url']
                    link=('https://www.pricerunner.dk'+ x3)

                    st.header(name)
                    st.write(des)
                    st.success(low+' DKK')
                    st.write('Link to buy:')
                    st.write('https://www.pricerunner.dk'+ x3)

                    cur.execute("insert into proucts values(?,?,?,?)", (name,low,des,link))
                    con.commit()

                st.write('---')

        df = pd.read_sql('SELECT * FROM proucts ', con)
        h = df.to_csv(sep=',')
        c = st.download_button("Download csv file",h,'price.csv','text/csv',)

        if c :
            cur.execute("DELETE from proucts ")
            con.commit()
            st.warning('Database is empty now again,try with new search again')


con.close()