/*****************************************************************
 *   Licensed to the Apache Software Foundation (ASF) under one
 *  or more contributor license agreements.  See the NOTICE file
 *  distributed with this work for additional information
 *  regarding copyright ownership.  The ASF licenses this file
 *  to you 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
 *
 *    https://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.apache.cayenne.access;

import org.apache.cayenne.Cayenne;
import org.apache.cayenne.ObjectContext;
import org.apache.cayenne.PersistenceState;
import org.apache.cayenne.di.Inject;
import org.apache.cayenne.map.LifecycleEvent;
import org.apache.cayenne.query.EJBQLQuery;
import org.apache.cayenne.reflect.LifecycleCallbackRegistry;
import org.apache.cayenne.test.jdbc.DBHelper;
import org.apache.cayenne.test.jdbc.TableHelper;
import org.apache.cayenne.testdo.testmap.Artist;
import org.apache.cayenne.testdo.testmap.Painting;
import org.apache.cayenne.unit.UnitDbAdapter;
import org.apache.cayenne.unit.di.server.CayenneProjects;
import org.apache.cayenne.unit.di.server.ServerCase;
import org.apache.cayenne.unit.di.server.UseServerRuntime;
import org.junit.Before;
import org.junit.Test;

import java.math.BigDecimal;
import java.sql.Types;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;

@UseServerRuntime(CayenneProjects.TESTMAP_PROJECT)
public class DataContextEJBQLQueryIT extends ServerCase {

    @Inject
    private ObjectContext context;

    @Inject
    private DBHelper dbHelper;

    @Inject
    private UnitDbAdapter accessStackAdapter;

    private TableHelper tArtist;
    private TableHelper tPainting;

    @Before
    public void setUp() throws Exception {
        tArtist = new TableHelper(dbHelper, "ARTIST");
        tArtist.setColumns("ARTIST_ID", "ARTIST_NAME");

        tPainting = new TableHelper(dbHelper, "PAINTING");
        tPainting.setColumns(
                "PAINTING_ID",
                "ARTIST_ID",
                "PAINTING_TITLE",
                "ESTIMATED_PRICE").setColumnTypes(
                Types.INTEGER,
                Types.BIGINT,
                Types.VARCHAR,
                Types.DECIMAL);
    }

    private void createFourArtistsTwoPaintings() throws Exception {
        tArtist.insert(33001, "AA1");
        tArtist.insert(33002, "AA2");
        tArtist.insert(33003, "BB1");
        tArtist.insert(33004, "BB2");
        tPainting.insert(33001, 33001, "P1", 3000);
        tPainting.insert(33002, 33002, "P2", 5000);
    }

    /**
     * CAY-899: Checks that aggregate results do not cause callbacks execution.
     */
    @Test
    public void testSelectAggregatePostLoadCallback() throws Exception {

        createFourArtistsTwoPaintings();

        LifecycleCallbackRegistry existingCallbacks = context
                .getEntityResolver()
                .getCallbackRegistry();
        LifecycleCallbackRegistry testCallbacks = new LifecycleCallbackRegistry(
                context.getEntityResolver());

        DataContextEJBQLQueryCallback listener = new DataContextEJBQLQueryCallback();
        testCallbacks.addDefaultListener(LifecycleEvent.POST_LOAD, listener, "postLoad");

        context.getEntityResolver().setCallbackRegistry(testCallbacks);

        try {
            String ejbql = "select count(p), count(distinct p.estimatedPrice), max(p.estimatedPrice), sum(p.estimatedPrice) from Painting p";
            EJBQLQuery query = new EJBQLQuery(ejbql);

            List<?> data = context.performQuery(query);

            assertFalse(listener.postLoad);

            assertEquals(1, data.size());
            assertTrue(data.get(0) instanceof Object[]);
        }
        finally {
            context.getEntityResolver().setCallbackRegistry(existingCallbacks);
        }
    }

    @Test
    public void testSelectAggregate() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select count(p), count(distinct p.estimatedPrice), max(p.estimatedPrice), sum(p.estimatedPrice) from Painting p";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> data = context.performQuery(query);
        assertEquals(1, data.size());
        assertTrue(data.get(0) instanceof Object[]);
        Object[] aggregates = (Object[]) data.get(0);
        assertEquals(2L, aggregates[0]);
        assertEquals(2L, aggregates[1]);
        assertEquals(5000d, ((BigDecimal) aggregates[2]).doubleValue(), 0.00001);
        assertEquals(8000d, ((BigDecimal) aggregates[3]).doubleValue(), 0.00001);
    }

    @Test
    public void testSelectAggregateNull() throws Exception {

        if (!accessStackAdapter.supportNullRowForAggregateFunctions()) {
            return;
        }

        String ejbql = "select count(p), max(p.estimatedPrice), sum(p.estimatedPrice) "
                + "from Painting p WHERE p.paintingTitle = 'X'";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> data = context.performQuery(query);
        assertEquals(1, data.size());
        assertTrue(data.get(0) instanceof Object[]);
        Object[] aggregates = (Object[]) data.get(0);
        assertEquals(0L, aggregates[0]);
        assertEquals(null, aggregates[1]);
        assertEquals(null, aggregates[2]);
    }

    @Test
    public void testSelectEntityPathsScalarResult() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select p.paintingTitle"
                + " from Painting p order by p.paintingTitle DESC";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> data = context.performQuery(query);
        assertEquals(2, data.size());

        assertEquals("P2", data.get(0));
        assertEquals("P1", data.get(1));
    }

    @Test
    public void testSelectEntityPathsArrayResult() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select p.estimatedPrice, p.toArtist.artistName "
                + "from Painting p order by p.estimatedPrice";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> data = context.performQuery(query);
        assertEquals(2, data.size());

        assertTrue(data.get(0) instanceof Object[]);
        Object[] row0 = (Object[]) data.get(0);
        assertEquals(2, row0.length);
        assertEquals(3000d, ((BigDecimal) row0[0]).doubleValue(), 0.00001);
        assertEquals("AA1", row0[1]);

        assertTrue(data.get(1) instanceof Object[]);
        Object[] row1 = (Object[]) data.get(1);
        assertEquals(2, row1.length);
        assertEquals(5000d, ((BigDecimal) row1[0]).doubleValue(), 0.00001);
        assertEquals("AA2", row1[1]);
    }

    @Test
    public void testSelectDbPath() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select db:p.ESTIMATED_PRICE "
                + "from Painting p order by p.estimatedPrice";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> data = context.performQuery(query);
        assertEquals(2, data.size());

        assertEquals(3000d, ((BigDecimal) data.get(0)).doubleValue(), 0.00001);
        assertEquals(5000d, ((BigDecimal) data.get(1)).doubleValue(), 0.00001);
    }

    @Test
    public void testSelectDbPath_Relationship() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select db:p.toArtist "
                + "from Painting p order by p.estimatedPrice";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> data = context.performQuery(query);
        assertEquals(2, data.size());

        assertTrue(data.get(0) instanceof Artist);
        assertEquals(33001, Cayenne.intPKForObject((Artist) data.get(0)));

        assertTrue(data.get(1) instanceof Artist);
        assertEquals(33002, Cayenne.intPKForObject((Artist) data.get(1)));
    }

    @Test
    public void testSimpleSelect() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select a FROM Artist a";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> artists = context.performQuery(query);
        assertEquals(4, artists.size());
        assertTrue(artists.get(0) instanceof Artist);
        assertTrue(((Artist) artists.get(0)).getPersistenceState() == PersistenceState.COMMITTED);
    }

    @Test
    public void testFetchLimit() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select a FROM Artist a";
        EJBQLQuery query = new EJBQLQuery(ejbql);
        query.setFetchLimit(2);

        List<?> artists = context.performQuery(query);
        assertEquals(2, artists.size());
    }

    @Test
    public void testSelectFromWhereEqual() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select a from Artist a where a.artistName = 'AA2'";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> artists = context.performQuery(query);
        assertEquals(1, artists.size());
        assertEquals("AA2", ((Artist) artists.get(0)).getArtistName());
    }

    @Test
    public void testSelectFromWhereEqualReverseOrder() throws Exception {
        if (!accessStackAdapter.supportsReverseComparison()) {
            return;
        }

        createFourArtistsTwoPaintings();

        String ejbql = "select a from Artist a where 'AA2' = a.artistName";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> artists = context.performQuery(query);
        assertEquals(1, artists.size());
        assertEquals("AA2", ((Artist) artists.get(0)).getArtistName());
    }

    @Test
    public void testSelectFromWhereNot() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select a from Artist a where not a.artistName = 'AA2'";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> artists = context.performQuery(query);
        assertEquals(3, artists.size());
        Iterator<?> it = artists.iterator();
        while (it.hasNext()) {
            Artist a = (Artist) it.next();
            assertFalse("AA2".equals(a.getArtistName()));
        }
    }

    @Test
    public void testSelectFromWhereNotEquals() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select a from Artist a where a.artistName <> 'AA2'";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> artists = context.performQuery(query);
        assertEquals(3, artists.size());
        Iterator<?> it = artists.iterator();
        while (it.hasNext()) {
            Artist a = (Artist) it.next();
            assertFalse("AA2".equals(a.getArtistName()));
        }
    }

    @Test
    public void testSelectFromWhereOrEqual() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select a from Artist a where a.artistName = 'AA2' or a.artistName = 'BB1'";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> artists = context.performQuery(query);
        assertEquals(2, artists.size());

        Set<String> names = new HashSet<String>();
        Iterator<?> it = artists.iterator();
        while (it.hasNext()) {
            names.add(((Artist) it.next()).getArtistName());
        }

        assertTrue(names.contains("AA2"));
        assertTrue(names.contains("BB1"));
    }

    @Test
    public void testSelectFromWhereAndEqual() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select P from Painting P where P.paintingTitle = 'P1' "
                + "AND p.estimatedPrice = 3000";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> ps = context.performQuery(query);
        assertEquals(1, ps.size());

        Painting p = (Painting) ps.get(0);
        assertEquals("P1", p.getPaintingTitle());
        assertEquals(3000d, p.getEstimatedPrice().doubleValue(), 0.00001);
    }

    @Test
    public void testSelectFromWhereBetween() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select P from Painting P WHERE p.estimatedPrice BETWEEN 2000 AND 3500";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> ps = context.performQuery(query);
        assertEquals(1, ps.size());

        Painting p = (Painting) ps.get(0);
        assertEquals("P1", p.getPaintingTitle());
        assertEquals(3000d, p.getEstimatedPrice().doubleValue(), 0.00001);
    }

    @Test
    public void testSelectFromWhereNotBetween() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select P from Painting P WHERE p.estimatedPrice NOT BETWEEN 2000 AND 3500";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> ps = context.performQuery(query);
        assertEquals(1, ps.size());

        Painting p = (Painting) ps.get(0);
        assertEquals("P2", p.getPaintingTitle());
        assertEquals(5000d, p.getEstimatedPrice().doubleValue(), 0.00001);
    }

    @Test
    public void testSelectFromWhereGreater() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select P from Painting P WHERE p.estimatedPrice > 3000";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> ps = context.performQuery(query);
        assertEquals(1, ps.size());

        Painting p = (Painting) ps.get(0);
        assertEquals("P2", p.getPaintingTitle());
        assertEquals(5000d, p.getEstimatedPrice().doubleValue(), 0.00001);
    }

    @Test
    public void testSelectFromWhereGreaterOrEqual() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select P from Painting P WHERE p.estimatedPrice >= 3000";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> ps = context.performQuery(query);
        assertEquals(2, ps.size());
    }

    @Test
    public void testSelectFromWhereLess() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select P from Painting P WHERE p.estimatedPrice < 5000";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> ps = context.performQuery(query);
        assertEquals(1, ps.size());

        Painting p = (Painting) ps.get(0);
        assertEquals("P1", p.getPaintingTitle());
        assertEquals(3000d, p.getEstimatedPrice().doubleValue(), 0.00001);
    }

    @Test
    public void testSelectFromWhereLessOrEqual() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select P from Painting P WHERE p.estimatedPrice <= 5000";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> ps = context.performQuery(query);
        assertEquals(2, ps.size());
    }

    @Test
    public void testSelectFromWhereDecimalNumber() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select P from Painting P WHERE p.estimatedPrice <= 5000.00";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> ps = context.performQuery(query);
        assertEquals(2, ps.size());
    }

    @Test
    public void testSelectFromWhereDecimalNumberPositional() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select P from Painting P WHERE p.estimatedPrice <= ?1";
        EJBQLQuery query = new EJBQLQuery(ejbql);
        query.setParameter(1, new BigDecimal(5000.00));

        List<?> ps = context.performQuery(query);
        assertEquals(2, ps.size());
    }

    @Test
    public void testSelectFromWhereDecimalNumberNamed() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select P from Painting P WHERE p.estimatedPrice <= :param";
        EJBQLQuery query = new EJBQLQuery(ejbql);
        query.setParameter("param", new BigDecimal(5000.00));

        List<?> ps = context.performQuery(query);
        assertEquals(2, ps.size());
    }

    @Test
    public void testSelectFromWhereMatchOnObject() throws Exception {
        createFourArtistsTwoPaintings();

        Artist a = Cayenne.objectForPK(context, Artist.class, 33002);

        String ejbql = "select P from Painting P WHERE p.toArtist = :param";
        EJBQLQuery query = new EJBQLQuery(ejbql);
        query.setParameter("param", a);

        List<?> ps = context.performQuery(query);
        assertEquals(1, ps.size());

        Painting p = (Painting) ps.get(0);
        assertEquals(33002, Cayenne.intPKForObject(p));
    }

    @Test
    public void testSelectFromWhereMatchRelationshipAndScalar() throws Exception {
        createFourArtistsTwoPaintings();

        String ejbql = "select P from Painting P WHERE p.toArtist = 33002";
        EJBQLQuery query = new EJBQLQuery(ejbql);

        List<?> ps = context.performQuery(query);
        assertEquals(1, ps.size());

        Painting p = (Painting) ps.get(0);
        assertEquals(33002, Cayenne.intPKForObject(p));
    }

}
