package com.cloudhandson.vpdbackoffice.service;

import com.cloudhandson.vpdbackoffice.domain.schemametadata.SchemaAnnotation;
import com.cloudhandson.vpdbackoffice.domain.schemametadata.SchemaMetadataAnnotationRow;
import com.cloudhandson.vpdbackoffice.domain.schemametadata.SchemaMetadataColumn;
import com.cloudhandson.vpdbackoffice.domain.schemametadata.SchemaMetadataColumnRow;
import com.cloudhandson.vpdbackoffice.domain.schemametadata.SchemaMetadataView;
import com.cloudhandson.vpdbackoffice.domain.structured.StructuredDataTable;
import com.cloudhandson.vpdbackoffice.mapper.SchemaMetadataMapper;
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.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * Screen-level orchestration for schema metadata. Database reads and DDL are
 * intentionally delegated only to {@link SchemaMetadataMapper}.
 */
@Service
public class SchemaMetadataService {

  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 SchemaMetadataMapper mapper;
  private final StructuredDataService structuredDataService;
  private final DataCatalog catalog;

  public SchemaMetadataService(
      SchemaMetadataMapper mapper,
      StructuredDataService structuredDataService, DataCatalog catalog
  ) {
    this.mapper = mapper;
    this.structuredDataService = structuredDataService;
    this.catalog = catalog;
  }

  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 = requireSimpleName(table.tableName(), "table name");
    Map<String, List<SchemaAnnotation>> annotations = annotationsByTarget(tableName);
    List<SchemaMetadataColumn> columns = columns(tableName, annotations);
    return new SchemaMetadataView(
        table,
        nullToEmpty(mapper.findTableComment(catalog.owner(), tableName)),
        annotations.getOrDefault(tableTargetKey(), List.of()),
        columns
    );
  }

  @Transactional
  public void updateTableComment(String tableKey, String comment) {
    StructuredDataTable table = structuredDataService.requireTable(tableKey);
    String tableName = requireSimpleName(table.tableName(), "table name");
    String normalizedComment = normalizeText(comment, MAX_COMMENT_LENGTH, "테이블 comment");
    mapper.updateTableComment(catalog.owner(), tableName, quoteLiteral(normalizedComment));
  }

  @Transactional
  public void updateColumnComment(String tableKey, String columnName, String comment) {
    StructuredDataTable table = structuredDataService.requireTable(tableKey);
    String tableName = requireSimpleName(table.tableName(), "table name");
    String column = requireColumn(tableName, columnName);
    String normalizedComment = normalizeText(comment, MAX_COMMENT_LENGTH, "컬럼 comment");
    mapper.updateColumnComment(catalog.owner(), tableName, column, quoteLiteral(normalizedComment));
  }

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

  @Transactional
  public void updateColumnAnnotation(
      String tableKey,
      String columnName,
      String annotationName,
      String annotationValue
  ) {
    StructuredDataTable table = structuredDataService.requireTable(tableKey);
    String tableName = requireSimpleName(table.tableName(), "table name");
    String column = requireColumn(tableName, columnName);
    updateAnnotation(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)) {
      if (columnName == null) {
        mapper.dropTableAnnotation(catalog.owner(), tableName, key);
      } else {
        mapper.dropColumnAnnotation(catalog.owner(), tableName, columnName, key);
      }
    }
    if (!value.isBlank()) {
      if (columnName == null) {
        mapper.addTableAnnotation(catalog.owner(), tableName, key, quoteLiteral(value));
      } else {
        mapper.addColumnAnnotation(catalog.owner(), tableName, columnName, key, quoteLiteral(value));
      }
    }
  }

  private List<SchemaMetadataColumn> columns(
      String tableName,
      Map<String, List<SchemaAnnotation>> annotations
  ) {
    return mapper.findColumns(catalog.owner(), tableName).stream()
        .map(row -> toColumn(row, annotations))
        .toList();
  }

  private SchemaMetadataColumn toColumn(
      SchemaMetadataColumnRow row,
      Map<String, List<SchemaAnnotation>> annotations
  ) {
    String columnName = requireSimpleName(row.columnName(), "column name");
    return new SchemaMetadataColumn(
        columnName,
        row.dataType(),
        "Y".equalsIgnoreCase(row.nullable()),
        nullToEmpty(row.comment()),
        annotations.getOrDefault(columnTargetKey(columnName), List.of())
    );
  }

  private Map<String, List<SchemaAnnotation>> annotationsByTarget(String tableName) {
    Map<String, LinkedHashMap<String, List<String>>> grouped = new LinkedHashMap<>();
    for (SchemaMetadataAnnotationRow row : mapper.findAnnotations(tableName)) {
      String target = row.columnName() == null
          ? tableTargetKey()
          : columnTargetKey(row.columnName());
      grouped
          .computeIfAbsent(target, ignored -> new LinkedHashMap<>())
          .computeIfAbsent(row.annotationName(), ignored -> new ArrayList<>())
          .add(nullToEmpty(row.annotationValue()));
    }

    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) {
    return columnName == null
        ? mapper.countTableAnnotation(tableName, annotationName) > 0
        : mapper.countColumnAnnotation(tableName, columnName, annotationName) > 0;
  }

  private String requireColumn(String tableName, String columnName) {
    String column = requireSimpleName(columnName, "column name");
    if (mapper.countColumn(catalog.owner(), tableName, column) == 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 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");
  }
}
