/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
LOAD 'age';
SET search_path TO ag_catalog;
--
-- Load data
--
SELECT create_graph('cypher_with');
NOTICE:  graph "cypher_with" has been created
 create_graph 
--------------
 
(1 row)

SELECT * FROM cypher('cypher_with', $$
    CREATE (andres {name : 'Andres', age : 36}),
    (caesar {name : 'Caesar', age : 25}),
    (bossman {name : 'Bossman', age : 55}),
    (david {name : 'David', age : 35}),
    (george {name : 'George', age : 37}),
    (andres)-[:BLOCKS]->(caesar),
    (andres)-[:KNOWS]->(bossman),
    (caesar)-[:KNOWS]->(george),
    (bossman)-[:BLOCKS]->(david),
    (bossman)-[:KNOWS]->(george),
    (david)-[:KNOWS]->(andres)
$$) as (a agtype);
 a 
---
(0 rows)

--
-- Test WITH clause
--
SELECT * FROM cypher('cypher_with', $$
    MATCH (n)-[e]->(m) 
    WITH n,e,m
    RETURN n,e,m
$$) AS (N1 agtype, edge agtype, N2 agtype);
                                             n1                                             |                                                            edge                                                            |                                             n2                                             
--------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------
 {"id": 281474976710657, "label": "", "properties": {"age": 36, "name": "Andres"}}::vertex  | {"id": 844424930131969, "label": "BLOCKS", "end_id": 281474976710658, "start_id": 281474976710657, "properties": {}}::edge | {"id": 281474976710658, "label": "", "properties": {"age": 25, "name": "Caesar"}}::vertex
 {"id": 281474976710659, "label": "", "properties": {"age": 55, "name": "Bossman"}}::vertex | {"id": 844424930131970, "label": "BLOCKS", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge | {"id": 281474976710660, "label": "", "properties": {"age": 35, "name": "David"}}::vertex
 {"id": 281474976710657, "label": "", "properties": {"age": 36, "name": "Andres"}}::vertex  | {"id": 1125899906842625, "label": "KNOWS", "end_id": 281474976710659, "start_id": 281474976710657, "properties": {}}::edge | {"id": 281474976710659, "label": "", "properties": {"age": 55, "name": "Bossman"}}::vertex
 {"id": 281474976710658, "label": "", "properties": {"age": 25, "name": "Caesar"}}::vertex  | {"id": 1125899906842626, "label": "KNOWS", "end_id": 281474976710661, "start_id": 281474976710658, "properties": {}}::edge | {"id": 281474976710661, "label": "", "properties": {"age": 37, "name": "George"}}::vertex
 {"id": 281474976710659, "label": "", "properties": {"age": 55, "name": "Bossman"}}::vertex | {"id": 1125899906842627, "label": "KNOWS", "end_id": 281474976710661, "start_id": 281474976710659, "properties": {}}::edge | {"id": 281474976710661, "label": "", "properties": {"age": 37, "name": "George"}}::vertex
 {"id": 281474976710660, "label": "", "properties": {"age": 35, "name": "David"}}::vertex   | {"id": 1125899906842628, "label": "KNOWS", "end_id": 281474976710657, "start_id": 281474976710660, "properties": {}}::edge | {"id": 281474976710657, "label": "", "properties": {"age": 36, "name": "Andres"}}::vertex
(6 rows)

-- WITH/AS
SELECT * FROM cypher('cypher_with', $$
    MATCH (n)-[e]->(m)
    WITH n.name AS n1, e as edge, m.name as n2
    RETURN n1,label(edge),n2
$$) AS (start_node agtype,edge agtype, end_node agtype);
 start_node |   edge   | end_node  
------------+----------+-----------
 "Andres"   | "BLOCKS" | "Caesar"
 "Bossman"  | "BLOCKS" | "David"
 "Andres"   | "KNOWS"  | "Bossman"
 "Caesar"   | "KNOWS"  | "George"
 "Bossman"  | "KNOWS"  | "George"
 "David"    | "KNOWS"  | "Andres"
(6 rows)

SELECT * FROM cypher('cypher_with',$$
    MATCH (person)-[r]->(otherPerson)
    WITH *, type(r) AS connectionType
    RETURN person.name, connectionType, otherPerson.name
$$) AS (start_node agtype, connection agtype, end_node agtype);
 start_node | connection | end_node  
------------+------------+-----------
 "Andres"   | "BLOCKS"   | "Caesar"
 "Bossman"  | "BLOCKS"   | "David"
 "Andres"   | "KNOWS"    | "Bossman"
 "Caesar"   | "KNOWS"    | "George"
 "Bossman"  | "KNOWS"    | "George"
 "David"    | "KNOWS"    | "Andres"
(6 rows)

SELECT * FROM cypher('cypher_with', $$
    WITH true AS b
    RETURN b
$$) AS (b bool);
 b 
---
 t
(1 row)

-- WITH/WHERE
SELECT * FROM cypher('cypher_with', $$
MATCH (george {name: 'George'})<-[]-(otherPerson)
    WITH otherPerson, toUpper(otherPerson.name) AS upperCaseName
    WHERE upperCaseName STARTS WITH 'C'
    RETURN otherPerson.name
$$) as (name agtype);
   name   
----------
 "Caesar"
(1 row)

SELECT * FROM cypher('cypher_with', $$
	MATCH (david {name: 'David'})-[]-(otherPerson)-[]->()
	WITH otherPerson, count(*) AS foaf
	WHERE foaf > 1
	RETURN otherPerson.name
$$) as (name agtype);
   name   
----------
 "Andres"
(1 row)

SELECT * FROM cypher('cypher_with', $$
    MATCH p = (m)-[*1..2]->(b) 
    WITH p, length(p) AS path_length 
    WHERE path_length > 1 
    RETURN p
$$) AS (pattern agtype);
                                                                                                                                                                                                                                                                                             pattern                                                                                                                                                                                                                                                                                              
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label": "KNOWS", "end_id": 281474976710659, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710659, "label": "_ag_label_vertex", "properties": {"age": 55, "name": "Bossman"}}::vertex, {"id": 1125899906842627, "label": "KNOWS", "end_id": 281474976710661, "start_id": 281474976710659, "properties": {}}::edge, {"id": 281474976710661, "label": "_ag_label_vertex", "properties": {"age": 37, "name": "George"}}::vertex]::path
 [{"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label": "KNOWS", "end_id": 281474976710659, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710659, "label": "_ag_label_vertex", "properties": {"age": 55, "name": "Bossman"}}::vertex, {"id": 844424930131970, "label": "BLOCKS", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge, {"id": 281474976710660, "label": "_ag_label_vertex", "properties": {"age": 35, "name": "David"}}::vertex]::path
 [{"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 844424930131969, "label": "BLOCKS", "end_id": 281474976710658, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710658, "label": "_ag_label_vertex", "properties": {"age": 25, "name": "Caesar"}}::vertex, {"id": 1125899906842626, "label": "KNOWS", "end_id": 281474976710661, "start_id": 281474976710658, "properties": {}}::edge, {"id": 281474976710661, "label": "_ag_label_vertex", "properties": {"age": 37, "name": "George"}}::vertex]::path
 [{"id": 281474976710659, "label": "_ag_label_vertex", "properties": {"age": 55, "name": "Bossman"}}::vertex, {"id": 844424930131970, "label": "BLOCKS", "end_id": 281474976710660, "start_id": 281474976710659, "properties": {}}::edge, {"id": 281474976710660, "label": "_ag_label_vertex", "properties": {"age": 35, "name": "David"}}::vertex, {"id": 1125899906842628, "label": "KNOWS", "end_id": 281474976710657, "start_id": 281474976710660, "properties": {}}::edge, {"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex]::path
 [{"id": 281474976710660, "label": "_ag_label_vertex", "properties": {"age": 35, "name": "David"}}::vertex, {"id": 1125899906842628, "label": "KNOWS", "end_id": 281474976710657, "start_id": 281474976710660, "properties": {}}::edge, {"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label": "KNOWS", "end_id": 281474976710659, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710659, "label": "_ag_label_vertex", "properties": {"age": 55, "name": "Bossman"}}::vertex]::path
 [{"id": 281474976710660, "label": "_ag_label_vertex", "properties": {"age": 35, "name": "David"}}::vertex, {"id": 1125899906842628, "label": "KNOWS", "end_id": 281474976710657, "start_id": 281474976710660, "properties": {}}::edge, {"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age": 36, "name": "Andres"}}::vertex, {"id": 844424930131969, "label": "BLOCKS", "end_id": 281474976710658, "start_id": 281474976710657, "properties": {}}::edge, {"id": 281474976710658, "label": "_ag_label_vertex", "properties": {"age": 25, "name": "Caesar"}}::vertex]::path
(6 rows)

-- MATCH/WHERE with WITH/WHERE
SELECT * FROM cypher('cypher_with', $$
    MATCH (m)-[e]->(b)
    WHERE label(e) = 'KNOWS'
    WITH *
    WHERE m.name = 'Andres'
    RETURN m.name,label(e),b.name
$$) AS (N1 agtype, edge agtype, N2 agtype);
    n1    |  edge   |    n2     
----------+---------+-----------
 "Andres" | "KNOWS" | "Bossman"
(1 row)

-- WITH/ORDER BY
SELECT * FROM cypher('cypher_with', $$
    MATCH (n)
    WITH n
    ORDER BY id(n)
    RETURN n
$$) as (name agtype);
                                            name                                            
--------------------------------------------------------------------------------------------
 {"id": 281474976710657, "label": "", "properties": {"age": 36, "name": "Andres"}}::vertex
 {"id": 281474976710658, "label": "", "properties": {"age": 25, "name": "Caesar"}}::vertex
 {"id": 281474976710659, "label": "", "properties": {"age": 55, "name": "Bossman"}}::vertex
 {"id": 281474976710660, "label": "", "properties": {"age": 35, "name": "David"}}::vertex
 {"id": 281474976710661, "label": "", "properties": {"age": 37, "name": "George"}}::vertex
(5 rows)

-- WITH/ORDER BY/DESC
SELECT * FROM cypher('cypher_with', $$
    MATCH (n)
    WITH n
    ORDER BY n.name DESC LIMIT 3
    RETURN collect(n.name)
$$) as (names agtype);
             names             
-------------------------------
 ["George", "David", "Caesar"]
(1 row)

SELECT * FROM cypher('cypher_with', $$
    MATCH (n {name: 'Andres'})-[]-(m)
    WITH m
    ORDER BY m.name DESC LIMIT 1
    MATCH (m)-[]-(o)
    RETURN o.name ORDER BY o.name
$$) as (name agtype);
   name    
-----------
 "Andres"
 "Bossman"
(2 rows)

-- multiple WITH clauses
SELECT * FROM cypher('cypher_with', $$
    MATCH (n)-[e]->(m)
    WITH n, e, m
    WHERE label(e) = 'KNOWS'
    WITH n.name as n1, label(e) as edge, m.name as n2
    WHERE n1 = 'Andres'
    RETURN n1,edge,n2
$$) AS (N1 agtype, edge agtype, N2 agtype);
    n1    |  edge   |    n2     
----------+---------+-----------
 "Andres" | "KNOWS" | "Bossman"
(1 row)

SELECT * FROM cypher('cypher_with', $$
    UNWIND [1, 2, 3, 4, 5, 6] AS x
    WITH x
    WHERE x > 2
    WITH x
    LIMIT 5
    RETURN x
$$) as (name agtype);
 name 
------
 3
 4
 5
 6
(4 rows)

SELECT * FROM cypher('cypher_with', $$
    MATCH (m)-[]->(b)
    WITH m,b
    ORDER BY id(m) DESC LIMIT 5
    WITH m as start_node, b as end_node
    WHERE end_node.name = 'George'
    RETURN id(start_node),start_node.name,id(end_node),end_node.name
$$) AS (id1 agtype, name1 agtype, id2 agtype, name2 agtype);
       id1       |   name1   |       id2       |  name2   
-----------------+-----------+-----------------+----------
 281474976710659 | "Bossman" | 281474976710661 | "George"
 281474976710658 | "Caesar"  | 281474976710661 | "George"
(2 rows)

-- Expression item must be aliased.
SELECT * FROM cypher('cypher_with', $$
    WITH 1 + 1
    RETURN i
$$) AS (i int);
ERROR:  expression item must be aliased
LINE 2:     WITH 1 + 1
                 ^
HINT:  Items can be aliased by using AS.
SELECT * FROM cypher('cypher_with', $$
    MATCH (m)-[]->(b)
    WITH id(m)
    RETURN m
$$) AS (id agtype);
ERROR:  expression item must be aliased
LINE 3:     WITH id(m)
                 ^
HINT:  Items can be aliased by using AS.
-- Reference undefined variable in WITH clause (should error out)
SELECT count(*) FROM cypher('cypher_with', $$
    MATCH (m)-[]->(b)
    WITH m  
    RETURN m,b
$$) AS (a agtype, b agtype);
ERROR:  could not find rte for b
LINE 4:     RETURN m,b
                     ^
SELECT * FROM cypher('cypher_with', $$
    MATCH (m)-[]->(b)
    WITH m AS start_node,b AS end_node
    WHERE start_node.name = 'Andres'
    WITH start_node
    WHERE start_node.name = 'George'
    RETURN id(start_node),end_node.name
$$) AS (id agtype, node agtype);
ERROR:  could not find rte for end_node
LINE 7:     RETURN id(start_node),end_node.name
                                  ^
-- Clean up
SELECT drop_graph('cypher_with', true);
NOTICE:  drop cascades to 4 other objects
DETAIL:  drop cascades to table cypher_with._ag_label_vertex
drop cascades to table cypher_with._ag_label_edge
drop cascades to table cypher_with."BLOCKS"
drop cascades to table cypher_with."KNOWS"
NOTICE:  graph "cypher_with" has been dropped
 drop_graph 
------------
 
(1 row)

-- Issue 329 (should error out)
SELECT create_graph('graph');
NOTICE:  graph "graph" has been created
 create_graph 
--------------
 
(1 row)

SELECT * FROM cypher('graph', $$
    CREATE (a:A)-[:incs]->(:C), (a)-[:incs]->(:C)
    RETURN a
$$) AS (n agtype);
                                n                                
-----------------------------------------------------------------
 {"id": 844424930131969, "label": "A", "properties": {}}::vertex
(1 row)

SELECT * FROM cypher('graph', $$
    MATCH (a:A) 
    WHERE ID(a)=0 
    WITH a 
    OPTIONAL MATCH (a)-[:incs]->(c)-[d:incs]-() 
    WITH a,c,COUNT(d) AS deps 
    WHERE deps<=1 
    RETURN c,d
$$) AS (n agtype, d agtype);
ERROR:  could not find rte for d
LINE 8:     RETURN c,d
                     ^
-- Issue 396 (should error out)
SELECT * FROM cypher('graph',$$
    CREATE (v),(u),(w),
        (v)-[:hasFriend]->(u),
        (u)-[:hasFriend]->(w)
$$) as (a agtype);
 a 
---
(0 rows)

SELECT * FROM cypher('graph',$$
    MATCH p=(v)-[*1..2]->(u) 
    WITH p,length(p) AS path_length 
    RETURN v,path_length
$$) as (a agtype,b agtype);
ERROR:  could not find rte for v
LINE 4:     RETURN v,path_length
                   ^
-- Clean up
SELECT drop_graph('graph', true);
NOTICE:  drop cascades to 6 other objects
DETAIL:  drop cascades to table graph._ag_label_vertex
drop cascades to table graph._ag_label_edge
drop cascades to table graph."A"
drop cascades to table graph.incs
drop cascades to table graph."C"
drop cascades to table graph."hasFriend"
NOTICE:  graph "graph" has been dropped
 drop_graph 
------------
 
(1 row)

--
-- End of test
--