#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Excel货号提取工具
从Excel文件中提取货号并生成删除SQL
"""

import pandas as pd
import sys
from pathlib import Path

def extract_product_codes_and_generate_sql(excel_file_path):
    """
    从Excel文件提取货号并生成删除SQL
    """
    try:
        # 读取Excel文件
        print(f"正在读取Excel文件: {excel_file_path}")
        
        # 尝试读取第一个工作表
        df = pd.read_excel(excel_file_path, sheet_name=0)
        
        print(f"Excel文件包含 {len(df)} 行数据")
        print("列名:", list(df.columns))
        
        # 寻找货号列
        product_code_column = None
        possible_columns = ['货号', 'productCode', 'product_code', '商品编码', '编码']
        
        for col in df.columns:
            if any(keyword in str(col) for keyword in possible_columns):
                product_code_column = col
                break
        
        if product_code_column is None:
            print("未找到货号列，请检查Excel文件格式")
            print("可用的列:", list(df.columns))
            return
        
        print(f"找到货号列: {product_code_column}")
        
        # 提取货号
        product_codes = df[product_code_column].dropna().astype(str).tolist()
        product_codes = [code.strip() for code in product_codes if code.strip()]
        
        print(f"共提取到 {len(product_codes)} 个货号")
        
        if not product_codes:
            print("没有找到有效的货号数据")
            return
        
        # 生成SQL语句
        generate_delete_sql(product_codes)
        
    except Exception as e:
        print(f"处理Excel文件时出错: {str(e)}")

def generate_delete_sql(product_codes):
    """
    生成删除SQL语句
    """
    # 格式化货号列表
    formatted_codes = [f"'{code}'" for code in product_codes]
    codes_str = ',\n  '.join(formatted_codes)
    
    sql_template = f"""
-- 根据货号删除商品及相关数据的SQL语句
-- 总共 {len(product_codes)} 个货号

-- 1. 首先查询要删除的商品ID (用于确认)
SELECT id, product_code, product_name 
FROM sm_erp_spu 
WHERE product_code IN (
  {codes_str}
);

-- 2. 删除相关的辅助条码数据
DELETE FROM sm_erp_auxiliary_code 
WHERE spu_id IN (
  SELECT id FROM sm_erp_spu WHERE product_code IN (
    {codes_str}
  )
);

-- 3. 删除相关的单位数据
DELETE FROM sm_erp_unit 
WHERE spu_id IN (
  SELECT id FROM sm_erp_spu WHERE product_code IN (
    {codes_str}
  )
);

-- 4. 删除相关的库存数据
DELETE FROM sm_erp_stock 
WHERE erp_spu_id IN (
  SELECT id FROM sm_erp_spu WHERE product_code IN (
    {codes_str}
  )
);

-- 5. 最后删除商品主表数据
DELETE FROM sm_erp_spu 
WHERE product_code IN (
  {codes_str}
);

-- 货号列表:
{chr(10).join([f"-- {i+1}. {code}" for i, code in enumerate(product_codes)])}
"""
    
    # 保存到文件
    with open('delete_products.sql', 'w', encoding='utf-8') as f:
        f.write(sql_template)
    
    print("SQL语句已生成并保存到 delete_products.sql 文件")
    print("\n" + "="*50)
    print(sql_template)

if __name__ == "__main__":
    # Excel文件路径
    excel_file = "pjcloud-supermarket/pjcloud-supermarket-biz/青少年宫.111xls.xls"
    
    if not Path(excel_file).exists():
        print(f"Excel文件不存在: {excel_file}")
        sys.exit(1)
    
    extract_product_codes_and_generate_sql(excel_file)