from flask import Flask, request, send_from_directory
import pandas as pd
import mysql.connector
from base64 import b64decode
from cryptography.hazmat.primitives.ciphers import Cipher, algorithms, modes
from cryptography.hazmat.primitives import padding
from cryptography.hazmat.backends import default_backend
import json, os

app = Flask(__name__)

def decrypt_data(encrypted_value, key):
    decoded_value = b64decode(encrypted_value)
    iv = decoded_value[:16]
    encrypted_data = decoded_value[16:]

    cipher = Cipher(algorithms.AES(key), modes.CBC(iv), backend=default_backend())
    decryptor = cipher.decryptor()
    decrypted = decryptor.update(encrypted_data) + decryptor.finalize()

    unpadder = padding.PKCS7(128).unpadder()
    decrypted = unpadder.update(decrypted) + unpadder.finalize()

    return json.loads(decrypted.decode('utf-8'))

@app.route('/export', methods=['GET'])
def export_data():
    config = {
        'user': 'root',
        'password': 'root',
        'host': 'localhost',
        'database': 'myapp2',
        'raise_on_warnings': True
    }

    cnx = mysql.connector.connect(**config)
    data = request.args

    query = "SELECT * FROM full_report WHERE 1=1"
    params = []

    if 'year' in data:
        query += " AND `year` = %s"
        params.append(data['year'])
    if 'month' in data:
        query += " AND `month` = %s"
        params.append(data['month'])

    df = pd.read_sql(query, cnx, params=params)
    cnx.close()

    filename = data.get('filename', 'report.xlsx')
    output_dir = 'storage/app/public'
    os.makedirs(output_dir, exist_ok=True)
    output_path = os.path.join(output_dir, filename)

    df.to_excel(output_path, index=False)

    return send_from_directory(output_dir, filename, as_attachment=True)

if __name__ == '__main__':
    app.run(debug=True)