package com.cloudhandson.vpdbackoffice.service;

import com.cloudhandson.vpdbackoffice.domain.schemametadata.SchemaAnnotation;
import com.cloudhandson.vpdbackoffice.domain.schemametadata.SchemaMetadataColumn;
import com.cloudhandson.vpdbackoffice.domain.schemametadata.SchemaMetadataView;
import com.cloudhandson.vpdbackoffice.domain.structured.StructuredDataTable;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.regex.Pattern;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class SchemaMetadataService {

  private static final String OWNER = "POC_2";
  private static final int MAX_COMMENT_LENGTH = 4000;
  private static final int MAX_ANNOTATION_VALUE_LENGTH = 4000;
  private static final Pattern ORACLE_SIMPLE_NAME = Pattern.compile("[A-Z][A-Z0-9_$#]{0,127}");

  private final JdbcTemplate jdbcTemplate;
  private final StructuredDataService structuredDataService;

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

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

  public String defaultKey() {
    return structuredDataService.defaultKey();
  }

  public SchemaMetadataView find(String tableKey) {
    StructuredDataTable table = structuredDataService.requireTable(tableKey);
    String tableName = table.tableName();
    String tableComment = tableComment(tableName);
    Map<String, List<SchemaAnnotation>> annotations = annotationsByTarget(tableName);
    List<SchemaMetadataColumn> columns = columns(tableName, annotations);
    return new SchemaMetadataView(
        table,
        nullToEmpty(tableComment),
        annotations.getOrDefault(tableTargetKey(), List.of()),
        columns
    );
  }

  @Transactional
  public void updateTableComment(String tableKey, String comment) {
    StructuredDataTable table = structuredDataService.requireTable(tableKey);
    String normalizedComment = normalizeText(comment, MAX_COMMENT_LENGTH, "테이블 comment");
    jdbcTemplate.execute("COMMENT ON TABLE " + qualifiedTable(table.tableName())
        + " IS " + quoteLiteral(normalizedComment));
  }

  @Transactional
  public void updateColumnComment(String tableKey, String columnName, String comment) {
    StructuredDataTable table = structuredDataService.requireTable(tableKey);
    String column = requireColumn(table.tableName(), columnName);
    String normalizedComment = normalizeText(comment, MAX_COMMENT_LENGTH, "컬럼 comment");
    jdbcTemplate.execute("COMMENT ON COLUMN " + qualifiedTable(table.tableName()) + "."
        + quoteName(column) + " IS " + quoteLiteral(normalizedComment));
  }

  @Transactional
  public void updateTableAnnotation(String tableKey, String annotationName, String annotationValue) {
    StructuredDataTable table = structuredDataService.requireTable(tableKey);
    updateAnnotation(table.tableName(), null, annotationName, annotationValue);
  }

  @Transactional
  public void updateColumnAnnotation(
      String tableKey,
      String columnName,
      String annotationName,
      String annotationValue
  ) {
    StructuredDataTable table = structuredDataService.requireTable(tableKey);
    String column = requireColumn(table.tableName(), columnName);
    updateAnnotation(table.tableName(), column, annotationName, annotationValue);
  }

  private void updateAnnotation(
      String tableName,
      String columnName,
      String annotationName,
      String annotationValue
  ) {
    String key = requireSimpleName(annotationName, "annotation name");
    String value = normalizeText(annotationValue, MAX_ANNOTATION_VALUE_LENGTH, "annotation value");
    if (annotationExists(tableName, columnName, key)) {
      jdbcTemplate.execute(annotationSql(tableName, columnName, "DROP " + quoteName(key)));
    }
    if (!value.isBlank()) {
      jdbcTemplate.execute(annotationSql(tableName, columnName,
          "ADD " + quoteName(key) + " " + quoteLiteral(value)));
    }
  }

  private String tableComment(String tableName) {
    List<String> values = jdbcTemplate.query("""
        SELECT comments
        FROM all_tab_comments
        WHERE owner = ?
          AND table_name = ?
        """, (rs, rowNum) -> rs.getString(1), OWNER, tableName);
    return values.isEmpty() ? "" : values.getFirst();
  }

  private List<SchemaMetadataColumn> columns(
      String tableName,
      Map<String, List<SchemaAnnotation>> annotations
  ) {
    return jdbcTemplate.query("""
        SELECT c.column_name,
               CASE
                 WHEN c.data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR')
                   THEN c.data_type || '(' || c.char_length || ')'
                 WHEN c.data_type = 'NUMBER' AND c.data_precision IS NOT NULL AND c.data_scale IS NOT NULL
                   THEN c.data_type || '(' || c.data_precision || ',' || c.data_scale || ')'
                 WHEN c.data_type = 'NUMBER' AND c.data_precision IS NOT NULL
                   THEN c.data_type || '(' || c.data_precision || ')'
                 ELSE c.data_type
               END AS display_type,
               c.nullable,
               cc.comments
        FROM all_tab_columns c
        LEFT JOIN all_col_comments cc
          ON cc.owner = c.owner
         AND cc.table_name = c.table_name
         AND cc.column_name = c.column_name
        WHERE c.owner = ?
          AND c.table_name = ?
        ORDER BY c.column_id
        """, (rs, rowNum) -> new SchemaMetadataColumn(
            rs.getString("column_name"),
            rs.getString("display_type"),
            "Y".equalsIgnoreCase(rs.getString("nullable")),
            nullToEmpty(rs.getString("comments")),
            annotations.getOrDefault(columnTargetKey(rs.getString("column_name")), List.of())
        ), OWNER, tableName);
  }

  private Map<String, List<SchemaAnnotation>> annotationsByTarget(String tableName) {
    Map<String, LinkedHashMap<String, List<String>>> grouped = new LinkedHashMap<>();
    jdbcTemplate.query("""
        SELECT column_name, annotation_name, annotation_value
        FROM all_annotations_usage
        WHERE object_name = ?
          AND object_type = 'TABLE'
        ORDER BY column_name NULLS FIRST, annotation_name, annotation_value
        """, rs -> {
          String target = rs.getString("column_name") == null
              ? tableTargetKey()
              : columnTargetKey(rs.getString("column_name"));
          grouped
              .computeIfAbsent(target, ignored -> new LinkedHashMap<>())
              .computeIfAbsent(rs.getString("annotation_name"), ignored -> new ArrayList<>())
              .add(nullToEmpty(rs.getString("annotation_value")));
        }, tableName);

    Map<String, List<SchemaAnnotation>> result = new LinkedHashMap<>();
    grouped.forEach((target, valuesByName) -> {
      List<SchemaAnnotation> annotations = new ArrayList<>();
      valuesByName.forEach((name, values) -> annotations.add(new SchemaAnnotation(
          name,
          String.join("\n--- duplicate annotation value ---\n", values)
      )));
      result.put(target, annotations);
    });
    return result;
  }

  private boolean annotationExists(String tableName, String columnName, String annotationName) {
    Integer count = columnName == null
        ? jdbcTemplate.queryForObject("""
            SELECT COUNT(*)
            FROM all_annotations_usage
            WHERE object_name = ?
              AND object_type = 'TABLE'
              AND annotation_name = ?
              AND column_name IS NULL
            """, Integer.class, tableName, annotationName)
        : jdbcTemplate.queryForObject("""
            SELECT COUNT(*)
            FROM all_annotations_usage
            WHERE object_name = ?
              AND object_type = 'TABLE'
              AND annotation_name = ?
              AND column_name = ?
            """, Integer.class, tableName, annotationName, columnName);
    return count != null && count > 0;
  }

  private String annotationSql(String tableName, String columnName, String operation) {
    if (columnName == null) {
      return "ALTER TABLE " + qualifiedTable(tableName) + " ANNOTATIONS (" + operation + ")";
    }
    return "ALTER TABLE " + qualifiedTable(tableName) + " MODIFY " + quoteName(columnName)
        + " ANNOTATIONS (" + operation + ")";
  }

  private String requireColumn(String tableName, String columnName) {
    String column = requireSimpleName(columnName, "column name");
    Integer count = jdbcTemplate.queryForObject("""
        SELECT COUNT(*)
        FROM all_tab_columns
        WHERE owner = ?
          AND table_name = ?
          AND column_name = ?
        """, Integer.class, OWNER, tableName, column);
    if (count == null || count == 0) {
      throw new AppException("선택한 테이블에 존재하지 않는 컬럼입니다.");
    }
    return column;
  }

  private String requireSimpleName(String value, String label) {
    if (value == null || value.isBlank()) {
      throw new AppException(label + "은(는) 필수입니다.");
    }
    String normalized = value.trim().toUpperCase(Locale.ROOT);
    if (!ORACLE_SIMPLE_NAME.matcher(normalized).matches()) {
      throw new AppException(label + " 형식이 올바르지 않습니다. 영문 대문자, 숫자, _, $, #만 사용할 수 있습니다.");
    }
    return normalized;
  }

  private String normalizeText(String value, int maxLength, String label) {
    String normalized = value == null ? "" : value.trim();
    if (normalized.length() > maxLength) {
      throw new AppException(label + "은(는) " + maxLength + "자 이하여야 합니다.");
    }
    return normalized;
  }

  private String qualifiedTable(String tableName) {
    return quoteName(OWNER) + "." + quoteName(requireSimpleName(tableName, "table name"));
  }

  private String quoteName(String value) {
    return "\"" + value.replace("\"", "\"\"") + "\"";
  }

  private String quoteLiteral(String value) {
    return "'" + value.replace("'", "''") + "'";
  }

  private String nullToEmpty(String value) {
    return value == null ? "" : value;
  }

  private String tableTargetKey() {
    return "<TABLE>";
  }

  private String columnTargetKey(String columnName) {
    return requireSimpleName(columnName, "column name");
  }
}
