package com.erp.service;

import com.erp.common.BaseService;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
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 ReportService extends BaseService {

    @Autowired private CommonService commonService;
    @Autowired private ExternalDbConnectionService externalDbConnectionService;
    @Autowired private JdbcTemplate jdbcTemplate;
    @Autowired private ObjectMapper objectMapper;

    private static final List<String> DANGEROUS_KEYWORDS = List.of(
        "DELETE", "DROP", "TRUNCATE", "INSERT", "UPDATE", "ALTER",
        "CREATE", "REPLACE", "MERGE", "GRANT", "REVOKE",
        "EXECUTE", "EXEC", "CALL"
    );

    // ── Report Master ─────────────────────────────────────────────────────────

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

    public Map<String, Object> getReportInfo(Map<String, Object> params) {
        Map<String, Object> report = sqlSession.selectOne("report.getReportInfo", params);
        if (report == null) return null;

        String reportId = String.valueOf(params.get("report_id"));
        Map<String, Object> qp = Map.of("report_id", reportId);

        Map<String, Object> layout = sqlSession.selectOne("report.getReportLayout", qp);
        List<Map<String, Object>> queries = sqlSession.selectList("report.getReportQueryList", qp);
        List<Map<String, Object>> menuMappings = sqlSession.selectList("report.getReportMenuMappings", qp);
        List<Object> menuObjids = new ArrayList<>();
        if (menuMappings != null) {
            for (Map<String, Object> m : menuMappings) {
                menuObjids.add(m.get("menu_objid"));
            }
        }

        Map<String, Object> result = new LinkedHashMap<>(report);
        result.put("layout", layout);
        result.put("queries", queries != null ? queries : List.of());
        result.put("menu_objids", menuObjids);
        return result;
    }

    @Transactional
    public Map<String, Object> createReport(Map<String, Object> params) {
        String reportId = "RPT_" + UUID.randomUUID().toString().replace("-", "").substring(0, 20);
        params.put("report_id", reportId);
        sqlSession.insert("report.insertReport", params);

        // 템플릿이 있으면 레이아웃 복사
        String templateId = (String) params.get("template_id");
        if (templateId != null && !templateId.isEmpty()) {
            Map<String, Object> tp = Map.of("template_id", templateId);
            Map<String, Object> template = sqlSession.selectOne("report.getReportTemplateById", tp);
            if (template != null && template.get("layout_config") != null) {
                String layoutJson = String.valueOf(template.get("layout_config"));
                try {
                    Map<String, Object> layoutConfig = objectMapper.readValue(
                        layoutJson, new TypeReference<>() {});
                    String layoutId = "LAY_" + UUID.randomUUID().toString().replace("-", "").substring(0, 20);
                    Map<String, Object> lp = new HashMap<>();
                    lp.put("layout_id", layoutId);
                    lp.put("report_id", reportId);
                    lp.put("canvas_width", layoutConfig.getOrDefault("width", 210));
                    lp.put("canvas_height", layoutConfig.getOrDefault("height", 297));
                    lp.put("page_orientation", layoutConfig.getOrDefault("orientation", "portrait"));
                    lp.put("margin_top", 20);
                    lp.put("margin_bottom", 20);
                    lp.put("margin_left", 20);
                    lp.put("margin_right", 20);
                    lp.put("components", "[]");
                    lp.put("created_by", params.get("created_by"));
                    sqlSession.insert("report.insertReportLayout", lp);
                } catch (Exception e) {
                    log.warn("템플릿 레이아웃 복사 실패: {}", e.getMessage());
                }
            }
        }

        Map<String, Object> result = new LinkedHashMap<>();
        result.put("report_id", reportId);
        return result;
    }

    @Transactional
    public Map<String, Object> updateReport(Map<String, Object> params) {
        sqlSession.update("report.updateReport", params);
        return params;
    }

    @Transactional
    public Map<String, Object> deleteReport(Map<String, Object> params) {
        String reportId = String.valueOf(params.get("report_id"));
        Map<String, Object> rp = Map.of("report_id", reportId);
        sqlSession.delete("report.deleteReportQueriesByReportId", rp);
        sqlSession.delete("report.deleteReportLayout", rp);
        sqlSession.delete("report.deleteReportMenuMappingsByReportId", rp);
        sqlSession.delete("report.deleteReport", params);
        return params;
    }

    @Transactional
    public Map<String, Object> copyReport(Map<String, Object> params) {
        String reportId = String.valueOf(params.get("report_id"));
        String userId = (String) params.getOrDefault("user_id", "SYSTEM");
        Map<String, Object> qp = Map.of("report_id", reportId, "company_code", params.getOrDefault("company_code", "*"));

        Map<String, Object> original = sqlSession.selectOne("report.getReportInfo", qp);
        if (original == null) return null;

        String newReportId = "RPT_" + UUID.randomUUID().toString().replace("-", "").substring(0, 20);
        Map<String, Object> newReport = new HashMap<>(original);
        newReport.put("report_id", newReportId);
        newReport.put("report_name_kor", original.get("report_name_kor") + " (복사)");
        Object eng = original.get("report_name_eng");
        newReport.put("report_name_eng", eng != null ? eng + " (Copy)" : null);
        newReport.put("created_by", userId);
        sqlSession.insert("report.insertReport", newReport);

        Map<String, Object> origLayout = sqlSession.selectOne("report.getReportLayout", Map.of("report_id", reportId));
        if (origLayout != null) {
            String newLayoutId = "LAY_" + UUID.randomUUID().toString().replace("-", "").substring(0, 20);
            Map<String, Object> lp = new HashMap<>(origLayout);
            lp.put("layout_id", newLayoutId);
            lp.put("report_id", newReportId);
            lp.put("created_by", userId);
            lp.put("components", origLayout.getOrDefault("components", "[]"));
            sqlSession.insert("report.insertReportLayout", lp);
        }

        List<Map<String, Object>> origQueries = sqlSession.selectList("report.getReportQueryList", Map.of("report_id", reportId));
        if (origQueries != null) {
            for (Map<String, Object> q : origQueries) {
                String newQueryId = "QRY_" + UUID.randomUUID().toString().replace("-", "").substring(0, 20);
                Map<String, Object> qNew = new HashMap<>(q);
                qNew.put("query_id", newQueryId);
                qNew.put("report_id", newReportId);
                qNew.put("created_by", userId);
                qNew.put("parameters", q.getOrDefault("parameters", "[]"));
                sqlSession.insert("report.insertReportQuery", qNew);
            }
        }

        Map<String, Object> result = new LinkedHashMap<>();
        result.put("report_id", newReportId);
        return result;
    }

    // ── Report Layout ─────────────────────────────────────────────────────────

    public Map<String, Object> getLayout(Map<String, Object> params) {
        return sqlSession.selectOne("report.getReportLayout", params);
    }

    @Transactional
    public Map<String, Object> saveLayout(Map<String, Object> params) {
        String reportId = String.valueOf(params.get("report_id"));
        String userId = (String) params.getOrDefault("user_id", "SYSTEM");

        // layoutConfig 파싱
        Object layoutConfigObj = params.get("layout_config");
        Map<String, Object> layoutConfig = new HashMap<>();
        if (layoutConfigObj instanceof Map) {
            layoutConfig = (Map<String, Object>) layoutConfigObj;
        }

        // 첫 페이지 기준으로 canvas 크기/방향 결정
        List<Map<String, Object>> pages = new ArrayList<>();
        Object pagesObj = layoutConfig.get("pages");
        if (pagesObj instanceof List) {
            pages = (List<Map<String, Object>>) pagesObj;
        }
        Map<String, Object> firstPage = pages.isEmpty() ? new HashMap<>() : pages.get(0);
        int canvasWidth = toInt(firstPage.getOrDefault("width", 210));
        int canvasHeight = toInt(firstPage.getOrDefault("height", 297));
        String orientation = canvasWidth > canvasHeight ? "landscape" : "portrait";
        Map<String, Object> margins = (Map<String, Object>) firstPage.getOrDefault("margins",
            Map.of("top", 20, "bottom", 20, "left", 20, "right", 20));

        String componentsJson;
        try {
            componentsJson = objectMapper.writeValueAsString(layoutConfig);
        } catch (Exception e) {
            componentsJson = "{}";
        }

        // layout upsert
        Map<String, Object> existing = sqlSession.selectOne("report.getReportLayout", Map.of("report_id", reportId));
        Map<String, Object> lp = new HashMap<>();
        lp.put("report_id", reportId);
        lp.put("canvas_width", canvasWidth);
        lp.put("canvas_height", canvasHeight);
        lp.put("page_orientation", orientation);
        lp.put("margin_top", toInt(margins.getOrDefault("top", 20)));
        lp.put("margin_bottom", toInt(margins.getOrDefault("bottom", 20)));
        lp.put("margin_left", toInt(margins.getOrDefault("left", 20)));
        lp.put("margin_right", toInt(margins.getOrDefault("right", 20)));
        lp.put("components", componentsJson);
        lp.put("updated_by", userId);
        lp.put("created_by", userId);

        if (existing != null) {
            sqlSession.update("report.updateReportLayout", lp);
        } else {
            lp.put("layout_id", "LAY_" + UUID.randomUUID().toString().replace("-", "").substring(0, 20));
            sqlSession.insert("report.insertReportLayout", lp);
        }

        // 쿼리 저장
        Object queriesObj = params.get("queries");
        if (queriesObj instanceof List) {
            List<Map<String, Object>> queries = (List<Map<String, Object>>) queriesObj;
            if (!queries.isEmpty()) {
                sqlSession.delete("report.deleteReportQueriesByReportId", Map.of("report_id", reportId));
                for (int i = 0; i < queries.size(); i++) {
                    Map<String, Object> q = queries.get(i);
                    String queryId = q.get("id") != null ? String.valueOf(q.get("id"))
                        : "QRY_" + UUID.randomUUID().toString().replace("-", "").substring(0, 20);
                    Map<String, Object> qp = new HashMap<>();
                    qp.put("query_id", queryId);
                    qp.put("report_id", reportId);
                    qp.put("query_name", q.get("name"));
                    qp.put("query_type", q.get("type"));
                    qp.put("sql_query", q.get("sql_query"));
                    Object paramList = q.get("parameters");
                    try {
                        qp.put("parameters", paramList != null
                            ? objectMapper.writeValueAsString(paramList) : "[]");
                    } catch (Exception e) {
                        qp.put("parameters", "[]");
                    }
                    qp.put("external_connection_id", q.get("external_connection_id"));
                    qp.put("display_order", i);
                    qp.put("created_by", userId);
                    sqlSession.insert("report.insertReportQuery", qp);
                }
            }
        }

        // 메뉴 매핑 저장
        Object menuObjidsObj = params.get("menu_objids");
        if (menuObjidsObj instanceof List) {
            List<Object> menuObjids = (List<Object>) menuObjidsObj;
            sqlSession.delete("report.deleteReportMenuMappingsByReportId", Map.of("report_id", reportId));
            if (!menuObjids.isEmpty()) {
                // 회사코드 조회
                Map<String, Object> rInfo = sqlSession.selectOne("report.getReportInfo",
                    Map.of("report_id", reportId, "company_code", "*"));
                String companyCode = rInfo != null
                    ? String.valueOf(rInfo.getOrDefault("company_code", "*")) : "*";
                for (Object menuObjid : menuObjids) {
                    Map<String, Object> mp = new HashMap<>();
                    mp.put("report_id", reportId);
                    mp.put("menu_objid", menuObjid);
                    mp.put("company_code", companyCode);
                    mp.put("created_by", userId);
                    sqlSession.insert("report.insertReportMenuMapping", mp);
                }
            }
        }

        Map<String, Object> result = new LinkedHashMap<>();
        result.put("report_id", reportId);
        result.put("success", true);
        return result;
    }

    // ── Query Execution ───────────────────────────────────────────────────────

    public Map<String, Object> executeQuery(Map<String, Object> params) {
        String reportId = String.valueOf(params.get("report_id"));
        String queryId = String.valueOf(params.get("query_id"));
        Map<String, Object> parameters = params.get("parameters") instanceof Map
            ? (Map<String, Object>) params.get("parameters") : new HashMap<>();
        String directSql = (String) params.get("sql_query");
        Object extConnObj = params.get("external_connection_id");

        String sqlQuery;
        Long connectionId = null;

        if (directSql != null && !directSql.isEmpty()) {
            sqlQuery = directSql;
            connectionId = extConnObj != null ? toLong(extConnObj) : null;
        } else {
            Map<String, Object> qRow = sqlSession.selectOne("report.getReportQueryById",
                Map.of("query_id", queryId, "report_id", reportId));
            if (qRow == null) throw new NoSuchElementException("쿼리를 찾을 수 없습니다.");
            sqlQuery = String.valueOf(qRow.get("sql_query"));
            Object extId = qRow.get("external_connection_id");
            connectionId = extId != null ? toLong(extId) : null;
        }

        // 보안 검증 (SELECT만 허용)
        validateQuerySafety(sqlQuery);

        List<Map<String, Object>> rows;
        if (connectionId != null) {
            // 외부 DB 실행
            Map<String, Object> execResult = externalDbConnectionService.executeQuery(connectionId, sqlQuery);
            Object dataObj = execResult.get("data");
            rows = dataObj instanceof List ? (List<Map<String, Object>>) dataObj : List.of();
        } else {
            // 내부 DB 실행
            rows = jdbcTemplate.queryForList(sqlQuery);
        }

        List<String> fields = rows.isEmpty() ? List.of() : new ArrayList<>(rows.get(0).keySet());
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("fields", fields);
        result.put("rows", rows);
        return result;
    }

    // ── Report Template ───────────────────────────────────────────────────────

    public Map<String, Object> getTemplates() {
        List<Map<String, Object>> all = sqlSession.selectList("report.getReportTemplateList", new HashMap<>());
        if (all == null) all = new ArrayList<>();
        List<Map<String, Object>> system = new ArrayList<>();
        List<Map<String, Object>> custom = new ArrayList<>();
        for (Map<String, Object> t : all) {
            Object isSystem = t.get("is_system");
            if ("Y".equals(isSystem)) system.add(t);
            else custom.add(t);
        }
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("system", system);
        result.put("custom", custom);
        return result;
    }

    @Transactional
    public Map<String, Object> createTemplate(Map<String, Object> params) {
        String templateId = "TPL_" + UUID.randomUUID().toString().replace("-", "").substring(0, 20);
        params.put("template_id", templateId);
        // layoutConfig/defaultQueries가 객체면 JSON 문자열로 변환
        serializeJsonField(params, "layout_config");
        serializeJsonField(params, "default_queries");
        sqlSession.insert("report.insertReportTemplate", params);
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("template_id", templateId);
        return result;
    }

    @Transactional
    public Map<String, Object> createTemplateFromLayout(Map<String, Object> params) {
        return createTemplate(params);
    }

    @Transactional
    public Map<String, Object> deleteTemplate(Map<String, Object> params) {
        sqlSession.delete("report.deleteReportTemplate", params);
        return params;
    }

    @Transactional
    public Map<String, Object> saveAsTemplate(Map<String, Object> params) {
        String reportId = String.valueOf(params.get("report_id"));
        String userId = (String) params.getOrDefault("user_id", "SYSTEM");
        Map<String, Object> companyFilter = Map.of("report_id", reportId, "company_code", "*");

        Map<String, Object> report = sqlSession.selectOne("report.getReportInfo", companyFilter);
        if (report == null) throw new NoSuchElementException("리포트를 찾을 수 없습니다.");

        Map<String, Object> layout = sqlSession.selectOne("report.getReportLayout", Map.of("report_id", reportId));
        if (layout == null) throw new IllegalStateException("레이아웃을 찾을 수 없습니다.");

        List<Map<String, Object>> queries = sqlSession.selectList("report.getReportQueryList", Map.of("report_id", reportId));

        Map<String, Object> layoutConfig = new LinkedHashMap<>();
        layoutConfig.put("width", layout.get("canvas_width"));
        layoutConfig.put("height", layout.get("canvas_height"));
        layoutConfig.put("orientation", layout.get("page_orientation"));
        Map<String, Object> margins = new LinkedHashMap<>();
        margins.put("top", layout.get("margin_top"));
        margins.put("bottom", layout.get("margin_bottom"));
        margins.put("left", layout.get("margin_left"));
        margins.put("right", layout.get("margin_right"));
        layoutConfig.put("margins", margins);
        try {
            String comps = String.valueOf(layout.getOrDefault("components", "[]"));
            layoutConfig.put("components", objectMapper.readValue(comps, List.class));
        } catch (Exception e) {
            layoutConfig.put("components", List.of());
        }

        List<Map<String, Object>> defaultQueries = new ArrayList<>();
        if (queries != null) {
            for (Map<String, Object> q : queries) {
                Map<String, Object> dq = new LinkedHashMap<>();
                dq.put("name", q.get("query_name"));
                dq.put("type", q.get("query_type"));
                dq.put("sql_query", q.get("sql_query"));
                dq.put("parameters", q.getOrDefault("parameters", "[]"));
                dq.put("external_connection_id", q.get("external_connection_id"));
                dq.put("display_order", q.get("display_order"));
                defaultQueries.add(dq);
            }
        }

        Map<String, Object> tp = new HashMap<>(params);
        tp.put("template_type", report.get("report_type"));
        tp.put("created_by", userId);
        tp.put("sort_order", 999);
        try {
            tp.put("layout_config", objectMapper.writeValueAsString(layoutConfig));
            tp.put("default_queries", objectMapper.writeValueAsString(defaultQueries));
        } catch (Exception e) {
            tp.put("layout_config", "{}");
            tp.put("default_queries", "[]");
        }
        return createTemplate(tp);
    }

    // ── External Connections (for report editor) ──────────────────────────────

    public List<Map<String, Object>> getExternalConnections(Map<String, Object> params) {
        return externalDbConnectionService.getConnections(params);
    }

    // ── Helpers ───────────────────────────────────────────────────────────────

    private void validateQuerySafety(String sql) {
        String upper = sql.trim().toUpperCase();
        if (!upper.startsWith("SELECT") && !upper.startsWith("WITH")) {
            throw new IllegalArgumentException("SELECT 쿼리만 허용됩니다.");
        }
        for (String kw : DANGEROUS_KEYWORDS) {
            if (upper.contains(kw)) {
                throw new IllegalArgumentException(
                    "보안상의 이유로 " + kw + " 명령어는 사용할 수 없습니다.");
            }
        }
    }

    private void serializeJsonField(Map<String, Object> params, String key) {
        Object val = params.get(key);
        if (val != null && !(val instanceof String)) {
            try {
                params.put(key, objectMapper.writeValueAsString(val));
            } catch (Exception e) {
                params.put(key, "null");
            }
        }
    }

    private int toInt(Object val) {
        if (val == null) return 0;
        if (val instanceof Number) return ((Number) val).intValue();
        try { return Integer.parseInt(String.valueOf(val)); } catch (Exception e) { return 0; }
    }

    private long toLong(Object val) {
        if (val == null) return 0L;
        if (val instanceof Number) return ((Number) val).longValue();
        try { return Long.parseLong(String.valueOf(val)); } catch (Exception e) { return 0L; }
    }
}
