import json
import os
import sys
import zipfile
from datetime import datetime
from xml.sax.saxutils import escape

import tkinter as tk
from tkinter import filedialog, messagebox, ttk

DATE_FORMAT = "%d.%m.%Y"
STATUSES = ("новая", "в работе", "готова")
STATUS_FILTERS = ("Все", "новая", "в работе", "готова")
BG = "#f2f6fc"
WHITE = "#ffffff"
BAD = "#ff9b9b"
OVERDUE_BG = "#ffb3b3"
OVERDUE_FG = "#7a0000"
TEXT_COLOR = "#1f2d3d"
ACCENT = "#2f6fd0"
FONT = ("Segoe UI", 11)
FONT_BOLD = ("Segoe UI", 11, "bold")

XLSX_CONTENT_TYPES = (
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
    '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
    '<Default Extension="xml" ContentType="application/xml"/>'
    '<Override PartName="/xl/workbook.xml" '
    'ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
    '<Override PartName="/xl/worksheets/sheet1.xml" '
    'ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
    '</Types>'
)

XLSX_ROOT_RELS = (
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
    '<Relationship Id="rId1" '
    'Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" '
    'Target="xl/workbook.xml"/>'
    '</Relationships>'
)

XLSX_WORKBOOK = (
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
    'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
    '<sheets><sheet name="Заявки" sheetId="1" r:id="rId1"/></sheets>'
    '</workbook>'
)

XLSX_WORKBOOK_RELS = (
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
    '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
    '<Relationship Id="rId1" '
    'Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" '
    'Target="worksheets/sheet1.xml"/>'
    '</Relationships>'
)


def base_dir():
    if getattr(sys, "frozen", False):
        return os.path.dirname(sys.executable)
    return os.path.dirname(os.path.abspath(__file__))


def parse_date(text):
    try:
        return datetime.strptime(text, DATE_FORMAT).date()
    except ValueError:
        return None


def is_overdue(record, today):
    if record["status"] == "готова":
        return False
    due = parse_date(record["due"])
    if due is None:
        return False
    return due < today


def write_xlsx(path, headers, rows):
    def column_name(index):
        name = ""
        number = index + 1
        while number:
            number, remainder = divmod(number - 1, 26)
            name = chr(65 + remainder) + name
        return name

    def make_cell(row_number, column_index, value):
        reference = "%s%d" % (column_name(column_index), row_number)
        return '<c r="%s" t="inlineStr"><is><t xml:space="preserve">%s</t></is></c>' % (
            reference,
            escape(str(value)),
        )

    all_rows = [list(headers)] + [list(row) for row in rows]
    row_xml = []
    for row_number, row in enumerate(all_rows, start=1):
        cells = "".join(
            make_cell(row_number, column_index, value)
            for column_index, value in enumerate(row)
        )
        row_xml.append('<row r="%d">%s</row>' % (row_number, cells))

    cols = "".join(
        '<col min="%d" max="%d" width="%d" customWidth="1"/>' % (index + 1, index + 1, width)
        for index, width in enumerate((12, 22, 48, 20, 12, 14))
    )
    sheet = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
        '<cols>%s</cols><sheetData>%s</sheetData></worksheet>'
        % (cols, "".join(row_xml))
    )

    with zipfile.ZipFile(path, "w", zipfile.ZIP_DEFLATED) as archive:
        archive.writestr("[Content_Types].xml", XLSX_CONTENT_TYPES)
        archive.writestr("_rels/.rels", XLSX_ROOT_RELS)
        archive.writestr("xl/workbook.xml", XLSX_WORKBOOK)
        archive.writestr("xl/_rels/workbook.xml.rels", XLSX_WORKBOOK_RELS)
        archive.writestr("xl/worksheets/sheet1.xml", sheet)


class JournalApp:
    def __init__(self, root):
        self.root = root
        self.records = []
        self.path = os.path.join(base_dir(), "journal.json")
        self.search_var = tk.StringVar()
        self.status_var = tk.StringVar(value="Все")
        self.executor_var = tk.StringVar(value="Все")
        self.count_var = tk.StringVar(value="")
        self.columns = ("date", "client", "desc", "executor", "due", "status")
        self.setup_style()
        self.build_window()
        self.load_records()
        self.search_var.trace_add("write", lambda *args: self.refresh())
        self.refresh()
        self.root.after(300, self.show_summary)

    def setup_style(self):
        style = ttk.Style(self.root)
        if "clam" in style.theme_names():
            style.theme_use("clam")
        style.configure(".", background=BG, foreground=TEXT_COLOR, font=FONT)
        style.configure("TFrame", background=BG)
        style.configure("TLabel", background=BG, foreground=TEXT_COLOR, font=FONT)
        style.configure(
            "TButton",
            font=FONT_BOLD,
            padding=(14, 9),
            background="#e3ebf7",
            foreground=TEXT_COLOR,
        )
        style.map("TButton", background=[("active", "#d2ddf0"), ("pressed", "#c2d2ea")])
        style.configure(
            "Accent.TButton",
            font=FONT_BOLD,
            padding=(14, 9),
            background=ACCENT,
            foreground=WHITE,
        )
        style.map(
            "Accent.TButton",
            background=[("active", "#2559a8"), ("pressed", "#1d4a8f")],
            foreground=[("disabled", "#dfe6f2")],
        )
        style.configure(
            "Danger.TButton",
            font=FONT_BOLD,
            padding=(14, 9),
            background="#d9534f",
            foreground=WHITE,
        )
        style.map(
            "Danger.TButton",
            background=[("active", "#c1453f"), ("pressed", "#a93b36")],
            foreground=[("disabled", "#f0dede")],
        )
        style.configure("TEntry", padding=5)
        style.configure("TCombobox", padding=5)
        style.configure(
            "Treeview",
            font=FONT,
            rowheight=28,
            background=WHITE,
            fieldbackground=WHITE,
            foreground=TEXT_COLOR,
            borderwidth=1,
        )
        style.configure(
            "Treeview.Heading",
            font=FONT_BOLD,
            background="#dbe6f6",
            foreground=TEXT_COLOR,
            padding=6,
        )
        style.map("Treeview.Heading", background=[("active", "#c9d9ee")])
        style.map(
            "Treeview",
            background=[("selected", ACCENT)],
            foreground=[("selected", WHITE)],
        )

    def build_window(self):
        self.root.title("Журнал заявок")
        self.root.geometry("1180x680")
        self.root.minsize(940, 560)
        self.root.configure(bg=BG)

        container = ttk.Frame(self.root, padding=12)
        container.pack(fill="both", expand=True)
        container.columnconfigure(0, weight=1)
        container.rowconfigure(2, weight=1)

        toolbar = ttk.Frame(container)
        toolbar.grid(row=0, column=0, sticky="ew", pady=(0, 10))
        ttk.Button(
            toolbar,
            text="Добавить заявку",
            style="Accent.TButton",
            command=self.add_record,
        ).pack(side="left", padx=(0, 8))
        ttk.Button(toolbar, text="Редактировать", command=self.edit_record).pack(
            side="left", padx=(0, 8)
        )
        ttk.Button(
            toolbar, text="Удалить", style="Danger.TButton", command=self.delete_record
        ).pack(side="left", padx=(0, 8))
        ttk.Button(toolbar, text="Выгрузить в Excel", command=self.export_excel).pack(
            side="left", padx=(0, 8)
        )
        ttk.Button(toolbar, text="Сводка дня", command=self.show_summary).pack(side="right")

        filters = ttk.Frame(container)
        filters.grid(row=1, column=0, sticky="ew", pady=(0, 10))
        ttk.Label(filters, text="Статус:").pack(side="left", padx=(0, 6))
        self.status_combo = ttk.Combobox(
            filters,
            textvariable=self.status_var,
            values=list(STATUS_FILTERS),
            state="readonly",
            width=14,
        )
        self.status_combo.pack(side="left", padx=(0, 16))
        self.status_combo.bind("<<ComboboxSelected>>", lambda event: self.refresh())
        ttk.Label(filters, text="Исполнитель:").pack(side="left", padx=(0, 6))
        self.executor_combo = ttk.Combobox(
            filters,
            textvariable=self.executor_var,
            values=["Все"],
            state="readonly",
            width=20,
        )
        self.executor_combo.pack(side="left", padx=(0, 16))
        self.executor_combo.bind("<<ComboboxSelected>>", lambda event: self.refresh())
        ttk.Label(filters, text="Поиск:").pack(side="left", padx=(0, 6))
        ttk.Entry(filters, textvariable=self.search_var, width=28).pack(
            side="left", fill="x", expand=True
        )

        tree_frame = ttk.Frame(container)
        tree_frame.grid(row=2, column=0, sticky="nsew")
        tree_frame.columnconfigure(0, weight=1)
        tree_frame.rowconfigure(0, weight=1)

        headings = {
            "date": "Дата",
            "client": "Клиент",
            "desc": "Описание заявки",
            "executor": "Исполнитель",
            "due": "Срок",
            "status": "Статус",
        }
        widths = {
            "date": 100,
            "client": 180,
            "desc": 400,
            "executor": 170,
            "due": 100,
            "status": 120,
        }

        self.tree = ttk.Treeview(
            tree_frame, columns=self.columns, show="headings", selectmode="browse"
        )
        for column in self.columns:
            self.tree.heading(column, text=headings[column])
            self.tree.column(
                column,
                width=widths[column],
                anchor="w",
                stretch=(column == "desc"),
            )
        self.tree.tag_configure("overdue", background=OVERDUE_BG, foreground=OVERDUE_FG)
        self.tree.grid(row=0, column=0, sticky="nsew")
        scrollbar = ttk.Scrollbar(tree_frame, orient="vertical", command=self.tree.yview)
        scrollbar.grid(row=0, column=1, sticky="ns")
        self.tree.configure(yscrollcommand=scrollbar.set)
        self.tree.bind("<Double-1>", self.on_tree_double_click)

        ttk.Label(container, textvariable=self.count_var).grid(
            row=3, column=0, sticky="w", pady=(8, 0)
        )

    def on_tree_double_click(self, event):
        if self.tree.identify_region(event.x, event.y) == "cell":
            self.edit_record()

    def load_records(self):
        if not os.path.exists(self.path):
            self.records = []
            return
        try:
            with open(self.path, "r", encoding="utf-8") as file:
                data = json.load(file)
        except (OSError, ValueError) as error:
            messagebox.showerror(
                "Ошибка", "Не удалось прочитать журнал:\n%s" % error, parent=self.root
            )
            self.records = []
            return
        if not isinstance(data, list):
            messagebox.showerror(
                "Ошибка",
                "Файл журнала повреждён: ожидался список заявок.",
                parent=self.root,
            )
            self.records = []
            return

        records = []
        used = set()
        for index, item in enumerate(data):
            if not isinstance(item, dict):
                continue
            try:
                record_id = int(item.get("id"))
            except (TypeError, ValueError):
                record_id = 0
            if record_id <= 0 or record_id in used:
                record_id = max(used) + 1 if used else 1
            status = str(item.get("status", ""))
            if status not in STATUSES:
                status = STATUSES[0]
            records.append(
                {
                    "id": record_id,
                    "date": str(item.get("date", "")),
                    "client": str(item.get("client", "")),
                    "desc": str(item.get("desc", "")),
                    "executor": str(item.get("executor", "")),
                    "due": str(item.get("due", "")),
                    "status": status,
                }
            )
            used.add(record_id)
        self.records = records

    def save_records(self):
        try:
            with open(self.path, "w", encoding="utf-8") as file:
                json.dump(self.records, file, ensure_ascii=False, indent=2)
        except OSError as error:
            messagebox.showerror(
                "Ошибка", "Не удалось сохранить журнал:\n%s" % error, parent=self.root
            )

    def next_id(self):
        return max((record["id"] for record in self.records), default=0) + 1

    def sorted_records(self):
        def key(record):
            value = parse_date(record["date"])
            if value is None:
                return (1, 0, record["id"])
            return (0, value.toordinal(), record["id"])

        return sorted(self.records, key=key)

    def refresh_executor_values(self):
        values = ["Все"] + sorted(
            {record["executor"] for record in self.records if record["executor"]}
        )
        self.executor_combo.configure(values=values)
        if self.executor_var.get() not in values:
            self.executor_var.set("Все")

    def refresh(self):
        self.refresh_executor_values()
        for item in self.tree.get_children():
            self.tree.delete(item)

        word = self.search_var.get().strip().lower()
        status = self.status_var.get()
        executor = self.executor_var.get()
        today = datetime.now().date()
        shown = 0

        for record in self.sorted_records():
            if status != "Все" and record["status"] != status:
                continue
            if executor != "Все" and record["executor"] != executor:
                continue
            if word:
                haystack = " ".join(
                    (
                        record["date"],
                        record["client"],
                        record["desc"],
                        record["executor"],
                        record["due"],
                        record["status"],
                    )
                ).lower()
                if word not in haystack:
                    continue
            tags = ("overdue",) if is_overdue(record, today) else ()
            self.tree.insert(
                "",
                "end",
                iid=str(record["id"]),
                values=(
                    record["date"],
                    record["client"],
                    record["desc"].replace("\n", " "),
                    record["executor"],
                    record["due"],
                    record["status"],
                ),
                tags=tags,
            )
            shown += 1

        self.count_var.set("Показано заявок: %d из %d" % (shown, len(self.records)))

    def selected_record(self):
        selection = self.tree.selection()
        if not selection:
            return None
        record_id = int(selection[0])
        for record in self.records:
            if record["id"] == record_id:
                return record
        return None

    def add_record(self):
        self.open_editor(None)

    def edit_record(self):
        record = self.selected_record()
        if record is None:
            messagebox.showinfo(
                "Журнал заявок", "Выберите заявку в таблице.", parent=self.root
            )
            return
        self.open_editor(record)

    def delete_record(self):
        record = self.selected_record()
        if record is None:
            messagebox.showinfo(
                "Журнал заявок", "Выберите заявку в таблице.", parent=self.root
            )
            return
        question = "Удалить заявку клиента «%s» от %s?" % (
            record["client"],
            record["date"],
        )
        if not messagebox.askyesno("Удаление заявки", question, parent=self.root):
            return
        self.records = [item for item in self.records if item["id"] != record["id"]]
        self.save_records()
        self.refresh()

    def export_excel(self):
        items = self.tree.get_children()
        if not items:
            messagebox.showinfo(
                "Выгрузка в Excel", "Нет заявок для выгрузки.", parent=self.root
            )
            return
        path = filedialog.asksaveasfilename(
            parent=self.root,
            title="Выгрузить в Excel",
            defaultextension=".xlsx",
            initialfile="Заявки.xlsx",
            filetypes=[("Книга Excel", "*.xlsx")],
        )
        if not path:
            return
        headers = [self.tree.heading(column)["text"] for column in self.columns]
        rows = [self.tree.item(item, "values") for item in items]
        try:
            write_xlsx(path, headers, rows)
        except (OSError, ValueError) as error:
            messagebox.showerror(
                "Ошибка выгрузки",
                "Не удалось сохранить файл:\n%s" % error,
                parent=self.root,
            )
            return
        messagebox.showinfo(
            "Выгрузка в Excel",
            "Сохранено заявок: %d\nФайл: %s" % (len(rows), path),
            parent=self.root,
        )

    def show_summary(self):
        today = datetime.now().date()
        new_count = sum(1 for record in self.records if record["status"] == "новая")
        work_count = sum(1 for record in self.records if record["status"] == "в работе")
        overdue_count = sum(1 for record in self.records if is_overdue(record, today))
        messagebox.showinfo(
            "Сводка дня",
            "Новых заявок: %d\nВ работе: %d\nПросрочено: %d"
            % (new_count, work_count, overdue_count),
            parent=self.root,
        )

    def open_editor(self, record):
        window = tk.Toplevel(self.root)
        window.title("Редактирование заявки" if record else "Новая заявка")
        window.configure(bg=BG)
        window.resizable(False, False)
        window.transient(self.root)

        body = tk.Frame(window, bg=BG, padx=16, pady=16)
        body.pack(fill="both", expand=True)
        body.columnconfigure(1, weight=1)

        def add_label(text, row):
            tk.Label(body, text=text, bg=BG, fg=TEXT_COLOR, font=FONT).grid(
                row=row, column=0, sticky="w", pady=6, padx=(0, 12)
            )

        date_entry = tk.Entry(
            body, font=FONT, width=34, bg=WHITE, fg=TEXT_COLOR, relief="solid", bd=1
        )
        client_entry = tk.Entry(
            body, font=FONT, width=34, bg=WHITE, fg=TEXT_COLOR, relief="solid", bd=1
        )
        executor_entry = tk.Entry(
            body, font=FONT, width=34, bg=WHITE, fg=TEXT_COLOR, relief="solid", bd=1
        )
        due_entry = tk.Entry(
            body, font=FONT, width=34, bg=WHITE, fg=TEXT_COLOR, relief="solid", bd=1
        )
        desc_text = tk.Text(
            body,
            font=FONT,
            width=42,
            height=5,
            bg=WHITE,
            fg=TEXT_COLOR,
            relief="solid",
            bd=1,
            wrap="word",
        )
        status_combo = ttk.Combobox(
            body, values=list(STATUSES), state="readonly", width=32
        )

        add_label("Дата заявки (ДД.ММ.ГГГГ)", 0)
        date_entry.grid(row=0, column=1, sticky="ew", pady=6)
        add_label("Клиент", 1)
        client_entry.grid(row=1, column=1, sticky="ew", pady=6)
        add_label("Описание заявки", 2)
        desc_text.grid(row=2, column=1, sticky="ew", pady=6)
        add_label("Исполнитель", 3)
        executor_entry.grid(row=3, column=1, sticky="ew", pady=6)
        add_label("Срок (ДД.ММ.ГГГГ)", 4)
        due_entry.grid(row=4, column=1, sticky="ew", pady=6)
        add_label("Статус", 5)
        status_combo.grid(row=5, column=1, sticky="ew", pady=6)

        today_text = datetime.now().strftime(DATE_FORMAT)
        if record:
            date_entry.insert(0, record["date"])
            client_entry.insert(0, record["client"])
            desc_text.insert("1.0", record["desc"])
            executor_entry.insert(0, record["executor"])
            due_entry.insert(0, record["due"])
            status_combo.set(record["status"])
        else:
            date_entry.insert(0, today_text)
            due_entry.insert(0, today_text)
            status_combo.set(STATUSES[0])

        def on_save():
            for widget in (date_entry, client_entry, executor_entry, due_entry, desc_text):
                widget.configure(bg=WHITE)

            date_text = date_entry.get().strip()
            client = client_entry.get().strip()
            description = desc_text.get("1.0", "end").strip()
            executor = executor_entry.get().strip()
            due_text = due_entry.get().strip()
            status = status_combo.get().strip()

            date_value = parse_date(date_text)
            if date_value is None:
                date_entry.configure(bg=BAD)
                date_entry.focus_set()
                messagebox.showerror(
                    "Ошибка",
                    "Дата заявки должна быть в формате ДД.ММ.ГГГГ.",
                    parent=window,
                )
                return
            if not client:
                client_entry.configure(bg=BAD)
                client_entry.focus_set()
                messagebox.showerror("Ошибка", "Укажите клиента.", parent=window)
                return
            if not description:
                desc_text.configure(bg=BAD)
                desc_text.focus_set()
                messagebox.showerror(
                    "Ошибка", "Введите описание заявки.", parent=window
                )
                return
            if not executor:
                executor_entry.configure(bg=BAD)
                executor_entry.focus_set()
                messagebox.showerror("Ошибка", "Укажите исполнителя.", parent=window)
                return
            due_value = parse_date(due_text)
            if due_value is None:
                due_entry.configure(bg=BAD)
                due_entry.focus_set()
                messagebox.showerror(
                    "Ошибка",
                    "Срок должен быть в формате ДД.ММ.ГГГГ.",
                    parent=window,
                )
                return
            if due_value < date_value:
                due_entry.configure(bg=BAD)
                due_entry.focus_set()
                messagebox.showerror(
                    "Ошибка",
                    "Срок не может быть раньше даты заявки.",
                    parent=window,
                )
                return
            if status not in STATUSES:
                status = STATUSES[0]

            data = {
                "date": date_value.strftime(DATE_FORMAT),
                "client": client,
                "desc": description,
                "executor": executor,
                "due": due_value.strftime(DATE_FORMAT),
                "status": status,
            }
            if record is None:
                data["id"] = self.next_id()
                self.records.append(data)
            else:
                record.update(data)
            self.save_records()
            self.refresh()
            window.destroy()

        buttons = tk.Frame(body, bg=BG)
        buttons.grid(row=6, column=0, columnspan=2, sticky="e", pady=(14, 0))
        ttk.Button(buttons, text="Сохранить", style="Accent.TButton", command=on_save).pack(
            side="left", padx=(0, 8)
        )
        ttk.Button(buttons, text="Отмена", command=window.destroy).pack(side="left")

        window.bind("<Escape>", lambda event: window.destroy())
        window.wait_visibility()
        window.grab_set()
        date_entry.focus_set()
        self.root.wait_window(window)


def main():
    root = tk.Tk()
    JournalApp(root)
    root.mainloop()


if __name__ == "__main__":
    main()