/*
 * JBoss, Home of Professional Open Source.
 * See the COPYRIGHT.txt file distributed with this work for information
 * regarding copyright ownership.  Some portions may be licensed
 * to Red Hat, Inc. under one or more contributor license agreements.
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301 USA.
 */
package org.teiid.translator.mongodb;

import static org.junit.Assert.assertEquals;

import java.util.Arrays;

import org.junit.Before;
import org.junit.Test;
import org.teiid.cdk.api.TranslationUtility;
import org.teiid.core.util.ObjectConverterUtil;
import org.teiid.core.util.UnitTestUtil;
import org.teiid.language.Select;
import org.teiid.metadata.MetadataFactory;
import org.teiid.query.function.FunctionTree;
import org.teiid.query.function.UDFSource;
import org.teiid.query.metadata.MetadataValidator;
import org.teiid.query.metadata.TransformationMetadata;
import org.teiid.query.parser.TestDDLParser;
import org.teiid.query.unittest.RealMetadataFactory;
import org.teiid.query.validator.ValidatorReport;

import com.mongodb.BasicDBObject;

@SuppressWarnings("nls")
public class TestMongoDBSelectVisitor {
    private MongoDBExecutionFactory translator;
    private TranslationUtility utility;

    @Before
    public void setUp() throws Exception {
    	this.translator = new MongoDBExecutionFactory();
    	this.translator.start();

    	MetadataFactory mf = TestDDLParser.helpParse(ObjectConverterUtil.convertFileToString(UnitTestUtil.getTestDataFile("northwind.ddl")), "northwind");

    	TransformationMetadata metadata = RealMetadataFactory.createTransformationMetadata(mf.asMetadataStore(), "sakila", new FunctionTree("mongo", new UDFSource(translator.getPushDownFunctions())));
    	ValidatorReport report = new MetadataValidator().validate(metadata.getVdbMetaData(), metadata.getMetadataStore());
    	if (report.hasItems()) {
    		throw new RuntimeException(report.getFailureMessage());
    	}
    	this.utility = new TranslationUtility(metadata);
    }

    private void helpExecute(String query, String collection, String project, String match) throws Exception {
    	helpExecute(query, collection, project, match, null, null);
    }
    private MongoDBSelectVisitor helpExecute(String query, String collection, String project, String match, String groupby, String having) throws Exception {
    	Select cmd = (Select)this.utility.parseCommand(query);
    	MongoDBSelectVisitor visitor = new MongoDBSelectVisitor(this.translator, this.utility.createRuntimeMetadata());
    	visitor.visitNode(cmd);
    	if (!visitor.exceptions.isEmpty()) {
    		throw visitor.exceptions.get(0);
    	}

    	assertEquals(collection, visitor.mongoDoc.getTargetTable().getName());
    	if (project != null) {
    		assertEquals("project wrong", project, visitor.project.toString());
    	}

    	if (match != null) {
    		assertEquals("match wrong", match, visitor.match.toString());
    	}

    	if (groupby != null) {
    		assertEquals("groupby wrong", groupby, visitor.group.toString());
    	}

    	if (having != null) {
    		assertEquals("having wrong", having, visitor.having.toString());
    	}
    	return visitor;
    }

    @Test
    public void testSelectStar() throws Exception {
		helpExecute(
				"select * from customers",
				"Customers",
				"{ \"_m0\" : \"$_id\" , \"_m1\" : \"$CompanyName\" , \"_m2\" : \"$ContactName\" , \"_m3\" : \"$ContactTitle\" , \"_m4\" : \"$Address\" , \"_m5\" : \"$City\" , \"_m6\" : \"$Region\" , \"_m7\" : \"$PostalCode\" , \"_m8\" : \"$Country\" , \"_m9\" : \"$Phone\" , \"_m10\" : \"$Fax\"}",
				null);
    }

    @Test
    public void testSelectColum() throws Exception {
		helpExecute("select CompanyName, ContactName from customers",
				"Customers",
				"{ \"_m0\" : \"$CompanyName\" , \"_m1\" : \"$ContactName\"}",
				null);
    }

    @Test
    public void testWhereEQ() throws Exception {
		helpExecute(
				"SELECT CompanyName, ContactName FROM customers WHERE CompanyName = 'A'",
				"Customers",
				"{ \"_m0\" : \"$CompanyName\" , \"_m1\" : \"$ContactName\"}",
				"{ \"CompanyName\" : \"A\"}");
    }

    @Test
    public void testAND() throws Exception {
		helpExecute(
				"SELECT CompanyName, ContactName FROM customers WHERE CompanyName = 'A' AND ContactName = 'B'",
				"Customers",
				"{ \"_m0\" : \"$CompanyName\" , \"_m1\" : \"$ContactName\"}",
				"{ \"$and\" : [ { \"CompanyName\" : \"A\"} , { \"ContactName\" : \"B\"}]}");
    }

    @Test
    public void testOR() throws Exception {
		helpExecute(
				"SELECT CompanyName, ContactName FROM customers WHERE CompanyName = 'A' OR ContactName = 'B'",
				"Customers",
				"{ \"_m0\" : \"$CompanyName\" , \"_m1\" : \"$ContactName\"}",
				"{ \"$or\" : [ { \"CompanyName\" : \"A\"} , { \"ContactName\" : \"B\"}]}");
    }


    @Test
    public void testComplexAndOr() throws Exception {
		helpExecute(
				"SELECT CompanyName, ContactName FROM customers WHERE (CompanyName = 'A' AND ContactName = 'B') OR (CompanyName = 'B' AND ContactName = 'A')",
				"Customers",
				"{ \"_m0\" : \"$CompanyName\" , \"_m1\" : \"$ContactName\"}",
				"{ \"$or\" : [ { \"$and\" : [ { \"CompanyName\" : \"A\"} , { \"ContactName\" : \"B\"}]} , { \"$and\" : [ { \"CompanyName\" : \"B\"} , { \"ContactName\" : \"A\"}]}]}");
    }

    @Test
    public void testComplexOrAnd() throws Exception {
		helpExecute(
				"SELECT CompanyName, ContactName FROM customers WHERE (CompanyName = 'A' OR ContactName = 'B') AND (CompanyName = 'B' OR ContactName = 'A')",
				"Customers",
				"{ \"_m0\" : \"$CompanyName\" , \"_m1\" : \"$ContactName\"}",
				"{ \"$and\" : [ { \"$or\" : [ { \"CompanyName\" : \"A\"} , { \"ContactName\" : \"B\"}]} , { \"$or\" : [ { \"CompanyName\" : \"B\"} , { \"ContactName\" : \"A\"}]}]}");
    }

    @Test
    public void testOrRewriteToIn() throws Exception {
		helpExecute(
				"SELECT CompanyName, ContactName FROM customers WHERE CompanyName = 'A' OR CompanyName = 'B'",
				"Customers",
				"{ \"_m0\" : \"$CompanyName\" , \"_m1\" : \"$ContactName\"}",
				"{ \"CompanyName\" : { \"$in\" : [ \"B\" , \"A\"]}}");
    }

    @Test
    public void testIn() throws Exception {
		helpExecute(
				"SELECT CompanyName, ContactName FROM customers WHERE CompanyName IN('A', 'B')",
				"Customers",
				"{ \"_m0\" : \"$CompanyName\" , \"_m1\" : \"$ContactName\"}",
				"{ \"CompanyName\" : { \"$in\" : [ \"A\" , \"B\"]}}");
    }

    @Test
    public void testNotIn() throws Exception {
		helpExecute(
				"SELECT CompanyName, ContactName FROM customers WHERE CompanyName NOT IN ('A', 'B')",
				"Customers",
				"{ \"_m0\" : \"$CompanyName\" , \"_m1\" : \"$ContactName\"}",
				"{ \"CompanyName\" : { \"$nin\" : [ \"A\" , \"B\"]}}");
    }

    @Test
    public void testIsNull() throws Exception {
		helpExecute(
				"SELECT CompanyName, ContactName FROM Customers WHERE ContactName IS NULL",
				"Customers",
				"{ \"_m0\" : \"$CompanyName\" , \"_m1\" : \"$ContactName\"}",
				"{ \"ContactName\" :  null }");
    }

    @Test
    public void testIsNotNull() throws Exception {
		helpExecute(
				"SELECT CompanyName, ContactName FROM Customers WHERE ContactName IS NOT NULL",
				"Customers",
				"{ \"_m0\" : \"$CompanyName\" , \"_m1\" : \"$ContactName\"}",
				"{ \"ContactName\" : { \"$ne\" :  null }}");
    }

    @Test
    public void testGtLt() throws Exception {
		helpExecute(
				"SELECT age,status FROM users WHERE age > 25 AND age <= 50",
				"users", "{ \"_m0\" : \"$age\" , \"_m1\" : \"$status\"}",
				"{ \"$and\" : [ { \"age\" : { \"$gt\" : 25}} , { \"age\" : { \"$lte\" : 50}}]}");
    }

    @Test
    public void testLike() throws Exception {
		helpExecute(
				"SELECT user_id, age, status FROM users WHERE user_id like '%bc%'",
				"users",
				"{ \"_m0\" : \"$user_id\" , \"_m1\" : \"$age\" , \"_m2\" : \"$status\"}",
				"{ \"user_id\" : { \"$regex\" : \"bc\"}}");
    }

    @Test
    public void testLike2() throws Exception {
		helpExecute(
				"SELECT user_id, age, status FROM users WHERE user_id like 'bc%'",
				"users",
				"{ \"_m0\" : \"$user_id\" , \"_m1\" : \"$age\" , \"_m2\" : \"$status\"}",
				"{ \"user_id\" : { \"$regex\" : \"^bc\"}}");
    }

    @Test
    public void testLike3() throws Exception {
		helpExecute(
				"SELECT user_id, age, status FROM users WHERE user_id like 'b%c'",
				"users",
				"{ \"_m0\" : \"$user_id\" , \"_m1\" : \"$age\" , \"_m2\" : \"$status\"}",
				"{ \"user_id\" : { \"$regex\" : \"^b.*c$\"}}");
    }

    @Test
    public void testLike4() throws Exception {
		helpExecute(
				"SELECT user_id, age, status FROM users WHERE user_id NOT LIKE 'b%c'",
				"users",
				"{ \"_m0\" : \"$user_id\" , \"_m1\" : \"$age\" , \"_m2\" : \"$status\"}",
				"{ \"user_id\" : { \"$not\" : { \"$regex\" : \"^b.*c$\"}}}");
    }

    @Test
    public void testLimit() throws Exception {
    	String query = "SELECT user_id, age, status FROM users LIMIT 2,5";
    	Select cmd = (Select)this.utility.parseCommand(query);
    	MongoDBSelectVisitor visitor = new MongoDBSelectVisitor(this.translator, this.utility.createRuntimeMetadata());
    	visitor.visitNode(cmd);
    	assertEquals(new Integer(5), visitor.limit);
    	assertEquals(new Integer(2), visitor.skip);
    }

    @Test
    public void testOrderBy() throws Exception {
    	String query = "SELECT user_id, age, status FROM users ORDER BY age, status DESC";
    	Select cmd = (Select)this.utility.parseCommand(query);
    	MongoDBSelectVisitor visitor = new MongoDBSelectVisitor(this.translator, this.utility.createRuntimeMetadata());
    	visitor.visitNode(cmd);
    	BasicDBObject expected = new BasicDBObject("_m1", 1);
    	expected.put("_m2", -1);
    	assertEquals(expected, visitor.sort);
    }

    @Test
    public void testCountStar() throws Exception {
    	String query = "SELECT COUNT(*) AS allusers FROM users";
		helpExecute(query, "users", "{ \"allusers\" : 1}", null,
				"{ \"_id\" :  null  , \"allusers\" : { \"$sum\" : 1}}", null);
    }

    @Test
    public void testCountStarWithoutAlias() throws Exception {
    	String query = "SELECT COUNT(*) FROM users";
		helpExecute(query, "users", "{ \"_m0\" : 1}", null,
				"{ \"_id\" :  null  , \"_m0\" : { \"$sum\" : 1}}", null);
    }

    @Test
    public void testCountStarWithDistinct() throws Exception {
    	String query = "SELECT DISTINCT COUNT(*) FROM users";
		helpExecute(query, "users", "{ \"_m0\" : 1}", null,
				"{ \"_id\" :  null  , \"_m0\" : { \"$sum\" : 1}}", null);
    }
    @Test
    public void testDistinct() throws Exception {
    	String query = "SELECT DISTINCT user_id, age FROM users";
		helpExecute(query, "users",
				"{ \"_m0\" : \"$_id._m0\" , \"_m1\" : \"$_id._m1\"}", //project
				null, 												  // match
				"{ \"_id\" : { \"_m0\" : \"$user_id\" , \"_m1\" : \"$age\"}}", //group by
				null); 												  // having
    }

    @Test
    public void testDistinctEquivalent() throws Exception {
    	String query = "SELECT user_id, age age FROM users group by user_id, age";

    	MongoDBSelectVisitor visitor = helpExecute(
				query,
				"users",
				"{ \"_m0\" : \"$_id._c0\" , \"age\" : \"$_id._c1\"}",
				null,
				"{ \"_id\" : { \"_c0\" : \"$user_id\" , \"_c1\" : \"$age\"}}",
				null);
    	assertEquals(Arrays.asList("_m0", "age"), visitor.selectColumnReferences);
    }
    
    @Test
    public void testAggregateWithGroupBy() throws Exception {
        String query = "SELECT user_id, sum(age) FROM users group by user_id";

        MongoDBSelectVisitor visitor = helpExecute(
                query,
                "users",
                "{ \"_m0\" : \"$_id._c0\" , \"_m1\" : 1}",
                null,
                "{ \"_id\" : { \"_c0\" : \"$user_id\"} , \"_m1\" : { \"$sum\" : \"$age\"}}",
                null);
        assertEquals(Arrays.asList("_m0", "_m1"), visitor.selectColumnReferences);
    }    

    @Test
    public void testSum() throws Exception {
    	String query = "SELECT SUM(age) as total FROM users";
		helpExecute(query, "users", "{ \"total\" : 1}", null,
				"{ \"total\" : { \"$sum\" : \"$age\"} , \"_id\" :  null }",
				null);
    }

    @Test
    public void testPlusOperatorWithAlias() throws Exception {
    	String query = "SELECT (age+age) as total FROM users";
    	helpExecute(query, "users", "{ \"total\" : { \"$add\" : [ \"$age\" , \"$age\"]}}", null, null, null);
    }

    @Test
    public void testPlusOperatorWithOutAlias() throws Exception {
    	String query = "SELECT (age+age) FROM users";
    	helpExecute(query, "users", "{ \"_m0\" : { \"$add\" : [ \"$age\" , \"$age\"]}}", null, null, null);
    }

    @Test
    public void testPlusOperatorInWhere() throws Exception {
    	String query = "SELECT age FROM users WHERE age > 5.0";
    	helpExecute(query, "users", "{ \"_m0\" : \"$age\"}", "{ \"age\" : { \"$gt\" : 5}}");
    }

    @Test
    public void testFunction() throws Exception {
    	String query = "SELECT concat(user_id, user_id) FROM users";
		helpExecute(query, "users",
				"{ \"_m0\" : { \"$concat\" : [ \"$user_id\" , \"$user_id\"]}}",
				null);
    }
    
    @Test
    public void testSelectBooleanExpression() throws Exception {
    	String query = "SELECT (user_id = 'USER') as X1 FROM users";
		helpExecute(query, "users",
				"{ \"X1\" : { \"$cond\" : [ { \"$eq\" : [ \"$user_id\" , \"USER\"]} , true , false]}}",
				null);
    }    
    
    @Test
    public void testSelectBooleanExpression2() throws Exception {
    	String query = "SELECT (user_id > 'USER') as X1 FROM users";
		helpExecute(query, "users",
				"{ \"X1\" : { \"$cond\" : [ { \"$gt\" : [ \"$user_id\" , \"USER\"]} , true , false]}}",
				null);
    } 
    
    @Test
    public void testSelectBooleanExpression3() throws Exception {
    	String query = "SELECT (user_id = 'USER' OR user_id = 'user') as X1 FROM users";
		helpExecute(query, "users",
				"{ \"X1\" : { \"$cond\" : [ { \"user_id\" : { \"$in\" : [ \"user\" , \"USER\"]}} , true , false]}}",
				null);
    }    
    
    @Test
    public void testSelectBooleanExpression4() throws Exception {
    	String query = "SELECT (user_id = 'USER' AND age > 30) as X1 FROM users";
		helpExecute(query, "users",
				"{ \"X1\" : { \"$cond\" : [ { \"$and\" : [ { \"$eq\" : [ \"$user_id\" , \"USER\"]} , { \"$gt\" : [ \"$age\" , 30]}]} , true , false]}}",
				null);
    } 

    @Test
    public void testNestedFunction() throws Exception {
    	String query = "SELECT concat(concat(user_id, user_id), user_id) FROM users";
		helpExecute(query, "users",
				"{ \"_m0\" : { \"$concat\" : [ { \"$concat\" : [ \"$user_id\" , \"$user_id\"]} , \"$user_id\"]}}",
				null);
    }    
    
    @Test
    public void testWhereReference() throws Exception {
    	String query = "SELECT age FROM users WHERE user_id = 'bob'";
		helpExecute(query, "users",
				"{ \"_m0\" : \"$age\"}",
				"{ \"user_id\" : \"bob\"}");
    }

    @Test
    public void testSelectStarCompositeKey() throws Exception {
    	String query = "SELECT * from G1 where e1 = 50";
		helpExecute(query, "G1",
				"{ \"_m0\" : \"$_id.e1\" , \"_m1\" : \"$_id.e2\" , \"_m2\" : \"$e3\"}",
				"{ \"_id.e1\" : 50}");
    }

    @Test
    public void testCompositeFKKeyWhere() throws Exception {
    	String query = "SELECT * from G2 where e2 = 50";
		helpExecute(query, "G2",
				"{ \"_m0\" : \"$e1\" , \"_m1\" : \"$e2\" , \"_m2\" : \"$e3\"}",
				"{ \"e2\" : 50}");
    }
    
    @Test
    public void testGeoWithinPloygonFunction() throws Exception {
    	String query = "SELECT mongo.geoWithin(user_id, 'LineString', ((cast(1.0 as double), cast(2.0 as double)), (cast(1.0 as double), cast(2.0 as double)))) FROM users";
		helpExecute(query, "users",
				"{ \"_m0\" : { \"user_id\" : { \"$geoWithin\" : { \"$geometry\" : { \"type\" : \"LineString\" , \"coordinates\" : [ [ [ 1.0 , 2.0] , [ 1.0 , 2.0]]]}}}}}",
				null);
    }
    
    @Test
    public void testGeoNearFunction() throws Exception {
    	String query = "SELECT mongo.geonear(user_id, (cast(1.0 as double), cast(2.0 as double)), 22, 10) FROM users";
		helpExecute(query, "users",
				"{ \"_m0\" : { \"user_id\" : { \"$near\" : { \"$geometry\" : { \"type\" : \"Point\" , \"coordinates\" : [ [ 1.0 , 2.0]]} , \"$maxDistance\" : 22 , \"$minDistance\" : 10}}}}",
				null);
    }   
    
    @Test
    public void testGeoWithinPloygonFunctionInWhere() throws Exception {
    	String query = "SELECT user_id FROM users where mongo.geoWithin(user_id, 'LineString', ((cast(1.0 as double), cast(2.0 as double)), (cast(1.0 as double), cast(2.0 as double))))";
		helpExecute(query, "users",
				"{ \"_m1\" : \"$user_id\"}",
				"{ \"user_id\" : { \"$geoWithin\" : { \"$geometry\" : { \"type\" : \"LineString\" , \"coordinates\" : [ [ [ 1.0 , 2.0] , [ 1.0 , 2.0]]]}}}}"				
				);
    }    
    
    @Test
    public void testAliasPloygonFunctionInWhere() throws Exception {
    	String query = "SELECT user_id FROM users where mongo.geoPolygonWithin(user_id, 1.0, 2.0, 3.0, 4.0)";
		helpExecute(query, "users",
				"{ \"_m1\" : \"$user_id\"}",
				"{ \"user_id\" : { \"$geoWithin\" : { \"$geometry\" : { \"type\" : \"Polygon\" , \"coordinates\" : [ [ [ 3.0 , 1.0] , [ 2.0 , 1.0] , [ 2.0 , 4.0] , [ 3.0 , 4.0] , [ 3.0 , 1.0]]]}}}}"				
				);
    }     
}
