from datetime import date, timedelta, datetime
from email.message import EmailMessage
import mysql.connector
import pandas as pd
import smtplib
import traceback
import sys
import os

DirToSaveCSV = '/opt/weeklyprofitreport/workdir'

DB_CONFIG = {
    'host': '192.168.144.179',
    'user': 'python_weeklyprofitreport',
    'password': 'ojF8QwFP',
    'database': 'smsgw_control'
}

EMAIL_CONFIG = {
    'smtp_server': 'smtp.eu.mailgun.org',
    'smtp_port': 587,
    'username': 'stps@monitoring.fortytwo.mt',
    'password': 'f18c48a7076c868bef91eb8f0ca59d33-9776af14-d2648a16',
    'sender': 'sw-vm-ub16-sysopscore@fortytwo.com',
    'recipients_success': ['mark@42tele.com'],
    'recipients_failure': ['sysops@fortytwo.com']
}

def log(msg):
    timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    print(f"{timestamp} {msg}")

def send_email(subject, body, attachments=None, recipients=None):
    recipients = recipients or []
    if not recipients:
        return

    msg = EmailMessage()
    msg['Subject'] = subject
    msg['From'] = EMAIL_CONFIG['sender']
    msg['To'] = ', '.join(recipients)
    msg.set_content(body)

    if attachments:
        for file_path in attachments:
            with open(file_path, 'rb') as f:
                file_data = f.read()
                file_name = file_path.split('/')[-1]
            msg.add_attachment(file_data, maintype='application', subtype='octet-stream', filename=file_name)

    try:
        with smtplib.SMTP(EMAIL_CONFIG['smtp_server'], EMAIL_CONFIG['smtp_port']) as server:
            server.starttls()
            server.login(EMAIL_CONFIG['username'], EMAIL_CONFIG['password'])
            server.send_message(msg)
        log(f"Email sent successfully to: {recipients}")
    except Exception as e:
        log(f"Failed to send email to {recipients}. Error: {e}")


def get_week_range():
    today = date.today()
    offset = (today.weekday() - 3) % 7  # min jrid weekly report jibda mil hamis lmao
    last_thursday = today - timedelta(days=offset)
    prev_friday = last_thursday - timedelta(days=6)
    return prev_friday, last_thursday

try:
    start_date, end_date = get_week_range()
    log(f"Started generating weekly report range: {start_date} → {end_date}")

    conn = mysql.connector.connect(
        host=DB_CONFIG['host'],
        user=DB_CONFIG['user'],
        password=DB_CONFIG['password'],
        database=DB_CONFIG['database']
    )

    QUERY = f"""
    SELECT 
        clientlogin,
        SUM(knownpaid - knowncost) AS profit,
        SUM(knownpaid + unknownpaid) AS revenue
    FROM
        stats_profit_country_client_route as spccr
        LEFT JOIN client as cnt
        ON spccr.clientid = cnt.clientid
    WHERE
        date >= '{start_date}'
            AND date <= '{end_date}'
    GROUP BY clientlogin
    ORDER BY profit DESC;
    """

    QUERY_TOTAL_PROFIT = f"""
    SELECT 
        SUM(knownpaid - knowncost) as total_profit
    FROM
        stats_profit_country_client_route
    WHERE
        date >= '{start_date}'
            AND date <= '{end_date}';
    """

    QUERY_TOTAL_REVENUE = f"""
    SELECT 
        SUM(knownpaid + unknownpaid) as total_revenue
    FROM
        stats_profit_country_client_route
    WHERE
        date >= '{start_date}'
            AND date <= '{end_date}';
    """

    df = pd.read_sql(QUERY, conn)
    df_total_profit = pd.read_sql(QUERY_TOTAL_PROFIT, conn)
    df_total_revenue = pd.read_sql(QUERY_TOTAL_REVENUE, conn)
    conn.close()

    total_profit = df_total_profit['total_profit'].iloc[0]
    total_revenue = df_total_revenue['total_revenue'].iloc[0]
    profit_str = f"{int(round(total_profit))}"
    revenue_str = f"{int(round(total_revenue))}"

    if df.empty:
        log("No data found for this week.")
        sys.exit(0)

    output_dir = DirToSaveCSV
    os.makedirs(output_dir, exist_ok=True)

    csv_file = os.path.join(output_dir, f"weekly_profit_{start_date}_to_{end_date}.csv")
    df.to_csv(csv_file, index=False)
    log(f"CSV saved: {csv_file}")

    send_email(
        subject=f"Fortytwo Weekly Profit Report between {start_date} - {end_date}",
        body=f"The total profit for this period was: {profit_str}\n"
             f"The total revenue for this period was: {revenue_str}\n"
             f"Attached also please find the weekly profit report for Fortytwo {start_date} - {end_date}.\n"
             "\n"
             "\n"
             f"This email was generated automatically by sw-vm-ub16-sysopscore on 192.168.144.11. Do not reply to this address.\n"
             f"If you need any help, please contact {EMAIL_CONFIG['recipients_failure']}.\n",
        attachments=[csv_file],
        recipients=EMAIL_CONFIG['recipients_success']
    )


except Exception as e:
    error_msg = f"Script failed: {e}\n\nTraceback:\n{traceback.format_exc()}"
    log(error_msg)
    send_email(
        subject=f"ALERT: Weekly Profit Report FAILED for {datetime.now().date()}",
        body=error_msg,
        recipients=EMAIL_CONFIG['recipients_failure']
    )