from typing import Optional
import pandas as pd
import streamlit as st
import os
from sqlmodel import Field, Session, SQLModel, create_engine, select


class Hero(SQLModel, table=True):
    __table_args__ = {'extend_existing': True}  # required for streamlit refreshing
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None


sqlite_file_name = 'database.db'
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    hero_1 = Hero(name="Rusty-Man", secret_name="<NAME>", age=36)
    hero_2 = Hero(name="Tarantula", secret_name="<NAME>", age=26)
    hero_3 = Hero(name="<NAME>", secret_name="<NAME>", age=33)
    with Session(engine) as session:
        session.add(hero_1)
        session.add(hero_2)
        session.add(hero_3)
        session.commit()


def get_db_size():
    with Session(engine) as session:
        heroes = session.exec(select(Hero)).all()
    return len(heroes)


def select_heros():
    with Session(engine) as session:
        statement = select(Hero).where(Hero.age <= 35)
        results = session.exec(statement)
        for hero in results:
            st.text(hero)
        #st.text(len(results))


def show_table():
    with 