MetaFiller.py

# -*- coding: utf-8 -*-
# MetaFiller.py
# DaVinci Resolve script
# Purpose: Read continuity CSV/XLSX and write clip metadata into Resolve Media Pool clips.

import os
import sys

# Redirect stdout/stderr safely for debugging Resolve crashes
try:
    log_file = open(r"d:/04_ACTIVE_WORKBENCH/MetaFiller/metafiller_debug.log", "w", encoding="utf-8", buffering=1)
    sys.stdout = log_file
    sys.stderr = log_file
except Exception:
    pass

import csv
import re
import glob
import datetime
import traceback
import json
import threading
import tkinter as tk
from tkinter import ttk
from tkinter import filedialog
from tkinter import messagebox


def _load_resolve_module():
    """
    Import DaVinciResolveScript robustly.
    """
    try:
        import DaVinciResolveScript as dvr_script  # noqa
        return dvr_script
    except ImportError:
        pass

    module_path = os.environ.get("RESOLVE_SCRIPT_API")
    candidate_dirs = []

    if module_path:
        candidate_dirs.append(os.path.join(module_path, "Modules"))

    if sys.platform.startswith("win"):
        program_data = os.environ.get("PROGRAMDATA", r"C:\ProgramData")
        candidate_dirs.append(
            os.path.join(
                program_data,
                "Blackmagic Design",
                "DaVinci Resolve",
                "Support", "Developer", "Scripting", "Modules",
            )
        )
    elif sys.platform == "darwin":
        candidate_dirs.append(
            "/Library/Application Support/Blackmagic Design/"
            "DaVinci Resolve/Developer/Scripting/Modules"
        )
    else:  # Linux
        candidate_dirs.append(
            "/opt/resolve/Developer/Scripting/Modules"
        )
        candidate_dirs.append(
            "/home/resolve/Developer/Scripting/Modules"
        )

    for d in candidate_dirs:
        if d and os.path.isdir(d) and d not in sys.path:
            sys.path.append(d)

    try:
        import DaVinciResolveScript as dvr_script  # noqa
        return dvr_script
    except ImportError as e:
        raise ImportError(
            f"Could not import DaVinciResolveScript. Original error: {e}"
        )


dvr_script = _load_resolve_module()


# =========================
# SETTINGS MANAGEMENT
# =========================

SETTINGS_PATH = r"d:\04_ACTIVE_WORKBENCH\MetaFiller\metafiller_settings.json"

def load_settings():
    default_dir = r"D:\04_ACTIVE_WORKBENCH\PSA_Kolektif\02_Project_Files\Continuity_Logs"
    if not os.path.exists(default_dir):
        default_dir = r"d:\04_ACTIVE_WORKBENCH\MetaFiller"
        
    defaults = {
        "last_csv_path": "",
        "last_dir": default_dir,
        "scan_scope": "SELECTED_BIN_WITH_SUBFOLDERS",
        "bin_filter_string": "Raw_Camera",
        "overwrite": False,
        "apply_audio": True,
        "timeline_group_by": "Scene",
        "timeline_include": "Good Takes Only",
        "timeline_order": "Scene > Shot > Take > Camera",
        "timeline_bin_mode": "Current Bin",
        "timeline_custom_bin_path": "Master/05_Timeline_Comp",
    }
    if os.path.exists(SETTINGS_PATH):
        try:
            with open(SETTINGS_PATH, "r") as f:
                data = json.load(f)
                defaults.update(data)
        except Exception:
            pass
    return defaults


def save_settings(settings):
    try:
        os.makedirs(os.path.dirname(SETTINGS_PATH), exist_ok=True)
        with open(SETTINGS_PATH, "w") as f:
            json.dump(settings, f, indent=4)
    except Exception:
        pass


# =========================
# CONSTANTS & HEADERS
# =========================

# Resolve metadata field mapping.
METADATA_MAP = {
    "SCENE": "Scene",
    "TAKE": "Take",
    "SHOT": "Shot",
    "CAMERA": "Camera #",
    "NOTES": "Comments",
    "SHOT_SIZE": "Shot Type",
}

# Clip color mapping based on GOOD_NG value.
# Resolve supports: Orange, Apricot, Yellow, Lime, Olive, Green,
# Teal, Navy, Blue, Purple, Violet, Pink, Tan, Beige, Brown, Chocolate
CLIP_COLOR_MAP = {
    "G": "Green",
    "GOOD": "Green",
    "NG": "Red",       # Red is not in official list; we use Pink as closest
    "NF": "Yellow",    # Not Focused / technical fail
}
# Resolve does not have a "Red" clip color. Use these fallbacks:
CLIP_COLOR_FALLBACK = {
    "Red": "Pink",
}

# Flag color mapping for GOOD/NG markers.
# Resolve flag colors: Blue, Cyan, Green, Yellow, Red, Pink, Purple, Fuchsia, Rose, Lavender, Sky, Mint, Lemon, Sand, Cocoa, Cream
FLAG_COLOR_MAP = {
    "G": "Green",
    "GOOD": "Green",
    "NG": "Red",
    "NF": "Yellow",
}

# Deterministic scene colors: cycle through these for scene-based coloring.
SCENE_COLOR_PALETTE = [
    "Orange", "Apricot", "Yellow", "Lime", "Olive", "Green",
    "Teal", "Navy", "Blue", "Purple", "Violet", "Pink",
    "Tan", "Beige", "Brown", "Chocolate",
]

EXTRA_FIELDS_FOR_KEYWORDS = [
    "GOOD_NG",
    "SHOT_SIZE",
    "LENS",
    "FSTOP",
    "AUDIO_GNG",
    "AUDIO_NAME",
]

REQUIRED_COLUMNS = [
    "SCENE",
    "TAKE",
    "SHOT",
    "CLIP_NAME",
    "CAMERA",
    "GOOD_NG",
    "SHOT_SIZE",
    "LENS",
    "FSTOP",
    "AUDIO_GNG",
    "AUDIO_NAME",
    "NOTES",
]
_MIN_HEADER_MATCHES = 3


HEADER_ALIASES = {
    "SCENE": "SCENE",
    "SCENENO": "SCENE",
    "TAKE": "TAKE",
    "TAKENO": "TAKE",
    "SHOT": "SHOT",
    "SHOTNO": "SHOT",
    "CLIPNAME": "CLIP_NAME",
    "CLIP": "CLIP_NAME",
    "FILENAME": "CLIP_NAME",
    "CLIPNAMEFIXED": "CLIP_NAME_FIXED",
    "CLIPNAMEFIX": "CLIP_NAME_FIXED",
    "CLIPFIXED": "CLIP_NAME_FIXED",
    "CLIPFIX": "CLIP_NAME_FIXED",
    "CAMERA": "CAMERA",
    "CAM": "CAMERA",
    "GOODNG": "GOOD_NG",
    "GOODBNG": "GOOD_NG",
    "SHOTSIZE": "SHOT_SIZE",
    "SIZE": "SHOT_SIZE",
    "LENS": "LENS",
    "FSTOP": "FSTOP",
    "TSTOP": "FSTOP",
    "APERTURE": "FSTOP",
    "AUDIOGNG": "AUDIO_GNG",
    "AUDIOGOODNG": "AUDIO_GNG",
    "AUDIONAME": "AUDIO_NAME",
    "AUDIOFILE": "AUDIO_NAME",
    "CATATAN": "NOTES",
    "NOTES": "NOTES",
    "NOTE": "NOTES",
    "KETERANGAN": "NOTES",
    "SHOOTDAY": "SHOOT_DAY",
    "SHOOT_DAY": "SHOOT_DAY",
    "DAY": "SHOOT_DAY",
    "PRODUCTIONDAY": "SHOOT_DAY",
}


# =========================
# HELPERS
# =========================

def normalize_clip_name(name):
    if not name:
        return ""
    # Strip spaces from inside the name so typos like "C1632 . MP 4" become "C1632.MP4"
    return re.sub(r"\s+", "", os.path.basename(str(name))).upper()


def natural_sort_key(s):
    if not s:
        return []
    return [int(text) if text.isdigit() else text.lower() for text in re.split(r'(\d+)', str(s))]


def sanitize_group_name(name):
    if not name:
        return "Unknown"
    if str(name).isdigit():
        return f"{int(name):02d}"
    return re.sub(r"[^A-Za-z0-9_\-]", "_", str(name)).strip("_")


def get_group_key_and_name(row, group_by):
    if group_by == "Entire CSV":
        return "All", "Comp_All"
    elif group_by == "Scene":
        val = row.get("SCENE", "").strip()
        name_part = sanitize_group_name(val)
        return val, f"Comp_Scene_{name_part}"
    elif group_by == "Shoot Day":
        val = row.get("SHOOT_DAY", "").strip()
        name_part = sanitize_group_name(val)
        return val, f"Comp_Day_{name_part}"
    elif group_by == "Camera":
        val = row.get("CAMERA", "").strip()
        name_part = sanitize_group_name(val)
        return val, f"Comp_Cam_{name_part}"
    return "All", "Comp_All"



def get_unique_timeline_name(project, base_name):
    existing_names = set()
    
    get_count = getattr(project, "GetTimelineCount", None)
    get_by_idx = getattr(project, "GetTimelineByIndex", None)
    
    if get_count and callable(get_count) and get_by_idx and callable(get_by_idx):
        try:
            timeline_count = get_count()
            for i in range(1, timeline_count + 1):
                t = get_by_idx(i)
                if t:
                    existing_names.add(t.GetName().upper())
        except Exception:
            pass
            
    if base_name.upper() not in existing_names:
        return base_name
        
    ts = timestamp()
    candidate = f"{base_name}_{ts}"
    if candidate.upper() not in existing_names:
        return candidate
        
    idx = 1
    while f"{base_name}_{ts}_{idx}".upper() in existing_names:
        idx += 1
    return f"{base_name}_{ts}_{idx}"

def parse_audio_names(raw_name):
    """
    Parses audio names from CSV (e.g. REC1/REC2/S24/SH4/TK1)
    Returns a list of expected base names (e.g. ['REC1S24SH4TK1', 'REC2S24SH4TK1'])
    """
    raw_name = clean_value(raw_name).upper()
    if not raw_name or raw_name == "MOS":
        return []
    
    # Split by slash and remove empty parts
    parts = [p.strip() for p in raw_name.split("/") if p.strip()]
    if not parts:
        return []
        
    mics = []
    suffix_parts = []
    
    for part in parts:
        # Check if part is a mic identifier (e.g. REC1, REC 2, CAMA)
        # It's a mic if it starts with REC or CAM, or if we already have some mics and haven't hit scene yet.
        # But simply looking for "REC" or just collecting early parts until we hit "S" or "SH" is safer.
        if part.startswith("REC") or part.startswith("CAM"):
            mics.append(part.replace(" ", ""))
        elif part.startswith("S") or part.startswith("SH") or part.startswith("TK") or part.startswith("PA"):
            suffix_parts.append(part.replace(" ", ""))
        else:
            # If we don't recognize it, just assume it's part of the suffix if mics already exist
            if not mics:
                mics.append(part.replace(" ", ""))
            else:
                suffix_parts.append(part.replace(" ", ""))
                
    if not mics:
        return ["".join(parts).replace(" ", "")]
        
    suffix_str = "".join(suffix_parts)
    return [f"{mic}{suffix_str}" for mic in mics]


def get_audio_track_idx(base, expected_bases):
    if not base:
        return 3
    match = re.search(r"REC\s*(\d+)", base, re.IGNORECASE)
    if match:
        mic_num = int(match.group(1))
        return 3 + (mic_num - 1)
    
    try:
        idx = expected_bases.index(base)
        return 3 + idx
    except ValueError:
        return 3


def parse_scene_shot_take_from_audio_name(audio_name_raw):
    raw_name = clean_value(audio_name_raw).upper()
    if not raw_name:
        return "", "", ""
        
    parts = [p.strip() for p in raw_name.split("/") if p.strip()]
    sc = ""
    sh = ""
    tk = ""
    
    for p in parts:
        if p.startswith("S") and not p.startswith("SH"):
            sc = p[1:]
        elif p.startswith("SH"):
            sh = p[2:]
        elif p.startswith("TK"):
            tk = p[2:]
            
    return sc, sh, tk


def determine_audio_category(path_str, fallback="Dialogue"):
    if not path_str:
        return fallback
    path_lower = str(path_str).lower()
    if any(k in path_lower for k in ["sfx", "sound effect", "effect", "fx"]):
        return "Effect"
    elif any(k in path_lower for k in ["music", "bgm", "ost"]):
        return "Music"
    elif any(k in path_lower for k in ["silence", "mos"]):
        return "Silence"
    elif any(k in path_lower for k in ["dialogue", "dialog", "voiceover", "vo"]):
        return "Dialogue"
    return fallback


def timecode_to_frames(tc_str, fps):
    if not tc_str:
        return 0
    parts = tc_str.strip().split(":")
    if len(parts) != 4:
        return 0
    try:
        h, m, s, f = int(parts[0]), int(parts[1]), int(parts[2]), int(parts[3])
        total_frames = int(((h * 3600) + (m * 60) + s) * fps + f)
        return total_frames
    except Exception:
        return 0


def get_clip_duration_frames(clip, fps):
    try:
        val = clip.GetClipProperty("Frames")
        if val and str(val).isdigit():
            return int(val)
    except Exception:
        pass

    try:
        val_dur = clip.GetClipProperty("Duration")
        if val_dur:
            return timecode_to_frames(val_dur, fps)
    except Exception:
        pass
        
    return 0


def clean_value(value):
    if value is None:
        return ""
    return str(value).strip()


def timestamp():
    return datetime.datetime.now().strftime("%Y%m%d_%H%M%S")


def detect_day_number(path):
    name = os.path.basename(str(path))
    match = re.search(r"day\s*0*(\d+)", name, re.IGNORECASE)
    if match:
        return int(match.group(1))
    return None


def get_resolve():
    injected = globals().get("resolve")
    if injected is not None:
        return injected

    injected_fusion = globals().get("fusion")
    if injected_fusion is not None:
        try:
            resolve = injected_fusion.GetResolve()
            if resolve is not None:
                return resolve
        except Exception:
            pass

    try:
        import builtins
        if getattr(builtins, "resolve", None) is not None:
            return builtins.resolve
    except Exception:
        pass

    try:
        import builtins
        if getattr(builtins, "fusion", None) is not None:
            resolve = builtins.fusion.GetResolve()
            if resolve is not None:
                return resolve
    except Exception:
        pass


    resolve = dvr_script.scriptapp("Resolve")
    if resolve is not None:
        return resolve


    raise RuntimeError(
        "Could not connect to DaVinci Resolve.\n"
        "Ensure Resolve is running and scripting is enabled."
    )


def find_or_create_folder(media_pool, path_str):
    root = media_pool.GetRootFolder()
    parts = [p.strip() for p in path_str.replace("\\", "/").split("/") if p.strip()]
    if not parts:
        return root
    
    if parts[0].lower() in ("master", "root"):
        current_folder = root
        parts = parts[1:]
    else:
        current_folder = root
        
    for part in parts:
        found = None
        for sub in current_folder.GetSubFolderList():
            if sub.GetName() == part:
                found = sub
                break
        if found:
            current_folder = found
        else:
            new_folder = media_pool.AddSubFolder(current_folder, part)
            if not new_folder:
                return current_folder
            current_folder = new_folder
            
    return current_folder


def get_all_folder_paths():
    try:
        resolve = get_resolve()
        if not resolve:
            return ["Master"]
        project = resolve.GetProjectManager().GetCurrentProject()
        if not project:
            return ["Master"]
        media_pool = project.GetMediaPool()
        root = media_pool.GetRootFolder()
        
        paths = []
        def walk(folder, current_path):
            paths.append(current_path)
            for sub in folder.GetSubFolderList():
                walk(sub, current_path + "/" + sub.GetName())
        
        walk(root, "Master")
        return paths
    except Exception:
        return ["Master"]



def collect_clips_with_paths(media_pool, scan_scope, filter_str=None):
    if not media_pool:
        return {}, {}, {}
    try:
        root_folder = media_pool.GetRootFolder()
        if not root_folder:
            return {}, {}, {}
    except Exception:
        return {}, {}, {}
        
    try:
        current_folder = media_pool.GetCurrentFolder()
        if not current_folder:
            current_folder = root_folder
    except Exception:
        current_folder = root_folder


    current_folder_path = []

    def find_path(curr, target, current_accum):
        if curr.GetUniqueId() == target.GetUniqueId():
            return current_accum
        for sub in curr.GetSubFolderList():
            res = find_path(sub, target, current_accum + [sub.GetName()])
            if res is not None:
                return res
        return None

    if scan_scope in ("CURRENT_SELECTED_BIN", "SELECTED_BIN_WITH_SUBFOLDERS"):
        if current_folder.GetUniqueId() == root_folder.GetUniqueId():
            current_folder_path = ["Master"]
        else:
            path_tail = find_path(root_folder, current_folder, [])
            if path_tail is not None:
                current_folder_path = ["Master"] + path_tail
            else:
                current_folder_path = [current_folder.GetName()]

    all_clips = {}

    def walk_folder(folder, path_list, recursive):
        path_str = "/".join(path_list)
        for clip in folder.GetClipList():
            try:
                if clip.GetClipProperty("Type") == "Timeline":
                    continue
            except Exception:
                pass
            clip_name = normalize_clip_name(clip.GetName())
            if not clip_name:
                continue
            all_clips.setdefault(clip_name, []).append((clip, path_str))


        if recursive:
            for sub in folder.GetSubFolderList():
                walk_folder(sub, path_list + [sub.GetName()], recursive)

    if scan_scope == "ENTIRE_MEDIA_POOL":
        walk_folder(root_folder, ["Master"], recursive=True)
    elif scan_scope == "CURRENT_SELECTED_BIN":
        walk_folder(current_folder, current_folder_path, recursive=False)
    elif scan_scope == "SELECTED_BIN_WITH_SUBFOLDERS":
        walk_folder(current_folder, current_folder_path, recursive=True)
    elif scan_scope == "BIN_PATH_CONTAINS_FILTER":
        walk_folder(root_folder, ["Master"], recursive=True)
        filtered_clips = {}
        filter_upper = filter_str.upper() if filter_str else ""
        for name, clip_list in all_clips.items():
            matching_items = []
            for clip, path_str in clip_list:
                if filter_upper in path_str.upper():
                    matching_items.append((clip, path_str))
            if matching_items:
                filtered_clips[name] = matching_items
        all_clips = filtered_clips

    clips_by_name = {}
    duplicates = {}
    clip_paths = {}

    for name, items in all_clips.items():
        if len(items) == 1:
            clip, path_str = items[0]
            clips_by_name[name] = clip
            clip_paths[clip.GetUniqueId()] = path_str
        else:
            duplicates[name] = items
            for clip, path_str in items:
                clip_paths[clip.GetUniqueId()] = path_str

    return clips_by_name, duplicates, clip_paths


def validate_day_camera(row, day_number, bin_path, camera_value):
    warnings = []

    row_day = row.get("SHOOT_DAY")
    target_day = None
    if row_day:
        match = re.search(r"\d+", str(row_day))
        if match:
            target_day = int(match.group(0))
    elif day_number is not None:
        target_day = day_number

    if target_day is not None:
        day_patterns = [
            rf"\bDAY\s*0*{target_day}\b",
            rf"\bDAY_0*{target_day}\b",
            rf"\bDAY0*{target_day}\b",
        ]
        matched_day = False
        for pat in day_patterns:
            if re.search(pat, bin_path, re.IGNORECASE):
                matched_day = True
                break
        if not matched_day:
            warnings.append(f"Day mismatch: expected Day {target_day} in bin path")

    if camera_value:
        cam_clean = str(camera_value).strip().upper()
        cam_patterns = [
            rf"\bCAM\s*[-_]*{re.escape(cam_clean)}\b",
            rf"\b{re.escape(cam_clean)}\b",
        ]
        matched_cam = False
        for pat in cam_patterns:
            if re.search(pat, bin_path, re.IGNORECASE):
                matched_cam = True
                break
        if not matched_cam:
            warnings.append(f"Camera mismatch: expected Camera {cam_clean} in bin path")

    return warnings


def _normalize_header(value):
    if value is None:
        return ""
    return re.sub(r"[^A-Za-z0-9]", "", str(value)).upper()


def _map_header_row(cells):
    mapping = {}
    for col_index, cell in enumerate(cells):
        norm = _normalize_header(cell)
        if not norm:
            continue
        canonical = HEADER_ALIASES.get(norm)
        if canonical and canonical not in mapping.values():
            mapping[col_index] = canonical
    return mapping


def _find_header_mapping(grid, source_name):
    best_index = None
    best_mapping = {}

    for index, cells in enumerate(grid):
        mapping = _map_header_row(cells)
        if "CLIP_NAME" not in mapping.values() and "CLIP_NAME_FIXED" not in mapping.values():
            continue
        if len(mapping) > len(best_mapping):
            best_mapping = mapping
            best_index = index

    if best_index is None or len(best_mapping) < _MIN_HEADER_MATCHES:
        raise ValueError(
            f"Could not find a header row in {source_name}.\n"
            "Expected a row containing columns like CLIP NAME, SCENE, TAKE, SHOT, CAMERA, etc."
        )

    found = set(best_mapping.values())
    required_cols_to_check = [c for c in REQUIRED_COLUMNS if c != "CLIP_NAME"]
    missing = [c for c in required_cols_to_check if c not in found]
    if "CLIP_NAME" not in found and "CLIP_NAME_FIXED" not in found:
        missing.append("CLIP_NAME")
    if missing:
        raise ValueError(
            f"{source_name} header row was found but is missing column(s): "
            + ", ".join(missing)
        )

    return best_index, best_mapping


def _rows_from_grid(grid, source_name):
    header_index, mapping = _find_header_mapping(grid, source_name)

    rows = []
    for offset, cells in enumerate(grid[header_index + 1:], start=1):
        if not cells or all(
            c is None or str(c).strip() == "" for c in cells
        ):
            continue
        row = {}
        for col_index, canonical in mapping.items():
            value = cells[col_index] if col_index < len(cells) else ""
            row[canonical] = clean_value(value)
        row["_CSV_ROW_NUMBER"] = header_index + 1 + offset
        rows.append(row)

    return rows


def _grid_from_csv(path):
    last_error = None
    for encoding in ("utf-8-sig", "cp1252", "latin-1"):
        try:
            with open(path, "r", encoding=encoding, newline="") as f:
                return [row for row in csv.reader(f)]
        except UnicodeDecodeError as e:
            last_error = e
            continue
    raise RuntimeError(
        f"Could not decode CSV: {path}. Error: {last_error}"
    )


def _grid_from_xlsx(path):
    try:
        import openpyxl
        wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
        sheet = wb.active
        grid = []
        for row in sheet.iter_rows(values_only=True):
            grid.append([str(val) if val is not None else "" for val in row])
        wb.close()
        return grid
    except Exception as e:
        raise RuntimeError(f"Could not read Excel file {path}. Error: {e}")


def get_sheet_names(path):
    """Return list of sheet names from an XLSX file, or ['CSV'] for CSV files."""
    _, ext = os.path.splitext(path)
    if ext.lower() == ".xlsx":
        import openpyxl
        wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
        names = list(wb.sheetnames)
        wb.close()
        return names
    return ["CSV"]


def read_log_rows(path, selected_sheets=None):
    if not os.path.exists(path):
        raise FileNotFoundError(f"Log file not found: {path}")
    _, ext = os.path.splitext(path)
    if ext.lower() == ".xlsx":
        import openpyxl
        try:
            wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
            all_rows = []
            global_row_counter = 1
            for sheet_name in wb.sheetnames:
                if selected_sheets is not None and sheet_name not in selected_sheets:
                    continue
                sheet = wb[sheet_name]
                grid = []
                for row in sheet.iter_rows(values_only=True):
                    grid.append([str(val) if val is not None else "" for val in row])
                
                try:
                    sheet_rows = _rows_from_grid(grid, f"XLSX Sheet '{sheet_name}'")
                    for r in sheet_rows:
                        r["_SHEET_NAME"] = sheet_name
                        r["_GLOBAL_ROW_NUMBER"] = global_row_counter
                        global_row_counter += 1
                    all_rows.extend(sheet_rows)
                except ValueError:
                    # Skip sheets that don't match our headers
                    pass
            wb.close()
            if not all_rows:
                raise ValueError("Could not find a valid header row in any of the Excel sheets.")
            return all_rows
        except Exception as e:
            raise RuntimeError(f"Could not read Excel file {path}. Error: {e}")
    else:
        grid = _grid_from_csv(path)
        rows = _rows_from_grid(grid, "CSV")
        for idx, r in enumerate(rows, start=1):
            r["_SHEET_NAME"] = "CSV"
            r["_GLOBAL_ROW_NUMBER"] = idx
        return rows


def build_keywords(row):
    parts = []
    for field in EXTRA_FIELDS_FOR_KEYWORDS:
        value = clean_value(row.get(field))
        if value:
            parts.append(f"{field}={value}")
    return ", ".join(parts)


def build_metadata_payload(row):
    metadata = {}
    for csv_field, resolve_field in METADATA_MAP.items():
        if resolve_field == "Comments":
            continue
        value = clean_value(row.get(csv_field))
        if value:
            metadata[resolve_field] = value

    # Map Good/NG directly to Description
    good_ng = clean_value(row.get("GOOD_NG")).upper()
    if good_ng in ("G", "GOOD"):
        metadata["Description"] = "GOOD"
    elif good_ng in ("NG", "REJECTED"):
        metadata["Description"] = "NG"
    else:
        metadata["Description"] = ""

    # Keywords should always be empty
    metadata["Keywords"] = ""

    # Comments should ONLY contain CATATAN (NOTES) value
    original_notes = clean_value(row.get("NOTES"))
    metadata["Comments"] = original_notes

    return metadata


def apply_clip_color(clip, good_ng_value):
    """
    Set clip color based on GOOD_NG value.
    Returns: (success_bool, color_applied_or_error_str)
    """
    val = str(good_ng_value).strip().upper()
    color = CLIP_COLOR_MAP.get(val)
    if not color:
        return True, "NO_MAPPING"
    # Resolve might not have exact color; apply fallback
    color = CLIP_COLOR_FALLBACK.get(color, color)
    try:
        ok = clip.SetClipColor(color)
        return bool(ok), color
    except Exception as e:
        return False, f"ERROR: {e}"


def apply_clip_flag(clip, good_ng_value):
    """
    Add a colored flag to clip based on GOOD_NG value.
    Returns: (success_bool, flag_color_or_error_str)
    """
    val = str(good_ng_value).strip().upper()
    flag_color = FLAG_COLOR_MAP.get(val)
    if not flag_color:
        return True, "NO_MAPPING"
    try:
        ok = clip.AddFlag(flag_color)
        return bool(ok), flag_color
    except Exception as e:
        return False, f"ERROR: {e}"


def set_metadata_safely(clip, metadata):
    results = {}
    for key, value in metadata.items():
        try:
            ok = clip.SetMetadata(key, value)
            results[key] = bool(ok)
        except Exception as e:
            results[key] = f"ERROR: {e}"
    return results


def write_report(report_path, report_rows):
    import openpyxl
    from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
    from openpyxl.utils import get_column_letter

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "MetaFiller Report"

    headers = [
        "status",
        "sheet_name",
        "csv_row",
        "clip_name",
        "message",
        "metadata_attempted",
        "field_results",
    ]

    # Colors
    header_fill = PatternFill(start_color="5B9BD5", end_color="5B9BD5", fill_type="solid") # Soft blue
    header_font = Font(name="Plus Jakarta Sans", size=11, bold=True, color="FFFFFF")
    
    missing_fill = PatternFill(start_color="F2C4A2", end_color="F2C4A2", fill_type="solid") # Orange/light peach
    missing_font = Font(name="Plus Jakarta Sans", size=10, color="000000")
    
    normal_font = Font(name="Plus Jakarta Sans", size=10)
    
    thin_border = Border(
        left=Side(style='thin', color='D9D9D9'),
        right=Side(style='thin', color='D9D9D9'),
        top=Side(style='thin', color='D9D9D9'),
        bottom=Side(style='thin', color='D9D9D9')
    )

    # Write Headers
    for col_idx, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal="center", vertical="center")
        cell.border = thin_border

    # Write Rows
    for row_idx, row in enumerate(report_rows, 2):
        is_missing = False
        
        # Check if the row contains missing keywords
        status_val = str(row.get("status", "")).upper()
        msg_val = str(row.get("message", "")).lower()
        meta_val = str(row.get("metadata_attempted", "")).lower()
        
        if "MISSING" in status_val or "FAILED" in status_val or "missing" in msg_val or "missing" in meta_val or "not found" in msg_val or "not found" in meta_val:
            is_missing = True

        for col_idx, header in enumerate(headers, 1):
            val = row.get(header, "")
            # Convert dictionary/list to string for metadata_attempted and field_results
            if isinstance(val, (dict, list)):
                val = str(val)
            
            cell = ws.cell(row=row_idx, column=col_idx, value=val)
            cell.border = thin_border
            
            if is_missing:
                cell.fill = missing_fill
                cell.font = missing_font
            else:
                cell.font = normal_font
                
            if header in ("csv_row", "status"):
                cell.alignment = Alignment(horizontal="center")

    # Add spacing
    start_summary_row = len(report_rows) + 4
    
    # Summary Header
    summary_title_fill = PatternFill(start_color="1F4E78", end_color="1F4E78", fill_type="solid") # Dark Blue
    summary_title_font = Font(name="Plus Jakarta Sans", size=11, bold=True, color="FFFFFF")
    
    ws.merge_cells(start_row=start_summary_row, start_column=1, end_row=start_summary_row, end_column=3)
    title_cell = ws.cell(row=start_summary_row, column=1, value="SUMMARY METRICS")
    title_cell.fill = summary_title_fill
    title_cell.font = summary_title_font
    title_cell.alignment = Alignment(horizontal="center")
    
    # Calculate Metrics
    total_attempted = len(report_rows)
    total_updated = sum(1 for r in report_rows if "UPDATED" in str(r.get("status", "")).upper())
    total_missing = sum(1 for r in report_rows if "MISSING" in str(r.get("status", "")).upper() or "missing" in str(r.get("message", "")).lower() or "missing" in str(r.get("metadata_attempted", "")).lower())
    total_duplicates = sum(1 for r in report_rows if "DUPLICATE" in str(r.get("status", "")).upper())
    total_failed = sum(1 for r in report_rows if "FAIL" in str(r.get("status", "")).upper())
    
    metrics = [
        ("Total Rows Attempted", total_attempted),
        ("Total Successfully Updated", total_updated),
        ("Total Missing Elements", total_missing),
        ("Total Duplicates", total_duplicates),
        ("Total Failed", total_failed)
    ]
    
    metric_label_font = Font(name="Plus Jakarta Sans", size=10, bold=True)
    metric_val_font = Font(name="Plus Jakarta Sans", size=10)
    
    for i, (label, val) in enumerate(metrics, 1):
        r = start_summary_row + i
        lbl_cell = ws.cell(row=r, column=1, value=label)
        lbl_cell.font = metric_label_font
        lbl_cell.border = thin_border
        
        val_cell = ws.cell(row=r, column=2, value=val)
        val_cell.font = metric_val_font
        val_cell.alignment = Alignment(horizontal="center")
        val_cell.border = thin_border
        
        # Merge key column to make it look cleaner
        ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=3)

    # Auto-adjust column widths
    for col in ws.columns:
        max_len = 0
        col_letter = get_column_letter(col[0].column)
        for cell in col:
            # Avoid using merged cells for width calculation to prevent giant columns
            if cell.coordinate in ws.merged_cells:
                continue
            if cell.value:
                max_len = max(max_len, len(str(cell.value)))
        ws.column_dimensions[col_letter].width = max(max_len + 3, 12)

    os.makedirs(os.path.dirname(report_path), exist_ok=True)
    wb.save(report_path)



# =========================
# CORE EXECUTION ENGINE
# =========================

def run_metadata_import(csv_path, dry_run, scan_scope, filter_str, overwrite, apply_colors=False, apply_audio=True, selected_sheets=None, progress_callback=None):
    """
    Run the metadata matching and writing process.
    Returns: (success_bool, summary_message, report_path)
    """
    try:
        if not os.path.exists(csv_path):
            return False, f"Error: CSV path does not exist: {csv_path}", ""

        day_number = detect_day_number(csv_path)

        resolve = get_resolve()
        project_manager = resolve.GetProjectManager()
        project = project_manager.GetCurrentProject()
        if not project:
            return False, "Error: No DaVinci Resolve project is open.", ""

        media_pool = project.GetMediaPool()

        # Scan media pool
        clips_by_name, duplicates, clip_paths = collect_clips_with_paths(media_pool, scan_scope, filter_str)

        # Always scan the entire Media Pool for audio clips to support cross-bin audio matching
        all_pool_clips, _, all_pool_paths = collect_clips_with_paths(media_pool, "ENTIRE_MEDIA_POOL", "")
        audio_clips = []
        for name, clip in all_pool_clips.items():
            try:
                if clip.GetClipProperty("Type") == "Audio":
                    audio_clips.append((name, clip))
            except Exception:
                pass
        clip_paths.update(all_pool_paths)

        # Parse CSV
        csv_rows = read_log_rows(csv_path, selected_sheets=selected_sheets)

        # Build scene/shot/take to audio names mapping and AUDIO_GNG mapping
        scene_shot_take_audios = {}
        audio_gng_by_name = {}
        for row in csv_rows:
            sc = clean_value(row.get("SCENE"))
            sh = clean_value(row.get("SHOT"))
            tk = clean_value(row.get("TAKE"))
            audio_name = clean_value(row.get("AUDIO_NAME"))
            audio_gng = clean_value(row.get("AUDIO_GNG"))
            
            if audio_name:
                audio_gng_by_name[audio_name] = audio_gng
                if sc and sh and tk and audio_name.upper() != "MOS":
                    scene_shot_take_audios.setdefault((sc, sh, tk), set()).add(audio_name)

        report_rows = []
        updated_count = 0
        missing_count = 0
        duplicate_count = 0
        failed_count = 0
        audio_matched_count = 0

        for row in csv_rows:
            if progress_callback:
                try:
                    progress_callback()
                except Exception:
                    pass
            csv_row_number = row.get("_CSV_ROW_NUMBER")
            clip_name_normal = normalize_clip_name(row.get("CLIP_NAME"))
            clip_name_fixed = normalize_clip_name(row.get("CLIP_NAME_FIXED"))

            # If both clip name columns are empty, check if this is an audio-only warning/double-check row
            if not clip_name_normal and not clip_name_fixed:
                audio_name_raw = clean_value(row.get("AUDIO_NAME"))
                audio_gng = clean_value(row.get("AUDIO_GNG"))
                notes_val = clean_value(row.get("NOTES"))
                
                if audio_name_raw:
                    sc, sh, tk = parse_scene_shot_take_from_audio_name(audio_name_raw)
                    expected_bases = parse_audio_names(audio_name_raw)
                    matched_warning_clips = []
                    if expected_bases:
                        for base in expected_bases:
                            base_norm = normalize_clip_name(base)
                            for a_name, a_clip in audio_clips:
                                if a_name.startswith(base_norm):
                                    matched_warning_clips.append((base, a_clip))
                                    break
                    
                    if matched_warning_clips:
                        results = {}
                        category = "Dialogue"
                        for base, a_clip in matched_warning_clips:
                            a_path = clip_paths.get(a_clip.GetUniqueId(), "")
                            category = determine_audio_category(a_path, fallback="Dialogue")
                            
                            fn_good = "GOOD" in a_clip.GetName().upper()
                            log_good = audio_gng.upper() in ("G", "GOOD")
                            log_ng = audio_gng.upper() in ("NG", "REJECTED")
                            
                            if log_good:
                                desc = "GOOD"
                            elif log_ng:
                                desc = "NG"
                            else:
                                desc = "GOOD" if fn_good else ""
                                
                            a_meta = {
                                "Scene": sc,
                                "Shot": sh,
                                "Take": tk,
                                "Description": desc,
                                "Category": category,
                                "Comments": "Video clip is missing."
                            }
                            
                            if not dry_run:
                                field_res = set_metadata_safely(a_clip, a_meta)
                                flag_ok = a_clip.AddFlag("Red")
                                field_res["Flag"] = "Red" if flag_ok else "FAILED"
                                results[a_clip.GetName()] = field_res
                            else:
                                results[a_clip.GetName()] = {
                                    "Scene": sc,
                                    "Shot": sh,
                                    "Take": tk,
                                    "Description": desc,
                                    "Category": category,
                                    "Comments": "Video clip is missing.",
                                    "Flag": "Red"
                                }
                        
                        if not dry_run:
                            updated_count += 1
                            status = "AUDIO_ONLY_UPDATED"
                            msg = f"Audio metadata parsed and updated from filename. Flagged Red."
                        else:
                            status = "AUDIO_ONLY_DRY_RUN"
                            msg = f"Audio matched. Would tag metadata from filename and flag Red."
                            
                        report_rows.append({
                            "status": status,
                            "sheet_name": row.get("_SHEET_NAME", ""),
                            "csv_row": csv_row_number,
                            "clip_name": audio_name_raw,
                            "message": msg,
                            "metadata_attempted": f"Scene={sc}, Shot={sh}, Take={tk}, Description={desc}, Comments=Video clip is missing., Flag=Red",
                            "field_results": str(results),
                        })
                    else:
                        missing_count += 1
                        report_rows.append({
                            "status": "AUDIO_ONLY_MISSING",
                            "sheet_name": row.get("_SHEET_NAME", ""),
                            "csv_row": csv_row_number,
                            "clip_name": audio_name_raw,
                            "message": f"Expected audio clip '{audio_name_raw}' not found in Media Pool.",
                            "metadata_attempted": "",
                            "field_results": "",
                        })
                    continue
                else:
                    failed_count += 1
                    report_rows.append({
                        "status": "FAILED",
                        "sheet_name": row.get("_SHEET_NAME", ""),
                        "csv_row": csv_row_number,
                        "clip_name": "",
                        "message": "Missing CLIP_NAME and AUDIO_NAME in CSV row.",
                        "metadata_attempted": "",
                        "field_results": "",
                    })
                    continue

            # Prioritized clip matching: try Clip Name Fixed first, then Clip Name
            clip = None
            clip_name = ""
            is_duplicate = False

            candidates = []
            if clip_name_fixed:
                candidates.append(clip_name_fixed)
            if clip_name_normal:
                candidates.append(clip_name_normal)

            for name in candidates:
                if name in duplicates:
                    clip_name = name
                    is_duplicate = True
                    break
                elif name in clips_by_name:
                    clip_name = name
                    clip = clips_by_name[name]
                    break

            if is_duplicate:
                duplicate_count += 1
                dup_items = duplicates[clip_name]
                paths_str = ", ".join([path for _, path in dup_items])
                report_rows.append({
                    "status": "DUPLICATE_CLIP_NAME",
                    "sheet_name": row.get("_SHEET_NAME", ""),
                    "csv_row": csv_row_number,
                    "clip_name": clip_name,
                    "message": f"Duplicate clip name found in Media Pool at paths: {paths_str}.",
                    "metadata_attempted": "",
                    "field_results": "",
                })
                continue

            video_missing = (clip is None)
            missing_name = clip_name_fixed if clip_name_fixed else clip_name_normal

            warning_msg = ""
            has_warning = False
            metadata = {}
            good_ng = ""

            if not video_missing:
                # Day/camera validation against bin path
                bin_path = clip_paths.get(clip.GetUniqueId(), "")
                camera_value = row.get("CAMERA")
                warnings = validate_day_camera(row, day_number, bin_path, camera_value)
                has_warning = len(warnings) > 0
                warning_msg = " | ".join(warnings) if has_warning else ""

                metadata = build_metadata_payload(row)
                good_ng = clean_value(row.get("GOOD_NG"))

            # --- Audio Matching Logic ---
            audio_name_raw = clean_value(row.get("AUDIO_NAME"))
            sc = clean_value(row.get("SCENE"))
            sh = clean_value(row.get("SHOT"))
            tk = clean_value(row.get("TAKE"))

            group_audios = set()
            if audio_name_raw and audio_name_raw.upper() != "MOS":
                group_audios.add(audio_name_raw)
            else:
                if sc and sh and tk:
                    group_audios.update(scene_shot_take_audios.get((sc, sh, tk), []))


            matched_audio_clips = []
            expected_bases = []
            missing_bases = []

            if apply_audio and not video_missing:
                if audio_name_raw.upper() == "MOS":
                    metadata["Category"] = "Silence"
                elif group_audios:
                    metadata["Category"] = "Dialogue"
                else:
                    metadata["Category"] = "Dialogue"

            if apply_audio and group_audios:
                for aud_name in sorted(group_audios):
                    bases = parse_audio_names(aud_name)
                    if bases:
                        expected_bases.extend(bases)
                seen = set()
                expected_bases = [x for x in expected_bases if not (x in seen or seen.add(x))]

                if expected_bases:

                    matched_bases = set()
                    # Find audio clips
                    for base in expected_bases:
                        base_norm = normalize_clip_name(base)
                        for a_name, a_clip in audio_clips:
                            if a_name.startswith(base_norm):
                                matched_audio_clips.append((base, a_clip))
                                matched_bases.add(base)
                                break

                    for base, a_clip in matched_audio_clips:
                        track_idx = get_audio_track_idx(base, expected_bases)
                        actual_filename = a_clip.GetName()
                        
                        if not video_missing:
                            metadata[f"Track {track_idx}"] = actual_filename

                        # Find original audio name to get its respective AUDIO_GNG value
                        orig_aud_name = ""
                        for aud_name in group_audios:
                            if base in parse_audio_names(aud_name):
                                orig_aud_name = aud_name
                                break

                        audio_gng = audio_gng_by_name.get(orig_aud_name, "")

                        # Determine audio category
                        a_path = clip_paths.get(a_clip.GetUniqueId(), "")
                        category = determine_audio_category(a_path, fallback="Dialogue")

                        # Check G/NG mismatch
                        fn_good = "GOOD" in actual_filename.upper()
                        log_good = audio_gng.upper() in ("G", "GOOD")
                        log_ng = audio_gng.upper() in ("NG", "REJECTED")

                        mismatch = False
                        note_msg = ""
                        if fn_good and log_ng:
                            mismatch = True
                            note_msg = "Audio is good, but continuity log says NG."
                        elif (not fn_good) and log_good:
                            mismatch = True
                            note_msg = "Audio is NG, but continuity log says GOOD."

                        a_meta = {
                            "Scene": sc,
                            "Shot": sh,
                            "Take": tk,
                            "Description": "GOOD" if log_good else ("NG" if log_ng else ""),
                            "Category": category
                        }

                        if video_missing:
                            a_meta["Comments"] = "Video clip is missing."
                        else:
                            if mismatch:
                                a_meta["Audio Notes"] = note_msg
                            elif overwrite:
                                a_meta["Audio Notes"] = ""

                        if not dry_run:
                            set_metadata_safely(a_clip, a_meta)
                            if video_missing:
                                a_clip.AddFlag("Red")
                            else:
                                if mismatch:
                                    a_clip.AddFlag("Orange")
                                elif apply_colors and audio_gng:
                                    apply_clip_color(a_clip, audio_gng)
                                    apply_clip_flag(a_clip, audio_gng)

                    if matched_audio_clips:
                        audio_matched_count += len(matched_audio_clips)

                    missing_bases = [b for b in expected_bases if b not in matched_bases]
                    if not video_missing and missing_bases:
                        missing_mics = []
                        for b in missing_bases:
                            m = re.search(r"REC\s*(\d+)", b, re.IGNORECASE)
                            if m:
                                missing_mics.append(f"REC {m.group(1)}")
                            else:
                                missing_mics.append(b)
                        
                        unique_mics = sorted(list(set(missing_mics)))
                        if len(unique_mics) > 1:
                            missing_text = " & ".join(unique_mics) + " are missing"
                        else:
                            missing_text = unique_mics[0] + " is missing"

                        existing_notes = metadata.get("Comments", "")
                        if existing_notes:
                            metadata["Comments"] = f"{existing_notes} | {missing_text}."
                        else:
                            metadata["Comments"] = f"{missing_text}."

            if video_missing:
                missing_count += 1
                msg = "Clip not found in Media Pool. Matching audio clips updated and flagged Red."
                if dry_run:
                    msg = "Clip not found in Media Pool. Matching audio clips would be updated and flagged Red."
                report_rows.append({
                    "status": "MISSING_DRY_RUN" if dry_run else "MISSING",
                    "sheet_name": row.get("_SHEET_NAME", ""),
                    "csv_row": csv_row_number,
                    "clip_name": missing_name,
                    "message": msg,
                    "metadata_attempted": "",
                    "field_results": "",
                })
                continue

            if dry_run:
                status = "DRY_RUN_MATCHED"
                message = "Clip matched. No metadata written."
                if apply_colors and good_ng:
                    color = CLIP_COLOR_MAP.get(good_ng.upper(), "none")
                    flag = "Red" if (apply_audio and group_audios and expected_bases and missing_bases) else FLAG_COLOR_MAP.get(good_ng.upper(), "none")
                    message += f" [Color={color}, Flag={flag}]"
                if has_warning:
                    status = "BIN_PATH_DAY_CAMERA_MISMATCH"
                    message = f"{message} Warning: {warning_msg}"

                report_rows.append({
                    "status": status,
                    "sheet_name": row.get("_SHEET_NAME", ""),
                    "csv_row": csv_row_number,
                    "clip_name": clip_name,
                    "message": message,
                    "metadata_attempted": metadata,
                    "field_results": "",
                })
                continue

            field_results = set_metadata_safely(clip, metadata)

            # Apply clip colors and flags if enabled
            color_result = ""
            flag_result = ""
            if apply_colors:
                if apply_audio and group_audios and expected_bases and missing_bases:
                    if not dry_run:
                        clip.AddFlag("Red")
                    flag_result = "Flag=Red"
                    if good_ng:
                        c_ok, c_info = apply_clip_color(clip, good_ng)
                        color_result = f"Color={c_info}"
                        if not c_ok:
                            field_results["ClipColor"] = c_info
                elif good_ng:
                    c_ok, c_info = apply_clip_color(clip, good_ng)
                    f_ok, f_info = apply_clip_flag(clip, good_ng)
                    color_result = f"Color={c_info}"
                    flag_result = f"Flag={f_info}"
                    if not c_ok:
                        field_results["ClipColor"] = c_info
                    if not f_ok:
                        field_results["Flag"] = f_info

            critical_success = True
            for key, val in field_results.items():
                if key in ("Good Take", "Tag", "Take Tag", "Take Status"):
                    continue
                if val is not True:
                    critical_success = False
                    break

            if critical_success:
                updated_count += 1
                if has_warning:
                    status = "BIN_PATH_DAY_CAMERA_MISMATCH"
                    message = f"Metadata written, but warning: {warning_msg}"
                else:
                    status = "UPDATED"
                    message = "Metadata written successfully."
                if color_result:
                    message += f" [{color_result}, {flag_result}]"
            else:
                failed_count += 1
                status = "PARTIAL_OR_FAILED"
                message = "Some metadata fields failed."
                if has_warning:
                    message = f"{message} Warning: {warning_msg}"

            report_rows.append({
                "status": status,
                "sheet_name": row.get("_SHEET_NAME", ""),
                "csv_row": csv_row_number,
                "clip_name": clip_name,
                "message": message,
                "metadata_attempted": metadata,
                "field_results": field_results,
            })

        # Derived reports folder path
        reports_dir = os.path.join(os.path.dirname(csv_path), "MetaFiller_Reports")
        report_filename = f"metafiller_report_{timestamp()}.xlsx"
        report_path = os.path.join(reports_dir, report_filename)


        write_report(report_path, report_rows)

        audio_summary = f"\nAudio Clips Matched: {audio_matched_count}" if apply_audio else ""
        
        summary = (
            f"{'Dry Run' if dry_run else 'Apply'} complete.\n"
            f"Unique clips found in pool: {len(clips_by_name)}\n"
            f"Matched: {updated_count if not dry_run else (len(csv_rows) - missing_count - duplicate_count - failed_count)}\n"
            f"Missing: {missing_count}\n"
            f"Duplicates: {duplicate_count}\n"
            f"Failed: {failed_count}{audio_summary}"
        )
        return True, summary, report_path
    except Exception as e:
        return False, f"Runtime Error: {e}\n{traceback.format_exc()}", ""


def run_timeline_builder(csv_path, scan_scope, filter_str, group_by, include_mode, order_mode,
                         selected_sheets=None, bin_mode="Current Bin", custom_bin_path="Master/05_Timeline_Comp", progress_callback=None):
    """
    Performs grouping, filtering, sorting, and creates timelines in DaVinci Resolve.
    Returns (success_bool, summary_message)
    """
    try:
        if not os.path.exists(csv_path):
            return False, f"Error: CSV path does not exist: {csv_path}"

        resolve = get_resolve()
        try:
            current_page = resolve.GetCurrentPage()
            if current_page != "edit":
                resolve.OpenPage("edit")
                import time
                time.sleep(0.5)
        except Exception:
            pass

        project_manager = resolve.GetProjectManager()
        project = project_manager.GetCurrentProject()
        if not project:
            return False, "Error: No DaVinci Resolve project is open."

        media_pool = project.GetMediaPool()
        if not media_pool:
            return False, "Error: Could not access DaVinci Resolve Media Pool. Ensure a project is open and Media Pool is accessible."

        # Verify CreateTimelineFromClips is available
        if not getattr(media_pool, 'CreateTimelineFromClips', None) or not getattr(media_pool, 'CreateEmptyTimeline', None):
            return False, "Error: DaVinci Resolve must be on the 'Edit' page to create timelines. Please switch to the Edit page manually in Resolve and try again."


        print(f"DEBUG: media_pool type: {type(media_pool)}")
        print(f"DEBUG: CreateTimelineFromClips: {getattr(media_pool, 'CreateTimelineFromClips', None)}")
        print(f"DEBUG: CreateEmptyTimeline: {getattr(media_pool, 'CreateEmptyTimeline', None)}")
        print(f"DEBUG: media_pool methods: {[attr for attr in dir(media_pool) if not attr.startswith('__')]}")



        # Resolve target bin selection/creation
        target_folder = None
        original_folder = None
        try:
            original_folder = media_pool.GetCurrentFolder()
        except Exception:
            pass

        if bin_mode == "Custom Bin" and custom_bin_path:
            try:
                target_folder = find_or_create_folder(media_pool, custom_bin_path)
            except Exception as e:
                print(f"Failed to find or create custom folder {custom_bin_path}: {e}")


        clips_by_name, duplicates, clip_paths = collect_clips_with_paths(media_pool, scan_scope, filter_str)

        csv_rows = read_log_rows(csv_path, selected_sheets=selected_sheets)

        # Build scene/shot/take to audio names mapping
        scene_shot_take_audios = {}
        for row in csv_rows:
            sc = clean_value(row.get("SCENE"))
            sh = clean_value(row.get("SHOT"))
            tk = clean_value(row.get("TAKE"))
            audio_name = clean_value(row.get("AUDIO_NAME"))
            if sc and sh and tk and audio_name and audio_name.upper() != "MOS":
                scene_shot_take_audios.setdefault((sc, sh, tk), set()).add(audio_name)

        # 1. Match CSV rows to Media Pool clips
        matched_items = []
        for row in csv_rows:
            clip_name_normal = normalize_clip_name(row.get("CLIP_NAME"))
            clip_name_fixed = normalize_clip_name(row.get("CLIP_NAME_FIXED"))
            
            clip = None
            candidates = []
            if clip_name_fixed:
                candidates.append(clip_name_fixed)
            if clip_name_normal:
                candidates.append(clip_name_normal)
                
            for name in candidates:
                if name in duplicates:
                    break
                elif name in clips_by_name:
                    clip = clips_by_name[name]
                    break
            
            if clip:
                matched_items.append((row, clip))

        if not matched_items:
            return False, "No matched clips found in the Media Pool to create timelines."

        # 2. Filter clips based on include mode
        filtered_items = []
        for row, clip in matched_items:
            # Check clip Description metadata first (since Tag is read-only), fallback to CSV row GOOD_NG
            gng_val = ""
            try:
                gng_val = clip.GetMetadata("Description")
            except Exception:
                pass
            if not gng_val:
                gng_val = row.get("GOOD_NG")
            gng = clean_value(gng_val).upper()

            include = False
            if include_mode == "Good Takes Only":
                if gng in ("G", "GOOD"):
                    include = True
            elif include_mode == "Good + Untagged":
                if gng in ("G", "GOOD", ""):
                    include = True
            elif include_mode == "All Except Rejected":
                if gng not in ("NG", "REJECTED"):
                    include = True
            elif include_mode == "All Clips":
                include = True
            
            if include:
                filtered_items.append((row, clip))

        if not filtered_items:
            return False, f"No clips remained after filtering by: {include_mode}."

        # 3. Group clips
        grouped_items = {}
        for row, clip in filtered_items:
            g_key, timeline_base_name = get_group_key_and_name(row, group_by)
            grouped_items.setdefault(timeline_base_name, []).append((row, clip))

        # 4. Sort and create timelines
        created_timelines = []
        failed_timelines = []

        for timeline_base_name, items in grouped_items.items():
            if progress_callback:
                try:
                    progress_callback()
                except Exception:
                    pass
            if order_mode == "Scene > Shot > Take > Camera":
                def sort_func(item):
                    r = item[0]
                    return (
                        natural_sort_key(r.get("SCENE")),
                        natural_sort_key(r.get("SHOT")),
                        natural_sort_key(r.get("TAKE")),
                        natural_sort_key(r.get("CAMERA")),
                        r.get("_GLOBAL_ROW_NUMBER", 0)
                    )
            elif order_mode == "Shoot Day > Scene > Shot > Take > Camera":
                def sort_func(item):
                    r = item[0]
                    return (
                        natural_sort_key(r.get("SHOOT_DAY")),
                        natural_sort_key(r.get("SCENE")),
                        natural_sort_key(r.get("SHOT")),
                        natural_sort_key(r.get("TAKE")),
                        natural_sort_key(r.get("CAMERA")),
                        r.get("_GLOBAL_ROW_NUMBER", 0)
                    )
            else: # CSV Row Order
                def sort_func(item):
                    return item[0].get("_GLOBAL_ROW_NUMBER", 0)

            # Sort the items
            sorted_items = sorted(items, key=sort_func)
            clip_list = [clip for row, clip in sorted_items]

            # Collect all audio clips by scanning the ENTIRE Media Pool always
            all_pool_clips, _, _ = collect_clips_with_paths(media_pool, "ENTIRE_MEDIA_POOL", "")
            audio_clips = []
            for name, clip in all_pool_clips.items():
                try:
                    if clip.GetClipProperty("Type") == "Audio":
                        audio_clips.append((name, clip))
                except Exception:
                    pass

            # Query project frame rate
            fps = 24.0
            try:
                fps_val = project.GetSetting("timelineFrameRate")
                if fps_val:
                    fps = float(fps_val)
            except Exception:
                pass

            video_start_frame = 0
            audio_clip_info_list = []

            for row, v_clip in sorted_items:
                v_dur = get_clip_duration_frames(v_clip, fps)
                
                # Find matching audio clips
                audio_name_raw = clean_value(row.get("AUDIO_NAME"))
                sc = clean_value(row.get("SCENE"))
                sh = clean_value(row.get("SHOT"))
                tk = clean_value(row.get("TAKE"))
                
                group_audios = set()
                if audio_name_raw and audio_name_raw.upper() != "MOS":
                    group_audios.add(audio_name_raw)
                else:
                    if sc and sh and tk:
                        group_audios.update(scene_shot_take_audios.get((sc, sh, tk), []))
                
                expected_bases = []
                if group_audios:
                    for aud_name in sorted(group_audios):
                        bases = parse_audio_names(aud_name)
                        if bases:
                            expected_bases.extend(bases)
                    seen = set()
                    expected_bases = [x for x in expected_bases if not (x in seen or seen.add(x))]
                
                matched_audios = []
                if expected_bases:
                    for base in expected_bases:
                        base_norm = normalize_clip_name(base)
                        for a_name, a_clip in audio_clips:
                            if a_name.startswith(base_norm):
                                matched_audios.append(a_clip)
                                break
                
                # Append separate Audio Clip Infos on Track 3+
                for a_clip in matched_audios:
                    a_dur = get_clip_duration_frames(a_clip, fps)
                    
                    # Find which base matched this a_clip
                    base_matched = ""
                    for base in expected_bases:
                        base_norm = normalize_clip_name(base)
                        if normalize_clip_name(a_clip.GetName()).startswith(base_norm):
                            base_matched = base
                            break
                            
                    track_idx = get_audio_track_idx(base_matched, expected_bases)
                    audio_clip_info_list.append({
                        "mediaPoolItem": a_clip,
                        "startFrame": 0,
                        "endFrame": a_dur - 1 if a_dur > 0 else 0,
                        "trackIndex": track_idx,
                        "recordFrame": video_start_frame,
                        "mediaType": 2 # Audio only
                    })
                
                # Increment video_start_frame by the duration of this video clip
                video_start_frame += v_dur if v_dur > 0 else 100

            # Generate unique timeline name
            unique_name = get_unique_timeline_name(project, timeline_base_name)
            
            # Create timeline
            if not clip_list:
                failed_timelines.append(f"{unique_name} (Error: No matched video clips found)")
                continue

            try:
                if target_folder:
                    try:
                        media_pool.SetCurrentFolder(target_folder)
                    except Exception:
                        pass

                new_t = None

                # Step 1: Create timeline using simple video clip list (extremely robust, natively sets up timeline)
                if clip_list:
                    try:
                        new_t = media_pool.CreateTimelineFromClips(unique_name, clip_list)
                    except Exception as e:
                        print(f"DEBUG: CreateTimelineFromClips failed for {unique_name}: {e}")
                        import traceback
                        traceback.print_exc()
                        new_t = None

                # Step 2: Append audio clips if timeline was created and audio clips exist
                if new_t:
                    if audio_clip_info_list:
                        try:
                            # Pre-create audio tracks if needed
                            max_audio_track = 0
                            for ci in audio_clip_info_list:
                                max_audio_track = max(max_audio_track, ci.get("trackIndex", 1))
                            try:
                                current_audio_count = new_t.GetTrackCount("audio") or 1
                                for _ in range(max_audio_track - current_audio_count):
                                    try:
                                        new_t.AddTrack("audio", "stereo")
                                    except Exception as e:
                                        print(f"DEBUG: AddTrack failed for {unique_name}: {e}")
                                        break
                            except Exception as e:
                                print(f"DEBUG: GetTrackCount/AddTrack failed: {e}")

                            project.SetCurrentTimeline(new_t)
                            
                            # Adjust recordFrame by adding the timeline's start frame (usually 86400 for 01:00:00:00)
                            start_frame = 86400
                            try:
                                sf = new_t.GetStartFrame()
                                if sf is not None:
                                    start_frame = sf
                            except Exception:
                                pass
                            
                            adjusted_audio_clip_info_list = []
                            for ci in audio_clip_info_list:
                                adjusted_ci = ci.copy()
                                adjusted_ci["recordFrame"] = ci["recordFrame"] + start_frame
                                adjusted_audio_clip_info_list.append(adjusted_ci)

                            try:
                                media_pool.AppendToTimeline(adjusted_audio_clip_info_list)
                            except Exception as e:
                                print(f"DEBUG: AppendToTimeline failed for {unique_name}: {e}")
                                import traceback
                                traceback.print_exc()
                        except Exception as e:
                            print(f"DEBUG: Processing audio overlay failed for {unique_name}: {e}")
                            import traceback
                            traceback.print_exc()
                    created_timelines.append(unique_name)
                else:
                    failed_timelines.append(unique_name)
            except Exception as e:
                import traceback
                tb_str = traceback.format_exc()
                failed_timelines.append(f"{unique_name} (Error: {e})\n{tb_str}")
            finally:
                if target_folder and original_folder:
                    try:
                        media_pool.SetCurrentFolder(original_folder)
                    except Exception:
                        pass



        # 5. Formulate summary
        summary_msg = ""
        if created_timelines:
            summary_msg += f"Successfully created {len(created_timelines)} timeline(s):\n" + "\n".join([f"- {name}" for name in created_timelines])
        if failed_timelines:
            if summary_msg:
                summary_msg += "\n\n"
            summary_msg += f"Failed to create {len(failed_timelines)} timeline(s):\n" + "\n".join([f"- {name}" for name in failed_timelines])

        success = len(created_timelines) > 0
        return success, summary_msg
    except Exception as e:
        return False, f"Timeline creation runtime error: {e}\n{traceback.format_exc()}"


# =========================
# FLOATING GUI (Tkinter Engine)
# =========================

def launch_gui():
    settings = load_settings()

    # -- Color Palette --------------------------------------
    BG          = "#13121b"
    SURFACE     = "#1a1a2e"
    SURFACE_HI  = "#2a2932"
    ON_SURFACE  = "#e5e0ed"
    ON_MUTED    = "#928ea0"
    PRIMARY     = "#6C5CE7"
    PRIMARY_DIM = "#4029ba"
    TEAL        = "#00CEC9"
    YELLOW      = "#FDCB6E"
    BORDER      = "#33314a"
    INPUT_BG    = "#0e0d15"
    GREEN_DOT   = "#2ecc71"
    PINK_DOT    = "#fd79a8"
    YELLOW_DOT  = "#fdcb6e"
    RED_STATUS  = "#ff6b6b"

    _FONT_FAMILY = "Segoe UI"
    FONT_HEADING = (_FONT_FAMILY, 16, "bold")
    FONT_SUBTITLE = (_FONT_FAMILY, 10)
    FONT_LABEL   = (_FONT_FAMILY, 10, "bold")
    FONT_BODY    = (_FONT_FAMILY, 10)
    FONT_SMALL   = (_FONT_FAMILY, 9)
    FONT_BTN_PRI = (_FONT_FAMILY, 11, "bold")
    FONT_BTN_SEC = (_FONT_FAMILY, 10)
    FONT_BTN_GHO = (_FONT_FAMILY, 9)

    # -- Root Window ----------------------------------------
    root = tk.Tk()
    root.title("MetaFiller")
    root.geometry("620x760")
    root.configure(bg=BG)
    root.resizable(False, False)

    try:
        root.iconbitmap(default=None)
    except Exception:
        pass

    # -- Scrollable Canvas ----------------------------------
    outer = tk.Frame(root, bg=BG)
    outer.pack(fill="both", expand=True)

    canvas = tk.Canvas(outer, bg=BG, highlightthickness=0)
    scrollbar = tk.Scrollbar(outer, orient="vertical", command=canvas.yview)
    canvas.configure(yscrollcommand=scrollbar.set)

    scrollbar.pack(side="right", fill="y")
    canvas.pack(side="left", fill="both", expand=True)

    main_frame = tk.Frame(canvas, bg=BG, padx=24, pady=20)
    canvas_window = canvas.create_window((0, 0), window=main_frame, anchor="nw")

    def _on_frame_configure(event):
        canvas.configure(scrollregion=canvas.bbox("all"))

    def _on_canvas_configure(event):
        canvas.itemconfig(canvas_window, width=event.width)

    main_frame.bind("<Configure>", _on_frame_configure)
    canvas.bind("<Configure>", _on_canvas_configure)

    def _on_mousewheel(event):
        canvas.yview_scroll(int(-1 * (event.delta / 120)), "units")

    canvas.bind_all("<MouseWheel>", _on_mousewheel)

    # -- Helper: create a card frame ------------------------
    def make_card(parent, label_text=None):
        card = tk.Frame(parent, bg=SURFACE, bd=0, highlightthickness=1,
                        highlightbackground=BORDER, highlightcolor=BORDER)
        card.pack(fill="x", pady=(0, 16))
        inner = tk.Frame(card, bg=SURFACE, padx=16, pady=14)
        inner.pack(fill="x")
        if label_text:
            lbl = tk.Label(inner, text=label_text, bg=SURFACE, fg=ON_SURFACE,
                           font=FONT_LABEL, anchor="w")
            lbl.pack(fill="x", pady=(0, 10))
        return inner

    # -- Helper: styled entry -------------------------------
    def make_entry(parent, textvariable, readonly=False, **kw):
        e = tk.Entry(parent, textvariable=textvariable, bg=INPUT_BG,
                     fg=ON_SURFACE, insertbackground=PRIMARY,
                     font=FONT_BODY, bd=0, relief="flat",
                     readonlybackground=INPUT_BG,
                     highlightthickness=1, highlightbackground=BORDER,
                     highlightcolor=PRIMARY, **kw)
        if readonly:
            e.config(state="readonly")
        return e

    # -- Helper: toggle switch widget -----------------------
    class ToggleSwitch(tk.Canvas):
        def __init__(self, parent, variable, **kw):
            super().__init__(parent, width=40, height=22, bg=SURFACE,
                             highlightthickness=0, cursor="hand2", **kw)
            self.variable = variable
            self._draw()
            self.bind("<Button-1>", self._toggle)
            self.variable.trace_add("write", lambda *_: self._draw())

        def _draw(self):
            self.delete("all")
            on = self.variable.get()
            track_color = PRIMARY if on else "#474554"
            knob_x = 24 if on else 10
            self.create_rounded_rect(2, 3, 38, 19, 8, fill=track_color, outline="")
            self.create_oval(knob_x - 7, 4, knob_x + 7, 18, fill="#FFFFFF", outline="")

        def create_rounded_rect(self, x1, y1, x2, y2, r, **kw):
            points = [
                x1 + r, y1, x2 - r, y1, x2, y1, x2, y1 + r,
                x2, y2 - r, x2, y2, x2 - r, y2, x1 + r, y2,
                x1, y2, x1, y2 - r, x1, y1 + r, x1, y1,
            ]
            return self.create_polygon(points, smooth=True, **kw)

        def _toggle(self, event=None):
            self.variable.set(not self.variable.get())

    # -- Helper: styled dropdown ----------------------------
    def make_dropdown(parent, variable, values, state="readonly"):
        style_name = f"MF_{id(parent)}.TCombobox"
        combo = ttk.Combobox(parent, textvariable=variable, values=values,
                             state=state, font=FONT_BODY)
        return combo


    # ======================================================
    # 1. HEADER
    # ======================================================
    header_frame = tk.Frame(main_frame, bg=BG)
    header_frame.pack(fill="x", pady=(0, 4))

    icon_canvas = tk.Canvas(header_frame, width=32, height=32, bg=BG,
                            highlightthickness=0)
    icon_canvas.pack(side="left", padx=(0, 12))
    # Film clapperboard icon (simplified)
    icon_canvas.create_rectangle(4, 8, 28, 28, fill=PRIMARY, outline="")
    icon_canvas.create_polygon(4, 8, 10, 2, 22, 2, 28, 8, fill=PRIMARY_DIM, outline="")
    icon_canvas.create_line(8, 5, 14, 5, fill=ON_SURFACE, width=1)
    icon_canvas.create_line(18, 5, 24, 5, fill=ON_SURFACE, width=1)
    icon_canvas.create_rectangle(8, 12, 24, 14, fill=BG, outline="")

    title_block = tk.Frame(header_frame, bg=BG)
    title_block.pack(side="left", fill="x")

    tk.Label(title_block, text="MetaFiller", bg=BG, fg=ON_SURFACE,
             font=FONT_HEADING).pack(anchor="w")
    tk.Label(title_block, text="Continuity Metadata Assistant", bg=BG,
             fg=ON_MUTED, font=FONT_SUBTITLE).pack(anchor="w")

    # Accent divider
    divider = tk.Frame(main_frame, bg=PRIMARY, height=2)
    divider.pack(fill="x", pady=(8, 16))

    # ======================================================
    # 2. FILE SELECTION
    # ======================================================
    tk.Label(main_frame, text="Continuity Log", bg=BG, fg=ON_MUTED,
             font=FONT_LABEL).pack(anchor="w", pady=(0, 4))

    file_row = tk.Frame(main_frame, bg=BG)
    file_row.pack(fill="x", pady=(0, 8))

    csv_path_var = tk.StringVar(value=settings.get("last_csv_path", ""))

    csv_entry = make_entry(file_row, csv_path_var, readonly=True)
    csv_entry.pack(side="left", fill="x", expand=True, padx=(0, 8), ipady=6)

    browse_btn = tk.Button(file_row, text="Browse", bg=SURFACE_HI, fg=ON_SURFACE,
                           font=FONT_BTN_SEC, bd=0, padx=16, pady=4, cursor="hand2",
                           activebackground=PRIMARY, activeforeground="#FFFFFF",
                           highlightthickness=1, highlightbackground=BORDER)
    browse_btn.pack(side="right")

    # -- Sheet Selection Area -------------------------------
    sheet_frame = tk.Frame(main_frame, bg=BG)
    sheet_frame.pack(fill="x", pady=(0, 16))

    sheet_header = tk.Frame(sheet_frame, bg=BG)
    sheet_header.pack(fill="x", pady=(0, 6))

    sheet_label = tk.Label(sheet_header, text="Sheets Found:", bg=BG,
                           fg=ON_MUTED, font=FONT_SMALL)
    sheet_label.pack(side="left")

    sheet_count_badge = tk.Label(sheet_header, text="", bg=PRIMARY_DIM,
                                 fg="#FFFFFF", font=("Plus Jakarta Sans", 8, "bold"),
                                 padx=6, pady=1)
    sheet_count_badge.pack(side="left", padx=(6, 0))

    sheet_chips_frame = tk.Frame(sheet_frame, bg=BG)
    sheet_chips_frame.pack(fill="x")

    sheet_select_link = tk.Frame(sheet_frame, bg=BG)
    sheet_select_link.pack(fill="x", pady=(4, 0))

    # Storage for sheet toggle state
    sheet_vars = {}   # {sheet_name: BooleanVar}
    chip_widgets = [] # list of chip label widgets

    def _build_sheet_chips(sheet_names):
        # Clear existing
        for w in chip_widgets:
            w.destroy()
        chip_widgets.clear()
        sheet_vars.clear()

        for w in sheet_select_link.winfo_children():
            w.destroy()

        if not sheet_names or sheet_names == ["CSV"]:
            sheet_count_badge.config(text="CSV")
            return

        sheet_count_badge.config(text=f"{len(sheet_names)} sheets")

        for sn in sheet_names:
            var = tk.BooleanVar(value=True)
            sheet_vars[sn] = var

            chip = tk.Label(sheet_chips_frame, text=f"  *  {sn}  ", bg=PRIMARY_DIM,
                            fg="#FFFFFF", font=FONT_SMALL, padx=6, pady=3,
                            cursor="hand2", bd=0,
                            highlightthickness=1, highlightbackground=PRIMARY)
            chip.pack(side="left", padx=(0, 6), pady=2)
            chip_widgets.append(chip)

            def _make_toggler(chip_w, v, name):
                def _toggle(event=None):
                    v.set(not v.get())
                    if v.get():
                        chip_w.config(bg=PRIMARY_DIM, fg="#FFFFFF", text=f"  *  {name}  ",
                                      highlightbackground=PRIMARY)
                    else:
                        chip_w.config(bg=SURFACE, fg=ON_MUTED, text=f"      {name}  ",
                                      highlightbackground=BORDER)
                return _toggle

            chip.bind("<Button-1>", _make_toggler(chip, var, sn))

        # Select All / Deselect All links
        def _select_all():
            for sn, v in sheet_vars.items():
                v.set(True)
            _refresh_chips()

        def _deselect_all():
            for sn, v in sheet_vars.items():
                v.set(False)
            _refresh_chips()

        def _refresh_chips():
            for idx, sn in enumerate(sheet_names):
                v = sheet_vars[sn]
                chip_w = chip_widgets[idx]
                if v.get():
                    chip_w.config(bg=PRIMARY_DIM, fg="#FFFFFF", text=f"  *  {sn}  ",
                                  highlightbackground=PRIMARY)
                else:
                    chip_w.config(bg=SURFACE, fg=ON_MUTED, text=f"      {sn}  ",
                                  highlightbackground=BORDER)

        sel_all = tk.Label(sheet_select_link, text="Select All", bg=BG,
                           fg=PRIMARY, font=("Plus Jakarta Sans", 8), cursor="hand2")
        sel_all.pack(side="left")
        sel_all.bind("<Button-1>", lambda e: _select_all())

        tk.Label(sheet_select_link, text=" / ", bg=BG, fg=ON_MUTED,
                 font=("Plus Jakarta Sans", 8)).pack(side="left")

        desel_all = tk.Label(sheet_select_link, text="Deselect All", bg=BG,
                             fg=PRIMARY, font=("Plus Jakarta Sans", 8), cursor="hand2")
        desel_all.pack(side="left")
        desel_all.bind("<Button-1>", lambda e: _deselect_all())

    def _get_selected_sheets():
        """Return list of selected sheet names or None (for CSV / all selected)."""
        if not sheet_vars:
            return None
        selected = [sn for sn, v in sheet_vars.items() if v.get()]
        if len(selected) == len(sheet_vars):
            return None  # all selected = no filter
        return selected if selected else None

    # ======================================================
    # 3. SCAN SETTINGS CARD
    # ======================================================
    scan_card = make_card(main_frame, "Scan Settings")

    # Configure ttk styles for dark theme
    style = ttk.Style(root)
    style.theme_use("clam")
    style.configure(".", background=BG, foreground=ON_SURFACE, font=FONT_BODY)
    style.configure("TLabel", background=BG, foreground=ON_SURFACE)
    style.configure("TCombobox", fieldbackground=INPUT_BG, background=SURFACE_HI,
                     foreground=ON_SURFACE, arrowcolor=ON_MUTED)
    style.map("TCombobox",
              fieldbackground=[("readonly", INPUT_BG), ("active", SURFACE_HI)],
              foreground=[("readonly", ON_SURFACE), ("active", ON_SURFACE)])
    root.option_add("*TCombobox*Listbox.background", SURFACE)
    root.option_add("*TCombobox*Listbox.foreground", ON_SURFACE)
    root.option_add("*TCombobox*Listbox.selectBackground", PRIMARY)
    root.option_add("*TCombobox*Listbox.selectForeground", "#FFFFFF")

    scan_row1 = tk.Frame(scan_card, bg=SURFACE)
    scan_row1.pack(fill="x", pady=(0, 8))

    tk.Label(scan_row1, text="Scan Scope", bg=SURFACE, fg=ON_MUTED,
             font=FONT_SMALL).pack(anchor="w")

    scopes = [
        "SELECTED_BIN_WITH_SUBFOLDERS",
        "ENTIRE_MEDIA_POOL",
        "CURRENT_SELECTED_BIN",
        "BIN_PATH_CONTAINS_FILTER"
    ]
    scope_var = tk.StringVar(value=settings.get("scan_scope", "SELECTED_BIN_WITH_SUBFOLDERS"))
    scope_combo = make_dropdown(scan_row1, scope_var, scopes)
    scope_combo.pack(fill="x", pady=(2, 0), ipady=3)

    scan_row2 = tk.Frame(scan_card, bg=SURFACE)
    scan_row2.pack(fill="x")

    tk.Label(scan_row2, text="Bin Path Filter", bg=SURFACE, fg=ON_MUTED,
             font=FONT_SMALL).pack(anchor="w")

    filter_var = tk.StringVar(value=settings.get("bin_filter_string", "Raw_Camera"))
    filter_entry = make_entry(scan_row2, filter_var)
    filter_entry.pack(fill="x", pady=(2, 0), ipady=6)

    # ======================================================
    # 4. OPTIONS CARD
    # ======================================================
    opt_card = make_card(main_frame, "Options")

    def _make_toggle_row(parent, label_text, variable, extra_widget=None):
        row = tk.Frame(parent, bg=SURFACE)
        row.pack(fill="x", pady=(0, 10))
        tk.Label(row, text=label_text, bg=SURFACE, fg=ON_SURFACE,
                 font=FONT_BODY).pack(side="left")
        if extra_widget:
            extra_widget.pack(side="left", padx=(6, 0))
        toggle = ToggleSwitch(row, variable)
        toggle.pack(side="right")
        return row

    overwrite_var = tk.BooleanVar(value=settings.get("overwrite", False))
    _make_toggle_row(opt_card, "Overwrite existing metadata", overwrite_var)

    # Color legend dots
    colors_var = tk.BooleanVar(value=settings.get("apply_colors", True))
    dot_frame = tk.Frame(opt_card, bg=SURFACE)
    for c in [GREEN_DOT, PINK_DOT, YELLOW_DOT]:
        dot = tk.Canvas(dot_frame, width=8, height=8, bg=SURFACE, highlightthickness=0)
        dot.create_oval(0, 0, 8, 8, fill=c, outline="")
        dot.pack(side="left", padx=1)
    _make_toggle_row(opt_card, "Apply clip colors & flags", colors_var, dot_frame)

    audio_var = tk.BooleanVar(value=settings.get("apply_audio", True))
    _make_toggle_row(opt_card, "Match & sync audio clips", audio_var)

    # ======================================================
    # 5. TIMELINE BUILDER (Collapsible)
    # ======================================================
    tb_outer = tk.Frame(main_frame, bg=SURFACE, bd=0, highlightthickness=1,
                        highlightbackground=BORDER, highlightcolor=BORDER)
    tb_outer.pack(fill="x", pady=(0, 16))

    tb_header = tk.Frame(tb_outer, bg=SURFACE, padx=16, pady=10, cursor="hand2")
    tb_header.pack(fill="x")

    tk.Label(tb_header, text="Timeline Builder", bg=SURFACE, fg=ON_SURFACE,
             font=FONT_LABEL).pack(side="left")

    chevron_lbl = tk.Label(tb_header, text="^", bg=SURFACE, fg=ON_MUTED,
                           font=("Plus Jakarta Sans", 10))
    chevron_lbl.pack(side="right")

    tb_content = tk.Frame(tb_outer, bg=SURFACE, padx=16, pady=(0))
    tb_content.pack(fill="x")

    tb_expanded = tk.BooleanVar(value=True)

    def _toggle_timeline():
        if tb_expanded.get():
            tb_content.pack_forget()
            chevron_lbl.config(text="v")
            tb_expanded.set(False)
        else:
            tb_content.pack(fill="x")
            chevron_lbl.config(text="^")
            tb_expanded.set(True)

    tb_header.bind("<Button-1>", lambda e: _toggle_timeline())
    for w in tb_header.winfo_children():
        w.bind("<Button-1>", lambda e: _toggle_timeline())

    # Timeline dropdowns
    tb_grid = tk.Frame(tb_content, bg=SURFACE)
    tb_grid.pack(fill="x", pady=(0, 14))

    def _tb_row(parent, label, variable, values, row_idx):
        tk.Label(parent, text=label, bg=SURFACE, fg=ON_MUTED,
                 font=FONT_SMALL).grid(row=row_idx, column=0, sticky="w", pady=4)
        combo = make_dropdown(parent, variable, values)
        combo.grid(row=row_idx, column=1, sticky="ew", padx=(12, 0), pady=4)

    group_var = tk.StringVar(value=settings.get("timeline_group_by", "Scene"))
    _tb_row(tb_grid, "Group By:", group_var,
            ["Scene", "Entire CSV", "Shoot Day", "Camera"], 0)

    include_var = tk.StringVar(value=settings.get("timeline_include", "Good Takes Only"))
    _tb_row(tb_grid, "Include Takes:", include_var,
            ["Good Takes Only", "Good + Untagged", "All Except Rejected", "All Clips"], 1)

    order_var = tk.StringVar(value=settings.get("timeline_order", "Scene > Shot > Take > Camera"))
    _tb_row(tb_grid, "Sort Order:", order_var,
            ["Scene > Shot > Take > Camera",
             "Shoot Day > Scene > Shot > Take > Camera",
             "CSV Row Order"], 2)

    bin_mode_var = tk.StringVar(value=settings.get("timeline_bin_mode", "Current Bin"))
    _tb_row(tb_grid, "Build Location:", bin_mode_var,
            ["Current Bin", "Custom Bin"], 3)

    custom_bin_label = tk.Label(tb_grid, text="Custom Bin:", bg=SURFACE, fg=ON_MUTED,
                                font=FONT_SMALL)
    custom_bin_label.grid(row=4, column=0, sticky="w", pady=4)

    custom_bin_path_var = tk.StringVar(value=settings.get("timeline_custom_bin_path", "Master/05_Timeline_Comp"))

    custom_bin_frame = tk.Frame(tb_grid, bg=SURFACE)
    custom_bin_frame.grid(row=4, column=1, sticky="ew", padx=(12, 0), pady=4)

    initial_folders = get_all_folder_paths()
    custom_bin_combo = make_dropdown(custom_bin_frame, custom_bin_path_var, initial_folders, state="normal")
    custom_bin_combo.pack(side="left", fill="x", expand=True)

    def refresh_folders():
        new_folders = get_all_folder_paths()
        custom_bin_combo.config(values=new_folders)

    refresh_btn = tk.Button(custom_bin_frame, text="⟳", bg=SURFACE_HI, fg=ON_SURFACE,
                            font=FONT_SMALL, bd=0, padx=8, cursor="hand2", command=refresh_folders)
    refresh_btn.pack(side="right", padx=(6, 0))

    def update_bin_mode_state(*args):
        if bin_mode_var.get() == "Custom Bin":
            custom_bin_combo.config(state="normal")
            refresh_btn.config(state="normal")
            custom_bin_label.config(fg=ON_MUTED)
        else:
            custom_bin_combo.config(state="disabled")
            refresh_btn.config(state="disabled")
            custom_bin_label.config(fg="#474554")

    bin_mode_var.trace_add("write", update_bin_mode_state)
    update_bin_mode_state()

    tb_grid.columnconfigure(1, weight=1)


    # ======================================================
    # 6. STATUS BAR
    # ======================================================
    status_frame = tk.Frame(main_frame, bg=BG)
    status_frame.pack(fill="x", pady=(0, 16))

    status_dot = tk.Canvas(status_frame, width=10, height=10, bg=BG,
                           highlightthickness=0)
    status_dot.create_oval(1, 1, 9, 9, fill=TEAL, outline="")
    status_dot.pack(side="left", padx=(0, 6))

    status_label = tk.Label(status_frame, text="Ready", bg=BG, fg=TEAL,
                            font=FONT_SMALL, anchor="w")
    status_label.pack(side="left", fill="x")

    def set_status(text, color=TEAL):
        status_label.config(text=text, fg=color)
        status_dot.delete("all")
        status_dot.create_oval(1, 1, 9, 9, fill=color, outline="")
        root.update()

    # ======================================================
    # 7. ACTION BUTTONS
    # ======================================================
    btn_area = tk.Frame(main_frame, bg=BG)
    btn_area.pack(fill="x", side="bottom")

    # Primary row
    primary_row = tk.Frame(btn_area, bg=BG)
    primary_row.pack(fill="x", pady=(0, 8))

    # "Check" -- secondary outlined
    check_btn = tk.Button(primary_row, text="*  Check", bg=BG, fg=PRIMARY,
                          font=FONT_BTN_SEC, bd=0, padx=20, pady=8,
                          cursor="hand2", activebackground=PRIMARY_DIM,
                          activeforeground="#FFFFFF",
                          highlightthickness=1, highlightbackground=PRIMARY,
                          highlightcolor=PRIMARY)
    check_btn.pack(side="left", padx=(0, 8), expand=True, fill="x")

    # "Apply Metadata" -- primary filled
    apply_btn = tk.Button(primary_row, text="Apply Metadata", bg=PRIMARY,
                          fg="#FFFFFF", font=FONT_BTN_PRI, bd=0, padx=24, pady=10,
                          cursor="hand2", activebackground=PRIMARY_DIM,
                          activeforeground="#FFFFFF")
    apply_btn.pack(side="left", expand=True, fill="x")

    # Secondary row
    secondary_row = tk.Frame(btn_area, bg=BG)
    secondary_row.pack(fill="x")

    # Ghost buttons
    def _ghost_btn(parent, text, command):
        btn = tk.Button(parent, text=text, bg=BG, fg=ON_MUTED, font=FONT_BTN_GHO,
                        bd=0, padx=12, pady=5, cursor="hand2",
                        activebackground=SURFACE, activeforeground=ON_SURFACE)
        btn.config(command=command)
        return btn

    timeline_btn = _ghost_btn(secondary_row, "  Build Timeline", lambda: None)
    timeline_btn.pack(side="left", expand=True, fill="x")

    reports_btn = _ghost_btn(secondary_row, "  Open Reports", lambda: None)
    reports_btn.pack(side="left", expand=True, fill="x")

    close_btn = _ghost_btn(secondary_row, "Close", root.destroy)
    close_btn.pack(side="left", expand=True, fill="x")

    # ======================================================
    # BUTTON STATE MANAGEMENT
    # ======================================================
    def update_button_states():
        path = csv_path_var.get()
        if path and os.path.exists(path):
            check_btn.config(state="normal")
            apply_btn.config(state="normal")
            timeline_btn.config(state="normal")
            set_status("Ready", TEAL)
        elif path:
            check_btn.config(state="disabled")
            apply_btn.config(state="disabled")
            timeline_btn.config(state="disabled")
            set_status("File not found. Select again.", RED_STATUS)
        else:
            check_btn.config(state="disabled")
            apply_btn.config(state="disabled")
            timeline_btn.config(state="disabled")
            set_status("Select a continuity log file.", ON_MUTED)

    # ======================================================
    # BROWSE CALLBACK
    # ======================================================
    def browse_csv():
        last_dir = settings.get("last_dir", "")
        selected = filedialog.askopenfilename(
            initialdir=last_dir,
            title="Select Continuity Log",
            filetypes=[
                ("Log Files", "*.csv;*.xlsx"),
                ("CSV Files", "*.csv"),
                ("Excel Files", "*.xlsx"),
                ("All Files", "*.*")
            ]
        )
        if selected:
            _, ext = os.path.splitext(selected)
            if ext.lower() not in (".csv", ".xlsx"):
                messagebox.showerror("Invalid File Type",
                                     "Please select a valid CSV or XLSX file.")
                return

            settings["last_csv_path"] = selected
            settings["last_dir"] = os.path.dirname(selected)
            save_settings(settings)

            csv_path_var.set(selected)

            # Detect sheets and build chips
            try:
                names = get_sheet_names(selected)
                _build_sheet_chips(names)
            except Exception:
                _build_sheet_chips(["CSV"])

            update_button_states()

    browse_btn.config(command=browse_csv)

    # ======================================================
    # ACTION CALLBACKS
    # ======================================================
    def _disable_action_buttons():
        try:
            apply_btn.config(state="disabled")
            check_btn.config(state="disabled")
            timeline_btn.config(state="disabled")
        except Exception:
            pass

    def _enable_action_buttons():
        try:
            apply_btn.config(state="normal")
            check_btn.config(state="normal")
            timeline_btn.config(state="normal")
        except Exception:
            pass

    def run_action(dry_run):
        path = csv_path_var.get()
        scope = scope_var.get()
        filt = filter_var.get()
        overwrite = overwrite_var.get()
        colors = colors_var.get()
        apply_audio = audio_var.get()
        sel_sheets = _get_selected_sheets()

        settings["scan_scope"] = scope
        settings["bin_filter_string"] = filt
        settings["overwrite"] = overwrite
        settings["apply_colors"] = colors
        settings["apply_audio"] = apply_audio
        save_settings(settings)

        action_name = "Check" if dry_run else "Apply"
        set_status(f"Running {action_name}...", YELLOW)
        _disable_action_buttons()
        root.update()

        ok, msg, rpt = run_metadata_import(
            path, dry_run=dry_run, scan_scope=scope, filter_str=filt,
            overwrite=overwrite, apply_colors=colors, apply_audio=apply_audio,
            selected_sheets=sel_sheets, progress_callback=root.update
        )

        _enable_action_buttons()
        if ok:
            set_status(f"{action_name} complete. Report written.", TEAL)
            messagebox.showinfo(f"{action_name} Summary",
                                f"{msg}\n\nReport Path:\n{rpt}")
        else:
            set_status(f"{action_name} failed.", RED_STATUS)
            messagebox.showerror("Error", f"Execution failed:\n{msg}")

    def run_stringout():
        path = csv_path_var.get()
        scope = scope_var.get()
        filt = filter_var.get()
        group = group_var.get()
        inc = include_var.get()
        ord_mode = order_var.get()
        bin_mode = bin_mode_var.get()
        custom_path = custom_bin_path_var.get()
        sel_sheets = _get_selected_sheets()

        settings["scan_scope"] = scope
        settings["bin_filter_string"] = filt
        settings["timeline_group_by"] = group
        settings["timeline_include"] = inc
        settings["timeline_order"] = ord_mode
        settings["timeline_bin_mode"] = bin_mode
        settings["timeline_custom_bin_path"] = custom_path
        save_settings(settings)

        set_status("Building Timelines...", YELLOW)
        _disable_action_buttons()
        root.update()

        ok, msg = run_timeline_builder(
            path, scan_scope=scope, filter_str=filt,
            group_by=group, include_mode=inc, order_mode=ord_mode,
            selected_sheets=sel_sheets,
            bin_mode=bin_mode, custom_bin_path=custom_path,
            progress_callback=root.update
        )

        _enable_action_buttons()
        if ok:
            set_status("Timelines built.", TEAL)
            messagebox.showinfo("Timeline Builder Success", msg)
        else:
            set_status("Timeline Builder failed.", RED_STATUS)
            messagebox.showerror("Timeline Builder Error", f"Failed:\n{msg}")

    def open_report_folder():
        path = csv_path_var.get()
        if path:
            reports_dir = os.path.join(os.path.dirname(path), "MetaFiller_Reports")
            if not os.path.exists(reports_dir):
                os.makedirs(reports_dir, exist_ok=True)
            try:
                os.startfile(reports_dir)
            except Exception as e:
                messagebox.showerror("Error", f"Could not open folder:\n{e}")
        else:
            last_dir = settings.get("last_dir", "")
            if last_dir and os.path.exists(last_dir):
                try:
                    os.startfile(last_dir)
                except Exception as e:
                    messagebox.showerror("Error", f"Could not open folder:\n{e}")
            else:
                messagebox.showinfo("Info", "No log file selected yet.")

    # Wire up button commands
    check_btn.config(command=lambda: run_action(True))
    apply_btn.config(command=lambda: run_action(False))
    timeline_btn.config(command=run_stringout)
    reports_btn.config(command=open_report_folder)

    # ======================================================
    # INITIAL STATE
    # ======================================================
    # If there's already a file loaded, detect sheets
    initial_path = csv_path_var.get()
    if initial_path and os.path.exists(initial_path):
        try:
            names = get_sheet_names(initial_path)
            _build_sheet_chips(names)
        except Exception:
            _build_sheet_chips(["CSV"])

    update_button_states()

    root.mainloop()


if __name__ == "__main__":
    try:
        launch_gui()
    except Exception as e:
        try:
            with open(r"d:/04_ACTIVE_WORKBENCH/MetaFiller/metafiller_debug.log", "w", encoding="utf-8") as f:
                f.write("CRASHED AT STARTUP:\n")
                traceback.print_exc(file=f)
        except Exception:
            pass
        print("SCRIPT FAILED:")
        print(traceback.format_exc())