import sys
import os
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

import customtkinter as ctk
import tkinter.font as tkFont
from tkinter import messagebox, ttk
from tkcalendar import DateEntry
from datetime import datetime, timedelta
from sqlalchemy import select, func, between
from conf import db_schema, db
from lib import holiday  # 휴일 기능

# Windows DPI Awareness 설정
if sys.platform == "win32":
    import ctypes
    try:
        ctypes.windll.shcore.SetProcessDpiAwareness(1)
    except Exception:
        pass

pos_table = db_schema.pos
engine = db.engine

class PosViewGUI(ctk.CTk):
    def __init__(self):
        super().__init__()

        self.title("POS 데이터 조회")
        self.geometry("1100x700")
        self.configure(fg_color="#f0f0f0")

        ctk.set_appearance_mode("light")
        ctk.set_default_color_theme("blue")

        try:
            self.label_font = ("NanumGothic", 13)
        except Exception:
            self.label_font = ("Arial", 13)

        style = ttk.Style(self)
        style.theme_use('default')
        style.configure("Treeview", font=("NanumGothic", 12), rowheight=30)
        style.configure("Treeview.Heading", font=("NanumGothic", 13, "bold"))

        # 날짜 필터
        ctk.CTkLabel(self, text="시작일:", anchor="w", font=self.label_font, fg_color="#f0f0f0")\
            .grid(row=0, column=0, padx=10, pady=5, sticky="e")
        self.start_date_entry = DateEntry(self, width=12, background='darkblue', foreground='white')
        self.start_date_entry.grid(row=0, column=1, padx=10, pady=5, sticky="w")

        ctk.CTkLabel(self, text="종료일:", anchor="w", font=self.label_font, fg_color="#f0f0f0")\
            .grid(row=0, column=2, padx=10, pady=5, sticky="e")
        self.end_date_entry = DateEntry(self, width=12, background='darkblue', foreground='white')
        self.end_date_entry.grid(row=0, column=3, padx=10, pady=5, sticky="w")

        # 날짜유형 라디오버튼
        self.date_filter_var = ctk.StringVar(value="전체")
        ctk.CTkLabel(self, text="날짜유형:", font=self.label_font, fg_color="#f0f0f0")\
            .grid(row=0, column=4, padx=(10, 0), pady=5, sticky="e")

        ctk.CTkRadioButton(self, text="전체", variable=self.date_filter_var, value="전체")\
            .grid(row=0, column=5, padx=2, pady=5, sticky="w")
        ctk.CTkRadioButton(self, text="휴일", variable=self.date_filter_var, value="휴일")\
            .grid(row=0, column=6, padx=2, pady=5, sticky="w")
        ctk.CTkRadioButton(self, text="평일", variable=self.date_filter_var, value="평일")\
            .grid(row=0, column=7, padx=2, pady=5, sticky="w")

        # 대분류
        ctk.CTkLabel(self, text="대분류 :", anchor="w", font=self.label_font, fg_color="#f0f0f0")\
            .grid(row=1, column=0, padx=10, pady=5, sticky="e")
        self.ca01_combo = ctk.CTkComboBox(self, values=["전체"], width=180)
        self.ca01_combo.grid(row=1, column=1, padx=10, pady=5, sticky="w")
        self.ca01_combo.configure(command=self.on_ca01_selected)

        # 소분류
        ctk.CTkLabel(self, text="소분류 :", anchor="w", font=self.label_font, fg_color="#f0f0f0")\
            .grid(row=1, column=2, padx=10, pady=5, sticky="e")
        self.ca03_combo = ctk.CTkComboBox(self, values=["전체"], width=180)
        self.ca03_combo.grid(row=1, column=3, padx=10, pady=5, sticky="w")

        # 상품명
        ctk.CTkLabel(self, text="상품명 :", anchor="w", font=self.label_font, fg_color="#f0f0f0")\
            .grid(row=2, column=0, padx=10, pady=5, sticky="e")
        self.name_entry = ctk.CTkEntry(self, width=280)
        self.name_entry.grid(row=2, column=1, columnspan=3, padx=10, pady=5, sticky="w")

        # 조회 버튼
        self.search_btn = ctk.CTkButton(self, text="조회", command=self.search,
                                        fg_color="#0d6efd", hover_color="#0b5ed7", text_color="white")
        self.search_btn.grid(row=3, column=0, columnspan=8, pady=10)

        # 상품별 트리뷰
        self.DISPLAY_COLUMNS = ['ca01', 'ca02', 'ca03', 'name', 'qty', 'tot_amount', 'tot_discount', 'actual_amount']
        self.COLUMN_LABELS = {
            'ca01': '대분류',
            'ca02': '중분류',
            'ca03': '소분류',
            'name': '상품명',
            'qty': '수량',
            'tot_amount': '총매출액',
            'tot_discount': '총할인액',
            'actual_amount': '실매출액'
        }

        self.tree = ttk.Treeview(self, columns=self.DISPLAY_COLUMNS, show='headings', height=12)
        for col in self.DISPLAY_COLUMNS:
            self.tree.heading(col, text=self.COLUMN_LABELS[col])
            self.tree.column(col, width=120, anchor='center')
        self.tree.grid(row=4, column=0, columnspan=8, padx=10, pady=10, sticky="nsew")

        # 날짜 요약 트리뷰
        self.date_tree = ttk.Treeview(self, columns=['date', 'qty', 'tot_amount', 'actual_amount'], show='headings', height=6)
        self.date_tree.heading('date', text='일자')
        self.date_tree.heading('qty', text='수량합')
        self.date_tree.heading('tot_amount', text='총매출합')
        self.date_tree.heading('actual_amount', text='실매출합')

        for col in ['date', 'qty', 'tot_amount', 'actual_amount']:
            self.date_tree.column(col, width=150, anchor='center')

        self.date_tree.grid(row=5, column=0, columnspan=8, padx=10, pady=(0, 10), sticky="nsew")

        self.grid_rowconfigure(4, weight=1)
        self.grid_rowconfigure(5, weight=1)
        for col_index in range(8):
            self.grid_columnconfigure(col_index, weight=1)

        # 날짜 기본값
        end_date = datetime.today().date() - timedelta(days=1)
        start_date = end_date - timedelta(days=6)
        self.start_date_entry.set_date(start_date)
        self.end_date_entry.set_date(end_date)

        self.load_ca01_options()

    def on_ca01_selected(self, value):
        self.load_ca03_options()

    def load_ca01_options(self):
        start_date = self.start_date_entry.get_date()
        end_date = self.end_date_entry.get_date()
        with engine.connect() as conn:
            stmt = select(pos_table.c.ca01).where(
                between(pos_table.c.date, start_date, end_date)
            ).distinct().order_by(pos_table.c.ca01)
            result = conn.execute(stmt)
            ca01_list = [row[0] for row in result.fetchall()]
        self.ca01_combo.configure(values=['전체'] + ca01_list)
        self.ca01_combo.set('전체')
        self.load_ca03_options()

    def load_ca03_options(self):
        start_date = self.start_date_entry.get_date()
        end_date = self.end_date_entry.get_date()
        ca01_val = self.ca01_combo.get()
        with engine.connect() as conn:
            stmt = select(pos_table.c.ca03).where(
                between(pos_table.c.date, start_date, end_date)
            )
            if ca01_val != '전체':
                stmt = stmt.where(pos_table.c.ca01 == ca01_val)
            stmt = stmt.distinct().order_by(pos_table.c.ca03)
            result = conn.execute(stmt)
            ca03_list = [row[0] for row in result.fetchall()]
        self.ca03_combo.configure(values=['전체'] + ca03_list)
        self.ca03_combo.set('전체')

    def search(self):
        start_date = self.start_date_entry.get_date()
        end_date = self.end_date_entry.get_date()
        ca01_val = self.ca01_combo.get()
        ca03_val = self.ca03_combo.get()
        name_val = self.name_entry.get().strip()
        date_filter = self.date_filter_var.get()

        print("🔍 date_filter:", date_filter, 
            "| start:", start_date, "end:", end_date)
        if date_filter == "휴일":
            valid_dates = holiday.get_holiday_dates(start_date, end_date)
            print("🚩 반환된 휴일 날짜 리스트:", valid_dates)

        conditions = []

        if date_filter == "전체":
            conditions.append(between(pos_table.c.date, start_date, end_date))
        else:
            if date_filter == "휴일":
                valid_dates = holiday.get_holiday_dates(start_date, end_date)
            elif date_filter == "평일":
                valid_dates = holiday.get_weekday_dates(start_date, end_date)
            else:
                valid_dates = set()

            if not valid_dates:
                messagebox.showinfo("알림", f"{date_filter}에 해당하는 데이터가 없습니다.")
                self.tree.delete(*self.tree.get_children())
                self.date_tree.delete(*self.date_tree.get_children())
                return

            conditions.append(pos_table.c.date.in_(valid_dates))

        if ca01_val != '전체':
            conditions.append(pos_table.c.ca01 == ca01_val)
        if ca03_val != '전체':
            conditions.append(pos_table.c.ca03 == ca03_val)
        if name_val:
            conditions.append(pos_table.c.name.like(f"%{name_val}%"))

        with engine.connect() as conn:
            # 상품별
            stmt = select(
                pos_table.c.ca01,
                pos_table.c.ca02,
                pos_table.c.ca03,
                pos_table.c.name,
                func.sum(pos_table.c.qty).label("qty"),
                func.sum(pos_table.c.tot_amount).label("tot_amount"),
                func.sum(pos_table.c.tot_discount).label("tot_discount"),
                func.sum(pos_table.c.actual_amount).label("actual_amount")
            ).where(*conditions).group_by(pos_table.c.barcode).order_by(pos_table.c.ca01, pos_table.c.ca03)

            result = conn.execute(stmt).mappings().all()

            # 날짜별 요약
            date_stmt = select(
                pos_table.c.date,
                func.sum(pos_table.c.qty).label("qty"),
                func.sum(pos_table.c.tot_amount).label("tot_amount"),
                func.sum(pos_table.c.actual_amount).label("actual_amount")
            ).where(*conditions).group_by(pos_table.c.date).order_by(pos_table.c.date)

            date_summary = conn.execute(date_stmt).mappings().all()

        # 트리뷰 초기화
        self.tree.delete(*self.tree.get_children())
        self.date_tree.delete(*self.date_tree.get_children())

        # 상품별 출력
        for row in result:
            values = tuple(row[col] for col in self.DISPLAY_COLUMNS)
            self.tree.insert('', 'end', values=values)

        # 날짜별 출력
        total_qty = total_amount = total_actual = 0
        for row in date_summary:
            self.date_tree.insert('', 'end', values=(
                row['date'].strftime("%Y-%m-%d"),
                row['qty'],
                row['tot_amount'],
                row['actual_amount']
            ))
            total_qty += row['qty']
            total_amount += row['tot_amount']
            total_actual += row['actual_amount']

        # 총합계 추가
        self.date_tree.insert('', 'end', values=("총합계", total_qty, total_amount, total_actual))


if __name__ == "__main__":
    try:
        import tkcalendar
    except ImportError:
        print("tkcalendar가 설치되어 있지 않습니다. 'pip install tkcalendar'로 설치해주세요.")
        sys.exit(1)

    app = PosViewGUI()
    app.mainloop()