services.py

import logging
from collections import OrderedDict

from openpyxl.cell.cell import ILLEGAL_CHARACTERS_RE
from django.db.models import Prefetch
from django.utils import timezone

from partner_programs.models import (
    PartnerProgram,
    PartnerProgramField,
    PartnerProgramFieldValue,
    PartnerProgramProject,
    PartnerProgramUserProfile,
)
from project_rates.models import Criteria, ProjectScore
from projects.models import Project

logger = logging.getLogger()


def publish_finished_program_projects(now=None) -> int:
    if now is None:
        now = timezone.now()

    program_ids = PartnerProgram.objects.filter(
        publish_projects_after_finish=True,
        datetime_finished__lte=now,
    ).values_list("id", flat=True)
    if not program_ids.exists():
        return 0

    link_project_ids = PartnerProgramProject.objects.filter(
        partner_program_id__in=program_ids
    ).values_list("project_id", flat=True)
    profile_project_ids = PartnerProgramUserProfile.objects.filter(
        partner_program_id__in=program_ids,
        project_id__isnull=False,
    ).values_list("project_id", flat=True)
    project_ids = link_project_ids.union(profile_project_ids)

    return Project.objects.filter(
        id__in=project_ids,
        is_public=False,
        draft=False,
    ).update(is_public=True)


class ProjectScoreDataPreparer:
    """
    Data preparer about project_rates by experts.
    """

    USER_ERROR_FIELDS = {
        "Фамилия": "ОШИБКА",
        "Имя": "ОШИБКА",
        "Отчество": "ОШИБКА",
        "Email": "ОШИБКА",
        "Регион_РФ": "ОШИБКА",
        "Учебное_заведение": "ОШИБКА",
        "Название_учебного_заведения": "ОШИБКА",
        "Класс_курс": "ОШИБКА",
    }

    EXPERT_ERROR_FIELDS = {"Фамилия эксперта": "ОШИБКА"}

    def __init__(
        self,
        user_profiles: dict[int, PartnerProgramUserProfile],
        scores: dict[int, list[ProjectScore]],
        project_id: int,
        program_id: int,
    ):
        self._project_id = project_id
        self._user_profiles = user_profiles
        self._scores = scores
        self._program_id = program_id

    def get_project_user_info(self) -> dict[str, str]:
        try:
            user_program_profile: PartnerProgramUserProfile = self._user_profiles.get(
                self._project_id
            )
            user_program_profile_json: dict = (
                user_program_profile.partner_program_data if user_program_profile else {}
            )

            user_info: dict[str, str] = {
                "Фамилия": (
                    user_program_profile.user.last_name if user_program_profile else ""
                ),
                "Имя": (
                    user_program_profile.user.first_name if user_program_profile else ""
                ),
                "Отчество": (
                    user_program_profile.user.patronymic if user_program_profile else ""
                ),
                "Email": (
                    user_program_profile_json.get("email")
                    if user_program_profile_json.get("email")
                    else user_program_profile.user.email
                ),
                "Регион_РФ": user_program_profile_json.get("region", ""),
                "Учебное_заведение": user_program_profile_json.get("education_type", ""),
                "Название_учебного_заведения": user_program_profile_json.get(
                    "institution_name", ""
                ),
                "Класс_курс": user_program_profile_json.get("class_course", ""),
            }
            return user_info
        except Exception as e:
            logger.error(
                f"Prepare export rates data about user error: {str(e)}", exc_info=True
            )
            return self.USER_ERROR_FIELDS

    def get_project_expert_info(self) -> dict[str, str]:
        try:
            project_scores: list[ProjectScore] = self._scores.get(self._project_id, [])
            first_score = project_scores[0] if project_scores else None
            expert_last_name: dict[str, str] = {
                "Фамилия эксперта": first_score.user.last_name if first_score else ""
            }
            return expert_last_name
        except Exception as e:
            logger.error(
                f"Prepare export rates data about expert error: {str(e)}", exc_info=True
            )
            return self.EXPERT_ERROR_FIELDS

    def get_project_scores_info(self) -> dict[str, str]:
        try:
            project_scores_dict = {}
            project_scores: list[ProjectScore] = self._scores.get(self._project_id, [])
            score_info_with_out_comment: dict[str, str] = {
                score.criteria.name: score.value
                for score in project_scores
                if score.criteria.name != "Комментарий"
            }
            project_scores_dict.update(score_info_with_out_comment)
            comment = next(
                (
                    score
                    for score in project_scores
                    if score.criteria.name == "Комментарий"
                ),
                None,
            )
            if comment is not None:
                project_scores_dict["Комментарий"] = comment.value
            return project_scores_dict
        except Exception as e:
            logger.error(
                f"Prepare export rates data about project_scores error: {str(e)}",
                exc_info=True,
            )
            return {
                criteria.name: "ОШИБКА"
                for criteria in Criteria.objects.filter(
                    partner_program__id=self._program_id
                )
            }


BASE_COLUMNS = [
    ("row_number", "№ п/п"),
    ("project_name", "Название проекта"),
    ("project_description", "Описание проекта"),
    ("project_region", "Регион проекта"),
    ("project_presentation", "Ссылка на презентацию"),
    ("team_size", "Количество человек в команде"),
    ("team_members", "Состав команды"),
    ("leader_full_name", "Имя фамилия лидера"),
]
EXCEL_CELL_MAX = 32767  # лимит символов в ячейке Excel


def sanitize_excel_value(value):
    """
    Приводит значение к безопасному для openpyxl виду:
    - None -> ""
    - для строк: вычищает запрещённые символы, нормализует переносы строк,
      и обрезает до лимита Excel (32767).
    - для чисел/булевых оставляет как есть.
    """
    if value is None:
        return ""

    if isinstance(value, (int, float, bool)):
        return value

    text = str(value)
    text = text.replace("\r\n", "\n").replace("\r", "\n")
    text = ILLEGAL_CHARACTERS_RE.sub(" ", text)

    if len(text) > EXCEL_CELL_MAX:
        text = text[: EXCEL_CELL_MAX - 3] + "..."

    return text


def _leader_full_name(user):
    if not user:
        return ""
    if hasattr(user, "get_full_name") and callable(user.get_full_name):
        full = user.get_full_name()
        if full:
            return full
    first = getattr(user, "first_name", "") or ""
    last = getattr(user, "last_name", "") or ""
    return (first + " " + last).strip() or getattr(user, "username", "") or str(user.pk)


def _calc_team_size(project):
    prefetched_collaborators = getattr(project, "_prefetched_collaborators", None)
    if prefetched_collaborators is not None:
        return 1 + len(prefetched_collaborators)

    try:
        if hasattr(project, "get_collaborators_user_list"):
            return 1 + len(project.get_collaborators_user_list())
        if hasattr(project, "collaborator_set"):
            return 1 + project.collaborator_set.count()
    except Exception:
        pass
    return 1


def _team_members(project) -> str:
    members: list[str] = []
    seen_ids: set[int | None] = set()

    leader = getattr(project, "leader", None)
    if leader:
        leader_name = _leader_full_name(leader)
        if leader_name:
            members.append(leader_name)
        seen_ids.add(getattr(leader, "id", None))

    collaborators = getattr(project, "_prefetched_collaborators", None)
    if collaborators is None:
        collaborators = (
            project.collaborator_set.select_related("user").all()
            if hasattr(project, "collaborator_set")
            else []
        )

    for collaborator in collaborators:
        user = getattr(collaborator, "user", None)
        if not user:
            continue
        user_id = getattr(user, "id", None)
        if user_id in seen_ids:
            continue
        name = _leader_full_name(user)
        if not name:
            continue
        members.append(name)
        seen_ids.add(user_id)

    return "\n".join(members)


def build_program_field_columns(program) -> list[tuple[str, str]]:
    program_fields = program.fields.all().order_by("pk")
    return [
        (f"name:{program_field.name}", program_field.label)
        for program_field in program_fields
    ]


def row_dict_for_link(
    program_project_link,
    extra_field_keys_order: list[str],
    row_number: int,
) -> OrderedDict:
    """
    program_project_link: PartnerProgramProject
    extra_field_keys_order: список псевдоключей "name:<field.name>" в нужном порядке
    row_number: порядковый номер строки в Excel (начиная с 1)
    """
    project = program_project_link.project
    row = OrderedDict()

    row["row_number"] = row_number

    row["project_name"] = project.name or ""
    row["project_description"] = project.description or ""
    row["project_region"] = project.region or ""
    row["project_presentation"] = project.presentation_address or ""
    row["team_size"] = _calc_team_size(project)
    row["team_members"] = _team_members(project)
    row["leader_full_name"] = _leader_full_name(getattr(project, "leader", None))

    values_map: dict[str, str] = {}
    prefetched_values = getattr(program_project_link, "_prefetched_field_values", None)
    field_values_iterable = (
        prefetched_values
        if prefetched_values is not None
        else program_project_link.field_values.all()
    )

    for field_value in field_values_iterable:
        if (
            field_value.field.partner_program_id
            == program_project_link.partner_program_id
        ):
            values_map[f"name:{field_value.field.name}"] = field_value.get_value()

    for field_key in extra_field_keys_order:
        row[field_key] = values_map.get(field_key, "")

    return row


def prepare_project_scores_export_data(program_id: int) -> list[dict]:
    """
    Готовит данные для выгрузки оценок проектов.
    Порядок колонок: название проекта → фамилия эксперта → доп. поля программы →
    критерии → комментарий.
    Если у проекта несколько экспертов, на каждый проект-эксперт создаётся отдельная строка.
    """
    criterias = list(
        Criteria.objects.filter(partner_program__id=program_id)
        .select_related("partner_program")
        .order_by("id")
    )
    if not criterias:
        return []

    comment_criteria = next(
        (criteria for criteria in criterias if criteria.name == "Комментарий"),
        None,
    )
    criterias_without_comment = [
        criteria for criteria in criterias if criteria != comment_criteria
    ]

    program_fields = list(
        PartnerProgramField.objects.filter(partner_program_id=program_id).order_by("id")
    )

    scores = (
        ProjectScore.objects.filter(criteria__in=criterias)
        .select_related("user", "criteria", "project")
        .order_by("project_id", "criteria_id", "id")
    )
    scores_dict: dict[int, list[ProjectScore]] = {}
    for score in scores:
        scores_dict.setdefault(score.project_id, []).append(score)

    if not scores_dict:
        empty_row: dict[str, str] = {
            "Название проекта": "",
            "Фамилия эксперта": "",
        }
        for field in program_fields:
            empty_row[field.label] = ""
        for criteria in criterias_without_comment:
            empty_row[criteria.name] = ""
        if comment_criteria:
            empty_row["Комментарий"] = ""
        return [empty_row]

    project_ids = list(scores_dict.keys())

    field_values_prefetch = Prefetch(
        "field_values",
        queryset=PartnerProgramFieldValue.objects.select_related("field").filter(
            program_project__partner_program_id=program_id,
            program_project__project_id__in=project_ids,
        ),
        to_attr="_prefetched_field_values",
    )
    program_projects = (
        PartnerProgramProject.objects.filter(
            partner_program_id=program_id, project_id__in=project_ids
        )
        .select_related("project")
        .prefetch_related(field_values_prefetch)
    )
    program_project_by_project_id: dict[int, PartnerProgramProject] = {
        link.project_id: link for link in program_projects
    }

    prepared_projects_rates_data: list[dict] = []
    for project_id, project_scores in scores_dict.items():
        project_link = program_project_by_project_id.get(project_id)
        project = (
            project_link.project
            if project_link
            else (project_scores[0].project if project_scores else None)
        )

        field_values_map: dict[int, str] = {}
        field_values = (
            getattr(project_link, "_prefetched_field_values", None)
            if project_link
            else None
        )
        if field_values:
            for field_value in field_values:
                field_values_map[field_value.field_id] = field_value.get_value()

        scores_by_expert: dict[int, list[ProjectScore]] = {}
        for score in project_scores:
            scores_by_expert.setdefault(score.user_id, []).append(score)

        for _, expert_scores in scores_by_expert.items():
            row_data: dict[str, str] = {}
            row_data["Название проекта"] = (
                getattr(project, "name", "") if project else ""
            )
            row_data["Фамилия эксперта"] = (
                expert_scores[0].user.last_name if expert_scores else ""
            )

            for field in program_fields:
                row_data[field.label] = field_values_map.get(field.id, "")

            scores_map: dict[int, str] = {
                score.criteria_id: score.value for score in expert_scores
            }

            for criteria in criterias_without_comment:
                row_data[criteria.name] = scores_map.get(criteria.id, "")

            if comment_criteria:
                row_data["Комментарий"] = scores_map.get(comment_criteria.id, "")

            prepared_projects_rates_data.append(row_data)

    return prepared_projects_rates_data