import mysql.connector
from openpyxl import Workbook
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email import encoders
import os


def connect_db(sql_query):
    try:
        connection = mysql.connector.connect(
            host='192.168.',
            user='',
            password='',
            database='',
            auth_plugin='mysql_native_password',
            ssl_disabled=True,
            use_pure=True
        )
        cursor = connection.cursor()
        cursor.execute(sql_query)

        results = cursor.fetchall()
        column_headers = [i[0] for i in cursor.description]
        return results, column_headers

    except mysql.connector.Error as e:
        print(f"Error connecting to DB: {e}")
        return None


def write_to_excel(data, headers, file_name):
    workbook = Workbook()
    sheet = workbook.active
    sheet.append(headers)

    for row in data:
        sheet.append(row)

    workbook.save(file_name)
    print(f"Excel file saved as {file_name}")


def send_email(subject, body, recipient_emails, sender_email, filename, smtp_server, smtp_port, smtp_user, smtp_password):
    try:
        msg = MIMEMultipart()
        msg['From'] = sender_email
        msg['To'] = ', '.join(recipient_emails)  # Join multiple recipients
        msg['Subject'] = subject

        msg.attach(MIMEText(body, 'plain'))

        with open(filename, 'rb') as attachment:
            part = MIMEBase('application', 'octet-stream')
            part.set_payload(attachment.read())
            encoders.encode_base64(part)
            part.add_header(
                'Content-Disposition',
                f'attachment; filename={os.path.basename(filename)}'
            )
            msg.attach(part)

        with smtplib.SMTP(smtp_server, smtp_port) as server:
            server.starttls()
            server.login(smtp_user, smtp_password)
            server.send_message(msg)
            server.quit()
            print("Email sent!")

    except Exception as e:
        print(f"Error: {e}")


if __name__ == "__main__":

    sql_query = """SELECT
    countryname_eng as Countryname, ifnull(network, 'Default') as Network, routingprices.mcc as MCC, REPLACE(routingprices.mnc, 'X', '0') as MNC, route, price
FROM
    smsgw_control.routingprices
    LEFT JOIN smsgw_control.E212_MCC ON routingprices.mcc = smsgw_control.E212_MCC.MCC
    LEFT JOIN smsgw_control.E212_MCCMNC on routingprices.mcc = smsgw_control.E212_MCCMNC.MCC and routingprices.mnc = smsgw_control.E212_MCCMNC.MNC
WHERE
    route = 'M1' AND clientid = '13283';"""

    results, headers = connect_db(sql_query)

    excel_file_name = "Mes_Bird_Dir_price_list.xlsx"
    write_to_excel(results, headers, excel_file_name)

    subject = "MEXEDIA PRICE CHANGE for MessageBird- Mes_Bird_Dir"
    body = """Dear Valued Customer,

We would like to inform you of the following price changes on your account Mes_Bird_Dir.

Please note that the changes are effective immediately, any messages sent from now on will be charged based on the new price.
If you require any further assistance or clarification, please do not hesitate to contact your Account Manager or our Support Team on support.sms@mexedia.com and we'll gladly assist you further.

Best Regards,

Mexedia Customer Support

Support | support.sms@mexedia.com
Sales | sales-sms@mexedia.com
www.mexedia.com"""

    recipient_emails = ["xxx@xxx.com", "xxx@xxx.com"]
    sender_email = "donotreply@mexedia.mt"
    smtp_server = "smtp.eu.mailgun.org"
    smtp_port = 587
    smtp_user = ''
    smtp_password = ''

    send_email(
        subject, body, recipient_emails, sender_email, excel_file_name, smtp_server, smtp_port, smtp_user, smtp_password
    )