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],
    ) -> 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('部分数据文件不存在')

            total_count = sum(f.data_count for f in files)

            pkg = DataPackage(
                name=name,
                category_id=cat_db_id,
                remark=remark,
                data_count=total_count,
            )
            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('数据包不存在')

            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)

    def preview_records(self, file_ids: list[int], limit: int = 300) -> 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)

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

    def _merge_records(
        self,
        file_ids: list[int],
        file_map: dict[int, Any],
        limit: int,
    ) -> dict[str, Any]:
        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)
            records, count = self._dm._read_records(path, limit=remaining if remaining > 0 else 0)
            total_count += count
            all_records.extend(records)
            remaining -= len(records)
            if remaining <= 0:
                break

        return {'records': all_records, 'count': total_count}

    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
