import pandas as pd
from datetime import datetime
from app.database.database import SessionLocal
from app.models import Project, User


def import_projects_from_excel():
    """从Excel文件导入项目数据"""
    db = SessionLocal()
    
    try:
        # 读取Excel文件
        excel_path = "/home/xsl/code/xsl_node/docs/example.xls"
        print(f"Reading Excel file: {excel_path}")
        
        # 读取所有工作表
        xls = pd.ExcelFile(excel_path)
        print(f"Excel file has sheets: {xls.sheet_names}")
        
        # 读取第一个工作表
        df = pd.read_excel(xls, sheet_name=0)
        print(f"Read {len(df)} rows from Excel")
        
        # 获取marketing用户ID
        marketing_user = db.query(User).filter(User.username == "marketing").first()
        if not marketing_user:
            print("Marketing user not found. Exiting.")
            return
        
        # 准备导入的项目数据
        projects_to_import = []
        skipped_rows = 0
        
        for index, row in df.iterrows():
            try:
                # 创建项目对象
                project = Project(
                    project_code=str(row.get('项目编号', f'PROJ{index+1}')),
                    name=str(row.get('项目名称', f'项目{index+1}')),
                    project_type=str(row.get('工程类别', '')),
                    description=str(row.get('项目描述', '')),
                    department=str(row.get('所属部门', '未知部门')),
                    contract_amount=float(row.get('合同金额（万元）', 0)) if pd.notna(row.get('合同金额（万元）')) else 0,
                    project_manager=str(row.get('项目负责人', '')),
                    client_company=str(row.get('建设单位', '')),
                    client_contact=str(row.get('建设单位联系人', '')),
                    contract_date=pd.to_datetime(row.get('合同签订日期')).date() if pd.notna(row.get('合同签订日期')) else None,
                    start_date=pd.to_datetime(row.get('开始日期')).date() if pd.notna(row.get('开始日期')) else None,
                    planned_end_date=pd.to_datetime(row.get('计划结束日期')).date() if pd.notna(row.get('计划结束日期')) else None,
                    actual_end_date=pd.to_datetime(row.get('实际结束日期')).date() if pd.notna(row.get('实际结束日期')) else None,
                    progress=float(row.get('累计进度（%）', 0)) if pd.notna(row.get('累计进度（%）')) else 0,
                    actual_received_amount=float(row.get('实际收款金额（万元）', 0)) if pd.notna(row.get('实际收款金额（万元）')) else 0,
                    payment_completion_rate=float(row.get('回款完成率（%）', 0)) if pd.notna(row.get('回款完成率（%）')) else 0,
                    payment_terms=str(row.get('付款方式', '')),
                    status=str(row.get('状态', 'pending')),
                    created_by=marketing_user.id
                )
                projects_to_import.append(project)
                
                # 每100条记录打印一次进度
                if (index + 1) % 100 == 0:
                    print(f"Processed {index + 1} rows...")
                    
            except Exception as e:
                print(f"Error processing row {index + 1}: {e}")
                skipped_rows += 1
                continue
        
        # 批量导入数据
        if projects_to_import:
            print(f"Importing {len(projects_to_import)} projects to database...")
            db.add_all(projects_to_import)
            db.commit()
            print(f"Successfully imported {len(projects_to_import)} projects!")
            print(f"Skipped {skipped_rows} rows due to errors")
        else:
            print("No projects to import")
            
    except Exception as e:
        db.rollback()
        print(f"Error importing projects: {e}")
        import traceback
        traceback.print_exc()
    finally:
        db.close()


if __name__ == "__main__":
    import_projects_from_excel()