#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
修復廠商郵件資料
如果您之前匯入的廠商沒有郵件資料，可以使用此腳本重新匯入
"""

import sys
import os
import pandas as pd

# 加入專案路徑
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))

from database.vendor_manager import VendorDatabaseManager

def import_vendor_emails_from_excel(excel_path):
    """
    從 Excel 匯入廠商郵件

    Args:
        excel_path: Excel 檔案路徑

    預期欄位：
        - 來源供應商編號 (必填)
        - 來源供應商名稱 (必填)
        - Mail (選填)
    """

    print("=" * 60)
    print("廠商郵件資料匯入工具")
    print("=" * 60)

    # 檢查檔案是否存在
    if not os.path.exists(excel_path):
        print(f"\n❌ 檔案不存在: {excel_path}")
        return

    print(f"\n讀取檔案: {excel_path}")

    try:
        # 讀取 Excel
        df = pd.read_excel(excel_path)
        print(f"總行數: {len(df)}")
        print(f"欄位: {list(df.columns)}")

        # 驗證欄位
        required_columns = ['來源供應商編號', '來源供應商名稱']
        missing = [col for col in required_columns if col not in df.columns]
        if missing:
            print(f"\n❌ 缺少必要欄位: {', '.join(missing)}")
            return

        if 'Mail' not in df.columns:
            print(f"\n⚠️  未找到 'Mail' 欄位，無法匯入郵件資料")
            return

        # 初始化資料庫
        db = VendorDatabaseManager()

        # 統計
        vendor_updated = 0
        email_added = 0
        email_skipped = 0
        vendor_not_found = 0

        print(f"\n開始處理...")

        # 逐行處理
        for idx, row in df.iterrows():
            vendor_code = str(row.get('來源供應商編號', '')).strip()
            vendor_name = str(row.get('來源供應商名稱', '')).strip()

            # 支援多種郵件欄位名稱（Mail, MAIL, mail, 郵件 等）
            email = ''
            for mail_col in ['Mail', 'MAIL', 'mail', 'E-mail', 'EMAIL', 'email', '郵件', 'E-Mail']:
                if mail_col in df.columns and pd.notna(row.get(mail_col)):
                    email = str(row.get(mail_col, '')).strip()
                    break

            if not vendor_code or not vendor_name:
                continue

            # 檢查廠商是否存在
            vendor = db.get_vendor_by_code(vendor_code)
            if not vendor:
                print(f"  ⚠️  廠商不存在: {vendor_code} - {vendor_name}")
                vendor_not_found += 1

                # 詢問是否要新增
                if idx == 0 or vendor_not_found <= 3:  # 只問前幾個
                    response = input(f"     是否要新增此廠商? (y/n): ").lower()
                    if response == 'y':
                        new_vendor = db.add_vendor(vendor_code, vendor_name)
                        if new_vendor:
                            print(f"     ✅ 已新增廠商")
                            vendor_updated += 1
                        else:
                            print(f"     ❌ 新增失敗")
                            continue
                    else:
                        continue
                else:
                    continue
            else:
                vendor_updated += 1

            # 如果有郵件地址，新增到廠商郵件表
            if email and '@' in email:
                # 可能有多個郵件（用逗號或分號分隔）
                emails = email.replace(';', ',').split(',')
                for single_email in emails:
                    single_email = single_email.strip()
                    if single_email and '@' in single_email:
                        result = db.add_vendor_email(
                            vendor_code=vendor_code,
                            email=single_email,
                            email_type='primary'
                        )
                        if result:
                            print(f"  ✅ {vendor_code} | 新增郵件: {single_email}")
                            email_added += 1
                        else:
                            print(f"  ⚠️  {vendor_code} | 郵件已存在: {single_email}")
                            email_skipped += 1

        # 顯示結果
        print("\n" + "=" * 60)
        print("匯入完成")
        print("=" * 60)
        print(f"處理廠商數: {vendor_updated}")
        print(f"廠商不存在: {vendor_not_found}")
        print(f"新增郵件數: {email_added}")
        print(f"跳過郵件數: {email_skipped} (重複)")
        print("=" * 60)

    except Exception as e:
        print(f"\n❌ 處理失敗: {e}")
        import traceback
        traceback.print_exc()


def show_current_status():
    """顯示目前廠商郵件狀況"""

    print("\n" + "=" * 60)
    print("目前廠商郵件狀況")
    print("=" * 60)

    from database.vendor_models import VendorList, VendorEmail

    db = VendorDatabaseManager()
    session = db.get_session()

    try:
        vendors = session.query(VendorList).all()
        total_emails = session.query(VendorEmail).count()

        print(f"\n總廠商數: {len(vendors)}")
        print(f"總郵件數: {total_emails}")

        vendors_with_email = 0
        vendors_without_email = 0

        for vendor in vendors:
            emails = session.query(VendorEmail).filter_by(vendor_id=vendor.id).all()
            if emails:
                vendors_with_email += 1
            else:
                vendors_without_email += 1

        print(f"有郵件的廠商: {vendors_with_email}")
        print(f"無郵件的廠商: {vendors_without_email}")

        if vendors_without_email > 0:
            print(f"\n⚠️  有 {vendors_without_email} 個廠商沒有設定郵件地址")
            print("   建議使用此工具匯入郵件資料")

    finally:
        session.close()


if __name__ == '__main__':
    # 顯示目前狀況
    show_current_status()

    # 詢問是否要匯入
    print("\n" + "=" * 60)
    excel_path = input("請輸入 Excel 檔案路徑 (或按 Enter 跳過): ").strip()

    if excel_path:
        import_vendor_emails_from_excel(excel_path)

        # 再次顯示狀況
        show_current_status()
    else:
        print("\n已取消")