package com.cloudhandson.vpdbackoffice.service;

import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.ApplicationRunner;
import org.springframework.core.annotation.Order;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

/**
 * Keeps the HMM knowledge store as the source of truth while preserving the
 * CB_VECTOR_* contracts that the existing ORDS and backoffice screens use.
 */
@Component
@Order(0)
public class HmmKnowledgeSchemaInitializer implements ApplicationRunner {

  private static final Logger log = LoggerFactory.getLogger(HmmKnowledgeSchemaInitializer.class);

  private static final List<SchemaObject> TABLES = List.of(
      new SchemaObject("HMM_KNOWLEDGE_DOCUMENTS", """
          CREATE TABLE hmm_knowledge_documents (
            document_id   VARCHAR2(200) PRIMARY KEY,
            file_name     VARCHAR2(500) NOT NULL,
            source_uri    VARCHAR2(1000),
            abstract_text CLOB,
            original_text CLOB,
            file_content  BLOB,
            created_at    TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
            updated_at    TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
          )
          """),
      new SchemaObject("HMM_KNOWLEDGE_CHUNKS", """
          CREATE TABLE hmm_knowledge_chunks (
            chunk_id    NUMBER PRIMARY KEY,
            document_id VARCHAR2(200) NOT NULL
              REFERENCES hmm_knowledge_documents(document_id) ON DELETE CASCADE,
            chunk_no    NUMBER NOT NULL,
            title       VARCHAR2(500),
            chunk_text  CLOB NOT NULL,
            source_uri  VARCHAR2(1000),
            embedding   VECTOR,
            created_at  TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
            CONSTRAINT hmm_knowledge_chunk_uk UNIQUE (document_id, chunk_no)
          )
          """),
      new SchemaObject("HMM_KNOWLEDGE_TAGS", """
          CREATE TABLE hmm_knowledge_tags (
            chunk_id NUMBER NOT NULL
              REFERENCES hmm_knowledge_chunks(chunk_id) ON DELETE CASCADE,
            tech_tag VARCHAR2(100) NOT NULL,
            CONSTRAINT hmm_knowledge_tag_pk PRIMARY KEY (chunk_id, tech_tag),
            CONSTRAINT hmm_knowledge_tag_format_ck
              CHECK (REGEXP_LIKE(tech_tag, '^[A-Z0-9_-]+$'))
          )
          """)
  );

  private final JdbcTemplate jdbcTemplate;

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

  @Override
  public void run(org.springframework.boot.ApplicationArguments args) {
    TABLES.forEach(this::ensureTable);
    ensureSequence();
    ensureIndex();
    ensureLegacyViews();
    log.info("HMM knowledge schema and CB_VECTOR compatibility views are ready");
  }

  private void ensureTable(SchemaObject table) {
    if (!objectExists(table.name(), "TABLE")) {
      jdbcTemplate.execute(table.ddl());
    }
  }

  private void ensureSequence() {
    if (!objectExists("HMM_KNOWLEDGE_CHUNK_SEQ", "SEQUENCE")) {
      jdbcTemplate.execute("CREATE SEQUENCE hmm_knowledge_chunk_seq START WITH 30000 INCREMENT BY 1 NOCACHE");
    }
  }

  private void ensureIndex() {
    if (!objectExists("HMM_KNOWLEDGE_TAG_IX", "INDEX")) {
      jdbcTemplate.execute("CREATE INDEX hmm_knowledge_tag_ix ON hmm_knowledge_tags (tech_tag, chunk_id)");
    }
  }

  private void ensureLegacyViews() {
    replaceViewUnlessPhysicalTable("CB_VECTOR_DOCUMENT_CHUNK", """
        CREATE OR REPLACE VIEW cb_vector_document_chunk AS
        SELECT chunk_id, document_id, chunk_no, title, chunk_text, source_uri, embedding, created_at
        FROM hmm_knowledge_chunks
        """);
    replaceViewUnlessPhysicalTable("CB_VECTOR_DOCUMENT_TAG", """
        CREATE OR REPLACE VIEW cb_vector_document_tag AS
        SELECT chunk_id, tech_tag
        FROM hmm_knowledge_tags
        """);
    replaceViewUnlessPhysicalTable("CB_VECTOR_SEARCH_DOCUMENTS", """
        CREATE OR REPLACE VIEW cb_vector_search_documents AS
        SELECT c.chunk_id,
               c.document_id,
               c.chunk_no,
               c.title,
               c.chunk_text,
               c.source_uri,
               c.embedding,
               (SELECT LISTAGG(t.tech_tag, ',') WITHIN GROUP (ORDER BY t.tech_tag)
                  FROM hmm_knowledge_tags t
                 WHERE t.chunk_id = c.chunk_id) AS tech_tag
        FROM hmm_knowledge_chunks c
        WHERE EXISTS (
          SELECT 1 FROM hmm_knowledge_tags t WHERE t.chunk_id = c.chunk_id
        )
        """);
  }

  private void replaceViewUnlessPhysicalTable(String name, String ddl) {
    if (objectExists(name, "TABLE")) {
      log.info("{} is a physical legacy table; leaving it unchanged", name);
      return;
    }
    jdbcTemplate.execute(ddl);
  }

  private boolean objectExists(String name, String type) {
    Integer count = jdbcTemplate.queryForObject("""
        SELECT COUNT(*)
        FROM user_objects
        WHERE object_name = ?
          AND object_type = ?
        """, Integer.class, name, type);
    return count != null && count > 0;
  }

  private record SchemaObject(String name, String ddl) {
  }
}
