def main():
st.title("SQLite Database Browser")

# Get list of valid database files in the directory
base_dir = r'C:\Users\dejan\Desktop\chrome hacking\User 1'
db_files = [
file for file in os.listdir(base_dir)
if os.path.isfile(os.path.join(base_dir, file)) and
not file.endswith('-journal') and
f"{file}-journal" in os.listdir(base_dir)
]

# Let user select the database file
selected_db = st.selectbox("Select Database File", db_files)

if selected_db:
db_path = os.path.join(base_dir, selected_db)
st.write(f"Selected Database: **{selected_db}**")

# Connect to the SQLite database
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# Get list of tables in the database
tables = cursor.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall()
tables = [table[0] for table in tables]

# Display a summary table showing the number of records in each table
summary_data = []
for table in tables:
count = cursor.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
summary_data.append({"Table Name": table, "Record Count": count})

summary_df = pd.DataFrame(summary_data)
st.write("**Summary of Tables:**")
st.dataframe(summary_df)

# Select table
selected_table = st.selectbox("Select Table", tables)

if selected_table:
# Fetch and display schema for selected table
schema = cursor.execute(f"PRAGMA table_info({selected_table});").fetchall()
st.write(f"Schema for {selected_table} table:")
st.write(schema)

# Display summary for the selected table
column_summary = []
for col in schema:
col_name = col[1]
non_null_count = cursor.execute(f"SELECT COUNT({col_name}) FROM {selected_table} WHERE {col_name} IS NOT NULL").fetchone()[0]
column_summary.append({"Column Name": col_name, "Non-Null Count": non_null_count})

column_summary_df = pd.DataFrame(column_summary)
st.write(f"**Summary for {selected_table} table:**")
st.dataframe(column_summary_df)

# User input for search query
search_query = st.text_input("Search by text")

# Modify SQL query based on search input
query = f"SELECT * FROM {selected_table}"
if search_query:
columns = [col[1] for col in schema]
search_conditions = " OR ".join([f"{col} LIKE '%{search_query}%'" for col in columns])
query += f" WHERE {search_conditions}"

# Fetch data
data = cursor.execute(query).fetchall()

# Pagination settings
page_size = 100
total_records = len(data)
total_pages = math.ceil(total_records / page_size)
page_number = st.number_input("Page number", min_value=1, max_value=total_pages, value=1)
start_index = (page_number - 1) * page_size
end_index = min(start_index + page_size, total_records)

st.write(data[start_index:end_index])

# Add button to download table data as CSV
if st.button("Download Table as CSV"):
csv_data = StringIO()
csv_writer = csv.writer(csv_data)
csv_writer.writerow([i[0] for i in cursor.description]) # Write headers
csv_writer.writerows(data) # Write data rows
csv_data.seek(0)
csv_bytes = csv_data.getvalue().encode()
st.download_button(label='Download CSV', data=csv_bytes, file_name=f'{selected_table}.csv', mime='text/csv')

# Add button to delete table
if st.button("Delete Table"):
cursor.execute(f"DROP TABLE IF EXISTS {selected_table};")
conn.commit()
st.success(f"Table '{selected_table}' deleted successfully.")

# Add button to empty table
if st.button("Empty Table"):
cursor.execute(f"DELETE FROM {selected_table};")
conn.commit()
st.success(f"Table '{selected_table}' emptied successfully.")

# Close database connection
conn.close()