src/streamlit_app.py

# (c) Timeless Wind Publishing // Proprietary Information

import streamlit as st
import tempfile
from pathlib import Path
import hashlib
import pandas as pd
import io
from accounting_program import generate_report
from openpyxl.styles import Font, PatternFill
import os


# Password protection
def check_password():
    """Returns `True` if the user has entered the correct password."""
    
    def password_entered():
        """Checks whether a password entered by the user is correct."""
        # Hash the password for security
        entered_password = st.session_state["password"]
        # Change this to your desired password (hashed)
        # To generate hash: hashlib.sha256("your_password".encode()).hexdigest()
        correct_password_hash = os.environ.get("PASSWORD_HASH")
        
        if hashlib.sha256(entered_password.encode()).hexdigest() == correct_password_hash:
            st.session_state["password_correct"] = True
            del st.session_state["password"]  # Don't store password
        else:
            st.session_state["password_correct"] = False

    # First run or password not correct
    if "password_correct" not in st.session_state:
        st.text_input(
            "🔒 Enter Password",
            type="password",
            on_change=password_entered,
            key="password"
        )
        st.info("Please enter the password to access the application.")
        return False
    elif not st.session_state["password_correct"]:
        st.text_input(
            "🔒 Enter Password",
            type="password",
            on_change=password_entered,
            key="password"
        )
        st.error("😕 Password incorrect")
        return False
    else:
        return True

def create_download_dataframe(final_royalties, include_total=True):
    """Create a formatted dataframe for download"""
    grouped = final_royalties.groupby(['Author', 'Title'], as_index=False)['Adjusted_Earnings'].sum()
    
    # Create download dataframe
    download_df = grouped.copy()
    download_df.columns = ['Author', 'Title', 'Earnings']
    
    # Add a total row only if requested (for CSV)
    if include_total:
        total_earnings = final_royalties["Adjusted_Earnings"].sum()
        total_row = pd.DataFrame({
            'Author': ['TOTAL'],
            'Title': [''],
            'Earnings': [total_earnings]
        })
        download_df = pd.concat([download_df, total_row], ignore_index=True)
    
    return download_df

def display_report(royalties_path, payments_path):
    """Display the accounting report in Streamlit format"""
    final_royalties = generate_report(royalties_path, payments_path)
    
    # Store in session state for download
    st.session_state['report_data'] = final_royalties
    
    grouped = final_royalties.groupby(['Author', 'Title'], as_index=False)['Adjusted_Earnings'].sum()
    
    # Display total earnings
    total_earnings = final_royalties["Adjusted_Earnings"].sum()
    st.subheader("📊 Report Summary")
    st.metric("Total Earnings", f"${total_earnings:,.2f}")
    
    st.divider()
    
    # Display earnings by author
    st.subheader("📚 Earnings by Author")
    
    for author, group in grouped.groupby('Author'):
        author_total = group['Adjusted_Earnings'].sum()
        
        with st.expander(f"**{author}** - ${author_total:,.2f}", expanded=True):
            # Create a dataframe for this author's books
            books_data = []
            for _, row in group.iterrows():
                books_data.append({
                    'Title': row['Title'],
                    'Earnings': f"${row['Adjusted_Earnings']:,.2f}"
                })
            
            # Display as a clean table
            st.dataframe(
                books_data,
                use_container_width=True,
                hide_index=True
            )
            
            st.caption(f"Total for {author}: ${author_total:,.2f}")
    
    # Download section
    st.divider()
    st.subheader("📥 Download Report")
    
    col1, col2 = st.columns(2)
    
    with col1:
        # Excel download with pivot table
        excel_buffer = io.BytesIO()
        
        # Create dataframe without total row for Excel
        download_df_excel = create_download_dataframe(final_royalties, include_total=False)
        
        # Create a pivot table summary using pandas
        pivot_df = download_df_excel.copy()
        
        # Create author subtotals
        author_totals = pivot_df.groupby('Author')['Earnings'].sum().reset_index()
        author_totals['Title'] = 'SUBTOTAL'
        
        # Sort and combine data with subtotals
        pivot_display = []
        for author in pivot_df['Author'].unique():
            # Add books for this author
            author_books = pivot_df[pivot_df['Author'] == author].copy()
            for _, row in author_books.iterrows():
                pivot_display.append({
                    'Author': row['Author'],
                    'Title': row['Title'],
                    'Earnings': row['Earnings']
                })
            
            # Add subtotal row
            author_total = author_totals[author_totals['Author'] == author]['Earnings'].values[0]
            pivot_display.append({
                'Author': author,
                'Title': '  → Subtotal',
                'Earnings': author_total
            })
        
        # Add grand total
        grand_total = pivot_df['Earnings'].sum()
        pivot_display.append({
            'Author': 'GRAND TOTAL',
            'Title': '',
            'Earnings': grand_total
        })
        
        pivot_summary = pd.DataFrame(pivot_display)
        
        with pd.ExcelWriter(excel_buffer, engine='openpyxl') as writer:
            # Write main data sheet
            download_df_excel.to_excel(writer, index=False, sheet_name='Earnings Data')
            
            # Write pivot table summary
            pivot_summary.to_excel(writer, index=False, sheet_name='Pivot Summary')
            
            # Format the pivot summary sheet
            workbook = writer.book
            pivot_sheet = workbook['Pivot Summary']
            
            # Bold the header row
            for cell in pivot_sheet[1]:
                cell.font = cell.font.copy(bold=True)
            
            # Bold subtotal and grand total rows
            for row in pivot_sheet.iter_rows(min_row=2):
                if row[1].value and ('Subtotal' in str(row[1].value) or row[0].value == 'GRAND TOTAL'):
                    for cell in row:
                        cell.font = Font(bold=True)
                        if row[0].value == 'GRAND TOTAL':
                            cell.fill = PatternFill(start_color='E0E0E0', end_color='E0E0E0', fill_type='solid')
        
        excel_buffer.seek(0)
        
        st.download_button(
            label="📊 Download as Excel",
            data=excel_buffer,
            file_name="twp_earnings_report.xlsx",
            mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            use_container_width=True
        )
    
    with col2:
        # CSV download with total row
        download_df_csv = create_download_dataframe(final_royalties, include_total=True)
        csv_buffer = io.StringIO()
        download_df_csv.to_csv(csv_buffer, index=False)
        
        st.download_button(
            label="📄 Download as CSV",
            data=csv_buffer.getvalue(),
            file_name="twp_earnings_report.csv",
            mime="text/csv",
            use_container_width=True
        )

def main():
    # Check password first
    if not check_password():
        st.stop()  # Don't continue if password is wrong
    
    st.title("TWP Accounting Report Generator")
    st.write("Upload your royalties and payouts files to generate a report")
    
    # Create two columns for file uploaders
    col1, col2 = st.columns(2)
    
    with col1:
        royalties_file = st.file_uploader(
            "Upload Royalties File",
            type=['csv', 'xlsx', 'xls'],
            help="Select your royalties data file"
        )
    
    with col2:
        payouts_file = st.file_uploader(
            "Upload Payouts File",
            type=['csv', 'xlsx', 'xls'],
            help="Select your payouts data file"
        )
    
    # Process button
    if st.button("Generate Report", type="primary", disabled=not (royalties_file and payouts_file)):
        if royalties_file and payouts_file:
            with st.spinner("Processing files..."):
                # Create temporary files to save uploads
                with tempfile.TemporaryDirectory() as tmp_dir:
                    # Save uploaded files to temp directory
                    royalties_path = Path(tmp_dir) / royalties_file.name
                    payouts_path = Path(tmp_dir) / payouts_file.name
                    
                    with open(royalties_path, 'wb') as f:
                        f.write(royalties_file.getbuffer())
                    
                    with open(payouts_path, 'wb') as f:
                        f.write(payouts_file.getbuffer())
                    
                    # Display processing info
                    st.info(f"Processing: {royalties_file.name} and {payouts_file.name}")
                    
                    # Generate and display the report
                    try:
                        display_report(str(royalties_path), str(payouts_path))
                        st.success("Report generated successfully!")
                        
                    except Exception as e:
                        st.error(f"Error generating report: {str(e)}")
                        st.exception(e)
    
    # Instructions
    with st.expander("ℹ️ Instructions"):
        st.markdown("""
        1. Upload your **Royalties File** using the left file uploader
        2. Upload your **Payouts File** using the right file uploader
        3. Click **Generate Report** to process the files
        4. View the results below
        5. Download the report as Excel or CSV using the download buttons
        
        **Supported file formats:** CSV, Excel (.xlsx, .xls)
        """)

if __name__ == "__main__":
    main()