import csv
import io
from datetime import datetime
from pathlib import Path
from typing import Any

import xlrd
from openpyxl import Workbook, load_workbook
from sqlalchemy import func

from app.database import db_session
from app.models import Category, DataFile, DataQualityConfig


class DataManagementService:
    def __init__(self) -> None:
        self._base_dir = Path(__file__).resolve().parents[2]
        self._upload_dir = self._base_dir / 'uploads'
        self._data_dir = self._upload_dir / 'data'
        self._legacy_data_dir = Path(__file__).resolve().parents[3] / 'uploads' / 'data'

        self._upload_dir.mkdir(parents=True, exist_ok=True)
        self._data_dir.mkdir(parents=True, exist_ok=True)

    def get_category_tree(self) -> list[dict[str, Any]]:
        with db_session() as session:
            rows = (
                session.query(Category)
                .filter(Category.type == 'data_file')
                .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_file',
                parent_id=None,
                sort_order=0,
            )
            session.add(category)
            session.commit()
            session.refresh(category)

            return {
                'id': category.id,
                'name': category.name,
                'parent_id': category.parent_id,
                '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('分类名称不能为空')

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

            category.name = name
            session.commit()
            session.refresh(category)

            return {
                'id': category.id,
                'name': category.name,
                'parent_id': category.parent_id,
                'type': category.type,
                'sort_order': category.sort_order,
            }

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

        category_db_id = self._parse_int_id(category_id, '分类ID')

        with db_session() as session:
            category = (
                session.query(Category)
                .filter(Category.id == category_db_id, Category.type == 'data_file')
                .first()
            )
            if not category:
                raise ValueError('分类不存在')

            child_count = (
                session.query(func.count(Category.id))
                .filter(Category.parent_id == category_db_id, Category.type == 'data_file')
                .scalar()
            )
            if child_count and child_count > 0:
                raise ValueError('请先删除子分类')

            file_count = (
                session.query(func.count(DataFile.id))
                .filter(DataFile.category_id == category_db_id)
                .scalar()
            )
            if file_count and file_count > 0:
                raise ValueError('该分类下仍有文件，无法删除')

            session.delete(category)
            session.commit()

    def list_files(self, category_id: str = 'all', filename: str = '') -> list[dict[str, Any]]:
        with db_session() as session:
            query = session.query(DataFile)

            if category_id not in {'', 'all', None}:
                root_id = self._parse_int_id(category_id, '分类ID')
                query = query.filter(DataFile.category_id == root_id)

            if filename:
                query = query.filter(DataFile.filename.like(f'%{filename}%'))

            rows = query.order_by(DataFile.uploaded_at.desc(), DataFile.id.desc()).all()

            return [
                {
                    'id': item.id,
                    'filename': item.filename,
                    'stored_name': item.stored_name,
                    'file_path': item.file_path,
                    'category_id': item.category_id,
                    'uploaded_at': item.uploaded_at.strftime('%Y-%m-%d %H:%M:%S') if item.uploaded_at else '',
                    'data_count': item.data_count,
                    'remark': item.remark or '',
                }
                for item in rows
            ]

    def upload_file(self, filename: str, content: bytes, category_id: str = 'all') -> dict[str, Any]:
        suffix = Path(filename).suffix.lower()
        if suffix not in {'.xlsx', '.xls', '.csv'}:
            raise ValueError('仅支持数据文件（.xlsx/.xls/.csv）')

        if category_id in {'', 'all', None}:
            raise ValueError('请先选择分类后再上传文件')
        category_db_id = self._parse_int_id(category_id, '分类ID')

        with db_session() as session:
            category = (
                session.query(Category)
                .filter(Category.id == category_db_id, Category.type == 'data_file')
                .first()
            )
            if not category:
                raise ValueError('上传分类不存在')

            timestamp = datetime.now().strftime('%Y%m%d%H%M%S%f')
            safe_category_name = self._sanitize_filename(category.name)
            safe_original_name = self._sanitize_filename(Path(filename).stem)
            stored_name = f'{safe_category_name}_{timestamp}_{safe_original_name}{suffix}'
            target_path = self._data_dir / stored_name
            db_file_path = f'/app/uploads/data/{stored_name}'

            with target_path.open('wb') as file:
                file.write(content)

            try:
                _, total_count = self._read_records(target_path, limit=None)

                file_meta = DataFile(
                    filename=filename,
                    stored_name=stored_name,
                    file_path=db_file_path,
                    category_id=category_db_id,
                    data_count=total_count,
                )
                session.add(file_meta)
                session.commit()
                session.refresh(file_meta)

                return {
                    'id': file_meta.id,
                    'filename': file_meta.filename,
                    'stored_name': file_meta.stored_name,
                    'file_path': file_meta.file_path,
                    'category_id': file_meta.category_id,
                    'uploaded_at': file_meta.uploaded_at.strftime('%Y-%m-%d %H:%M:%S') if file_meta.uploaded_at else '',
                    'data_count': file_meta.data_count,
                }
            except Exception:
                if target_path.exists():
                    target_path.unlink()
                raise

    def update_file(self, file_id: str, filename: str | None = None, remark: str | None = None, category_id: str | None = None) -> dict[str, Any]:
        file_db_id = self._parse_int_id(file_id, '文件ID')

        if category_id is not None:
            cat_db_id = self._parse_int_id(category_id, '分类ID')

        with db_session() as session:
            matched = session.query(DataFile).filter(DataFile.id == file_db_id).first()
            if not matched:
                raise ValueError('文件不存在')

            if filename is not None:
                stripped = filename.strip()
                if not stripped:
                    raise ValueError('文件名不能为空')
                matched.filename = stripped

            if remark is not None:
                matched.remark = remark.strip() or None

            if category_id is not None:
                category = (
                    session.query(Category)
                    .filter(Category.id == cat_db_id, Category.type == 'data_file')
                    .first()
                )
                if not category:
                    raise ValueError('分类不存在')
                matched.category_id = cat_db_id

            session.commit()
            session.refresh(matched)

            return {
                'id': matched.id,
                'filename': matched.filename,
                'remark': matched.remark or '',
                'category_id': matched.category_id,
            }

    def delete_file(self, file_id: str) -> None:
        file_db_id = self._parse_int_id(file_id, '文件ID')

        with db_session() as session:
            matched = session.query(DataFile).filter(DataFile.id == file_db_id).first()
            if not matched:
                raise ValueError('文件不存在')

            target_path = self._resolve_local_file_path(matched.file_path, matched.stored_name)
            if target_path.exists():
                target_path.unlink()

            session.delete(matched)
            session.commit()

    def get_file_records(self, file_id: str, limit: int | None = None) -> dict[str, Any]:
        file_db_id = self._parse_int_id(file_id, '文件ID')

        with db_session() as session:
            file_meta = session.query(DataFile).filter(DataFile.id == file_db_id).first()

        if not file_meta:
            raise ValueError('文件不存在')

        target_path = self._resolve_local_file_path(file_meta.file_path, file_meta.stored_name)
        if not target_path.exists():
            raise ValueError('文件已丢失，请重新上传')

        records, total_count = self._read_records(target_path, limit=limit)
        return {
            'records': records,
            'count': total_count,
        }

    def _read_records(self, file_path: Path, limit: int | None = None) -> tuple[list[dict[str, Any]], int]:
        rows = self._read_excel_rows(file_path)

        if not rows:
            return [], 0

        first_row = [item.strip() for item in rows[0]]
        has_header = any(
            self._normalize_header(item) in {'time', 'current', 'voltage', 'set_temperature', 'actual_temperature'}
            for item in first_row
        )

        if has_header:
            header = first_row
            data_rows = rows[1:]
        else:
            header = ['time', 'current', 'voltage', 'set_temperature', 'actual_temperature']
            data_rows = rows

        index_map = self._build_index_map(header)
        total_count = len(data_rows)

        if limit is None or limit < 0:
            selected_rows = data_rows
        else:
            selected_rows = data_rows[:limit]

        records: list[dict[str, Any]] = []
        for row in selected_rows:
            records.append(
                {
                    'time': self._read_value(row, index_map['time']),
                    'current': self._to_float(self._read_value(row, index_map['current'])),
                    'voltage': self._to_float(self._read_value(row, index_map['voltage'])),
                    'set_temperature': self._to_float(self._read_value(row, index_map['set_temperature'])),
                    'actual_temperature': self._to_float(self._read_value(row, index_map['actual_temperature'])),
                }
            )

        return records, total_count

    def _build_index_map(self, header: list[str]) -> dict[str, int]:
        index_map = {
            'time': 0,
            'current': 1,
            'voltage': 2,
            'set_temperature': 3,
            'actual_temperature': 4,
        }

        for index, name in enumerate(header):
            normalized = self._normalize_header(name)
            if normalized == 'time':
                index_map['time'] = index
            elif normalized == 'current':
                index_map['current'] = index
            elif normalized == 'voltage':
                index_map['voltage'] = index
            elif normalized == 'set_temperature':
                index_map['set_temperature'] = index
            elif normalized == 'actual_temperature':
                index_map['actual_temperature'] = index

        return index_map

    def _normalize_header(self, name: str) -> str:
        key = name.strip().lower()
        if key in {'time', '时间', 'timestamp'}:
            return 'time'
        if key in {'current', '电流'}:
            return 'current'
        if key in {'voltage', '电压'}:
            return 'voltage'
        if key in {'set_temperature', '设定温度', 'set temperature'}:
            return 'set_temperature'
        if key in {'actual_temperature', '实际温度', 'actual temperature', 'temperature', '温度'}:
            return 'actual_temperature'
        return key

    def _sanitize_filename(self, value: str) -> str:
        cleaned = ''.join(char if char.isalnum() or char in {'_', '-', '一', '二', '三', '四', '五', '六', '七', '八', '九', '十', '升', '温', '恒', '降', '实', '验'} else '_' for char in value.strip())
        compacted = '_'.join(part for part in cleaned.split('_') if part)
        return compacted or 'file'

    def _resolve_local_file_path(self, db_file_path: str, stored_name: str) -> Path:
        def _with_legacy_fallback(name: str) -> Path:
            current_path = self._data_dir / name
            if current_path.exists():
                return current_path
            legacy_path = self._legacy_data_dir / name
            if legacy_path.exists():
                return legacy_path
            return current_path

        if db_file_path:
            normalized = db_file_path.replace('\\', '/')
            suffix = normalized.split('/app/uploads/data/', 1)
            if len(suffix) == 2 and suffix[1]:
                return _with_legacy_fallback(Path(suffix[1]).name)
            return _with_legacy_fallback(Path(normalized).name)
        return _with_legacy_fallback(stored_name)

    def _read_value(self, row: list[str], index: int) -> str:
        if index < len(row):
            return row[index].strip()
        return ''

    def _read_excel_rows(self, file_path: Path) -> list[list[str]]:
        suffix = file_path.suffix.lower()
        if suffix == '.xlsx':
            return self._read_xlsx_rows(file_path)
        if suffix == '.xls':
            return self._read_xls_rows(file_path)
        if suffix == '.csv':
            return self._read_csv_rows(file_path)
        return []

    def _read_xlsx_rows(self, file_path: Path) -> list[list[str]]:
        workbook = load_workbook(file_path, data_only=True, read_only=True)
        try:
            sheet = workbook.active
            rows: list[list[str]] = []
            for row in sheet.iter_rows(values_only=True):
                cells = [self._cell_to_text(cell) for cell in row]
                if any(cell != '' for cell in cells):
                    rows.append(cells)
            return rows
        finally:
            workbook.close()

    def _read_xls_rows(self, file_path: Path) -> list[list[str]]:
        workbook = xlrd.open_workbook(file_path)
        sheet = workbook.sheet_by_index(0)
        rows: list[list[str]] = []
        for row_index in range(sheet.nrows):
            cells = [self._cell_to_text(sheet.cell_value(row_index, col_index)) for col_index in range(sheet.ncols)]
            if any(cell != '' for cell in cells):
                rows.append(cells)
        return rows

    def _read_csv_rows(self, file_path: Path) -> list[list[str]]:
        encodings = ['utf-8-sig', 'utf-8', 'gbk', 'gb2312']

        for encoding in encodings:
            try:
                with file_path.open('r', encoding=encoding, newline='') as file:
                    reader = csv.reader(file)
                    rows: list[list[str]] = []
                    for row in reader:
                        cells = [self._cell_to_text(cell) for cell in row]
                        if any(cell != '' for cell in cells):
                            rows.append(cells)
                    return rows
            except UnicodeDecodeError:
                continue

        raise ValueError('CSV 文件编码无法识别，请保存为 UTF-8 或 GBK 后重试')

    def _cell_to_text(self, value: Any) -> str:
        if value is None:
            return ''
        if isinstance(value, datetime):
            return value.strftime('%Y-%m-%d %H:%M:%S')
        return str(value).strip()

    def _to_float(self, value: str) -> float | None:
        if value == '':
            return None
        try:
            return float(value)
        except ValueError:
            return None

    def _parse_int_id(self, value: str, field_name: str) -> int:
        try:
            return int(value)
        except (TypeError, ValueError) as error:
            raise ValueError(f'{field_name}格式错误') from error

    def get_excel_template_bytes(self) -> bytes:
        workbook = Workbook()
        sheet = workbook.active
        sheet.title = '数据模板'
        sheet.append(['时间', '电流', '电压', '设定温度', '实际温度'])
        sheet.append(['2026/1/22 16:38:01', 1.2, 220.5, 37.0, 36.8])

        output = io.BytesIO()
        workbook.save(output)
        workbook.close()
        return output.getvalue()

    # ── quality ───────────────────────────────────────────────────────────────

    def get_quality_config(self) -> list[dict[str, Any]]:
        with db_session() as session:
            rows = session.query(DataQualityConfig).order_by(DataQualityConfig.id.asc()).all()
            return [
                {
                    'id': item.id,
                    'field_name': item.field_name,
                    'label': item.label,
                    'unit': item.unit,
                    'min_value': item.min_value,
                    'max_value': item.max_value,
                }
                for item in rows
            ]

    def assess_file_quality(self, file_id: str) -> dict[str, Any]:
        file_db_id = self._parse_int_id(file_id, '文件ID')

        with db_session() as session:
            file_meta = session.query(DataFile).filter(DataFile.id == file_db_id).first()
            if not file_meta:
                raise ValueError('文件不存在')

        target_path = self._resolve_local_file_path(file_meta.file_path, file_meta.stored_name)
        if not target_path.exists():
            raise ValueError('文件已丢失，请重新上传')

        quality_config = self._get_quality_config_map()

        all_rows = self._read_all_excel_rows(target_path)
        if not all_rows:
            return self._empty_quality_result()

        first_row = [item.strip() for item in all_rows[0]]
        has_header = any(
            self._normalize_header(item) in {'time', 'current', 'voltage', 'set_temperature', 'actual_temperature'}
            for item in first_row
        )

        if has_header:
            header = first_row
            data_rows = all_rows[1:]
        else:
            header = ['time', 'current', 'voltage', 'set_temperature', 'actual_temperature']
            data_rows = all_rows

        index_map = self._build_index_map(header)
        total = len(data_rows)
        if total == 0:
            return self._empty_quality_result()

        measurement_fields = ['current', 'voltage', 'set_temperature', 'actual_temperature']

        complete_count = 0
        continuous_count = 0
        accurate_count = 0
        has_measurement_count = 0

        for row in data_rows:
            field_values: dict[str, float | None] = {}
            for field in measurement_fields:
                val_str = self._read_value(row, index_map[field])
                field_values[field] = self._to_float(val_str)

            any_present = any(v is not None for v in field_values.values())
            all_present = all(v is not None for v in field_values.values())

            if any_present:
                continuous_count += 1
                has_measurement_count += 1

                in_range = True
                for field, val in field_values.items():
                    if val is None:
                        continue
                    cfg = quality_config.get(field, {})
                    min_val = cfg.get('min_value')
                    max_val = cfg.get('max_value')
                    if min_val is not None and val < min_val:
                        in_range = False
                        break
                    if max_val is not None and val > max_val:
                        in_range = False
                        break
                if in_range:
                    accurate_count += 1

            if all_present:
                complete_count += 1

        completeness = round(complete_count / total * 100, 2) if total > 0 else 0.0
        continuity = round(continuous_count / total * 100, 2) if total > 0 else 0.0
        accuracy = round(accurate_count / has_measurement_count * 100, 2) if has_measurement_count > 0 else 0.0

        return {
            'total': total,
            'complete_count': complete_count,
            'continuous_count': continuous_count,
            'accurate_count': accurate_count,
            'has_measurement_count': has_measurement_count,
            'completeness': completeness,
            'continuity': continuity,
            'accuracy': accuracy,
            'quality_config': list(quality_config.values()),
        }

    def _get_quality_config_map(self) -> dict[str, dict[str, Any]]:
        with db_session() as session:
            rows = session.query(DataQualityConfig).all()
            return {
                item.field_name: {
                    'field_name': item.field_name,
                    'label': item.label,
                    'unit': item.unit,
                    'min_value': item.min_value,
                    'max_value': item.max_value,
                }
                for item in rows
            }

    @staticmethod
    def _empty_quality_result() -> dict[str, Any]:
        return {
            'total': 0,
            'complete_count': 0,
            'continuous_count': 0,
            'accurate_count': 0,
            'has_measurement_count': 0,
            'completeness': 0.0,
            'continuity': 0.0,
            'accuracy': 0.0,
            'quality_config': [],
        }

    def _read_all_excel_rows(self, file_path: Path) -> list[list[str]]:
        """Read all rows including fully-empty rows (for continuity check)."""
        suffix = file_path.suffix.lower()
        if suffix == '.xlsx':
            return self._read_xlsx_rows_all(file_path)
        if suffix == '.xls':
            return self._read_xls_rows_all(file_path)
        if suffix == '.csv':
            return self._read_csv_rows_all(file_path)
        return []

    def _read_xlsx_rows_all(self, file_path: Path) -> list[list[str]]:
        workbook = load_workbook(file_path, data_only=True, read_only=True)
        try:
            sheet = workbook.active
            rows: list[list[str]] = []
            for row in sheet.iter_rows(values_only=True):
                cells = [self._cell_to_text(cell) for cell in row]
                rows.append(cells)
            return rows
        finally:
            workbook.close()

    def _read_xls_rows_all(self, file_path: Path) -> list[list[str]]:
        workbook = xlrd.open_workbook(file_path)
        sheet = workbook.sheet_by_index(0)
        rows: list[list[str]] = []
        for row_index in range(sheet.nrows):
            cells = [self._cell_to_text(sheet.cell_value(row_index, col_index)) for col_index in range(sheet.ncols)]
            rows.append(cells)
        return rows

    def _read_csv_rows_all(self, file_path: Path) -> list[list[str]]:
        encodings = ['utf-8-sig', 'utf-8', 'gbk', 'gb2312']
        for encoding in encodings:
            try:
                with file_path.open('r', encoding=encoding, newline='') as file:
                    reader = csv.reader(file)
                    rows: list[list[str]] = []
                    for row in reader:
                        cells = [self._cell_to_text(cell) for cell in row]
                        rows.append(cells)
                    return rows
            except UnicodeDecodeError:
                continue
        raise ValueError('CSV 文件编码无法识别，请保存为 UTF-8 或 GBK 后重试')
