-- Create the Apache AGE extension
CREATE EXTENSION IF NOT EXISTS age;

-- Add ag_catalog to search_path
SET search_path = ag_catalog, "$user", public;

-- Or set it permanently (recommended)
ALTER DATABASE mydb SET search_path = ag_catalog, "$user", public;

-- Create a new graph
SELECT create_graph('knowledge_graph');

-- Create a node with Tags and attributes
SELECT * FROM cypher('knowledge_graph', $$
    CREATE (n:Person {name: 'Zhang San', age: 30, role: 'Engineer'})
    RETURN n
$$) AS (node agtype);

-- Create nodes in batches
SELECT * FROM cypher('knowledge_graph', $$
    CREATE (a:Person {name: 'Li Si', role: 'Manager'}),
           (b:Company {name: 'Tencent Cloud', industry: 'Cloud'}),
           (c:Technology {name: 'PostgreSQL', type: 'Database'})
    RETURN a, b, c
$$) AS (a agtype, b agtype, c agtype);

-- Create relationships between nodes
SELECT * FROM cypher('knowledge_graph', $$
    MATCH (p:Person {name: 'Zhang San'}), (c:Company {name: 'Tencent Cloud'})
    CREATE (p)-[r:WORKS_AT {since: '2020-01-01'}]->(c)
    RETURN r
$$) AS (rel agtype);

-- Create a USES relationship
SELECT * FROM cypher('knowledge_graph', $$
    MATCH (p:Person {name: 'Zhang San'}), (t:Technology {name: 'PostgreSQL'})
    CREATE (p)-[r:USES {level: 'expert'}]->(t)
    RETURN r
$$) AS (rel agtype);

-- Find all Person nodes
SELECT * FROM cypher('knowledge_graph', $$
    MATCH (n:Person)
    RETURN n.name, n.role
$$) AS (name agtype, role agtype);

-- Find relationship paths
SELECT * FROM cypher('knowledge_graph', $$
    MATCH (p:Person)-[r:WORKS_AT]->(c:Company)
    RETURN p.name, c.name, r.since
$$) AS (person agtype, company agtype, since agtype);

-- Multi-hop query
SELECT * FROM cypher('knowledge_graph', $$
    MATCH (p:Person)-[:WORKS_AT]->(c:Company),
          (p)-[:USES]->(t:Technology)
    RETURN p.name, c.name, t.name
$$) AS (person agtype, company agtype, tech agtype);

-- Path query (2-3 hops)
SELECT * FROM cypher('knowledge_graph', $$
    MATCH path = (a:Person)-[*2..3]->(b)
    RETURN path
$$) AS (path agtype);

-- Update node attributes
SELECT * FROM cypher('knowledge_graph', $$
    MATCH (p:Person {name: 'Zhang San'})
    SET p.age = 31
    RETURN p
$$) AS (node agtype);

-- Delete relationships
SELECT * FROM cypher('knowledge_graph', $$
    MATCH (p:Person {name: 'Zhang San'})-[r:USES]->(t:Technology)
    DELETE r
$$) AS (result agtype);

-- Delete nodes (requires deleting associated edges first)
SELECT * FROM cypher('knowledge_graph', $$
    MATCH (t:Technology {name: 'PostgreSQL'})
    DETACH DELETE t
$$) AS (result agtype);

-- Graph query results combined with relational tables
SELECT g.person_name, u.email, u.department
FROM (
    SELECT * FROM cypher('knowledge_graph', $$
        MATCH (p:Person)-[:WORKS_AT]->(c:Company {name: 'Tencent Cloud'})
        RETURN p.name AS person_name
    $$) AS (person_name agtype)
) g
JOIN users u ON u.name = g.person_name::text;

-- Build a product knowledge graph
SELECT create_graph('product_kg');

SELECT * FROM cypher('product_kg', $$
    CREATE (pg:Product {name: 'PostgreSQL', category: 'Database'}),
           (vec:Feature {name: 'Vector search', desc: 'Based on pgvector'}),
           (ai:Feature {name: 'AI Invocation', desc: 'Based on tencentdb_ai'}),
           (pg)-[:HAS_FEATURE]->(vec),
           (pg)-[:HAS_FEATURE]->(ai)
    RETURN pg, vec, ai
$$) AS (pg agtype, vec agtype, ai agtype);

-- After entities are extracted and stored in the graph, a hybrid recall using both graph and vector methods is performed during search:
-- 1. Vector recall related documents
-- 2. Find the relationship chains of relevant entities from the graph
-- 3. Send the graph context + document content together to the large model

-- Record the interaction relationships between Agents
SELECT * FROM cypher('agent_memory', $$
    CREATE (a1:Agent {name: 'Planner', role: 'planning'}),
           (a2:Agent {name: 'Researcher', role: 'research'}),
           (a1)-[:DELEGATES {task: 'Research competing products', timestamp: '2026-01-15'}]->(a2)
    RETURN a1, a2
$$) AS (a1 agtype, a2 agtype);