/*
 * 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
 *
 * 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.apache.openjpa.persistence.embed;

import java.io.IOException;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.EntityTransaction;
import jakarta.persistence.PersistenceUnitUtil;
import jakarta.persistence.Query;

import org.apache.openjpa.jdbc.conf.JDBCConfiguration;
import org.apache.openjpa.persistence.ArgumentException;
import org.apache.openjpa.persistence.OpenJPAEntityManager;
import org.apache.openjpa.persistence.OpenJPAEntityManagerSPI;
import org.apache.openjpa.persistence.OpenJPAPersistence;
import org.apache.openjpa.persistence.test.SQLListenerTestCase;

public class TestEmbeddable extends SQLListenerTestCase {
    private static final Calendar cal = new GregorianCalendar();
    private static final Integer timeHash = cal.hashCode();
    public int numEmbeddables = 1;
    public int numBasicTypes = 1;
    public int numProgramManagers = 2;
    public int numNickNames = 3;
    public int numEmployeesPerPhoneNumber = 1;
    public int numPhoneNumbersPerEmployee = 2;
    public int numEmployeesPerProgramManager = 2;
    public int numEmployees = numProgramManagers * numEmployeesPerProgramManager;
    public int numPhoneNumbers = numEmployees * numPhoneNumbersPerEmployee;
    public int numDepartments = 2;
    public int numEmployeesPerDept = 2;
    public int numItems = 2;
    public int numImagesPerItem = 3;
    public int numCompany = 2;
    public int numDivisionsPerCo = 2;
    public int ID = 1;
    public int itemId = 1;
    public int compId = 1;
    public int divId = 1;
    public int vpId = 1;
    public int deptId = 1;
    public int empId = 1;
    public int phoneId = 1;
    public int pmId = 1;
    public int parkingSpotId = 1;
    public Map<Integer, PhoneNumber> phones = new HashMap<>();
    public Map<Integer, Employee> employees = new HashMap<>();

    @Override
    public void setUp() {
        setUp(Embed.class, Embed_Coll_Embed.class, Embed_Coll_Integer.class,
            Embed_Embed.class, Embed_Embed_ToMany.class, Embed_ToMany.class,
            Embed_ToOne.class, EntityA_Coll_Embed_ToOne.class,
            EntityA_Coll_String.class, EntityA_Embed_Coll_Embed.class,
            EntityA_Embed_Coll_Integer.class, EntityA_Embed_Embed.class,
            EntityA_Embed_Embed_ToMany.class, EntityA_Embed_ToMany.class,
            EntityA_Embed_ToOne.class, EntityB1.class,
            EntityA_Coll_Embed_Embed.class, ContactInfo.class,
            Employee.class, JobInfo.class, LocationDetails.class,
            ParkingSpot.class, PhoneNumber.class, ProgramManager.class,
            Department1.class, Employee1.class, Department2.class,
            Employee2.class, EmployeePK2.class, Department3.class,
            Employee3.class, EmployeeName3.class, Item1.class, Item2.class,
            Item3.class, Item4.class, Item5.class, FileName4.class,
            Company1.class, Company2.class, Division.class,
            VicePresident.class, EntityA_Embed_MappedToOne.class,
            Embed_MappedToOne.class, Embed_MappedToOneCascadeDelete.class,
            EntityA_Embed_MappedToOneCascadeDelete.class, EntityB2.class,
            Book.class, Listing.class, Seller.class,
            EntityA_Embed_Coll_Map.class, Embed_Coll_Map.class,
            EntityA_Embed_Single_Coll.class, Embed_Single_Coll.class, EntityA_Embed.class,
            EntityA_Embed_Complex.class, A.class, CLEAR_TABLES);
            sql.clear();
    }

    public void testGroupByEmbed() {
        EntityManager em = emf.createEntityManager();
        String query[] = {
                "select KEY(e) from Department3 d join d.emps e group by KEY(e)",
                "select a.embed from EntityA_Embed_Embed a group by a.embed",
                "select e from EntityA_Embed_Embed a join a.embed e group by e",
        };
        List rs = null;
        for (String s : query) {
            try {
                rs = em.createQuery(s).getResultList();
            }
            catch (ArgumentException e) {
                System.out.println(e.getMessage()); // as expected : Group by embeddable field is not allowed
            }
        }
        em.close();
    }

    public void testKeyEmbeddableCompare() {
        EntityManager em = emf.createEntityManager();
        String query[] = {
                "select o from Company1 c join c.organization o where KEY(o) = ?1",
                "select d from Department3 d join d.emps e where KEY(e) = ? 1"
        };
        List rs = null;
        Division d = new Division();
        d.setId(10);
        d.setName("division 10");
        EmployeeName3 name = new EmployeeName3("fname1", "lname1");
        for (int i = 0; i < query.length; i++) {
            switch (i) {
            case 0:
                // Division is an embeddable as well as an entity, comparison is allowed
                rs = em.createQuery(query[i]).setParameter(1, d).getResultList();
                break;
            case 1:
                try {
                    rs = em.createQuery(query[i]).setParameter(1, name).getResultList();
                } catch(ArgumentException e) {
                    // as expected : compare embeddable is not allowed
                    System.out.println(e.getMessage());
                }
                break;
            }
        }
        em.close();
    }

    public void testEntityA_Embed_Coll_Map() {
        queryEntityA_Embed_Coll_Map();
    }

    /**
     * Test for OJ-1793.
     */
    public void testEntityA_Embed_Single_Coll() {
        EntityManager em = emf.createEntityManager();

        //create an EntityA_Embed_Single_Coll and persist it.
        EntityA_Embed_Single_Coll eesc = new EntityA_Embed_Single_Coll();
        Embed_Single_Coll esc = new Embed_Single_Coll();
        java.sql.Date date = java.sql.Date.valueOf("2010-10-13");
        esc.setDate(date);
        eesc.setEmbed(esc);
        em.getTransaction().begin();
        em.persist(eesc);
        em.flush();
        em.getTransaction().commit();
        em.close();

        //Now query the recently created EntityA_Embed_Single_Coll.  Without OJ-1793, when you query
        //the EntityA_Embed_Single_Coll object, the issue of OJ-1793 will occur, regardless of the query
        //string (even the simplest string will do).
        em = emf.createEntityManager();
        Query query1 = em.createQuery("SELECT e FROM EntityA_Embed_Single_Coll e "
            + "where e.embed.date = :wheredate");
        query1.setParameter("wheredate", date);
        eesc = (EntityA_Embed_Single_Coll) query1.getSingleResult();
        assertEquals(eesc.getEmbed().getDate().toString(), date.toString());
        em.close();
    }

    /**
     * Test for OJ-1890.
     */
    public void testSettingEmbeddableMultipleTimesOnManagedEntity() {
        createEntityA_Embed();
        EntityManager em = emf.createEntityManager();
        EntityA_Embed ee = em.find(EntityA_Embed.class, ID);
        Embed embed = createEmbed(ID, 0);
        ee.setEmbed(embed);
        Embed embed1 = createEmbed(ID, 1);
        ee.setEmbed(embed1);
        em.close();
    }

    public void queryEntityA_Embed_Coll_Map() {
        EntityManager em = emf.createEntityManager();
        String query[] = {
                "select e from EntityA_Embed_Coll_Map a join a.embed e " +
                "join e.mapKeyInteger i where value(i) > 0 ",
            };
        List rs = null;
        for (String s : query) {
            rs = em.createQuery(s).getResultList();
            em.clear();
        }
        em.close();
    }

    public void testEntityA_Coll_String() {
        createEntityA_Coll_String();
        queryEntityA_Coll_String();
        findEntityA_Coll_String();
    }

    public void testEntityA_Embed_ToOne() {
        createEntityA_Embed_ToOne();
        queryEntityA_Embed_ToOne();
        findEntityA_Embed_ToOne();
    }

    public void testEntityA_Embed_MappedToOne() {
        createEntityA_Embed_MappedToOne();
        queryEntityA_Embed_MappedToOne();
        findEntityA_Embed_MappedToOne();
    }

    public void testEntityA_Coll_Embed_ToOne() {
        createEntityA_Coll_Embed_ToOne();
        queryEntityA_Coll_Embed_ToOne();
        findEntityA_Coll_Embed_ToOne();
    }

    public void testEntityA_Embed_ToMany() {
        createEntityA_Embed_ToMany();
        queryEntityA_Embed_ToMany();
        findEntityA_Embed_ToMany();
    }

    public void testEntityA_Embed_Embed_ToMany() {
        createEntityA_Embed_Embed_ToMany();
        queryEntityA_Embed_Embed_ToMany();
        findEntityA_Embed_Embed_ToMany();
    }

    public void testEntityA_Embed_Coll_Integer() {
        createEntityA_Embed_Coll_Integer();
        queryEntityA_Embed_Coll_Integer();
        findEntityA_Embed_Coll_Integer();
    }

    public void testEntityA_Embed_Embed() {
        createEntityA_Embed_Embed();
        queryEntityA_Embed_Embed();
        findEntityA_Embed_Embed();
    }

    public void testEntityA_Coll_Embed_Embed() {
        createEntityA_Coll_Embed_Embed();
        queryEntityA_Coll_Embed_Embed();
        findEntityA_Coll_Embed_Embed();
    }

    public void testEntityA_Embed_Coll_Embed() {
        createEntityA_Embed_Coll_Embed();
        queryEntityA_Embed_Coll_Embed();
        findEntityA_Embed_Coll_Embed();
    }

    public void testEmployee() {
        createEmployeeObj();
        queryEmployeeObj();
        findEmployeeObj();
    }

    public void testMapKey() {
        createObjMapKey();
        queryObjMapKey();
        findObjMapKey();
    }

    public void testMapKeyClass() {
        createObjMapKeyClass();
        queryObjMapKeyClass();
        findObjMapKeyClass();
    }

    public void testMapKeyEnumerated() {
        createObjMapKeyEnumerated();
        findObjMapKeyEnumerated();
    }

    public void testMapKeyTemporal() {
        createObjMapKeyTemporal();
        findObjMapKeyTemporal();
        queryObjMapKeyTemporal();
    }

    public void testEntityA_Embed_MappedToOneCascadeDelete() {
        createEntityA_Embed_MappedToOneCascadeDelete();
        updateEntityA_Embed_MappedToOneCascadeDelete();
    }

    public void testEmbeddableContainingRelationWithGeneratedKey()
        throws IOException, SQLException {
        createEmbeddableContainingRelationWithGeneratedKey();
    }

    /*
     * Create EntityA_Coll_String
     */
    public void createEntityA_Coll_String() {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        createEntityA_Coll_String(em, ID);

        EntityB1 b = new EntityB1();
        b.setId(ID);
        b.setName("b" + ID);
        em.persist(b);

        tran.begin();
        em.flush();
        tran.commit();
        em.close();
    }

    public void createEntityA_Coll_String(EntityManager em, int id) {
        EntityA_Coll_String a = new EntityA_Coll_String();
        a.setId(id);
        a.setName("a" + id);
        a.setAge(id);
        for (int i = 0; i < numBasicTypes; i++)
            a.addNickName("nickName_" + id + i);
        a.addCreditRating(EntityA_Coll_String.CreditRating.POOR);
        a.addTimestamp(new Timestamp(System.currentTimeMillis()));
        a.addLob("lob");
        em.persist(a);
    }

    /*
     * Create EntityA_Embed_ToOne
     */
    public void createEntityA_Embed_ToOne() {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        createEntityA_Embed_ToOne(em, ID);
        tran.begin();
        em.flush();
        tran.commit();
        em.close();
    }

    public void createEntityA_Embed_ToOne(EntityManager em, int id) {
        EntityA_Embed_ToOne a = new EntityA_Embed_ToOne();
        a.setId(id);
        a.setName("a" + id);
        a.setAge(id);
        Embed_ToOne embed = createEmbed_ToOne(em, id);
        a.setEmbed(embed);
        em.persist(a);
    }

    public Embed_ToOne createEmbed_ToOne(EntityManager em, int id) {
        Embed_ToOne embed = new Embed_ToOne();
        embed.setName1("name1");
        embed.setName2("name2");
        embed.setName3("name3");
        EntityB1 b = new EntityB1();
        b.setId(id);
        b.setName("b" + id);
        embed.setEntityB(b);
        em.persist(b);
        return embed;
    }

    /*
     * Create EntityA_Embed_MappedToOne
     */
    public void createEntityA_Embed_MappedToOne() {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        createEntityA_Embed_MappedToOne(em, ID);
        tran.begin();
        em.flush();
        tran.commit();
        em.close();
    }

    public void createEntityA_Embed_MappedToOne(EntityManager em, int id) {
        EntityA_Embed_MappedToOne a = new EntityA_Embed_MappedToOne();
        a.setId(id);
        a.setName("a" + id);
        a.setAge(id);
        Embed_MappedToOne embed = createEmbed_MappedToOne(em, id, a);
        a.setEmbed(embed);
        em.persist(a);
    }

    public Embed_MappedToOne createEmbed_MappedToOne(EntityManager em, int id,
        EntityA_Embed_MappedToOne a) {
        Embed_MappedToOne embed = new Embed_MappedToOne();
        embed.setName1("name1");
        embed.setName2("name2");
        embed.setName3("name3");
        EntityB1 b = new EntityB1();
        b.setId(id);
        b.setName("bm" + id);
        b.setEntityA(a);
        embed.setMappedEntityB(b);
        em.persist(b);
        return embed;
    }

    /*
     * Create EntityA_Embed_MappedToOneCascadeDelete
     */
    public void createEntityA_Embed_MappedToOneCascadeDelete() {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        createEntityA_Embed_MappedToOneCascadeDelete(em, ID);
        tran.begin();
        em.flush();
        tran.commit();
        em.close();
    }

    public void createEntityA_Embed_MappedToOneCascadeDelete(EntityManager em,
        int id) {
        EntityA_Embed_MappedToOneCascadeDelete a =
            new EntityA_Embed_MappedToOneCascadeDelete();
        a.setId(id);
        a.setName("a" + id);
        a.setAge(id);
        Embed_MappedToOneCascadeDelete embed =
            createEmbed_MappedToOneDeleteCascade(em, id, a);
        a.setEmbed(embed);
        em.persist(a);
    }

    public Embed_MappedToOneCascadeDelete createEmbed_MappedToOneDeleteCascade(
        EntityManager em, int id, EntityA_Embed_MappedToOneCascadeDelete a) {
        Embed_MappedToOneCascadeDelete embed =
            new Embed_MappedToOneCascadeDelete();
        embed.setName1("name1");
        embed.setName2("name2");
        embed.setName3("name3");
        EntityB2 b = new EntityB2();
        b.setId(id);
        b.setName("bm" + id);
        b.setEntityA(a);
        embed.setMappedEntityB(b);
        em.persist(b);
        return embed;
    }

    /*
     * Update EntityA_Embed_MappedToOneCascadeDelete
     */
    public void updateEntityA_Embed_MappedToOneCascadeDelete() {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        updateEntityA_Embed_MappedToOneCascadeDelete(em, ID);
        tran.begin();
        em.flush();
        tran.commit();
        em.clear();

        EntityA_Embed_MappedToOneCascadeDelete a =
            em.find(EntityA_Embed_MappedToOneCascadeDelete.class, ID);
        assertNotNull(a);

        EntityB2 b2 = em.find(EntityB2.class, ID);
        assertNotNull(b2);

        em.close();
    }

    public void updateEntityA_Embed_MappedToOneCascadeDelete(EntityManager em,
        int id) {
        EntityA_Embed_MappedToOneCascadeDelete a =
            em.find(EntityA_Embed_MappedToOneCascadeDelete.class, id);
        a.setName("newa" + id);
        a.setAge(id + 1);
        Embed_MappedToOneCascadeDelete embed =
            createEmbed_MappedToOneDeleteCascade(em, id+1, a);
        a.setEmbed(embed);
    }

    /*
     * Create EntityA_Coll_Embed_ToOne
     */
    public void createEntityA_Coll_Embed_ToOne() {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        createEntityA_Coll_Embed_ToOne(em, ID);
        tran.begin();
        em.flush();
        tran.commit();
        em.close();
    }

    public void createEntityA_Coll_Embed_ToOne(EntityManager em, int id) {
        EntityA_Coll_Embed_ToOne a = new EntityA_Coll_Embed_ToOne();
        a.setId(id);
        a.setName("a" + id);
        a.setAge(id);
        for (int i = 0; i < numEmbeddables; i++) {
            Embed_ToOne embed = createEmbed_ToOne(em, i+id);
            a.addEmbed1ToOne(embed);
        }
        em.persist(a);
    }

    /*
     * Create EntityA_Embed_ToMany
     */
    public void createEntityA_Embed_ToMany() {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        createEntityA_Embed_ToMany(em, ID);
        tran.begin();
        em.flush();
        tran.commit();
        em.close();
    }

    public void createEntityA_Embed_ToMany(EntityManager em, int id) {
        EntityA_Embed_ToMany a = new EntityA_Embed_ToMany();
        a.setId(id);
        a.setName("a" + id);
        a.setAge(id);
        Embed_ToMany embed = createEmbed_ToMany(em, id);
        a.setEmbed(embed);
        em.persist(a);
    }

    public Embed_ToMany createEmbed_ToMany(EntityManager em, int id) {
        Embed_ToMany embed = new Embed_ToMany();
        embed.setName1("name1");
        embed.setName2("name2");
        embed.setName3("name3");
        for (int i = 0; i < numEmbeddables; i++) {
            EntityB1 b = new EntityB1();
            b.setId(id + i);
            b.setName("b" + id + i);
            embed.addEntityB(b);
            em.persist(b);
        }
        return embed;
    }

   /*
     * Create EntityA_Embed_Embed_ToMany
     */
    public void createEntityA_Embed_Embed_ToMany() {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        createEntityA_Embed_Embed_ToMany(em, ID);
        tran.begin();
        em.flush();
        tran.commit();
        em.close();
    }

    public void createEntityA_Embed_Embed_ToMany(EntityManager em, int id) {
        EntityA_Embed_Embed_ToMany a = new EntityA_Embed_Embed_ToMany();
        a.setId(id);
        a.setName("a" + id);
        a.setAge(id);
        Embed_Embed_ToMany embed = createEmbed_Embed_ToMany(em, id);
        a.setEmbed(embed);
        em.persist(a);
    }

    public Embed_Embed_ToMany createEmbed_Embed_ToMany(EntityManager em,
            int id) {
        Embed_Embed_ToMany embed = new Embed_Embed_ToMany();
        embed.setIntVal1(1);
        embed.setIntVal2(2);
        embed.setIntVal3(3);
        Embed_ToMany embed_ToMany = createEmbed_ToMany(em, id);
        embed.setEmbed(embed_ToMany);
        return embed;
    }

    /*
     * Create EntityA_Embed_Coll_Integer
     */
    public void createEntityA_Embed_Coll_Integer() {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        createEntityA_Embed_Coll_Integer(em, ID);
        tran.begin();
        em.flush();
        tran.commit();
        em.close();
    }

    public void createEntityA_Embed_Coll_Integer(EntityManager em, int id) {
        EntityA_Embed_Coll_Integer a = new EntityA_Embed_Coll_Integer();
        a.setId(id);
        a.setName("a" + id);
        a.setAge(id);
        Embed_Coll_Integer embed = createEmbed_Coll_Integer(em, id);
        a.setEmbed(embed);
        em.persist(a);
    }

    public Embed_Coll_Integer createEmbed_Coll_Integer(EntityManager em,
            int id) {
        Embed_Coll_Integer embed = new Embed_Coll_Integer();
        embed.setIntVal1(id*10 + 1);
        embed.setIntVal2(id*10 + 2);
        embed.setIntVal3(id*10 + 3);
        for (int i = 0; i < numBasicTypes; i++) {
            embed.addOtherIntVal(id * 100 + i);
        }
        return embed;
    }

    /*
     * Create EntityA_Embed_Embed
     */
    public void createEntityA_Embed_Embed() {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        createEntityA_Embed_Embed(em, ID);
        tran.begin();
        em.flush();
        tran.commit();
        em.close();
    }

    public void createEntityA_Embed_Embed(EntityManager em, int id) {
        EntityA_Embed_Embed a = new EntityA_Embed_Embed();
        a.setId(id);
        a.setName("a" + id);
        a.setAge(id);
        Embed_Embed embed = createEmbed_Embed(id, 0);
        a.setEmbed(embed);
        em.persist(a);
    }

    public Embed_Embed createEmbed_Embed(int id, int idx) {
        Embed_Embed embed = new Embed_Embed();
        embed.setIntVal1(id * 100 + idx * 10 + 1);
        embed.setIntVal2(id * 100 + idx * 10 + 2);
        embed.setIntVal3(id * 100 + idx * 10 + 3);
        Embed embed1 = createEmbed(id, idx);
        embed.setEmbed(embed1);
        return embed;
    }

    public void createEntityA_Embed() {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        createEntityA_Embed(em, ID);
        tran.begin();
        em.flush();
        tran.commit();
        em.close();
    }

    public void createEntityA_Embed(EntityManager em, int id) {
        EntityA_Embed a = new EntityA_Embed();
        a.setId(id);
        a.setName("a" + id);
        a.setAge(id);
        Embed embed = createEmbed(id, 0);
        a.setEmbed(embed);
        em.persist(a);
    }

    public Embed createEmbed(int id, int idx) {
        Embed embed = new Embed();
        embed.setIntVal1(id * 100 + idx * 10 + 4);
        embed.setIntVal2(id * 100 + idx * 10 + 5);
        embed.setIntVal3(id * 100 + idx * 10 + 6);
        return embed;
    }

    /*
     * Create EntityA_Coll_Embed_Embed
     */
    public void createEntityA_Coll_Embed_Embed() {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        createEntityA_Coll_Embed_Embed(em, ID);
        tran.begin();
        em.flush();
        tran.commit();
        em.close();
    }

    public void createEntityA_Coll_Embed_Embed(EntityManager em, int id) {
        EntityA_Coll_Embed_Embed a = new EntityA_Coll_Embed_Embed();
        a.setId(id);
        a.setName("a" + id);
        a.setAge(id);
        for (int i = 0; i < numEmbeddables; i++) {
            Embed_Embed embed = createEmbed_Embed(id, i);
            a.addEmbed(embed);
        }
        em.persist(a);
    }

    /*
     * Create EntityA_Embed_Coll_Embed
     */
    public void createEntityA_Embed_Coll_Embed() {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        createEntityA_Embed_Coll_Embed(em, ID);
        tran.begin();
        em.flush();
        tran.commit();
        em.close();
    }

    public void createEntityA_Embed_Coll_Embed(EntityManager em, int id) {
        EntityA_Embed_Coll_Embed a = new EntityA_Embed_Coll_Embed();
        a.setId(id);
        a.setName("a" + id);
        a.setAge(id);
        Embed_Coll_Embed embed = createEmbed_Coll_Embed(em, id);
        a.setEmbed(embed);
        em.persist(a);
    }

    public Embed_Coll_Embed createEmbed_Coll_Embed(EntityManager em, int id) {
        Embed_Coll_Embed embed = new Embed_Coll_Embed();
        embed.setIntVal1(id * 10 + 1);
        embed.setIntVal2(id * 10 + 2);
        embed.setIntVal3(id * 10 + 3);
        for (int i = 0; i < numEmbeddables; i++) {
            Embed embed1 = createEmbed(id, i);
            embed.addEmbed(embed1);
        }
        return embed;
    }

    /*
     * Create Employee
     */
    public void createEmployeeObj() {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        createPhoneNumbers(em);
        createEmployees(em);
        createProgramManagers(em);

        tran.begin();
        em.flush();
        tran.commit();
        em.close();
    }

    public void createProgramManagers(EntityManager em) {
        empId = 1;
        for (int i = 0; i < numProgramManagers; i++)
            createProgramManager(em, pmId++);
    }

    public void createProgramManager(EntityManager em, int id) {
        ProgramManager pm = new ProgramManager();
        pm.setId(id);
        for (int i = 0; i < numEmployeesPerProgramManager; i++) {
            Employee e = employees.get(empId++);
            pm.addManage(e);
            JobInfo jobInfo = new JobInfo();
            jobInfo.setJobDescription("jobDescription" + e.getEmpId());
            jobInfo.setProgramManager(pm);
            e.setJobInfo(jobInfo);
        }
        em.persist(pm);
    }

    public void createEmployees(EntityManager em) {
        phoneId = 1;
        for (int i = 0; i < numEmployees; i++) {
            Employee e = createEmployee(em, empId++);
            employees.put(e.getEmpId(), e);
        }
    }

    public Employee createEmployee(EntityManager em, int id) {
        Employee e = new Employee();
        e.setEmpId(id);
        ContactInfo contactInfo = new ContactInfo();
        for (int i = 0; i < numPhoneNumbersPerEmployee; i++) {
            PhoneNumber phoneNumber = phones.get(phoneId++);
            contactInfo.addPhoneNumber(phoneNumber);
            e.setContactInfo(contactInfo);
            phoneNumber.addEmployees(e);
            em.persist(phoneNumber);
        }
        ParkingSpot parkingSpot = createParkingSpot(em, parkingSpotId++);
        LocationDetails location = new LocationDetails();
        location.setOfficeNumber(id);
        location.setParkingSpot(parkingSpot);
        e.setLocationDetails(location);
        parkingSpot.setAssignedTo(e);
        for (int i = 0; i < numNickNames; i++)
            e.addNickName("nickName" + id + i);
        em.persist(parkingSpot);
        em.persist(e);
        return e;
    }

    public void createPhoneNumbers(EntityManager em) {
        for (int i = 0; i < numPhoneNumbers; i++) {
            PhoneNumber p = new PhoneNumber();
            p.setNumber(phoneId++);
            phones.put(p.getNumber(), p);
            em.persist(p);
        }
    }

    public ParkingSpot createParkingSpot(EntityManager em, int id) {
        ParkingSpot p = new ParkingSpot();
        p.setId(id);
        p.setGarage("garage" + id);
        em.persist(p);
        return p;
    }

    public void findEmployeeObj() {
        EntityManager em = emf.createEntityManager();
        ProgramManager pm = em.find(ProgramManager.class, 1);
        assertProgramManager(pm);

        pm = em.find(ProgramManager.class, 2);
        assertProgramManager(pm);

        Employee e = em.find(Employee.class, 1);
        assertEmployee(e);

        PhoneNumber p = em.find(PhoneNumber.class, 1);
        assertPhoneNumber(p);

        ParkingSpot ps = em.find(ParkingSpot.class, 1);
        assertParkingSpot(ps);

        em.close();
    }

    public void queryEmployeeObj() {
        queryProgramManager(emf);
        queryEmployeeObj(emf);
        queryPhoneNumber(emf);
        queryParkingSpot(emf);
    }

    public void queryParkingSpot(EntityManagerFactory emf) {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        tran.begin();
        Query q = em.createQuery("select p from ParkingSpot p");
        List<ParkingSpot> ps = q.getResultList();
        for (ParkingSpot p : ps){
            assertParkingSpot(p);
        }
        tran.commit();
        em.close();
    }

    public void queryProgramManager(EntityManagerFactory emf) {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        tran.begin();
        Query q = em.createQuery("select pm from ProgramManager pm");
        List<ProgramManager> pms = q.getResultList();
        for (ProgramManager pm : pms){
            assertProgramManager(pm);
        }
        tran.commit();
        em.close();
    }

    public void queryPhoneNumber(EntityManagerFactory emf) {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        tran.begin();
        Query q = em.createQuery("select p from PhoneNumber p");
        List<PhoneNumber> ps = q.getResultList();
        for (PhoneNumber p : ps){
            assertPhoneNumber(p);
        }
        tran.commit();
        em.close();
    }

    public void queryEmployeeObj(EntityManagerFactory emf) {
        EntityManager em = emf.createEntityManager();
        EntityTransaction tran = em.getTransaction();
        tran.begin();
        Query q = em.createQuery("select e from Employee e");
        List<Employee> es = q.getResultList();
        for (Employee e : es){
            assertEmployee(e);
        }
        tran.commit();
        em.clear();
        // test range variable over element collection
        String[] query = {
            "select e from Employee e, in (e.nickNames) n " +
                " where n like '%1'",
        };
        for (String s : query) {
            es = em.createQuery(s).getResultList();
            for (Employee e : es) {
                assertEmployee(e);
            }
        }
        em.close();
    }

    public void assertProgramManager(ProgramManager pm) {
        int id = pm.getId();
        Collection<Employee> es = pm.getManages();
        assertEquals(numEmployeesPerProgramManager, es.size());
        for (Employee e : es) {
            assertEmployee(e);
        }
    }

    public void assertEmployee(Employee e) {
        int id = e.getEmpId();
        ContactInfo c = e.getContactInfo();
        List<PhoneNumber> phones = c.getPhoneNumbers();
        assertEquals(numPhoneNumbersPerEmployee, phones.size());
        for (PhoneNumber p : phones) {
            assertPhoneNumber(p);
        }

        LocationDetails loc = e.getLocationDetails();
        int officeNumber = loc.getOfficeNumber();
        ParkingSpot p = loc.getParkingSpot();
        assertParkingSpot(p);
        ProgramManager pm = e.getJobInfo().getProgramManager();
        Set<String> nickNames = e.getNickNames();
        assertEquals(numNickNames, nickNames.size());

    }

    public void assertPhoneNumber(PhoneNumber p) {
        int number = p.getNumber();
        Collection<Employee> es = p.getEmployees();
        assertEquals(numEmployeesPerPhoneNumber, es.size());
    }

    public void assertParkingSpot(ParkingSpot p) {
        String garage = p.getGarage();
        Employee e = p.getAssignedTo();
    }

    /*
     * Find EntityA_Coll_String
     */
    public void findEntityA_Coll_String() {
        EntityManager em = emf.createEntityManager();
        EntityA_Coll_String a = em.find(EntityA_Coll_String.class, ID);
        checkEntityA_Coll_String(a);
        em.close();
    }

    /*
     * Find EntityA_Embed_ToOne
     */
    public void findEntityA_Embed_ToOne() {
        EntityManager em = emf.createEntityManager();
        EntityA_Embed_ToOne a = em.find(EntityA_Embed_ToOne.class, ID);
        checkEntityA_Embed_ToOne(a);
        em.getTransaction().begin();
        em.remove(a);
        em.getTransaction().commit();
        //delete cascade
        EntityB1 b = em.find(EntityB1.class, ID);
        assertNull(b);
        em.close();
    }

    public void testLazyFetchEmbed() {
        EntityManager em = emf.createEntityManager();
        EntityA_Embed a = new EntityA_Embed();
        a.setId(ID);
        a.setName("name");
        a.setAge(1);
