mid.py

from google.cloud import bigquery
from google.oauth2 import service_account
import streamlit as st
import json
import datetime
import imaplib
import email
import pandas as pd
import os
from query_mid import queries
import html
from ap import Webhook_urls
import requests

# Hardcoded email credentials (consider securing these in the backend instead of in the script)
EMAIL_USERNAME = "[email protected]"
EMAIL_PASSWORD = "etdv kmvs qbsi zose"

# Ensure a directory exists for storing attachments
ATTACHMENTS_DIR = "attachments"
os.makedirs(ATTACHMENTS_DIR, exist_ok=True)

# Custom button styles
button_styles = """
    <style>
        .download-button {
            color: #ffffff;
            font-size: 20px;
            background-image: linear-gradient(to right, #800000, #ff0000);
            border: none;
            padding: 10px 20px;
            cursor: pointer;
            border-radius: 15px;
            display: inline-block;
            box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1), 0 8px 15px rgba(0, 0, 0, 0.1);
            transition: all 0.3s ease;
        }
        .download-button:hover {
            background-color: #00ff00;
            color: #ff0000;
            box-shadow: 0 6px 10px rgba(0, 0, 0, 0.2), 0 12px 20px rgba(0, 0, 0, 0.2);
        }
        .email-container {
            background-color: #ffffff; /* Shiny white */
            padding: 20px; /* Inner padding */
            border-radius: 15px; /* Rounded corners */
            box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2); /* 3D effect */
            margin: 10px 0; /* Margin between containers */
            font-size: 20px; /* Font size for text */
            color: #800000; /* Font color */
            font-family: 'Arial', sans-serif; /* Font family */
            position: relative; /* For 3D effect */
            z-index: 1; /* Ensure it's above the background */
        }
        .email-container::before {
            content: '';
            position: absolute;
            top: 0;
            left: 0;
            right: 0;
            bottom: 0;
            border-radius: 15px; /* Match the container's border radius */
            background: rgba(255, 255, 255, 0.1); /* Light overlay for effect */
            z-index: -1; /* Position behind the text */
            filter: blur(10px); /* Soft blur for 3D effect */
        }
    </style>
"""

st.markdown(button_styles, unsafe_allow_html=True)

# Function to fetch filtered emails from today's date and download attachments
def fetch_filtered_today_emails():
    today = datetime.date.today()
    emails_data = []
    try:
        mail = imaplib.IMAP4_SSL("imap.gmail.com")
        mail.login(EMAIL_USERNAME, EMAIL_PASSWORD)
        mail.select("inbox")
        status, response = mail.search(None, f'(SENTON "{today.strftime("%d-%b-%Y")}")')
        email_ids = response[0].split()

        for email_id in email_ids:
            status, msg_data = mail.fetch(email_id, "(RFC822)")
            if status == "OK":
                msg = email.message_from_bytes(msg_data[0][1])
                if ("Razorpay Reports" in msg["From"] and "Combined_Report_" in msg["Subject"]):
                    email_details = {
                        "Message ID": msg["Message-ID"],
                        "Created at": msg["Date"],
                        "From": msg["From"],
                        "To": msg["To"],
                        "Subject": msg["Subject"],
                        "SPF": "PASS",
                        "DKIM": "PASS",
                        "DMARC": "PASS",
                        "Attachments": []
                    }
                    for part in msg.walk():
                        if part.get_content_disposition() == "attachment":
                            filename = part.get_filename()
                            if filename:
                                file_path = os.path.join(ATTACHMENTS_DIR, filename)
                                with open(file_path, "wb") as f:
                                    f.write(part.get_payload(decode=True))
                                email_details["Attachments"].append((file_path, filename))

                    emails_data.append(email_details)

        mail.logout()
    except Exception as e:
        st.error(f"Failed to retrieve emails: {e}")

    return emails_data

# BigQuery Query and Duplicate Check Function
def check_duplicates(credentials_file):
    results = {}
    credentials = service_account.Credentials.from_service_account_info(json.loads(credentials_file))
    client = bigquery.Client(credentials=credentials, project=credentials.project_id)

    mids_from_bq = set()
    query_record_count = 0  # Initialize record count

    for query_name, query in queries.items():
        query_job = client.query(query)
        df = query_job.result().to_dataframe()
        count = len(df)
        query_record_count += count  # Update total record count

        if 'Subject_date' in df.columns:
            df['Subject_date'] = pd.to_datetime(df['Subject_date']).dt.date
            mids_from_bq.update(df['MID'].unique())  # Assuming 'MID' is the column name in your BigQuery results
            results[query_name] = df

        html_subject = """
    <html>
    <head>
    <style>
        .button {
            display: inline-block;
            padding: 10px 20px;
            border-radius: 12px;
            background: linear-gradient(to bottom, #f8f9fa, #e0e0e0);
            box-shadow: 
                0 6px 12px rgba(0, 0, 0, 0.3), 
                0 8px 16px rgba(0, 0, 0, 0.2), 
                inset 0 -2px 4px rgba(255, 255, 255, 0.6);
            text-align: center;
            position: relative;
            transform: translateY(4px);
            transition: transform 0.2s ease-in-out, box-shadow 0.2s ease-in-out;
            cursor: pointer;
            user-select: none;
        }
        .button:hover {
            box-shadow: 
                0 8px 16px rgba(0, 0, 0, 0.3), 
                0 12px 24px rgba(0, 0, 0, 0.2);
            transform: translateY(2px);
        }
        .button:active {
            box-shadow: 
                0 4px 8px rgba(0, 0, 0, 0.3), 
                0 6px 12px rgba(0, 0, 0, 0.2);
            transform: translateY(0);
        }
    </style>
    </head>
    <body>
        <div class="button">
            <h3 style="
                font-size: 20px;
                color: #ffffff;
                background-image: linear-gradient(to right, #800000, #ff0000, #ffdab9);
                background-clip: text;
                -webkit-background-clip: text;
                text-fill-color: transparent;
                -webkit-text-fill-color: transparent;
                margin: 0;
                text-shadow: 0 2px 5px rgba(0, 0, 0, 0.4);
            ">MID query results:</h3>
        </div>
    </body>
    </html>
    """


        st.markdown(html_subject, unsafe_allow_html=True)
        st.write("")
        st.write("")
        st.dataframe(df)

    return results, mids_from_bq, query_record_count  # Return the total count

def send_message_via_webhook(message, webhook_url):
    """Send a message to a Slack channel via webhook."""
    response = requests.post(webhook_url, json={"text": message})

    if response.status_code != 200:
        return False, f"Request to Slack returned an error {response.status_code}, the response is:\n{response.text}"

    return True, "Message sent successfully."


# Dropdown for selecting webhook URL
html_subject = """
    <html>
    <head>
    <style>
        .button {
            display: inline-block;
            padding: 10px 20px;
            border-radius: 12px;
            background: linear-gradient(to bottom, #f8f9fa, #e0e0e0);
            box-shadow: 
                0 6px 12px rgba(0, 0, 0, 0.3), 
                0 8px 16px rgba(0, 0, 0, 0.2), 
                inset 0 -2px 4px rgba(255, 255, 255, 0.6);
            text-align: center;
            position: relative;
            transform: translateY(4px);
            transition: transform 0.2s ease-in-out, box-shadow 0.2s ease-in-out;
            cursor: pointer;
            user-select: none;
        }
        .button:hover {
            box-shadow: 
                0 8px 16px rgba(0, 0, 0, 0.3), 
                0 12px 24px rgba(0, 0, 0, 0.2);
            transform: translateY(2px);
        }
        .button:active {
            box-shadow: 
                0 4px 8px rgba(0, 0, 0, 0.3), 
                0 6px 12px rgba(0, 0, 0, 0.2);
            transform: translateY(0);
        }
    </style>
    </head>
    <body>
        <div class="button">
            <h3 style="
                font-size: 20px;
                color: #ffffff;
                background-image: linear-gradient(to right, #800000, #ff0000, #ffdab9);
                background-clip: text;
                -webkit-background-clip: text;
                text-fill-color: transparent;
                -webkit-text-fill-color: transparent;
                margin: 0;
                text-shadow: 0 2px 5px rgba(0, 0, 0, 0.4);
            ">Select the channel/member</h3>
        </div>
    </body>
    </html>
    """


st.markdown(html_subject, unsafe_allow_html=True)
selected_webhook_name = st.selectbox("", [""] + list(Webhook_urls.keys()))
selected_webhook_url = Webhook_urls[selected_webhook_name] if selected_webhook_name else None

# Get credentials for BigQuery access
# Upload credentials file
html_subject = """
    <html>
    <head>
    <style>
        .button {
            display: inline-block;
            padding: 10px 20px;
            border-radius: 12px;
            background: linear-gradient(to bottom, #f8f9fa, #e0e0e0);
            box-shadow: 
                0 6px 12px rgba(0, 0, 0, 0.3), 
                0 8px 16px rgba(0, 0, 0, 0.2), 
                inset 0 -2px 4px rgba(255, 255, 255, 0.6);
            text-align: center;
            position: relative;
            transform: translateY(4px);
            transition: transform 0.2s ease-in-out, box-shadow 0.2s ease-in-out;
            cursor: pointer;
            user-select: none;
        }
        .button:hover {
            box-shadow: 
                0 8px 16px rgba(0, 0, 0, 0.3), 
                0 12px 24px rgba(0, 0, 0, 0.2);
            transform: translateY(2px);
        }
        .button:active {
            box-shadow: 
                0 4px 8px rgba(0, 0, 0, 0.3), 
                0 6px 12px rgba(0, 0, 0, 0.2);
            transform: translateY(0);
        }
    </style>
    </head>
    <body>
        <div class="button">
            <h3 style="
                font-size: 20px;
                color: #ffffff;
                background-image: linear-gradient(to right, #800000, #ff0000, #ffdab9);
                background-clip: text;
                -webkit-background-clip: text;
                text-fill-color: transparent;
                -webkit-text-fill-color: transparent;
                margin: 0;
                text-shadow: 0 2px 5px rgba(0, 0, 0, 0.4);
            ">Upload the JSON file</h3>
        </div>
    </body>
    </html>
    """


st.markdown(html_subject, unsafe_allow_html=True)
credentials_file = st.file_uploader("", type="json")

# Initialize filtered_emails and mids
filtered_emails = []
mids_from_emails = set()

if credentials_file:
    credentials_data = credentials_file.read().decode("utf-8")
    query_results, mids_from_bq, query_record_count = check_duplicates(credentials_data)  # Capture the query count

    # Retrieve today's filtered emails
    filtered_emails = fetch_filtered_today_emails()

    if filtered_emails:
        html_subject = """
    <html>
    <head>
    <style>
        .button {
            display: inline-block;
            padding: 10px 20px;
            border-radius: 12px;
            background: linear-gradient(to bottom, #f8f9fa, #e0e0e0);
            box-shadow: 
                0 6px 12px rgba(0, 0, 0, 0.3), 
                0 8px 16px rgba(0, 0, 0, 0.2), 
                inset 0 -2px 4px rgba(255, 255, 255, 0.6);
            text-align: center;
            position: relative;
            transform: translateY(4px);
            transition: transform 0.2s ease-in-out, box-shadow 0.2s ease-in-out;
            cursor: pointer;
            user-select: none;
        }
        .button:hover {
            box-shadow: 
                0 8px 16px rgba(0, 0, 0, 0.3), 
                0 12px 24px rgba(0, 0, 0, 0.2);
            transform: translateY(2px);
        }
        .button:active {
            box-shadow: 
                0 4px 8px rgba(0, 0, 0, 0.3), 
                0 6px 12px rgba(0, 0, 0, 0.2);
            transform: translateY(0);
        }
    </style>
    </head>
    <body>
        <div class="button">
            <h3 style="
                font-size: 20px;
                color: #ffffff;
                background-image: linear-gradient(to right, #800000, #ff0000, #ffdab9);
                background-clip: text;
                -webkit-background-clip: text;
                text-fill-color: transparent;
                -webkit-text-fill-color: transparent;
                margin: 0;
                text-shadow: 0 2px 5px rgba(0, 0, 0, 0.4);
            ">Emails generated from Razorpay accounts:</h3>
        </div>
    </body>
    </html>
    """


        st.markdown(html_subject, unsafe_allow_html=True)
        st.write("")
        for email_detail in filtered_emails:
            mids_from_emails.add(email_detail["Message ID"])
            email_container = f"""
            <div class="email-container">
                <p><strong>Message ID:</strong> {html.escape(email_detail['Message ID'])}</p>
                <p><strong>Created at:</strong> {html.escape(email_detail['Created at'])}</p>
                <p><strong>From:</strong> <span>{html.escape(email_detail['From'].replace('@', '&#64;'))}</span></p>
                <p><strong>To:</strong> <span>{html.escape(email_detail['To'].replace('@', '&#64;'))}</span></p>
                <p><strong>Subject:</strong> {html.escape(email_detail['Subject'])}</p>
                <p><strong>SPF:</strong> {html.escape(email_detail['SPF'])}</p>
                <p><strong>DKIM:</strong> {html.escape(email_detail['DKIM'])}</p>
                <p><strong>DMARC:</strong> {html.escape(email_detail['DMARC'])}</p>
            </div>
            """
            st.markdown(email_container, unsafe_allow_html=True)

            if email_detail["Attachments"]:
                subheader_html = f"""
            <div style="background-image: linear-gradient(to right, #800000, #ff0000); 
                        -webkit-background-clip: text; 
                        -webkit-text-fill-color: transparent; 
                        margin: 10px 0;">
                <h4 style="margin: 0; font-size: 20px;">Attachment:</h4> <!-- Change h2 to h4 -->
            </div>
            """

                # Render the subheader
                st.markdown(subheader_html, unsafe_allow_html=True)
                for idx, (attachment_path, filename) in enumerate(email_detail["Attachments"]):
                    with open(attachment_path, "rb") as file:
                        file_data = file.read()
                        btn_id = f"download-button-{idx}-{filename}"
                        download_button = f"""
            <a href="data:application/octet-stream;base64,{file_data.hex()}" download="{filename}">
                <div class="download-button">{filename}</div>
            </a>
            """
                        st.markdown(download_button, unsafe_allow_html=True)
                st.write("---")

    else:
        st.error("No matching emails found for today.")

    # Check for mismatches
    missing_mids = mids_from_bq.difference(mids_from_emails)

    # Prepare the Slack message
    total_records_in_email = len(filtered_emails)
    mismatch_found = len(missing_mids) > 0

    slack_message = (
        f"Total Records in BigQuery: {query_record_count}\n"
        f"Total Records in Email: {total_records_in_email}\n"
        f"MID Mismatch: {'Yes' if mismatch_found else 'No'}"
    )

    # st.write(f"Total Records in BigQuery: {query_record_count}")  # Show total records fetched
    # st.write(f"Total Emails received from Razorpay: {total_records_in_email}") 
    # st.write(f"Missing MIDs: {', '.join(missing_mids) if missing_mids else 'None'}")
    
    subheader_html = f"""
            <div style="background-image: linear-gradient(to right, #800000, #ff0000); 
                        -webkit-background-clip: text; 
                        -webkit-text-fill-color: transparent; 
                        margin: 10px 0;">
                <h4 style="margin: 0; font-size: 20px;">Total records in MID query: {query_record_count}</h4> <!-- Change h2 to h4 -->
            </div>
            """

    st.markdown(subheader_html, unsafe_allow_html=True)

    subheader_html = f"""
            <div style="background-image: linear-gradient(to right, #800000, #ff0000); 
                        -webkit-background-clip: text; 
                        -webkit-text-fill-color: transparent; 
                        margin: 10px 0;">
                <h4 style="margin: 0; font-size: 20px;">Total Emails recieved from Razorpay accounts: {total_records_in_email}</h4> <!-- Change h2 to h4 -->
            </div>
            """

    st.markdown(subheader_html, unsafe_allow_html=True)


    subheader_html = f"""
            <div style="background-image: linear-gradient(to right, #800000, #ff0000); 
                        -webkit-background-clip: text; 
                        -webkit-text-fill-color: transparent; 
                        margin: 10px 0;">
                <h4 style="margin: 0; font-size: 20px;">Missing MIDs:{', '.join(missing_mids) if missing_mids else 'None'}</h4> <!-- Change h2 to h4 -->
            </div>
            """

    st.markdown(subheader_html, unsafe_allow_html=True)


    
    # Send message to Slack
    button_styles = """
                    <style>
                    div.stButton > button {
                        color: #ffffff; /* Text color */
                        font-size: 30px;
                        background-image: linear-gradient(to right, #800000, #ff0000); /* Maroon to light red gradient */
                        border: none;
                        padding: 10px 20px;
                        cursor: pointer;
                        border-radius: 15px;
                        display: inline-block;
                        box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1), 0 8px 15px rgba(0, 0, 0, 0.1); /* Box shadow */
                        transition: all 0.3s ease; /* Smooth transition on hover */
                    }
                    div.stButton > button:hover {
                        background-color: #00ff00; /* Hover background color */
                        color: #ff0000; /* Hover text color */
                        box-shadow: 0 6px 10px rgba(0, 0, 0, 0.2), 0 12px 20px rgba(0, 0, 0, 0.2); /* Box shadow on hover */
                    }
                    </style>
                """
    st.markdown(button_styles, unsafe_allow_html=True)
    if st.button("Send to Slack"):
        success, response = send_message_via_webhook(slack_message, selected_webhook_url)
        if success:
            st.success(response)
        else:
            st.error(response)