/*
 * Copyright (c) 1998, 2021 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 v. 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0,
 * or the Eclipse Distribution License v. 1.0 which is available at
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
 */

// Contributors:
//     Oracle - initial API and implementation from Oracle TopLink
package org.eclipse.persistence.testing.tests.customsqlstoredprocedures;

import java.sql.Types;
import java.util.*;

import org.eclipse.persistence.queries.DataReadQuery;
import org.eclipse.persistence.queries.StoredFunctionCall;
import org.eclipse.persistence.queries.StoredProcedureCall;
import org.eclipse.persistence.sessions.UnitOfWork;
import org.eclipse.persistence.tools.schemaframework.*;
import org.eclipse.persistence.descriptors.ClassDescriptor;
import org.eclipse.persistence.internal.helper.DatabaseField;
import org.eclipse.persistence.testing.framework.*;
import org.eclipse.persistence.testing.models.employee.relational.EmployeeSystem;
import org.eclipse.persistence.testing.models.employee.domain.*;
import org.eclipse.persistence.testing.framework.InsertObjectTest;
import org.eclipse.persistence.testing.framework.ReadAllTest;
import org.eclipse.persistence.testing.framework.ReadObjectTest;
import org.eclipse.persistence.testing.framework.WriteObjectTest;
import org.eclipse.persistence.testing.tests.employee.EmployeeDeleteTest;
import org.eclipse.persistence.testing.tests.employee.ProjectDeleteTest;

public class CustomSQLTestModel extends TestModel {
    public CustomSQLTestModel() {
        setDescription("This model tests reading/writing/deleting  using Custom SQL with the employee demo.");
    }

    public CustomSQLTestModel(String description) {
        setDescription(description);
    }

    /**
     * This sets the custom SQL for the populate.
     */
    @Override
    public void addForcedRequiredSystems() {
        getExecutor().removeConfigureSystem(new EmployeeSystem());

        // Force the database to be recreated using custom SQL.
        addForcedRequiredSystem(new EmployeeCustomSQLSystem());
        if (getSession().getPlatform().isOracle()) {
            addForcedRequiredSystem(new InsuranceORStoredProcedureSystem());
        }
        // Force field names to upper case for custom SQL tests on postgres.
        if (getSession().getPlatform().isPostgreSQL()) {
            getSession().getPlatform().setShouldForceFieldNamesToUpperCase(true);
        }
    }

    @Override
    public void addRequiredSystems() {
        addRequiredSystem(new org.eclipse.persistence.testing.models.legacy.LegacySystem());
    }

    /**
    * Ensure that the employee model is setup and change the employee descriptor to use custom SQL.
    */
    @Override
    public void addTests() {
        addTest(getReadObjectTestSuite());
        addTest(getRefreshObjectTestSuite());
        addTest(getUpdateObjectTestSuite());
        addTest(getReadAllTestSuite());
        addTest(getInsertObjectTestSuite());
        addTest(getDeleteObjectTestSuite());
        addTest(getSelectWithOutputParametersTestSuite());
        addTest(getOutputParametersTestSuite());
        addTest(getStoredProcedureTestSuite());
        addTest(getStoredFunctionTestSuite());
    }

    public static TestSuite getDeleteObjectTestSuite() {
        TestSuite suite = new TestSuite();
        suite.setName("CustomSQLDeleteObjectTestSuite");
        suite.setDescription("This suite tests delete SQL.");

        Class<Employee> employeeClass = Employee.class;
        PopulationManager manager = PopulationManager.getDefaultManager();

        suite.addTest(new EmployeeDeleteTest(manager.getObject(employeeClass, "0001")));
        suite.addTest(new EmployeeDeleteTest(manager.getObject(employeeClass, "0002")));

        suite.addTest(new ProjectDeleteTest(manager.getObject(SmallProject.class, "0003")));
        suite.addTest(new ProjectDeleteTest(manager.getObject(LargeProject.class, "0001")));

        return suite;
    }

    public static TestSuite getInsertObjectTestSuite() {
        TestSuite suite = new TestSuite();
        suite.setName("CustomSQLInsertObjectTestSuite");
        suite.setDescription("This suite tests insert SQL.");
        org.eclipse.persistence.testing.models.employee.domain.EmployeePopulator system = new org.eclipse.persistence.testing.models.employee.domain.EmployeePopulator();

        suite.addTest(new InsertObjectTest(system.basicEmployeeExample4()));
        suite.addTest(new InsertObjectTest(system.basicEmployeeExample5()));

        return suite;
    }

    public TestSuite getOutputParametersTestSuite() {
        TestSuite test = new TestSuite();
        test.setDescription("This Suite is used to test special Output Parameter functions ");
        test.setName("OutputParametersTestSuite");

        test.addTest(new OutputParameterEventTest((Employee)(new EmployeePopulator()).basicEmployeeExample10()));

        return test;
    }

    public static TestSuite getReadAllTestSuite() {
        TestSuite suite = new TestSuite();
        suite.setName("CustomSQLReadAllTestSuite");
        suite.setDescription("This suite tests read all sql.");

        suite.addTest(new ReadAllTest(Employee.class, 12));
        suite.addTest(new CustomSQLSubQueryTest());

        return suite;
    }

    public static TestSuite getReadObjectTestSuite() {
        TestSuite suite = new TestSuite();
        suite.setName("CustomSQLReadObjectTestSuite");
        suite.setDescription("This suite test read sql.");

        Class<Employee> employeeClass = Employee.class;
        PopulationManager manager = PopulationManager.getDefaultManager();

        suite.addTest(new ReadObjectTest(manager.getObject(employeeClass, "0001")));
        suite.addTest(new ReadObjectTest(manager.getObject(employeeClass, "0004")));
        suite.addTest(new CacheHitTest(manager.getObject(employeeClass, "0003")));
        suite.addTest(new CustomSQLCursoredStreamReadTest());

        // Add some test from the owenership to test multiple table and primary key
        //suite.addTest(new ReadObjectTest(
        //    manager.getObject(org.eclipse.persistence.testing.models.legacy.Employee.class, "example3")));
        return suite;
    }

    public TestSuite getRefreshObjectTestSuite() {
        TestSuite suite = new TestSuite();
        suite.setName("RefreshObjectTestSuite");
        suite.setDescription("This Suite contains tests that verify that Refreshing of Objects is working correctly");
        Class<Employee> employeeClass = Employee.class;
        PopulationManager manager = PopulationManager.getDefaultManager();
        suite.addTest(new ReadObjectQueryTest((Employee)manager.getObject(employeeClass, "0003")));
        return suite;
    }

    public TestSuite getSelectWithOutputParametersTestSuite() {
        TestSuite test = new TestSuite();
        test.setDescription("This Suite is used to test special Output Parameter functions when using selecting queries");
        test.setName("SelectWithOutputParametersTestSuite");
        test.addTest(new DataReadQueryTest());
        // StoredProcWithOutputParamsAndResultSetTest(useCustomSQL, shouldBindAllParameters)
        test.addTest(new StoredProcWithOutputParamsAndResultSetTest(false, false));
        test.addTest(new StoredProcWithOutputParamsAndResultSetTest(false, true));
        test.addTest(new StoredProcWithOutputParamsAndResultSetTest(true, false));
        test.addTest(new StoredProcWithOutputParamsAndResultSetTest(true, true));
        return test;
    }

    public static TestSuite getStoredProcedureTestSuite() {
        TestSuite suite = new TestSuite();
        suite.setName("CustomSQLStoredProcedureTestSuite");
        suite.setDescription("This suite tests certains aspects of StoredProcedures");

        Class<Employee> employeeClass = Employee.class;
        PopulationManager manager = PopulationManager.getDefaultManager();

        suite.addTest(new CacheHitTest(manager.getObject(employeeClass, "0003")));
        suite.addTest(new StoredProcedureTest());
        suite.addTest(new StoredProcedureTest(false));
        suite.addTest(new StoredProcedureTimestampTest());
        suite.addTest(new StoredProcedureTimestampTest(false));

        // StoredProcedureTest_Inout_Out_In procUseCustomSQL(boolean shouldBindAllParameters)
        suite.addTest(StoredProcedureTest_Inout_Out_In.procUseCustomSQL(false));
        suite.addTest(StoredProcedureTest_Inout_Out_In.procUseCustomSQL(true));
        // StoredProcedureTest_Inout_Out_In proc(boolean useArgumentNames, boolean shouldBindAllParameters)
        suite.addTest(StoredProcedureTest_Inout_Out_In.proc(false, false));
        suite.addTest(StoredProcedureTest_Inout_Out_In.proc(false, true));
        suite.addTest(StoredProcedureTest_Inout_Out_In.proc(true, false));
        suite.addTest(StoredProcedureTest_Inout_Out_In.proc(true, true));

        // StoredProcedureTest_Inout_Out_In funcUseCustomSQL(boolean shouldBindAllParameters)
        suite.addTest(StoredProcedureTest_Inout_Out_In.funcUseCustomSQL(false));
        suite.addTest(StoredProcedureTest_Inout_Out_In.funcUseCustomSQL(true));
        // StoredProcedureTest_Inout_Out_In func(boolean useArgumentNames, boolean shouldBindAllParameters)
        suite.addTest(StoredProcedureTest_Inout_Out_In.func(false, false));
        suite.addTest(StoredProcedureTest_Inout_Out_In.func(false, true));
        suite.addTest(StoredProcedureTest_Inout_Out_In.func(true, false));
        suite.addTest(StoredProcedureTest_Inout_Out_In.func(true, true));
        suite.addTest(new StoredProcedureARRAYTest());

        suite.addTest(new StoredProcedureNullInOutTest());
        suite.addTest(new StoredProcedureNullInOutTest(false));
        suite.addTest(new StoredProcedureResultSetAndOutputTest());
        suite.addTest(new StoredProcedureResultSetAndOutputTest(false));

        suite.addTest(new StoredProcedureNoParametersTest());
        suite.addTest(new StoredProcedureObjectRelationalParameters(false));
        suite.addTest(new StoredProcedureObjectRelationalParameters(true));
        suite.addTest(new StoredProcedureVARRAYParametersTest(false));
        suite.addTest(new StoredProcedureVARRAYParametersTest(true));
        suite.addTest(new StoredProcedureORParametersClientSessionTest());
        suite.addTest(buildSQLTransactionTest());
        suite.addTest(build2OutCursorTest());
        suite.addTest(buildUnnamedCursorTest());
        suite.addTest(build2ResultSetTest());
        return suite;
    }

    public static TestSuite getStoredFunctionTestSuite() {
        TestSuite suite = new TestSuite();
        suite.setName("CustomSQLStoredFunctionTestSuite");
        suite.setDescription("This suite tests certains aspects of StoredFunction");
        suite.addTest(buildStoredFunctionRefCursorTest());
        return suite;
    }

    public static TestSuite getUpdateObjectTestSuite() {
        TestSuite suite = new TestSuite();
        suite.setName("CustomSQLUpdateObjectTestSuite");
        suite.setDescription("This suite tests update sql.");

        Class<Employee> employeeClass = Employee.class;
        PopulationManager manager = PopulationManager.getDefaultManager();

        suite.addTest(new WriteObjectTest(manager.getObject(employeeClass, "0001")));
        suite.addTest(new WriteObjectTest(manager.getObject(employeeClass, "0005")));

        suite.addTest(new WriteObjectTest(manager.getObject(SmallProject.class, "0003")));
        suite.addTest(new WriteObjectTest(manager.getObject(LargeProject.class, "0001")));
        suite.addTest(new SetCustomSQLQueryTest("UPDATE EMPLOYEE SET F_NAME = 'Fatima?' WHERE L_NAME = 'Smith'"));
        Vector v = new Vector();
        v.addElement("L_NAME");
        Vector myV = new Vector();
        myV.addElement("Smith");
        suite.addTest(new SetCustomSQLQueryTest("UPDATE EMPLOYEE SET F_NAME = 'Fatima' WHERE L_NAME = #L_NAME", v, myV));

        return suite;
    }

    /**
     * Revert the descriptors back to their old state.
     */
    @Override
    public void reset() {
        getExecutor().removeConfigureSystem(new EmployeeCustomSQLSystem());

        (new EmployeeSystem()).addDescriptors(getDatabaseSession());
        (new org.eclipse.persistence.testing.models.mapping.MappingSystem()).addDescriptors(getDatabaseSession());
    }

    /**
     * Ensure that the employee model is setup and change the employee descriptor to use custom SQL.
     */
    @Override
    public void setup() {
        // Setup complex mapping employee as well.
        ClassDescriptor empDescriptor = getSession().getClassDescriptor(org.eclipse.persistence.testing.models.legacy.Employee.class);
        empDescriptor.getQueryManager().setReadObjectSQLString("select LEG_EMP.*, LEG_ADD.* FROM LEG_EMP, LEG_ADD WHERE (((LEG_EMP.FNAME = #LEG_EMP.FNAME) AND (LEG_EMP.LNAME = #LEG_EMP.LNAME)) AND ((LEG_ADD.FIRST_NM = #LEG_EMP.FNAME) AND (LEG_ADD.LNAME = #LEG_EMP.LNAME)))");
    }

    /**
     * Test that transaction with only SQL queries commit.
     */
    public static TestCase buildSQLTransactionTest() {
        TestCase test = new TestCase() {
            @Override
            public void test() {
                UnitOfWork uow = getSession().acquireUnitOfWork();
                uow.executeNonSelectingSQL("Insert into ADDRESS (ADDRESS_ID) values (999999)");
                uow.commit();
                try {
                    if (getAbstractSession().isInTransaction()
                            || (getSession().executeSQL("Select * from ADDRESS where ADDRESS_ID = 999999").size() == 0)) {
                        throwError("Database transaction not committed.");
                    }
                } finally {
                    uow = getSession().acquireUnitOfWork();
                    uow.executeNonSelectingSQL("Delete from ADDRESS where ADDRESS_ID = 999999");
                    uow.commit();
                }
            }
        };
        test.setName("SQLTransactionTest");
        return test;
    }

    /**
     * Test a procedure with 2 out cursors.
     */
    public static TestCase build2OutCursorTest() {
        TestCase test = new TestCase() {
            @Override
            public void test() {
                if (!(getSession().getPlatform().isOracle())) {
                    throwWarning("This test can only be run in Oracle");
                }
                DataReadQuery query = new DataReadQuery();
                StoredProcedureCall call = new StoredProcedureCall();
                call.setProcedureName("Read_Emp_Add");
                call.getProcedureArgumentNames().add("CUR1");
                call.appendOutCursor(new DatabaseField("CUR1"));
                call.getProcedureArgumentNames().add("CUR2");
                call.appendOutCursor(new DatabaseField("CUR2"));
                query.setCall(call);
                List<Map> result = (List<Map>)getSession().executeQuery(query);
                if (!(result.get(0).get("CUR1") instanceof List)) {
                    throwError("CUR1 not in output");
                }
                if (!(result.get(0).get("CUR2") instanceof List)) {
                    throwError("CUR2 not in output");
                }
            }
        };
        test.setName("2OutCursorTest");
        return test;
    }

    /**
     * Test a procedure with unnamed cursor.
     */
    public static TestCase buildUnnamedCursorTest() {
        TestCase test = new TestCase() {
            @Override
            public void test() {
                if (!(getSession().getPlatform().isOracle())) {
                    throwWarning("This test can only be run in Oracle");
                }
                DataReadQuery query = new DataReadQuery();
                StoredProcedureCall call = new StoredProcedureCall();
                call.setProcedureName("Read_All_Employees");
                call.useUnnamedCursorOutputAsResultSet();
                query.setCall(call);
                List<Map> result = (List<Map>)getSession().executeQuery(query);
                result.size();
                result = (List<Map>)getSession().executeQuery(query);
            }
        };
        test.setName("UnnamedCursorTest");
        return test;
    }

    /**
     * Test a procedure with multiple result sets
     */
    public static TestCase build2ResultSetTest() {
        TestCase test = new TestCase() {
            @Override
            public void test() {
                if (!(getSession().getPlatform().isMySQL())) {
                    throwWarning("This test can only be run in MySQL");
                }
                DataReadQuery query = new DataReadQuery();
                StoredProcedureCall call = new StoredProcedureCall();
                call.setProcedureName("Read_Emp_Add");
                call.setHasMultipleResultSets(true);
                query.setCall(call);
                List<Map> result = (List<Map>)getSession().executeQuery(query);
                if (result.size() != (getSession().readAllObjects(Employee.class).size() + getSession().readAllObjects(Address.class).size())) {
                    throwError("Incorrect number of rows returned : " + result);
                }
                result = (List<Map>)getSession().executeQuery(query);
            }
        };
        test.setName("2ResultSetTest");
        return test;
    }

    /**
     * Test a stored function with ref cursor.
     */
    public static TestCase buildStoredFunctionRefCursorTest() {
        TestCase test = new TestCase() {
            @Override
            public void test() {
                if (!(getSession().getPlatform().isOracle())) {
                    throwWarning("This test can only be run in Oracle");
                }
                StoredFunctionCall call = new StoredFunctionCall();
                call.setProcedureName("PackageFunction_ResultCursor.BUSINESS_DATE");
                call.addNamedArgument("P_CODE");
                call.addNamedArgument("P_LOOKUP_TBL", "P_LOOKUP_TBL", Types.ARRAY, "SF_LOOKUP_TBL");
                call.setResultCursor();

                DataReadQuery query = new DataReadQuery();
                query.setCall(call);
                query.addArgument("P_CODE");
                query.addArgument("P_LOOKUP_TBL");

                Object[] data = new Object[] {"5-Jul-13", 5L};

                List args = new ArrayList();
                args.add("CN");
                args.add(new Object[] {data});

                List<Map> result = (List<Map>) getSession().executeQuery(query, args);
                if (result == null || result.size() != 1) {
                    throwError("Incorrect number of rows returned: " + result);
                }
            }
        };
        test.setName("StoredFunctionRefCursorTest");
        return test;
    }
}