from tkinter import Frame
import tkinter.font as tkFont

from ttkbootstrap import *
import ttkbootstrap as ttk
from ttkbootstrap.constants import *

import pandas as pd
import pymysql
from ttkbootstrap.toast import ToastNotification

from BaseWindow import BaseWindow
from X2.StuSys_X2_bootstrap import LoadElement


def get_text_content(text):
    return text.get("0.0", "end").replace("\n", " ")




class FormElement(Frame):
    def __init__(self, master):
        super().__init__(master)
        self.master = master
        self.data = []
        self.conn = pymysql.connect(user="root", password="cyh0110", database="SCT", host="127.0.0.1", port=3306)
        self.font_style = tkFont.Font(family="Lucida Grande", size=20)
        self.button_font = tkFont.Font(family="Lucida Grande", size=10)
        self.default_query_sql = "select * from student"
        self.create_form()
        self.create_treeview(pd.read_sql(self.default_query_sql, con=self.conn))

    def create_form(self):
        # 创建表单区域
        self.sql_text = Text(self, width=75, height=6, font=self.font_style)
        self.sql_text.grid(row=0, column=0, rowspan=2)
        Button(self, text="\n\n执行SQL\n\n", command=self.execute_sql, width=8,
               ).grid(column=1, row=0, rowspan=2, padx=10)
        self.sql_text.insert(1.0, self.default_query_sql)

    def sql_query(self, SQLString):
        SQLResultList = pd.read_sql(SQLString, self.conn)
        return SQLResultList

    def execute_sql(self):
        # 执行sql并将sql执行后查询的数据展示
        sql = get_text_content(self.sql_text).lower().strip()
        if sql.startswith("select"):
            data_frame = self.sql_query(sql)
            self.create_treeview(data_frame)
        ToastNotification(message="语句执行成功",
                          title="提示",
                          duration=1500,
                          position=(int(self.winfo_screenwidth() / 2) - 150, 80, "ne"),
                          bootstyle="success",
                          icon="").show_toast()

    def create_treeview(self, data_frame):
        frame = Frame(self, height=700)
        frame.grid(row=10, column=0, columnspan=10, rowspan=5, sticky=NSEW)
        frame.grid_columnconfigure(0, weight=1)
        frame.grid_rowconfigure(0, weight=1)
        columns = data_frame.keys().tolist()
        tree = ttk.Treeview(frame, columns=columns, show="headings", style="mystyle.Treeview", height=50,
                            selectmode=BROWSE)
        style = ttk.Style()
        style.configure("Treeview.Heading", font=self.font_style)
        style.configure("Treeview", rowheight=30, font=self.font_style, borderwidth=1)
        # 根据dataFrame结构生成表
        for i in columns:
            tree.heading(column=i, text=i)
        for i in columns:
            tree.column(i, anchor=CENTER, minwidth=100, width=100)
        for row in data_frame.itertuples():
            tree.insert(parent="", index=END, values=[row[i] for i in range(1, len(row))])
        tree.grid(row=0, column=0, sticky=NSEW, pady=10)
        y_scrollbar = ttk.Scrollbar(frame, orient='vertical', command=tree.yview)
        tree.configure(yscrollcommand=y_scrollbar.set)
        y_scrollbar.grid(row=0, column=1, sticky='ns')
        frame.grid_propagate(False)


class StuSys_X3(BaseWindow):
    def __init__(self, master, attr):
        super().__init__(master, attr)
        self.create_left_plate()
        self.create_right_plate()

    def create_left_plate(self):
        self.left_frame = Frame(self.master, height=self.attr["height"])
        self.left_frame.pack(side=LEFT, anchor=NW, pady=10, padx=10)
        load_element1 = LoadElement(self.left_frame, "student")
        load_element1.pack(side=TOP, pady=10)
        load_element2 = LoadElement(self.left_frame, "course")
        load_element2.pack(side=TOP, pady=10)
        load_element2 = LoadElement(self.left_frame, "sc")
        load_element2.pack(side=TOP, pady=10)

    def create_right_plate(self):
        self.right_frame = Frame(self.master, height=self.attr["height"])
        self.right_frame.pack(side=LEFT, anchor=NW, pady=10, padx=10)
        form_element = FormElement(self.right_frame)
        form_element.pack(side=TOP, pady=10, anchor=NE)


if __name__ == '__main__':
    """
    minty, lumen, sandstone, yeti, pulse, united, morph, journal, darkly, superhero, solar
    cyborg, vapor, simplex, cerculean, 
    """
    style = "morph"
    root = Window(themename=style)
    screenwidth = root.winfo_screenwidth()
    screenheight = root.winfo_screenheight()
    root.columnconfigure(0, weight=1)
    root.rowconfigure(1, weight=1)
    root_attr = {
        "width": screenwidth * 0.83,
        "height": screenheight * 0.85,
    }
    alignstr = '%dx%d+%d+%d' % (root_attr['width'], root_attr['height'], (screenwidth - root_attr['width']) / 2,
                                (screenheight - root_attr['height']) / 2)
    root.geometry(alignstr)
    root.resizable(width=False, height=False)
    app = StuSys_X3(root, root_attr)

    ttk.Style().configure("TButton", font="-size 18")
    root.mainloop()