package com.erp.service;

import com.erp.common.BaseService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.*;

@Service
@Slf4j
public class DesignService extends BaseService {

    @Autowired private CommonService commonService;
    @Autowired private JdbcTemplate jdbcTemplate;

    // ============================================
    // 설계의뢰 (Design Request)
    // ============================================

    public Map<String, Object> getDesignRequestList(Map<String, Object> params) {
        commonService.applyCompanyCodeFilter(params);
        commonService.applyPagination(params);
        Number cntNum = sqlSession.selectOne("design.getDesignRequestListCnt", params);
        int totalCount = cntNum != null ? cntNum.intValue() : 0;
        List<Map<String, Object>> list = sqlSession.selectList("design.getDesignRequestList", params);
        return commonService.buildListResponse(list, totalCount, params);
    }

    public Map<String, Object> getDesignRequestDetail(String id, String companyCode) {
        List<Object> args = new ArrayList<>();
        args.add(id);
        String companyFilter = "";
        if (!"*".equals(companyCode)) {
            companyFilter = " AND r.company_code = ?";
            args.add(companyCode);
        }
        String sql = "SELECT r.*,"
            + " COALESCE((SELECT json_agg(json_build_object('id',h.id,'step',h.step,'history_date',h.history_date,'user_name',h.user_name,'description',h.description) ORDER BY h.created_date) FROM dsn_request_history h WHERE h.request_id=r.id),'[]'::json) AS history,"
            + " COALESCE((SELECT json_agg(i.impact_type) FROM dsn_request_impact i WHERE i.request_id=r.id),'[]'::json) AS impact"
            + " FROM dsn_design_request r WHERE r.id=?" + companyFilter;
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, args.toArray());
        return result.isEmpty() ? null : result.get(0);
    }

    @Transactional
    public Map<String, Object> createDesignRequest(Map<String, Object> body, String companyCode, String userId) {
        Map<String, Object> params = new HashMap<>(body);
        params.put("company_code", companyCode);
        params.put("writer", userId);
        if (params.get("source_type") == null && params.get("source_type") != null)
            params.put("source_type", params.get("source_type"));
        if (params.get("source_type") == null) params.put("source_type", "dr");
        if (params.get("priority") == null) params.put("priority", "보통");
        if (params.get("status") == null) params.put("status", "신규접수");
        if (params.get("urgency") == null) params.put("urgency", "보통");
        sqlSession.insert("design.insertDesignRequest", params);

        Long requestId = ((Number) params.get("id")).longValue();

        @SuppressWarnings("unchecked")
        List<String> impact = (List<String>) body.get("impact");
        if (impact != null) {
            for (String imp : impact) {
                jdbcTemplate.update(
                    "INSERT INTO dsn_request_impact (request_id, impact_type, writer, company_code) VALUES (?,?,?,?)",
                    requestId, imp, userId, companyCode
                );
            }
        }

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> history = (List<Map<String, Object>>) body.get("history");
        if (history != null) {
            for (Map<String, Object> h : history) {
                jdbcTemplate.update(
                    "INSERT INTO dsn_request_history (request_id, step, history_date, user_name, description, writer, company_code) VALUES (?,?,?,?,?,?,?)",
                    requestId, h.get("step"), h.get("history_date"), h.get("user_name"), h.get("description"), userId, companyCode
                );
            }
        }
        return params;
    }

    @Transactional
    public Map<String, Object> updateDesignRequest(String id, Map<String, Object> body, String companyCode, String userId) {
        List<String> sets = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        String[] fields = {"request_no", "source_type", "request_date", "due_date", "priority", "status",
            "approval_step", "target_name", "customer", "req_dept", "requester", "designer", "order_no",
            "design_type", "spec", "change_type", "drawing_no", "urgency", "reason",
            "content", "apply_timing", "review_memo", "project_id", "ecn_no"};
        for (String f : fields) {
            if (body.containsKey(f)) { sets.add(f + " = ?"); params.add(body.get(f)); }
        }
        sets.add("updated_date = now()");
        String where = "id = ?";
        params.add(id);
        if (!"*".equals(companyCode)) { where += " AND company_code = ?"; params.add(companyCode); }
        jdbcTemplate.update("UPDATE dsn_design_request SET " + String.join(", ", sets) + " WHERE " + where, params.toArray());

        if (body.containsKey("impact")) {
            jdbcTemplate.update("DELETE FROM dsn_request_impact WHERE request_id = ?", id);
            @SuppressWarnings("unchecked")
            List<String> impact = (List<String>) body.get("impact");
            if (impact != null) {
                for (String imp : impact) {
                    jdbcTemplate.update("INSERT INTO dsn_request_impact (request_id, impact_type, writer, company_code) VALUES (?,?,?,?)", id, imp, userId, companyCode);
                }
            }
        }
        if (body.containsKey("history")) {
            jdbcTemplate.update("DELETE FROM dsn_request_history WHERE request_id = ?", id);
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> history = (List<Map<String, Object>>) body.get("history");
            if (history != null) {
                for (Map<String, Object> h : history) {
                    jdbcTemplate.update("INSERT INTO dsn_request_history (request_id, step, history_date, user_name, description, writer, company_code) VALUES (?,?,?,?,?,?,?)",
                        id, h.get("step"), h.get("history_date"), h.get("user_name"), h.get("description"), userId, companyCode);
                }
            }
        }
        List<Map<String, Object>> result = jdbcTemplate.queryForList("SELECT * FROM dsn_design_request WHERE id = ?", id);
        return result.isEmpty() ? null : result.get(0);
    }

    @Transactional
    public boolean deleteDesignRequest(String id, String companyCode) {
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        params.put("company_code", companyCode);
        return sqlSession.delete("design.deleteDesignRequest", params) > 0;
    }

    @Transactional
    public Map<String, Object> addRequestHistory(String requestId, Map<String, Object> body, String companyCode, String userId) {
        jdbcTemplate.update(
            "INSERT INTO dsn_request_history (request_id, step, history_date, user_name, description, writer, company_code) VALUES (?,?,?,?,?,?,?)",
            requestId, body.get("step"), body.get("history_date"), body.get("user_name"), body.get("description"), userId, companyCode
        );
        List<Map<String, Object>> result = jdbcTemplate.queryForList(
            "SELECT * FROM dsn_request_history WHERE request_id = ? ORDER BY id DESC LIMIT 1", requestId
        );
        return result.isEmpty() ? null : result.get(0);
    }

    // ============================================
    // 설계 프로젝트
    // ============================================

    public Map<String, Object> getProjectList(Map<String, Object> params) {
        commonService.applyCompanyCodeFilter(params);
        commonService.applyPagination(params);
        Number cntNum = sqlSession.selectOne("design.getProjectListCnt", params);
        int totalCount = cntNum != null ? cntNum.intValue() : 0;
        List<Map<String, Object>> list = sqlSession.selectList("design.getProjectList", params);
        return commonService.buildListResponse(list, totalCount, params);
    }

    public Map<String, Object> getProjectDetail(String id, String companyCode) {
        List<Object> args = new ArrayList<>();
        args.add(id);
        String companyFilter = "";
        if (!"*".equals(companyCode)) { companyFilter = " AND p.company_code = ?"; args.add(companyCode); }
        String sql = "SELECT p.*,"
            + " COALESCE((SELECT json_agg(json_build_object('id',t.id,'name',t.name,'category',t.category,'assignee',t.assignee,'start_date',t.start_date,'end_date',t.end_date,'status',t.status,'progress',t.progress,'priority',t.priority,'remark',t.remark,'sort_order',t.sort_order) ORDER BY t.sort_order,t.start_date) FROM dsn_project_task t WHERE t.project_id=p.id),'[]'::json) AS tasks"
            + " FROM dsn_project p WHERE p.id=?" + companyFilter;
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql, args.toArray());
        return result.isEmpty() ? null : result.get(0);
    }

    @Transactional
    public Map<String, Object> createProject(Map<String, Object> body, String companyCode, String userId) {
        Map<String, Object> params = new HashMap<>(body);
        params.put("company_code", companyCode);
        params.put("writer", userId);
        if (params.get("status") == null) params.put("status", "계획");
        if (params.get("progress") == null) params.put("progress", "0");
        sqlSession.insert("design.insertProject", params);

        Long projectId = ((Number) params.get("id")).longValue();

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> tasks = (List<Map<String, Object>>) body.get("tasks");
        if (tasks != null) {
            for (int i = 0; i < tasks.size(); i++) {
                Map<String, Object> t = tasks.get(i);
                jdbcTemplate.update(
                    "INSERT INTO dsn_project_task (project_id, name, category, assignee, start_date, end_date, status, progress, priority, remark, sort_order, writer, company_code) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    projectId, t.get("name"), t.get("category"), t.get("assignee"),
                    t.get("start_date"), t.get("end_date"),
                    nvl(t.get("status"), "대기"), nvl(t.get("progress"), "0"),
                    nvl(t.get("priority"), "보통"), t.get("remark"), String.valueOf(i), userId, companyCode
                );
            }
        }
        return params;
    }

    @Transactional
    public Map<String, Object> updateProject(String id, Map<String, Object> body, String companyCode) {
        List<String> sets = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        String[] fields = {"project_no", "name", "status", "pm", "customer", "start_date", "end_date",
            "source_no", "description", "progress", "parent_id", "relation_type"};
        for (String f : fields) {
            if (body.containsKey(f)) { sets.add(f + " = ?"); params.add(body.get(f)); }
        }
        sets.add("updated_date = now()");
        String where = "id = ?";
        params.add(id);
        if (!"*".equals(companyCode)) { where += " AND company_code = ?"; params.add(companyCode); }
        jdbcTemplate.update("UPDATE dsn_project SET " + String.join(", ", sets) + " WHERE " + where, params.toArray());
        List<Map<String, Object>> result = jdbcTemplate.queryForList("SELECT * FROM dsn_project WHERE id = ?", id);
        return result.isEmpty() ? null : result.get(0);
    }

    @Transactional
    public boolean deleteProject(String id, String companyCode) {
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        params.put("company_code", companyCode);
        return sqlSession.delete("design.deleteProject", params) > 0;
    }

    // ============================================
    // 프로젝트 태스크
    // ============================================

    public List<Map<String, Object>> getTasksByProject(String projectId, String companyCode) {
        List<Object> args = new ArrayList<>();
        args.add(projectId);
        String companyFilter = "";
        if (!"*".equals(companyCode)) { companyFilter = " AND t.company_code = ?"; args.add(companyCode); }
        String sql = "SELECT t.*,"
            + " COALESCE((SELECT json_agg(json_build_object('id',w.id,'start_dt',w.start_dt,'end_dt',w.end_dt,'hours',w.hours,'description',w.description,'progress_before',w.progress_before,'progress_after',w.progress_after,'author',w.author,'sub_item_id',w.sub_item_id) ORDER BY w.start_dt) FROM dsn_work_log w WHERE w.task_id=t.id),'[]'::json) AS work_logs,"
            + " COALESCE((SELECT json_agg(json_build_object('id',i.id,'title',i.title,'status',i.status,'priority',i.priority,'description',i.description,'registered_by',i.registered_by,'registered_date',i.registered_date,'resolved_date',i.resolved_date)) FROM dsn_task_issue i WHERE i.task_id=t.id),'[]'::json) AS issues,"
            + " COALESCE((SELECT json_agg(json_build_object('id',s.id,'name',s.name,'weight',s.weight,'progress',s.progress,'status',s.status) ORDER BY s.created_date) FROM dsn_task_sub_item s WHERE s.task_id=t.id),'[]'::json) AS sub_items"
            + " FROM dsn_project_task t WHERE t.project_id=?" + companyFilter
            + " ORDER BY t.sort_order, t.start_date";
        return jdbcTemplate.queryForList(sql, args.toArray());
    }

    @Transactional
    public Map<String, Object> createTask(String projectId, Map<String, Object> body, String companyCode, String userId) {
        jdbcTemplate.update(
            "INSERT INTO dsn_project_task (project_id, name, category, assignee, start_date, end_date, status, progress, priority, remark, sort_order, writer, company_code) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
            projectId, body.get("name"), body.get("category"), body.get("assignee"),
            body.get("start_date"), body.get("end_date"),
            nvl(body.get("status"), "대기"), nvl(body.get("progress"), "0"),
            nvl(body.get("priority"), "보통"), body.get("remark"), nvl(body.get("sort_order"), "0"), userId, companyCode
        );
        List<Map<String, Object>> result = jdbcTemplate.queryForList(
            "SELECT * FROM dsn_project_task WHERE project_id = ? AND company_code = ? ORDER BY id DESC LIMIT 1",
            projectId, companyCode
        );
        return result.isEmpty() ? null : result.get(0);
    }

    @Transactional
    public Map<String, Object> updateTask(String taskId, Map<String, Object> body, String companyCode) {
        List<String> sets = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        String[] fields = {"name", "category", "assignee", "start_date", "end_date", "status", "progress", "priority", "remark", "sort_order"};
        for (String f : fields) {
            if (body.containsKey(f)) { sets.add(f + " = ?"); params.add(body.get(f)); }
        }
        sets.add("updated_date = now()");
        String where = "id = ?";
        params.add(taskId);
        if (!"*".equals(companyCode)) { where += " AND company_code = ?"; params.add(companyCode); }
        jdbcTemplate.update("UPDATE dsn_project_task SET " + String.join(", ", sets) + " WHERE " + where, params.toArray());
        List<Map<String, Object>> result = jdbcTemplate.queryForList("SELECT * FROM dsn_project_task WHERE id = ?", taskId);
        return result.isEmpty() ? null : result.get(0);
    }

    @Transactional
    public boolean deleteTask(String taskId, String companyCode) {
        List<Object> args = new ArrayList<>();
        args.add(taskId);
        String sql = "DELETE FROM dsn_project_task WHERE id = ?";
        if (!"*".equals(companyCode)) { sql += " AND company_code = ?"; args.add(companyCode); }
        return jdbcTemplate.update(sql, args.toArray()) > 0;
    }

    // ============================================
    // 작업일지
    // ============================================

    public List<Map<String, Object>> getWorkLogsByTask(String taskId, String companyCode) {
        List<Object> args = new ArrayList<>();
        args.add(taskId);
        String companyFilter = "";
        if (!"*".equals(companyCode)) { companyFilter = " AND w.company_code = ?"; args.add(companyCode); }
        String sql = "SELECT w.*,"
            + " COALESCE((SELECT json_agg(json_build_object('id',a.id,'file_name',a.file_name,'file_type',a.file_type,'file_size',a.file_size)) FROM dsn_work_attachment a WHERE a.work_log_id=w.id),'[]'::json) AS attachments,"
            + " COALESCE((SELECT json_agg(json_build_object('id',p.id,'item',p.item,'qty',p.qty,'unit',p.unit,'reason',p.reason,'status',p.status)) FROM dsn_purchase_req p WHERE p.work_log_id=w.id),'[]'::json) AS purchase_reqs,"
            + " COALESCE((SELECT json_agg(json_build_object('id',c.id,'to_user',c.to_user,'to_dept',c.to_dept,'title',c.title,'description',c.description,'status',c.status,'due_date',c.due_date,'responses',COALESCE((SELECT json_agg(json_build_object('id',cr.id,'response_date',cr.response_date,'user_name',cr.user_name,'content',cr.content)) FROM dsn_coop_response cr WHERE cr.coop_req_id=c.id),'[]'::json))) FROM dsn_coop_req c WHERE c.work_log_id=w.id),'[]'::json) AS coop_reqs"
            + " FROM dsn_work_log w WHERE w.task_id=?" + companyFilter + " ORDER BY w.start_dt DESC";
        return jdbcTemplate.queryForList(sql, args.toArray());
    }

    @Transactional
    public Map<String, Object> createWorkLog(String taskId, Map<String, Object> body, String companyCode, String userId) {
        jdbcTemplate.update(
            "INSERT INTO dsn_work_log (task_id, start_dt, end_dt, hours, description, progress_before, progress_after, author, sub_item_id, writer, company_code) VALUES (?,?,?,?,?,?,?,?,?,?,?)",
            taskId, body.get("start_dt"), body.get("end_dt"),
            nvl(body.get("hours"), "0"), body.get("description"),
            nvl(body.get("progress_before"), "0"), nvl(body.get("progress_after"), "0"),
            body.get("author"), body.get("sub_item_id"), userId, companyCode
        );
        List<Map<String, Object>> result = jdbcTemplate.queryForList(
            "SELECT * FROM dsn_work_log WHERE task_id = ? AND company_code = ? ORDER BY id DESC LIMIT 1",
            taskId, companyCode
        );
        return result.isEmpty() ? null : result.get(0);
    }

    @Transactional
    public boolean deleteWorkLog(String workLogId, String companyCode) {
        List<Object> args = new ArrayList<>();
        args.add(workLogId);
        String sql = "DELETE FROM dsn_work_log WHERE id = ?";
        if (!"*".equals(companyCode)) { sql += " AND company_code = ?"; args.add(companyCode); }
        return jdbcTemplate.update(sql, args.toArray()) > 0;
    }

    // ============================================
    // 태스크 하위항목
    // ============================================

    @Transactional
    public Map<String, Object> createSubItem(String taskId, Map<String, Object> body, String companyCode, String userId) {
        jdbcTemplate.update(
            "INSERT INTO dsn_task_sub_item (task_id, name, weight, progress, status, writer, company_code) VALUES (?,?,?,?,?,?,?)",
            taskId, body.get("name"), nvl(body.get("weight"), "0"),
            nvl(body.get("progress"), "0"), nvl(body.get("status"), "대기"), userId, companyCode
        );
        List<Map<String, Object>> result = jdbcTemplate.queryForList(
            "SELECT * FROM dsn_task_sub_item WHERE task_id = ? AND company_code = ? ORDER BY id DESC LIMIT 1",
            taskId, companyCode
        );
        return result.isEmpty() ? null : result.get(0);
    }

    @Transactional
    public Map<String, Object> updateSubItem(String subItemId, Map<String, Object> body, String companyCode) {
        List<String> sets = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        for (String f : new String[]{"name", "weight", "progress", "status"}) {
            if (body.containsKey(f)) { sets.add(f + " = ?"); params.add(body.get(f)); }
        }
        sets.add("updated_date = now()");
        String where = "id = ?";
        params.add(subItemId);
        if (!"*".equals(companyCode)) { where += " AND company_code = ?"; params.add(companyCode); }
        jdbcTemplate.update("UPDATE dsn_task_sub_item SET " + String.join(", ", sets) + " WHERE " + where, params.toArray());
        List<Map<String, Object>> result = jdbcTemplate.queryForList("SELECT * FROM dsn_task_sub_item WHERE id = ?", subItemId);
        return result.isEmpty() ? null : result.get(0);
    }

    @Transactional
    public boolean deleteSubItem(String subItemId, String companyCode) {
        List<Object> args = new ArrayList<>();
        args.add(subItemId);
        String sql = "DELETE FROM dsn_task_sub_item WHERE id = ?";
        if (!"*".equals(companyCode)) { sql += " AND company_code = ?"; args.add(companyCode); }
        return jdbcTemplate.update(sql, args.toArray()) > 0;
    }

    // ============================================
    // 태스크 이슈
    // ============================================

    @Transactional
    public Map<String, Object> createIssue(String taskId, Map<String, Object> body, String companyCode, String userId) {
        jdbcTemplate.update(
            "INSERT INTO dsn_task_issue (task_id, title, status, priority, description, registered_by, registered_date, writer, company_code) VALUES (?,?,?,?,?,?,?,?,?)",
            taskId, body.get("title"), nvl(body.get("status"), "등록"),
            nvl(body.get("priority"), "보통"), body.get("description"),
            body.get("registered_by"), body.get("registered_date"), userId, companyCode
        );
        List<Map<String, Object>> result = jdbcTemplate.queryForList(
            "SELECT * FROM dsn_task_issue WHERE task_id = ? AND company_code = ? ORDER BY id DESC LIMIT 1",
            taskId, companyCode
        );
        return result.isEmpty() ? null : result.get(0);
    }

    @Transactional
    public Map<String, Object> updateIssue(String issueId, Map<String, Object> body, String companyCode) {
        List<String> sets = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        for (String f : new String[]{"title", "status", "priority", "description", "resolved_date"}) {
            if (body.containsKey(f)) { sets.add(f + " = ?"); params.add(body.get(f)); }
        }
        sets.add("updated_date = now()");
        String where = "id = ?";
        params.add(issueId);
        if (!"*".equals(companyCode)) { where += " AND company_code = ?"; params.add(companyCode); }
        jdbcTemplate.update("UPDATE dsn_task_issue SET " + String.join(", ", sets) + " WHERE " + where, params.toArray());
        List<Map<String, Object>> result = jdbcTemplate.queryForList("SELECT * FROM dsn_task_issue WHERE id = ?", issueId);
        return result.isEmpty() ? null : result.get(0);
    }

    // ============================================
    // ECN (설변통보)
    // ============================================

    public Map<String, Object> getEcnList(Map<String, Object> params) {
        commonService.applyCompanyCodeFilter(params);
        commonService.applyPagination(params);
        Number cntNum = sqlSession.selectOne("design.getEcnListCnt", params);
        int totalCount = cntNum != null ? cntNum.intValue() : 0;
        List<Map<String, Object>> list = sqlSession.selectList("design.getEcnList", params);
        return commonService.buildListResponse(list, totalCount, params);
    }

    @Transactional
    public Map<String, Object> createEcn(Map<String, Object> body, String companyCode, String userId) {
        Map<String, Object> params = new HashMap<>(body);
        params.put("company_code", companyCode);
        params.put("writer", userId);
        if (params.get("status") == null) params.put("status", "ECN발행");
        sqlSession.insert("design.insertEcn", params);

        Long ecnId = ((Number) params.get("id")).longValue();

        @SuppressWarnings("unchecked")
        List<String> notifyDepts = (List<String>) body.get("notify_depts");
        if (notifyDepts != null) {
            for (String dept : notifyDepts) {
                jdbcTemplate.update("INSERT INTO dsn_ecn_notify_dept (ecn_id, dept_name, writer, company_code) VALUES (?,?,?,?)", ecnId, dept, userId, companyCode);
            }
        }

        @SuppressWarnings("unchecked")
        List<Map<String, Object>> history = (List<Map<String, Object>>) body.get("history");
        if (history != null) {
            for (Map<String, Object> h : history) {
                jdbcTemplate.update("INSERT INTO dsn_ecn_history (ecn_id, status, history_date, user_name, description, writer, company_code) VALUES (?,?,?,?,?,?,?)",
                    ecnId, h.get("status"), h.get("history_date"), h.get("user_name"), h.get("description"), userId, companyCode);
            }
        }
        return params;
    }

    @Transactional
    public Map<String, Object> updateEcn(String id, Map<String, Object> body, String companyCode, String userId) {
        List<String> sets = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        String[] fields = {"ecn_no", "ecn_date", "apply_date", "status", "target",
            "drawing_before", "drawing_after", "designer", "before_content", "after_content", "reason", "remark"};
        for (String f : fields) {
            if (body.containsKey(f)) { sets.add(f + " = ?"); params.add(body.get(f)); }
        }
        sets.add("updated_date = now()");
        String where = "id = ?";
        params.add(id);
        if (!"*".equals(companyCode)) { where += " AND company_code = ?"; params.add(companyCode); }
        jdbcTemplate.update("UPDATE dsn_ecn SET " + String.join(", ", sets) + " WHERE " + where, params.toArray());

        if (body.containsKey("notify_depts")) {
            jdbcTemplate.update("DELETE FROM dsn_ecn_notify_dept WHERE ecn_id = ?", id);
            @SuppressWarnings("unchecked")
            List<String> notifyDepts = (List<String>) body.get("notify_depts");
            if (notifyDepts != null) {
                for (String dept : notifyDepts) {
                    jdbcTemplate.update("INSERT INTO dsn_ecn_notify_dept (ecn_id, dept_name, writer, company_code) VALUES (?,?,?,?)", id, dept, userId, companyCode);
                }
            }
        }
        if (body.containsKey("history")) {
            jdbcTemplate.update("DELETE FROM dsn_ecn_history WHERE ecn_id = ?", id);
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> history = (List<Map<String, Object>>) body.get("history");
            if (history != null) {
                for (Map<String, Object> h : history) {
                    jdbcTemplate.update("INSERT INTO dsn_ecn_history (ecn_id, status, history_date, user_name, description, writer, company_code) VALUES (?,?,?,?,?,?,?)",
                        id, h.get("status"), h.get("history_date"), h.get("user_name"), h.get("description"), userId, companyCode);
                }
            }
        }
        List<Map<String, Object>> result = jdbcTemplate.queryForList("SELECT * FROM dsn_ecn WHERE id = ?", id);
        return result.isEmpty() ? null : result.get(0);
    }

    @Transactional
    public boolean deleteEcn(String id, String companyCode) {
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        params.put("company_code", companyCode);
        return sqlSession.delete("design.deleteEcn", params) > 0;
    }

    // ============================================
    // 나의 업무
    // ============================================

    public List<Map<String, Object>> getMyWork(Map<String, Object> queryParams, String companyCode, String userName) {
        List<String> conditions = new ArrayList<>();
        List<Object> args = new ArrayList<>();
        conditions.add("t.assignee = ?");
        args.add(userName);
        if (!"*".equals(companyCode)) { conditions.add("t.company_code = ?"); args.add(companyCode); }
        if (queryParams.get("status") != null) { conditions.add("t.status = ?"); args.add(queryParams.get("status")); }
        if (queryParams.get("project_id") != null) { conditions.add("t.project_id = ?"); args.add(queryParams.get("project_id")); }

        String sql = "SELECT t.*, p.project_no, p.name AS project_name, p.customer AS project_customer, p.status AS project_status,"
            + " COALESCE((SELECT json_agg(json_build_object('id',s.id,'name',s.name,'weight',s.weight,'progress',s.progress,'status',s.status) ORDER BY s.created_date) FROM dsn_task_sub_item s WHERE s.task_id=t.id),'[]'::json) AS sub_items,"
            + " COALESCE((SELECT json_agg(json_build_object('id',w.id,'start_dt',w.start_dt,'end_dt',w.end_dt,'hours',w.hours,'description',w.description,'sub_item_id',w.sub_item_id)) FROM dsn_work_log w WHERE w.task_id=t.id),'[]'::json) AS work_logs"
            + " FROM dsn_project_task t JOIN dsn_project p ON p.id=t.project_id"
            + " WHERE " + String.join(" AND ", conditions)
            + " ORDER BY CASE t.status WHEN '진행중' THEN 1 WHEN '대기' THEN 2 WHEN '검토중' THEN 3 ELSE 4 END, t.end_date ASC";
        return jdbcTemplate.queryForList(sql, args.toArray());
    }

    // ============================================
    // 구매요청 / 협업요청
    // ============================================

    @Transactional
    public Map<String, Object> createPurchaseReq(String workLogId, Map<String, Object> body, String companyCode, String userId) {
        jdbcTemplate.update(
            "INSERT INTO dsn_purchase_req (work_log_id, item, qty, unit, reason, status, writer, company_code) VALUES (?,?,?,?,?,?,?,?)",
            workLogId, body.get("item"), body.get("qty"), body.get("unit"),
            body.get("reason"), nvl(body.get("status"), "요청"), userId, companyCode
        );
        List<Map<String, Object>> result = jdbcTemplate.queryForList(
            "SELECT * FROM dsn_purchase_req WHERE work_log_id = ? AND company_code = ? ORDER BY id DESC LIMIT 1",
            workLogId, companyCode
        );
        return result.isEmpty() ? null : result.get(0);
    }

    @Transactional
    public Map<String, Object> createCoopReq(String workLogId, Map<String, Object> body, String companyCode, String userId) {
        jdbcTemplate.update(
            "INSERT INTO dsn_coop_req (work_log_id, to_user, to_dept, title, description, status, due_date, writer, company_code) VALUES (?,?,?,?,?,?,?,?,?)",
            workLogId, body.get("to_user"), body.get("to_dept"), body.get("title"),
            body.get("description"), "요청", body.get("due_date"), userId, companyCode
        );
        List<Map<String, Object>> result = jdbcTemplate.queryForList(
            "SELECT * FROM dsn_coop_req WHERE work_log_id = ? AND company_code = ? ORDER BY id DESC LIMIT 1",
            workLogId, companyCode
        );
        return result.isEmpty() ? null : result.get(0);
    }

    @Transactional
    public Map<String, Object> addCoopResponse(String coopReqId, Map<String, Object> body, String companyCode, String userId) {
        jdbcTemplate.update(
            "INSERT INTO dsn_coop_response (coop_req_id, response_date, user_name, content, writer, company_code) VALUES (?,?,?,?,?,?)",
            coopReqId, body.get("response_date"), body.get("user_name"), body.get("content"), userId, companyCode
        );
        List<Map<String, Object>> result = jdbcTemplate.queryForList(
            "SELECT * FROM dsn_coop_response WHERE coop_req_id = ? AND company_code = ? ORDER BY id DESC LIMIT 1",
            coopReqId, companyCode
        );
        return result.isEmpty() ? null : result.get(0);
    }

    private Object nvl(Object value, Object defaultValue) {
        return value != null ? value : defaultValue;
    }
}
