import sqlite3
import pandas as pd

# Path to your SQLite database
db_path = 'events.db'

# Path to your SQL query file
sql_file_path = 'data.sql'

# Path to your Excel file
excel_path = 'WBR.xlsx'
sheet_name = 'Data'  # The name of the first tab where you want to insert the data

# Function to read SQL query from a file
def read_sql_query(file_path):
    with open(file_path, 'r') as file:
        return file.read()

# Read the SQL query
sql_query = read_sql_query(sql_file_path)

# Connect to the SQLite database
conn = sqlite3.connect(db_path)

# Execute the SQL query and fetch the data into a DataFrame
df = pd.read_sql_query(sql_query, conn)

# Close the database connection
conn.close()

# Write the DataFrame to the Excel file, replacing the content of the first tab
with pd.ExcelWriter(excel_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
    df.to_excel(writer, sheet_name=sheet_name, index=False)

print("Data has been successfully inserted into the Excel file.")

import win32com.client as win32
import os

def refresh_pivot_table_and_chart(excel_path):
    excel = win32.Dispatch("Excel.Application")
    wb = excel.Workbooks.Open(excel_path)
    excel.Visible = True

    # Refresh pivot table
    ws = wb.Sheets('Analysis')
    pivot_table = ws.PivotTables(1)
    pivot_table.RefreshTable()

    # Copy the chart from 'Visualization' to clipboard
    chart_sheet = wb.Sheets('Visualization')
    chart = chart_sheet.ChartObjects(1).Chart

    # Save the chart as an image
    image_path = os.path.abspath('chart_image.png')
    chart.Export(Filename=image_path)

    wb.Close(SaveChanges=True)
    excel.Quit()

    return image_path

def create_email_with_image(image_path):
    outlook = win32.Dispatch('outlook.application')
    mail = outlook.CreateItem(0)
    mail.To = 'recipient@example.com'
    mail.Subject = 'Data Analysis Update'

    # Convert relative path to absolute path
    abs_image_path = os.path.abspath(image_path)
    print(f"Attempting to attach image at: {abs_image_path}")  # Debugging line

    # Check if the file actually exists before trying to attach it
    if not os.path.exists(abs_image_path):
        raise FileNotFoundError("The specified image file does not exist at the provided path.")

    # Attach the image file and set a content ID
    attachment = mail.Attachments.Add(Source=abs_image_path)
    attachment.PropertyAccessor.SetProperty("http://schemas.microsoft.com/mapi/proptag/0x3712001F", "ChartImage")

    # Use the content ID in the HTML body to reference the image
    mail.HTMLBody = f"<p>Here's the updated chart from our analysis:</p><img src='cid:ChartImage'>"

    mail.Display(True)  # Opens the draft email

# Path to your Excel file
excel_file = 'WBR.xlsx'

# Refresh pivot table and save the chart as an image
chart_image_path = refresh_pivot_table_and_chart(excel_file)

# Create and display an email draft with the chart
create_email_with_image(chart_image_path)