"""
Data synchronization module for Design Image Search
监听 MySQL design 表变化，同步图片特征到本地数据库
"""

import logging
import json
import os
import time

import requests
from datetime import datetime
from typing import Optional, List, Dict
import pymysql
from urllib.parse import urlparse

from database import DatabaseManager
from core.feature_extractor import FeatureExtractor
from core.faiss_manager import FAISSManager

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


class DesignDataSync:
    """Design 数据同步器（统一的增量同步逻辑）"""

    def __init__(self, config: dict, memory_index=None):
        """
        初始化数据同步器

        Args:
            config: 配置字典
            memory_index: 内存索引实例（可选）
        """
        self.config = config
        self.memory_index = memory_index
        self.db_manager = DatabaseManager(config['database']['sqlite']['path'])
        self.feature_extractor = FeatureExtractor(
            orb_max_features=config['feature_extractor']['orb']['max_features'],
            cnn_enabled=True
        )
        self.faiss_manager = FAISSManager(
            index_path=config['faiss']['index_path'],
            mapping_path=config['faiss']['mapping_path'],
            tombstone_path=config['faiss']['tombstone_path'],
            vector_dim=config['faiss']['vector_dim']
        )

        # 图片缓存目录
        self.image_cache_dir = config['sync']['image_cache_dir']
        os.makedirs(self.image_cache_dir, exist_ok=True)

        # 尝试加载现有索引
        if not self.faiss_manager.load_index():
            logger.info("未找到现有索引，将在首次同步后创建")

    def _get_mysql_connection(self):
        """获取 MySQL 连接"""
        mysql_config = self.config['mysql']
        return pymysql.connect(
            host=mysql_config['host'],
            port=mysql_config['port'],
            user=mysql_config['username'],
            password=mysql_config['password'],
            database=mysql_config['database'],
            charset=mysql_config['charset'],
            cursorclass=pymysql.cursors.DictCursor
        )

    def _get_last_sync_time(self) -> Optional[datetime]:
        """
        获取上次同步时间
        首次返回 None，自动使用 1970-01-01
        """
        last_sync = self.db_manager.get_last_sync_time()
        if last_sync is None:
            # 首次同步，返回 1970-01-01（获取所有历史数据）
            logger.info("🔄 首次同步，将从 1970-01-01 开始（将处理所有历史数据）")
            return datetime(1970, 1, 1)
        return last_sync

    def _fetch_batch(self, last_sync_time: datetime, batch_size: int = 500) -> List[Dict]:
        """
        获取一批需要同步的 design 记录

        Args:
            last_sync_time: 上次同步时间
            batch_size: 批次大小

        Returns:
            List[Dict]: design 记录列表
        """
        try:
            with self._get_mysql_connection() as conn:
                cursor = conn.cursor();
                cursor.execute("""
                    SELECT id, design_no, images, utc_modified
                    FROM saas_design.design
                    WHERE eps_id = 2 
                    AND DELETE_KEY = 0
                    AND utc_modified > %s
                    ORDER BY utc_modified ASC
                    LIMIT %s
                """, (last_sync_time, batch_size))

                records = cursor.fetchall()
                if records:
                    logger.info(f"获取到 {len(records)} 条待同步记录（从 {last_sync_time} 开始）")
                return records

        except Exception as e:
            logger.error(f"获取同步批次失败: {e}")
            return []

    def _download_image(self, image_url: str) -> Optional[str]:
        """
        下载图片到本地

        Args:
            image_url: 图片 URL

        Returns:
            str or None: 本地图片路径
        """
        try:
            # 检查是否已缓存
            url_hash = hash(image_url)
            filename = f"{url_hash}.jpg"
            local_path = os.path.join(self.image_cache_dir, filename)

            if os.path.exists(local_path):
                logger.debug(f"使用缓存的图片: {image_url}")
                return local_path

            # 下载图片
            logger.info(f"下载图片: {image_url}")
            response = requests.get(
                image_url,
                timeout=self.config['sync']['image_download_timeout']
            )
            response.raise_for_status()

            # 保存到本地
            with open(local_path, 'wb') as f:
                f.write(response.content)

            return local_path

        except Exception as e:
            logger.error(f"下载图片失败 {image_url}: {e}")
            return None

    def _process_design(self, design_record: Dict) -> bool:
        """
        处理单个 design 记录

        Args:
            design_record: design 记录

        Returns:
            bool: 是否处理成功
        """
        try:
            design_id = design_record['id']
            design_no = design_record.get('design_no', '')
            images_json = design_record.get('images', '{}')
            utc_modified = design_record['utc_modified']

            # 确保 utc_modified 是 datetime 对象
            if isinstance(utc_modified, str):
                from datetime import datetime
                for fmt in ['%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M:%S.%f', '%Y-%m-%dT%H:%M:%S', '%Y-%m-%dT%H:%M:%S.%f', '%Y-%m-%dT%H:%M:%SZ']:
                    try:
                        utc_modified = datetime.strptime(utc_modified.replace('Z', '+00:00'), fmt.replace('Z', '+00:00'))
                        break
                    except ValueError:
                        continue
                else:
                    # 如果都失败了，使用当前时间
                    utc_modified = datetime.now()

            # 解析 images JSON 字段
            images_data = json.loads(images_json) if images_json else {}
            if not images_data:
                logger.warning(f"design {design_id} 没有图片数据")
                return False

            # 获取第一张图片（或主图）
            image_url = None
            if isinstance(images_data, list) and images_data:
                # 数组格式，取第一个
                first_image = images_data[0]
                if isinstance(first_image, dict):
                    # 支持多种可能的字段名
                    image_url = first_image.get('imgUrl') or first_image.get('url') or first_image.get('image_url') or first_image.get('src')
                    # 优先选择 score=1 的图片
                    for img in images_data:
                        if isinstance(img, dict) and img.get('score') == 1:
                            image_url = img.get('imgUrl') or img.get('url') or img.get('image_url') or img.get('src')
                            break
                else:
                    image_url = first_image
            elif isinstance(images_data, dict):
                # 对象格式，找主图或第一个
                image_url = images_data.get('imgUrl') or images_data.get('url') or images_data.get('image_url') or images_data.get('src') or images_data.get('main')
                if not image_url and images_data:
                    # 取第一个值
                    first_value = next(iter(images_data.values()))
                    if isinstance(first_value, dict):
                        image_url = first_value.get('imgUrl') or first_value.get('url') or first_value.get('image_url') or first_value.get('src')
                    elif isinstance(first_value, str):
                        # 检查是否是 URL
                        if first_value.startswith('http'):
                            image_url = first_value
                    else:
                        image_url = first_value

            if not image_url:
                logger.warning(f"design {design_id} 无法从 images 中提取图片 URL，原始数据: {images_json[:100]}...")
                return False

            logger.info(f"design {design_id} 提取到图片 URL: {image_url}")

            # 下载图片
            local_path = self._download_image(image_url)
            if not local_path:
                return False

            try:
                # 提取特征
                features = self.feature_extractor.extract_all_features(local_path)
                if not features:
                    logger.warning(f"design {design_id} 特征提取失败")
                    return False

                # 保存到数据库（image_path 保留用于调试，实际不使用）
                success = self.db_manager.save_image_features(
                    design_id=design_id,
                    design_no=design_no,
                    image_url=image_url,
                    image_path=local_path,  # 保留路径但文件会被删除
                    cnn_vector=features.get('cnn_vector'),
                    orb_keypoints=features.get('orb_kp'),
                    orb_desc=features.get('orb_desc')
                )

                if success:
                    # 增量添加到 FAISS 索引
                    cnn_vector = features.get('cnn_vector')
                    if cnn_vector is not None:
                        # 如果该ID已存在，先标记为墓碑（避免重复）
                        if design_id in self.faiss_manager.id_mapping:
                            logger.info(f"设计款号 {design_id} 已存在，标记旧版本为墓碑")
                            self.faiss_manager.mark_delete(design_id)

                        # 添加新版本
                        self.faiss_manager.incremental_add(design_id, cnn_vector)

                    # 同步更新内存索引
                    if self.memory_index:
                        self.memory_index.add_or_update(
                            img_id=design_id,
                            design_no=design_no,
                            image_url=image_url,
                            image_path=local_path
                        )

                # 特征保存成功后，删除本地图片文件以节省空间
                try:
                    import os
                    if os.path.exists(local_path):
                        os.remove(local_path)
                        logger.debug(f"已删除本地图片: {local_path}")
                except Exception as e:
                    logger.warning(f"删除本地图片失败 {local_path}: {e}")

                return success

            except Exception as e:
                # 发生错误时也要清理临时文件
                try:
                    import os
                    if os.path.exists(local_path):
                        os.remove(local_path)
                except:
                    pass
                raise e

        except Exception as e:
            logger.error(f"处理 design 记录失败 {design_record.get('id')}: {e}")
            return False

    def sync_once(self) -> Dict[str, int]:
        """
        执行一次同步（统一逻辑，消除特殊情况）

        Returns:
            Dict[str, int]: 同步结果统计
        """
        import time
        start_time = time.time()
        total_processed = 0
        total_success = 0
        max_modified = self._get_last_sync_time()

        try:
            logger.info("开始数据同步...")

            # 分批处理
            batch_size = self.config['sync']['batch_size']

            while True:
                # 查询一批数据
                designs = self._fetch_batch(max_modified, batch_size)
                if len(designs) == 0:
                    logger.info("没有更多数据需要同步")
                    break

                # 处理这一批
                batch_success = 0
                for design in designs:
                    if self._process_design(design):
                        batch_success += 1

                    # 更新最大修改时间
                    utc_mod = design['utc_modified']
                    # 确保比较的是 datetime 对象
                    if isinstance(utc_mod, str):
                        from datetime import datetime
                        for fmt in ['%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M:%S.%f', '%Y-%m-%dT%H:%M:%S', '%Y-%m-%dT%H:%M:%S.%f', '%Y-%m-%dT%H:%M:%SZ']:
                            try:
                                utc_mod = datetime.strptime(utc_mod.replace('Z', '+00:00'), fmt.replace('Z', '+00:00'))
                                break
                            except ValueError:
                                continue
                        else:
                            # 如果都失败了，使用当前时间
                            utc_mod = datetime.now()

                    if utc_mod > max_modified:
                        max_modified = utc_mod

                total_processed += len(designs)
                total_success += batch_success

                # 更新同步时间（支持断点续传）
                self.db_manager.update_sync_time(max_modified, len(designs))

                logger.info(f"批次处理完成: {len(designs)} 条记录，成功 {batch_success} 条")

                # 继续下一批
                if len(designs) < batch_size:
                    # 这已经是最后一批了
                    break

                # 使用最后一条记录的时间，确保是 datetime 对象
                last_utc_mod = designs[-1]['utc_modified']
                if isinstance(last_utc_mod, str):
                    from datetime import datetime
                    for fmt in ['%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M:%S.%f', '%Y-%m-%dT%H:%M:%S', '%Y-%m-%dT%H:%M:%S.%f', '%Y-%m-%dT%H:%M:%SZ']:
                        try:
                            max_modified = datetime.strptime(last_utc_mod.replace('Z', '+00:00'), fmt.replace('Z', '+00:00'))
                            break
                        except ValueError:
                            continue
                    else:
                        max_modified = datetime.now()
                else:
                    max_modified = last_utc_mod

            # 保存 FAISS 索引
            if total_success > 0:
                self.faiss_manager._save_index()
                logger.info("FAISS 索引已保存")

                # 检查墓碑数量，超过阈值自动触发重建
                tombstone_count = len(self.faiss_manager.tombstones)
                tombstone_threshold = self.config.get('faiss', {}).get('tombstone_rebuild_threshold', 1000)

                if tombstone_count >= tombstone_threshold:
                    logger.warning(f"墓碑数量达到阈值: {tombstone_count}/{tombstone_threshold}")
                    logger.info("自动触发索引重建以清理墓碑...")

                    import time
                    rebuild_start = time.time()

                    try:
                        if self.faiss_manager.rebuild_index(self.db_manager):
                            rebuild_time = time.time() - rebuild_start
                            logger.info(f"✓ 索引重建成功，耗时 {rebuild_time:.2f}秒")
                            logger.info(f"  清理前墓碑: {tombstone_count}")
                            logger.info(f"  清理后墓碑: {len(self.faiss_manager.tombstones)}")
                        else:
                            logger.error("✗ 索引重建失败")
                    except Exception as e:
                        logger.error(f"索引重建异常: {e}", exc_info=True)

            # 获取统计信息
            elapsed = time.time() - start_time
            result = {
                'total_processed': total_processed,
                'total_success': total_success,
                'total_failed': total_processed - total_success,
                'elapsed_seconds': elapsed,
                'last_sync_time': max_modified
            }

            logger.info(f"数据同步完成: {result}")
            return result

        except Exception as e:
            logger.error(f"数据同步失败: {e}")
            return {
                'total_processed': total_processed,
                'total_success': total_success,
                'total_failed': total_processed - total_success,
                'elapsed_seconds': time.time() - start_time,
                'error': str(e)
            }

    def run_forever(self):
        """定时同步，60 秒间隔"""
        interval = self.config['sync']['interval_seconds']
        logger.info(f"启动定时同步，间隔 {interval} 秒")

        while True:
            try:
                self.sync_once()
                logger.info(f"等待 {interval} 秒后进行下次同步...")
                time.sleep(interval)
            except KeyboardInterrupt:
                logger.info("收到中断信号，停止同步")
                break
            except Exception as e:
                logger.error(f"同步循环出错: {e}")
                logger.info("等待 60 秒后重试...")
                time.sleep(60)


if __name__ == "__main__":
    import yaml
    from dotenv import load_dotenv

    # 加载环境变量
    load_dotenv()

    # 加载配置
    with open('config.yml', 'r', encoding='utf-8') as f:
        config = yaml.safe_load(f)

    # 替换环境变量
    import os
    def replace_env_vars(obj):
        if isinstance(obj, dict):
            return {k: replace_env_vars(v) for k, v in obj.items()}
        elif isinstance(obj, list):
            return [replace_env_vars(item) for item in obj]
        elif isinstance(obj, str) and obj.startswith('${') and obj.endswith('}'):
            env_var = obj[2:-1]
            default = None
            if ':' in env_var:
                env_var, default = env_var.split(':', 1)
            return os.getenv(env_var, default)
        return obj

    config = replace_env_vars(config)

    # 执行一次同步
    sync = DesignDataSync(config)
    result = sync.sync_once()
    print(f"同步结果: {result}")