/*
 * 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.jpa.fieldaccess.advanced;


import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import jakarta.persistence.EntityManager;

import org.eclipse.persistence.jpa.JpaEntityManager;
import org.eclipse.persistence.queries.*;
import org.eclipse.persistence.testing.models.jpa.fieldaccess.advanced.AdvancedTableCreator;
import org.eclipse.persistence.testing.models.jpa.fieldaccess.advanced.EmployeePopulator;
import org.eclipse.persistence.testing.models.jpa.fieldaccess.advanced.LargeProject;
import org.eclipse.persistence.testing.models.jpa.fieldaccess.advanced.Project;
import org.eclipse.persistence.testing.models.jpa.fieldaccess.advanced.SmallProject;
import org.eclipse.persistence.testing.models.jpa.fieldaccess.advanced.Employee;
import org.eclipse.persistence.testing.models.jpa.fieldaccess.advanced.Buyer;
import org.eclipse.persistence.testing.framework.junit.JUnitTestCase;
import junit.framework.TestSuite;
import junit.framework.Test;

public class SQLResultSetMappingTestSuite extends JUnitTestCase {
    protected boolean m_reset = false;    // reset gets called twice on error


    public SQLResultSetMappingTestSuite() {
    }

    public SQLResultSetMappingTestSuite(String name) {
        super(name);
    }

    @Override
    public void setUp () {
        m_reset = true;
        super.setUp();
        clearCache("fieldaccess");

        // Force uppercase for Postgres.
        if (getServerSession("fieldaccess").getPlatform().isPostgreSQL()) {
            getServerSession("fieldaccess").getLogin().setShouldForceFieldNamesToUpperCase(true);
        }
    }


    public static Test suite() {
        TestSuite suite = new TestSuite();
        suite.setName("SQLResultSetMappingTestSuite");

        suite.addTest(new SQLResultSetMappingTestSuite("testSetup"));
        suite.addTest(new SQLResultSetMappingTestSuite("testInheritanceNoDiscriminatorColumn"));
        suite.addTest(new SQLResultSetMappingTestSuite("testComplicateResultWithInheritance"));
        suite.addTest(new SQLResultSetMappingTestSuite("testRefresh"));
        suite.addTest(new SQLResultSetMappingTestSuite("testBindParameters"));
        suite.addTest(new SQLResultSetMappingTestSuite("testBindParametersWithPostitional"));
        suite.addTest(new SQLResultSetMappingTestSuite("testSimpleInheritance"));
        suite.addTest(new SQLResultSetMappingTestSuite("testPessimisticLocking"));
        suite.addTest(new SQLResultSetMappingTestSuite("testComplicateResults"));
        return suite;
    }

    @Override
    public String getPersistenceUnitName() {
        return "fieldaccess";
    }

    /**
     * The setup is done as a test, both to record its failure, and to allow execution in the server.
     */
    public void testSetup() {
        new AdvancedTableCreator().replaceTables(JUnitTestCase.getServerSession("fieldaccess"));
        EmployeePopulator employeePopulator = new EmployeePopulator();
        employeePopulator.buildExamples();
        //Persist the examples in the database
        employeePopulator.persistExample(getServerSession("fieldaccess"));
        clearCache("fieldaccess");
    }

    public void testInheritanceNoDiscriminatorColumn() throws Exception {
        SQLResultSetMapping resultSetMapping = new SQLResultSetMapping("testInheritanceNoDiscriminatorColumn");
        EntityResult entityResult = new EntityResult(Buyer.class);
        resultSetMapping.addResult(entityResult);
        entityResult.setDiscriminatorColumn("DTYPE_DESCRIM");

        SQLCall call = new SQLCall("SELECT t0.BUYER_ID, t0.DTYPE AS DTYPE_DESCRIM, t0.BUYER_NAME, t0.DESCRIP, t0.VERSION, t1.PURCHASES FROM CMP3_FA_BUYER t0, CMP3_FA_PBUYER t1 WHERE t1.BUYER_ID = t0.BUYER_ID");
        ResultSetMappingQuery query = new ResultSetMappingQuery(call);
        query.setSQLResultSetMapping(resultSetMapping);
        query.setShouldRefreshIdentityMapResult(true);
        EntityManager em = createEntityManager();
        beginTransaction(em);
        try{
            List results = (List)((JpaEntityManager)em.getDelegate()).getActiveSession().executeQuery(query);
            assertNotNull("No result returned", results);

            Buyer buyer = (Buyer)results.get(0);
            buyer.setDescription("To A new changed description");
            results = (List)((JpaEntityManager)em.getDelegate()).getActiveSession().executeQuery(query);
            assertNotNull("No result returned", results);
            assertFalse("Object was not refreshed", buyer.getDescription().equals("To A new changed description"));
        } finally {
            if (isTransactionActive(em)){
                rollbackTransaction(em);
            }
        }
    }

    public void testComplicateResultWithInheritance() throws Exception {
        SQLResultSetMapping resultSetMapping = new SQLResultSetMapping("ComplicatedInheritance");
        resultSetMapping.addResult(new ColumnResult("BUDGET_SUM"));
        EntityResult entityResult = new EntityResult(Project.class);
        resultSetMapping.addResult(entityResult);
        entityResult = new EntityResult(SmallProject.class);
        entityResult.addFieldResult(new FieldResult("id", "SMALL_ID"));
        entityResult.addFieldResult(new FieldResult("name", "SMALL_NAME"));
        entityResult.addFieldResult(new FieldResult("description", "SMALL_DESCRIPTION"));
        entityResult.addFieldResult(new FieldResult("teamLeader", "SMALL_TEAMLEAD"));
        entityResult.addFieldResult(new FieldResult("version", "SMALL_VERSION"));
        entityResult.setDiscriminatorColumn("SMALL_DESCRIM");
        resultSetMapping.addResult(entityResult);

        SQLCall call = new SQLCall("SELECT (t1.BUDGET/t0.PROJ_ID) AS BUDGET_SUM, t0.PROJ_ID, t0.PROJ_TYPE, t0.PROJ_NAME, t0.DESCRIP, t0.LEADER_ID, t0.VERSION, t1.BUDGET, t2.PROJ_ID AS SMALL_ID, t2.PROJ_TYPE AS SMALL_DESCRIM, t2.PROJ_NAME AS SMALL_NAME, t2.DESCRIP AS SMALL_DESCRIPTION, t2.LEADER_ID AS SMALL_TEAMLEAD, t2.VERSION AS SMALL_VERSION FROM CMP3_FA_PROJECT t0, CMP3_FA_PROJECT t2, CMP3_FA_LPROJECT t1 WHERE t1.PROJ_ID = t0.PROJ_ID AND t2.PROJ_TYPE='S'");
        ResultSetMappingQuery query = new ResultSetMappingQuery(call);
        query.setSQLResultSetMapping(resultSetMapping);
        List results = (List)getServerSession("fieldaccess").executeQuery(query);
        assertNotNull("No result returned", results);
        assertTrue("Empty list returned", (results.size()!=0));

        for (Iterator iterator = results.iterator(); iterator.hasNext(); ){
            Object[] resultElement = (Object[])iterator.next();
            assertTrue("Failed to Return 3 items", (resultElement.length == 3));
            // Using Number as Different db/drivers  can return different types
            // e.g. Oracle with ijdbc14.jar returns BigDecimal where as
            // Derby with derbyclient.jar returns Double
            assertTrue("Failed to return column",(resultElement[0] instanceof Number) );
            assertTrue("Failed to return LargeProject", (resultElement[1] instanceof LargeProject) );
            assertTrue("Failed To Return SmallProject", (resultElement[2] instanceof SmallProject) );
            assertFalse("Returned same data in both result elements",((SmallProject)resultElement[2]).getName().equals(((LargeProject)resultElement[1]).getName()));
        }
    }

    public void testRefresh() throws Exception {
        SQLResultSetMapping resultSetMapping = new SQLResultSetMapping("ComplicatedInheritance");
        EntityResult entityResult = new EntityResult(Project.class);
        resultSetMapping.addResult(entityResult);
        entityResult.setDiscriminatorColumn("SMALL_DESCRIM");

        SQLCall call = new SQLCall("SELECT t0.PROJ_ID, t0.PROJ_TYPE AS SMALL_DESCRIM, t0.PROJ_NAME, t0.DESCRIP, t0.LEADER_ID, t0.VERSION, t1.BUDGET FROM CMP3_FA_PROJECT t0, CMP3_FA_PROJECT t2, CMP3_FA_LPROJECT t1 WHERE t1.PROJ_ID = t0.PROJ_ID");
        ResultSetMappingQuery query = new ResultSetMappingQuery(call);
        query.setSQLResultSetMapping(resultSetMapping);
        query.setShouldRefreshIdentityMapResult(true);
        EntityManager em = createEntityManager();
        beginTransaction(em);
        try{
            List results = (List)getServerSession("fieldaccess").executeQuery(query);
            assertNotNull("No result returned", results);
            Project project = (Project)results.get(0);
            project.setDescription("To A new changed description");
            results = (List)getServerSession("fieldaccess").executeQuery(query);
            assertNotNull("No result returned", results);
            assertTrue("Empty list returned", (results.size()!=0));
            assertFalse("Object was not refreshed", project.getDescription().equals("To A new changed description"));
        }finally{
            if (isTransactionActive(em)){
                rollbackTransaction(em);
            }
            closeEntityManager(em);

        }
    }

    public void testBindParameters() throws Exception {
        SQLResultSetMapping resultSetMapping = new SQLResultSetMapping("BindParameters");
        EntityResult entityResult = new EntityResult(Project.class);
        resultSetMapping.addResult(entityResult);
        entityResult.setDiscriminatorColumn("SMALL_DESCRIM");

        SQLCall call = new SQLCall("SELECT t0.PROJ_ID, t0.PROJ_TYPE AS SMALL_DESCRIM, t0.PROJ_NAME, t0.DESCRIP, t0.LEADER_ID, t0.VERSION, t1.BUDGET FROM CMP3_FA_PROJECT t0, CMP3_FA_LPROJECT t1 WHERE t1.PROJ_ID = t0.PROJ_ID AND t1.BUDGET > ? AND t1.BUDGET < 30000000");
        ResultSetMappingQuery query = new ResultSetMappingQuery(call);
        query.setSQLResultSetMapping(resultSetMapping);
        query.setShouldRefreshIdentityMapResult(false);
        query.setShouldBindAllParameters(true);
        query.addArgument("1");
        Vector params = new Vector();
        //4000 is a more reasonable budget given test data if results are expected
        params.add(4000);
        List results = (List)getServerSession("fieldaccess").executeQuery(query, params);
        assertNotNull("No result returned", results);
        assertTrue("Empty list returned", (results.size()!=0));
    }

    public void testBindParametersWithPostitional() throws Exception {
        SQLResultSetMapping resultSetMapping = new SQLResultSetMapping("BindParameters");
        EntityResult entityResult = new EntityResult(Project.class);
        resultSetMapping.addResult(entityResult);
        entityResult.setDiscriminatorColumn("SMALL_DESCRIM");

        SQLCall call = new SQLCall("SELECT t0.PROJ_ID, t0.PROJ_TYPE AS SMALL_DESCRIM, t0.PROJ_NAME, t0.DESCRIP, t0.LEADER_ID, t0.VERSION, t1.BUDGET FROM CMP3_FA_PROJECT t0, CMP3_FA_LPROJECT t1 WHERE t1.PROJ_ID = t0.PROJ_ID AND t1.BUDGET > ? AND t1.BUDGET < 30000000");
        ResultSetMappingQuery query = new ResultSetMappingQuery(call);
        query.setSQLResultSetMapping(resultSetMapping);
        query.setShouldRefreshIdentityMapResult(false);
        query.setShouldBindAllParameters(true);
        query.addArgument("1");
        Vector params = new Vector();
        params.add(4000);
        List results = (List)getServerSession("fieldaccess").executeQuery(query, params);
        assertNotNull("No result returned", results);
        assertTrue("Empty list returned", (results.size()!=0));
    }
    public void testSimpleInheritance() throws Exception {
        SQLResultSetMapping resultSetMapping = new SQLResultSetMapping("SimpleInheritance");
        EntityResult entityResult = new EntityResult(Project.class);
        entityResult.setDiscriminatorColumn("SMALL_DESCRIM");
        resultSetMapping.addResult(entityResult);

        //Use ANSI outer join sytax so that the query works on most of the databases.
        //SQLCall call = new SQLCall("SELECT t0.PROJ_ID, t0.PROJ_TYPE AS SMALL_DESCRIM,  t0.PROJ_NAME, t0.DESCRIP, t0.LEADER_ID, t0.VERSION, t1.BUDGET FROM CMP3_FA_PROJECT t0, CMP3_FA_LPROJECT t1 WHERE t1.PROJ_ID (+)= t0.PROJ_ID");
        SQLCall call = new SQLCall("SELECT t0.PROJ_ID, t0.PROJ_TYPE AS SMALL_DESCRIM,  t0.PROJ_NAME, t0.DESCRIP, t0.LEADER_ID, t0.VERSION, t1.BUDGET FROM CMP3_FA_PROJECT t0 left outer join CMP3_FA_LPROJECT t1 on t1.PROJ_ID = t0.PROJ_ID");
        ResultSetMappingQuery query = new ResultSetMappingQuery(call);
        query.setSQLResultSetMapping(resultSetMapping);
        List results = (List)getServerSession("fieldaccess").executeQuery(query);
        assertNotNull("No result returned", results);
        assertTrue("Empty list returned", (results.size()!=0));
        for (Iterator iterator = results.iterator(); iterator.hasNext(); ){
            Object project = iterator.next();
            assertTrue("Failed to return a project", (project instanceof Project) );
        }
    }

    public void testPessimisticLocking() throws Exception {
        // Not all database support locking or the for update syntax.
        if (!getDatabaseSession().getPlatform().isOracle() && !getDatabaseSession().getPlatform().isMySQL()) {
            warning("FOR UPDATE syntax not supported.");
            return;
        }
        EntityManager em = createEntityManager();
        SmallProject smallProject = (SmallProject)getServerSession("fieldaccess").readObject(SmallProject.class);
        SQLResultSetMapping resultSetMapping = new SQLResultSetMapping("PessimisticLocking");
        EntityResult entityResult = new EntityResult(SmallProject.class);
        resultSetMapping.addResult(entityResult);

        SQLCall call = new SQLCall("SELECT t0.PROJ_ID, t0.PROJ_TYPE, t0.PROJ_NAME, t0.DESCRIP, t0.LEADER_ID, t0.VERSION FROM CMP3_FA_PROJECT t0 WHERE t0.PROJ_ID = " + smallProject.getId() + " FOR UPDATE");
        ResultSetMappingQuery query = new ResultSetMappingQuery(call);
        query.setSQLResultSetMapping(resultSetMapping);
        query.setLockMode(ObjectBuildingQuery.LOCK);
        beginTransaction(em);
        try{
            List results = (List)((JpaEntityManager)em.getDelegate()).getActiveSession().executeQuery(query);
            assertNotNull("No result returned", results);
            assertTrue("Empty list returned", (results.size()!=0));
            smallProject = (SmallProject)(results.get(0));
            smallProject.setDescription("A relatively new Description");
            commitTransaction(em);
        }catch (RuntimeException ex){
            if (isTransactionActive(em)){
                rollbackTransaction(em);
            }
            closeEntityManager(em);
            throw ex;
        }

        smallProject = em.find(SmallProject.class, smallProject.getId());
        closeEntityManager(em);
        assertTrue("Failed to update the new description", smallProject.getDescription().equals("A relatively new Description"));

    }

    /** tests that embeddable and dot notation for fieldresults work */
     public void testComplicateResults() throws Exception {
        SQLResultSetMapping resultSetMapping = new SQLResultSetMapping("ComplicatedInheritance");
        EntityResult entityResult;

        entityResult = new EntityResult(Employee.class);
        entityResult.addFieldResult(new FieldResult("period.startDate", "STARTDATE"));
        entityResult.addFieldResult(new FieldResult("address.id", "EMP_ADDR"));

        resultSetMapping.addResult(entityResult);
        SQLCall call = new SQLCall("SELECT t0.EMP_ID, t1.EMP_ID, t0.F_NAME, t0.L_NAME, t0.VERSION, t1.SALARY, t0.START_DATE AS STARTDATE, t0.END_DATE, t0.ADDR_ID AS EMP_ADDR, t0.MANAGER_EMP_ID FROM CMP3_FA_EMPLOYEE t0, CMP3_FA_SALARY t1 WHERE ((t1.EMP_ID = t0.EMP_ID) AND ( t0.L_NAME = 'Smith' ))");
        ResultSetMappingQuery query = new ResultSetMappingQuery(call);
        query.setSQLResultSetMapping(resultSetMapping);
        List results = (List)getServerSession("fieldaccess").executeQuery(query);
        assertNotNull("No result returned", results);
        assertTrue("Incorrect number of results returned, expected 2 got "+results.size(), (results.size()==2));

        for (Iterator iterator = results.iterator(); iterator.hasNext(); ){
            Object resultElement = iterator.next();
            assertTrue("Failed to return Employee", (resultElement instanceof Employee) );
            Employee emp = (Employee)resultElement;
            assertNotNull("Failed to get an address for Employee "+emp.getFirstName(), emp.getAddress() );
        }
    }


    @Override
    public void tearDown () {
        if (m_reset) {
            m_reset = false;
        }
        super.tearDown();
    }
}