package com.erp.provisioning;

import com.erp.tenant.DbContextHolder;
import com.erp.tenant.TenantDbSettings;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * 회사에 설치된 TableGroup 조회 + 템플릿 재복제.
 *
 * 재복제 정책 (MVP): {@code INSERT ... ON CONFLICT DO NOTHING}.
 *   메타 DB 의 공통 템플릿(company_code IN ('*','TEMPLATE') or NULL) row 들을
 *   tenant DB 에 추가 — **이미 있는 row 는 스킵**, 새로 추가된 것만 반영.
 *   사용자 커스터마이징 데이터는 덮어쓰지 않음.
 *
 * installed_groups 는 {@code COMPANY_MNG.INSTALLED_GROUPS} (JSONB) 에 저장.
 */
@Service
@RequiredArgsConstructor
@Slf4j
public class CompanyTemplatesService {

    private final SqlSession sqlSession;
    private final TenantDbSettings settings;
    private final ObjectMapper objectMapper;

    /**
     * 설치된 그룹 id 조회. 메타 DB 의 COMPANY_MNG.INSTALLED_GROUPS 에서 읽어옴.
     * 빈 배열이면 legacy 회사 → 필수 그룹만 최소 반환.
     */
    public List<Map<String, Object>> listInstalledGroups(String companyCode) {
        String prevCtx = DbContextHolder.get();
        DbContextHolder.setMeta();
        try {
            Map<String, Object> p = Map.of("company_code", companyCode);
            Map<String, Object> row = sqlSession.selectOne("provisioning.selectCompanyByCode", p);
            if (row == null) return List.of();

            List<String> ids = parseJsonArray(row.get("installed_groups"));
            // 빈 배열 이면 legacy — 필수 그룹은 반드시 깔렸다고 가정
            if (ids.isEmpty()) {
                ids = TableGroup.requiredGroups().stream().map(TableGroup::name).toList();
            }

            List<Map<String, Object>> out = new ArrayList<>();
            Set<String> idSet = new HashSet<>(ids);
            for (TableGroup g : TableGroup.values()) {
                Map<String, Object> m = new LinkedHashMap<>(g.toMap());
                m.put("installed", idSet.contains(g.name()));
                out.add(m);
            }
            return out;
        } finally {
            restoreCtx(prevCtx);
        }
    }

    /**
     * provisioning 시 initiate() 에서 호출 — selected + required 를 JSONB 로 저장.
     */
    public void saveInstalledGroups(String companyCode, List<String> selectedGroupIds) {
        String prevCtx = DbContextHolder.get();
        DbContextHolder.setMeta();
        try {
            Set<String> all = new HashSet<>(selectedGroupIds == null ? List.of() : selectedGroupIds);
            TableGroup.requiredGroups().forEach(g -> all.add(g.name()));
            // 유효한 그룹 id 만
            List<String> validated = all.stream()
                    .filter(id -> TableGroup.parse(id) != null)
                    .sorted()
                    .toList();
            String json = objectMapper.writeValueAsString(validated);
            Map<String, Object> p = new LinkedHashMap<>();
            p.put("company_code", companyCode);
            p.put("installed_groups_json", json);
            sqlSession.update("provisioning.updateInstalledGroups", p);
            log.info("[CompanyTemplates] saved installed_groups for {}: {}", companyCode, validated);
        } catch (Exception e) {
            log.error("[CompanyTemplates] saveInstalledGroups failed {}: {}", companyCode, e.getMessage());
        } finally {
            restoreCtx(prevCtx);
        }
    }

    /**
     * 템플릿 재복제. 선택된 그룹의 테이블에서 공통 template row 를 메타→tenant 로 추가.
     * ON CONFLICT DO NOTHING 으로 idempotent. 사용자 데이터는 건드리지 않음.
     *
     * @return { tables: [{name, inserted, skipped}], total_inserted, errors }
     */
    public Map<String, Object> recopy(String srcDbName, String dstDbName, List<String> selectedGroupIds) {
        List<String> tables = gatherTables(selectedGroupIds);
        Map<String, Object> result = new LinkedHashMap<>();
        List<Map<String, Object>> perTable = new ArrayList<>();
        int totalInserted = 0;
        List<String> errors = new ArrayList<>();

        try (Connection src = DriverManager.getConnection(
                settings.buildJdbcUrl(srcDbName), settings.username(), settings.password());
             Connection dst = DriverManager.getConnection(
                     settings.buildJdbcUrl(dstDbName), settings.username(), settings.password())) {

            src.setAutoCommit(true);
            dst.setAutoCommit(false);

            for (String table : tables) {
                Map<String, Object> row = new LinkedHashMap<>();
                row.put("table", table);
                try {
                    int inserted = copyTableIdempotent(src, dst, table);
                    row.put("inserted", inserted);
                    row.put("status", "ok");
                    totalInserted += inserted;
                } catch (SQLException e) {
                    row.put("inserted", 0);
                    row.put("status", "error");
                    row.put("error", e.getMessage());
                    errors.add(table + ": " + e.getMessage());
                    log.warn("[CompanyTemplates] recopy table={} failed: {}", table, e.getMessage());
                }
                perTable.add(row);
            }
            dst.commit();
        } catch (SQLException e) {
            errors.add("connection: " + e.getMessage());
            log.error("[CompanyTemplates] recopy top-level failure {}: {}", dstDbName, e.getMessage());
        }

        result.put("tables", perTable);
        result.put("total_inserted", totalInserted);
        result.put("errors", errors);
        result.put("selected_groups", selectedGroupIds);
        return result;
    }

    /** 복사 + ON CONFLICT DO NOTHING. inserted row 수 반환. */
    private int copyTableIdempotent(Connection src, Connection dst, String table) throws SQLException {
        boolean hasCompanyCode = columnExists(src, table, "company_code");
        List<String> cols = listColumns(src, table);
        if (cols.isEmpty()) return 0;

        String where = hasCompanyCode ? " WHERE company_code IS NULL OR company_code IN ('*', 'TEMPLATE')" : "";
        String quotedCols = cols.stream().map(c -> "\"" + c + "\"").collect(Collectors.joining(","));
        String selectSql = "SELECT " + quotedCols + " FROM \"" + table + "\"" + where;
        String placeholders = cols.stream().map(c -> "?").collect(Collectors.joining(","));
        String insertSql = "INSERT INTO \"" + table + "\" (" + quotedCols + ") VALUES (" + placeholders + ") ON CONFLICT DO NOTHING";

        int inserted = 0;
        try (PreparedStatement sel = src.prepareStatement(selectSql);
             ResultSet rs = sel.executeQuery();
             PreparedStatement ins = dst.prepareStatement(insertSql)) {
            ResultSetMetaData meta = rs.getMetaData();
            int colCount = meta.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= colCount; i++) ins.setObject(i, rs.getObject(i));
                inserted += ins.executeUpdate();
            }
        }
        return inserted;
    }

    private List<String> gatherTables(List<String> selectedGroupIds) {
        if (selectedGroupIds == null || selectedGroupIds.isEmpty()) return List.of();
        List<String> result = new ArrayList<>();
        for (String id : selectedGroupIds) {
            TableGroup g = TableGroup.parse(id);
            if (g != null) result.addAll(g.getTables());
        }
        return result;
    }

    private List<String> listColumns(Connection conn, String table) throws SQLException {
        List<String> cols = new ArrayList<>();
        try (PreparedStatement ps = conn.prepareStatement(
                "SELECT column_name FROM information_schema.columns " +
                        "WHERE table_schema='public' AND lower(table_name)=lower(?) " +
                        "ORDER BY ordinal_position")) {
            ps.setString(1, table);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) cols.add(rs.getString(1));
            }
        }
        return cols;
    }

    private boolean columnExists(Connection conn, String table, String col) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(
                "SELECT 1 FROM information_schema.columns " +
                        "WHERE table_schema='public' AND lower(table_name)=lower(?) AND lower(column_name)=lower(?)")) {
            ps.setString(1, table);
            ps.setString(2, col);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next();
            }
        }
    }

    private List<String> parseJsonArray(Object raw) {
        if (raw == null) return List.of();
        try {
            return objectMapper.readValue(raw.toString(), new TypeReference<List<String>>() {});
        } catch (Exception e) {
            log.warn("[CompanyTemplates] parse installed_groups failed: {}", e.getMessage());
            return List.of();
        }
    }

    private static void restoreCtx(String prev) {
        if (prev == null) DbContextHolder.clear();
        else DbContextHolder.set(prev);
    }
}
