import mysql.connector
import pandas as pd
import imaplib
import email
import os

# Email credentials
EMAIL_ADDRESS = 'your_email@example.com'
EMAIL_PASSWORD = 'your_password'

# MySQL connection parameters
DB_HOST = 'localhost'
DB_USER = 'your_db_user'
DB_PASSWORD = 'your_db_password'
DB_NAME = 'your_db_name'

def fetch_email():
    """Fetches CSV files from email and saves them locally."""
    mail = imaplib.IMAP4_SSL("imap.gmail.com")
    mail.login(EMAIL_ADDRESS, EMAIL_PASSWORD)
    mail.select("inbox")

    # Search for all emails
    status, messages = mail.search(None, 'ALL')
    file_path = None

    for num in messages[0].split():
        status, msg_data = mail.fetch(num, "(RFC822)")
        for response_part in msg_data:
            if isinstance(response_part, tuple):
                msg = email.message_from_bytes(response_part[1])
                if msg.get_content_maintype() == "multipart":
                    for part in msg.walk():
                        if part.get_content_disposition() == "attachment":
                            filename = part.get_filename()
                            if filename.endswith(".csv"):
                                file_path = os.path.join("/path/to/download/folder", filename)
                                with open(file_path, "wb") as f:
                                    f.write(part.get_payload(decode=True))
    mail.logout()
    return file_path

def import_csv_to_mysql(csv_file):
    """Imports data from a CSV file into the MySQL database."""
    conn = mysql.connector.connect(
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASSWORD,
        database=DB_NAME
    )
    cursor = conn.cursor()

    # Load CSV data
    df = pd.read_csv(csv_file)

    # Prepare SQL insert statement
    for index, row in df.iterrows():
        cursor.execute("""
            INSERT into employeeinfo (emp_id,firstname,lastname,email) 
            VALUES (%s, %s, %s)
        """, (row['column1'], row['column2'], row['column3']))

    conn.commit()
    cursor.close()
    conn.close()

if __name__ == "__main__":
    csv_file = fetch_email()
    if csv_file:
        import_csv_to_mysql(csv_file)
        print("Data imported successfully!")
    else:
        print("No CSV file found.")