import base64
import datetime
import json
import uuid
import streamlit as st
from streamlit_timeline import st_timeline
from sqlalchemy.sql import text

conn = st.experimental_connection(
    "db",
    type="sql",
    url="sqlite:///db.sqlite3",
)
with conn.session as session:
    # check if the table exists
    session.execute(text("""
        CREATE TABLE IF NOT EXISTS files (
            id VARCHAR(255),
            name VARCHAR(255),
            tags VARCHAR(255),
            content_type VARCHAR(255),
            date DATETIME DEFAULT CURRENT_TIMESTAMP,
            file LONGBLOB NOT NULL,
            PRIMARY KEY (id)
        )
    """))
    # commit the changes
    session.commit()

def get_files(tags: str = "", min_date: datetime.date = None, max_date: datetime.date = None):
    # get the files metadata from the database and search for files with the given tag
    with conn.session as session:
        if tags and min_date and max_date:
            result = session.execute(text("""
                SELECT id, name, tags, date
                FROM files
                WHERE (tags LIKE :tags AND date BETWEEN :min_date AND :max_date) OR (tags LIKE :tags AND date = :min_date OR tags LIKE :tags AND date = :max_date)
            """), {
                'tags': f'%{tags}%',
                'min_date': min_date,
                'max_date': max_date
            })
        elif tags:
            result = session.execute(text("""
                SELECT id, name, tags, date
                FROM files
                WHERE tags LIKE :tags
            """), {
                'tags': f'%{tags}%'
            })
        elif min_date and max_date:
            # get the files where date is between min_date and max_date or is equal to min_date or max_date
            result = session.execute(text("""
                SELECT id, name, tags, date
                FROM files
                WHERE (date BETWEEN :min_date AND :max_date) OR (date = :min_date OR date = :max_date)
            """), {
                'min_date': min_date,
                'max_date': max_date
            })
        else:
            result = session.execute(text("""
                SELECT id, name, tags, date
                FROM files
            """))
        return result.fetchall()

def get_file(id):
    # get the file from the database
    with conn.session as session:

        result = session.execute(text("""
            SELECT file
            FROM files
            WHERE id = :id
        """), {
            'id': id
        })
        file = result.fetchone()
        return file[0]

def get_timeline(tags: str = "", min_date: datetime.date = None, max_date: datetime.date = None):
    # get all the files from the database and format them for the timeline
    return [
            {
                "id": file[0],
                "content": f"{file[1]} - {file[2]}",
                "start": file[3],
                "name": file[1],
                "tags": file[2],
            }
            for file in get_files(
                tags=tag, 
                min_date=min_date, 
                max_date=max_date
            )
        ]


st.title('File sharing app')

# create an app that allows users to upload files and add tags to them
# the app should allow users to search for files by tag or date range
tag = st.text_input('Enter a tag to search for files')

min_date = datetime.date(1970, 1, 1)

# max range for the date picker
max_date = st.date_input('Enter the max date to search for files', value=datetime.date.today() + datetime.timedelta(days=1), min_value=min_date, max_value=datetime.date.today() + datetime.timedelta(days=1))

# min range for the date picker
min_date = st.date_input('Enter the min date to search for files', value=datetime.date(1970, 1, 1), min_value=datetime.date(1970, 1, 1), max_value=max_date)

# create a button to submit the form
if st.button('Search'):
    items = get_timeline(tags=tag, min_date=min_date, max_date=max_date)
else:
    items = get_timeline()


# the first page should be a timeline that would propegate different artifacts
# then they can filter using it to search or they can upload a new file

# get all the files from the database to display on the timeline
timeline = st_timeline(items, groups=[], options={}, height="300px")
st.subheader("Selected item")
# display the selected file
if timeline:
    # display a container for the file
    file_container = st.container()
    # display the file name
    file_container.markdown(f'**{timeline["name"]}**')
    # display the file tags
    file_container.markdown(f'*{"*, *".join(json.loads(timeline["tags"]))}*')
    # display the file date
    file_container.markdown(f'_{timeline["start"]}_')
    # display the file
    if timeline["name"] == "text":
        file_container.markdown(f"```{get_file(timeline['id'])}```")
    else:
        file_container.video(get_file(timeline["id"]))