#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
測試從真實資料庫數據生成 Excel
"""

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

from database.vendor_manager import VendorDatabaseManager
from database.vendor_models import VendorStockout
from services.vendor_email_service import VendorEmailService
import pandas as pd
import io
import re

def test_real_excel():
    """測試真實資料庫數據生成Excel"""

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

    try:
        # 查詢第一筆記錄
        record = session.query(VendorStockout).first()

        if not record:
            print("❌ 沒有資料！")
            return

        print("=" * 80)
        print("從資料庫讀取的原始數據")
        print("=" * 80)
        print(f"import_date: {record.import_date}")
        print(f"department: {record.department}")
        print(f"section: {record.section}")
        print(f"pm_name: {record.pm_name}")
        print(f"product_code: {record.product_code}")
        print(f"product_name: {record.product_name[:50]}...")
        print(f"product_spec: {record.product_spec}")
        print(f"vendor_code: {record.vendor_code}")
        print(f"vendor_name: {record.vendor_name}")
        print(f"monthly_sales_qty: {record.monthly_sales_qty}")
        print(f"monthly_sales_amount: {record.monthly_sales_amount}")
        print(f"daily_avg_sales: {record.daily_avg_sales}")
        print(f"current_stock: {record.current_stock}")
        print(f"safe_stock_days: {record.safe_stock_days}")
        print(f"notes: {record.notes}")

        # 解析 notes
        zone_id = ''
        zone_name = ''
        borrow_transfer = ''
        stockout_date = ''
        stockout_days = ''

        if record.notes:
            zone_id_match = re.search(r'區ID:\s*([^,]*)', record.notes)
            zone_name_match = re.search(r'區名稱:\s*([^,]*)', record.notes)
            borrow_match = re.search(r'借採轉:\s*([^,]*)', record.notes)
            stockout_date_match = re.search(r'缺貨日期:\s*([^,]*)', record.notes)
            stockout_days_match = re.search(r'缺貨天數:\s*([^,]*)', record.notes)

            if zone_id_match:
                zone_id = zone_id_match.group(1).strip()
            if zone_name_match:
                zone_name = zone_name_match.group(1).strip()
            if borrow_match:
                borrow_transfer = borrow_match.group(1).strip()
            if stockout_date_match:
                stockout_date = stockout_date_match.group(1).strip()
            if stockout_days_match:
                stockout_days = stockout_days_match.group(1).strip()

        print("\n" + "=" * 80)
        print("解析後的額外欄位")
        print("=" * 80)
        print(f"zone_id: {zone_id}")
        print(f"zone_name: {zone_name}")
        print(f"borrow_transfer: {borrow_transfer}")
        print(f"stockout_date: {stockout_date}")
        print(f"stockout_days: {stockout_days}")

        # 準備測試數據
        test_items = [{
            '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': zone_id,
            'zone_name': zone_name,
            'product_code': record.product_code,
            'product_name': record.product_name,
            'product_spec': record.product_spec,
            'borrow_transfer': borrow_transfer,
            'vendor_code': record.vendor_code,
            'vendor_name': record.vendor_name,
            'monthly_sales_qty': record.monthly_sales_qty,
            'current_stock': record.current_stock,
            'stockout_date': stockout_date,
            'stockout_days': 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
        }]

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

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

        print("\n" + "=" * 80)
        print("生成的 Excel 內容")
        print("=" * 80)

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

        print(f"\n📊 Excel 欄位數量: {len(df.columns)} 個")
        print("\n📋 欄位清單：")
        for i, col in enumerate(df.columns, 1):
            print(f"   {i:2d}. {col!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)

    except Exception as e:
        print(f"\n❌ 錯誤: {e}")
        import traceback
        traceback.print_exc()
    finally:
        session.close()

if __name__ == '__main__':
    test_real_excel()