/*
 * ModeShape (http://www.modeshape.org)
 *
 * Licensed 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.
 */
package org.modeshape.sequencer.ddl.dialect.teiid;

import static org.hamcrest.core.Is.is;
import static org.junit.Assert.assertThat;
import java.util.ArrayList;
import java.util.List;
import org.junit.Before;
import org.junit.Test;
import org.modeshape.sequencer.ddl.DdlParserScorer;
import org.modeshape.sequencer.ddl.DdlParserTestHelper;
import org.modeshape.sequencer.ddl.node.AstNode;

/**
 * A test class for the {@link TeiidDdlParser}.
 */
public class TeiidDdlParserTest extends DdlParserTestHelper implements TeiidDdlConstants {

    public static final String DDL_FILE_PATH = "ddl/dialect/teiid/";

    @Before
    public void beforeEach() {
        this.parser = new TeiidDdlParser();
        setRootNode(this.parser.nodeFactory().node("DdlRootNode"));
        this.scorer = new DdlParserScorer();
    }

    /**
     * See Teiid TestDDLParser#testDuplicateFunctions()
     */
    @Test
    public void shouldParseDuplicateFunctions() {
        final String functionName = "SourceFunc";
        final String content = "CREATE FUNCTION " + functionName + "() RETURNS string; CREATE FUNCTION " + functionName
                               + "(param string) RETURNS string";
        assertScoreAndParse(content, null, 2);

        final List<AstNode> kids = getRootNode().getChildren();
        assertMixinType(kids.get(0), TeiidDdlLexicon.CreateProcedure.FUNCTION_STATEMENT);
        assertMixinType(kids.get(1), TeiidDdlLexicon.CreateProcedure.FUNCTION_STATEMENT);
    }

    /**
     * See Teiid TestDDLParser#testDuplicateFunctions1()
     */
    @Test
    public void shouldParseDuplicateFunctions1() {
        final String content = "CREATE FUNCTION SourceFunc() RETURNS string OPTIONS (UUID 'a'); CREATE FUNCTION SourceFunc1() RETURNS string OPTIONS (UUID 'a')";
        assertScoreAndParse(content, null, 2);

        {
            final List<AstNode> kids = getRootNode().childrenWithName("SourceFunc");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateProcedure.FUNCTION_STATEMENT);
        }

        {
            final List<AstNode> kids = getRootNode().childrenWithName("SourceFunc1");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateProcedure.FUNCTION_STATEMENT);
        }
    }

    /**
     * See Teiid TestDDLParser#testMultipleCommands()
     */
    @Test
    public void shouldParseMultipleCommands() {
        final String content = "CREATE VIEW V1 AS SELECT * FROM PM1.G1 "
                               + "CREATE PROCEDURE FOO(P1 integer) RETURNS (e1 integer, e2 varchar) AS SELECT * FROM PM1.G1;";
        assertScoreAndParse(content, null, 2);

        {
            final List<AstNode> kids = getRootNode().childrenWithName("V1");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateTable.VIEW_STATEMENT);
        }

        {
            final List<AstNode> kids = getRootNode().childrenWithName("FOO");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateProcedure.PROCEDURE_STATEMENT);
        }
    }

    /**
     * See Teiid TestDDLParser#testMultipleCommands2()
     */
    @Test
    public void shouldParseMultipleCommands2() {
        final String content = "CREATE VIRTUAL PROCEDURE getTweets(query varchar) RETURNS (created_on varchar(25), "
                               + "from_user varchar(25), to_user varchar(25), "
                               + "profile_image_url varchar(25), source varchar(25), text varchar(140)) AS "
                               + "select tweet.* from "
                               + "(call twitter.invokeHTTP(action => 'GET', endpoint =>querystring('',query as \"q\"))) w, "
                               + "XMLTABLE('results' passing JSONTOXML('myxml', w.result) columns "
                               + "created_on string PATH 'created_at', " + "from_user string PATH 'from_user', "
                               + "to_user string PATH 'to_user', " + "profile_image_url string PATH 'profile_image_url', "
                               + "source string PATH 'source', " + "text string PATH 'text') tweet;"
                               + "CREATE VIEW Tweet AS select * FROM twitterview.getTweets;";
        assertScoreAndParse(content, null, 2);

        {
            final List<AstNode> kids = getRootNode().childrenWithName("getTweets");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateProcedure.PROCEDURE_STATEMENT);
        }

        {
            final List<AstNode> kids = getRootNode().childrenWithName("Tweet");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateTable.VIEW_STATEMENT);
        }
    }

    /**
     * See Teiid TestDDLParser#testInsteadOfTrigger()
     */
    @Test
    public void shouldParseInsteadOfTrigger() {
        final String content = "CREATE VIEW G1( e1 integer, e2 varchar) AS select * from foo;"
                               + "CREATE TRIGGER ON G1 INSTEAD OF INSERT AS " + "FOR EACH ROW " + "BEGIN ATOMIC "
                               + "insert into g1 (e1, e2) values (1, 'trig');" + "END;"
                               + "CREATE View G2( e1 integer, e2 varchar) AS select * from foo;";
        assertScoreAndParse(content, null, 3);

        {
            final List<AstNode> kids = getRootNode().childrenWithName("G1");
            assertThat(kids.size(), is(2)); // view, trigger
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateTable.VIEW_STATEMENT);
            assertMixinType(kids.get(1), TeiidDdlLexicon.CreateTrigger.STATEMENT);
        }

        {
            final List<AstNode> kids = getRootNode().childrenWithName("G2");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateTable.VIEW_STATEMENT);
        }
    }

    /**
     * See Teiid TestDDLParser#testFK()
     */
    @Test
    public void shouldParseForeignKey() {
        final String content = "CREATE FOREIGN TABLE G1(g1e1 integer, g1e2 varchar, PRIMARY KEY(g1e1, g1e2));"
                               + "CREATE FOREIGN TABLE G2( g2e1 integer, g2e2 varchar, FOREIGN KEY (g2e1, g2e2) REFERENCES G1 (g1e1, g1e2))";
        assertScoreAndParse(content, null, 2);

        {
            final List<AstNode> kids = getRootNode().childrenWithName("G1");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateTable.TABLE_STATEMENT);
        }

        {
            final List<AstNode> kids = getRootNode().childrenWithName("G2");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateTable.TABLE_STATEMENT);
        }
    }

    /**
     * See Teiid TestDDLParser#testOptionalFK()
     */
    @Test
    public void shouldParseOptionalForeignKey() {
        final String content = "CREATE FOREIGN TABLE G1(g1e1 integer, g1e2 varchar, PRIMARY KEY(g1e1, g1e2));"
                               + "CREATE FOREIGN TABLE G2( g2e1 integer, g2e2 varchar, PRIMARY KEY(g2e1, g2e2), FOREIGN KEY (g2e1, g2e2) REFERENCES G1)";
        assertScoreAndParse(content, null, 2);

        {
            final List<AstNode> kids = getRootNode().childrenWithName("G1");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateTable.TABLE_STATEMENT);
        }

        {
            final List<AstNode> kids = getRootNode().childrenWithName("G2");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateTable.TABLE_STATEMENT);
        }
    }

    @Test
    public void shouldParseAccountsDdl() {
        final String content = "CREATE FOREIGN TABLE \"accounts.ACCOUNT\" ("
                               + "  ACCOUNT_ID long NOT NULL DEFAULT '0' OPTIONS (ANNOTATION '', NAMEINSOURCE '`ACCOUNT_ID`', NATIVE_TYPE 'INT'),"
                               + "  SSN string(10) OPTIONS (ANNOTATION '', NAMEINSOURCE '`SSN`', NATIVE_TYPE 'CHAR'),"
                               + "  STATUS string(10) OPTIONS (ANNOTATION '', NAMEINSOURCE '`STATUS`', NATIVE_TYPE 'CHAR'),"
                               + "  TYPE string(10) OPTIONS (ANNOTATION '', NAMEINSOURCE '`TYPE`', NATIVE_TYPE 'CHAR'),"
                               + "  DATEOPENED timestamp NOT NULL DEFAULT 'CURRENT_TIMESTAMP' OPTIONS (ANNOTATION '', NAMEINSOURCE '`DATEOPENED`', NATIVE_TYPE 'TIMESTAMP'),"
                               + "  DATECLOSED timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' OPTIONS (ANNOTATION '', NAMEINSOURCE '`DATECLOSED`', NATIVE_TYPE 'TIMESTAMP')"
                               + "  )" + " OPTIONS (ANNOTATION '', NAMEINSOURCE '`accounts`.`ACCOUNT`', UPDATABLE TRUE);";
        assertScoreAndParse(content, null, 1);
        final AstNode tableNode = getRootNode().getChildren().get(0);
        assertMixinType(tableNode, TeiidDdlLexicon.CreateTable.TABLE_STATEMENT);
        assertProperty(tableNode, TeiidDdlLexicon.SchemaElement.TYPE, SchemaElementType.FOREIGN.toDdl());
    }

    @Test
    public void shouldParsePortfolioBR() {
        final String content = "CREATE VIRTUAL FUNCTION performRuleOnData(className string, returnMethodName string, returnIfNull string, VARIADIC z object )"
                               + "RETURNS string OPTIONS (JAVA_CLASS 'org.jboss.teiid.businessrules.udf.RulesUDF',  JAVA_METHOD 'performRuleOnData', VARARGS 'true');"
                               + "CREATE VIEW StockPrices ("
                               + "  symbol string,"
                               + "  price bigdecimal"
                               + "  )"
                               + "  AS "
                               + "    SELECT StockPrices.symbol, StockPrices.price"
                               + "      FROM (EXEC MarketData.getTextFiles('*.txt')) AS f,"
                               + "        TEXTTABLE(f.file COLUMNS symbol string, price bigdecimal HEADER) AS StockPrices;"
                               + "CREATE VIEW Stock ("
                               + "  symbol string,"
                               + "  price bigdecimal,"
                               + "  company_name   varchar(256)"
                               + "  )"
                               + "  AS"
                               + "    SELECT  S.symbol, S.price, A.COMPANY_NAME"
                               + "      FROM StockPrices AS S, Accounts.PRODUCT AS A"
                               + "      WHERE S.symbol = A.SYMBOL;"
                               + "CREATE VIRTUAL PROCEDURE StockValidation() RETURNS (companyname varchar(256), symbol string(10), price bigdecimal, message varchar(256) )"
                               + "  AS"
                               + "    BEGIN"
                               + "      DECLARE String VARIABLES.msg;"
                               + "      CREATE LOCAL TEMPORARY TABLE TEMP (companyname string, symbol string, price bigdecimal, message string);"
                               + "      LOOP ON (SELECT Stock.symbol, Stock.price, Stock.company_name FROM Stock) AS txncursor"
                               + "      BEGIN"
                               + "        VARIABLES.msg = Stocks.performRuleOnData('org.jboss.teiid.quickstart.data.MarketData', 'getInvalidMessage', 'noMsg', txncursor.company_name, txncursor.symbol, txncursor.price);"
                               + "        IF(VARIABLES.msg <> 'NoMsg')"
                               + "          BEGIN"
                               + "            INSERT INTO TEMP (TEMP.companyname, TEMP.symbol, TEMP.price, TEMP.message) VALUES (txncursor.COMPANY_NAME, txncursor.symbol, txncursor.price, VARIABLES.msg);"
                               + "          END"
                               + "      END"
                               + "      SELECT TEMP.companyname, TEMP.symbol, TEMP.price, TEMP.message FROM TEMP;"
                               + "    END"
                               + "";

        assertScoreAndParse(content, null, 4);

        {
            final List<AstNode> kids = getRootNode().childrenWithName("performRuleOnData");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateProcedure.FUNCTION_STATEMENT);
        }

        {
            final List<AstNode> kids = getRootNode().childrenWithName("StockPrices");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateTable.VIEW_STATEMENT);
        }

        {
            final List<AstNode> kids = getRootNode().childrenWithName("Stock");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateTable.VIEW_STATEMENT);
        }

        {
            final List<AstNode> kids = getRootNode().childrenWithName("StockValidation");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateProcedure.PROCEDURE_STATEMENT);
        }
    }

    @Test
    public void shouldParseAndResolveTableReference() {
        final String content = "CREATE FOREIGN TABLE G2(g2e1 integer, g2e2 varchar, PRIMARY KEY(g2e1, g2e2), FOREIGN KEY (g2e1, g2e2) REFERENCES G1)"
                               + "CREATE FOREIGN TABLE G1(g1e1 integer, g1e2 varchar, PRIMARY KEY(g1e1, g1e2));";

        assertScoreAndParse(content, null, 2);

        {
            final List<AstNode> kids = getRootNode().childrenWithName("G1");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateTable.TABLE_STATEMENT);
        }

        {
            final List<AstNode> kids = getRootNode().childrenWithName("G2");
            assertThat(kids.size(), is(1));
            assertMixinType(kids.get(0), TeiidDdlLexicon.CreateTable.TABLE_STATEMENT);
        }
    }

    @Test
    public void shouldParseTeiidStatements_1() {
        // Parses a simplified DDL that contains 3 tables, 2 with Foreign keys.
        // The Tables in the DDL are arranged, such that the first table has FK reference to 3rd table
        // and results in an Unresolved table reference that should be handled by a postProcess() method

        printTest("shouldParseTeiidStatements_1()");
        String content = getFileContent(DDL_FILE_PATH + "sap_short_test.ddl");
        assertScoreAndParse(content, "teiid_test_statements_1", 3);
        final AstNode tableNode = getRootNode().getChildren().get(0);
        if (tableNode != null) {
            final List<AstNode> kids = getRootNode().childrenWithName("BookingCollection");
            assertThat(kids.size(), is(1));
            final List<AstNode> tableKids = kids.get(0).getChildren();
            assertThat(tableKids.size(), is(9));
            final List<AstNode> fkNodes = kids.get(0).childrenWithName("BookingFlight");
            assertThat(fkNodes.size(), is(1));

            final List<AstNode> fc_kids = getRootNode().childrenWithName("FlightCollection");
            assertThat(fc_kids.size(), is(1));
            final List<AstNode> fc_columns = fc_kids.get(0).childrenWithName("carrid");
            assertThat(fc_columns.size(), is(1));
            AstNode columnNode = fc_columns.get(0);

            @SuppressWarnings( "unchecked" )
            ArrayList<AstNode> props = ((ArrayList<AstNode>)fkNodes.get(0).getProperty(TeiidDdlLexicon.Constraint.TABLE_REFERENCE_REFERENCES));
            AstNode refColumnNode = props.get(0);
            assertThat(refColumnNode, is(columnNode));
        }
    }

    @Test
    public void shouldParseTeiidStatements_2() {
        // Parses a full DDL file that contains multiple tables with multiple FK's
        // The Tables in the DDL are arranged, such that the at least one table has FK reference to table defined later in the DDL
        // and results in an Unresolved table reference that should be handled by a postProcess() method

        printTest("shouldParseTeiidStatements_2()");
        String content = getFileContent(DDL_FILE_PATH + "sap-flight.ddl");
        assertScoreAndParse(content, "teiid_test_statements_2", 12);
        final AstNode tableNode = getRootNode().getChildren().get(0);

        if (tableNode != null) {
            final List<AstNode> kids = getRootNode().childrenWithName("BookingCollection");
            assertThat(kids.size(), is(1));
            final List<AstNode> tableKids = kids.get(0).getChildren();
            assertThat(tableKids.size(), is(28));
            final List<AstNode> fkNodes = kids.get(0).childrenWithName("BookingFlight");
            assertThat(fkNodes.size(), is(1));

            final List<AstNode> fc_kids = getRootNode().childrenWithName("FlightCollection");
            assertThat(fc_kids.size(), is(1));
            final List<AstNode> fc_columns = fc_kids.get(0).childrenWithName("carrid");
            assertThat(fc_columns.size(), is(1));
            AstNode columnNode = fc_columns.get(0);

            @SuppressWarnings( "unchecked" )
            ArrayList<AstNode> props = ((ArrayList<AstNode>)fkNodes.get(0).getProperty(TeiidDdlLexicon.Constraint.TABLE_REFERENCE_REFERENCES));
            AstNode refColumnNode = null;
            for (AstNode nextColumnNode : props) {

                if (nextColumnNode.getName().equals("carrid")) {
                    refColumnNode = nextColumnNode;
                    break;
                }
            }

            assertThat(refColumnNode, is(columnNode));
        }
    }

}
