/*
 * Copyright 2002-2008 the original author or authors.
 *
 * Licensed 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.springframework.samples.petclinic.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jmx.export.annotation.ManagedOperation;
import org.springframework.jmx.export.annotation.ManagedResource;
import org.springframework.orm.ObjectRetrievalFailureException;
import org.springframework.samples.petclinic.Clinic;
import org.springframework.samples.petclinic.Owner;
import org.springframework.samples.petclinic.Pet;
import org.springframework.samples.petclinic.PetType;
import org.springframework.samples.petclinic.Specialty;
import org.springframework.samples.petclinic.Vet;
import org.springframework.samples.petclinic.Visit;
import org.springframework.samples.petclinic.util.EntityUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * A simple JDBC-based implementation of the {@link Clinic} interface.
 *
 * <p>This class uses Java 5 language features and the {@link SimpleJdbcTemplate}
 * plus {@link SimpleJdbcInsert}. It also takes advantage of classes like
 * {@link BeanPropertySqlParameterSource} and
 * {@link ParameterizedBeanPropertyRowMapper} which provide automatic mapping
 * between JavaBean properties and JDBC parameters or query results.
 *
 * <p>SimpleJdbcClinic is a rewrite of the AbstractJdbcClinic which was the base
 * class for JDBC implementations of the Clinic interface for Spring 2.0.
 *
 * @author Ken Krebs
 * @author Juergen Hoeller
 * @author Rob Harrop
 * @author Sam Brannen
 * @author Thomas Risberg
 * @author Mark Fisher
 */
@Service
@ManagedResource("petclinic:type=Clinic")
public class SimpleJdbcClinic implements Clinic, SimpleJdbcClinicMBean {

    private final Log logger = LogFactory.getLog(getClass());

    private SimpleJdbcTemplate simpleJdbcTemplate;

    private SimpleJdbcInsert insertOwner;
    private SimpleJdbcInsert insertPet;
    private SimpleJdbcInsert insertVisit;

    private final List<Vet> vets = new ArrayList<Vet>();


    @Autowired
    public void init(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);

        this.insertOwner = new SimpleJdbcInsert(dataSource)
            .withTableName("owners")
            .usingGeneratedKeyColumns("id");
        this.insertPet = new SimpleJdbcInsert(dataSource)
            .withTableName("pets")
            .usingGeneratedKeyColumns("id");
        this.insertVisit = new SimpleJdbcInsert(dataSource)
            .withTableName("visits")
            .usingGeneratedKeyColumns("id");
    }


    /**
     * Refresh the cache of Vets that the Clinic is holding.
     * @see org.springframework.samples.petclinic.Clinic#getVets()
     */
    @ManagedOperation
    @Transactional(readOnly = true)
    public void refreshVetsCache() throws DataAccessException {
        synchronized (this.vets) {
            this.logger.info("Refreshing vets cache");

            // Retrieve the list of all vets.
            this.vets.clear();
            this.vets.addAll(this.simpleJdbcTemplate.query(
                    "SELECT id, first_name, last_name FROM vets ORDER BY last_name,first_name",
                    ParameterizedBeanPropertyRowMapper.newInstance(Vet.class)));

            // Retrieve the list of all possible specialties.
            final List<Specialty> specialties = this.simpleJdbcTemplate.query(
                    "SELECT id, name FROM specialties",
                    ParameterizedBeanPropertyRowMapper.newInstance(Specialty.class));

            // Build each vet's list of specialties.
            for (Vet vet : this.vets) {
                final List<Integer> vetSpecialtiesIds = this.simpleJdbcTemplate.query(
                        "SELECT specialty_id FROM vet_specialties WHERE vet_id=?",
                        new ParameterizedRowMapper<Integer>() {
                            public Integer mapRow(ResultSet rs, int row) throws SQLException {
                                return Integer.valueOf(rs.getInt(1));
                            }},
                        vet.getId().intValue());
                for (int specialtyId : vetSpecialtiesIds) {
                    Specialty specialty = EntityUtils.getById(specialties, Specialty.class, specialtyId);
                    vet.addSpecialty(specialty);
                }
            }
        }
    }


    // START of Clinic implementation section *******************************

    @Transactional(readOnly = true)
    public Collection<Vet> getVets() throws DataAccessException {
        synchronized (this.vets) {
            if (this.vets.isEmpty()) {
                refreshVetsCache();
            }
            return this.vets;
        }
    }

    @Transactional(readOnly = true)
    public Collection<PetType> getPetTypes() throws DataAccessException {
        return this.simpleJdbcTemplate.query(
                "SELECT id, name FROM types ORDER BY name",
                ParameterizedBeanPropertyRowMapper.newInstance(PetType.class));
    }

    /**
     * Loads {@link Owner Owners} from the data store by last name, returning
     * all owners whose last name <i>starts</i> with the given name; also loads
     * the {@link Pet Pets} and {@link Visit Visits} for the corresponding
     * owners, if not already loaded.
     */
    @Transactional(readOnly = true)
    public Collection<Owner> findOwners(String lastName) throws DataAccessException {
        List<Owner> owners = this.simpleJdbcTemplate.query(
                "SELECT id, first_name, last_name, address, city, telephone FROM owners WHERE last_name like ?",
                ParameterizedBeanPropertyRowMapper.newInstance(Owner.class),
                lastName + "%");
        loadOwnersPetsAndVisits(owners);
        return owners;
    }

    /**
     * Loads the {@link Owner} with the supplied {@code id}; also loads
     * the {@link Pet Pets} and {@link Visit Visits} for the corresponding
     * owner, if not already loaded.
     */
    @Transactional(readOnly = true)
    public Owner loadOwner(int id) throws DataAccessException {
        Owner owner;
        try {
            owner = this.simpleJdbcTemplate.queryForObject(
                    "SELECT id, first_name, last_name, address, city, telephone FROM owners WHERE id=?",
                    ParameterizedBeanPropertyRowMapper.newInstance(Owner.class),
                    id);
        }
        catch (EmptyResultDataAccessException ex) {
            throw new ObjectRetrievalFailureException(Owner.class, new Integer(id));
        }
        loadPetsAndVisits(owner);
        return owner;
    }

    @Transactional(readOnly = true)
    public Pet loadPet(int id) throws DataAccessException {
        JdbcPet pet;
        try {
            pet = this.simpleJdbcTemplate.queryForObject(
                    "SELECT id, name, birth_date, type_id, owner_id FROM pets WHERE id=?",
                    new JdbcPetRowMapper(),
                    id);
        }
        catch (EmptyResultDataAccessException ex) {
            throw new ObjectRetrievalFailureException(Pet.class, new Integer(id));
        }
        Owner owner = loadOwner(pet.getOwnerId());
        owner.addPet(pet);
        pet.setType(EntityUtils.getById(getPetTypes(), PetType.class, pet.getTypeId()));
        loadVisits(pet);
        return pet;
    }

    @Transactional
    public void storeOwner(Owner owner) throws DataAccessException {
        if (owner.isNew()) {
            Number newKey = this.insertOwner.executeAndReturnKey(
                    new BeanPropertySqlParameterSource(owner));
            owner.setId(newKey.intValue());
        }
        else {
            this.simpleJdbcTemplate.update(
                    "UPDATE owners SET first_name=:firstName, last_name=:lastName, address=:address, " +
                    "city=:city, telephone=:telephone WHERE id=:id",
                    new BeanPropertySqlParameterSource(owner));
        }
    }

    @Transactional
    public void storePet(Pet pet) throws DataAccessException {
        if (pet.isNew()) {
            Number newKey = this.insertPet.executeAndReturnKey(
                    createPetParameterSource(pet));
            pet.setId(newKey.intValue());
        }
        else {
            this.simpleJdbcTemplate.update(
                    "UPDATE pets SET name=:name, birth_date=:birth_date, type_id=:type_id, " +
                    "owner_id=:owner_id WHERE id=:id",
                    createPetParameterSource(pet));
        }
    }

    @Transactional
    public void storeVisit(Visit visit) throws DataAccessException {
        if (visit.isNew()) {
            Number newKey = this.insertVisit.executeAndReturnKey(
                    createVisitParameterSource(visit));
            visit.setId(newKey.intValue());
        }
        else {
            throw new UnsupportedOperationException("Visit update not supported");
        }
    }

    // END of Clinic implementation section ************************************


    /**
     * Creates a {@link MapSqlParameterSource} based on data values from the
     * supplied {@link Pet} instance.
     */
    private MapSqlParameterSource createPetParameterSource(Pet pet) {
        return new MapSqlParameterSource()
            .addValue("id", pet.getId())
            .addValue("name", pet.getName())
            .addValue("birth_date", pet.getBirthDate())
            .addValue("type_id", pet.getType().getId())
            .addValue("owner_id", pet.getOwner().getId());
    }

    /**
     * Creates a {@link MapSqlParameterSource} based on data values from the
     * supplied {@link Visit} instance.
     */
    private MapSqlParameterSource createVisitParameterSource(Visit visit) {
        return new MapSqlParameterSource()
            .addValue("id", visit.getId())
            .addValue("visit_date", visit.getDate())
            .addValue("description", visit.getDescription())
            .addValue("pet_id", visit.getPet().getId());
    }

    /**
     * Loads the {@link Visit} data for the supplied {@link Pet}.
     */
    private void loadVisits(JdbcPet pet) {
        final List<Visit> visits = this.simpleJdbcTemplate.query(
                "SELECT id, visit_date, description FROM visits WHERE pet_id=?",
                new ParameterizedRowMapper<Visit>() {
                    public Visit mapRow(ResultSet rs, int row) throws SQLException {
                        Visit visit = new Visit();
                        visit.setId(rs.getInt("id"));
                        visit.setDate(rs.getTimestamp("visit_date"));
                        visit.setDescription(rs.getString("description"));
                        return visit;
                    }
                },
                pet.getId().intValue());
        for (Visit visit : visits) {
            pet.addVisit(visit);
        }
    }

    /**
     * Loads the {@link Pet} and {@link Visit} data for the supplied
     * {@link Owner}.
     */
    private void loadPetsAndVisits(final Owner owner) {
        final List<JdbcPet> pets = this.simpleJdbcTemplate.query(
                "SELECT id, name, birth_date, type_id, owner_id FROM pets WHERE owner_id=?",
                new JdbcPetRowMapper(),
                owner.getId().intValue());
        for (JdbcPet pet : pets) {
            owner.addPet(pet);
            pet.setType(EntityUtils.getById(getPetTypes(), PetType.class, pet.getTypeId()));
            loadVisits(pet);
        }
    }

    /**
     * Loads the {@link Pet} and {@link Visit} data for the supplied
     * {@link List} of {@link Owner Owners}.
     *
     * @param owners the list of owners for whom the pet and visit data should be loaded
     * @see #loadPetsAndVisits(Owner)
     */
    private void loadOwnersPetsAndVisits(List<Owner> owners) {
        for (Owner owner : owners) {
            loadPetsAndVisits(owner);
        }
    }

    /**
     * {@link ParameterizedRowMapper} implementation mapping data from a
     * {@link ResultSet} to the corresponding properties of the {@link JdbcPet} class.
     */
    private class JdbcPetRowMapper implements ParameterizedRowMapper<JdbcPet> {

        public JdbcPet mapRow(ResultSet rs, int rownum) throws SQLException {
            JdbcPet pet = new JdbcPet();
            pet.setId(rs.getInt("id"));
            pet.setName(rs.getString("name"));
            pet.setBirthDate(rs.getDate("birth_date"));
            pet.setTypeId(rs.getInt("type_id"));
            pet.setOwnerId(rs.getInt("owner_id"));
            return pet;
        }
    }

}
