package com.cloudhandson.ddsbackoffice.service;

import com.cloudhandson.ddsbackoffice.domain.DdsMcpEndUserPublishResult;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.regex.Pattern;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

/**
 * Projects the existing application user/role/permission model to the local
 * DDS identities consumed by the SSE MCP endpoint. No IAM user is created or
 * changed here: OCI IAM only authorizes the service to attach a context.
 */
@Service
public class DdsMcpEndUserPublisher {

  private static final String LOOKUP_KEY_REFERENCE = "DDS_OCI_IAM_CLIENT_SECRET_DERIVED_V1";
  private static final Pattern TAG = Pattern.compile("[A-Za-z0-9_-]+");

  private final JdbcTemplate jdbcTemplate;

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

  public DdsMcpEndUserPublishResult publish() {
    ensureMapTable();
    synchronizeMappings();

    int published = 0;
    int defaultDenied = 0;
    int revoked = 0;
    for (Mapping mapping : mappings()) {
      try {
        if (mapping.active()) {
          ensureEndUser(mapping);
          ensureDataRole(mapping);
          ensureRoleGrant(mapping);
          String predicate = vectorPredicate(mapping.applicationUserId());
          replaceVectorGrant(mapping, predicate, excludedVectorColumns(mapping.applicationUserId()));
          mark(mapping, "PUBLISHED", null);
          published++;
          if (predicate == null) {
            defaultDenied++;
          }
        } else {
          dropVectorGrant(mapping);
          revokeRoleGrant(mapping);
          mark(mapping, "REVOKED", null);
          revoked++;
        }
      } catch (DataAccessException exception) {
        markFailed(mapping);
        throw new DataAccessResourceFailureException(
            "DDS MCP END USER 권한을 게시하지 못했습니다. 기존 DDS 설정과 DB 권한을 확인하세요.", exception);
      }
    }
    return new DdsMcpEndUserPublishResult(published, defaultDenied, revoked);
  }

  private void ensureMapTable() {
    try {
      jdbcTemplate.queryForObject("SELECT COUNT(*) FROM cb_dds_end_user_map", Integer.class);
    } catch (DataAccessException missing) {
      jdbcTemplate.execute("""
          CREATE TABLE cb_dds_end_user_map (
            application_user_id NUMBER PRIMARY KEY REFERENCES cb_app_user(user_id),
            end_user_name       VARCHAR2(128) NOT NULL UNIQUE,
            data_role_name      VARCHAR2(128) NOT NULL UNIQUE,
            lookup_key_ref      VARCHAR2(128) NOT NULL,
            grant_name          VARCHAR2(128) NOT NULL,
            publish_status      VARCHAR2(20) NOT NULL
              CHECK (publish_status IN ('PENDING', 'PUBLISHED', 'REVOKED', 'FAILED')),
            published_at        TIMESTAMP,
            last_error          VARCHAR2(1000)
          )
          """);
    }
  }

  private void synchronizeMappings() {
    jdbcTemplate.update("""
        MERGE INTO cb_dds_end_user_map target
        USING (
          SELECT user_id AS application_user_id,
                 'DDS_U_' || TO_CHAR(user_id) AS end_user_name,
                 'DDS_U_' || TO_CHAR(user_id) || '_ROLE' AS data_role_name,
                 ? AS lookup_key_ref,
                 'DDS_MCP_U_' || TO_CHAR(user_id) || '_VECTOR_GRANT' AS grant_name
          FROM cb_app_user
        ) source
        ON (target.application_user_id = source.application_user_id)
        WHEN MATCHED THEN UPDATE SET
          target.end_user_name = source.end_user_name,
          target.data_role_name = source.data_role_name,
          target.lookup_key_ref = source.lookup_key_ref,
          target.grant_name = source.grant_name
        WHEN NOT MATCHED THEN INSERT (
          application_user_id, end_user_name, data_role_name, lookup_key_ref,
          grant_name, publish_status
        ) VALUES (
          source.application_user_id, source.end_user_name, source.data_role_name,
          source.lookup_key_ref, source.grant_name, 'PENDING'
        )
        """, LOOKUP_KEY_REFERENCE);
  }

  private List<Mapping> mappings() {
    return jdbcTemplate.query("""
        SELECT m.application_user_id, m.end_user_name, m.data_role_name, m.grant_name,
               u.active
        FROM cb_dds_end_user_map m
        JOIN cb_app_user u ON u.user_id = m.application_user_id
        ORDER BY m.application_user_id
        """, (row, ignored) -> new Mapping(
            row.getLong("application_user_id"),
            row.getString("end_user_name"),
            row.getString("data_role_name"),
            row.getString("grant_name"),
            "Y".equalsIgnoreCase(row.getString("active"))));
  }

  private void ensureEndUser(Mapping mapping) {
    if (!endUserExists(mapping.endUserName())) {
      jdbcTemplate.execute("CREATE END USER \"" + mapping.endUserName() + "\"");
    }
  }

  private void ensureDataRole(Mapping mapping) {
    if (!dataRoleExists(mapping.dataRoleName())) {
      jdbcTemplate.execute("CREATE DATA ROLE " + mapping.dataRoleName());
    }
  }

  private void ensureRoleGrant(Mapping mapping) {
    Integer grants = jdbcTemplate.queryForObject("""
        SELECT COUNT(*) FROM dba_data_role_grants
        WHERE grantee = ? AND data_role = ?
        """, Integer.class, mapping.endUserName(), mapping.dataRoleName());
    if (grants == null || grants == 0) {
      jdbcTemplate.execute("GRANT DATA ROLE " + mapping.dataRoleName()
          + " TO \"" + mapping.endUserName() + "\"");
    }
  }

  private void revokeRoleGrant(Mapping mapping) {
    Integer grants = jdbcTemplate.queryForObject("""
        SELECT COUNT(*) FROM dba_data_role_grants
        WHERE grantee = ? AND data_role = ?
        """, Integer.class, mapping.endUserName(), mapping.dataRoleName());
    if (grants != null && grants > 0) {
      jdbcTemplate.execute("REVOKE DATA ROLE " + mapping.dataRoleName()
          + " FROM \"" + mapping.endUserName() + "\"");
    }
  }

  private void replaceVectorGrant(Mapping mapping, String predicate, String excludedColumns) {
    dropVectorGrant(mapping);
    if (predicate == null) {
      return;
    }
    String select = excludedColumns == null || excludedColumns.isBlank()
        ? "AS SELECT"
        : "AS SELECT (ALL COLUMNS EXCEPT " + excludedColumns + ")";
    jdbcTemplate.execute("CREATE DATA GRANT ADMIN." + mapping.grantName() + " " + select
        + " ON ADMIN.CB_DDS_VECTOR_SEARCH_DOCUMENTS WHERE " + predicate
        + " TO " + mapping.dataRoleName());
  }

  private void dropVectorGrant(Mapping mapping) {
    Integer grants = jdbcTemplate.queryForObject("""
        SELECT COUNT(*) FROM dba_data_grants
        WHERE owner = 'ADMIN' AND grant_name = ?
        """, Integer.class, mapping.grantName());
    if (grants != null && grants > 0) {
      jdbcTemplate.execute("DROP DATA GRANT ADMIN." + mapping.grantName());
    }
  }

  private String vectorPredicate(long userId) {
    List<PermissionRule> rules = jdbcTemplate.query("""
        WITH effective_role AS (
          SELECT role_id FROM cb_user_role WHERE user_id = ?
          UNION
          SELECT gr.role_id
          FROM cb_user_group ug
          JOIN cb_app_group g ON g.group_id = ug.group_id AND g.active_yn = 'Y'
          JOIN cb_group_role gr ON gr.group_id = ug.group_id
          WHERE ug.user_id = ?
        )
        SELECT NVL(UPPER(TRIM(p.permission_effect)), 'ALLOW') AS permission_effect,
               UPPER(TRIM(r.rule_type)) AS rule_type,
               TRIM(r.rule_value) AS rule_value
        FROM effective_role er
        JOIN cb_permission p ON p.role_id = er.role_id
        JOIN cb_permission_rule r ON r.perm_id = p.perm_id
        WHERE p.target_name = 'CB_VECTOR_SEARCH_DOCUMENTS'
          AND p.action_name = 'SELECT'
        ORDER BY p.perm_id, r.rule_id
        """, (row, ignored) -> new PermissionRule(
            row.getString("permission_effect"), row.getString("rule_type"), row.getString("rule_value")),
        userId, userId);
    List<String> allow = new ArrayList<>();
    List<String> deny = new ArrayList<>();
    for (PermissionRule rule : rules) {
      String clause = clause(rule);
      if (clause == null) {
        continue;
      }
      ("DENY".equalsIgnoreCase(rule.effect()) ? deny : allow).add(clause);
    }
    if (allow.isEmpty()) {
      return null;
    }
    String allowExpression = disjunction(allow);
    return deny.isEmpty() ? allowExpression : "(" + allowExpression + ") AND NOT (" + disjunction(deny) + ")";
  }

  private String clause(PermissionRule rule) {
    if ("ALL".equals(rule.type())) {
      return "1 = 1";
    }
    if ("TAG".equals(rule.type()) && rule.value() != null && TAG.matcher(rule.value()).matches()) {
      String tag = rule.value().toUpperCase(Locale.ROOT);
      return "REGEXP_LIKE(UPPER(tech_tag), '(^|,)" + tag + "(,|$)')";
    }
    return null;
  }

  private String disjunction(List<String> clauses) {
    return clauses.size() == 1 ? clauses.getFirst() : "(" + String.join(") OR (", clauses) + ")";
  }

  private String excludedVectorColumns(long userId) {
    List<String> columns = jdbcTemplate.query("""
        SELECT pc.column_name
        FROM cb_protected_column pc
        JOIN cb_protected_object po ON po.object_id = pc.object_id
        WHERE po.object_name = 'CB_VECTOR_SEARCH_DOCUMENTS'
          AND pc.sensitive_yn = 'Y'
          AND pc.column_name <> 'EMBEDDING'
          AND NOT EXISTS (
            WITH effective_role AS (
              SELECT role_id FROM cb_user_role WHERE user_id = ?
              UNION
              SELECT gr.role_id
              FROM cb_user_group ug
              JOIN cb_app_group g ON g.group_id = ug.group_id AND g.active_yn = 'Y'
              JOIN cb_group_role gr ON gr.group_id = ug.group_id
              WHERE ug.user_id = ?
            )
            SELECT 1
            FROM effective_role er
            JOIN cb_permission p ON p.role_id = er.role_id
            JOIN cb_permission_column allowed ON allowed.permission_id = p.perm_id
            WHERE p.target_name = 'CB_VECTOR_SEARCH_DOCUMENTS'
              AND p.action_name = 'SELECT'
              AND p.permission_effect = 'ALLOW'
              AND allowed.column_name = pc.column_name
          )
        ORDER BY pc.column_name
        """, (row, ignored) -> row.getString("column_name"), userId, userId);
    return String.join(", ", columns);
  }

  private boolean endUserExists(String endUserName) {
    Integer count = jdbcTemplate.queryForObject(
        "SELECT COUNT(*) FROM dba_end_users WHERE username = ?", Integer.class, endUserName);
    return count != null && count > 0;
  }

  private boolean dataRoleExists(String dataRoleName) {
    Integer count = jdbcTemplate.queryForObject(
        "SELECT COUNT(*) FROM dba_data_roles WHERE data_role = ?", Integer.class, dataRoleName);
    return count != null && count > 0;
  }

  private void mark(Mapping mapping, String status, String error) {
    jdbcTemplate.update("""
        UPDATE cb_dds_end_user_map
        SET publish_status = ?, published_at = SYSTIMESTAMP, last_error = ?
        WHERE application_user_id = ?
        """, status, error, mapping.applicationUserId());
  }

  private void markFailed(Mapping mapping) {
    try {
      mark(mapping, "FAILED", "DDS MCP local END USER publish failed");
    } catch (DataAccessException ignored) {
      // Keep the original DDL failure as the request failure.
    }
  }

  private record Mapping(
      long applicationUserId,
      String endUserName,
      String dataRoleName,
      String grantName,
      boolean active
  ) {
  }

  private record PermissionRule(String effect, String type, String value) {
  }
}
