package com.cloudhandson.vpdbackoffice.service;

import com.cloudhandson.vpdbackoffice.domain.structured.StructuredDataPreview;
import com.cloudhandson.vpdbackoffice.domain.structured.StructuredDataTable;
import java.util.List;
import java.util.Map;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class StructuredDataService {

  private static final String OWNER = "POC_2";
  private static final int ROW_LIMIT = 50;
  private static final List<StructuredDataTable> TABLES = List.of(
      new StructuredDataTable("customers", "KB_CUSTOMERS", "고객원장", "고객 기본정보"),
      new StructuredDataTable("products", "KB_PRODUCTS", "상품원장", "보험상품 마스터"),
      new StructuredDataTable("contracts", "KB_CONTRACTS", "계약원장", "보험계약 정보"),
      new StructuredDataTable("coverages", "KB_COVERAGES", "담보원장", "보장·특약 정보"),
      new StructuredDataTable("claims", "KB_CLAIMS", "청구원장", "보험금 청구·지급 정보"),
      new StructuredDataTable("external-holdings", "KB_EXTERNAL_HOLDINGS", "외부보유정보 원장", "타사·외부 가입·보유 정보"),
      new StructuredDataTable("stakeholders", "KB_STAKEHOLDERS", "이해관계자 원장", "역할·담당자 매핑"));

  private final JdbcTemplate jdbcTemplate;

  public StructuredDataService(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
  }

  public List<StructuredDataTable> tables() {
    return TABLES;
  }

  public String defaultKey() {
    return TABLES.getFirst().key();
  }

  public StructuredDataTable requireTable(String key) {
    return TABLES.stream()
        .filter(table -> table.key().equals(key))
        .findFirst()
        .orElseThrow(() -> new AppException("선택할 수 없는 정형 데이터 테이블입니다."));
  }

  public StructuredDataPreview preview(String key) {
    StructuredDataTable table = requireTable(key);
    try {
      List<String> columns = jdbcTemplate.query(
          """
              SELECT column_name
              FROM all_tab_columns
              WHERE owner = ?
                AND table_name = ?
              ORDER BY column_id
              """,
          (resultSet, rowNum) -> resultSet.getString(1), OWNER, table.tableName());
      if (columns.isEmpty()) {
        throw new AppException("정형 데이터 테이블의 컬럼 정보를 찾을 수 없습니다.");
      }

      List<Map<String, Object>> rows = jdbcTemplate.queryForList(
          previewSql(table), ROW_LIMIT);
      return new StructuredDataPreview(table, columns, rows, ROW_LIMIT);
    } catch (DataAccessException exception) {
      throw new AppException("정형 데이터를 조회할 수 없습니다. POC_2 조회 권한과 대상 테이블 상태를 확인하세요.");
    }
  }

  /**
   * The table is selected from a closed application whitelist, so the query
   * text remains fixed and no request value can become a SQL identifier.
   */
  private String previewSql(StructuredDataTable table) {
    return switch (table.key()) {
      case "customers" -> "SELECT * FROM POC_2.KB_CUSTOMERS WHERE ROWNUM <= ?";
      case "products" -> "SELECT * FROM POC_2.KB_PRODUCTS WHERE ROWNUM <= ?";
      case "contracts" -> "SELECT * FROM POC_2.KB_CONTRACTS WHERE ROWNUM <= ?";
      case "coverages" -> "SELECT * FROM POC_2.KB_COVERAGES WHERE ROWNUM <= ?";
      case "claims" -> "SELECT * FROM POC_2.KB_CLAIMS WHERE ROWNUM <= ?";
      case "external-holdings" -> "SELECT * FROM POC_2.KB_EXTERNAL_HOLDINGS WHERE ROWNUM <= ?";
      case "stakeholders" -> "SELECT * FROM POC_2.KB_STAKEHOLDERS WHERE ROWNUM <= ?";
      default -> throw new AppException("선택할 수 없는 정형 데이터 테이블입니다.");
    };
  }
}
