import json, re, os, html, traceback
from pathlib import Path
import tkinter as tk
from tkinter import ttk, filedialog, messagebox
from openpyxl import load_workbook

APP_TITLE = "GENERATOR PETA PASAR V5"

def extract_coords(value):
    s = str(value or "").strip()
    m = re.search(r'@(-?\d+(?:\.\d+)?),(-?\d+(?:\.\d+)?)', s)
    if m: return float(m.group(1)), float(m.group(2)), "Google Maps"
    m = re.search(r'!3d(-?\d+(?:\.\d+)?)!4d(-?\d+(?:\.\d+)?)', s)
    if m: return float(m.group(1)), float(m.group(2)), "Google Maps"
    m = re.search(r'(-?\d{1,3}\.\d+)\s*[,;]\s*(-?\d{1,3}\.\d+)', s)
    if m: return float(m.group(1)), float(m.group(2)), "Koordinat"
    return None, None, "Belum terbaca"

def filename(name, n):
    s = re.sub(r"[^A-Za-z0-9_-]+", "-", str(name).strip()).strip("-").lower()
    return f"{n:03d}-{s or 'pasar'}.html"

def write_html(pages, fn, name, data):
    pages.mkdir(parents=True, exist_ok=True)
    safe_name = html.escape(name)
    safe_data = html.escape(data).replace("\n", "<br>\n")
    (pages / fn).write_text(f"""<!doctype html>
<html lang="id"><head>
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>{safe_name}</title>
<style>body{{font-family:Arial;margin:0;background:#f5f7fa}}.wrap{{max-width:900px;margin:auto;background:#fff;min-height:100vh;padding:28px}}.data{{line-height:1.7}}a{{display:inline-block;margin-top:22px}}</style>
</head><body><div class="wrap"><h1>{safe_name}</h1><div class="data">{safe_data}</div>
<a href="../index_peta.html">← Kembali ke Peta Pasar</a></div></body></html>""", encoding="utf-8")


def write_excel_report(project, records):
    """Membuat laporan Excel hasil proses generator."""
    from openpyxl import Workbook
    from openpyxl.styles import Font, PatternFill, Alignment
    from openpyxl.utils import get_column_letter

    wb = Workbook()
    ws = wb.active
    ws.title = "HASIL PEMERIKSAAN"
    headers = ["NO", "ID", "NAMA LEMBAGA", "LATITUDE", "LONGITUDE",
               "STATUS KOORDINAT", "SUMBER KOORDINAT", "NAMA FILE HTML",
               "STATUS FILE HTML", "ISI DATA"]
    ws.append(headers)
    for i, rec in enumerate(records, 1):
        ws.append([i, rec.get("id", ""), rec.get("nama", ""),
                   rec.get("latitude"), rec.get("longitude"),
                   rec.get("status_koordinat", "Belum terbaca"),
                   rec.get("sumber_koordinat", ""), rec.get("html", ""),
                   rec.get("status_file_html", "TIDAK ADA FILE"),
                   rec.get("isi_data", "")])
    for cell in ws[1]:
        cell.font = Font(bold=True, color="FFFFFF")
        cell.fill = PatternFill("solid", fgColor="176B3A")
        cell.alignment = Alignment(vertical="center", wrap_text=True)
    ws.freeze_panes = "A2"
    ws.auto_filter.ref = ws.dimensions
    widths = [8, 14, 38, 15, 15, 22, 48, 52, 20, 60]
    for idx, width in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(idx)].width = width
    for row in ws.iter_rows(min_row=2):
        for cell in row:
            cell.alignment = Alignment(vertical="top", wrap_text=True)
    out = project / "HASIL_GENERATOR_PETA_PASAR.xlsx"
    wb.save(out)
    return out

class App:
    def __init__(self, root):
        self.root=root
        root.title(APP_TITLE)
        root.geometry("780x720")
        root.minsize(700,650)

        self.excel=tk.StringVar()
        self.sheet=tk.StringVar()
        self.output=tk.StringVar(value=str(Path(__file__).resolve().parent))
        self.html_folder=tk.StringVar()
        self.data_mode=tk.StringVar(value="timpa")
        self.name_col=tk.StringVar()
        self.coord_col=tk.StringVar()
        self.data_col=tk.StringVar()
        self.status=tk.StringVar(value="Siap. Pilih file Excel.")
        self.summary=tk.StringVar(value="Belum ada proses.")

        main=ttk.Frame(root,padding=12); main.pack(fill="both",expand=True)

        ttk.Label(main,text=APP_TITLE,font=("Arial",18,"bold")).pack(anchor="w")
        ttk.Label(main,text="Excel → JSON → HTML",foreground="#666").pack(anchor="w",pady=(0,8))

        f=ttk.LabelFrame(main,text="MODE BANYAK DATA — EXCEL",padding=8); f.pack(fill="x")

        self.file_row(f)
        self.sheet_row(f)
        self.name_cb=self.combo(f,"NAMA LEMBAGA",self.name_col)
        self.coord_cb=self.combo(f,"KOORDINAT",self.coord_col)
        self.data_cb=self.combo(f,"ISI DATA",self.data_col)

        r=ttk.Frame(f); r.pack(fill="x",pady=3)
        ttk.Label(r,text="Folder peta_pasar",width=24).pack(side="left")
        ttk.Entry(r,textvariable=self.output).pack(side="left",fill="x",expand=True)
        ttk.Button(r,text="PILIH",command=self.pick_output).pack(side="left",padx=5)

        r=ttk.Frame(f); r.pack(fill="x",pady=3)
        ttk.Label(r,text="Folder HTML yang sudah ada",width=24).pack(side="left")
        ttk.Entry(r,textvariable=self.html_folder).pack(side="left",fill="x",expand=True)
        ttk.Button(r,text="PILIH FOLDER HTML",command=self.pick_html_folder).pack(side="left",padx=5)

        r=ttk.LabelFrame(f,text="MODE PENYIMPANAN DATA",padding=5); r.pack(fill="x",pady=4)
        ttk.Radiobutton(r,text="TIMPA DATA LAMA",variable=self.data_mode,value="timpa").pack(side="left",padx=8)
        ttk.Radiobutton(r,text="TAMBAH DATA",variable=self.data_mode,value="tambah").pack(side="left",padx=8)

        r=ttk.Frame(f); r.pack(fill="x",pady=(5,2))
        ttk.Button(r,text="▶ GENERATE",command=self.generate).pack(side="left",ipadx=14,ipady=4)
        ttk.Button(r,text="BUKA FOLDER",command=self.open_output).pack(side="left",padx=6,ipadx=8,ipady=4)

        f2=ttk.LabelFrame(main,text="HASIL PROSES",padding=7); f2.pack(fill="both",expand=True,pady=6)
        ttk.Label(f2,textvariable=self.summary,font=("Arial",10,"bold")).pack(anchor="w")
        self.log=tk.Text(f2,height=7,wrap="word",state="disabled")
        self.log.pack(fill="both",expand=True,pady=(4,0))

        # MODE CEPAT dibuat ringkas agar selalu terlihat.
        f3=ttk.LabelFrame(main,text="MODE CEPAT — 1 DATA MENDADAK",padding=7); f3.pack(fill="x")

        r=ttk.Frame(f3); r.pack(fill="x",pady=2)
        ttk.Label(r,text="NAMA",width=24).pack(side="left")
        self.quick_name=ttk.Entry(r); self.quick_name.pack(side="left",fill="x",expand=True)

        r=ttk.Frame(f3); r.pack(fill="x",pady=2)
        ttk.Label(r,text="KOORDINAT / GOOGLE MAPS",width=24).pack(side="left")
        self.quick_coord=ttk.Entry(r); self.quick_coord.pack(side="left",fill="x",expand=True)

        r=ttk.Frame(f3); r.pack(fill="x",pady=2)
        ttk.Label(r,text="ISI DATA",width=24).pack(side="left")
        self.quick_text=tk.Text(r,height=3,wrap="word")
        self.quick_text.pack(side="left",fill="x",expand=True)

        r=ttk.Frame(f3); r.pack(fill="x",pady=3)
        ttk.Button(r,text="AMBIL TXT",command=self.pick_txt).pack(side="left")
        ttk.Button(r,text="TAMBAH 1 DATA",command=self.quick_add).pack(side="left",padx=6,ipadx=10,ipady=3)

        bottom=ttk.Frame(main); bottom.pack(fill="x",pady=(6,0))
        ttk.Label(bottom,textvariable=self.status,foreground="#1769ff").pack(side="left")
        ttk.Button(bottom,text="TUTUP / CLOSE",command=root.destroy).pack(side="right",ipadx=12,ipady=4)

    def file_row(self,p):
        r=ttk.Frame(p); r.pack(fill="x",pady=3)
        ttk.Label(r,text="File Excel",width=24).pack(side="left")
        ttk.Entry(r,textvariable=self.excel).pack(side="left",fill="x",expand=True)
        ttk.Button(r,text="PILIH FILE",command=self.pick_excel).pack(side="left",padx=5)

    def sheet_row(self,p):
        r=ttk.Frame(p); r.pack(fill="x",pady=3)
        ttk.Label(r,text="Sheet",width=24).pack(side="left")
        self.sheet_cb=ttk.Combobox(r,textvariable=self.sheet,state="readonly")
        self.sheet_cb.pack(side="left",fill="x",expand=True)
        self.sheet_cb.bind("<<ComboboxSelected>>",lambda e:self.load_headers())
        ttk.Button(r,text="BACA SHEET",command=self.load_headers).pack(side="left",padx=5)

    def combo(self,p,label,var):
        r=ttk.Frame(p); r.pack(fill="x",pady=3)
        ttk.Label(r,text=label,width=24).pack(side="left")
        cb=ttk.Combobox(r,textvariable=var,state="readonly")
        cb.pack(side="left",fill="x",expand=True)
        return cb

    def logline(self,s):
        self.log.config(state="normal"); self.log.insert("end",s+"\n"); self.log.see("end"); self.log.config(state="disabled")
        self.root.update_idletasks()

    def pick_excel(self):
        p=filedialog.askopenfilename(title="Pilih file Excel",filetypes=[("Excel","*.xlsx *.xlsm")])
        if not p:return
        self.excel.set(p)
        try:
            wb=load_workbook(p,read_only=True,data_only=True)
            self.sheet_cb["values"]=wb.sheetnames
            if wb.sheetnames:
                self.sheet.set(wb.sheetnames[0]); self.load_headers()
            self.status.set("Excel berhasil dibaca.")
        except Exception as e:
            self.error("Gagal membaca Excel",e)

    def load_headers(self):
        try:
            wb=load_workbook(self.excel.get(),read_only=True,data_only=True)
            ws=wb[self.sheet.get()]
            first=next(ws.iter_rows(min_row=1,max_row=1,values_only=True))
            headers=[str(x).strip() for x in first if x is not None and str(x).strip()]
            for cb in (self.name_cb,self.coord_cb,self.data_cb): cb["values"]=headers
            def guess(words):
                for h in headers:
                    if any(w in h.upper() for w in words): return h
                return headers[0] if headers else ""
            self.name_col.set(guess(["NAMA"]))
            self.coord_col.set(guess(["KOORDINAT","GOOGLE MAP","LOKASI"]))
            self.data_col.set(guess(["ISI DATA","DATA","KETERANGAN"]))
            self.status.set("Sheet siap diproses.")
        except Exception as e:self.error("Gagal membaca sheet",e)

    def pick_output(self):
        p=filedialog.askdirectory(title="Pilih folder peta_pasar")
        if p:self.output.set(p)

    def pick_html_folder(self):
        p=filedialog.askdirectory(title="Pilih folder HTML profil yang sudah ada")
        if p:
            self.html_folder.set(p)
            self.status.set("Folder HTML dipilih.")

    def project(self):
        p=Path(self.output.get()); (p/"data").mkdir(parents=True,exist_ok=True); (p/"pages").mkdir(parents=True,exist_ok=True); return p

    def find_existing_html(self, filename_from_excel):
        """Cari file HTML persis sesuai kolom Excel D, tanpa membuat atau mengganti nama."""
        name = str(filename_from_excel or "").strip()
        if not name or not self.html_folder.get().strip():
            return None
        folder = Path(self.html_folder.get().strip())
        # Exact filename only; search selected folder and its subfolders.
        for candidate in folder.rglob("*"):
            if candidate.is_file() and candidate.name == name:
                return candidate
        return None

    def copy_existing_html_to_pages(self, source, pages):
        """Salin file yang sudah ada ke pages dengan nama identik; tidak menulis ulang isi HTML."""
        pages.mkdir(parents=True, exist_ok=True)
        target = pages / source.name
        if source.resolve() != target.resolve():
            import shutil
            shutil.copy2(source, target)
        return target

    def generate(self):
        self.log.config(state="normal"); self.log.delete("1.0","end"); self.log.config(state="disabled")
        try:
            if not self.excel.get() or not self.sheet.get():
                raise ValueError("File Excel dan Sheet harus dipilih.")
            if not self.name_col.get() or not self.coord_col.get() or not self.data_col.get():
                raise ValueError("Kolom NAMA, KOORDINAT, dan ISI DATA harus dipilih.")
            if not self.html_folder.get().strip():
                raise ValueError("Pilih folder HTML profil yang sudah ada.")

            self.logline("=== MULAI GENERATE ===")
            self.logline("Membuka Excel...")
            wb=load_workbook(self.excel.get(),read_only=True,data_only=True)
            ws=wb[self.sheet.get()]
            rows=ws.iter_rows(values_only=True)
            first=next(rows)
            headers=[str(x).strip() if x is not None else "" for x in first]
            idx={h:i for i,h in enumerate(headers) if h}

            project=self.project()
            records=[]; valid=0; no_coord=0; total=0

            for n,row in enumerate(rows,1):
                total+=1
                name=str(row[idx[self.name_col.get()]] or "").strip()
                coord=str(row[idx[self.coord_col.get()]] or "").strip()
                data=str(row[idx[self.data_col.get()]] or "").strip()
                if not name: continue
                lat,lon,source=extract_coords(coord)
                fn = Path(data).name if data else ""
                existing = self.find_existing_html(fn)
                if existing is not None:
                    self.copy_existing_html_to_pages(existing, project/"pages")
                    html_status = "ADA FILE"
                    self.logline(f"✓ HTML ditemukan: {fn}")
                else:
                    html_status = "TIDAK ADA FILE"
                    self.logline(f"⚠ HTML tidak ditemukan: {fn or '(nama file kolom D kosong)'}")
                    if fn and messagebox.askyesno("File HTML tidak ditemukan",
                            f"File ini tidak ditemukan:\n{fn}\n\nApakah saudara ingin memilih file HTML pengganti?"):
                        replacement = filedialog.askopenfilename(
                            title=f"Pilih file HTML pengganti untuk {fn}",
                            filetypes=[("File HTML","*.html *.htm"),("Semua file","*.*")])
                        if replacement:
                            # Copy the selected existing HTML content under the exact filename from Excel column D.
                            target = project/"pages"/fn
                            target.parent.mkdir(parents=True, exist_ok=True)
                            import shutil
                            shutil.copy2(replacement, target)
                            html_status = "FILE PENGGANTI DIPAKAI"
                            self.logline(f"✓ File pengganti digunakan untuk: {fn}")
                        else:
                            self.logline(f"⚠ Tidak memilih file pengganti untuk: {fn}")
                if lat is not None and lon is not None:
                    valid+=1
                else:
                    no_coord+=1
                    self.logline(f"⚠ {name} — koordinat belum terbaca")
                    if not messagebox.askyesno("Koordinat tidak ditemukan",
                            f"Koordinat untuk lembaga berikut tidak terbaca:\n{name}\n\nLanjutkan proses?"):
                        self.logline("Proses dibatalkan atas pilihan pengguna karena koordinat tidak ditemukan.")
                        self.status.set("Proses dibatalkan.")
                        return

                records.append({"id":f"PASAR-{n:03d}","nama":name,"latitude":lat,"longitude":lon,
                                "html":fn,"status_file_html":html_status,"isi_data":data,"sumber_koordinat":coord,
                                "status_koordinat":source})

            jp=project/"data"/"data.json"
            if self.data_mode.get() == "tambah" and jp.exists():
                old_records = json.loads(jp.read_text(encoding="utf-8"))
                existing_keys = {(str(r.get("nama","")).strip().casefold(), str(r.get("html","")).strip().casefold()) for r in old_records}
                next_num = len(old_records) + 1
                for rec in records:
                    key = (str(rec.get("nama","")).strip().casefold(), str(rec.get("html","")).strip().casefold())
                    if key in existing_keys:
                        continue
                    rec["id"] = f"PASAR-{next_num:03d}"
                    next_num += 1
                    old_records.append(rec)
                    existing_keys.add(key)
                records = old_records
            jp.write_text(json.dumps(records,ensure_ascii=False,indent=2),encoding="utf-8")
            report_path = write_excel_report(project, records)

            self.summary.set(f"HASIL: {len(records)} data | {valid} marker | {no_coord} tanpa koordinat")
            self.status.set("Generate BERHASIL.")
            self.logline("=== SELESAI ===")
            self.logline(f"Total data: {len(records)}")
            self.logline(f"Koordinat valid: {valid}")
            self.logline(f"Tanpa koordinat: {no_coord}")
            self.logline(f"JSON: {jp}")
            self.logline(f"Excel: {report_path}")
            messagebox.showinfo("Generate selesai",f"Berhasil!\n\nData: {len(records)}\nMarker: {valid}\nTanpa koordinat: {no_coord}\n\nLaporan Excel:\n{report_path}")
        except Exception as e:
            self.status.set("Generate GAGAL — lihat pesan kesalahan.")
            self.logline("!!! ERROR !!!")
            self.logline(str(e))
            self.error("Generate gagal",e)

    def pick_txt(self):
        p=filedialog.askopenfilename(title="Pilih file TXT",filetypes=[("Text","*.txt"),("Semua","*.*")])
        if not p:return
        try:
            try:text=Path(p).read_text(encoding="utf-8")
            except UnicodeDecodeError:text=Path(p).read_text(encoding="cp1252")
            self.quick_text.delete("1.0","end"); self.quick_text.insert("1.0",text)
            self.status.set("TXT berhasil dimasukkan.")
        except Exception as e:self.error("Gagal membaca TXT",e)

    def quick_add(self):
        try:
            name=self.quick_name.get().strip(); coord=self.quick_coord.get().strip(); data=self.quick_text.get("1.0","end").strip()
            if not name or not coord or not data: raise ValueError("NAMA, KOORDINAT, dan ISI DATA wajib diisi.")
            lat,lon,source=extract_coords(coord)
            if lat is None: raise ValueError("Koordinat belum terbaca.")
            replacement = filedialog.askopenfilename(
                title="Pilih HTML profil yang sudah ada untuk data cepat",
                filetypes=[("File HTML","*.html *.htm"),("Semua file","*.*")])
            if not replacement:
                self.status.set("Mode cepat dibatalkan: HTML yang sudah ada belum dipilih.")
                return
            project=self.project(); jp=project/"data"/"data.json"
            records=json.loads(jp.read_text(encoding="utf-8")) if jp.exists() else []
            n=len(records)+1; fn=Path(replacement).name
            target=project/"pages"/fn
            target.parent.mkdir(parents=True,exist_ok=True)
            import shutil
            if Path(replacement).resolve() != target.resolve():
                shutil.copy2(replacement,target)
            records.append({"id":f"PASAR-{n:03d}","nama":name,"latitude":lat,"longitude":lon,"html":fn,
                            "status_file_html":"ADA FILE","isi_data":data,"sumber_koordinat":coord,"status_koordinat":source})
            jp.write_text(json.dumps(records,ensure_ascii=False,indent=2),encoding="utf-8")
            write_excel_report(project, records)
            self.quick_name.delete(0,"end"); self.quick_coord.delete(0,"end"); self.quick_text.delete("1.0","end")
            self.summary.set(f"MODE CEPAT: {len(records)} data sekarang tersimpan")
            self.status.set("Data cepat BERHASIL ditambahkan.")
            self.logline(f"✓ MODE CEPAT: {name}")
            self.logline(f"JSON sekarang: {len(records)} data")
            messagebox.showinfo("Berhasil",f"{name}\nberhasil masuk ke data.json.")
        except Exception as e:self.error("Mode cepat gagal",e)

    def open_output(self):
        p=Path(self.output.get()); p.mkdir(parents=True,exist_ok=True)
        try:os.startfile(p)
        except Exception:pass

    def error(self,title,e):
        self.logline("Kesalahan: "+str(e))
        messagebox.showerror(title,str(e))

if __name__=="__main__":
    root=tk.Tk(); App(root); root.mainloop()