#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
手动添加 location_type 字段到 vwed_operate_point_layer 表
使用项目的数据库连接
"""

import os
import sys

# 添加项目根目录到系统路径
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

try:
    from sqlalchemy import text
    from data.session import get_session, session_scope
    from utils.logger import get_logger
    
    logger = get_logger("manual_migration")
    HAS_DEPENDENCIES = True
except ImportError as e:
    print(f"缺少依赖模块: {e}")
    print("请确保已安装所有依赖或在有数据库连接的环境中运行")
    HAS_DEPENDENCIES = False

def add_location_type_field():
    """手动添加 location_type 字段"""
    try:
        with session_scope() as session:
            logger.info("开始添加 location_type 字段...")
            
            # 1. 检查字段是否已存在
            check_column_sql = """
            SELECT COUNT(*) as column_exists
            FROM information_schema.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'vwed_operate_point_layer' 
            AND COLUMN_NAME = 'location_type'
            """
            
            result = session.execute(text(check_column_sql)).fetchone()
            column_exists = result[0] if result else 0
            
            if column_exists > 0:
                logger.info("location_type 字段已存在，跳过添加")
                return True
            
            # 2. 添加 location_type 字段
            add_column_sql = """
            ALTER TABLE vwed_operate_point_layer 
            ADD COLUMN location_type INT NOT NULL DEFAULT 1 
            COMMENT '库位类型：1-物理库位，2-逻辑库位'
            """
            
            logger.info("正在添加 location_type 字段...")
            session.execute(text(add_column_sql))
            logger.info("✅ location_type 字段添加成功")
            
            # 3. 为所有现有记录设置默认值（物理库位）
            update_sql = """
            UPDATE vwed_operate_point_layer 
            SET location_type = 1 
            WHERE location_type IS NULL OR location_type = 0
            """
            
            logger.info("正在为现有记录设置默认值...")
            result = session.execute(text(update_sql))
            affected_rows = result.rowcount
            logger.info(f"✅ 已为 {affected_rows} 条记录设置为物理库位（location_type = 1）")
            
            # 4. 验证更新结果
            verify_sql = """
            SELECT 
                COUNT(*) as total_records,
                SUM(CASE WHEN location_type = 1 THEN 1 ELSE 0 END) as physical_locations,
                SUM(CASE WHEN location_type = 2 THEN 1 ELSE 0 END) as logical_locations
            FROM vwed_operate_point_layer
            """
            
            result = session.execute(text(verify_sql)).fetchone()
            if result:
                total, physical, logical = result
                logger.info(f"验证结果：总记录数 {total}，物理库位 {physical}，逻辑库位 {logical}")
            
            session.commit()
            logger.info("🎉 location_type 字段添加完成！")
            return True
            
    except Exception as e:
        logger.error(f"添加 location_type 字段失败: {str(e)}")
        return False

def show_table_structure():
    """显示表结构确认字段已添加"""
    try:
        with session_scope() as session:
            describe_sql = "DESCRIBE vwed_operate_point_layer"
            result = session.execute(text(describe_sql)).fetchall()
            
            logger.info("vwed_operate_point_layer 表结构：")
            logger.info("Field\t\tType\t\tNull\tKey\tDefault\tExtra")
            logger.info("-" * 80)
            
            for row in result:
                logger.info(f"{row[0]}\t\t{row[1]}\t\t{row[2]}\t{row[3]}\t{row[4]}\t{row[5]}")
                
    except Exception as e:
        logger.error(f"显示表结构失败: {str(e)}")

if __name__ == "__main__":
    if not HAS_DEPENDENCIES:
        print("❌ 缺少必要的依赖模块，无法运行迁移脚本")
        print("请使用以下SQL语句手动在数据库中执行：")
        print()
        print("-- 添加 location_type 字段")
        print("ALTER TABLE vwed_operate_point_layer ADD COLUMN location_type INT NOT NULL DEFAULT 1 COMMENT '库位类型：1-物理库位，2-逻辑库位';")
        print()
        print("-- 为现有记录设置默认值")
        print("UPDATE vwed_operate_point_layer SET location_type = 1 WHERE location_type IS NULL OR location_type = 0;")
        print()
        print("-- 验证结果")
        print("SELECT COUNT(*) as total_records, SUM(CASE WHEN location_type = 1 THEN 1 ELSE 0 END) as physical_locations, SUM(CASE WHEN location_type = 2 THEN 1 ELSE 0 END) as logical_locations FROM vwed_operate_point_layer;")
        sys.exit(1)
    
    logger.info("开始手动添加 location_type 字段...")
    
    success = add_location_type_field()
    
    if success:
        logger.info("显示更新后的表结构：")
        show_table_structure()
        logger.info("✅ 迁移成功完成！")
    else:
        logger.error("❌ 迁移失败！")
        sys.exit(1)