Job 批次設置匯入匯出 Implementation Plan

{
  "status": true,
  "data": {
    "valid_count": 30,
    "error_count": 2,
    "items": [
      {
        "row": 2,
        "job_uid": "uuid-string",
        "job_name": "任務名稱",
        "control_name": "控制項名稱",
        "ao_name": "檢查項目名稱",
        "job_type": "general",
        "assignees": "user1, user2",
        "departments": "部門A",
        "devices": "設備1, 設備2",
        "errors": []
      },
      {
        "row": 5,
        "job_uid": "uuid-string",
        "job_name": "任務名稱",
        "control_name": "控制項名稱",
        "ao_name": "檢查項目名稱",
        "job_type": "invalid",
        "assignees": "unknown_user",
        "departments": "",
        "devices": "",
        "errors": [
          {"field": "job_type", "message": "無效的任務類型，允許值：general, survey"},
          {"field": "assignees", "message": "使用者 unknown_user 不存在"}
        ]
      }
    ]
  }
}

{
  "status": true,
  "data": {
    "updated_count": 30
  }
}

"""Job 批次設置 — 匯入匯出 Serializers"""
from marshmallow import Schema, fields


class JobImportValidationErrorSchema(Schema):
    field = fields.String()
    message = fields.String()


class JobImportRowSchema(Schema):
    """匯入驗證結果的單筆資料"""
    row = fields.Integer()
    job_uid = fields.String()
    job_name = fields.String(allow_none=True, load_default=None)
    control_name = fields.String(allow_none=True, load_default=None)
    ao_name = fields.String(allow_none=True, load_default=None)
    job_type = fields.String(allow_none=True, load_default=None)
    assignees = fields.String(allow_none=True, load_default=None)
    departments = fields.String(allow_none=True, load_default=None)
    devices = fields.String(allow_none=True, load_default=None)
    errors = fields.List(fields.Nested(JobImportValidationErrorSchema), load_default=[])


class JobImportConfirmItemSchema(Schema):
    """確認匯入的單筆資料"""
    job_uid = fields.String(required=True)
    job_type = fields.String(required=True)
    assignees = fields.String(required=True)
    departments = fields.String(allow_none=True, load_default=None)
    devices = fields.String(allow_none=True, load_default=None)


class JobImportValidationResponseSchema(Schema):
    valid_count = fields.Integer()
    error_count = fields.Integer()
    items = fields.List(fields.Nested(JobImportRowSchema))


class JobImportConfirmRequestSchema(Schema):
    items = fields.List(fields.Nested(JobImportConfirmItemSchema), required=True)


class JobImportConfirmResponseSchema(Schema):
    updated_count = fields.Integer()

git add api/grc/serializers/job_import.py
git commit -m "feat(job-import): add marshmallow schemas for batch job setup"

    # ── Job Import ─────────────────────────────────────────────────────
    GRC_JOB_IMPORT_INVALID_FILE      = ("上傳檔案格式無效，請使用匯出的 Excel 範本",  "GRC_400003")
    GRC_JOB_IMPORT_FILE_TOO_LARGE    = ("上傳檔案超過大小限制（10MB）",              "GRC_400004")

git add common/code/grc_error_code.py
git commit -m "feat(job-import): add job import error codes"

"""Job 批次設置 — 匯出資料查詢（infra 層）

查詢 AP 下 Group → Control → AO → Job 完整階層，
以及每個 Job 的 assignees / departments / devices 名稱。
"""
from collections import defaultdict

from jedi_common.session.database.session_context import get_session
from jedi_auth.infra.models.user import User
from jedi_auth.infra.models.org_unit import OrgUnit
from jedi_device.infra.models.device import Device
from jedi_flow_engine.common.enum.job_code import JobType
from jedi_flow_engine.infra.models.job_execution import JobExecution

from jedi_oscal.infra.model.ap.assessment_plan import OscalAssessmentPlan
from jedi_oscal.infra.model.ap.assessment_plan_control import OscalAssessmentPlanControl
from jedi_oscal.infra.model.ap.assessment_plan_group import OscalAssessmentPlanGroup
from jedi_oscal.infra.model.ap.assessment_plan_task import OscalAssessmentPlanTask
from jedi_oscal.infra.model.ap.assessment_task_control import OscalAssessmentTaskControl

from infra.associations.model.assessment_plan_task_workflow_execution_mapping import (
    AssessmentPlanTaskWorkflowExecutionMapping,
)
from infra.grc.model.job_execution_device import JobExecutionDevice
from infra.grc.model.job_execution_org_unit import JobExecutionOrgUnit
from infra.participant.model.task_assignee import TaskAssignee


class JobExportQuery:
    """查詢 AP 下所有 Job 的匯出資料"""

    def get_export_data(self, ap_uid: str) -> list:
        """取得 AP 下所有 Job 的匯出資料

        Returns:
            list of dict, 每個 dict 代表一個 job row:
            {
                "job_uid": str,
                "group_name": str,
                "control_code": str,
                "control_name": str,
                "ao_name": str,
                "job_name": str,
                "job_type": str,
                "assignees": str (逗號分隔 login_name),
                "departments": str (逗號分隔部門名稱),
                "devices": str (逗號分隔設備名稱),
            }
        """
        session = get_session()

        # 1. Resolve AP
        ap = session.query(OscalAssessmentPlan).filter(
            OscalAssessmentPlan.uid == ap_uid
        ).first()
        if not ap:
            return []

        # 2. Groups（含子群組，確保所有層級的 control 都能找到 group）
        groups = (
            session.query(OscalAssessmentPlanGroup)
            .filter(OscalAssessmentPlanGroup.assessment_plan_id == ap.id)
            .order_by(OscalAssessmentPlanGroup.order_no)
            .all()
        )
        group_by_id = {g.id: g for g in groups}

        # 3. Controls
        controls = (
            session.query(OscalAssessmentPlanControl)
            .filter(OscalAssessmentPlanControl.assessment_plan_id == ap.id)
            .order_by(OscalAssessmentPlanControl.order_no)
            .all()
        )
        control_by_id = {c.id: c for c in controls}

        # 4. Task-Control mappings
        tc_rows = (
            session.query(OscalAssessmentTaskControl)
            .filter(
                OscalAssessmentTaskControl.control_id.in_([c.id for c in controls])
            )
            .all()
        )
        task_to_control = {}
        for tc in tc_rows:
            task_to_control[tc.task_id] = tc.control_id

        # 5. Tasks (AOs)
        task_ids = list(task_to_control.keys())
        if not task_ids:
            return []

        tasks = (
            session.query(OscalAssessmentPlanTask)
            .filter(OscalAssessmentPlanTask.id.in_(task_ids))
            .order_by(OscalAssessmentPlanTask.sequence)
            .all()
        )
        task_by_id = {t.id: t for t in tasks}

        # 6. AO → workflow_execution mappings
        wf_mappings = (
            session.query(AssessmentPlanTaskWorkflowExecutionMapping)
            .filter(
                AssessmentPlanTaskWorkflowExecutionMapping.assessment_plan_task_id.in_(task_ids)
            )
            .all()
        )
        task_to_wf_exec = {m.assessment_plan_task_id: m.workflow_execution_id for m in wf_mappings}

        # 7. Jobs (USER type only)
        wf_exec_ids = list(set(task_to_wf_exec.values()))
        jobs = []
        if wf_exec_ids:
            jobs = (
                session.query(JobExecution)
                .filter(
                    JobExecution.workflow_execution_id.in_(wf_exec_ids),
                    JobExecution.type == JobType.USER,
                )
                .order_by(JobExecution.id)
                .all()
            )

        # Build wf_exec_id → task_id reverse map
        wf_exec_to_task = {v: k for k, v in task_to_wf_exec.items()}

        # 8. Batch load assignees, departments, devices for all jobs
        job_ids = [j.id for j in jobs]

        # Assignees: login_name
        assignee_map = defaultdict(list)
        if job_ids:
            rows = (
                session.query(TaskAssignee.task_id, User.login_name)
                .join(User, User.id == TaskAssignee.user_id)
                .filter(TaskAssignee.task_id.in_(job_ids))
                .all()
            )
            for job_id, login_name in rows:
                assignee_map[job_id].append(login_name)

        # Departments: name
        dept_map = defaultdict(list)
        if job_ids:
            rows = (
                session.query(JobExecutionOrgUnit.job_execution_id, OrgUnit.name)
                .join(OrgUnit, OrgUnit.id == JobExecutionOrgUnit.org_unit_id)
                .filter(JobExecutionOrgUnit.job_execution_id.in_(job_ids))
                .all()
            )
            for job_id, name in rows:
                dept_map[job_id].append(name)

        # Devices: name
        device_map = defaultdict(list)
        if job_ids:
            rows = (
                session.query(JobExecutionDevice.job_execution_id, Device.name)
                .join(Device, Device.id == JobExecutionDevice.device_id)
                .filter(JobExecutionDevice.job_execution_id.in_(job_ids))
                .all()
            )
            for job_id, name in rows:
                device_map[job_id].append(name)

        # 9. Assemble rows
        result = []
        for job in jobs:
            task_id = wf_exec_to_task.get(job.workflow_execution_id)
            task = task_by_id.get(task_id) if task_id else None
            ctrl_id = task_to_control.get(task_id) if task_id else None
            ctrl = control_by_id.get(ctrl_id) if ctrl_id else None
            group = group_by_id.get(ctrl.group_id) if ctrl else None

            result.append({
                "job_uid": str(job.uid),
                "group_name": (group.description or group.name or "") if group else "",
                "control_code": (ctrl.control_id or "") if ctrl else "",
                "control_name": (ctrl.control_title or "") if ctrl else "",
                "ao_name": (task.title or "") if task else "",
                "job_name": job.name or "",
                "job_type": job.job_type or "general",
                "assignees": ", ".join(sorted(assignee_map.get(job.id, []))),
                "departments": ", ".join(sorted(dept_map.get(job.id, []))),
                "devices": ", ".join(sorted(device_map.get(job.id, []))),
            })

        return result

git add infra/grc/repository/job_export_query.py
git commit -m "feat(job-import): add infra query for job export data"

"""Job 批次設置 — 匯入匯出 Service

負責：
- 匯出：查詢 AP 下所有 Job，產生 Excel（A 欄隱藏 job_uid）
- 匯入：解析 Excel、驗證資料、批次呼叫 JobService.update_job()
"""
import os
from io import BytesIO

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.workbook import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

from jedi_common.handler.exception import BadRequestError, NotFound
from jedi_common.session.database.db import transaction
from jedi_common.session.database.session_context import get_session
from jedi_auth.infra.models.user import User
from jedi_auth.infra.models.org_unit import OrgUnit
from jedi_device.infra.models.device import Device
from jedi_flow_engine.infra.models.job_execution import JobExecution

from app.grc.service.job_service import JobService
from infra.grc.repository.job_export_query import JobExportQuery
from common.code.grc_error_code import GrcErrorCode

VALID_JOB_TYPES = ["general", "survey"]

HEADER_LABELS = [
    "job_uid",            # A (隱藏)
    "控制項群組",          # B
    "控制項識別碼",        # C
    "控制項名稱",          # D
    "檢查項目名稱",        # E
    "任務名稱",            # F
    "任務類型",            # G
    "指派人員",            # H
    "參與部門",            # I
    "設備",               # J
]

HEADER_FONT_WHITE = Font(bold=True, size=11, color="FFFFFF")
HEADER_FILL = PatternFill("solid", start_color="4472C4")
READONLY_FILL = PatternFill("solid", start_color="F2F2F2")
EDITABLE_FILL = PatternFill("solid", start_color="FFFFC0")
WRAP_ALIGNMENT = Alignment(wrap_text=True, vertical="top")
THIN_BORDER = Border(
    left=Side(style="thin"),
    right=Side(style="thin"),
    top=Side(style="thin"),
    bottom=Side(style="thin"),
)

MAX_FILE_SIZE = 10 * 1024 * 1024


class JobImportService:
    """Job 批次設置匯入匯出"""

    def __init__(
        self,
        job_service: JobService,
        job_export_query: JobExportQuery,
    ):
        self._job_svc = job_service
        self._export_query = job_export_query

    @transaction
    def export_excel(self, project_uid: str, ap_uid: str) -> BytesIO:
        """匯出 AP 下所有 Job 為 Excel"""
        rows = self._export_query.get_export_data(ap_uid)
        if not rows:
            raise NotFound(GrcErrorCode.GRC_AP_NOT_FOUND)

        wb = Workbook()
        ws = wb.active
        ws.title = "任務批次設置"

        # Header
        ws.append(HEADER_LABELS)
        for col_idx in range(1, len(HEADER_LABELS) + 1):
            cell = ws.cell(row=1, column=col_idx)
            cell.font = HEADER_FONT_WHITE
            cell.fill = HEADER_FILL
            cell.alignment = WRAP_ALIGNMENT
            cell.border = THIN_BORDER

        # Column widths
        col_widths = [36, 20, 18, 25, 30, 25, 12, 30, 25, 25]
        for i, w in enumerate(col_widths, 1):
            ws.column_dimensions[chr(64 + i)].width = w

        # 隱藏 A 欄（job_uid）
        ws.column_dimensions["A"].hidden = True

        # 任務類型下拉 (G 欄)
        type_dv = DataValidation(
            type="list",
            formula1='"' + ",".join(VALID_JOB_TYPES) + '"',
            allow_blank=False,
        )
        type_dv.error = "請選擇有效的任務類型"
        type_dv.errorTitle = "無效值"
        ws.add_data_validation(type_dv)

        # Data rows
        for row_idx, data in enumerate(rows, start=2):
            ws.cell(row=row_idx, column=1, value=data["job_uid"])      # A: hidden
            ws.cell(row=row_idx, column=2, value=data["group_name"])    # B
            ws.cell(row=row_idx, column=3, value=data["control_code"]) # C
            ws.cell(row=row_idx, column=4, value=data["control_name"]) # D
            ws.cell(row=row_idx, column=5, value=data["ao_name"])      # E
            ws.cell(row=row_idx, column=6, value=data["job_name"])     # F
            ws.cell(row=row_idx, column=7, value=data["job_type"])     # G
            ws.cell(row=row_idx, column=8, value=data["assignees"])    # H
            ws.cell(row=row_idx, column=9, value=data["departments"])  # I
            ws.cell(row=row_idx, column=10, value=data["devices"])     # J

            # 框線全欄
            for col in range(1, 11):
                ws.cell(row=row_idx, column=col).border = THIN_BORDER

            # 唯讀灰底：A-F
            for col in range(1, 7):
                ws.cell(row=row_idx, column=col).fill = READONLY_FILL

            # 可編輯淡黃底：G-J
            for col in range(7, 11):
                ws.cell(row=row_idx, column=col).fill = EDITABLE_FILL

            # 自動換行：H, I, J（可能有多個值）
            for col in [8, 9, 10]:
                ws.cell(row=row_idx, column=col).alignment = WRAP_ALIGNMENT

            type_dv.add(ws.cell(row=row_idx, column=7))

        ws.freeze_panes = "B2"  # 凍結 A 欄 + 首行
        excel_bytes = BytesIO()
        wb.save(excel_bytes)
        excel_bytes.seek(0)
        return excel_bytes

    @staticmethod
    def _validate_file(file):
        filename = getattr(file, "filename", "") or ""
        if not filename.lower().endswith(".xlsx"):
            raise BadRequestError(GrcErrorCode.GRC_JOB_IMPORT_INVALID_FILE)
        file.seek(0, os.SEEK_END)
        size = file.tell()
        file.seek(0)
        if size > MAX_FILE_SIZE:
            raise BadRequestError(GrcErrorCode.GRC_JOB_IMPORT_FILE_TOO_LARGE)

    @transaction
    def validate_import(self, project_uid: str, ap_uid: str, file) -> dict:
        """解析 Excel 並驗證"""
        self._validate_file(file)
        session = get_session()

        try:
            wb = load_workbook(file, data_only=True)
            ws = wb.active
        except Exception:
            raise BadRequestError(GrcErrorCode.GRC_JOB_IMPORT_INVALID_FILE)

        # 驗證 header
        headers = [cell.value for cell in ws[1]]
        if headers[:len(HEADER_LABELS)] != HEADER_LABELS:
            raise BadRequestError(GrcErrorCode.GRC_JOB_IMPORT_INVALID_FILE)

        # 預載驗證資料
        # 1. 該 AP 下所有有效 job_uids
        valid_job_uids = set()
        export_rows = self._export_query.get_export_data(ap_uid)
        for r in export_rows:
            valid_job_uids.add(r["job_uid"])

        # 2. 所有 login_names（統一 lower）
        all_users = session.query(User.login_name).all()
        valid_login_names = {u.login_name.strip().lower() for u in all_users if u.login_name}

        # 3. 所有部門名稱（檢查重名）
        all_depts = session.query(OrgUnit.name).all()
        dept_name_counts = {}
        for d in all_depts:
            if d.name:
                dept_name_counts[d.name.strip()] = dept_name_counts.get(d.name.strip(), 0) + 1
        valid_dept_names = set(dept_name_counts.keys())
        duplicate_dept_names = {n for n, c in dept_name_counts.items() if c > 1}

        # 4. 所有設備名稱（檢查重名）
        all_devices = session.query(Device.name).all()
        device_name_counts = {}
        for d in all_devices:
            if d.name:
                device_name_counts[d.name.strip()] = device_name_counts.get(d.name.strip(), 0) + 1
        valid_device_names = set(device_name_counts.keys())
        duplicate_device_names = {n for n, c in device_name_counts.items() if c > 1}

        items = []
        seen_job_uids = set()

        for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=True), start=2):
            if not row or all(v is None or str(v).strip() == "" for v in row):
                continue

            job_uid = str(row[0]).strip() if row[0] else None
            job_type = str(row[6]).strip() if row[6] else None
            assignees_raw = str(row[7]).strip() if row[7] else ""
            depts_raw = str(row[8]).strip() if row[8] else ""
            devices_raw = str(row[9]).strip() if row[9] else ""
            errors = []

            # job_uid 驗證
            if not job_uid:
                errors.append({"field": "job_uid", "message": "任務識別碼不可為空"})
            elif job_uid not in valid_job_uids:
                errors.append({"field": "job_uid", "message": f"任務 {job_uid} 不屬於此稽核計畫"})
            elif job_uid in seen_job_uids:
                errors.append({"field": "job_uid", "message": "重複的任務識別碼"})
            if job_uid:
                seen_job_uids.add(job_uid)

            # 任務類型驗證（必填）
            if not job_type:
                errors.append({"field": "job_type", "message": "任務類型為必填"})
            elif job_type not in VALID_JOB_TYPES:
                errors.append({
                    "field": "job_type",
                    "message": f"無效的任務類型，允許值：{', '.join(VALID_JOB_TYPES)}",
                })

            # 指派人員驗證（必填）
            assignee_names = [n.strip() for n in assignees_raw.split(",") if n.strip()] if assignees_raw else []
            if not assignee_names:
                errors.append({"field": "assignees", "message": "指派人員為必填"})
            else:
                invalid_users = [n for n in assignee_names if n.lower() not in valid_login_names]
                if invalid_users:
                    errors.append({
                        "field": "assignees",
                        "message": f"使用者不存在：{', '.join(invalid_users)}",
                    })

            # 參與部門驗證（選填）
            dept_names = [n.strip() for n in depts_raw.split(",") if n.strip()] if depts_raw else []
            if dept_names:
                invalid_depts = [n for n in dept_names if n not in valid_dept_names]
                if invalid_depts:
                    errors.append({
                        "field": "departments",
                        "message": f"部門不存在：{', '.join(invalid_depts)}",
                    })
                ambiguous_depts = [n for n in dept_names if n in duplicate_dept_names]
                if ambiguous_depts:
                    errors.append({
                        "field": "departments",
                        "message": f"部門名稱重複，無法辨識，請改用 UI 設定：{', '.join(ambiguous_depts)}",
                    })

            # 設備驗證（選填）
            device_names = [n.strip() for n in devices_raw.split(",") if n.strip()] if devices_raw else []
            if device_names:
                invalid_devices = [n for n in device_names if n not in valid_device_names]
                if invalid_devices:
                    errors.append({
                        "field": "devices",
                        "message": f"設備不存在：{', '.join(invalid_devices)}",
                    })
                ambiguous_devices = [n for n in device_names if n in duplicate_device_names]
                if ambiguous_devices:
                    errors.append({
                        "field": "devices",
                        "message": f"設備名稱重複，無法辨識，請改用 UI 設定：{', '.join(ambiguous_devices)}",
                    })

            items.append({
                "row": row_idx,
                "job_uid": job_uid,
                "job_name": str(row[5]).strip() if row[5] else None,
                "control_name": str(row[3]).strip() if row[3] else None,
                "ao_name": str(row[4]).strip() if row[4] else None,
                "job_type": job_type,
                "assignees": assignees_raw,
                "departments": depts_raw,
                "devices": devices_raw,
                "errors": errors,
            })

        valid_count = sum(1 for i in items if not i["errors"])
        error_count = sum(1 for i in items if i["errors"])
        return {"valid_count": valid_count, "error_count": error_count, "items": items}

    @transaction
    def confirm_import(
        self, project_uid: str, ap_uid: str, items: list,
        user_id: int, is_admin: bool, curr_user: str,
    ) -> dict:
        """確認匯入 — 批次更新 Job"""
        session = get_session()
        updated_count = 0

        # 預載 login_name → uid mapping（統一 lower 比對）
        all_login_names = set()
        for item in items:
            names = [n.strip().lower() for n in (item.get("assignees") or "").split(",") if n.strip()]
            all_login_names.update(names)

        user_rows = (
            session.query(User.login_name, User.uid)
            .all()
        ) if all_login_names else []
        login_to_uid = {u.login_name.lower(): str(u.uid) for u in user_rows if u.login_name}

        # 預載部門 name → uid mapping
        all_dept_names = set()
        for item in items:
            names = [n.strip() for n in (item.get("departments") or "").split(",") if n.strip()]
            all_dept_names.update(names)

        dept_rows = (
            session.query(OrgUnit.name, OrgUnit.uid)
            .filter(OrgUnit.name.in_(all_dept_names))
            .all()
        ) if all_dept_names else []
        dept_to_uid = {d.name: str(d.uid) for d in dept_rows}

        # 預載設備 name → uid mapping
        all_device_names = set()
        for item in items:
            names = [n.strip() for n in (item.get("devices") or "").split(",") if n.strip()]
            all_device_names.update(names)

        device_rows = (
            session.query(Device.name, Device.uid)
            .filter(Device.name.in_(all_device_names))
            .all()
        ) if all_device_names else []
        device_to_uid = {d.name: str(d.uid) for d in device_rows}

        for item in items:
            job_uid = item.get("job_uid")
            if not job_uid:
                continue

            # 組裝 assignees: [{uid, is_approver: False}]（統一 lower 比對）
            assignee_names = [n.strip() for n in (item.get("assignees") or "").split(",") if n.strip()]
            assignees = [
                {"uid": login_to_uid[name.lower()], "is_approver": False}
                for name in assignee_names
                if name.lower() in login_to_uid
            ]

            # 組裝 department_uids
            dept_names = [n.strip() for n in (item.get("departments") or "").split(",") if n.strip()]
            department_uids = [dept_to_uid[n] for n in dept_names if n in dept_to_uid]

            # 組裝 device_uids
            device_names = [n.strip() for n in (item.get("devices") or "").split(",") if n.strip()]
            device_uids = [device_to_uid[n] for n in device_names if n in device_to_uid]

            self._job_svc.update_job(
                project_uid=project_uid,
                job_uid=job_uid,
                user_id=user_id,
                is_admin=is_admin,
                curr_user=curr_user,
                job_type=item.get("job_type"),
                assignees=assignees,
                department_uids=department_uids or None,
                device_uids=device_uids or None,
            )
            updated_count += 1

        return {"updated_count": updated_count}

git add app/grc/service/job_import_service.py
git commit -m "feat(job-import): add job import/export service"

"""Job 批次設置 — 匯入匯出 Routes

端點：
- GET  /grc/project/<pid>/ap/<apid>/jobs/export         -> 匯出 Excel
- POST /grc/project/<pid>/ap/<apid>/jobs/import          -> 上傳 + 驗證
- POST /grc/project/<pid>/ap/<apid>/jobs/import/confirm   -> 確認匯入
"""
from dependency_injector.wiring import inject, Provide
from flask import request, send_file
from flask_apispec import MethodResource, doc, use_kwargs, marshal_with
from flask_jwt_extended import jwt_required
from jedi_common.handler.exception import BadRequestError
from jedi_common.session.auth.auth_context import get_user_context

from api.grc.serializers.job_import import (
    JobImportValidationResponseSchema,
    JobImportConfirmRequestSchema,
    JobImportConfirmResponseSchema,
)
from app.grc.service.job_import_service import JobImportService
from common.code.grc_error_code import GrcErrorCode
from common.util.response_util import return_response
from di_containers.containers import Containers

AUTH_PARAMS = {"Authorization": {"description": "Bearer token", "in": "header", "type": "string"}}


class JobExportRoute(MethodResource):
    """GET /grc/project/<pid>/ap/<apid>/jobs/export"""

    @doc(description="匯出 Job 批次設置為 Excel", tags=["GRC Job Import/Export"], params=AUTH_PARAMS)
    @jwt_required()
    @inject
    def get(
        self,
        project_uid: str,
        ap_uid: str,
        job_import_service: JobImportService = Provide[
            Containers.grc_container.job_import_service
        ],
    ):
        excel_bytes = job_import_service.export_excel(project_uid, ap_uid)
        return send_file(
            excel_bytes,
            mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            as_attachment=True,
            download_name=f"job-setup-{ap_uid[:8]}.xlsx",
        )


class JobImportRoute(MethodResource):
    """POST /grc/project/<pid>/ap/<apid>/jobs/import"""

    @doc(description="上傳 Excel 並驗證 Job 設置資料", tags=["GRC Job Import/Export"], params=AUTH_PARAMS)
    @marshal_with(JobImportValidationResponseSchema, apply=False)
    @jwt_required()
    @inject
    def post(
        self,
        project_uid: str,
        ap_uid: str,
        job_import_service: JobImportService = Provide[
            Containers.grc_container.job_import_service
        ],
    ):
        file = request.files.get("file")
        if not file:
            raise BadRequestError(GrcErrorCode.GRC_JOB_IMPORT_INVALID_FILE)

        result = job_import_service.validate_import(project_uid, ap_uid, file)
        return return_response(True, JobImportValidationResponseSchema().dump(result))


class JobImportConfirmRoute(MethodResource):
    """POST /grc/project/<pid>/ap/<apid>/jobs/import/confirm"""

    @doc(description="確認匯入 — 批次更新 Job 設置", tags=["GRC Job Import/Export"], params=AUTH_PARAMS)
    @use_kwargs(JobImportConfirmRequestSchema, location="json", apply=False)
    @marshal_with(JobImportConfirmResponseSchema, apply=False)
    @jwt_required()
    @inject
    def post(
        self,
        project_uid: str,
        ap_uid: str,
        job_import_service: JobImportService = Provide[
            Containers.grc_container.job_import_service
        ],
    ):
        payload = request.get_json(silent=True) or {}
        data = JobImportConfirmRequestSchema().load(payload)
        user = get_user_context()
        result = job_import_service.confirm_import(
            project_uid, ap_uid, data["items"],
            user_id=user.id,
            is_admin=bool(getattr(user, "is_admin", False)),
            curr_user=user.login_name,
        )
        return return_response(True, JobImportConfirmResponseSchema().dump(result))

git add api/grc/routes/job_import_route.py
git commit -m "feat(job-import): add export/import/confirm API routes"

from api.grc.routes.job_import_route import (
    JobExportRoute,
    JobImportRoute,
    JobImportConfirmRoute,
)

# --- Job 批次設置匯入匯出 ---
api.add_resource(
    JobExportRoute,
    "/project/<project_uid>/ap/<ap_uid>/jobs/export",
)
api.add_resource(
    JobImportRoute,
    "/project/<project_uid>/ap/<ap_uid>/jobs/import",
)
api.add_resource(
    JobImportConfirmRoute,
    "/project/<project_uid>/ap/<ap_uid>/jobs/import/confirm",
)

from app.grc.service.job_import_service import JobImportService
from infra.grc.repository.job_export_query import JobExportQuery

# Job 批次設置匯入匯出
job_export_query = providers.Singleton(JobExportQuery)

job_import_service = providers.Factory(
    JobImportService,
    job_service=job_service,
    job_export_query=job_export_query,
)

git add api/grc/__init__.py di_containers/grc/grc_containers.py
git commit -m "feat(job-import): register routes and DI wiring"

# Job 批次設置匯入匯出

## 需求說明

新增 Job 批次設置的匯入匯出功能，讓使用者透過 Excel 一次設定專案內所有 Job 的任務類型、指派人員、參與部門、設備。

## 變更範圍

### 新增檔案
- `app/grc/service/job_import_service.py` — 匯入匯出 service
- `infra/grc/repository/job_export_query.py` — 匯出資料查詢（infra 層）
- `api/grc/routes/job_import_route.py` — API routes
- `api/grc/serializers/job_import.py` — Marshmallow schemas

### 修改檔案
- `api/grc/__init__.py` — 註冊新 route
- `di_containers/grc/grc_containers.py` — DI wiring
- `common/code/grc_error_code.py` — 新增 error codes

## API 變更

| Method | URL | 說明 |
|--------|-----|------|
| GET | `/api/1.0/grc/project/<pid>/ap/<apid>/jobs/export` | 匯出 Excel |
| POST | `/api/1.0/grc/project/<pid>/ap/<apid>/jobs/import` | 上傳 + 驗證 |
| POST | `/api/1.0/grc/project/<pid>/ap/<apid>/jobs/import/confirm` | 確認匯入 |

git add docs/changelog/2026-03-28-job-batch-setup-import-export.md docs/api_spec/2026-03-28-job-batch-setup-frontend-spec.md
git commit -m "docs: add changelog and frontend spec for job batch setup"