from typing import Any

from sqlalchemy import func

from app.database import db_session
from app.models import Category, DataFile, DataPackage, DataPackageFile
from app.services.data_management_service import DataManagementService


class PackageManagementService:
    def __init__(self) -> None:
        self._dm = DataManagementService()

    # ── categories ──────────────────────────────────────────────────────────

    def get_category_tree(self) -> list[dict[str, Any]]:
        with db_session() as session:
            rows = (
                session.query(Category)
                .filter(Category.type == 'data_package')
                .filter(Category.parent_id.is_(None))
                .order_by(Category.sort_order.asc(), Category.id.asc())
                .all()
            )

        return [
            {
                'id': item.id,
                'name': item.name,
                'parent_id': None,
                'children': [],
            }
            for item in rows
        ]

    def create_category(self, name: str, parent_id: str = 'all') -> dict[str, Any]:
        if not name:
            raise ValueError('分类名称不能为空')
        if str(parent_id).strip().lower() not in {'', 'all', 'none', 'null'}:
            raise ValueError('当前仅支持一级分类')

        with db_session() as session:
            category = Category(
                name=name,
                type='data_package',
                parent_id=None,
                sort_order=0,
            )
            session.add(category)
            session.commit()
            session.refresh(category)
            return {
                'id': category.id,
                'name': category.name,
                'parent_id': None,
                'type': category.type,
                'sort_order': category.sort_order,
            }

    def update_category(self, category_id: str, name: str) -> dict[str, Any]:
        if category_id in {'', 'all'}:
            raise ValueError('分类ID不能为空')
        if not name:
            raise ValueError('分类名称不能为空')

        db_id = self._parse_int_id(category_id, '分类ID')
        with db_session() as session:
            category = (
                session.query(Category)
                .filter(Category.id == db_id, Category.type == 'data_package')
                .first()
            )
            if not category:
                raise ValueError('分类不存在')

            category.name = name
            session.commit()
            session.refresh(category)
            return {
                'id': category.id,
                'name': category.name,
                'parent_id': None,
                'type': category.type,
                'sort_order': category.sort_order,
            }

    def delete_category(self, category_id: str) -> None:
        if category_id in {'', 'all'}:
            raise ValueError('分类ID不能为空')

        db_id = self._parse_int_id(category_id, '分类ID')
        with db_session() as session:
            category = (
                session.query(Category)
                .filter(Category.id == db_id, Category.type == 'data_package')
                .first()
            )
            if not category:
                raise ValueError('分类不存在')

            pkg_count = (
                session.query(func.count(DataPackage.id))
                .filter(DataPackage.category_id == db_id)
                .scalar()
            )
            if pkg_count and pkg_count > 0:
                raise ValueError('该分类下仍有数据包，无法删除')

            session.delete(category)
            session.commit()

    # ── data files (for selection) ───────────────────────────────────────────

    def list_all_data_files(self) -> list[dict[str, Any]]:
        with db_session() as session:
            rows = (
                session.query(DataFile, Category)
                .outerjoin(Category, DataFile.category_id == Category.id)
                .order_by(Category.name.asc(), DataFile.uploaded_at.desc(), DataFile.id.desc())
                .all()
            )
            return [
                {
                    'id': f.id,
                    'filename': f.filename,
                    'category_id': f.category_id,
                    'category_name': c.name if c else '',
                    'data_count': f.data_count,
                    'uploaded_at': f.uploaded_at.strftime('%Y-%m-%d %H:%M:%S') if f.uploaded_at else '',
                }
                for f, c in rows
            ]

    # ── packages ─────────────────────────────────────────────────────────────

    def list_packages(self, category_id: str = '', name: str = '') -> list[dict[str, Any]]:
        with db_session() as session:
            query = session.query(DataPackage)

            if category_id not in ('', 'all', None):
                db_id = self._parse_int_id(category_id, '分类ID')
                query = query.filter(DataPackage.category_id == db_id)

            if name:
                query = query.filter(DataPackage.name.like(f'%{name}%'))

            rows = query.order_by(DataPackage.created_at.desc(), DataPackage.id.desc()).all()
            return [self._pkg_to_dict(item) for item in rows]

    def create_package(
        self,
        name: str,
        category_id: str | None,
        remark: str | None,
        file_ids: list[int],
        clean_rules: dict | None = None,
    ) -> dict[str, Any]:
        if not name:
            raise ValueError('数据包名称不能为空')
        if not file_ids:
            raise ValueError('请至少选择一个数据文件')

        cat_db_id: int | None = None
        if category_id and str(category_id).strip().lower() not in {'', 'all', 'none', 'null'}:
            cat_db_id = self._parse_int_id(str(category_id), '分类ID')

        with db_session() as session:
            if cat_db_id is not None:
                cat = (
                    session.query(Category)
                    .filter(Category.id == cat_db_id, Category.type == 'data_package')
                    .first()
                )
                if not cat:
                    raise ValueError('数据包分类不存在')

            files = session.query(DataFile).filter(DataFile.id.in_(file_ids)).all()
            if len(files) != len(file_ids):
                raise ValueError('部分数据文件不存在')

            file_map = {f.id: f for f in files}

            if clean_rules and clean_rules.get('enabled'):
                result = self._merge_records(file_ids, file_map, limit=None, clean_rules=clean_rules)
                total_count = result['count']
                stored_rules: dict | None = clean_rules
            else:
                total_count = sum(f.data_count for f in files)
                stored_rules = None

            pkg = DataPackage(
                name=name,
                category_id=cat_db_id,
                remark=remark,
                data_count=total_count,
                clean_rules=stored_rules,
            )
            session.add(pkg)
            session.flush()

            for idx, fid in enumerate(file_ids):
                pf = DataPackageFile(package_id=pkg.id, file_id=fid, sort_order=idx)
                session.add(pf)

            session.commit()
            session.refresh(pkg)
            return self._pkg_to_dict(pkg)

    def delete_package(self, package_id: str) -> None:
        db_id = self._parse_int_id(package_id, '数据包ID')
        with db_session() as session:
            pkg = session.query(DataPackage).filter(DataPackage.id == db_id).first()
            if not pkg:
                raise ValueError('数据包不存在')
            session.query(DataPackageFile).filter(DataPackageFile.package_id == db_id).delete()
            session.delete(pkg)
            session.commit()

    def get_package_records(self, package_id: str, limit: int = 500) -> dict[str, Any]:
        db_id = self._parse_int_id(package_id, '数据包ID')

        with db_session() as session:
            pkg = session.query(DataPackage).filter(DataPackage.id == db_id).first()
            if not pkg:
                raise ValueError('数据包不存在')

            stored_clean_rules = pkg.clean_rules

            pf_rows = (
                session.query(DataPackageFile)
                .filter(DataPackageFile.package_id == db_id)
                .order_by(DataPackageFile.sort_order.asc())
                .all()
            )
            file_ids = [pf.file_id for pf in pf_rows]
            files = session.query(DataFile).filter(DataFile.id.in_(file_ids)).all()
            file_map = {f.id: f for f in files}

        return self._merge_records(file_ids, file_map, limit, clean_rules=stored_clean_rules)

    def preview_records(
        self,
        file_ids: list[int],
        limit: int = 300,
        clean_rules: dict | None = None,
    ) -> dict[str, Any]:
        if not file_ids:
            return {'records': [], 'count': 0}

        with db_session() as session:
            files = session.query(DataFile).filter(DataFile.id.in_(file_ids)).all()
            file_map = {f.id: f for f in files}

        return self._merge_records(file_ids, file_map, limit, clean_rules=clean_rules)

    # ── helpers ──────────────────────────────────────────────────────────────

    def _merge_records(
        self,
        file_ids: list[int],
        file_map: dict[int, Any],
        limit: int | None,
        clean_rules: dict | None = None,
    ) -> dict[str, Any]:
        use_filter = bool(clean_rules and clean_rules.get('enabled'))
        all_records: list[dict[str, Any]] = []
        total_count = 0
        remaining = limit

        for fid in file_ids:
            if fid not in file_map:
                continue
            file_meta = file_map[fid]
            path = self._dm._resolve_local_file_path(file_meta.file_path, file_meta.stored_name)
            if use_filter:
                # Read all rows so filter can be applied correctly
                records, count = self._dm._read_records(path, limit=None)
            else:
                read_limit = (remaining if remaining is not None and remaining > 0 else 0) if remaining is not None else None
                records, count = self._dm._read_records(path, limit=read_limit)
            total_count += count
            all_records.extend(records)
            if not use_filter and remaining is not None:
                remaining -= len(records)
                if remaining <= 0:
                    break

        if use_filter:
            all_records = self._apply_clean_rules(all_records, clean_rules)
            total_count = len(all_records)

        if limit is not None and limit > 0:
            return {'records': all_records[:limit], 'count': total_count}
        return {'records': all_records, 'count': total_count}

    @staticmethod
    def _apply_clean_rules(records: list[dict[str, Any]], clean_rules: dict) -> list[dict[str, Any]]:
        c_min = clean_rules.get('current_min')
        c_max = clean_rules.get('current_max')
        v_min = clean_rules.get('voltage_min')
        v_max = clean_rules.get('voltage_max')
        t_min = clean_rules.get('temperature_min')
        t_max = clean_rules.get('temperature_max')

        result = []
        for r in records:
            current = r.get('current')
            voltage = r.get('voltage')
            temp = r.get('actual_temperature')

            if current is not None:
                if c_min is not None and current < c_min:
                    continue
                if c_max is not None and current > c_max:
                    continue
            if voltage is not None:
                if v_min is not None and voltage < v_min:
                    continue
                if v_max is not None and voltage > v_max:
                    continue
            if temp is not None:
                if t_min is not None and temp < t_min:
                    continue
                if t_max is not None and temp > t_max:
                    continue

            result.append(r)
        return result

    def _pkg_to_dict(self, pkg: DataPackage) -> dict[str, Any]:
        return {
            'id': pkg.id,
            'name': pkg.name,
            'category_id': pkg.category_id,
            'remark': pkg.remark,
            'data_count': pkg.data_count,
            'created_at': pkg.created_at.strftime('%Y-%m-%d %H:%M:%S') if pkg.created_at else '',
            'updated_at': pkg.updated_at.strftime('%Y-%m-%d %H:%M:%S') if pkg.updated_at else '',
        }

    @staticmethod
    def _parse_int_id(value: str, label: str = 'ID') -> int:
        try:
            return int(value)
        except (ValueError, TypeError) as exc:
            raise ValueError(f'{label}格式错误') from exc
