"""
莱塔萝门店数据导入脚本
用法：
  首次导入: python import_data.py --import-all
  追加销售: python import_data.py --append-sales <xls文件路径>
  更新库存: python import_data.py --update-inventory <xls文件路径>
  生成报表: python import_data.py --generate-report (日报|周报|月报)
"""

import os, sys, json, argparse, warnings
from datetime import datetime, timedelta
import pandas as pd
import numpy as np

warnings.filterwarnings('ignore')

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
SALES_FILE = os.path.join(BASE_DIR, 'sales.parquet')
INVENTORY_FILE = os.path.join(BASE_DIR, 'inventory.parquet')
CONFIG_FILE = os.path.join(BASE_DIR, 'config.json')

# ========== 列名映射 ==========

SALES_COLS = [
    'receipt_no', 'seq_no', 'product_code', 'product_name', 'product_short',
    'vip_customer', 'customer_name', 'salesperson', 'quantity', 'unit',
    'unit_price', 'total', 'discount', 'subtotal', 'cash',
    'wechat_alipay', 'other_pay', 'is_refund', 'sale_time', 'note',
    'store_name', 'extra_id'
]

INVENTORY_COLS = [
    'product_code', 'product_name', 'product_short', 'color_code', 'color_name',
    'size', 'brand', 'season', 'composition', 'history_sales',
    'entry_time', 'location', 'stock_qty', 'store_name', 'retail_price',
    'cost_price', 'cost_total', 'retail_total', 'series', 'category',
    'style_code'
]


def read_sales_xlrd(filepath):
    """Read sales XLS using xlrd, return DataFrame with standard column names."""
    import xlrd
    wb = xlrd.open_workbook(filepath, formatting_info=False)
    sh = wb.sheet_by_index(0)
    
    rows = []
    for r in range(1, sh.nrows):
        row = {}
        for c in range(min(sh.ncols, len(SALES_COLS))):
            v = sh.cell_value(r, c)
            if v == '' or v is None:
                row[SALES_COLS[c]] = None
            else:
                row[SALES_COLS[c]] = v
        rows.append(row)
    
    df = pd.DataFrame(rows)
    
    # Type conversions
    if 'sale_time' in df.columns:
        df['sale_time'] = pd.to_datetime(df['sale_time'], errors='coerce')
    if 'quantity' in df.columns:
        df['quantity'] = pd.to_numeric(df['quantity'], errors='coerce').fillna(0)
    if 'subtotal' in df.columns:
        df['subtotal'] = pd.to_numeric(df['subtotal'], errors='coerce').fillna(0)
    if 'discount' in df.columns:
        df['discount'] = pd.to_numeric(df['discount'], errors='coerce').fillna(1.0)
    if 'total' in df.columns:
        df['total'] = pd.to_numeric(df['total'], errors='coerce').fillna(0)
    if 'is_refund' in df.columns:
        df['is_refund'] = df['is_refund'].astype(str).str.lower().isin(['true', '1', 'yes'])
    
    # Fill store_name
    if 'store_name' in df.columns:
        df['store_name'] = df['store_name'].fillna('').astype(str).str.strip()
    
    # Mark refund rows (product_code starts with 销售额 or is empty)
    if 'product_code' in df.columns:
        df['is_refund_row'] = df['product_code'].astype(str).str.contains('销售额|退货|退款')
    
    return df


def read_inventory_xlrd(filepath):
    """Read inventory XLS using xlrd, return DataFrame."""
    import xlrd
    wb = xlrd.open_workbook(filepath, formatting_info=False)
    sh = wb.sheet_by_index(0)
    
    rows = []
    for r in range(1, sh.nrows):
        row = {}
        for c in range(min(sh.ncols, len(INVENTORY_COLS))):
            v = sh.cell_value(r, c)
            if v == '' or v is None:
                row[INVENTORY_COLS[c]] = None
            else:
                row[INVENTORY_COLS[c]] = v
        rows.append(row)
    
    df = pd.DataFrame(rows)
    
    # Type conversions
    if 'stock_qty' in df.columns:
        df['stock_qty'] = pd.to_numeric(df['stock_qty'], errors='coerce').fillna(0)
    if 'retail_price' in df.columns:
        df['retail_price'] = pd.to_numeric(df['retail_price'], errors='coerce').fillna(0)
    if 'cost_price' in df.columns:
        df['cost_price'] = pd.to_numeric(df['cost_price'], errors='coerce').fillna(0)
    if 'history_sales' in df.columns:
        df['history_sales'] = pd.to_numeric(df['history_sales'], errors='coerce').fillna(0)
    if 'store_name' in df.columns:
        df['store_name'] = df['store_name'].fillna('').astype(str).str.strip()
    
    return df


def init_data_directory():
    """Create data directory and config file if not exist."""
    os.makedirs(BASE_DIR, exist_ok=True)
    
    if not os.path.exists(CONFIG_FILE):
        config = {
            'stores': [],
            'last_sales_update': None,
            'last_inventory_update': None,
            'sales_date_range': [None, None],
            'total_sales_records': 0,
            'total_inventory_records': 0,
        }
        with open(CONFIG_FILE, 'w', encoding='utf-8') as f:
            json.dump(config, f, ensure_ascii=False, indent=2)
        print(f"✅ Config initialized: {CONFIG_FILE}")


def import_all():
    """Import both files from the letaro root."""
    print("=" * 60)
    print("📥 莱塔萝门店数据 - 首次导入")
    print("=" * 60)
    
    # Find sales file
    sales_files = [
        os.path.join(BASE_DIR, '..', '各门店日销售-明细.xls'),
        os.path.join(BASE_DIR, '..', '各门店日销售-明细_202605311127.xls'),
    ]
    sales_path = None
    for f in sales_files:
        if os.path.exists(f):
            sales_path = os.path.abspath(f)
            break
    
    inventory_files = [
        os.path.join(BASE_DIR, '..', '各门店库存情况-明细.xls'),
        os.path.join(BASE_DIR, '..', '各门店库存情况-明细_202605311126.xls'),
    ]
    inventory_path = None
    for f in inventory_files:
        if os.path.exists(f):
            inventory_path = os.path.abspath(f)
            break
    
    if sales_path:
        print(f"📄 导入销售流水: {os.path.basename(sales_path)}")
        df_sales = read_sales_xlrd(sales_path)
        df_sales.to_parquet(SALES_FILE, index=False)
        
        # Update config
        with open(CONFIG_FILE, 'r') as f:
            config = json.load(f)
        config['last_sales_update'] = datetime.now().isoformat()
        config['total_sales_records'] = len(df_sales)
        stores = df_sales['store_name'].dropna().unique().tolist()
        config['stores'] = [s for s in stores if s]
        
        if 'sale_time' in df_sales.columns:
            valid_times = df_sales['sale_time'].dropna()
            if len(valid_times) > 0:
                config['sales_date_range'] = [
                    valid_times.min().strftime('%Y-%m-%d'),
                    valid_times.max().strftime('%Y-%m-%d')
                ]
        
        with open(CONFIG_FILE, 'w') as f:
            json.dump(config, f, ensure_ascii=False, indent=2)
        
        print(f"   ✅ 销售记录: {len(df_sales)} 条")
        print(f"   ✅ 门店数: {len(config['stores'])}")
        print(f"   📅 日期范围: {config['sales_date_range'][0]} ~ {config['sales_date_range'][1]}")
    else:
        print("⚠️ 没有找到销售流水文件")
    
    if inventory_path:
        print(f"\n📄 导入库存快照: {os.path.basename(inventory_path)}")
        df_inv = read_inventory_xlrd(inventory_path)
        df_inv.to_parquet(INVENTORY_FILE, index=False)
        
        with open(CONFIG_FILE, 'r') as f:
            config = json.load(f)
        config['last_inventory_update'] = datetime.now().isoformat()
        config['total_inventory_records'] = len(df_inv)
        with open(CONFIG_FILE, 'w') as f:
            json.dump(config, f, ensure_ascii=False, indent=2)
        
        total_stock = df_inv['stock_qty'].sum()
        total_value = (df_inv['retail_price'] * df_inv['stock_qty']).sum() if 'retail_price' in df_inv.columns else 0
        print(f"   ✅ 库存记录: {len(df_inv)} 条")
        print(f"   📦 总库存量: {int(total_stock)} 件")
        print(f"   💰 库存总值: ¥{int(total_value):,}")
    
    print("\n✅ 首次导入完成！")


def append_sales(filepath):
    """Append new sales data to existing parquet."""
    if not os.path.exists(SALES_FILE):
        print("⚠️ 没有找到已有销售数据，请先运行 --import-all")
        return
    
    print(f"📄 追加销售流水: {os.path.basename(filepath)}")
    df_new = read_sales_xlrd(filepath)
    
    df_existing = pd.read_parquet(SALES_FILE)
    df_combined = pd.concat([df_existing, df_new], ignore_index=True)
    
    # Deduplicate by receipt no if possible
    if 'seq_no' in df_combined.columns:
        df_combined = df_combined.drop_duplicates(subset=['seq_no'], keep='last')
    
    df_combined.to_parquet(SALES_FILE, index=False)
    
    with open(CONFIG_FILE, 'r') as f:
        config = json.load(f)
    config['last_sales_update'] = datetime.now().isoformat()
    config['total_sales_records'] = len(df_combined)
    with open(CONFIG_FILE, 'w') as f:
        json.dump(config, f, ensure_ascii=False, indent=2)
    
    print(f"   ✅ 追加 {len(df_new)} 条")
    print(f"   ✅ 总计 {len(df_combined)} 条")


def update_inventory(filepath):
    """Replace inventory with new snapshot."""
    print(f"📄 更新库存: {os.path.basename(filepath)}")
    df_inv = read_inventory_xlrd(filepath)
    df_inv.to_parquet(INVENTORY_FILE, index=False)
    
    with open(CONFIG_FILE, 'r') as f:
        config = json.load(f)
    config['last_inventory_update'] = datetime.now().isoformat()
    config['total_inventory_records'] = len(df_inv)
    with open(CONFIG_FILE, 'w') as f:
        json.dump(config, f, ensure_ascii=False, indent=2)
    
    print(f"   ✅ 库存记录: {len(df_inv)} 条")


def show_status():
    """Show current data status."""
    if not os.path.exists(CONFIG_FILE):
        print("⚠️ 尚未初始化，请先运行 --import-all")
        return
    
    with open(CONFIG_FILE, 'r') as f:
        config = json.load(f)
    
    print("=" * 50)
    print("📊 莱塔萝数据状态")
    print("=" * 50)
    print(f"🏪 门店数: {len(config.get('stores', []))}")
    print(f"📅 销售日期范围: {config.get('sales_date_range', ['-', '-'])[0]} ~ {config.get('sales_date_range', ['-', '-'])[1]}")
    print(f"📄 销售记录数: {config.get('total_sales_records', 0):,}")
    print(f"📦 库存记录数: {config.get('total_inventory_records', 0):,}")
    print(f"🔄 最后销售更新: {config.get('last_sales_update', '-')[:19]}")
    print(f"🔄 最后库存更新: {config.get('last_inventory_update', '-')[:19]}")


def main():
    parser = argparse.ArgumentParser(description='莱塔萝门店数据管理')
    parser.add_argument('--import-all', action='store_true', help='首次导入所有数据')
    parser.add_argument('--append-sales', type=str, help='追加销售数据文件路径')
    parser.add_argument('--update-inventory', type=str, help='更新库存文件路径')
    parser.add_argument('--status', action='store_true', help='查看数据状态')
    parser.add_argument('--list-stores', action='store_true', help='列出所有门店')
    
    args = parser.parse_args()
    
    if args.status:
        show_status()
        return
    
    if args.list_stores:
        if not os.path.exists(SALES_FILE):
            print("⚠️ 没有销售数据")
            return
        df = pd.read_parquet(SALES_FILE)
        stores = df['store_name'].dropna().unique()
        for s in sorted(stores):
            if s:
                print(f"  {s}")
        return
    
    init_data_directory()
    
    if args.import_all:
        import_all()
    
    if args.append_sales:
        append_sales(args.append_sales)
    
    if args.update_inventory:
        update_inventory(args.update_inventory)


if __name__ == '__main__':
    main()