package com.cloudhandson.ddsbackoffice.service;

import com.cloudhandson.ddsbackoffice.config.DdsProperties;
import com.cloudhandson.ddsbackoffice.domain.DdsPublishedProtectionSpec;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

/** Idempotent in-console installer for the evidence schema and non-secret fixtures. */
@Service
public class DdsProtectionSchemaService {
  private final JdbcTemplate jdbc;
  private final DdsProperties properties;
  private final DdsProtectionEvidenceStore evidence;

  public DdsProtectionSchemaService(JdbcTemplate jdbc, DdsProperties properties, DdsProtectionEvidenceStore evidence) {
    this.jdbc = jdbc; this.properties = properties; this.evidence = evidence;
  }

  public void initialize() {
    ensure("CREATE SEQUENCE cb_dds_protection_snapshot_seq START WITH 1 INCREMENT BY 1 NOCACHE");
    ensure("CREATE SEQUENCE cb_dds_validation_evidence_seq START WITH 1 INCREMENT BY 1 NOCACHE");
    ensure("CREATE TABLE cb_dds_protection_snapshot (snapshot_id NUMBER PRIMARY KEY, object_name VARCHAR2(261) NOT NULL, enforcement_path VARCHAR2(20) NOT NULL, subject_key VARCHAR2(100) NOT NULL, fingerprint VARCHAR2(64) NOT NULL, actor VARCHAR2(100) NOT NULL, note VARCHAR2(500), published_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL)");
    ensure("CREATE TABLE cb_dds_validation_fixture (fixture_id VARCHAR2(100) PRIMARY KEY, enforcement_path VARCHAR2(20) NOT NULL, subject_key VARCHAR2(100) NOT NULL, application_user_id NUMBER DEFAULT 0 NOT NULL, document_id VARCHAR2(200) NOT NULL, expected_visible CHAR(1) NOT NULL, description VARCHAR2(500) NOT NULL, active_yn CHAR(1) DEFAULT 'Y' NOT NULL, fixture_version VARCHAR2(40) DEFAULT 'v1' NOT NULL)");
    ensure("CREATE TABLE cb_dds_validation_evidence (evidence_id NUMBER PRIMARY KEY, run_id VARCHAR2(36) NOT NULL, fixture_id VARCHAR2(100) NOT NULL, enforcement_path VARCHAR2(20) NOT NULL, subject_key VARCHAR2(100) NOT NULL, expected_visible CHAR(1) NOT NULL, actual_visible CHAR(1) NOT NULL, outcome VARCHAR2(20) NOT NULL, fingerprint VARCHAR2(64) NOT NULL, created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL)");
    jdbc.update("MERGE INTO cb_app_user t USING (SELECT 9904 id FROM dual) s ON (t.user_id=s.id) WHEN NOT MATCHED THEN INSERT (user_id,user_name,employee_no,dept_code,can_read_contents,active) VALUES (9904,'dds_fixture_agent','E9904','DDS','N','Y')");
    jdbc.update("MERGE INTO cb_app_role t USING (SELECT 9904 id FROM dual) s ON (t.role_id=s.id) WHEN NOT MATCHED THEN INSERT (role_id,role_name,max_sensitivity_level) VALUES (9904,'DDS_FIXTURE_ALLOW_ROLE','INTERNAL')");
    jdbc.update("MERGE INTO cb_user_role t USING (SELECT 9904 u,9904 r FROM dual) s ON (t.user_id=s.u AND t.role_id=s.r) WHEN NOT MATCHED THEN INSERT (user_id,role_id) VALUES (9904,9904)");
    jdbc.update("MERGE INTO cb_permission t USING (SELECT 9904 id FROM dual) s ON (t.perm_id=s.id) WHEN NOT MATCHED THEN INSERT (perm_id,role_id,target_name,action_name,permission_effect) VALUES (9904,9904,'CB_VECTOR_SEARCH_DOCUMENTS','SELECT','ALLOW')");
    jdbc.update("MERGE INTO cb_permission_rule t USING (SELECT 9904 id FROM dual) s ON (t.rule_id=s.id) WHEN NOT MATCHED THEN INSERT (rule_id,perm_id,rule_column,rule_type,rule_value) VALUES (9904,9904,'TECH_TAG','TAG','DDS_VERIFY_ALLOW')");
    seedDocument(29040, "dds-fixture-service-allow", "DDS_VERIFY_ALLOW", "DDS verification allow fixture");
    seedDocument(29041, "dds-fixture-service-deny", "DDS_VERIFY_DENY", "DDS verification deny fixture");
    fixture("SERVICE_ALLOW", "SERVICE", "service", 9904, "dds-fixture-service-allow", "Y", "Temporary token sees allowed tag");
    fixture("SERVICE_DENY", "SERVICE", "service", 9904, "dds-fixture-service-deny", "N", "Temporary token cannot see denied tag");
    fixture("DIRECT_ALLOW", "DIRECT", "both", 0, "knowledge-002", "Y", "Combined DDS profile sees direct fixture");
    fixture("DIRECT_DENY", "DIRECT", "none", 0, "knowledge-002", "N", "Default deny DDS profile cannot see fixture");
    if (evidence.latestPublished("SERVICE", "service").isEmpty()) {
      evidence.recordPublished(new DdsPublishedProtectionSpec("SERVICE", "service", DdsProtectionFingerprint.service(properties), java.time.Instant.now()), "DDS setup", "Initial token-service baseline");
    }
  }

  private void seedDocument(int chunkId, String documentId, String tag, String title) {
    jdbc.update("INSERT INTO cb_vector_document_chunk (chunk_id,document_id,chunk_no,title,chunk_text,source_uri,embedding) SELECT ?,?,1,?,TO_CLOB(?),'fixture://dds',TO_VECTOR('[0.11,0.22,0.33,0.44]') FROM dual WHERE NOT EXISTS (SELECT 1 FROM cb_vector_document_chunk WHERE chunk_id=?)", chunkId, documentId, title, title, chunkId);
    jdbc.update("MERGE INTO cb_vector_document_tag t USING (SELECT ? chunk_id, ? tech_tag FROM dual) s ON (t.chunk_id=s.chunk_id AND t.tech_tag=s.tech_tag) WHEN NOT MATCHED THEN INSERT (chunk_id,tech_tag) VALUES (s.chunk_id,s.tech_tag)", chunkId, tag);
  }

  private void fixture(String id, String path, String subject, int userId, String documentId, String visible, String description) {
    jdbc.update("MERGE INTO cb_dds_validation_fixture t USING (SELECT ? id FROM dual) s ON (t.fixture_id=s.id) WHEN MATCHED THEN UPDATE SET active_yn='Y' WHEN NOT MATCHED THEN INSERT (fixture_id,enforcement_path,subject_key,application_user_id,document_id,expected_visible,description) VALUES (?,?,?,?,?,?,?)", id, id, path, subject, userId, documentId, visible, description);
  }

  private void ensure(String sql) {
    try { jdbc.execute(sql); } catch (DataAccessException exception) {
      if (!exception.getMessage().contains("ORA-00955")) throw exception;
    }
  }
}
