import pandas as pd
import mysql.connector
from datetime import datetime

class ReminderDatabaseHandler:
    def __init__(self, db_host='localhost', db_user='root', db_password='root', db_name='project10'):
        """
        初始化 ReminderDatabaseHandler 類別，設置 MySQL 資料庫連線參數。
        """
        self.db_host = db_host
        self.db_user = db_user
        self.db_password = db_password
        self.db_name = db_name
        self.connection = None
        self.cursor = None

    def connect(self):
        """
        連接到 MySQL 資料庫
        """
        try:
            self.connection = mysql.connector.connect(
                host=self.db_host,
                user=self.db_user,
                password=self.db_password,
                database=self.db_name
            )
            self.cursor = self.connection.cursor()
            print("資料庫連線成功")
        except Exception as e:
            print(f"資料庫連線失敗: {str(e)}")
            raise

    def disconnect(self):
        """
        關閉資料庫連線
        """
        if self.connection and self.connection.is_connected():
            self.cursor.close()
            self.connection.close()
            print("資料庫連線已關閉")

    def is_reminder_exists(self, reminder_id):
        """檢查資料庫中是否已經存在此 reminder_id"""
        query = "SELECT COUNT(1) FROM reminders WHERE reminder_userID_time = %s"
        self.cursor.execute(query, (reminder_id,))
        result = self.cursor.fetchone()
        return result[0] > 0

    def process_and_upload_data(self, csv_file='reminder_data.csv'):
        """
        讀取 CSV 檔案並將資料上傳到資料庫。
        """
        try:
            # 讀取 CSV 檔案
            df = pd.read_csv(csv_file)
            
            # 準備插入資料的 SQL 語句
            insert_query = """
            INSERT INTO reminders 
            (reminder_userID_time, title, content, first_reminder_time, repeat_count, repeat_interval, files)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
            """
            
            continue_data = []

            # 處理每一筆資料並插入資料庫
            for _, row in df.iterrows():
                reminder_id = row['reminder_userID_time']
                
                # 檢查資料庫中是否已經存在該 reminder_id
                if self.is_reminder_exists(reminder_id):
                    continue_data.append(reminder_id)
                    continue  # 跳過已存在的資料
                
                data_tuple = (
                    str(reminder_id),
                    str(row['Title']),
                    str(row['Content']),
                    str(row['First Reminder Time']),
                    str(row['Repeat Count']),
                    str(row['Interval']),
                    str(row['Files'])
                )
                
                # 執行插入操作
                self.cursor.execute(insert_query, data_tuple)
            
            # 提交變更
            self.connection.commit()
            if continue_data:
                print(f"跳過上傳的資料: {continue_data}")
            print("上傳至資料庫 - 上傳成功")
        
        except Exception as e:
            print(f"上傳至資料庫 - 發生錯誤: {str(e)}")
            raise

    def __enter__(self):
        """
        用於支持 with 語句，進行自動連線管理
        """
        self.connect()
        return self

    def __exit__(self, exc_type, exc_value, traceback):
        """
        用於支持 with 語句，進行自動連線關閉
        """
        self.disconnect()

# 用於直接執行 deal_with_sql.py 時執行資料上傳的邏輯
if __name__ == "__main__":
    # 創建類別實例並執行資料處理和上傳
    handler = ReminderDatabaseHandler()
    handler.connect()
    handler.process_and_upload_data()
    handler.disconnect()