/*******************************************************************************
 * Copyright (c) 2011, 2013 Oracle and/or its affiliates. All rights reserved.
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
 * which accompanies this distribution.
 * The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
 * and the Eclipse Distribution License is available at
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * Contributors:
 *     David McCann - June 28, 2012 - 2.4.1 - Initial implementation
 ******************************************************************************/
package dbws.testing.simplenestedtable;

//javase imports
import java.io.StringReader;
import java.sql.SQLException;
import java.sql.Statement;

import org.w3c.dom.Document;

//java eXtension imports
import javax.wsdl.WSDLException;

//JUnit4 imports
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;

//EclipseLink imports
import org.eclipse.persistence.internal.xr.Invocation;
import org.eclipse.persistence.internal.xr.Operation;
import org.eclipse.persistence.oxm.XMLMarshaller;
import org.eclipse.persistence.oxm.XMLUnmarshaller;
import org.eclipse.persistence.tools.dbws.DBWSBuilder;
import org.eclipse.persistence.tools.dbws.oracle.OracleHelper;

//test imports
import dbws.testing.DBWSTestSuite;

/**
 * Tests nested tables with columns containing scalar values. 
 *
 */
public class SimpleNestedTableTestSuite extends DBWSTestSuite {

    static final String CREATE_NUMBER_TABLE =
	    "CREATE OR REPLACE TYPE NUMTAB IS TABLE OF NUMBER";
	
    static final String CREATE_NUMBER_WRAPPER =
	    "CREATE OR REPLACE TYPE WRAPPER_NUMTAB AS OBJECT (" +
            "\nNUMBTABLIST NUMTAB" +
	    "\n);";

    static final String CREATE_TEST_NUMBER_PACKAGE =
	    "CREATE OR REPLACE PACKAGE TEST_NUMBER_LIST_PKG AS" +
	        "\nFUNCTION TEST_NUM RETURN WRAPPER_NUMTAB;" +
	    "\nEND TEST_NUMBER_LIST_PKG;";

    static final String CREATE_TEST_NUMBER_PACKAGE_BODY =
	    "CREATE OR REPLACE PACKAGE BODY TEST_NUMBER_LIST_PKG AS" +
	        "\nFUNCTION TEST_NUM RETURN WRAPPER_NUMTAB IS" +
            "\nL_WRAPPER_NUMTAB WRAPPER_NUMTAB;" +
            "\nL_NUMTAB NUMTAB;" +
            "\nBEGIN" +
                "\nL_NUMTAB := NUMTAB();" +
                "\nL_NUMTAB.EXTEND(1);" +
                "\nL_NUMTAB(1) := 115;" +
                "\nL_WRAPPER_NUMTAB := WRAPPER_NUMTAB(L_NUMTAB);" +
                "\nRETURN L_WRAPPER_NUMTAB;" +
            "\nEND;" +
	    "\nEND TEST_NUMBER_LIST_PKG;";
	
    static final String DROP_TEST_NUMBER_BODY =
        "DROP PACKAGE BODY TEST_NUMBER_LIST_PKG";
    static final String DROP_TEST_NUMBER_PACKAGE =
        "DROP PACKAGE TEST_NUMBER_LIST_PKG";
    static final String DROP_NUMBER_WRAPPER_TYPE =
        "DROP TYPE WRAPPER_NUMTAB";
    static final String DROP_NUMTAB_TYPE =
        "DROP TYPE NUMTAB";
    
    static final String CREATE_USERS_TABLE =
        "CREATE TABLE USERS_TABLE(" +
            "\nU_ID VARCHAR2(8)," +
            "\nU_NAME VARCHAR2(10)," +
            "\nPRIMARY KEY (U_ID)" +
        "\n)";
    static final String[] POPULATE_TABLE = new String[] {
        "INSERT INTO USERS_TABLE (U_ID, U_NAME) VALUES ('abc123', 'Ricky')",
        "INSERT INTO USERS_TABLE (U_ID, U_NAME) VALUES ('xxx666', 'Julian')",
        "INSERT INTO USERS_TABLE (U_ID, U_NAME) VALUES ('qwerty', 'Bubbles')",
        "INSERT INTO USERS_TABLE (U_ID, U_NAME) VALUES ('foobar', 'Conky')",
        "INSERT INTO USERS_TABLE (U_ID, U_NAME) VALUES ('321123', 'Lahey')",
        "INSERT INTO USERS_TABLE (U_ID, U_NAME) VALUES ('barfoo', 'Randy')"
    };
    
    static final String CREATE_USERS_PKG =
        "CREATE OR REPLACE PACKAGE USERS_PKG AS" +
            "\nFUNCTION GET_USERS_NAME_test(in_users_id_list IN USERS_ID_LIST_TYPE) RETURN VARCHAR2;" +
        "\nEND USERS_PKG;";
    static final String CREATE_USERS_BODY =
        "CREATE OR REPLACE PACKAGE BODY USERS_PKG AS" +
            "\nFUNCTION GET_USERS_NAME_TEST(in_users_id_list IN  USERS_ID_LIST_TYPE) RETURN VARCHAR2 AS" +
              "\nout_usersname VARCHAR2(255);" +
              "\nUSER_NAME VARCHAR2(10);" +
              "\nNUM NUMBER;" +
            "\nBEGIN" +
                "\nNUM := in_users_id_list.COUNT;" + 
                "\nFOR I IN 1..NUM LOOP" +
                    "\nSELECT U_NAME INTO USER_NAME FROM USERS_TABLE WHERE U_ID LIKE in_users_id_list(I);" +
                    "\nout_usersname := CONCAT(out_usersname, USER_NAME);" +
                    "\nIF I < NUM THEN" +
                        "\nout_usersname := CONCAT(out_usersname, ', ');" +
                    "\nEND IF;" +
                "\nEND LOOP;" +
                "\nRETURN out_usersname;" +
             "\nEND;" +
         "\nEND USERS_PKG;";
    static final String CREATE_USERS_ID_LIST_TYPE =
        "CREATE OR REPLACE TYPE USERS_ID_LIST_TYPE AS TABLE OF varchar2(8);";
    
    static final String DROP_USERS_BODY =
        "DROP PACKAGE BODY USERS_PKG";
    static final String DROP_USERS_PKG =
        "DROP PACKAGE USERS_PKG";
    static final String DROP_USERS_ID_LIST_TYPE =
        "DROP TYPE USERS_ID_LIST_TYPE";
    static final String DROP_USERS_TABLE =
        "DROP TABLE USERS_TABLE";

    static boolean ddlCreate = false;
    static boolean ddlDrop = false;
    static boolean ddlDebug = false;

    @BeforeClass
    public static void setUp() throws WSDLException {
        if (conn == null) {
            try {
                conn = buildConnection();
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
        String ddlCreateProp = System.getProperty(DATABASE_DDL_CREATE_KEY, DEFAULT_DATABASE_DDL_CREATE);
        if ("true".equalsIgnoreCase(ddlCreateProp)) {
            ddlCreate = true;
        }
        String ddlDropProp = System.getProperty(DATABASE_DDL_DROP_KEY, DEFAULT_DATABASE_DDL_DROP);
        if ("true".equalsIgnoreCase(ddlDropProp)) {
            ddlDrop = true;
        }
        String ddlDebugProp = System.getProperty(DATABASE_DDL_DEBUG_KEY, DEFAULT_DATABASE_DDL_DEBUG);
        if ("true".equalsIgnoreCase(ddlDebugProp)) {
            ddlDebug = true;
        }
        if (ddlCreate) {
            runDdl(conn, CREATE_NUMBER_TABLE, ddlDebug);
            runDdl(conn, CREATE_NUMBER_WRAPPER, ddlDebug);
            runDdl(conn, CREATE_TEST_NUMBER_PACKAGE, ddlDebug);
            runDdl(conn, CREATE_TEST_NUMBER_PACKAGE_BODY, ddlDebug);
            runDdl(conn, CREATE_USERS_TABLE, ddlDebug);
            runDdl(conn, CREATE_USERS_ID_LIST_TYPE, ddlDebug);
            runDdl(conn, CREATE_USERS_PKG, ddlDebug);
            runDdl(conn, CREATE_USERS_BODY, ddlDebug);
            try {
                Statement stmt = conn.createStatement();
                for (int i = 0; i < POPULATE_TABLE.length; i++) {
                    stmt.addBatch(POPULATE_TABLE[i]);
                }
                stmt.executeBatch();
            }
            catch (SQLException e) {
                //e.printStackTrace();
            }
        }
        DBWS_BUILDER_XML_USERNAME =
            "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
            "<dbws-builder xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +
              "<properties>" +
                  "<property name=\"projectName\">SimpleNestedTableTest</property>" +
                  "<property name=\"logLevel\">off</property>" +
                  "<property name=\"username\">";
          DBWS_BUILDER_XML_PASSWORD =
                  "</property><property name=\"password\">";
          DBWS_BUILDER_XML_URL =
                  "</property><property name=\"url\">";
          DBWS_BUILDER_XML_DRIVER =
                  "</property><property name=\"driver\">";
          DBWS_BUILDER_XML_PLATFORM =
                  "</property><property name=\"platformClassname\">";
          DBWS_BUILDER_XML_MAIN =
                  "</property>" +
              "</properties>" +
              "<plsql-procedure " +
                 "name=\"GetUsersTest\" " +
                 "catalogPattern=\"USERS_PKG\" " +
                 "procedurePattern=\"GET_USERS_NAME_TEST\" " +
              "/>" +
              "<plsql-procedure " +
                 "name=\"NumberTableTest\" " +
                 "catalogPattern=\"TEST_NUMBER_LIST_PKG\" " +
                 "procedurePattern=\"TEST_NUM\" " +
                 //"returnType=\"wrapper_numtabType\" " +
              "/>" +
            "</dbws-builder>";
          builder = new DBWSBuilder();
          OracleHelper builderHelper = new OracleHelper(builder);
          builder.setBuilderHelper(builderHelper);
          DBWSTestSuite.setUp(".");
    }

    @AfterClass
    public static void tearDown() {
        if (ddlDrop) {
            runDdl(conn, DROP_USERS_BODY, ddlDebug);
            runDdl(conn, DROP_USERS_PKG, ddlDebug);
            runDdl(conn, DROP_USERS_ID_LIST_TYPE, ddlDebug);
            runDdl(conn, DROP_USERS_TABLE, ddlDebug);

            runDdl(conn, DROP_TEST_NUMBER_BODY, ddlDebug);
            runDdl(conn, DROP_TEST_NUMBER_PACKAGE, ddlDebug);
            runDdl(conn, DROP_NUMBER_WRAPPER_TYPE, ddlDebug);
            runDdl(conn, DROP_NUMTAB_TYPE, ddlDebug);
        }
    }

    @Test
    public void testGetUsers() {
        XMLUnmarshaller unmarshaller = xrService.getXMLContext().createUnmarshaller();
        Object inputList = unmarshaller.unmarshal(new StringReader(INPUT_ID_LIST_XML));
        Invocation invocation = new Invocation("GetUsersTest");
        invocation.setParameter("in_users_id_list", inputList);
        Operation op = xrService.getOperation(invocation.getName());
        Object result = op.invoke(xrService, invocation);
        assertNotNull("result is null", result);
        Document doc = xmlPlatform.createDocument();
        XMLMarshaller marshaller = xrService.getXMLContext().createMarshaller();
        marshaller.marshal(result, doc);
        Document controlDoc = xmlParser.parse(new StringReader(OUTPUT_USERS_LIST_XML));
        assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc));
    }
    public static final String INPUT_ID_LIST_XML =
        STANDALONE_XML_HEADER +
        "<users_id_list_typeType xmlns=\"urn:SimpleNestedTableTest\">" +
          "<item>qwerty</item>" +
          "<item>321123</item>" +
          "<item>xxx666</item>" +
        "</users_id_list_typeType>";
    public static final String OUTPUT_USERS_LIST_XML =
        STANDALONE_XML_HEADER +
        "<value>Bubbles, Lahey, Julian</value>";

    @Test
    public void testNumberTable() {
        Invocation invocation = new Invocation("NumberTableTest");
        Operation op = xrService.getOperation(invocation.getName());
        Object result = op.invoke(xrService, invocation);
        assertNotNull("result is null", result);
        Document doc = xmlPlatform.createDocument();
        XMLMarshaller marshaller = xrService.getXMLContext().createMarshaller();
        marshaller.marshal(result, doc);
        Document controlDoc = xmlParser.parse(new StringReader(NUMBERS_XML));
        assertTrue("Expected:\n" + documentToString(controlDoc) + "\nActual:\n" + documentToString(doc), comparer.isNodeEqual(controlDoc, doc));
    }
    public static final String NUMBERS_XML =
        STANDALONE_XML_HEADER +
        "<wrapper_numtabType xmlns=\"urn:SimpleNestedTableTest\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">" +
          "<numbtablist>115</numbtablist>" +
        "</wrapper_numtabType>";
}