/* Generated version of this file copyright 2004 SO.MUS.AR. s.a.s.  */
/* 
 Original hand-written version of this source file 
 Copyright 2003 Sun Microsystems, Inc. All rights reserved.
 
 Redistribution and use in source and binary forms, with or without
 modification, are permitted provided that the following conditions
 are met:
 
 - Redistributions of source code must retain the above copyright
   notice, this list of conditions and the following disclaimer.
 
 - Redistribution in binary form must reproduce the above copyright
   notice, this list of conditions and the following disclaimer in
   the documentation and/or other materials provided with the
   distribution.
 
 Neither the name of Sun Microsystems, Inc. or the names of
 contributors may be used to endorse or promote products derived
 from this software without specific prior written permission.
 
 This software is provided "AS IS," without a warranty of any
 kind. ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND
 WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY,
 FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT, ARE HEREBY
 EXCLUDED. SUN AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES
 SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR
 DISTRIBUTING THE SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL SUN
 OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR
 FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR
 PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF
 LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE,
 EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
 
 You acknowledge that Software is not designed, licensed or intended
 for use in the design, construction, operation or maintenance of
 any nuclear facility.
*/

package com.sun.j2ee.blueprints.catalog.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.Context;
import javax.naming.NamingException;
import java.util.Locale;
import java.util.ArrayList;
import java.util.Iterator;

import com.sun.j2ee.blueprints.catalog.*;

import com.sun.j2ee.blueprints.util.tracer.Debug;
import com.sun.j2ee.blueprints.util.dao.DAOUtils;
import com.sun.j2ee.blueprints.util.dao.DAOSystemException;

/**
 * This class implements CatalogDAO for Pointbase database.
 * This class encapsulates all the database access for the Catalog.
 * It follows the Data Access Object pattern.
 */

public class PointbaseCatalogDAO implements CatalogDAO {

//
// ----- Molding step 1
//       Generate SQL INSERT statement string if required by
//       LOGIC.default.queries
//
//
// ----- Molding step 2
//       Generate SQL SELECT statement string if required by
//       LOGIC.default.queries
//
//
// ----- Molding step 3
//       Generate SQL SELECT statement string for duplicate
//       checks if required by LOGIC.default.queries
//
//
// ----- Molding step 4
//       Generate custom SQL query strings if required by LOGIC.query
//
    private final static String GET_LODGINGS_SELECT_QUERY_STR =
        "SELECT " +
            "lodgingid, name, description, price, location" +
        " FROM " + DatabaseNames.LODGING_TABLE + 
        " WHERE " + 
            "location = ? AND locale = ?";

    private final static String GET_LODGING_SELECT_QUERY_STR =
        "SELECT " +
            "lodgingid, name, description, price, location" +
        " FROM " + DatabaseNames.LODGING_TABLE + 
        " WHERE " + 
            "lodgingid = ? AND locale = ?";

    private final static String GET_TRANSPORTATIONS_SELECT_QUERY_STR =
        "SELECT " +
            "transportationid, name, description, imageuri, price, " +
            "origin, destination, carrier, departuretime, arrivaltime, " +
            "class" +
        " FROM " + DatabaseNames.TRANSPORTATION_TABLE + 
        " WHERE " + 
            "origin = ? AND destination = ? AND locale = ?";

    private final static String GET_TRANSPORTATION_SELECT_QUERY_STR =
        "SELECT " +
            "transportationid, name, description, imageuri, price, " +
            "origin, destination, carrier, departuretime, arrivaltime, " +
            "class" +
        " FROM " + DatabaseNames.TRANSPORTATION_TABLE + 
        " WHERE " + 
            "transportationid = ? AND locale = ?";

    private final static String GET_ACTIVITIES_SELECT_QUERY_STR =
        "SELECT " +
            "activityid, name, description, price, location" +
        " FROM " + DatabaseNames.ACTIVITY_TABLE + 
        " WHERE " + 
            "location = ? AND locale = ?";

    private final static String GET_ACTIVITY_SELECT_QUERY_STR =
        "SELECT " +
            "activityid, name, description, price, location" +
        " FROM " + DatabaseNames.ACTIVITY_TABLE + 
        " WHERE " + 
            "activityid = ? AND locale = ?";

    private final static String GET_ADVENTURE_PACKAGE_SELECT_QUERY_STR =
        "SELECT " +
            "packageid, name, description, price, location, " +
            "lodgingid" +
        " FROM " + DatabaseNames.PACKAGE_TABLE + 
        " WHERE " + 
            "packageid = ? AND locale = ?";

    private final static String GET_ACTIVITY_LIST_SELECT_QUERY_STR =
        "SELECT " +
            "activityid" +
        " FROM " + DatabaseNames.ACTIVITYLIST_TABLE + 
        " WHERE " + 
            "packageid = ? AND locale = ?";

    public PointbaseCatalogDAO() {
    }

//
// ----- Molding step 5
//       Generate various database access methods depending on
//       generation requests listed in LOGIC.default.queries
//
    // getLodgings()
    public ArrayList getLodgings (
                        String keyLocation,
                        Locale keyLocale
           ) throws CatalogDAOException,
                    DAOSystemException {

        PreparedStatement stmt = null;
        ResultSet result = null;
        Connection dbConnection = null;
        ArrayList lodgings = new ArrayList();

        try {
            dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
            stmt = dbConnection.prepareStatement(GET_LODGINGS_SELECT_QUERY_STR);
            stmt.setString(1, keyLocation.trim());
            stmt.setString(2, keyLocale.toString().trim());
            result = stmt.executeQuery();

            if ( !result.next() )
                throw new CatalogDAOException(
                                  "No data found for " + 
                                  keyLocation + " " + keyLocale.toString());

            do {
                int i = 1;
                String    lodgingId   = result.getString(i++);
                String    name        = result.getString(i++);
                String    description = result.getString(i++);
                double    price       = result.getDouble(i++);
                String    location    = result.getString(i++);
                Lodging lodging = new Lodging(
                              lodgingId, name, description, price, location);
                lodgings.add(lodging);
            }
            while (result.next());

            return(lodgings);
        } catch(SQLException se) {
            throw new DAOSystemException("SQLException while getting " +
                      "lodging details; id = " + 
                              keyLocation + " " + keyLocale.toString()
                              + "\n", se);
        } finally {
            DAOUtils.closeResultSet(result);
            DAOUtils.closeStatement(stmt);
            DAOUtils.closeConnection(dbConnection);
        }
    }

    // getLodging()
    public Lodging getLodging (
                        String keyLodgingId,
                        Locale keyLocale
           ) throws CatalogDAOException,
                    DAOSystemException {

        PreparedStatement stmt = null;
        ResultSet result = null;
        Connection dbConnection = null;

        try {
            dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
            stmt = dbConnection.prepareStatement(GET_LODGING_SELECT_QUERY_STR);
            stmt.setString(1, keyLodgingId.trim());
            stmt.setString(2, keyLocale.toString().trim());
            result = stmt.executeQuery();

            if ( !result.next() )
                throw new CatalogDAOException(
                                  "No data found for " + 
                                  keyLodgingId + " " + keyLocale.toString());

            int i = 1;
            String    lodgingId   = result.getString(i++);
            String    name        = result.getString(i++);
            String    description = result.getString(i++);
            double    price       = result.getDouble(i++);
            String    location    = result.getString(i++);
            Lodging lodging = new Lodging(
                              lodgingId, name, description, price, location);
            return(lodging);
        } catch(SQLException se) {
            throw new DAOSystemException("SQLException while getting " +
                      "lodging details; id = " + 
                              keyLodgingId + " " + keyLocale.toString()
                              + "\n", se);
        } finally {
            DAOUtils.closeResultSet(result);
            DAOUtils.closeStatement(stmt);
            DAOUtils.closeConnection(dbConnection);
        }
    }

    // getTransportations()
    public ArrayList getTransportations (
                        String keyOrigin,
                        String keyDestination,
                        Locale keyLocale
           ) throws CatalogDAOException,
                    DAOSystemException {

        PreparedStatement stmt = null;
        ResultSet result = null;
        Connection dbConnection = null;
        ArrayList transportations = new ArrayList();

        try {
            dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
            stmt = dbConnection.prepareStatement(GET_TRANSPORTATIONS_SELECT_QUERY_STR);
            stmt.setString(1, keyOrigin.trim());
            stmt.setString(2, keyDestination.trim());
            stmt.setString(3, keyLocale.toString().trim());
            result = stmt.executeQuery();

            if ( !result.next() )
                throw new CatalogDAOException(
                                  "No data found for " + 
                                  keyOrigin + " " + keyDestination + " " + keyLocale.toString());

            do {
                int i = 1;
                String    transportationId      = result.getString(i++);
                String    name        = result.getString(i++);
                String    description = result.getString(i++);
                String    imageUri    = result.getString(i++);
                double    price       = result.getDouble(i++);
                String    origin      = result.getString(i++);
                String    destination = result.getString(i++);
                String    carrier     = result.getString(i++);
                String    departureTime = result.getString(i++);
                String    arrivalTime = result.getString(i++);
                String    travelClass = result.getString(i++);
                Transportation transportation = new Transportation(
                              transportationId, name, description, imageUri, price, origin, destination, carrier, departureTime, arrivalTime, travelClass);
                transportations.add(transportation);
            }
            while (result.next());

            return(transportations);
        } catch(SQLException se) {
            throw new DAOSystemException("SQLException while getting " +
                      "transportation details; id = " + 
                              keyOrigin + " " + keyDestination + " " + keyLocale.toString()
                              + "\n", se);
        } finally {
            DAOUtils.closeResultSet(result);
            DAOUtils.closeStatement(stmt);
            DAOUtils.closeConnection(dbConnection);
        }
    }

    // getTransportation()
    public Transportation getTransportation (
                        String keyTransportationId,
                        Locale keyLocale
           ) throws CatalogDAOException,
                    DAOSystemException {

        PreparedStatement stmt = null;
        ResultSet result = null;
        Connection dbConnection = null;

        try {
            dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
            stmt = dbConnection.prepareStatement(GET_TRANSPORTATION_SELECT_QUERY_STR);
            stmt.setString(1, keyTransportationId.trim());
            stmt.setString(2, keyLocale.toString().trim());
            result = stmt.executeQuery();

            if ( !result.next() )
                throw new CatalogDAOException(
                                  "No data found for " + 
                                  keyTransportationId + " " + keyLocale.toString());

            int i = 1;
            String    transportationId      = result.getString(i++);
            String    name        = result.getString(i++);
            String    description = result.getString(i++);
            String    imageUri    = result.getString(i++);
            double    price       = result.getDouble(i++);
            String    origin      = result.getString(i++);
            String    destination = result.getString(i++);
            String    carrier     = result.getString(i++);
            String    departureTime = result.getString(i++);
            String    arrivalTime = result.getString(i++);
            String    travelClass = result.getString(i++);
            Transportation transportation = new Transportation(
                              transportationId, name, description, imageUri, price, origin, destination, carrier, departureTime, arrivalTime, travelClass);
            return(transportation);
        } catch(SQLException se) {
            throw new DAOSystemException("SQLException while getting " +
                      "transportation details; id = " + 
                              keyTransportationId + " " + keyLocale.toString()
                              + "\n", se);
        } finally {
            DAOUtils.closeResultSet(result);
            DAOUtils.closeStatement(stmt);
            DAOUtils.closeConnection(dbConnection);
        }
    }

    // getActivities()
    public ArrayList getActivities (
                        String keyLocation,
                        Locale keyLocale
           ) throws CatalogDAOException,
                    DAOSystemException {

        PreparedStatement stmt = null;
        ResultSet result = null;
        Connection dbConnection = null;
        ArrayList activities = new ArrayList();

        try {
            dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
            stmt = dbConnection.prepareStatement(GET_ACTIVITIES_SELECT_QUERY_STR);
            stmt.setString(1, keyLocation.trim());
            stmt.setString(2, keyLocale.toString().trim());
            result = stmt.executeQuery();

            if ( !result.next() )
                throw new CatalogDAOException(
                                  "No data found for " + 
                                  keyLocation + " " + keyLocale.toString());

            do {
                int i = 1;
                String    activityId  = result.getString(i++);
                String    name        = result.getString(i++);
                String    description = result.getString(i++);
                double    price       = result.getDouble(i++);
                String    location    = result.getString(i++);
                Activity activity = new Activity(
                              activityId, name, description, price, location);
                activities.add(activity);
            }
            while (result.next());

            return(activities);
        } catch(SQLException se) {
            throw new DAOSystemException("SQLException while getting " +
                      "activity details; id = " + 
                              keyLocation + " " + keyLocale.toString()
                              + "\n", se);
        } finally {
            DAOUtils.closeResultSet(result);
            DAOUtils.closeStatement(stmt);
            DAOUtils.closeConnection(dbConnection);
        }
    }

    // getActivity()
    public Activity getActivity (
                        String keyActivityId,
                        Locale keyLocale
           ) throws CatalogDAOException,
                    DAOSystemException {

        PreparedStatement stmt = null;
        ResultSet result = null;
        Connection dbConnection = null;

        try {
            dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
            stmt = dbConnection.prepareStatement(GET_ACTIVITY_SELECT_QUERY_STR);
            stmt.setString(1, keyActivityId.trim());
            stmt.setString(2, keyLocale.toString().trim());
            result = stmt.executeQuery();

            if ( !result.next() )
                throw new CatalogDAOException(
                                  "No data found for " + 
                                  keyActivityId + " " + keyLocale.toString());

            int i = 1;
            String    activityId  = result.getString(i++);
            String    name        = result.getString(i++);
            String    description = result.getString(i++);
            double    price       = result.getDouble(i++);
            String    location    = result.getString(i++);
            Activity activity = new Activity(
                              activityId, name, description, price, location);
            return(activity);
        } catch(SQLException se) {
            throw new DAOSystemException("SQLException while getting " +
                      "activity details; id = " + 
                              keyActivityId + " " + keyLocale.toString()
                              + "\n", se);
        } finally {
            DAOUtils.closeResultSet(result);
            DAOUtils.closeStatement(stmt);
            DAOUtils.closeConnection(dbConnection);
        }
    }

    // getAdventurePackage()
    public AdventurePackage getAdventurePackage (
                        String keyPackageId,
                        Locale keyLocale
           ) throws CatalogDAOException,
                    DAOSystemException {

        PreparedStatement stmt = null;
        ResultSet result = null;
        Connection dbConnection = null;

        try {
            dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
            stmt = dbConnection.prepareStatement(GET_ADVENTURE_PACKAGE_SELECT_QUERY_STR);
            stmt.setString(1, keyPackageId.trim());
            stmt.setString(2, keyLocale.toString().trim());
            result = stmt.executeQuery();

            if ( !result.next() )
                throw new CatalogDAOException(
                                  "No data found for " + 
                                  keyPackageId + " " + keyLocale.toString());

            int i = 1;
            String    packageId   = result.getString(i++);
            String    name        = result.getString(i++);
            String    description = result.getString(i++);
            double    price       = result.getDouble(i++);
            String    location    = result.getString(i++);
            String    lodgingId   = result.getString(i++);
            ArrayList activities;
            activities = getActivityList(
                keyPackageId,
                keyLocale
            );

            AdventurePackage adventurePackage = new AdventurePackage(
                              packageId, name, description, location, lodgingId, price, activities);
            return(adventurePackage);
        } catch(SQLException se) {
            throw new DAOSystemException("SQLException while getting " +
                      "adventurePackage details; id = " + 
                              keyPackageId + " " + keyLocale.toString()
                              + "\n", se);
        } finally {
            DAOUtils.closeResultSet(result);
            DAOUtils.closeStatement(stmt);
            DAOUtils.closeConnection(dbConnection);
        }
    }

    // getActivityList()
    public ArrayList getActivityList (
                        String keyPackageId,
                        Locale keyLocale
           ) throws CatalogDAOException,
                    DAOSystemException {

        PreparedStatement stmt = null;
        ResultSet result = null;
        Connection dbConnection = null;
        ArrayList activityListItems = new ArrayList();

        try {
            dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
            stmt = dbConnection.prepareStatement(GET_ACTIVITY_LIST_SELECT_QUERY_STR);
            stmt.setString(1, keyPackageId.trim());
            stmt.setString(2, keyLocale.toString().trim());
            result = stmt.executeQuery();

            if ( !result.next() )
                throw new CatalogDAOException(
                                  "No data found for " + 
                                  keyPackageId + " " + keyLocale.toString());

            do {
                int i = 1;
                String    activityId  = result.getString(i++);
                activityListItems.add( activityId );
            }
            while (result.next());

            return(activityListItems);
        } catch(SQLException se) {
            throw new DAOSystemException("SQLException while getting " +
                      "activityList details; id = " + 
                              keyPackageId + " " + keyLocale.toString()
                              + "\n", se);
        } finally {
            DAOUtils.closeResultSet(result);
            DAOUtils.closeStatement(stmt);
            DAOUtils.closeConnection(dbConnection);
        }
    }

}