#!/usr/bin/env python3
"""
Combined DB Browser and Floorplan Editor

This script merges functionality from db_browser.py and floorplan.py into a single
Tkinter application called Browser. It allows:
 - Selecting a Diagnostic source
 - Editing param.db (FloorplanTransformParam)
 - Browsing and editing multiZone.db tables (with polygon point selector)
 - Visualizing zones on images
 - Picking side-by-side floorplan points and saving LocalPolygon/GlobalPolygon

No external frameworks required — uses Tkinter and OpenCV like the originals.
"""
import os
import sqlite3
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import cv2
import numpy as np
import csv
import json

DIAGNOSTIC_DIR = "Diagnostic"
ALLOWED_IMAGE_EXT = (".jpg", ".jpeg", ".png", ".webp")
ALLOWED_VIDEO_EXT = (".mp4",)

# ---------------- Utilities ----------------

def list_sources():
    if not os.path.isdir(DIAGNOSTIC_DIR):
        return []
    entries = []
    for f in os.listdir(DIAGNOSTIC_DIR):
        p = os.path.join(DIAGNOSTIC_DIR, f)
        if os.path.isdir(p) and f != "Images":
            entries.append(f)
    entries.sort()
    return entries

# zone color loader like db_browser
def load_zone_colors(csv_path="zone_colors.csv"):
    colors = {}
    if not os.path.exists(csv_path):
        return colors
    try:
        with open(csv_path, newline="") as f:
            reader = csv.reader(f)
            for row in reader:
                if len(row) >= 2:
                    zone, hexcolor = row[0].strip(), row[1].strip()
                    if hexcolor.startswith("#") and len(hexcolor) >= 7:
                        b = int(hexcolor[5:7], 16)
                        g = int(hexcolor[3:5], 16)
                        r = int(hexcolor[1:3], 16)
                        colors[zone] = (b, g, r)
    except Exception:
        pass
    return colors

ZONE_COLORS = load_zone_colors("zone_colors.csv")

# ---------------- DB helpers ----------------

def ensure_table_floorplan(conn):
    cur = conn.cursor()
    cur.execute("""
    CREATE TABLE IF NOT EXISTS FloorplanTransformParam (
        param TEXT PRIMARY KEY,
        value TEXT
    )
    """)
    conn.commit()

def load_params(conn):
    ensure_table_floorplan(conn)
    cur = conn.cursor()
    cur.execute("SELECT param, value FROM FloorplanTransformParam")
    rows = {r[0]: r[1] for r in cur.fetchall()}
    for k in ("Orientation", "GlobalPolygon", "LocalPolygon", "FloorplanId"):
        if k not in rows:
            cur.execute("INSERT OR IGNORE INTO FloorplanTransformParam(param, value) VALUES (?, ?)", (k, ""))
            rows[k] = ""
    conn.commit()
    return rows

def save_params(conn, mapping):
    ensure_table_floorplan(conn)
    cur = conn.cursor()
    try:
        for k, v in mapping.items():
            cur.execute("INSERT INTO FloorplanTransformParam(param, value) VALUES (?, ?) ON CONFLICT(param) DO UPDATE SET value=excluded.value", (k, v))
        conn.commit()
    except Exception:
        conn.rollback()
        raise

# fetch zone points from multiZone.db tables like db_browser.fetch_zone_points
def fetch_zone_points(conn):
    zones = {}
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = [r[0] for r in cursor.fetchall()]
    for table in tables:
        if table in ("ZoneLineCategories", "ZoneLineTypes"):
            continue
        try:
            cursor.execute(f"SELECT type, points FROM {table}")
        except Exception:
            continue
        for type_name, points_str in cursor.fetchall():
            zones[type_name] = points_str
    return zones

def get_zone_display_data(conn, zone_colors):
    db_zones = fetch_zone_points(conn)
    display = {}
    for zone_name, points_str in db_zones.items():
        color = zone_colors.get(zone_name, (0,0,0))
        if zone_name not in zone_colors:
            # console warning
            print(f"[Missing] {zone_name} -> default black")
        points = []
        if points_str:
            for token in points_str.split("|"):
                try:
                    x, y = map(float, token.split(","))
                    points.append((x,y))
                except Exception:
                    pass
        display[zone_name] = (points, color)
    return display

# draw zones on image similar to db_browser.draw_zones_on_image
def draw_zones_on_image(img_path, conn, zone_colors):
    img = cv2.imread(img_path)
    if img is None:
        raise ValueError(f"Failed to load {img_path}")
    display_data = get_zone_display_data(conn, zone_colors)
    h, w = img.shape[:2]
    for zone, (points, color) in display_data.items():
        if not points:
            continue
        abs_points = np.array([(int(x*w), int(y*h)) for x,y in points], dtype=np.int32)
        cv2.polylines(img, [abs_points], isClosed=True, color=color, thickness=2)
        cv2.putText(img, zone, tuple(abs_points[0]), cv2.FONT_HERSHEY_SIMPLEX, 0.5, color, 1)
    return img

# ---------------- Image helpers (from floorplan) ----------------

def pick_raw_image_for_source(source):
    rect_dir = os.path.join(DIAGNOSTIC_DIR, "Images", "Raw")
    if not os.path.isdir(rect_dir):
        return None
    for ext in ALLOWED_IMAGE_EXT + ALLOWED_VIDEO_EXT:
        candidate = os.path.join(rect_dir, f"{source}{ext}")
        if os.path.isfile(candidate):
            return candidate
    # fallback case-insensitive
    files = [f for f in os.listdir(rect_dir) if os.path.splitext(f)[1].lower() in ALLOWED_IMAGE_EXT + ALLOWED_VIDEO_EXT]
    for f in files:
        if os.path.splitext(f)[0].lower() == source.lower():
            return os.path.join(rect_dir, f)
    return None

def pick_rectified_image_for_source(source):
    rect_dir = os.path.join(DIAGNOSTIC_DIR, "Images", "Rectified")
    if not os.path.isdir(rect_dir):
        return None
    for ext in ALLOWED_IMAGE_EXT + ALLOWED_VIDEO_EXT:
        candidate = os.path.join(rect_dir, f"{source}{ext}")
        if os.path.isfile(candidate):
            return candidate
    # fallback case-insensitive
    files = [f for f in os.listdir(rect_dir) if os.path.splitext(f)[1].lower() in ALLOWED_IMAGE_EXT + ALLOWED_VIDEO_EXT]
    for f in files:
        if os.path.splitext(f)[0].lower() == source.lower():
            return os.path.join(rect_dir, f)
    return None

def load_image_or_first_frame(path):
    ext = os.path.splitext(path)[1].lower()
    if ext in ALLOWED_IMAGE_EXT:
        return cv2.imread(path)
    elif ext in ALLOWED_VIDEO_EXT:
        cap = cv2.VideoCapture(path)
        ok, frame = cap.read()
        cap.release()
        if not ok or frame is None:
            return None
        return frame
    return None

# ---------------- Side-by-side picker (from floorplan) ----------------
class SideBySidePicker:
    def __init__(self, left_img, right_img, left_name="Left", right_name="Right"):
        self.left_orig = left_img.copy() if left_img is not None else None
        self.right_orig = right_img.copy() if right_img is not None else None
        if self.left_orig is None or self.right_orig is None:
            raise ValueError("Both left and right images must be provided.")
        self.left_name = left_name
        self.right_name = right_name

        # original sizes (pixels)
        self.orig_left_h, self.orig_left_w = self.left_orig.shape[:2]
        self.orig_right_h, self.orig_right_w = self.right_orig.shape[:2]

        # make displayed copies (possibly resized to fit max height)
        max_h = 900
        scale_left = min(1.0, max_h / float(self.orig_left_h))
        scale_right = min(1.0, max_h / float(self.orig_right_h))

        if scale_left < 1.0:
            disp_left_w = int(self.orig_left_w * scale_left)
            disp_left_h = int(self.orig_left_h * scale_left)
            self.left = cv2.resize(self.left_orig, (disp_left_w, disp_left_h), interpolation=cv2.INTER_AREA)
        else:
            self.left = self.left_orig.copy()

        if scale_right < 1.0:
            disp_right_w = int(self.orig_right_w * scale_right)
            disp_right_h = int(self.orig_right_h * scale_right)
            self.right = cv2.resize(self.right_orig, (disp_right_w, disp_right_h), interpolation=cv2.INTER_AREA)
        else:
            self.right = self.right_orig.copy()

        # displayed sizes (pixels)
        self.disp_left_h, self.disp_left_w = self.left.shape[:2]
        self.disp_right_h, self.disp_right_w = self.right.shape[:2]

        # point containers (in displayed coordinates)
        self.left_points = []
        self.right_points = []
        self.left_confirmed = []
        self.right_confirmed = []
        self.last_active_side = None
        self.left_window = "Rectified Picker"
        self.right_window = "Floorplan Picker"
        cv2.namedWindow(self.left_window, cv2.WINDOW_NORMAL | cv2.WINDOW_KEEPRATIO)
        cv2.namedWindow(self.right_window, cv2.WINDOW_NORMAL | cv2.WINDOW_KEEPRATIO)
        cv2.setMouseCallback(self.left_window, self._mouse_callback_left)
        cv2.setMouseCallback(self.right_window, self._mouse_callback_right)
        self.quit_requested = False
        self.quit_key = None


    def _mouse_callback_left(self, event, x, y, flags, param):
        self.last_active_side = "left"
        if event == cv2.EVENT_LBUTTONDOWN:
            self.left_points.append((int(x), int(y)))

    def _mouse_callback_right(self, event, x, y, flags, param):
        self.last_active_side = "right"
        if event == cv2.EVENT_LBUTTONDOWN:
            self.right_points.append((int(x), int(y)))

    def _draw_points_and_numbers_left(self, canvas):
        for idx, (x, y) in enumerate(self.left_points, start=1):
            cv2.circle(canvas, (x, y), 4, (0, 0, 255), -1)
            cv2.putText(canvas, str(idx), (x+6, y-6), cv2.FONT_HERSHEY_SIMPLEX, 0.6, (0,255,0), 2, cv2.LINE_AA)
        if len(self.left_points) > 1:
            cv2.polylines(canvas, [np.array(self.left_points)], isClosed=True, color=(0,255,0), thickness=2)

    def _draw_points_and_numbers_right(self, canvas):
        for idx, (x, y) in enumerate(self.right_points, start=1):
            cv2.circle(canvas, (x, y), 4, (0, 0, 255), -1)
            cv2.putText(canvas, str(idx), (x+6, y-6), cv2.FONT_HERSHEY_SIMPLEX, 0.6, (0,255,0), 2, cv2.LINE_AA)
        if len(self.right_points) > 1:
            cv2.polylines(canvas, [np.array(self.right_points)], isClosed=True, color=(0,255,0), thickness=2)

    def run(self):
        while True:
            left_canvas = self.left.copy()
            right_canvas = self.right.copy()
            cv2.putText(left_canvas, self.left_name, (8, 20), cv2.FONT_HERSHEY_SIMPLEX, 0.7, (255,255,255), 2, cv2.LINE_AA)
            cv2.putText(right_canvas, self.right_name, (8, 20), cv2.FONT_HERSHEY_SIMPLEX, 0.7, (255,255,255), 2, cv2.LINE_AA)
            self._draw_points_and_numbers_left(left_canvas)
            self._draw_points_and_numbers_right(right_canvas)
            cv2.imshow(self.left_window, left_canvas)
            cv2.imshow(self.right_window, right_canvas)
            key = cv2.waitKey(20) & 0xFF
            if key == 13:  # ENTER -> confirm current points snapshot
                if self.last_active_side == "left":
                    self.left_confirmed = list(self.left_points)
                elif self.last_active_side == "right":
                    self.right_confirmed = list(self.right_points)
                else:
                    self.left_confirmed = list(self.left_points)
                    self.right_confirmed = list(self.right_points)
            elif key in (ord("c"), ord("C")):
                self.left_points = []
                self.right_points = []
            elif key in (ord("a"), ord("A")):
                if self.last_active_side == "left" and self.left_points:
                    self.left_points.pop()
                elif self.last_active_side == "right" and self.right_points:
                    self.right_points.pop()
            elif key in (ord("q"), 27):
                self.quit_requested = True
                self.quit_key = key
                break
            if cv2.getWindowProperty(self.left_window, cv2.WND_PROP_VISIBLE) < 1 or cv2.getWindowProperty(self.right_window, cv2.WND_PROP_VISIBLE) < 1:
                self.quit_requested = True
                self.quit_key = None
                break
        cv2.destroyWindow(self.left_window)
        cv2.destroyWindow(self.right_window)

        left_final = self.left_confirmed if self.left_confirmed else list(self.left_points)
        right_final = self.right_confirmed if self.right_confirmed else list(self.right_points)

        # Guard against zero-sized displayed images (shouldn't happen)
        disp_lw = self.disp_left_w if self.disp_left_w > 0 else self.orig_left_w
        disp_lh = self.disp_left_h if self.disp_left_h > 0 else self.orig_left_h
        disp_rw = self.disp_right_w if self.disp_right_w > 0 else self.orig_right_w
        disp_rh = self.disp_right_h if self.disp_right_h > 0 else self.orig_right_h

        # Scale both left and right points back to original (full-resolution) image size
        left_final_scaled = [
            (int(x * (self.orig_left_w / float(disp_lw))), int(y * (self.orig_left_h / float(disp_lh))))
            for x, y in left_final
        ]
        right_final_scaled = [
            (int(x * (self.orig_right_w / float(disp_rw))), int(y * (self.orig_right_h / float(disp_rh))))
            for x, y in right_final
        ]

        data = {
            # these point lists are now in ORIGINAL image pixel coordinates (not displayed coords)
            "left_points_disp": left_final_scaled,
            "right_points_disp": right_final_scaled,
            # return original image shapes so callers can normalize/interpret correctly
            "left_img_shape": (self.orig_left_h, self.orig_left_w),
            "right_img_shape": (self.orig_right_h, self.orig_right_w),
        }
        return True, "Quit", data

# ---------------- Converters ----------------
def points_to_local_polygon_str(points, img_w, img_h):
    if not points:
        return ""
    tokens = []
    for x, y in points:
        nx = x / float(img_w)  # Normalize x-coordinate
        ny = y / float(img_h)  # Normalize y-coordinate
        tokens.append(f"{nx:.4f},{ny:.4f}")
    return "|".join(tokens)

def points_to_global_polygon_str(points):
    if not points:
        return ""
    tokens = []
    for x, y in points:
        tokens.append(f"{int(x)},{int(y)}")
    return "|".join(tokens)

# ---------------- Main combined GUI ----------------
class BrowserApp:
    def __init__(self, root):
        self.root = root
        root.title("Diagnostic Browser")
        try:
            root.state("zoomed")
        except Exception:
            pass
        top = ttk.Frame(root, padding=8)
        top.pack(fill="x")
        ttk.Label(top, text="Select Source:").pack(side="left")
        self.combo = ttk.Combobox(top, values=list_sources(), state="readonly", width=40)
        self.combo.pack(side="left", padx=5)
        if self.combo['values']:
            self.combo.current(0)
        self.combo.bind("<<ComboboxSelected>>", self.on_source_change)
        # Buttons for common actions
        btn_frame = ttk.Frame(top)
        btn_frame.pack(side="right")
        ttk.Button(btn_frame, text="Open DB Browser", command=self.open_db_browser).pack(side="left", padx=4)
        ttk.Button(btn_frame, text="Open Floorplan Editor", command=self.open_floorplan_editor).pack(side="left", padx=4)
        ttk.Button(btn_frame, text="Show Zones on Live View", command=self.show_zones_on_live_view).pack(side="left", padx=4)

        self.content = ttk.Frame(root)
        self.content.pack(fill="both", expand=True)
        self.current_source = None
        self.conn_map = {}
        # initial load
        if self.combo['values']:
            self.load_for_source(self.combo.get())

    def on_source_change(self, event):
        self.load_for_source(self.combo.get())

    def load_for_source(self, source):
        self.current_source = source
        # close previous connections
        for c in self.conn_map.values():
            try:
                c.close()
            except Exception:
                pass
        self.conn_map = {}
        src_dir = os.path.join(DIAGNOSTIC_DIR, source)
        param_db = os.path.join(src_dir, "param.db")
        multi_db = os.path.join(src_dir, "multiZone.db")
        # open or create param.db
        if not os.path.exists(param_db):
            os.makedirs(src_dir, exist_ok=True)
            connp = sqlite3.connect(param_db)
        else:
            connp = sqlite3.connect(param_db)
        self.conn_map['param.db'] = connp
        if os.path.exists(multi_db):
            connm = sqlite3.connect(multi_db)
            self.conn_map['multiZone.db'] = connm
        # show a simple summary panel
        for w in self.content.winfo_children():
            w.destroy()
        lbl = ttk.Label(self.content, text=f"Selected source: {source}", font=(None, 12))
        lbl.pack(pady=8)
        info = ttk.Label(self.content, text=f"Databases: param.db {'(exists)' if os.path.exists(param_db) else '(created)'} | multiZone.db {'(exists)' if os.path.exists(multi_db) else '(missing)'}")
        info.pack()

    # ---------------- DB Browser view ----------------
    def open_db_browser(self):
        if not self.current_source:
            messagebox.showerror("Error", "No source selected")
            return
        db_paths = {}
        src_dir = os.path.join(DIAGNOSTIC_DIR, self.current_source)
        db_paths['param.db'] = os.path.join(src_dir, 'param.db')
        db_paths['multiZone.db'] = os.path.join(src_dir, 'multiZone.db')
        win = tk.Toplevel(self.root)
        win.title(f"DB Browser - {self.current_source}")
        editor = TableFormEditor(win, db_paths)
        editor.current_source_folder = self.current_source

    # ---------------- Floorplan editor view ----------------
    def open_floorplan_editor(self):
        if not self.current_source:
            messagebox.showerror("Error", "No source selected")
            return
        win = tk.Toplevel(self.root)
        win.title(f"Floorplan Editor - {self.current_source}")
        app = FloorplanEditor(win, self.current_source)

    # ---------------- Show zones on rectified image ----------------
    def show_zones_on_live_view(self):
        if not self.current_source:
            messagebox.showerror("Error", "No source selected")
            return
        img_path = pick_raw_image_for_source(self.current_source)
        if not img_path:
            messagebox.showerror("Error", "No rectified image found for this source")
            return
        # Use multiZone.db if available
        conn = self.conn_map.get('multiZone.db')
        if not conn:
            messagebox.showerror("Error", "No multiZone.db available for this source")
            return
        try:
            img = draw_zones_on_image(img_path, conn, ZONE_COLORS)
        except Exception as e:
            messagebox.showerror("Error", str(e))
            return
        # show image in a simple cv window
        cv2.imshow(f"Zones - {self.current_source}", img)
        cv2.waitKey(0)
        cv2.destroyWindow(f"Zones - {self.current_source}")

# ------------------ Reused UI components from db_browser and floorplan ----------------
class TableFormEditor:
    def __init__(self, parent, db_paths):
        self.parent = parent
        self.db_paths = db_paths
        self.conn_map = {}
        self.current_db = None
        self.current_table = None
        self.entries = []
        self.current_source_folder = None
        self.sidebar = tk.Listbox(parent, width=30)
        self.sidebar.pack(side="left", fill="y")
        self.canvas = tk.Canvas(parent)
        self.scrollbar = ttk.Scrollbar(parent, orient="vertical", command=self.canvas.yview)
        self.scrollable_frame = ttk.Frame(self.canvas)
        self.canvas_window = self.canvas.create_window((0, 0), window=self.scrollable_frame, anchor="nw")
        self.scrollable_frame.bind(
            "<Configure>",
            lambda e: self.canvas.configure(scrollregion=self.canvas.bbox("all")),
        )
        self.canvas.bind(
            "<Configure>",
            lambda e: self.canvas.itemconfig(self.canvas_window, width=self.canvas.winfo_width()),
        )
        self.canvas.configure(yscrollcommand=self.scrollbar.set)
        self.canvas.pack(side="left", fill="both", expand=True)
        self.scrollbar.pack(side="right", fill="y")
        self.load_tables()
        self.sidebar.bind("<<ListboxSelect>>", self.show_table)

    def load_tables(self):
        for db_name, path in self.db_paths.items():
            if not os.path.exists(path):
                continue
            conn = sqlite3.connect(path)
            self.conn_map[db_name] = conn
            cursor = conn.cursor()
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
            for (table,) in cursor.fetchall():
                self.sidebar.insert("end", f"{db_name}:{table}")

    def show_table(self, event):
        if not self.sidebar.curselection():
            return
        selection = self.sidebar.get(self.sidebar.curselection())
        db_name, table = selection.split(":")
        self.current_db, self.current_table = db_name, table
        conn = self.conn_map[db_name]
        cursor = conn.cursor()
        for widget in self.scrollable_frame.winfo_children():
            widget.destroy()
        self.entries = []
        cursor.execute(f"SELECT * FROM {table}")
        rows = cursor.fetchall()
        col_names = [d[0] for d in cursor.description]
        if db_name == 'param.db' and set(['param', 'value']).issubset(col_names):
            self.show_param_editor(rows, col_names)
            return
        if db_name == 'multiZone.db' and table not in ["ZoneLineCategories", "ZoneLineTypes"]:
            self.show_category_editor(rows, col_names, table)
            return
        if rows:
            self.show_readonly_tree(rows, col_names)
        else:
            ttk.Label(self.scrollable_frame, text=f"No rows in {table}").pack()

    def show_param_editor(self, rows, col_names):
        ttk.Label(self.scrollable_frame, text="param.db editor (param = readonly, value = editable)").pack(pady=5)
        form_frame = ttk.Frame(self.scrollable_frame)
        form_frame.pack(fill="both", expand=True)
        ttk.Label(form_frame, text="Param", width=40).grid(row=0, column=0, padx=5, pady=2, sticky="w")
        ttk.Label(form_frame, text="Value", width=60).grid(row=0, column=1, padx=5, pady=2, sticky="w")
        self.entries = []
        for i, row in enumerate(rows, start=1):
            param, value = row
            ttk.Label(form_frame, text=param).grid(row=i, column=0, padx=5, pady=2, sticky="w")
            entry = ttk.Entry(form_frame)
            entry.insert(0, str(value))
            entry.grid(row=i, column=1, padx=5, pady=2, sticky="ew")
            self.entries.append((param, entry))
        save_btn = ttk.Button(self.scrollable_frame, text="Save Changes", command=self.save_param_changes)
        save_btn.pack(pady=10, fill="x")

    def save_param_changes(self):
        conn = self.conn_map[self.current_db]
        cursor = conn.cursor()
        try:
            for param, entry in self.entries:
                cursor.execute(f"UPDATE {self.current_table} SET value=? WHERE param= ?", (entry.get(), param))
            conn.commit()
            messagebox.showinfo("Success", "param.db saved successfully")
        except Exception as e:
            conn.rollback()
            messagebox.showerror("Error", str(e))

    def show_category_editor(self, rows, col_names, table):
        ttk.Label(self.scrollable_frame, text=f"{table} (editable)").pack(pady=5)
        conn = self.conn_map['multiZone.db']
        cursor = conn.cursor()
        cursor.execute(f"SELECT rowid, * FROM {table}")
        rows_with_rowid = cursor.fetchall()
        display_cols = col_names
        tree = ttk.Treeview(self.scrollable_frame, columns=display_cols, show="headings")
        for col in display_cols:
            tree.heading(col, text=col)
            tree.column(col, anchor="w", stretch=True)
        for row in rows_with_rowid:
            rowid = row[0]
            values = row[1:]
            tree.insert("", "end", iid=str(rowid), values=values)
        tree.pack(fill="both", expand=True)
        btn_frame = ttk.Frame(self.scrollable_frame)
        btn_frame.pack(fill="x", pady=5)
        ttk.Button(btn_frame, text="Insert Row", command=lambda: self.insert_row_dialog(table)).pack(side="left", padx=5)
        ttk.Button(btn_frame, text="Edit Row", command=lambda: self.edit_row_dialog(table, display_cols, tree)).pack(side="left", padx=5)
        ttk.Button(btn_frame, text="Delete Row", command=lambda: self.delete_row(table, tree)).pack(side="left", padx=5)
        self._last_tree = (table, tree)

    def delete_row(self, table, tree):
        sel = tree.selection()
        if not sel:
            messagebox.showerror("Error", "No row selected")
            return
        rowid = int(sel[0])
        if not messagebox.askyesno("Confirm", "Delete selected row?"):
            return
        conn = self.conn_map['multiZone.db']
        cursor = conn.cursor()
        try:
            cursor.execute(f"DELETE FROM {table} WHERE rowid=?", (rowid,))
            conn.commit()
            self.show_table(None)
            messagebox.showinfo("Deleted", "Row deleted")
        except Exception as e:
            conn.rollback()
            messagebox.showerror("Error", str(e))

    def insert_row_dialog(self, table):
        conn = self.conn_map['multiZone.db']
        cursor = conn.cursor()
        cursor.execute(f"PRAGMA table_info({table})")
        cols_info = cursor.fetchall()
        col_names = [c[1] for c in cols_info]
        self.row_dialog(table, col_names, values=None)

    def edit_row_dialog(self, table, col_names, tree):
        sel = tree.selection()
        if not sel:
            messagebox.showerror("Error", "No row selected")
            return
        rowid = int(sel[0])
        conn = self.conn_map['multiZone.db']
        cursor = conn.cursor()
        cursor.execute(f"SELECT * FROM {table} WHERE rowid=?", (rowid,))
        row = cursor.fetchone()
        if not row:
            messagebox.showerror("Error", "Row not found")
            return
        self.row_dialog(table, col_names, values=row, rowid=rowid)

    def row_dialog(self, table, col_names, values=None, rowid=None):
        popup = tk.Toplevel(self.parent)
        popup.title(f"{'Edit' if rowid else 'Insert'} {table}")
        popup.grab_set()
        entries = {}
        cursor = self.conn_map['multiZone.db'].cursor()
        cursor.execute("SELECT type FROM ZoneLineTypes WHERE category=?", (table,))
        allowed_types = [r[0] for r in cursor.fetchall()]
        for i, col in enumerate(col_names):
            ttk.Label(popup, text=col).grid(row=i, column=0, padx=5, pady=2, sticky="w")
            if col == 'category':
                ent = ttk.Entry(popup, state='readonly')
                ent.insert(0, table)
            elif col == 'type':
                ent = ttk.Combobox(popup, values=allowed_types, state='readonly')
                if values:
                    ent.set(values[i])
                elif allowed_types:
                    ent.set(allowed_types[0])
            elif col in ("region_id", "zone_id"):
                ent = ttk.Entry(popup)
                if values:
                    ent.insert(0, values[i])
                def validate_int(action, value_if_allowed):
                    if action == "1" and value_if_allowed != "" and not value_if_allowed.isdigit():
                        return False
                    return True
                vcmd = (popup.register(validate_int), "%d", "%P")
                ent.config(validate="key", validatecommand=vcmd)
            elif col == 'points':
                ent_var = tk.StringVar()
                if values:
                    ent_var.set(str(values[i]))
                def on_select_points():
                    img_path = os.path.join(DIAGNOSTIC_DIR, "Images/Raw", f"{self.current_source_folder}.jpg")
                    if not os.path.exists(img_path):
                        messagebox.showerror("Error", f"Image not found: {img_path}")
                        return
                    points_str = select_polygon_points(img_path, conn=self.conn_map['multiZone.db'], zone_colors=ZONE_COLORS)
                    ent_var.set(points_str)
                ent_frame = ttk.Frame(popup)
                ent_frame.grid(row=i, column=1, padx=5, pady=2, sticky="ew")
                entry = ttk.Entry(ent_frame, textvariable=ent_var, width=40)
                entry.pack(side="left", fill="x", expand=True)
                btn = ttk.Button(ent_frame, text="Select Points", command=on_select_points)
                btn.pack(side="left", padx=2)
                ent = entry
                entries[col] = ent_var
                continue
            else:
                ent = ttk.Entry(popup)
                if values:
                    ent.insert(0, str(values[i]))
            ent.grid(row=i, column=1, padx=5, pady=2, sticky="ew")
            entries[col] = ent
        def save_dialog():
            conn = self.conn_map['multiZone.db']
            cursor = conn.cursor()
            try:
                vals = [entries[c].get() for c in col_names]
                for idx, c in enumerate(col_names):
                    if c in ("region_id", "zone_id"):
                        if vals[idx] == "" or not vals[idx].isdigit():
                            raise ValueError(f"{c} must be an integer")
                if "type" in col_names:
                    tval = vals[col_names.index('type')]
                    cursor.execute("SELECT 1 FROM ZoneLineTypes WHERE category=? AND type= ?", (table, tval))
                    if cursor.fetchone() is None:
                        raise ValueError(f"type '{tval}' is not allowed for category '{table}'")
                col_list = ",".join(col_names)
                placeholders = ",".join("?" for _ in col_names)
                if rowid:
                    set_clause = ",".join(f"{c}=?" for c in col_names)
                    cursor.execute(f"UPDATE {table} SET {set_clause} WHERE rowid=?", (*vals, rowid))
                else:
                    cursor.execute(f"INSERT INTO {table} ({col_list}) VALUES ({placeholders})", vals)
                conn.commit()
                popup.destroy()
                self.show_table(None)
                messagebox.showinfo("Success", f"{'Updated' if rowid else 'Inserted'} row in {table}")
            except Exception as e:
                conn.rollback()
                messagebox.showerror("Error", str(e))
        ttk.Button(popup, text="Save", command=save_dialog).grid(row=len(col_names), column=0, columnspan=2, pady=10)

    def show_readonly_tree(self, rows, col_names):
        tree = ttk.Treeview(self.scrollable_frame, columns=col_names, show="headings")
        for col in col_names:
            tree.heading(col, text=col)
            tree.column(col, anchor="w", stretch=True)
        for row in rows:
            tree.insert("", "end", values=row)
        tree.pack(fill="both", expand=True)

# polygon selector (simple single-window selector like db_browser.select_polygon_points)
def select_polygon_points(img_path, conn=None, zone_colors=None):
    img = cv2.imread(img_path)
    if img is None:
        raise ValueError(f"Failed to load image: {img_path}")
    if conn is not None and zone_colors is not None:
        img = draw_zones_on_image(img_path, conn, zone_colors)
    h, w = img.shape[:2]
    points = []
    def mouse_callback(event, x, y, flags, param):
        if event == cv2.EVENT_LBUTTONDOWN:
            points.append((x, y))
    clone = img.copy()
    cv2.namedWindow("Draw Polygon")
    cv2.setMouseCallback("Draw Polygon", mouse_callback)
    while True:
        temp = clone.copy()
        if len(points) > 1:
            cv2.polylines(temp, [np.array(points)], isClosed=True, color=(0,255,0), thickness=2)
        for p in points:
            cv2.circle(temp, p, 3, (0,0,255), -1)
        cv2.imshow("Draw Polygon", temp)
        key = cv2.waitKey(1) & 0xFF
        if key == 13:
            break
        elif key == ord('c'):
            points = []
        elif key == ord('a'):
            if points: points.pop()
        elif key in (ord('q'), 27):
            points = []
            break
    cv2.destroyAllWindows()
    if not points:
        return ""
    normalized_points = [f"{x/w:.4f},{y/h:.4f}" for x, y in points]
    return "|".join(normalized_points)

# ---------------- Floorplan editor window ----------------
class FloorplanEditor:
    def __init__(self, parent, source):
        self.parent = parent
        self.source = source
        self.conn = None
        self.floorplan_path = None
        src_dir = os.path.join(DIAGNOSTIC_DIR, source)
        param_db = os.path.join(src_dir, 'param.db')
        if not os.path.exists(param_db):
            os.makedirs(src_dir, exist_ok=True)
            self.conn = sqlite3.connect(param_db)
        else:
            self.conn = sqlite3.connect(param_db)
        ensure_table_floorplan(self.conn)
        params = load_params(self.conn)
        header = ttk.Label(parent, text=f"Floorplan Transform Editor for {source}", font=(None, 12))
        header.pack(pady=6)
        form = ttk.Frame(parent)
        form.pack(fill="x", padx=8, pady=4)
        self.param_entries = {}
        for i, key in enumerate(("Orientation", "GlobalPolygon", "LocalPolygon", "FloorplanId")):
            ttk.Label(form, text=key, width=20).grid(row=i, column=0, sticky="w", padx=4, pady=4)
            ent = ttk.Entry(form, width=80)
            ent.insert(0, params.get(key, "") or "")
            ent.grid(row=i, column=1, sticky="w", padx=4, pady=4)
            self.param_entries[key] = ent
        btn_frame = ttk.Frame(parent)
        btn_frame.pack(fill="x", padx=8, pady=8)
        ttk.Button(btn_frame, text="Save Changes", command=self.save_changes).pack(side="left", padx=6)
        ttk.Button(btn_frame, text="Select Image", command=self.select_image).pack(side="left", padx=6)
        ttk.Button(btn_frame, text="Select Floorplan Points", command=self.select_floorplan_points).pack(side="left", padx=6)
        ttk.Button(btn_frame, text="Preview Floorplan Points", command=self.preview_floorplan_points).pack(side="left", padx=6)

    def select_image(self):
        floorplan_path = filedialog.askopenfilename(title="Select floorplan image or mp4 (first frame)", filetypes=[("Images and MP4", ("*.jpg","*.jpeg","*.png","*.webp","*.mp4")), ("Images", ("*.jpg","*.jpeg","*.png","*.webp")), ("MP4","*.mp4"), ("All files","*.*")])
        if not floorplan_path:
            messagebox.showinfo("Cancelled", "No floorplan selected.")
            return
        self.floorplan_path = floorplan_path
        messagebox.showinfo("Selected", f"Floorplan image selected: {os.path.basename(floorplan_path)}")

    def preview_floorplan_points(self):
        # Preview each image in its own window, with polygons/points drawn, read-only
        rectified_path = pick_rectified_image_for_source(self.source)
        floorplan_path = self.floorplan_path or self.param_entries["FloorplanId"].get()
        if not rectified_path:
            messagebox.showerror("Error", f"No rectified image/video found in {os.path.join(DIAGNOSTIC_DIR, 'Images', 'Rectified')}")
            return
        if not floorplan_path or not os.path.exists(floorplan_path):
            rel_path = os.path.join(DIAGNOSTIC_DIR, "Images", "Rectified", floorplan_path)
            if os.path.exists(rel_path):
                floorplan_path = rel_path
            else:
                messagebox.showerror("Error", "No floorplan image selected or found.")
                return
        left_img = load_image_or_first_frame(rectified_path)
        right_img = load_image_or_first_frame(floorplan_path)
        if left_img is None or right_img is None:
            messagebox.showerror("Error", "Failed to load images for preview")
            return
        # Use the same logic as SideBySidePicker for sizing (scale by height, max_h)
        max_h = 900
        left_h, left_w = left_img.shape[:2]
        right_h, right_w = right_img.shape[:2]
        scale_left = min(1.0, max_h / float(left_h))
        scale_right = min(1.0, max_h / float(right_h))
        left_new_w, left_new_h = int(left_w * scale_left), int(left_h * scale_left)
        right_new_w, right_new_h = int(right_w * scale_right), int(right_h * scale_right)
        left_disp = cv2.resize(left_img, (left_new_w, left_new_h), interpolation=cv2.INTER_AREA) if scale_left < 1.0 else left_img.copy()
        right_disp = cv2.resize(right_img, (right_new_w, right_new_h), interpolation=cv2.INTER_AREA) if scale_right < 1.0 else right_img.copy()

        # Draw overlays using the same coordinate logic as SideBySidePicker (no padding offsets)
        local_str = self.param_entries["LocalPolygon"].get()
        global_str = self.param_entries["GlobalPolygon"].get()

        left_draw_pts = []
        if local_str:
            for token in local_str.split("|"):
                try:
                    nx, ny = map(float, token.split(","))
                    x = int(nx * left_new_w)
                    y = int(ny * left_new_h)
                    left_draw_pts.append((x, y))
                except Exception:
                    pass
        if left_draw_pts:
            cv2.polylines(left_disp, [np.array(left_draw_pts)], isClosed=True, color=(0,255,0), thickness=2)
            for idx, (x, y) in enumerate(left_draw_pts, start=1):
                cv2.circle(left_disp, (x, y), 5, (0,0,255), -1)
                cv2.putText(left_disp, str(idx), (x+6, y-6), cv2.FONT_HERSHEY_SIMPLEX, 0.7, (255,255,0), 2, cv2.LINE_AA)
        cv2.putText(left_disp, "Rectified (left)", (8, 28), cv2.FONT_HERSHEY_SIMPLEX, 0.9, (255,255,255), 2, cv2.LINE_AA)

        right_draw_pts = []
        if global_str:
            for token in global_str.split("|"):
                try:
                    gx, gy = token.split(",")
                    x = int(float(gx) * scale_right)
                    y = int(float(gy) * scale_right)
                    right_draw_pts.append((x, y))
                except Exception:
                    pass
        if right_draw_pts:
            cv2.polylines(right_disp, [np.array(right_draw_pts)], isClosed=True, color=(0,255,0), thickness=2)
            for idx, (x, y) in enumerate(right_draw_pts, start=1):
                cv2.circle(right_disp, (x, y), 5, (0,0,255), -1)
                cv2.putText(right_disp, str(idx), (x+6, y-6), cv2.FONT_HERSHEY_SIMPLEX, 0.7, (255,255,0), 2, cv2.LINE_AA)
        cv2.putText(right_disp, "Floorplan (right)", (8, 28), cv2.FONT_HERSHEY_SIMPLEX, 0.9, (255,255,255), 2, cv2.LINE_AA)

        cv2.namedWindow("Rectified Picker", cv2.WINDOW_NORMAL | cv2.WINDOW_KEEPRATIO)
        cv2.namedWindow("Floorplan Picker", cv2.WINDOW_NORMAL | cv2.WINDOW_KEEPRATIO)
        cv2.imshow("Rectified Picker", left_disp)
        cv2.imshow("Floorplan Picker", right_disp)
        cv2.waitKey(0)
        cv2.destroyWindow("Rectified Picker")
        cv2.destroyWindow("Floorplan Picker")

    def save_changes(self):
        mapping = {k: self.param_entries[k].get() for k in self.param_entries}
        try:
            save_params(self.conn, mapping)
            messagebox.showinfo("Success", "Saved FloorplanTransformParam successfully.")
        except Exception as e:
            messagebox.showerror("Error saving", str(e))

    def select_floorplan_points(self):
        rectified_path = pick_rectified_image_for_source(self.source)
        if not rectified_path:
            messagebox.showerror("Error", f"No rectified image/video found in {os.path.join(DIAGNOSTIC_DIR, 'Images', 'Rectified')}")
            return
        floorplan_path = self.floorplan_path
        if not floorplan_path or not os.path.exists(floorplan_path):
            # Try to resolve relative path
            floorplan_id = self.param_entries["FloorplanId"].get()
            rel_path = os.path.join(DIAGNOSTIC_DIR, "Images", "Rectified", floorplan_id)
            if os.path.exists(rel_path):
                floorplan_path = rel_path
            else:
                messagebox.showerror("Error", "No floorplan image selected or found. Use 'Select Image' first.")
                return
        left_img = load_image_or_first_frame(rectified_path)
        right_img = load_image_or_first_frame(floorplan_path)
        if left_img is None or right_img is None:
            messagebox.showerror("Error", "Failed to load images for picking points")
            return
        picker = SideBySidePicker(left_img, right_img, left_name="Rectified (left)", right_name="Floorplan (right)")
        success, reason, data = picker.run()
        left_pts = data.get("left_points_disp", [])
        right_pts = data.get("right_points_disp", [])
        left_h, left_w = data["left_img_shape"]
        right_h, right_w = data["right_img_shape"]
        if not left_pts or not right_pts:
            messagebox.showerror("Error", "No points captured on one or both images.")
            return
        if len(left_pts) != len(right_pts):
            messagebox.showerror("Mismatch", f"Number of points mismatch: left={len(left_pts)} right={len(right_pts)}. They must be equal.")
            return
        local_str = points_to_local_polygon_str(left_pts, left_w, left_h)
        global_str = points_to_global_polygon_str(right_pts)
        self.param_entries["LocalPolygon"].delete(0, "end")
        self.param_entries["LocalPolygon"].insert(0, local_str)
        self.param_entries["GlobalPolygon"].delete(0, "end")
        self.param_entries["GlobalPolygon"].insert(0, global_str)
        messagebox.showinfo("Success", f"Captured {len(left_pts)} points on each image. Local/Global polygons filled. Remember to 'Save Changes' to write to DB.")

# ----------------- main -----------------
def main():
    root = tk.Tk()
    app = BrowserApp(root)
    root.mainloop()

if __name__ == "__main__":
    main()
