from flask import Flask, render_template, request, send_file
import pandas as pd
import os
from werkzeug.utils import secure_filename
from AppCode.FileHandler import FileHandler
from AppCode.ServerPort import CompGSTServer
from AppCode.compare_excel import Comparison

app = Flask(__name__)

# check file Folder valid
FileHandler.check_or_create_folder_exists()

# upload page show 
@app.route('/')
def index():
    return render_template('upload.html')

# camparison route
@app.route('/upload', methods=['POST'])
def upload_file():
    file = request.files.get('excel_file')
    if not file:
        return "No file uploaded.", 400

    filename = secure_filename(file.filename)
    filepath = os.path.join(FileHandler.UPLOAD_FOLDER, filename)
    file.save(filepath)

    try:
        # Read first two sheets
        df1 = pd.read_excel(filepath, sheet_name=0, header=0)
        df2 = pd.read_excel(filepath, sheet_name=1, header=0)

        # Get unmatched rows separately
        unmatched_sheet1, unmatched_sheet2 = Comparison.find_unmatched_rows(df1, df2)

        # Write result into two Excel sheets
        with pd.ExcelWriter(FileHandler.RESULT_FILE, engine="openpyxl") as writer:
            unmatched_sheet1.to_excel(
                writer, sheet_name="Not In Tally", index=False
            )
            unmatched_sheet2.to_excel(
                writer, sheet_name="Not In Portal", index=False
            )

        return send_file(FileHandler.RESULT_FILE, as_attachment=True)

    except Exception as e:
        return f"Error processing file: {e}", 500

# run
if __name__ == '__main__':
    app.run(host=CompGSTServer.host, port=CompGSTServer.port, debug=True)