#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
測試 Excel 欄位是否完整
"""

import sys
import os
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))

from services.vendor_email_service import VendorEmailService
import pandas as pd
import io

def test_excel_columns():
    """測試 Excel 欄位生成"""

    print("=" * 80)
    print("測試 Excel 欄位")
    print("=" * 80)

    # 建立測試資料
    test_items = [{
        'id': 1,
        'batch_id': 'TEST001',
        'import_date': '2026-01-13',
        'department': '百貨二處',
        'section': '美妝',
        'pm_name': '陳穎品',
        'product_code': 'DDCV0J-A900HBCUQ-000',
        'product_name': '現貨!【愛爾康4入組】 視舒坦 玻尿酸濕潤液',
        'product_spec': '單品',
        'vendor_code': '33035',
        'vendor_name': '南紡夢時代',
        'monthly_sales_qty': 17,
        'current_stock': 0,
        'monthly_sales_amount': 37520,
        'daily_avg_sales': 0.57,
        'safe_stock_days': 10,
    }]

    # 產生 Excel
    service = VendorEmailService()
    excel_attachment = service._generate_excel_attachment(
        items=test_items,
        filename='test.xlsx'
    )

    if not excel_attachment:
        print("\n❌ Excel 產生失敗")
        return

    print("\n✅ Excel 產生成功")

    # 讀取 Excel 檢查欄位
    excel_data = excel_attachment['data']
    df = pd.read_excel(io.BytesIO(excel_data))

    print(f"\n📊 Excel 欄位數量: {len(df.columns)} 個")
    print("\n📋 欄位清單：")
    print("-" * 80)

    expected_columns = [
        '當前日期',          # 1
        '處別',              # 2
        '科別',              # 3
        'PM姓名',           # 4
        '區ID',             # 5
        '區名稱',           # 6
        '商品ID',           # 7
        '商品名稱',         # 8
        '單品/組合商品',    # 9
        '借採轉',           # 10
        '來源供應商編號',   # 11
        '來源供應商名稱',   # 12
        '商品可賣量',       # 13
        '缺貨日期',         # 14
        '缺貨天數',         # 15
        '缺貨商品前30天業績', # 16
        '最近30天銷售量',   # 17
        '',                 # 18 - 空白欄位
        '庫存水位',         # 19
    ]

    for i, col in enumerate(df.columns, 1):
        expected = expected_columns[i-1] if i-1 < len(expected_columns) else '?'
        match = '✅' if col == expected else '❌'
        print(f"   {i:2d}. [{match}] {col!r} (預期: {expected!r})")

    # 顯示第一行數據
    print("\n📄 第一行數據：")
    print("-" * 80)
    for col in df.columns:
        value = df[col].iloc[0] if len(df) > 0 else ''
        print(f"   {col}: {value}")

    # 檢查結果
    print("\n" + "=" * 80)
    if list(df.columns) == expected_columns:
        print("✅ 所有欄位都正確！")
    else:
        print("❌ 欄位不符合預期")
        print("\n缺少的欄位：")
        for col in expected_columns:
            if col not in df.columns:
                print(f"   - {col!r}")
        print("\n多出的欄位：")
        for col in df.columns:
            if col not in expected_columns:
                print(f"   - {col!r}")
    print("=" * 80)

if __name__ == '__main__':
    test_excel_columns()