#!/usr/bin/env python3
"""
修复数据库中双重编码的JSON数据
"""
import sqlite3
import json
import sys
from pathlib import Path

# 数据库路径 - 使用绝对路径
DB_PATH = Path("/Users/jinjunqian/PycharmProjects/订单处理新版/ordersys/backend/ordersys.db")

def fix_double_encoded_json(value):
    """修复双重编码的JSON字符串"""
    if value is None or value == '':
        return None
    
    if not isinstance(value, str):
        return value
    
    try:
        # 尝试直接解析（正常情况）
        result = json.loads(value)
        # 如果成功，再编码回去保证格式一致
        return json.dumps(result, ensure_ascii=False)
    except json.JSONDecodeError:
        # 处理双重编码的情况
        try:
            # 如果是双重编码的字符串，通常格式是 "\"[...]\"" 
            if value.startswith('"') and value.endswith('"'):
                # 去除外层引号
                unquoted = value[1:-1]
                # 替换转义的引号
                unescaped = unquoted.replace('\\"', '"').replace('\\\\', '\\')
                result = json.loads(unescaped)
                return json.dumps(result, ensure_ascii=False)
        except Exception:
            pass
    
    # 如果都失败了，返回原值
    return value

def main():
    print(f"连接数据库: {DB_PATH}")
    
    if not DB_PATH.exists():
        print(f"错误: 数据库文件不存在: {DB_PATH}")
        sys.exit(1)
    
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    try:
        # 先统计有多少记录需要修复
        cursor.execute("""
            SELECT COUNT(*) FROM products_master 
            WHERE original_image_urls LIKE '"%' OR local_image_paths LIKE '"%'
        """)
        count = cursor.fetchone()[0]
        print(f"发现 {count} 条可能需要修复的记录")
        
        if count == 0:
            print("没有需要修复的记录")
            return
        
        # 查询所有需要修复的记录
        cursor.execute("""
            SELECT id, original_image_urls, local_image_paths 
            FROM products_master
            WHERE original_image_urls IS NOT NULL OR local_image_paths IS NOT NULL
        """)
        
        records = cursor.fetchall()
        fixed_count = 0
        
        for record_id, original_urls, local_paths in records:
            # 修复original_image_urls
            fixed_original = fix_double_encoded_json(original_urls)
            fixed_local = fix_double_encoded_json(local_paths)
            
            # 检查是否有变化
            if fixed_original != original_urls or fixed_local != local_paths:
                cursor.execute("""
                    UPDATE products_master 
                    SET original_image_urls = ?, local_image_paths = ?
                    WHERE id = ?
                """, (fixed_original, fixed_local, record_id))
                fixed_count += 1
                
                if fixed_count % 100 == 0:
                    print(f"已修复 {fixed_count} 条记录...")
        
        # 提交事务
        conn.commit()
        print(f"\n修复完成！共修复 {fixed_count} 条记录")
        
        # 验证修复结果
        cursor.execute(r"""
            SELECT COUNT(*) FROM products_master 
            WHERE original_image_urls LIKE '"\[%' OR local_image_paths LIKE '"\[%'
        """)
        remaining = cursor.fetchone()[0]
        
        if remaining > 0:
            print(f"警告: 仍有 {remaining} 条记录可能存在问题")
        else:
            print("所有记录已成功修复！")
            
    except Exception as e:
        print(f"错误: {e}")
        conn.rollback()
        sys.exit(1)
    finally:
        conn.close()

if __name__ == "__main__":
    main()