/*******************************************************************************
 * Copyright (c) 2005, 2009 SAP. 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:
 *     SAP - initial API and implementation
 ******************************************************************************/

package org.eclipse.persistence.testing.tests.wdf.jpa1.query;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.persistence.TransactionRequiredException;

import org.eclipse.persistence.config.PersistenceUnitProperties;
import org.eclipse.persistence.testing.framework.wdf.Bugzilla;
import org.eclipse.persistence.testing.framework.wdf.JPAEnvironment;
import org.eclipse.persistence.testing.models.wdf.jpa1.employee.Department;
import org.eclipse.persistence.testing.models.wdf.jpa1.employee.Employee;
import org.eclipse.persistence.testing.models.wdf.jpa1.employee.Project;
import org.eclipse.persistence.testing.tests.wdf.jpa1.JPA1Base;
import org.junit.Test;

public class TestDeleteQuery extends JPA1Base {

    private final Set<Department> ALL_DEPARTMENTS = new HashSet<Department>();
    private final Department dep10 = new Department(10, "ten");
    private final Department dep20 = new Department(20, "twenty");

    private void init() throws SQLException {
        clearAllTables();
        ALL_DEPARTMENTS.add(dep10);
        ALL_DEPARTMENTS.add(dep20);
        JPAEnvironment env = getEnvironment();
        EntityManager em = env.getEntityManager();
        try {
            env.beginTransaction(em);
            Employee employee = new Employee(1, "Hugo", "Maier", dep10);
            em.persist(dep10);
            em.persist(dep20);
            em.persist(employee);
            env.commitTransactionAndClear(em);
        } finally {
            closeEntityManager(em);
        }
    }

    private void verifyCountOnDatabase(int expected, String table) throws SQLException {
        Connection conn = getEnvironment().getDataSource().getConnection();
        try {
            PreparedStatement stmt = conn.prepareStatement("select COUNT(*) from " + table);
            try {
                ResultSet rs = stmt.executeQuery();
                try {
                    verify(rs.next(), "no count ?!");
                    int count = rs.getInt(1);
                    verify(count == expected, "unexpected count: " + count + " on table >>" + table + "<<, expected: "
                            + expected);
                } finally {
                    rs.close();
                }
            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }
    }

    @Test
    public void testDeleteOutsideTx() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            Query query = em.createQuery("delete from Department d");
            try {
                query.executeUpdate();
                flop("missing TransactionRequiredException");
            } catch (TransactionRequiredException ex) {
            }
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testDeleteWithSecondaryTable() {

        Project project1 = new Project();
        project1.setName("foo");
        project1.setPlannedDays(1);
        project1.setUsedDays(53);

        Project project2 = new Project();
        project2.setName("bar");
        project2.setPlannedDays(100);
        project2.setUsedDays(410);

        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            em.persist(project1);
            em.persist(project2);
            em.flush();
            getEnvironment().commitTransaction(em);
            verifyCountOnDatabase(2, "TMP_PROJECT");
            verifyCountOnDatabase(2, "TMP_PROJECT_DETAILS");

            getEnvironment().beginTransaction(em);
            Query deleteQuery = em.createQuery("DELETE FROM Project p WHERE p.id IS NOT NULL");
            deleteQuery.executeUpdate();
            em.flush();
            getEnvironment().commitTransaction(em);

            verifyCountOnDatabase(0, "TMP_PROJECT");
            verifyCountOnDatabase(0, "TMP_PROJECT_DETAILS");
        } catch (SQLException sqlEx) {
            throw new RuntimeException(sqlEx);
        } finally {
            closeEntityManager(em);
        }

    }

    @Test
    @Bugzilla(bugid=338783, databaseNames={"org.eclipse.persistence.platform.database.oracle.Oracle10Platform", 
                                           "org.eclipse.persistence.platform.database.oracle.Oracle11Platform"})
    public void testDeleteAllDepartments() throws SQLException {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            init();
            getEnvironment().beginTransaction(em);
            // OS/390 doesn't get the update count right (returns -1), if there is no where clause.
            // Workaround is to supply a dummy where clause.
            Query emplyoeeDelete = em.createQuery("delete from Employee e");
            emplyoeeDelete.executeUpdate(); // del emp before dep caused by FK
            Query query = em.createQuery("delete from Department d where d.id is not null");
            int count = query.executeUpdate();
			if (!"JDBC".equals(getEnvironment().getPropertyValue(em, PersistenceUnitProperties.BATCH_WRITING))) {
				verify(count == 2, "wrong update count: " + count);
			}
            getEnvironment().commitTransaction(em);
            verifyCountOnDatabase(0, "TMP_DEP");
            init();
            getEnvironment().beginTransaction(em);
            emplyoeeDelete = em.createQuery("delete from Employee e");
            emplyoeeDelete.executeUpdate();
            query = em.createQuery("delete from Department d where d.id = 10");
            count = query.executeUpdate();
			if (!"JDBC".equals(getEnvironment().getPropertyValue(em, PersistenceUnitProperties.BATCH_WRITING))) {
				verify(count == 1, "wrong update count: " + count);
			}
            getEnvironment().commitTransaction(em);
            verifyCountOnDatabase(1, "TMP_DEP");
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testDeleteEmployeeWithGivenDepartment() throws SQLException {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            init();
            Department department = em.find(Department.class, Integer.valueOf(10));
            verifyDeleteEmployeeWithGivenDepartment(em, department);
            em.clear();
            init();
            verifyDeleteEmployeeWithGivenDepartment(em, dep10);
            em.clear();
        } finally {
            closeEntityManager(em);
        }
    }

    private void verifyDeleteEmployeeWithGivenDepartment(EntityManager em, Department dep) {
        getEnvironment().beginTransaction(em);
        Query query = em.createQuery("delete from Employee e where e.department = ?1");
        query.setParameter(1, dep);
        int count = query.executeUpdate();
        verify(count == 1, "wrong update count: " + count);
        getEnvironment().commitTransaction(em);
		getEnvironment().evict(em, Employee.class);
        verify(null == em.find(Employee.class, Integer.valueOf(1)), "employee found");
    }

    @Test
    public void testDeleteAllDepartmentsNative() throws SQLException {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            init();
            getEnvironment().beginTransaction(em);
            // OS/390 doesn't get the update count right (returns -1), if there is no where clause.
            // Workaround is to supply a dummy where clause.
            Query emplyoeeDelete = em.createQuery("delete from Employee e");
            emplyoeeDelete.executeUpdate(); // del emp before dep caused by FK
            Query query = em.createNativeQuery("delete from TMP_DEP where ID is not null");
            int count = query.executeUpdate();
            
			if (!"JDBC".equals(getEnvironment().getPropertyValue(em, PersistenceUnitProperties.BATCH_WRITING))) {
				verify(count == 2, "wrong update count: " + count);
			}
            getEnvironment().commitTransaction(em);
            verifyCountOnDatabase(0, "TMP_DEP");
            init();
            getEnvironment().beginTransaction(em);
            emplyoeeDelete = em.createQuery("delete from Employee e");
            emplyoeeDelete.executeUpdate();
            query = em.createNativeQuery("delete from TMP_DEP where TMP_DEP.ID = 10");
            count = query.executeUpdate();
			if (!"JDBC".equals(getEnvironment().getPropertyValue(em, PersistenceUnitProperties.BATCH_WRITING))) {
				verify(count == 1, "wrong update count: " + count);
			}
            getEnvironment().commitTransaction(em);
            verifyCountOnDatabase(1, "TMP_DEP");
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testExecuteUpdateSelect() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Query query = em.createQuery("select d from Department d");
            try {
                query.executeUpdate();
                flop("missing IllegalStateException");
            } catch (IllegalStateException ex) {
            } finally {
                getEnvironment().rollbackTransaction(em);
            }
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testGetResultListDelete() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Query query = em.createQuery("delete from Department d");
            try {
                query.getResultList();
                flop("missing IllegalStateException");
            } catch (IllegalStateException ex) {
            } finally {
                getEnvironment().rollbackTransaction(em);
            }
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testGetSingleResultDelete() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Query query = em.createQuery("delete from Department d");
            try {
                query.getResultList();
                flop("missing IllegalStateException");
            } catch (IllegalStateException ex) {
            } finally {
                getEnvironment().rollbackTransaction(em);
            }
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testUpdateDepartment() throws SQLException {
        clearAllTables();
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Department department = new Department(1, "hugo");
            em.persist(department);
            getEnvironment().commitTransactionAndClear(em);
            getEnvironment().beginTransaction(em);
            Query query = em.createQuery("update Department d set d.name = ?1 where d.id = ?2");
            query.setParameter(1, "emil");
            query.setParameter(2, Integer.valueOf(1));
            query.executeUpdate();
            getEnvironment().commitTransactionAndClear(em);
            Department found = em.find(Department.class, Integer.valueOf(1));
            verify("emil".equals(found.getName()), "wrong name: " + found.getName());
        } finally {
            closeEntityManager(em);
        }
    }
}