package com.cloudhandson.ddsbackoffice.service;

import com.cloudhandson.ddsbackoffice.domain.DdsPublishedProtectionSpec;
import com.cloudhandson.ddsbackoffice.domain.DdsValidationEvidence;
import com.cloudhandson.ddsbackoffice.domain.DdsValidationFixture;
import java.sql.Timestamp;
import java.time.Instant;
import java.util.List;
import java.util.Optional;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class JdbcDdsProtectionEvidenceStore implements DdsProtectionEvidenceStore {

  private final JdbcTemplate jdbcTemplate;

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

  @Override
  public Optional<DdsPublishedProtectionSpec> latestPublished(String path, String subjectKey) {
    List<DdsPublishedProtectionSpec> rows = jdbcTemplate.query("""
        SELECT enforcement_path, subject_key, fingerprint, published_at
        FROM cb_dds_protection_snapshot
        WHERE enforcement_path = ? AND subject_key = ?
        ORDER BY published_at DESC, snapshot_id DESC
        FETCH FIRST 1 ROW ONLY
        """, (row, ignored) -> new DdsPublishedProtectionSpec(
        row.getString("enforcement_path"), row.getString("subject_key"),
        row.getString("fingerprint"), toInstant(row.getTimestamp("published_at"))
    ), path, subjectKey);
    return rows.stream().findFirst();
  }

  @Override
  public Optional<DdsValidationEvidence> latestValidation(String path, String subjectKey) {
    List<DdsValidationEvidence> rows = jdbcTemplate.query("""
        SELECT enforcement_path, subject_key,
               CASE WHEN SUM(CASE WHEN outcome = 'FAILED' THEN 1 ELSE 0 END) > 0
                    THEN 'FAILED' ELSE 'PASSED' END AS outcome,
               COUNT(*) AS fixture_count,
               SUM(CASE WHEN outcome = 'FAILED' THEN 1 ELSE 0 END) AS failed_count,
               MAX(created_at) AS validated_at
        FROM cb_dds_validation_evidence
        WHERE enforcement_path = ?
          AND subject_key = ?
          AND run_id = (
            SELECT run_id
            FROM (
              SELECT run_id
              FROM cb_dds_validation_evidence
              WHERE enforcement_path = ? AND subject_key = ?
              GROUP BY run_id
              ORDER BY MAX(created_at) DESC
            )
            WHERE ROWNUM = 1
          )
        GROUP BY enforcement_path, subject_key
        """, (row, ignored) -> new DdsValidationEvidence(
        row.getString("enforcement_path"), row.getString("subject_key"), row.getString("outcome"),
        row.getInt("fixture_count"), row.getInt("failed_count"), toInstant(row.getTimestamp("validated_at"))
    ), path, subjectKey, path, subjectKey);
    return rows.stream().findFirst();
  }

  @Override
  public List<DdsValidationFixture> activeFixtures(String path) {
    return jdbcTemplate.query("""
        SELECT fixture_id, enforcement_path, subject_key, application_user_id,
               document_id, expected_visible, description
        FROM cb_dds_validation_fixture
        WHERE enforcement_path = ? AND active_yn = 'Y'
        ORDER BY fixture_id
        """, (row, ignored) -> new DdsValidationFixture(
        row.getString("fixture_id"), row.getString("enforcement_path"), row.getString("subject_key"),
        row.getLong("application_user_id"), row.getString("document_id"),
        "Y".equalsIgnoreCase(row.getString("expected_visible")), row.getString("description")
    ), path);
  }

  @Override
  public void recordPublished(DdsPublishedProtectionSpec spec, String actor, String note) {
    jdbcTemplate.update("""
        INSERT INTO cb_dds_protection_snapshot (
          snapshot_id, object_name, enforcement_path, subject_key, fingerprint,
          actor, note, published_at
        ) VALUES (
          cb_dds_protection_snapshot_seq.NEXTVAL, ?, ?, ?, ?, ?, ?, SYSTIMESTAMP
        )
        """, spec.subjectKey().startsWith("service") ? "ADMIN.CB_DDS_VECTOR_SEARCH_DOCUMENTS" : "ADMIN.CB_DDS_VECTOR_SEARCH_DOCUMENTS",
        spec.path(), spec.subjectKey(), spec.fingerprint(), actor, note);
  }

  @Override
  public void recordValidation(
      String runId,
      String fixtureId,
      String path,
      String subjectKey,
      boolean expectedVisible,
      boolean actualVisible,
      String fingerprint
  ) {
    jdbcTemplate.update("""
        INSERT INTO cb_dds_validation_evidence (
          evidence_id, run_id, fixture_id, enforcement_path, subject_key,
          expected_visible, actual_visible, outcome, fingerprint, created_at
        ) VALUES (
          cb_dds_validation_evidence_seq.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, SYSTIMESTAMP
        )
        """, runId, fixtureId, path, subjectKey,
        expectedVisible ? "Y" : "N", actualVisible ? "Y" : "N",
        expectedVisible == actualVisible ? "PASSED" : "FAILED", fingerprint);
  }

  private static Instant toInstant(Timestamp timestamp) {
    return timestamp == null ? Instant.EPOCH : timestamp.toInstant();
  }
}
