import csv
import sqlite3
import sys
from pathlib import Path
import tkinter as tk
from tkinter import ttk, messagebox, filedialog

ROOT = Path(__file__).resolve().parent
DATA_DIR = ROOT / "data"
DB_PATH = DATA_DIR / "workbench.db"

SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS tickets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    category TEXT NOT NULL,
    owner TEXT NOT NULL,
    status TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
"""

SEED_SQL = [
    ("Printer invoice gagal cetak", "Hardware", "Rina", "Open"),
    ("User tidak bisa login VPN", "Network", "Doni", "In Progress"),
    ("Permintaan upgrade RAM", "Asset", "Lina", "Open"),
]


def connect():
    DATA_DIR.mkdir(exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    conn.execute(SCHEMA_SQL)
    conn.commit()
    return conn


def seed_if_empty(conn):
    count = conn.execute("SELECT COUNT(*) FROM tickets").fetchone()[0]
    if count == 0:
        conn.executemany(
            "INSERT INTO tickets(title, category, owner, status) VALUES (?, ?, ?, ?)",
            SEED_SQL,
        )
        conn.commit()


class App(tk.Tk):
    def __init__(self):
        super().__init__()
        self.title("SQLite Reporting Workbench")
        self.geometry("1040x700")
        self.conn = connect()
        seed_if_empty(self.conn)

        header = tk.Label(self, text="SQLite Reporting Workbench", font=("Segoe UI", 22, "bold"), bg="#111827", fg="white", pady=14)
        header.pack(fill="x")

        wrap = tk.Frame(self, padx=12, pady=12)
        wrap.pack(fill="both", expand=True)

        form = tk.Frame(wrap)
        form.pack(fill="x", pady=(0, 10))

        self.title_var = tk.StringVar()
        self.category_var = tk.StringVar(value="Hardware")
        self.owner_var = tk.StringVar()
        self.status_var = tk.StringVar(value="Open")

        for idx, label in enumerate(["Judul", "Kategori", "PIC", "Status"]):
            tk.Label(form, text=label).grid(row=0, column=idx, sticky="w")

        tk.Entry(form, textvariable=self.title_var, width=38).grid(row=1, column=0, padx=(0, 8))
        ttk.Combobox(form, textvariable=self.category_var, values=["Hardware", "Software", "Network", "Asset"], width=18, state="readonly").grid(row=1, column=1, padx=(0, 8))
        tk.Entry(form, textvariable=self.owner_var, width=18).grid(row=1, column=2, padx=(0, 8))
        ttk.Combobox(form, textvariable=self.status_var, values=["Open", "In Progress", "Resolved"], width=18, state="readonly").grid(row=1, column=3, padx=(0, 8))

        tk.Button(form, text="Tambah", command=self.add_record, bg="#2563eb", fg="white").grid(row=1, column=4, padx=(0, 8))
        tk.Button(form, text="Seed Demo", command=self.seed_demo, bg="#0f766e", fg="white").grid(row=1, column=5, padx=(0, 8))
        tk.Button(form, text="Ekspor CSV", command=self.export_csv, bg="#7c3aed", fg="white").grid(row=1, column=6)

        self.tree = ttk.Treeview(wrap, columns=("id", "title", "category", "owner", "status", "created"), show="headings")
        for col, width in [("id", 60), ("title", 320), ("category", 120), ("owner", 120), ("status", 120), ("created", 180)]:
            self.tree.heading(col, text=col.upper())
            self.tree.column(col, width=width)
        self.tree.pack(fill="both", expand=True)

        bottom = tk.Frame(wrap)
        bottom.pack(fill="x", pady=(10, 0))
        self.summary_var = tk.StringVar()
        tk.Label(bottom, textvariable=self.summary_var, font=("Segoe UI", 10, "bold")).pack(side="left")
        tk.Button(bottom, text="Refresh Summary", command=self.refresh_view).pack(side="right")

        self.refresh_view()

    def add_record(self):
        if not self.title_var.get().strip() or not self.owner_var.get().strip():
            messagebox.showerror("Data belum lengkap", "Judul dan PIC wajib diisi.")
            return
        self.conn.execute(
            "INSERT INTO tickets(title, category, owner, status) VALUES (?, ?, ?, ?)",
            (self.title_var.get().strip(), self.category_var.get(), self.owner_var.get().strip(), self.status_var.get()),
        )
        self.conn.commit()
        self.title_var.set("")
        self.owner_var.set("")
        self.refresh_view()

    def seed_demo(self):
        self.conn.executemany(
            "INSERT INTO tickets(title, category, owner, status) VALUES (?, ?, ?, ?)",
            [
                ("Akun email tidak sinkron", "Software", "Bagas", "Open"),
                ("Switch lantai 2 tidak stabil", "Network", "Nina", "In Progress"),
            ],
        )
        self.conn.commit()
        self.refresh_view()

    def refresh_view(self):
        self.tree.delete(*self.tree.get_children())
        rows = self.conn.execute("SELECT id, title, category, owner, status, created_at FROM tickets ORDER BY id DESC").fetchall()
        for row in rows:
            self.tree.insert("", "end", values=row)
        total = self.conn.execute("SELECT COUNT(*) FROM tickets").fetchone()[0]
        open_count = self.conn.execute("SELECT COUNT(*) FROM tickets WHERE status != 'Resolved'").fetchone()[0]
        self.summary_var.set(f"Total tiket: {total} | Belum selesai: {open_count}")

    def export_csv(self):
        path = filedialog.asksaveasfilename(defaultextension=".csv", filetypes=[("CSV", "*.csv")])
        if not path:
            return
        rows = self.conn.execute("SELECT id, title, category, owner, status, created_at FROM tickets ORDER BY id DESC").fetchall()
        with open(path, "w", newline="", encoding="utf-8") as f:
            writer = csv.writer(f)
            writer.writerow(["id", "title", "category", "owner", "status", "created_at"])
            writer.writerows(rows)
        messagebox.showinfo("Berhasil", f"CSV disimpan ke:\n{path}")


def smoke_test():
    conn = connect()
    seed_if_empty(conn)
    total = conn.execute("SELECT COUNT(*) FROM tickets").fetchone()[0]
    print(f'{{"app":"sqlite-reporting-workbench-python","tickets":{total}}}')


if __name__ == "__main__":
    if "--smoke-test" in sys.argv:
        smoke_test()
    else:
        App().mainloop()
