/*******************************************************************************
 * Copyright (c) 2010-2012 Oracle. 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:
 *  dclarke - EclipseLink 2.3 - MySports Demo Bug 344608
 ******************************************************************************/
package example.mysports.tests.model;

import java.util.Map;
import java.util.Vector;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

import junit.framework.Assert;

import org.eclipse.persistence.config.PersistenceUnitProperties;
import org.eclipse.persistence.config.SessionCustomizer;
import org.eclipse.persistence.descriptors.ClassDescriptor;
import org.eclipse.persistence.exceptions.DatabaseException;
import org.eclipse.persistence.exceptions.IntegrityChecker;
import org.eclipse.persistence.internal.sessions.ArrayRecord;
import org.eclipse.persistence.jpa.JpaHelper;
import org.eclipse.persistence.sessions.Session;
import org.eclipse.persistence.sessions.server.Server;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import example.mysports.MySportsConfig;
import example.mysports.admin.model.HostedLeague;
import example.mysports.ejb.LeagueRepository;
import example.mysports.model.Extensible;
import example.mysports.tests.TestingLeagueRepository;
import example.mysports.tests.admin.MockAdminServerConnector;

/**
 * Create Schema and verify using integrity checker.
 * 
 * @author dclarke
 * @since EclipseLink 2.3.0
 */
public class CreateDatabase {

    private static LeagueRepository repository;

    /**
     * Using EclipseLink's schema generation when the
     * {@link EntityManagerFactory} is created to drop and create the tables and
     * sequences.
     */
    @Test
    public void createSharedMySportsSchema() throws Exception {
        Map<String, Object> properties = TestingLeagueRepository.get("ALL");

        properties.put(PersistenceUnitProperties.DDL_GENERATION, PersistenceUnitProperties.DROP_AND_CREATE);
        properties.put(PersistenceUnitProperties.DDL_GENERATION_MODE, PersistenceUnitProperties.DDL_DATABASE_GENERATION);
        properties.put(PersistenceUnitProperties.ALLOW_NATIVE_SQL_QUERIES, "true");

        repository.setLeagueId("ALL", properties);

        Server session = repository.unwrap(Server.class);

        for (ClassDescriptor desc : session.getDescriptors().values()) {
            Object result = session.executeSQL("SELECT COUNT(*) FROM " + desc.getTableName());
            Assert.assertNotNull(result);
            @SuppressWarnings("unchecked")
            ArrayRecord record = (ArrayRecord) ((Vector<Object>) result).get(0);
            Assert.assertEquals(0, ((Number) record.getValues().get(0)).intValue());
            
            if (Extensible.class.isAssignableFrom(desc.getJavaClass())) {
                Assert.assertNotNull(desc.getMappingForAttributeName("flex1"));
                session.executeSQL("SELECT flex_1 FROM " + desc.getTableName());
                Assert.assertNotNull(desc.getMappingForAttributeName("flex2"));
                session.executeSQL("SELECT flex_2 FROM " + desc.getTableName());
                Assert.assertNotNull(desc.getMappingForAttributeName("flex3"));
                session.executeSQL("SELECT flex_3 FROM " + desc.getTableName());
                Assert.assertNotNull(desc.getMappingForAttributeName("flex4"));
                session.executeSQL("SELECT flex_4 FROM " + desc.getTableName());
                Assert.assertNotNull(desc.getMappingForAttributeName("flex5"));
                session.executeSQL("SELECT flex_5 FROM " + desc.getTableName());
            }
        }
    }

    //@Test
    public void createMHLMySportsSchema() throws Exception {
        Map<String, Object> properties = TestingLeagueRepository.get("MHL");

        properties.put(PersistenceUnitProperties.DDL_GENERATION, PersistenceUnitProperties.DROP_AND_CREATE);
        properties.put(PersistenceUnitProperties.DDL_GENERATION_MODE, PersistenceUnitProperties.DDL_DATABASE_GENERATION);
        properties.put(PersistenceUnitProperties.ALLOW_NATIVE_SQL_QUERIES, "true");

        repository.setLeagueId("MHL", properties);

        Server session = repository.unwrap(Server.class);

        for (ClassDescriptor desc : session.getDescriptors().values()) {
            Object result = session.executeSQL("SELECT COUNT(*) FROM " + desc.getTableName());
            Assert.assertNotNull(result);
            @SuppressWarnings("unchecked")
            ArrayRecord record = (ArrayRecord) ((Vector<Object>) result).get(0);
            Assert.assertEquals(0, ((Number) record.getValues().get(0)).intValue());
        }
    }

    @Test
    public void createKFLMySportsSchema() throws Exception {
        Map<String, Object> properties = TestingLeagueRepository.get("KFL");
        properties.put(PersistenceUnitProperties.ALLOW_NATIVE_SQL_QUERIES, "true");
        repository.setLeagueId("KFL", properties);

        Server session = repository.unwrap(Server.class);

        try {
            session.executeNonSelectingSQL("DROP TABLE mys_player_kfl");
        } catch (DatabaseException dbe) {
        }
        try {
            session.executeNonSelectingSQL("DROP TABLE mys_team_kfl");
        } catch (DatabaseException dbe) {
        }
        try {
            session.executeNonSelectingSQL("DROP TABLE mys_div_kfl");
        } catch (DatabaseException dbe) {
        }

        session.executeNonSelectingSQL("CREATE TABLE mys_div_kfl (ID INTEGER NOT NULL, DEF_DIV TINYINT(1) default 0, NAME VARCHAR(255), flex_1 VARCHAR(255), flex_2 VARCHAR(255), flex_3 VARCHAR(255), flex_4 VARCHAR(255), flex_5 VARCHAR(255), VERSION BIGINT, PRIMARY KEY (ID))");
        session.executeNonSelectingSQL("CREATE TABLE mys_player_kfl (ID INTEGER NOT NULL, EMAIL VARCHAR(255), F_NAME VARCHAR(255), L_NAME VARCHAR(255), NUM INTEGER, flex_1 VARCHAR(255), flex_2 VARCHAR(255), flex_3 VARCHAR(255), flex_4 VARCHAR(255), flex_5 VARCHAR(255), USER_ID VARCHAR(255), VERSION BIGINT, TEAM_ID INTEGER, PRIMARY KEY (ID))");
        session.executeNonSelectingSQL("CREATE TABLE mys_team_kfl (ID INTEGER NOT NULL, NAME VARCHAR(255), flex_1 VARCHAR(255), flex_2 VARCHAR(255), flex_3 VARCHAR(255), flex_4 VARCHAR(255), flex_5 VARCHAR(255), VERSION BIGINT, DIVISION_ID INTEGER, PRIMARY KEY (ID))");

        for (ClassDescriptor desc : session.getDescriptors().values()) {
            Object result = session.executeSQL("SELECT COUNT(*) FROM " + desc.getTableName());
            Assert.assertNotNull(result);
            @SuppressWarnings("unchecked")
            ArrayRecord record = (ArrayRecord) ((Vector<Object>) result).get(0);
            Assert.assertEquals(0, ((Number) record.getValues().get(0)).intValue());
        }
    }

    /**
     * Verify the schema using the integrity checker to compare database
     * structure to that expected in the mappings.
     */
    @Test
    public void verifySharedMySportsSchema() {
        Map<String, Object> properties = TestingLeagueRepository.get();

        properties.put(PersistenceUnitProperties.SESSION_CUSTOMIZER, EnableIntegrityChecker.class.getName());
        properties.put(PersistenceUnitProperties.DEPLOY_ON_STARTUP, Boolean.TRUE.toString());
        properties.put(PersistenceUnitProperties.MULTITENANT_SHARED_EMF, Boolean.TRUE.toString());
        EntityManagerFactory emf = null;

        try {
            emf = Persistence.createEntityManagerFactory(MySportsConfig.PU_NAME, properties);

            IntegrityChecker ic = JpaHelper.getServerSession(emf).getIntegrityChecker();
            Assert.assertNotNull(ic);
            Assert.assertTrue(ic.getCaughtExceptions().isEmpty());
        } finally {
            if (emf != null) {
                emf.close();
            }
        }
    }

    @BeforeClass
    public static void createLeagueRepository() {
        EntityManagerFactory adminEMF = Persistence.createEntityManagerFactory("MySportsAdmin", TestingLeagueRepository.get());

        EntityManager em = adminEMF.createEntityManager();
        em.getTransaction().begin();
        // Create default shared league for purposes of schema gen
        HostedLeague allLeague = em.find(HostedLeague.class, "ALL");
        if (allLeague == null) {
            allLeague = new HostedLeague("ALL", "All League", "red");
            allLeague.addPlayerExtension("flex1", "java.lang.String", "flex_1", "flex1/text()");
            allLeague.addPlayerExtension("flex2", "java.lang.String", "flex_2", "flex1/text()");
            allLeague.addPlayerExtension("flex3", "java.lang.String", "flex_3", "flex1/text()");
            allLeague.addPlayerExtension("flex4", "java.lang.String", "flex_4", "flex1/text()");
            allLeague.addPlayerExtension("flex5", "java.lang.String", "flex_5", "flex1/text()");

            allLeague.addTeamExtension("flex1", "java.lang.String", "flex_1", "flex1/text()");
            allLeague.addTeamExtension("flex2", "java.lang.String", "flex_2", "flex1/text()");
            allLeague.addTeamExtension("flex3", "java.lang.String", "flex_3", "flex1/text()");
            allLeague.addTeamExtension("flex4", "java.lang.String", "flex_4", "flex1/text()");
            allLeague.addTeamExtension("flex5", "java.lang.String", "flex_5", "flex1/text()");

            allLeague.addDivisionExtension("flex1", "java.lang.String", "flex_1", "flex1/text()");
            allLeague.addDivisionExtension("flex2", "java.lang.String", "flex_2", "flex1/text()");
            allLeague.addDivisionExtension("flex3", "java.lang.String", "flex_3", "flex1/text()");
            allLeague.addDivisionExtension("flex4", "java.lang.String", "flex_4", "flex1/text()");
            allLeague.addDivisionExtension("flex5", "java.lang.String", "flex_5", "flex1/text()");

            em.persist(allLeague);
        }
        em.getTransaction().commit();

        MySportsConfig config = new MySportsConfig();
        repository = new TestingLeagueRepository(config);
        ((MockAdminServerConnector) config.getAdminConnector()).setEMF(adminEMF);
    }

    @AfterClass
    public static void closeLeagueRepository() {
        if (repository != null) {
            MySportsConfig config = repository.getConfig();

            EntityManagerFactory adminEMF = ((MockAdminServerConnector) config.getAdminConnector()).getEMF();
            EntityManager em = adminEMF.createEntityManager();
            em.getTransaction().begin();
            HostedLeague allLeague = em.find(HostedLeague.class, "ALL");
            if (allLeague != null) {
                em.remove(allLeague);
            }
            em.getTransaction().commit();
            em.close();
            adminEMF.close();

            repository.close();
        }
    }

    /**
     * {@link SessionCustomizer} that enables the {@link IntegrityChecker}.
     * 
     * NOTE: If another {@link SessionCustomizer} is specified in the
     * persistence.xml this one will replace it.
     */
    public static class EnableIntegrityChecker implements SessionCustomizer {

        public void customize(Session session) throws Exception {
            IntegrityChecker ic = new IntegrityChecker();
            ic.setShouldCheckDatabase(true);
            session.setIntegrityChecker(ic);
        }

    }

}