#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
廠商缺貨通知系統 - 路由模組
提供廠商缺貨管理的所有網頁路由與 API
"""

from flask import Blueprint, render_template, jsonify, request, send_file
from jinja2 import ChoiceLoader, FileSystemLoader, PrefixLoader
from werkzeug.utils import secure_filename
from datetime import datetime, date
from sqlalchemy import or_, func, desc
import pandas as pd
import os
import io
import json

# 導入資料庫管理器
from database.vendor_manager import VendorDatabaseManager
from database.vendor_models import VendorStockout, VendorList, VendorEmail, EmailSendLog
from services.logger_manager import SystemLogger
from services.vendor_stockout_query_service import (
    get_stockout_api_list_payload,
    get_stockout_batches_payload,
    get_vendor_detail_payload,
    get_vendor_dashboard_stats,
    get_vendor_list_payload,
    get_vendor_stockout_list_context,
)

# 初始化日誌
sys_log = SystemLogger("VendorRoutes").get_logger()

# 建立 Blueprint
# V-Fix: 指定正確的 template_folder，解決 TemplateNotFound 錯誤
_base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
_template_folder = os.path.join(_base_dir, 'web', 'templates')
vendor_bp = Blueprint('vendor', __name__, url_prefix='/vendor-stockout', template_folder=_template_folder)
_VENDOR_TEMPLATE_PREFIX = '__vendor_web__'


@vendor_bp.record_once
def _register_vendor_template_loader(setup_state):
    app = setup_state.app
    app.jinja_loader = ChoiceLoader([
        PrefixLoader({_VENDOR_TEMPLATE_PREFIX: FileSystemLoader(_template_folder)}),
        app.jinja_loader,
    ])


def _render_vendor_template(template_name, **context):
    return render_template(f'{_VENDOR_TEMPLATE_PREFIX}/{template_name}', **context)

# 初始化資料庫管理器
vendor_db = VendorDatabaseManager()

# ==========================================
# 主要頁面路由
# ==========================================


@vendor_bp.route('/')
def index():
    """廠商缺貨系統主頁"""
    sys_log.info("[VendorStockout] 進入廠商缺貨系統主頁")
    return render_template(
        'vendor_stockout_index_v2.html',
        active_page='vendor_stockout',
        stats=get_vendor_dashboard_stats(vendor_db)
    )


@vendor_bp.route('/import')
def import_page():
    """Excel 匯入頁面"""
    sys_log.info("[VendorStockout] 進入匯入頁面")
    return render_template(
        'vendor_stockout_import_v2.html',
        active_page='vendor_stockout'
    )


@vendor_bp.route('/list')
def list_page():
    """缺貨清單頁面"""
    sys_log.info("[VendorStockout] 進入缺貨清單頁面")
    return render_template(
        'vendor_stockout_list_v2.html',
        active_page='vendor_stockout',
        **get_vendor_stockout_list_context(
            vendor_db,
            page=request.args.get('page', 1, type=int),
            page_size=request.args.get('page_size', 30, type=int),
            status_filter=request.args.get('status'),
            batch_filter=request.args.get('batch'),
            vendor_keyword=request.args.get('q'),
            sort_by=request.args.get('sort'),
        )
    )


@vendor_bp.route('/vendor-management')
def vendor_management_page():
    """廠商管理頁面"""
    sys_log.info("[VendorStockout] 進入廠商管理頁面")
    return render_template('vendor_stockout_vendor_management_v2.html', active_page='vendor_stockout')


@vendor_bp.route('/send-email')
def send_email_page():
    """郵件發送頁面"""
    sys_log.info("[VendorStockout] 進入郵件發送頁面")
    return render_template('vendor_stockout_send_email_v2.html', active_page='vendor_stockout')


@vendor_bp.route('/history')
def history_page():
    """發送歷史頁面"""
    sys_log.info("[VendorStockout] 進入發送歷史頁面")
    return render_template('vendor_stockout_history_v2.html', active_page='vendor_stockout')


# ==========================================
# API - Excel 匯入功能
# ==========================================

@vendor_bp.route('/api/import/excel', methods=['POST'])
def api_import_excel():
    """
    處理 Excel 匯入

    預期檔案格式（實際欄位名稱）:
    - 當前日期、處別、科別、PM姓名、區ID、區名稱、商品ID、商品名稱
    - 單品/組合商品、借採轉、來源供應商編號、來源供應商名稱
    - 商品可賣量、缺貨日期、缺貨天數、缺貨商品前30天業績、最近30天銷售量、庫存水位
    """
    try:
        # 檢查是否有檔案
        if 'file' not in request.files:
            return jsonify({'success': False, 'message': '未上傳檔案'}), 400

        file = request.files['file']
        if file.filename == '':
            return jsonify({'success': False, 'message': '未選擇檔案'}), 400

        # 檢查副檔名
        if not file.filename.endswith(('.xlsx', '.xls')):
            return jsonify({'success': False, 'message': '僅支援 Excel 檔案 (.xlsx, .xls)'}), 400

        sys_log.info(f"[Import] 開始匯入 Excel | 檔名: {file.filename}")

        # 讀取 Excel（讓 pandas 自動處理數據類型，包括日期）
        df = pd.read_excel(file)

        # 驗證欄位（使用實際的欄位名稱）
        required_columns = ['來源供應商編號', '來源供應商名稱', '商品ID', '商品名稱']
        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            return jsonify({
                'success': False,
                'message': f'Excel 缺少必要欄位: {", ".join(missing_columns)}'
            }), 400

        # 產生批次編號
        batch_id = datetime.now().strftime('%Y%m%d_%H%M%S')

        # 檢查是否有當前日期欄位
        has_date_column = '當前日期' in df.columns
        if has_date_column:
            sys_log.info(f"✅ Excel 包含「當前日期」欄位，將使用每行的日期")
        else:
            sys_log.info(f"ℹ️  Excel 無「當前日期」欄位，將使用今天日期")

        # 轉換為記錄清單
        records_list = []
        for idx, row in df.iterrows():
            # 每一行分別讀取自己的「當前日期」
            if has_date_column:
                try:
                    date_value = row.get('當前日期')

                    # 檢查是否為有效值
                    if pd.notna(date_value):
                        # 檢查是否為數字類型（包括 numpy 類型）
                        import numpy as np
                        if isinstance(date_value, (int, float, np.integer, np.floating)):
                            # Excel 日期序列號（從 1899-12-30 開始計算的天數）
                            parsed_date = pd.to_datetime(date_value, unit='D', origin='1899-12-30', errors='coerce')
                        else:
                            # 字串或 datetime 對象
                            parsed_date = pd.to_datetime(date_value, errors='coerce')

                        if pd.notna(parsed_date):
                            row_import_date = parsed_date.date()
                            if idx == 0:  # 只記錄第一行的日期
                                sys_log.info(f"✅ 第 1 行日期轉換成功: {date_value} (類型: {type(date_value).__name__}) → {row_import_date}")
                        else:
                            if idx == 0:
                                sys_log.warning(f"⚠️  第 {idx+1} 行日期無法轉換: '{date_value}'（類型: {type(date_value)}），使用今天")
                            row_import_date = date.today()
                    else:
                        if idx == 0:  # 只警告第一行
                            sys_log.warning(f"⚠️  第 {idx+1} 行日期為空或無效，使用今天")
                        row_import_date = date.today()
                except Exception as e:
                    if idx == 0:
                        sys_log.error(f"❌ 第 {idx+1} 行日期轉換失敗: {e}，使用今天")
                    row_import_date = date.today()
            else:
                row_import_date = date.today()

            # 轉換「缺貨日期」（可能是Excel日期序列號）
            stockout_date_value = row.get('缺貨日期')
            stockout_date_obj = None
            if pd.notna(stockout_date_value):
                import numpy as np
                if isinstance(stockout_date_value, (int, float, np.integer, np.floating)):
                    # Excel 日期序列號
                    stockout_parsed = pd.to_datetime(stockout_date_value, unit='D', origin='1899-12-30', errors='coerce')
                    if pd.notna(stockout_parsed):
                        stockout_date_obj = stockout_parsed.date()
                else:
                    # 已經是日期或字串
                    stockout_parsed = pd.to_datetime(stockout_date_value, errors='coerce')
                    if pd.notna(stockout_parsed):
                        stockout_date_obj = stockout_parsed.date()

            # 輔助函數：將 pandas/numpy 類型轉換為 Python 原生類型
            def safe_int(value):
                """安全轉換為整數，處理 NaN"""
                if pd.notna(value):
                    return int(value)
                return None

            def safe_float(value):
                """安全轉換為浮點數，處理 NaN"""
                if pd.notna(value):
                    return float(value)
                return None

            def safe_str(value):
                """安全轉換為字串，處理 NaN"""
                if pd.notna(value):
                    return str(value).strip()
                return None

            record = {
                'import_date': row_import_date,
                'department': safe_str(row.get('處別')),
                'section': safe_str(row.get('科別')),
                'pm_name': safe_str(row.get('PM姓名')),
                'zone_id': safe_str(row.get('區ID')),
                'zone_name': safe_str(row.get('區名稱')),
                'product_code': safe_str(row.get('商品ID')) or '',
                'product_name': safe_str(row.get('商品名稱')) or '',
                'product_spec': safe_str(row.get('單品/組合商品')),
                'borrow_transfer': safe_str(row.get('借採轉')),
                'sale_price': None,
                'cost_price': None,
                'vendor_code': safe_str(row.get('來源供應商編號')) or '',
                'vendor_name': safe_str(row.get('來源供應商名稱')) or '',
                'current_stock': safe_int(row.get('商品可賣量')),
                'stockout_date': stockout_date_obj,
                'stockout_days': safe_int(row.get('缺貨天數')),
                'monthly_sales_amount': safe_float(row.get('缺貨商品前30天業績')),
                'monthly_sales_qty': safe_int(row.get('最近30天銷售量')),
                'daily_avg_sales': None,
                'safe_stock_days': safe_int(row.get('庫存水位')),
                'notes': None
            }

            records_list.append(record)

        # 批次寫入資料庫
        success_count, failed_count, duplicate_count = vendor_db.add_stockout_records(
            records_list, batch_id
        )

        sys_log.info(f"[Import] 匯入完成 | 批次: {batch_id} | 成功: {success_count} | "
                    f"失敗: {failed_count} | 重複: {duplicate_count}")

        return jsonify({
            'success': True,
            'message': '匯入完成',
            'data': {
                'batch_id': batch_id,
                'success_count': success_count,
                'failed_count': failed_count,
                'duplicate_count': duplicate_count,
                'total_count': len(records_list)
            }
        })

    except Exception as e:
        sys_log.error(f"[Import] 匯入失敗 | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'匯入失敗: {str(e)}'}), 500


@vendor_bp.route('/api/import/template')
def api_import_template():
    """下載 Excel 匯入範本（使用實際欄位名稱）"""
    try:
        template_columns = [
            '當前日期', '處別', '科別', 'PM姓名', '區ID', '區名稱', '商品ID', '商品名稱',
            '單品/組合商品', '借採轉', '來源供應商編號', '來源供應商名稱', '商品可賣量',
            '缺貨日期', '缺貨天數', '缺貨商品前30天業績', '最近30天銷售量', '庫存水位'
        ]
        df = pd.DataFrame(columns=template_columns)

        # 輸出到記憶體
        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name='缺貨清單')

        output.seek(0)

        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=f'廠商缺貨匯入範本.xlsx'
        )

    except Exception as e:
        sys_log.error(f"[Template] 範本下載失敗 | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'範本下載失敗: {str(e)}'}), 500


# ==========================================
# API - 缺貨清單查詢
# ==========================================

@vendor_bp.route('/api/stockout/list', methods=['GET'])
def api_get_stockout_list():
    """
    查詢缺貨清單 (支援分頁、篩選、排序)

    Query Parameters:
    - page: 頁碼 (預設 1)
    - page_size: 每頁筆數 (預設 50)
    - batch_number: 批次編號
    - vendor: 廠商代碼或名稱
    - status: 發送狀態 (pending/sent/failed)
    - sort_by: 排序方式 (created_at_desc/created_at_asc/vendor_code_asc/stockout_days_desc)
    """
    try:
        return jsonify({
            'success': True,
            'data': get_stockout_api_list_payload(
                vendor_db,
                page=request.args.get('page', 1, type=int),
                page_size=request.args.get('page_size', 50, type=int),
                batch_number=request.args.get('batch_number'),
                vendor_filter=request.args.get('vendor'),
                status_filter=request.args.get('status'),
                sort_by=request.args.get('sort_by', 'created_at_desc')
            )
        })

    except Exception as e:
        sys_log.error(f"[API] 查詢缺貨清單失敗 | 錯誤: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({'success': False, 'message': f'查詢失敗: {str(e)}'}), 500


@vendor_bp.route('/api/stockout/batches', methods=['GET'])
def api_get_stockout_batches():
    """取得批次清單"""
    try:
        return jsonify({
            'success': True,
            'data': get_stockout_batches_payload(vendor_db)
        })

    except Exception as e:
        sys_log.error(f"[API] 取得批次清單失敗 | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'查詢失敗: {str(e)}'}), 500


@vendor_bp.route('/api/stockout/<int:stockout_id>', methods=['PUT'])
def api_update_stockout(stockout_id):
    """更新單筆缺貨記錄"""
    try:
        session = vendor_db.get_session()
        data = request.get_json()

        # 查詢記錄
        record = session.query(VendorStockout).filter_by(id=stockout_id).first()
        if not record:
            return jsonify({'success': False, 'message': '記錄不存在'}), 404

        # 更新欄位
        if 'product_name' in data:
            record.product_name = data['product_name']
        if 'stockout_days' in data:
            record.safe_stock_days = data['stockout_days']
        if 'daily_avg_sales' in data:
            record.daily_avg_sales = data['daily_avg_sales']
        if 'current_stock' in data:
            record.current_stock = data['current_stock']
        if 'notes' in data:
            record.notes = data['notes']

        record.updated_at = datetime.now()

        session.commit()
        sys_log.info(f"[API] 更新缺貨記錄 | ID: {stockout_id}")

        return jsonify({
            'success': True,
            'message': '更新成功'
        })

    except Exception as e:
        if 'session' in locals():
            session.rollback()
        sys_log.error(f"[API] 更新缺貨記錄失敗 | ID: {stockout_id} | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'更新失敗: {str(e)}'}), 500
    finally:
        if 'session' in locals():
            session.close()


@vendor_bp.route('/api/stockout/<int:stockout_id>', methods=['DELETE'])
def api_delete_stockout(stockout_id):
    """刪除單筆缺貨記錄"""
    try:
        session = vendor_db.get_session()

        # 查詢記錄
        record = session.query(VendorStockout).filter_by(id=stockout_id).first()
        if not record:
            return jsonify({'success': False, 'message': '記錄不存在'}), 404

        session.delete(record)
        session.commit()
        sys_log.info(f"[API] 刪除缺貨記錄 | ID: {stockout_id}")

        return jsonify({
            'success': True,
            'message': '刪除成功'
        })

    except Exception as e:
        if 'session' in locals():
            session.rollback()
        sys_log.error(f"[API] 刪除缺貨記錄失敗 | ID: {stockout_id} | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'刪除失敗: {str(e)}'}), 500
    finally:
        if 'session' in locals():
            session.close()


@vendor_bp.route('/api/stockout/batch/delete', methods=['POST'])
def api_batch_delete_stockout():
    """批次刪除缺貨記錄"""
    try:
        session = vendor_db.get_session()
        data = request.get_json()
        ids = data.get('ids', [])

        if not ids:
            return jsonify({'success': False, 'message': '未選擇任何記錄'}), 400

        # 批次刪除
        deleted_count = session.query(VendorStockout)\
            .filter(VendorStockout.id.in_(ids))\
            .delete(synchronize_session=False)

        session.commit()
        sys_log.info(f"[API] 批次刪除缺貨記錄 | 數量: {deleted_count}")

        return jsonify({
            'success': True,
            'message': f'成功刪除 {deleted_count} 筆記錄',
            'data': {'deleted_count': deleted_count}
        })

    except Exception as e:
        if 'session' in locals():
            session.rollback()
        sys_log.error(f"[API] 批次刪除失敗 | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'刪除失敗: {str(e)}'}), 500
    finally:
        if 'session' in locals():
            session.close()


@vendor_bp.route('/api/stockout/batch/mark-sent', methods=['POST'])
def api_batch_mark_sent():
    """批次標記為已發送"""
    try:
        session = vendor_db.get_session()
        data = request.get_json()
        ids = data.get('ids', [])

        if not ids:
            return jsonify({'success': False, 'message': '未選擇任何記錄'}), 400

        # 批次更新
        updated_count = session.query(VendorStockout)\
            .filter(VendorStockout.id.in_(ids))\
            .update({
                'status': 'sent',
                'updated_at': datetime.now()
            }, synchronize_session=False)

        session.commit()
        sys_log.info(f"[API] 批次標記已發送 | 數量: {updated_count}")

        return jsonify({
            'success': True,
            'message': f'成功更新 {updated_count} 筆記錄',
            'data': {'updated_count': updated_count}
        })

    except Exception as e:
        if 'session' in locals():
            session.rollback()
        sys_log.error(f"[API] 批次標記失敗 | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'更新失敗: {str(e)}'}), 500
    finally:
        if 'session' in locals():
            session.close()


# ==========================================
# API - 廠商管理
# ==========================================

@vendor_bp.route('/api/vendor/list', methods=['GET'])
def api_get_vendor_list():
    """查詢廠商清單（支援分頁與搜尋）"""
    try:
        return jsonify({
            'success': True,
            'data': get_vendor_list_payload(
                vendor_db,
                page=request.args.get('page', 1, type=int),
                page_size=request.args.get('page_size', 20, type=int),
                search=request.args.get('search', ''),
                active_only=request.args.get('active_only', 'true').lower() == 'true'
            )
        })

    except Exception as e:
        sys_log.error(f"[API] 查詢廠商清單失敗 | 錯誤: {e}")
        import traceback
        traceback.print_exc()
        return jsonify({'success': False, 'message': f'查詢失敗: {str(e)}'}), 500


@vendor_bp.route('/api/vendor', methods=['POST'])
def api_add_vendor():
    """新增廠商（支援批量新增郵件）"""
    try:
        session = vendor_db.get_session()
        data = request.get_json()

        vendor_code = data.get('vendor_code', '').strip()
        vendor_name = data.get('vendor_name', '').strip()
        emails = data.get('emails', [])

        if not vendor_code or not vendor_name:
            return jsonify({'success': False, 'message': '廠商代碼與名稱不可為空'}), 400

        # 檢查廠商是否已存在
        existing = session.query(VendorList).filter_by(vendor_code=vendor_code).first()
        if existing:
            return jsonify({'success': False, 'message': '廠商代碼已存在'}), 400

        # 新增廠商
        vendor = VendorList(
            vendor_code=vendor_code,
            vendor_name=vendor_name,
            is_active=True
        )
        session.add(vendor)
        session.flush()

        # 新增郵件
        email_count = 0
        for email in emails:
            email = email.strip()
            if email and '@' in email:
                vendor_email = VendorEmail(
                    vendor_id=vendor.id,  # 修正：使用 vendor_id 而非 vendor_code
                    email=email,
                    email_type='primary',
                    is_active=True
                )
                session.add(vendor_email)
                email_count += 1

        session.commit()
        sys_log.info(f"[API] 新增廠商 | 代碼: {vendor_code} | 郵件數: {email_count}")

        return jsonify({
            'success': True,
            'message': f'新增廠商成功（含 {email_count} 個郵件）',
            'data': {
                'id': vendor.id,
                'vendor_code': vendor.vendor_code,
                'vendor_name': vendor.vendor_name,
                'email_count': email_count
            }
        })

    except Exception as e:
        if 'session' in locals():
            session.rollback()
        sys_log.error(f"[API] 新增廠商失敗 | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'新增失敗: {str(e)}'}), 500
    finally:
        if 'session' in locals():
            session.close()


@vendor_bp.route('/api/vendor/<vendor_code>', methods=['GET'])
def api_get_vendor(vendor_code):
    """取得單一廠商詳細資訊"""
    try:
        payload = get_vendor_detail_payload(vendor_db, vendor_code)
        if not payload:
            return jsonify({'success': False, 'message': '廠商不存在'}), 404

        return jsonify({
            'success': True,
            'data': payload
        })

    except Exception as e:
        sys_log.error(f"[API] 查詢廠商失敗 | 代碼: {vendor_code} | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'查詢失敗: {str(e)}'}), 500


@vendor_bp.route('/api/vendor/<vendor_code>', methods=['PUT'])
def api_update_vendor(vendor_code):
    """更新廠商資訊（支援批量更新郵件）"""
    try:
        session = vendor_db.get_session()
        data = request.get_json()

        vendor = session.query(VendorList).filter_by(vendor_code=vendor_code).first()
        if not vendor:
            return jsonify({'success': False, 'message': '廠商不存在'}), 404

        # 更新廠商基本資訊
        vendor_name = data.get('vendor_name')
        is_active = data.get('is_active')

        if vendor_name:
            vendor.vendor_name = vendor_name
        if is_active is not None:
            vendor.is_active = is_active

        vendor.updated_at = datetime.now()

        # 如果提供了郵件清單，則更新郵件
        if 'emails' in data:
            emails = data.get('emails', [])

            # 刪除現有郵件
            session.query(VendorEmail).filter(VendorEmail.vendor_id == vendor.id).delete()

            # 新增新的郵件
            email_count = 0
            for email in emails:
                email = email.strip()
                if email and '@' in email:
                    vendor_email = VendorEmail(
                        vendor_id=vendor.id,
                        email=email,
                        email_type='primary',
                        is_active=True
                    )
                    session.add(vendor_email)
                    email_count += 1

            session.commit()
            sys_log.info(f"[API] 更新廠商 | 代碼: {vendor_code} | 郵件數: {email_count}")

            return jsonify({
                'success': True,
                'message': f'更新成功（含 {email_count} 個郵件）'
            })
        else:
            session.commit()
            sys_log.info(f"[API] 更新廠商 | 代碼: {vendor_code}")
            return jsonify({'success': True, 'message': '更新成功'})

    except Exception as e:
        if 'session' in locals():
            session.rollback()
        sys_log.error(f"[API] 更新廠商失敗 | 代碼: {vendor_code} | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'更新失敗: {str(e)}'}), 500
    finally:
        if 'session' in locals():
            session.close()


@vendor_bp.route('/api/vendor/<vendor_code>', methods=['DELETE'])
def api_delete_vendor(vendor_code):
    """刪除廠商（同時刪除相關郵件）"""
    try:
        session = vendor_db.get_session()
        vendor = session.query(VendorList).filter_by(vendor_code=vendor_code).first()

        if not vendor:
            return jsonify({'success': False, 'message': '廠商不存在'}), 404

        # 刪除相關郵件
        session.query(VendorEmail).filter(VendorEmail.vendor_id == vendor.id).delete()

        # 刪除廠商
        session.delete(vendor)
        session.commit()

        sys_log.info(f"[API] 刪除廠商 | 代碼: {vendor_code}")

        return jsonify({'success': True, 'message': '刪除成功'})

    except Exception as e:
        if 'session' in locals():
            session.rollback()
        sys_log.error(f"[API] 刪除廠商失敗 | 代碼: {vendor_code} | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'刪除失敗: {str(e)}'}), 500
    finally:
        if 'session' in locals():
            session.close()


@vendor_bp.route('/api/vendor/<vendor_code>/emails', methods=['GET'])
def api_get_vendor_emails(vendor_code):
    """取得廠商的郵件清單"""
    try:
        session = vendor_db.get_session()

        # 先取得廠商
        vendor = session.query(VendorList).filter_by(vendor_code=vendor_code).first()
        if not vendor:
            return jsonify({'success': False, 'message': '廠商不存在'}), 404

        emails = session.query(VendorEmail).filter(
            VendorEmail.vendor_id == vendor.id,
            VendorEmail.is_active == True
        ).order_by(VendorEmail.id.asc()).all()

        emails_data = []
        for email in emails:
            emails_data.append({
                'id': email.id,
                'email': email.email,
                'email_type': email.email_type,
                'is_active': email.is_active,
                'created_at': email.created_at.isoformat() if email.created_at else None
            })

        return jsonify({
            'success': True,
            'data': emails_data
        })

    except Exception as e:
        sys_log.error(f"[API] 查詢廠商郵件失敗 | 代碼: {vendor_code} | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'查詢失敗: {str(e)}'}), 500
    finally:
        if 'session' in locals():
            session.close()


@vendor_bp.route('/api/vendor/<vendor_code>/emails', methods=['POST'])
def api_add_vendor_email(vendor_code):
    """新增廠商郵件"""
    try:
        session = vendor_db.get_session()
        data = request.get_json()
        email = data.get('email', '').strip()
        email_type = data.get('email_type', 'primary')

        if not email or '@' not in email:
            return jsonify({'success': False, 'message': '請輸入有效的郵件地址'}), 400

        # 檢查廠商是否存在
        vendor = session.query(VendorList).filter_by(vendor_code=vendor_code).first()
        if not vendor:
            return jsonify({'success': False, 'message': '廠商不存在'}), 404

        # 檢查郵件是否已存在
        existing = session.query(VendorEmail).filter_by(
            vendor_id=vendor.id,
            email=email
        ).first()

        if existing:
            if existing.is_active:
                return jsonify({'success': False, 'message': '該郵件地址已存在'}), 400
            else:
                # 重新啟用已停用的郵件
                existing.is_active = True
                existing.updated_at = datetime.now()
                session.commit()
                sys_log.info(f"[API] 重新啟用廠商郵件 | 代碼: {vendor_code} | 郵件: {email}")
                return jsonify({'success': True, 'message': '郵件地址已重新啟用'})

        # 新增郵件
        vendor_email = VendorEmail(
            vendor_id=vendor.id,
            email=email,
            email_type=email_type,
            is_active=True
        )
        session.add(vendor_email)
        session.commit()

        sys_log.info(f"[API] 新增廠商郵件 | 代碼: {vendor_code} | 郵件: {email}")

        return jsonify({
            'success': True,
            'message': '新增成功',
            'data': {
                'id': vendor_email.id,
                'email': vendor_email.email,
                'email_type': vendor_email.email_type
            }
        })

    except Exception as e:
        if 'session' in locals():
            session.rollback()
        sys_log.error(f"[API] 新增廠商郵件失敗 | 代碼: {vendor_code} | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'新增失敗: {str(e)}'}), 500
    finally:
        if 'session' in locals():
            session.close()


@vendor_bp.route('/api/vendor/email/<int:email_id>', methods=['DELETE'])
def api_delete_vendor_email(email_id):
    """刪除廠商郵件"""
    try:
        session = vendor_db.get_session()
        email = session.query(VendorEmail).filter_by(id=email_id).first()

        if not email:
            return jsonify({'success': False, 'message': '郵件不存在'}), 404

        session.delete(email)
        session.commit()

        sys_log.info(f"[API] 刪除廠商郵件 | ID: {email_id} | 郵件: {email.email}")

        return jsonify({'success': True, 'message': '刪除成功'})

    except Exception as e:
        if 'session' in locals():
            session.rollback()
        sys_log.error(f"[API] 刪除廠商郵件失敗 | ID: {email_id} | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'刪除失敗: {str(e)}'}), 500
    finally:
        if 'session' in locals():
            session.close()


@vendor_bp.route('/api/vendor/import/excel', methods=['POST'])
def api_import_vendor_excel():
    """
    廠商清單 Excel 匯入

    預期欄位：來源供應商編號、來源供應商名稱、Mail
    """
    try:
        # 檢查是否有檔案
        if 'file' not in request.files:
            return jsonify({'success': False, 'message': '未上傳檔案'}), 400

        file = request.files['file']
        if file.filename == '':
            return jsonify({'success': False, 'message': '未選擇檔案'}), 400

        # 檢查副檔名
        if not file.filename.endswith(('.xlsx', '.xls')):
            return jsonify({'success': False, 'message': '僅支援 Excel 檔案 (.xlsx, .xls)'}), 400

        sys_log.info(f"[VendorImport] 開始匯入廠商 Excel | 檔名: {file.filename}")

        # 讀取 Excel
        df = pd.read_excel(file)

        # 驗證欄位
        required_columns = ['來源供應商編號', '來源供應商名稱']
        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            return jsonify({
                'success': False,
                'message': f'Excel 缺少必要欄位: {", ".join(missing_columns)}'
            }), 400

        success_count = 0
        failed_count = 0
        updated_count = 0
        email_count = 0
        email_skipped_count = 0  # 新增：跳過的重複郵件數

        # 逐行處理
        for _, 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:
                failed_count += 1
                continue

            # 檢查廠商是否存在
            existing_vendor = vendor_db.get_vendor_by_code(vendor_code)

            if existing_vendor:
                # 更新現有廠商
                vendor_db.update_vendor(vendor_code, vendor_name=vendor_name)
                updated_count += 1
            else:
                # 新增廠商
                vendor = vendor_db.add_vendor(vendor_code, vendor_name)
                if vendor:
                    success_count += 1
                else:
                    failed_count += 1
                    continue

            # 如果有郵件地址，新增到廠商郵件表
            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 = vendor_db.add_vendor_email(
                            vendor_code=vendor_code,
                            email=single_email,
                            email_type='primary'
                        )
                        if result:
                            email_count += 1
                        else:
                            email_skipped_count += 1

        sys_log.info(f"[VendorImport] 匯入完成 | 新增: {success_count} | 更新: {updated_count} | "
                    f"失敗: {failed_count} | 郵件: {email_count} | 重複郵件: {email_skipped_count}")

        return jsonify({
            'success': True,
            'message': '匯入完成',
            'data': {
                'success_count': success_count,
                'updated_count': updated_count,
                'failed_count': failed_count,
                'email_count': email_count,
                'email_skipped_count': email_skipped_count,
                'total_count': len(df)
            }
        })

    except Exception as e:
        sys_log.error(f"[VendorImport] 匯入失敗 | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'匯入失敗: {str(e)}'}), 500


@vendor_bp.route('/api/vendor/import/template')
def api_vendor_import_template():
    """下載廠商清單 Excel 匯入範本"""
    try:
        # 建立範本 DataFrame
        template_data = {
            '來源供應商編號': ['V001', 'V002', 'V003'],
            '來源供應商名稱': ['範例供應商A股份有限公司', '範例供應商B有限公司', '範例供應商C企業社'],
            'Mail': ['vendorA@example.com', 'vendorB@example.com;vendorB2@example.com', 'vendorC@example.com']
        }
        df = pd.DataFrame(template_data)

        # 輸出到記憶體
        output = io.BytesIO()
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name='供應商清單')

        output.seek(0)

        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=f'廠商清單匯入範本.xlsx'
        )

    except Exception as e:
        sys_log.error(f"[VendorTemplate] 範本下載失敗 | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'範本下載失敗: {str(e)}'}), 500

# ==========================================
# API - 郵件發送
# ==========================================

@vendor_bp.route('/api/email/send/grouped', methods=['POST'])
def api_send_grouped_email():
    """
    按廠商分組發送郵件

    Request Body:
    {
        "stockout_ids": [1, 2, 3, ...]  # 缺貨記錄 ID 清單
    }

    Returns:
        成功/失敗的廠商統計
    """
    try:
        from services.vendor_email_service import VendorEmailService

        data = request.get_json()
        stockout_ids = data.get('stockout_ids', [])

        if not stockout_ids or len(stockout_ids) == 0:
            return jsonify({'success': False, 'message': '未選擇缺貨記錄'}), 400

        sys_log.info(f"[EmailSend] 開始按廠商分組發送 | 記錄數: {len(stockout_ids)}")

        # 查詢缺貨記錄
        session = vendor_db.get_session()
        stockout_records = session.query(VendorStockout).filter(
            VendorStockout.id.in_(stockout_ids)
        ).all()

        if not stockout_records:
            return jsonify({'success': False, 'message': '找不到缺貨記錄'}), 404

        # 按廠商分組
        vendor_groups = {}
        for record in stockout_records:
            vendor_code = record.vendor_code
            if vendor_code not in vendor_groups:
                vendor_groups[vendor_code] = {
                    'vendor_name': record.vendor_name,
                    'items': []
                }
            vendor_groups[vendor_code]['items'].append({
                'id': record.id,
                'batch_id': record.batch_id,
                'import_date': record.import_date.strftime('%Y-%m-%d') if record.import_date else '',
                'department': record.department,
                'section': record.section,
                'pm_name': record.pm_name,
                'zone_id': record.zone_id or '',
                'zone_name': record.zone_name or '',
                'product_code': record.product_code,
                'product_name': record.product_name,
                'product_spec': record.product_spec,
                'borrow_transfer': record.borrow_transfer or '',
                'vendor_code': record.vendor_code,
                'vendor_name': record.vendor_name,
                'monthly_sales_qty': record.monthly_sales_qty,
                'current_stock': record.current_stock,
                'stockout_date': record.stockout_date.strftime('%Y-%m-%d') if record.stockout_date else '',
                'stockout_days': record.stockout_days,
                'monthly_sales_amount': float(record.monthly_sales_amount) if record.monthly_sales_amount else 0,
                'daily_avg_sales': float(record.daily_avg_sales) if record.daily_avg_sales else 0,
                'safe_stock_days': record.safe_stock_days,
                'status': record.status,
                'notes': record.notes
            })

        # 初始化郵件服務
        email_service = VendorEmailService()

        # 發送結果統計
        success_vendors = []
        failed_vendors = []
        batch_id = datetime.now().strftime('%Y%m%d_%H%M%S')

        # 逐廠商發送
        for vendor_code, vendor_data in vendor_groups.items():
            # 取得廠商郵件
            vendor = session.query(VendorList).filter_by(vendor_code=vendor_code).first()

            if not vendor:
                sys_log.warning(f"[EmailSend] 廠商不存在 | 代碼: {vendor_code}")
                failed_vendors.append({
                    'vendor_code': vendor_code,
                    'vendor_name': vendor_data['vendor_name'],
                    'reason': '廠商資料不存在'
                })
                continue

            # 取得郵件清單
            emails = session.query(VendorEmail).filter(
                VendorEmail.vendor_id == vendor.id,
                VendorEmail.is_active == True
            ).all()

            vendor_emails = [e.email for e in emails]

            if not vendor_emails:
                sys_log.warning(f"[EmailSend] 廠商無郵件 | 代碼: {vendor_code}")
                failed_vendors.append({
                    'vendor_code': vendor_code,
                    'vendor_name': vendor_data['vendor_name'],
                    'reason': '未設定郵件地址'
                })
                continue

            # 發送郵件
            result = email_service.send_vendor_grouped_email(
                vendor_code=vendor_code,
                vendor_name=vendor_data['vendor_name'],
                vendor_emails=vendor_emails,
                stockout_items=vendor_data['items'],
                batch_id=batch_id
            )

            if result['success']:
                success_vendors.append({
                    'vendor_code': vendor_code,
                    'vendor_name': vendor_data['vendor_name'],
                    'item_count': len(vendor_data['items'])
                })

                # 更新缺貨記錄狀態
                for item in vendor_data['items']:
                    vendor_db.update_stockout_status(
                        stockout_id=item['id'],
                        status='sent',
                        sent_date=datetime.now()
                    )
            else:
                failed_vendors.append({
                    'vendor_code': vendor_code,
                    'vendor_name': vendor_data['vendor_name'],
                    'reason': result['message']
                })

        session.close()

        sys_log.info(f"[EmailSend] 分組發送完成 | 成功: {len(success_vendors)} | 失敗: {len(failed_vendors)}")

        # V-New: 發送 Telegram 和 Line 通知
        try:
            from services.notification_manager import NotificationManager
            from datetime import datetime

            now_str = datetime.now().strftime('%Y-%m-%d %H:%M')
            total_items = sum(v['item_count'] for v in success_vendors)

            message = (
                f"📧 廠商缺貨通知郵件發送報告 ({now_str})\n"
                f"{'='*30}\n"
                f"✅ 發送狀態：完成\n"
                f"📊 總計：\n"
                f"  - 成功發送：{len(success_vendors)} 個廠商\n"
                f"  - 失敗：{len(failed_vendors)} 個廠商\n"
                f"  - 缺貨商品總數：{total_items} 件\n"
                f"{'='*30}\n\n"
            )

            # 列出成功廠商（最多前5名）
            if success_vendors:
                message += "✅ 成功發送廠商：\n"
                for vendor in success_vendors[:5]:
                    message += f"  - {vendor['vendor_name']}（{vendor['item_count']}件）\n"
                if len(success_vendors) > 5:
                    message += f"  ...等共 {len(success_vendors)} 個廠商\n"
                message += "\n"

            # 列出失敗廠商
            if failed_vendors:
                message += "❌ 失敗廠商：\n"
                for vendor in failed_vendors:
                    message += f"  - {vendor['vendor_name']}（{vendor.get('reason', '未知原因')}）\n"

            notifier = NotificationManager()
            notifier._send_line_messages([message])
            notifier._send_telegram_messages([message])
            sys_log.info("[EmailSend] ✅ 已發送廠商缺貨郵件通知")
        except Exception as e:
            sys_log.error(f"[EmailSend] ❌ 發送通知失敗 | Error: {e}")

        return jsonify({
            'success': True,
            'message': f'發送完成：成功 {len(success_vendors)} 個廠商，失敗 {len(failed_vendors)} 個',
            'data': {
                'success_count': len(success_vendors),
                'failed_count': len(failed_vendors),
                'success_vendors': success_vendors,
                'failed_vendors': failed_vendors,
                'batch_id': batch_id
            }
        })

    except Exception as e:
        sys_log.error(f"[EmailSend] 分組發送失敗 | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'發送失敗: {str(e)}'}), 500


@vendor_bp.route('/api/email/send/single', methods=['POST'])
def api_send_single_email():
    """
    按商品單筆發送郵件

    Request Body:
    {
        "stockout_ids": [1, 2, 3, ...]  # 缺貨記錄 ID 清單
    }

    Returns:
        成功/失敗的商品統計
    """
    try:
        from services.vendor_email_service import VendorEmailService

        data = request.get_json()
        stockout_ids = data.get('stockout_ids', [])

        if not stockout_ids or len(stockout_ids) == 0:
            return jsonify({'success': False, 'message': '未選擇缺貨記錄'}), 400

        sys_log.info(f"[EmailSend] 開始單筆發送 | 記錄數: {len(stockout_ids)}")

        # 查詢缺貨記錄
        session = vendor_db.get_session()
        stockout_records = session.query(VendorStockout).filter(
            VendorStockout.id.in_(stockout_ids)
        ).all()

        if not stockout_records:
            return jsonify({'success': False, 'message': '找不到缺貨記錄'}), 404

        # 初始化郵件服務
        email_service = VendorEmailService()

        # 發送結果統計
        success_items = []
        failed_items = []
        batch_id = datetime.now().strftime('%Y%m%d_%H%M%S')

        # 逐筆發送
        for record in stockout_records:
            # 取得廠商郵件
            vendor = session.query(VendorList).filter_by(vendor_code=record.vendor_code).first()

            if not vendor:
                sys_log.warning(f"[EmailSend] 廠商不存在 | 代碼: {record.vendor_code}")
                failed_items.append({
                    'product_code': record.product_code,
                    'product_name': record.product_name,
                    'reason': '廠商資料不存在'
                })
                continue

            # 取得郵件清單
            emails = session.query(VendorEmail).filter(
                VendorEmail.vendor_id == vendor.id,
                VendorEmail.is_active == True
            ).all()

            vendor_emails = [e.email for e in emails]

            if not vendor_emails:
                sys_log.warning(f"[EmailSend] 廠商無郵件 | 代碼: {record.vendor_code}")
                failed_items.append({
                    'product_code': record.product_code,
                    'product_name': record.product_name,
                    'reason': '廠商未設定郵件地址'
                })
                continue

            # 準備商品資料（包含所有欄位）
            item_data = {
                'id': record.id,
                'batch_id': record.batch_id,
                'import_date': record.import_date.strftime('%Y-%m-%d') if record.import_date else '',
                'department': record.department,
                'section': record.section,
                'pm_name': record.pm_name,
                'zone_id': record.zone_id or '',
                'zone_name': record.zone_name or '',
                'product_code': record.product_code,
                'product_name': record.product_name,
                'product_spec': record.product_spec,
                'borrow_transfer': record.borrow_transfer or '',
                'vendor_code': record.vendor_code,
                'vendor_name': record.vendor_name,
                'monthly_sales_qty': record.monthly_sales_qty,
                'current_stock': record.current_stock,
                'stockout_date': record.stockout_date.strftime('%Y-%m-%d') if record.stockout_date else '',
                'stockout_days': record.stockout_days,
                'monthly_sales_amount': float(record.monthly_sales_amount) if record.monthly_sales_amount else 0,
                'daily_avg_sales': float(record.daily_avg_sales) if record.daily_avg_sales else 0,
                'safe_stock_days': record.safe_stock_days,
                'status': record.status,
                'notes': record.notes
            }

            # 發送郵件
            result = email_service.send_single_item_email(
                vendor_code=record.vendor_code,
                vendor_name=record.vendor_name,
                vendor_emails=vendor_emails,
                stockout_item=item_data,
                batch_id=batch_id
            )

            if result['success']:
                success_items.append({
                    'product_code': record.product_code,
                    'product_name': record.product_name,
                    'vendor_name': record.vendor_name
                })

                # 更新缺貨記錄狀態
                vendor_db.update_stockout_status(
                    stockout_id=record.id,
                    status='sent',
                    sent_date=datetime.now()
                )
            else:
                failed_items.append({
                    'product_code': record.product_code,
                    'product_name': record.product_name,
                    'reason': result['message']
                })

        session.close()

        sys_log.info(f"[EmailSend] 單筆發送完成 | 成功: {len(success_items)} | 失敗: {len(failed_items)}")

        return jsonify({
            'success': True,
            'message': f'發送完成：成功 {len(success_items)} 筆，失敗 {len(failed_items)} 筆',
            'data': {
                'success_count': len(success_items),
                'failed_count': len(failed_items),
                'success_items': success_items,
                'failed_items': failed_items,
                'batch_id': batch_id
            }
        })

    except Exception as e:
        sys_log.error(f"[EmailSend] 單筆發送失敗 | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'發送失敗: {str(e)}'}), 500


# ==========================================
# 郵件發送記錄查詢 API
# ==========================================

@vendor_bp.route('/api/email/logs')
def api_email_logs():
    """
    查詢郵件發送記錄
    支援分頁、搜尋、篩選
    """
    try:
        # 獲取查詢參數
        page = int(request.args.get('page', 1))
        page_size = int(request.args.get('page_size', 20))
        status = request.args.get('status', '')  # pending, sent, failed
        vendor_code = request.args.get('vendor_code', '')
        date_from = request.args.get('date_from', '')
        date_to = request.args.get('date_to', '')

        session = vendor_db.get_session()

        try:
            # 基礎查詢
            query = session.query(EmailSendLog).join(
                VendorList, EmailSendLog.vendor_id == VendorList.id
            )

            # 狀態篩選
            if status:
                query = query.filter(EmailSendLog.status == status)

            # 廠商篩選
            if vendor_code:
                query = query.filter(VendorList.vendor_code.like(f'%{vendor_code}%'))

            # 日期範圍篩選
            if date_from:
                query = query.filter(EmailSendLog.created_at >= datetime.strptime(date_from, '%Y-%m-%d'))
            if date_to:
                query = query.filter(EmailSendLog.created_at <= datetime.strptime(f'{date_to} 23:59:59', '%Y-%m-%d %H:%M:%S'))

            # 總數
            total = query.count()

            # 分頁
            logs = query.order_by(desc(EmailSendLog.created_at))\
                       .offset((page - 1) * page_size)\
                       .limit(page_size)\
                       .all()

            # 組裝結果
            result = []
            for log in logs:
                vendor = session.query(VendorList).filter_by(id=log.vendor_id).first()
                result.append({
                    'id': log.id,
                    'batch_id': log.batch_id,
                    'vendor_code': vendor.vendor_code if vendor else '',
                    'vendor_name': vendor.vendor_name if vendor else '',
                    'recipient_email': log.recipient_email,
                    'subject': log.subject,
                    'product_count': log.product_count,
                    'status': log.status,
                    'error_message': log.error_message,
                    'sent_at': log.sent_at.strftime('%Y-%m-%d %H:%M:%S') if log.sent_at else '',
                    'created_at': log.created_at.strftime('%Y-%m-%d %H:%M:%S')
                })

            return jsonify({
                'success': True,
                'data': result,
                'total': total,
                'page': page,
                'page_size': page_size
            })

        finally:
            session.close()

    except Exception as e:
        sys_log.error(f"[EmailLog] 查詢郵件記錄失敗 | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'查詢失敗: {str(e)}'}), 500


@vendor_bp.route('/api/email/stats')
def api_email_stats():
    """
    獲取郵件發送統計資訊
    """
    try:
        session = vendor_db.get_session()

        try:
            # 總發送數
            total_sent = session.query(EmailSendLog).count()

            # 成功數
            success_count = session.query(EmailSendLog).filter_by(status='sent').count()

            # 失敗數
            failed_count = session.query(EmailSendLog).filter_by(status='failed').count()

            # 待發送數
            pending_count = session.query(EmailSendLog).filter_by(status='pending').count()

            # 成功率
            success_rate = (success_count / total_sent * 100) if total_sent > 0 else 0

            # 今日發送數
            today = date.today()
            today_sent = session.query(EmailSendLog).filter(
                func.date(EmailSendLog.created_at) == today
            ).count()

            # 最近發送時間
            last_log = session.query(EmailSendLog).order_by(desc(EmailSendLog.created_at)).first()
            last_sent_time = last_log.created_at.strftime('%Y-%m-%d %H:%M:%S') if last_log else ''

            return jsonify({
                'success': True,
                'data': {
                    'total_sent': total_sent,
                    'success_count': success_count,
                    'failed_count': failed_count,
                    'pending_count': pending_count,
                    'success_rate': round(success_rate, 2),
                    'today_sent': today_sent,
                    'last_sent_time': last_sent_time
                }
            })

        finally:
            session.close()

    except Exception as e:
        sys_log.error(f"[EmailLog] 查詢統計資訊失敗 | 錯誤: {e}")
        return jsonify({'success': False, 'message': f'查詢失敗: {str(e)}'}), 500
