package com.erp.provisioning;

import com.erp.tenant.TenantDbSettings;
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.Statement;
import java.util.List;
import java.util.Map;

/**
 * 회사관리 UI (v9 accordion) 렌더용 집계 서비스.
 *
 * 정적 필드는 {@code provisioning.listCompaniesForUi} 에서 조회.
 * Derived 필드는 회사마다 런타임 계산:
 *   - {@code users}          — tenant DB: {@code SELECT count(*) FROM user_info}
 *   - {@code db_size_bytes}  — 메타 서버: {@code SELECT pg_database_size(?)}
 *   - {@code db_size}        — 위 값 포맷팅 ("4.2 GB", "820 MB" 등)
 *   - {@code db_pct}         — db_size / DB_QUOTA_GB * 100
 *   - {@code active30}       — MVP 0. (last_login_date 컬럼 없음. 생기면 구현)
 *   - {@code spark}          — MVP 빈 배열. (일자별 사용자 집계 테이블 생기면 구현)
 *
 * ⚠ N+1 주의: 회사 N 개면 tenant DB 에 N 번 접속 + 메타 서버에 N 번 접속.
 *   회사 50 개 넘어가면 병렬 (CompletableFuture) 또는 집계 캐시 도입. 지금은 MVP.
 */
@Service
@RequiredArgsConstructor
@Slf4j
public class CompanyStatsService {

    private final TenantDbSettings settings;
    private final SqlSession sqlSession;

    public List<Map<String, Object>> listWithStats() {
        List<Map<String, Object>> rows = sqlSession.selectList("provisioning.listCompaniesForUi");
        for (Map<String, Object> r : rows) {
            enrichOne(r);
        }
        return rows;
    }

    private void enrichOne(Map<String, Object> r) {
        String dbName = (String) r.get("db_name");
        String dbStatus = (String) r.get("db_status");
        int quotaGb = toInt(r.get("db_quota_gb"), 20);

        // Placeholder defaults
        r.putIfAbsent("users", 0);
        r.putIfAbsent("active30", 0);
        r.putIfAbsent("db_size", "—");
        r.putIfAbsent("db_size_bytes", 0L);
        r.putIfAbsent("db_pct", 0);
        r.putIfAbsent("spark", List.of());

        if (!"active".equals(dbStatus) || dbName == null) {
            return;
        }

        // users — tenant DB
        try (Connection c = DriverManager.getConnection(
                settings.buildJdbcUrl(dbName), settings.username(), settings.password());
             Statement s = c.createStatement();
             ResultSet rs = s.executeQuery("SELECT count(*) FROM user_info")) {
            if (rs.next()) r.put("users", rs.getInt(1));
        } catch (Exception e) {
            log.debug("[Stats] users failed for {}: {}", dbName, e.getMessage());
        }

        // db size — 메타 postgres DB 에서 pg_database_size
        try (Connection c = DriverManager.getConnection(
                String.format("jdbc:postgresql://%s:%d/postgres", settings.host(), settings.port()),
                settings.username(), settings.password());
             PreparedStatement ps = c.prepareStatement("SELECT pg_database_size(?)")) {
            ps.setString(1, dbName);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    long bytes = rs.getLong(1);
                    r.put("db_size_bytes", bytes);
                    r.put("db_size", formatBytes(bytes));
                    long quotaBytes = (long) quotaGb * 1024L * 1024L * 1024L;
                    r.put("db_pct", quotaBytes > 0 ? (int) ((bytes * 100) / quotaBytes) : 0);
                }
            }
        } catch (Exception e) {
            log.debug("[Stats] db_size failed for {}: {}", dbName, e.getMessage());
        }
    }

    private static String formatBytes(long bytes) {
        if (bytes < 1024) return bytes + " B";
        if (bytes < 1024L * 1024) return String.format("%.1f KB", bytes / 1024.0);
        if (bytes < 1024L * 1024 * 1024) return String.format("%.1f MB", bytes / (1024.0 * 1024));
        return String.format("%.2f GB", bytes / (1024.0 * 1024 * 1024));
    }

    private static int toInt(Object v, int defaultVal) {
        if (v == null) return defaultVal;
        if (v instanceof Number n) return n.intValue();
        try { return Integer.parseInt(v.toString()); } catch (Exception e) { return defaultVal; }
    }
}
