import java.net.URL;
import java.sql.*;

/**
 * class TourDAO
 * stores and retrieves objects of type Tour
 *
 * @author Jim
 * @version 1.0
 */
public class TourDAO
{
  private static Connection theConn;

   /**
     * Finds a tour from the database, specified by tourID
     * Also gets related package
     *
     * @param tourID the target tourID
     * @return the Tour retrieved from the database
     */
    public static Tour getByTourID(int tourID)
    {
         try {
             // connection to an ACCESS MDB
             theConn = getConnection();
             Tour t = null;

             ResultSet rs;
             Statement stmt;
             String sql;

             sql =
                "SELECT * " +
                "FROM Tours " +
                "WHERE tourID = " + tourID;
             System.out.println("Query: " + sql);   // for debugging SQL statement

             stmt = theConn.createStatement();
             rs = stmt.executeQuery(sql);

             // use only first row of result - should be unique
             if (rs.next()) {
                 t = new Tour(rs.getInt("tourID"),
                                rs.getString("departureDate"),
                                rs.getInt("offer")
                                );
                 int packageID = rs.getInt("packageID");    // database stores package as foreign key field
                 Package p = PackageDAO.getByPackageID(packageID);    // get related pPackage
                 t.setHolidayPackage(p);                                     // and set reference in Tour
             }
             rs.close();
             stmt.close();

             return t;
         }
         catch (Exception e) {
             System.out.println("COULD NOT COMPLETE QUERY: " + e.toString());
             //e.printStackTrace();
             return null;
         }
         finally {
             try {
                 if (theConn != null) theConn.close();
                }
             catch (Exception e) {
             }
        }
    }


    /**
     * Finds all tours from the database for a specific package, specified by packageID
     * Limited to first 20 tours.
     * Should use larger array or collection to get more
     * Also gets related package.
     *
     * @param packageID the target pacakgeID
     * @return the Tour retrieved from the database
     */
    public static Tour[] getByPackageID(int packageID)
    {
         try {
             // connection to an ACCESS MDB
             theConn = getConnection();
             Tour[] tours = new Tour[20];
             Tour t = null;
             Package p = null;

             ResultSet rs;
             Statement stmt;
             String sql;

             sql =
                "SELECT * " +
                "FROM Tours " +
                "WHERE packageID = " + packageID;
             System.out.println("Query: " + sql);   // for debugging SQL statement

             stmt = theConn.createStatement();
             rs = stmt.executeQuery(sql);

             int count = 0;
             while (rs.next() && count<20) {
                 t = new Tour(rs.getInt("tourID"),
                                rs.getString("departureDate"),
                                rs.getInt("offer")
                                );
                 if(p==null){                       // only need to get package once as all tours should be related to the same package
                    p = PackageDAO.getByPackageID(packageID);       // get related Package
                 }
                 t.setHolidayPackage(p);                                    // and set reference in Tour
                 tours[count] = t;
                 count++;
             }
             rs.close();
             stmt.close();

             return tours;
         }
         catch (Exception e) {
             System.out.println("COULD NOT COMPLETE QUERY: " + e.toString());
             //e.printStackTrace();
             return null;
         }
         finally {
             try {
                 if (theConn != null) theConn.close();
                }
             catch (Exception e) {
             }
        }
    }

     /**
     * Finds all tours from the database for a holiday location.
     * Limited to first 20 tours.
     * Should use larger array or collection to get more
     * Also gets related package.
     *
     * @param packageID the target pacakgeID
     * @return the Tour retrieved from the database
     */
    public static Tour[] getByLocation(String location)
    {
         try {
             // connection to an ACCESS MDB
             theConn = getConnection();
             Tour[] tours = new Tour[20];
             Tour t = null;
             Package p = null;

             ResultSet rs;
             Statement stmt;
             String sql;

             sql =
                "SELECT * " +
                "FROM Tours, Packages " +
                "WHERE Tours.packageID = Packages.packageID " +
                "AND location = '" + location + "'";
             System.out.println("Query: " + sql);   // for debugging SQL statement

             stmt = theConn.createStatement();
             rs = stmt.executeQuery(sql);

             int count = 0;
             while (rs.next() && count < 20) {
                 t = new Tour(rs.getInt("tourID"),
                                rs.getString("departureDate"),
                                rs.getInt("offer")
                                );
                 p = PackageDAO.getByPackageID(rs.getInt("packageID"));       // get related Package (this will create a separate Package object for each Tour - could use identity map to check if package has been instantiated)
                 t.setHolidayPackage(p);                                    // and set reference in Tour
                 tours[count] = t;
                 count++;
             }
             rs.close();
             stmt.close();

             return tours;
         }
         catch (Exception e) {
             System.out.println("COULD NOT COMPLETE QUERY: " + e.toString());
             //e.printStackTrace();
             return null;
         }
         finally {
             try {
                 if (theConn != null) theConn.close();
                }
             catch (Exception e) {
             }
        }
    }


      /**
     * Stores a new Tour object to the database
     *
     * @param tour the Tour object to be stored
     */
    public static void storeNew(Tour tour)
    {
         try {
             // connection to an ACCESS MDB
             theConn = getConnection();

             //check for new object (tourID = 0)
             if (tour.getTourID()!=0){
                 System.out.println("Use this method to store newly created tours only");
             }
             else {
                 Statement stmt;
                 String sql;

                 sql =          // note that tourID will be set automatically by the database
                    "INSERT INTO Tours(departureDate,offer,packageID) "+
                    "VALUES('" +
                        tour.getDepartureDate() + "'," +
                        tour.getOffer() + "," +
                        tour.getHolidayPackage().getPackageID() + ")" ;


                 System.out.println("Query: " + sql);   // for debugging SQL statement

                 stmt = theConn.createStatement();
                 int rowsAffected = stmt.executeUpdate(sql);

                 System.out.println(rowsAffected + " row(s) inserted");   // to check whether insert was successful

                 stmt.close();
            }

         }
         catch (Exception e) {
             System.out.println("COULD NOT STORE: " + e.toString());
             //e.printStackTrace();
         }
         finally {
             try {
                 if (theConn != null) theConn.close();
                }
             catch (Exception e) {
             }
        }
    }


     /**
      * Gets a Connection object to the database
      *
      * @return the Connection object
      */
      private static Connection getConnection() throws Exception {
        Driver d = (Driver)Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
        //String URL = "jdbc:odbc:gcutours";
        //Connection c = DriverManager.getConnection(URL, null, null);
        Connection c = DriverManager.getConnection(
            "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};" +
            "DBQ=c:/Users/student/My Documents/databases/gcutours_app.mdb"
        );
        return c;
    }
}