-- Tabela de entidades
CREATE TABLE entities (
                                id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                                name VARCHAR2(255) UNIQUE NOT NULL
);

-- Tabela de relações
CREATE TABLE relations (
                                 id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                                 from_entity_id NUMBER REFERENCES entities(id),
                                 to_entity_id NUMBER REFERENCES entities(id),
                                 relation VARCHAR2(255),
                                 source_text VARCHAR2(1000)
);

BEGIN
    ordsadmin.graph_view_admin.create_graph_view(
            graph_view_name => 'my_graph',
            vertex_table_names => 'ENTITIES',
            edge_table_names => 'RELATIONS',
            vertex_id_column => 'ID',
            edge_source_column => 'FROM_ENTITY_ID',
            edge_destination_column => 'TO_ENTITY_ID'
    );
END;
/


CREATE PROPERTY GRAPH my_graph
  VERTEX TABLES (ENTITIES
    KEY (ID)
    LABEL ENTITIES
    PROPERTIES (NAME))
  EDGE TABLES (RELATIONS
    KEY (ID)
    SOURCE KEY (SOURCE_ID) REFERENCES ENTITIES(ID)
    DESTINATION KEY (TARGET_ID) REFERENCES ENTITIES(ID)
    LABEL RELATIONS
    PROPERTIES (RELATION_TYPE, SOURCE_TEXT))
     options (PG_PGQL)

-- Drope o índice antigo, se necessário
DROP INDEX ent_name_text_idx;
DROP INDEX rel_type_text_idx;

-- Crie com o tipo correto
CREATE INDEX ent_name_text_idx ON ENTITIES(NAME) INDEXTYPE IS CTXSYS.CONTEXT;
CREATE INDEX rel_type_text_idx ON RELATIONS(RELATION_TYPE) INDEXTYPE IS CTXSYS.CONTEXT;

EXEC CTX_DDL.SYNC_INDEX('ENT_NAME_TEXT_IDX');
EXEC CTX_DDL.SYNC_INDEX('REL_TYPE_TEXT_IDX');

SELECT from_entity,
       relation_type,
       to_entity
FROM GRAPH_TABLE(
    my_graph
                MATCH (e1 is ENTITIES)-[r is RELATIONS]->(e2 is ENTITIES)
                 WHERE CONTAINS(LOWER(e1.name), 'gateway') > 0
                    OR CONTAINS(LOWER(e2.name), 'gateway') > 0
                    OR CONTAINS(LOWER(r.RELATION_TYPE), 'gateway') > 0
                COLUMNS (
                    e1.name AS from_entity, r.RELATION_TYPE AS relation_type, e2.name AS to_entity
                )
        )
    FETCH FIRST 20 ROWS ONLY

---------------
-- # 2026-01-29 - VECTOR 23ai

CREATE TABLE rag_docs (
                          id NUMBER GENERATED BY DEFAULT AS IDENTITY,
                          content CLOB,
                          source VARCHAR2(1000),
                          chunk_hash VARCHAR2(64),
                          status VARCHAR2(20),
                          embed VECTOR(1024)
);

CREATE VECTOR INDEX rag_docs_idx
ON rag_docs(embed)
ORGANIZATION HNSW
DISTANCE COSINE;

-------------------
-- #2026-02-07 - app_users

DROP TABLE app_users;

CREATE TABLE app_users (
                           id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

                           username VARCHAR2(100) UNIQUE,
                           name VARCHAR2(200),
                           email VARCHAR2(200) UNIQUE,

                           user_role VARCHAR2(50),

                           password_hash VARCHAR2(300),

                           active NUMBER(1) DEFAULT 1,

                           reset_token VARCHAR2(300),
                           reset_expire TIMESTAMP,

                           must_change_password NUMBER(1) DEFAULT 0,

                           created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON app_users(email);
CREATE INDEX idx_users_token ON app_users(reset_token);