"""断面数据服务"""
from typing import List, Dict, Tuple
from sqlalchemy import text
from sqlalchemy.orm import Session
from app.core.logging_config import get_logger
from app.models.section_data import SectionData
from app.schemas.section_data import SectionDataCreate, SectionDataQuery
from app.schemas.common import BatchImportResponse
from .table_manager import TableManager

logger = get_logger(__name__)

class SectionDataService:
    """断面数据服务"""
    
    @staticmethod
    def batch_import(db: Session, account_id: int, data: List[SectionDataCreate]) -> BatchImportResponse:
        """批量导入断面数据"""
        table_name = SectionData.get_table_name(account_id)
        
        if not TableManager.ensure_table_exists(db, "section_data", account_id):
            return BatchImportResponse(
                success=False, total=len(data), inserted=0, skipped=0,
                message="创建表失败"
            )
        
        # 获取已存在的section_id
        section_ids = [item.section_id for item in data if item.section_id]
        existing_ids = set()
        if section_ids:
            placeholders = ",".join([f":id_{i}" for i in range(len(section_ids))])
            params = {f"id_{i}": sid for i, sid in enumerate(section_ids)}
            result = db.execute(
                text(f"SELECT section_id FROM {table_name} WHERE section_id IN ({placeholders})"),
                params
            )
            existing_ids = {row[0] for row in result.fetchall()}
        
        to_insert = []
        skipped_ids = []
        for item in data:
            if item.section_id in existing_ids:
                skipped_ids.append(item.section_id)
            else:
                to_insert.append(item)
                existing_ids.add(item.section_id)
        
        if to_insert:
            try:
                values = []
                params = {}
                for i, item in enumerate(to_insert):
                    values.append(f"(:project_{i}, :mileage_{i}, :name_{i}, :number_{i}, :status_{i}, "
                                f":excavation_method_{i}, :rock_mass_classification_{i}, :width_{i}, "
                                f":U0_{i}, :remarks_{i}, :department_id_{i}, :section_id_{i})")
                    params[f"project_{i}"] = item.project
                    params[f"mileage_{i}"] = item.mileage
                    params[f"name_{i}"] = item.name
                    params[f"number_{i}"] = item.number
                    params[f"status_{i}"] = item.status
                    params[f"excavation_method_{i}"] = item.excavation_method
                    params[f"rock_mass_classification_{i}"] = item.rock_mass_classification
                    params[f"width_{i}"] = item.width
                    params[f"U0_{i}"] = float(item.U0) if item.U0 else None
                    params[f"remarks_{i}"] = item.remarks
                    params[f"department_id_{i}"] = item.department_id
                    params[f"section_id_{i}"] = item.section_id
                
                sql = f"""INSERT INTO {table_name} 
                    (project, mileage, name, number, status, excavation_method, 
                    rock_mass_classification, width, U0, remarks, department_id, section_id) 
                    VALUES {','.join(values)}"""
                db.execute(text(sql), params)
                db.commit()
                logger.info(f"断面数据导入成功: account_id={account_id}, 插入={len(to_insert)}, 跳过={len(skipped_ids)}")
            except Exception as e:
                db.rollback()
                logger.error(f"断面数据导入失败: {e}")
                return BatchImportResponse(
                    success=False, total=len(data), inserted=0, skipped=len(skipped_ids),
                    skipped_ids=skipped_ids, message=f"插入失败: {str(e)}"
                )
        
        return BatchImportResponse(
            success=True, total=len(data), inserted=len(to_insert), skipped=len(skipped_ids),
            skipped_ids=skipped_ids, message="导入成功"
        )
    
    @staticmethod
    def query(db: Session, params: SectionDataQuery) -> Tuple[List[Dict], int]:
        """查询断面数据"""
        table_name = SectionData.get_table_name(params.account_id)
        
        if not TableManager.ensure_table_exists(db, "section_data", params.account_id):
            return [], 0
        
        conditions = []
        query_params = {}
        
        if params.department_id:
            conditions.append("department_id = :department_id")
            query_params["department_id"] = params.department_id
        if params.section_id:
            conditions.append("section_id = :section_id")
            query_params["section_id"] = params.section_id
        if params.name:
            conditions.append("name LIKE :name")
            query_params["name"] = f"%{params.name}%"
        if params.number:
            conditions.append("number = :number")
            query_params["number"] = params.number
        
        where_clause = " AND ".join(conditions) if conditions else "1=1"
        
        count_sql = f"SELECT COUNT(*) FROM {table_name} WHERE {where_clause}"
        total = db.execute(text(count_sql), query_params).scalar()
        
        offset = (params.page - 1) * params.page_size
        query_params["limit"] = params.page_size
        query_params["offset"] = offset
        
        data_sql = f"SELECT * FROM {table_name} WHERE {where_clause} LIMIT :limit OFFSET :offset"
        result = db.execute(text(data_sql), query_params)
        items = [dict(row._mapping) for row in result.fetchall()]
        
        return items, total
    
    @staticmethod
    def get_by_section_ids(db: Session, account_id: int, section_ids: List[str]) -> Dict[str, Dict]:
        """根据section_id批量获取断面数据"""
        if not section_ids:
            return {}
        
        table_name = SectionData.get_table_name(account_id)
        if not TableManager.ensure_table_exists(db, "section_data", account_id):
            return {}
        
        placeholders = ",".join([f":id_{i}" for i in range(len(section_ids))])
        params = {f"id_{i}": sid for i, sid in enumerate(section_ids)}
        
        sql = f"SELECT section_id, mileage, rock_mass_classification FROM {table_name} WHERE section_id IN ({placeholders})"
        result = db.execute(text(sql), params)
        
        return {row[0]: {"mileage": row[1], "rock_mass_classification": row[2]} for row in result.fetchall()}