from pyscript import document, window
import pandas as pd
import io
import js
from datetime import datetime

# 全域變數
status_area = document.getElementById("status-area")
status_icon = status_area.querySelector(".status-icon")
status_title = status_area.querySelector("h3")
status_desc = status_area.querySelector("p")
drop_area = document.getElementById("drop-area")
file_input = document.getElementById("file-input")

def update_status(state, title, message):
    """更新 UI 狀態"""
    # 清除所有狀態 class
    status_area.classList.remove("processing", "success", "error", "unknown")
    
    if state == "processing":
        status_area.classList.add("processing")
        status_icon.innerText = "⏳"
    elif state == "success":
        status_area.classList.add("success")
        status_icon.innerText = "✅"
    elif state == "error":
        status_area.classList.add("error")
        status_icon.innerText = "❌"
    else:
        status_area.classList.add("unknown")
        status_icon.innerText = "Waiting"

    status_title.innerText = title
    status_desc.innerText = message

def log(message):
    """輸出日誌到 console 和 terminal 區域"""
    print(message)
    term = document.getElementById("terminal-output")
    term.hidden = False
    term.innerText += f"[{datetime.now().strftime('%H:%M:%S')}] {message}\n"
    term.scrollTop = term.scrollHeight

async def process_file_content(file_obj):
    """讀取並處理 Excel 檔案"""
    update_status("processing", "正在轉換...", f"讀取檔案: {file_obj.name}")
    log(f"開始處理檔案: {file_obj.name}")

    try:
        # 讀取檔案內容為 ArrayBuffer
        array_buf = await file_obj.arrayBuffer()
        # 轉換為 Python bytes
        file_bytes = array_buf.to_bytes()
        
        # 使用 pandas 讀取 Excel
        log("正在解析 Excel 資料...")
        # 使用 ExcelFile 以便檢查工作表名稱
        xls = pd.ExcelFile(io.BytesIO(file_bytes))
        sheet_names = xls.sheet_names
        log(f"工作表清單: {sheet_names}")

        target_sheet = "英規"
        if target_sheet in sheet_names:
            log(f"找到 '{target_sheet}' 工作表，開始讀取...")
            # 先讀取 header=None 以便後續動態搜尋標題列
            df = pd.read_excel(xls, sheet_name=target_sheet, header=None)
        else:
            log(f"警告: 找不到 '{target_sheet}' 工作表，預設讀取第一個工作表: {sheet_names[0]}")
            df = pd.read_excel(xls, sheet_name=0, header=None)
            
        # --- 讀取 "簡易" Sheet 以製作對照表 ---
        df_simp = None
        if "簡易" in sheet_names:
             log("讀取 '簡易' 工作表以建立對照表...")
             df_simp = pd.read_excel(xls, sheet_name="簡易", header=None)
        
        log(f"讀取成功！原始資料: {df.shape[0]} 筆列, {df.shape[1]} 個欄位")

        # --- 核心轉換邏輯開始 ---
        log("正在執行資料轉換...")
        
        # 呼叫轉換函式
        metadata_rows, processed_df = transform_data(df)

        # --- 生成中英對照表 ---
        # 即使轉換成功，也嘗試生成對照表
        if df_simp is not None:
            try:
                table_html = generate_translation_table(df, df_simp)
                # 更新 DOM
                tbody = document.getElementById("comparison-table").querySelector("tbody")
                tbody.innerHTML = table_html
                document.getElementById("translation-result").hidden = False
                log("中英對照表已生成")
            except Exception as e:
                log(f"生成對照表時發生錯誤: {e}")
                document.getElementById("translation-result").hidden = True
        else:
            document.getElementById("translation-result").hidden = True
        
        # --- 轉換結束 ---

        # 輸出為 CSV (解決中文亂碼問題，使用 utf-8-sig)
        log("正在產生 ERP 匯入檔 (CSV)...")
        csv_buffer = io.StringIO()
        
        # 1. 先寫入 Metadata (前 9 列)
        for row in metadata_rows:
            # 將列表轉為 CSV 格式字串 (簡單以逗號分隔，需處理特殊字元)
            # 使用 csv module 處理會更穩健，但這裡簡單拼湊即可
            # 注意: 若內容有逗號，需用引號包起來
            line = ",".join([f'"{str(x)}"' if x else "" for x in row])
            csv_buffer.write(line + "\n")
        
        # 2. 寫入 BOM 資料 (附加模式)
        processed_df.to_csv(csv_buffer, index=False, encoding='utf-8-sig')
        
        # 取得完整內容
        csv_content = csv_buffer.getvalue()

        # 觸發下載
        download_filename = f"ERP_Import_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv"
        trigger_download(csv_content, download_filename)
        
        update_status("success", "轉換成功！", f"已自動下載: {download_filename}")
        log("轉換完成。")

    except Exception as e:
        error_msg = str(e)
        update_status("error", "轉換失敗", error_msg)
        log(f"錯誤: {error_msg}")
        # 在 console 印出完整 traceback 方便除錯
        import traceback
        traceback.print_exc()

def transform_data(df):
    """
    自訂資料轉換邏輯: 英規 -> 原稿/簡易
    回傳: (metadata_rows, processed_df)
    """
    
    # --- Metadata 提取 (前 8 列邏輯，參照圖片) ---
    # 預設 Metadata 值
    meta_model_name = ""
    meta_brand = ""
    meta_year = ""
    meta_color = ""
    meta_size = ""
    meta_standard = ""
    meta_pbi = "" # PBI Info (e.g. 6B200213...)
    
    # 嘗試從前幾列讀取 Metadata (假設固定位置)
    try:
        # Row 1 (Index 0): Header Data (e.g. REFERENCE:, CUSTOMER:...)
        # Row 2 (Index 1): CUSTOMER / STANDARD / PBI
        # Row 3 (Index 2): BRAND / MODEL YEAR / SAGE
        # Row 4 (Index 3): MODEL NAME / FRAME COLOR / FRAME ARTWORK FILE NAME
        # Row 5 (Index 4): Values "CLASH 24", "PURE WHITE"
        
        # 掃描前 10 列找關鍵字
        for i in range(10):
            row_vals = [str(x).strip().upper() for x in df.iloc[i] if pd.notna(x)]
            row_raw = df.iloc[i]
            
            # --- Row 1 PBI Logic ---
            if "PBI" in str(row_raw).upper():
                for val in row_raw:
                    if pd.notna(val) and "PBI" in str(val).upper():
                         meta_pbi = str(val).split(":")[-1].strip() # 粗略抓取

            # --- Row 2 Standard Logic ---
            if "STANDARD:" in row_vals or "STANDARD" in row_vals:
                 for val in row_raw:
                    if pd.notna(val) and "STANDARD" in str(val).upper():
                        meta_standard = str(val).split(":")[-1].strip()

            # --- Row 3 Brand / Year ---
            if "BRAND:" in str(row_raw).upper():
                for val in row_raw:
                    if pd.notna(val) and "BRAND:" in str(val).upper():
                        meta_brand = str(val).split(":")[-1].strip()
            
            if "MODEL YEAR:" in str(row_raw).upper():
                 for val in row_raw:
                    if pd.notna(val) and "MODEL YEAR:" in str(val).upper():
                        meta_year = str(val).split(":")[-1].strip()

            # --- Row 4/5 Model Name Logic ---
            if "MODEL NAME" in row_vals:
                # 假設下一列 (i+1) 對應位置是值
                for col_idx, val in enumerate(row_raw):
                    if pd.notna(val) and "MODEL NAME" in str(val).upper():
                        if i + 1 < len(df):
                            meta_model_name = str(df.iloc[i+1, col_idx]).strip()
                        break
            
            if "FRAME COLOR" in row_vals:
                for col_idx, val in enumerate(row_raw):
                    if pd.notna(val) and "FRAME COLOR" in str(val).upper():
                        if i + 1 < len(df):
                            meta_color = str(df.iloc[i+1, col_idx]).strip()
                        break

        # 產品代碼/名稱 暫時用 Model Name
        meta_product_code = ""
        meta_product_name = ""
        if meta_model_name and meta_model_name != "nan":
            meta_product_name = meta_model_name
            meta_product_code = "7-" + meta_model_name + "-00"
            
            # 嘗試從 Code 提取尺寸 (e.g. CLASH 24 -> 24")
            if "24" in meta_model_name:
                meta_size = '24"'
            elif "20" in meta_model_name:
                meta_size = '20"'
            elif "27" in meta_model_name:
                meta_size = '27.5"'
            else:
                meta_size = ""

    except Exception as e:
        log(f"Metadata 提取失敗: {e}")

    # 建構 8 列 Metadata (依照圖片格式)
    # Row 1: [Model Name] | [] | [PBI Info] (Merged header style simulation)
    # Row 2: (Empty)
    # Row 3: 產品代碼 | Code | 庫存單位 (KG) | ...
    # Row 4: 產品名稱 | Name | 車別 | ...
    # Row 5: 品牌客戶 | Code(1301) | Brand | 測驗標準 | Standard
    # Row 6: 顏色 | | | 尺寸 | Size
    # Row 7: 年度 | Year | | 規格: | ...
    # Row 8: (Yellow Bar - Notes)
    
    metadata_rows = [
        [meta_model_name, "", f"PBI : {meta_pbi}"],                     # Row 1
        [],                                                             # Row 2
        ["產品代碼", meta_product_code, "庫存單位 (KG)", ""],             # Row 3
        ["產品名稱", meta_product_name, "車別", ""],                      # Row 4
        ["品牌客戶", "1301", meta_brand, "測驗標準", meta_standard],      # Row 5
        ["顏色", "", "", "尺寸", meta_size],                             # Row 6
        ["年度", meta_year, "", "規格:", meta_size],                      # Row 7
        ["電動車 -(耐重30KG)(破壞強度24KGS) (品項) 附件"],                 # Row 8 (Simulate Yellow Bar)
    ]

    # --- 核心 BOM 轉換 ---
    
    # 1. 動態尋找標題列
    # 搜尋包含 "ITEMS" 和 "VENDOR" 的列
    header_row_idx = None
    for idx, row in df.iterrows():
        # 將列轉換為字串並轉大寫以進行搜尋
        row_str = " ".join([str(val).upper() for val in row if pd.notna(val)])
        if "ITEMS" in row_str and "VENDOR" in row_str:
            header_row_idx = idx
            break
    
    if header_row_idx is None:
        log("警告: 無法自動偵測標題列 (找不到 ITEMS/VENDOR)，嘗試使用預設第 5 列 (Index 4)")
        header_row_idx = 4
    else:
        log(f"偵測到標題列位於第 {header_row_idx + 1} 列")

    # 重新設定 DataFrame 的 header
    df.columns = df.iloc[header_row_idx]
    # 截取資料列 (標題列之後)
    df = df[header_row_idx + 1:].reset_index(drop=True)
    
    # 清理欄位名稱 (移除換行、前後空白)
    df.columns = df.columns.astype(str).str.replace('\n', '').str.strip()
    log(f"原始欄位: {list(df.columns)}")

    # 2. 建立目標 DataFrame (原稿/簡易 格式)
    # 定義目標欄位 (依照圖片順序)
    target_columns = [
        "聯絡", "料號", "製程", "代碼", "品名", "廠商", 
        "廠商型號", "材質", "表面處理", "數量", "單位", 
        "(採購) 敘述", "(生產) 備註"
    ]
    erp_df = pd.DataFrame(columns=target_columns)

    # 3. 欄位對應 (Mapping)
    
    # Helper: 安全取得欄位資料
    def get_col_data(col_name):
        matches = [c for c in df.columns if col_name in str(c).upper()]
        if matches:
            return df[matches[0]].astype(str).replace('nan', '')
        return pd.Series([''] * len(df))

    # Items -> 品名
    erp_df["品名"] = get_col_data("ITEMS")
    # Vendor -> 廠商
    erp_df["廠商"] = get_col_data("VENDOR")
    
    # Model + P/N -> 廠商型號 (Merge)
    model_col = get_col_data("MODEL")
    pn_col = get_col_data("P/N")
    erp_df["廠商型號"] = model_col + "\n" + pn_col
    # 清理掉單獨的換行 (若無 P/N 會多個 \n)
    erp_df["廠商型號"] = erp_df["廠商型號"].str.strip()

    # Technical Spec -> 材質 (或 採購敘述?)
    # 根據圖片，"TECHNICAL SPECIFICATION" 對應 "材質" (如 ALLOY 6066...)
    erp_df["材質"] = get_col_data("TECHNICAL SPECIFICATION")
    
    # Assembly Instructions -> (生產) 備註
    erp_df["(生產) 備註"] = get_col_data("ASSEMBLY INSTRUCTIONS")

    # Base Color / Paint -> 表面處理 ?
    # 圖片範例中，Row 3 (Paint) "SHINY CLEAR COAT" 在 "表面處理" 欄位嗎?
    # 暫時抓取 BASE COLOR
    erp_df["表面處理"] = get_col_data("BASE COLOR")

    # 4. 資料清洗與預設值
    
    # 移除全空的列 (依品名判斷)
    erp_df = erp_df[erp_df["品名"] != ""]
    
    # 填入預設值
    erp_df["單位"] = "PCS" # 預設單位
    # erp_df["數量"] = 1 # 預設數量?
    
    log(f"轉換後資料預覽: {len(erp_df)} 筆")
    return metadata_rows, erp_df

def trigger_download(content, filename):
    """在瀏覽器端觸發檔案下載"""
    blob = js.Blob.new([content], {type: "text/csv;charset=utf-8;"})
    url = js.URL.createObjectURL(blob)
    
    a = document.createElement("a")
    a.href = url
    a.download = filename
    document.body.appendChild(a)
    a.click()
    document.body.removeChild(a)
    js.URL.revokeObjectURL(url)

async def handle_file_select(event):
    """處理檔案選擇事件"""
    file_list = event.target.files
    if not file_list:
        return
    
    file_obj = file_list.item(0)
    await process_file_content(file_obj)
    # 重置 input 以便重複選擇相同檔案
    file_input.value = ""

def generate_translation_table(df_eng_raw, df_simp_raw):
    """
    生成中英對照表 HTML (英規 vs 簡易)
    並驗證 MODEL 是否對應
    """
    
    # --- 1. 處理英規 (df_eng) ---
    eng_header_idx = -1
    for i, row in df_eng_raw.iterrows():
        row_str = str(row.tolist())
        if "ITEMS" in row_str and "VENDOR" in row_str:
            eng_header_idx = i
            break
            
    if eng_header_idx == -1:
        return "<tr><td colspan='5'>錯誤: 無法在 '英規' 表中找到 ITEMS/VENDOR 欄位</td></tr>"
        
    df_eng = df_eng_raw.iloc[eng_header_idx+1:].reset_index(drop=True)
    df_eng.columns = df_eng_raw.iloc[eng_header_idx]
    
    # 找出必要欄位索引 (英規)
    try:
        col_item_en = df_eng.columns.get_loc("ITEMS")
        col_vendor_en = df_eng.columns.get_loc("VENDOR")
        col_model_en = df_eng.columns.get_loc("MODEL")
        # P/N 是非必要，但有助於對應
        col_pn_en = df_eng.columns.get_loc("P/N") if "P/N" in df_eng.columns else -1
    except KeyError as e:
        return f"<tr><td colspan='5'>錯誤: '英規' 表缺少必要欄位: {e}</td></tr>"

    # --- 2. 處理簡易 (df_simp) ---
    simp_header_idx = -1
    for i, row in df_simp_raw.iterrows():
        row_str = str(row.tolist())
        if "品名" in row_str and "廠商" in row_str:
            simp_header_idx = i
            break
            
    if simp_header_idx == -1:
        # Fallback: 嘗試直接指定 Row 9 (Index 9)
        if len(df_simp_raw) > 9:
             header_cand = df_simp_raw.iloc[9].tolist()
             if "品名" in str(header_cand):
                 simp_header_idx = 9
    
    if simp_header_idx == -1:
         return "<tr><td colspan='5'>錯誤: 無法在 '簡易' 表中找到 品名/廠商 欄位</td></tr>"

    df_simp = df_simp_raw.iloc[simp_header_idx+1:].reset_index(drop=True)
    df_simp.columns = df_simp_raw.iloc[simp_header_idx]
    
    # 找出必要欄位索引 (簡易)
    try:
        col_item_tw = df_simp.columns.get_loc("品名")
        col_vendor_tw = df_simp.columns.get_loc("廠商")
        col_model_tw = df_simp.columns.get_loc("廠商型號")
    except KeyError as e:
        return f"<tr><td colspan='5'>錯誤: '簡易' 表缺少必要欄位: {e}</td></tr>"

    # --- 3. 逐列比對 ---
    html_rows = ""
    
    # 取最小列數進行比對 (或是以英規為主)
    max_rows = min(len(df_eng), len(df_simp))
    
    for i in range(max_rows):
        # 取得英規資料
        item_en = str(df_eng.iloc[i, col_item_en]).strip() if pd.notna(df_eng.iloc[i, col_item_en]) else ""
        vendor_en = str(df_eng.iloc[i, col_vendor_en]).strip() if pd.notna(df_eng.iloc[i, col_vendor_en]) else ""
        model_en = str(df_eng.iloc[i, col_model_en]).strip() if pd.notna(df_eng.iloc[i, col_model_en]) else ""
        pn_en = ""
        if col_pn_en != -1 and pd.notna(df_eng.iloc[i, col_pn_en]):
             pn_en = str(df_eng.iloc[i, col_pn_en]).strip()
            
        # 忽略空行
        if item_en == "nan" or item_en == "":
            continue
            
        # 取得簡易資料
        item_tw = str(df_simp.iloc[i, col_item_tw]).strip() if pd.notna(df_simp.iloc[i, col_item_tw]) else ""
        vendor_tw = str(df_simp.iloc[i, col_vendor_tw]).strip() if pd.notna(df_simp.iloc[i, col_vendor_tw]) else ""
        model_tw = str(df_simp.iloc[i, col_model_tw]).strip() if pd.notna(df_simp.iloc[i, col_model_tw]) else ""
        
        # 處理 nan 字串
        if item_tw == "nan": item_tw = ""
        if vendor_tw == "nan": vendor_tw = ""
        if model_tw == "nan": model_tw = ""
        if vendor_en == "nan": vendor_en = ""
        if model_en == "nan": model_en = ""
        if pn_en == "nan": pn_en = ""

        # --- 4. 驗證對應 (Model Check) ---
        match_status = ""
        match_class = "" # 用於 CSS 樣式
        
        check_key = model_en
        if pn_en:
            check_key += f" {pn_en}" # 組合 Model + PN
            
        # 比對邏輯: 英規 Model (或 P/N) 是否出現在簡易的廠商型號中
        if check_key and check_key != "nan":
            # 簡化比對: 移除空格與換行
            key_clean = "".join(check_key.split())
            target_clean = "".join(model_tw.split())
            
            # 檢查包含關係
            if key_clean in target_clean or model_en in model_tw:
                match_status = "✅ Match"
                match_class = "color: var(--success-color);"
            else:
                match_status = f"⚠️ Check<br><small>{check_key} vs {model_tw}</small>"
                match_class = "color: var(--error-color);"
        else:
            # 如果英規沒有 Model，則檢查是否兩邊都空，或視為 Info
            if not model_tw:
                 match_status = "-"
            else:
                 match_status = "Skipped"
        
        # 建構 HTML Row
        html_rows += f"""
        <tr>
            <td>{item_en}</td>
            <td>{item_tw}</td>
            <td>{vendor_en}</td>
            <td>{vendor_tw}</td>
            <td style="{match_class}">{match_status}</td>
        </tr>
        """
        
    return html_rows

def setup_event_listeners():
    """設定事件監聽器"""
    # 點擊上傳
    drop_area.onclick = lambda e: file_input.click()
    
    # input change 事件
    # 注意: PyScript 中 add_event_listener 與 js 互動的寫法
    # 這裡直接用 js.document 來綁定或許更穩，或透過 proxy
    # 簡化寫法：在 Python 中定義 callback，然後掛載到元素上
    
    # 這裡使用 PyScript 的方式綁定 file input change
    file_input.onchange = handle_file_select

    # 拖放效果 (UI 互動)
    def on_drag_over(e):
        e.preventDefault()
        drop_area.classList.add("drag-over")

    def on_drag_leave(e):
        e.preventDefault()
        drop_area.classList.remove("drag-over")

    def on_drop(e):
        e.preventDefault()
        drop_area.classList.remove("drag-over")
        
        js.console.log("File dropped event triggered")
        if e.dataTransfer.files and e.dataTransfer.files.length > 0:
            file_obj = e.dataTransfer.files.item(0)
            log(f"接收到拖放檔案: {file_obj.name}")
            
            # 使用 asyncio 排程非同步處理
            import asyncio
            asyncio.ensure_future(process_file_content(file_obj))
        else:
            log("拖放事件未偵測到檔案")

    # 綁定拖放視覺效果
    drop_area.ondragover = on_drag_over
    drop_area.ondragleave = on_drag_leave
    drop_area.ondrop = on_drop

    log("系統就緒，等待使用者操作...")

# 初始化
if __name__ == "__main__":
    setup_event_listeners()