package com.springbook;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.object.MappingSqlQuery;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;

public class JDBCRentABike extends JdbcDaoSupport implements RentABike {
   private String storeName;
   private static final int BIKE_ID = 1;
   private static final int BIKE_MANUFACTURER = 2;
   private static final int BIKE_MODEL = 3;
   private static final int BIKE_FRAME = 4;
   private static final int BIKE_SERIALNO = 5;
   private static final int BIKE_WEIGHT = 6;
   private static final int BIKE_STATUS = 7;
   private static final int CUST_ID = 1;
   private static final int CUST_FIRSTNAME = 2;
   private static final int CUST_LASTNAME = 3;


   public List getBikes() {
       final ArrayList results = new ArrayList();
       JdbcTemplate template = new JdbcTemplate();
       template.setDataSource(this.getDataSource());

       class BikesHandler implements RowCallbackHandler {
           public void processRow(ResultSet rs) throws SQLException {
               Bike bike = new Bike(rs.getString(BIKE_MANUFACTURER),
                     rs.getString(BIKE_MODEL), rs.getInt(BIKE_FRAME),
                     rs.getString(BIKE_SERIALNO), rs.getDouble(BIKE_WEIGHT),
                     rs.getString(BIKE_STATUS));
               results.add(bike);
           }
       }

       template.query("SELECT * FROM bikes", new BikesHandler());
       return results;
   }

   public Bike getBike(String serialNo) {
       final Bike bike = new Bike();
       JdbcTemplate template = new JdbcTemplate();
       template.setDataSource(this.getDataSource());

       class BikeHandler implements RowCallbackHandler {
           public void processRow(ResultSet rs) throws SQLException {
               bike.setBikeId(rs.getInt(BIKE_ID));
               bike.setManufacturer(rs.getString(BIKE_MANUFACTURER));
               bike.setModel(rs.getString(BIKE_MODEL));
               bike.setFrame(rs.getInt(BIKE_FRAME));
               bike.setSerialNo(rs.getString(BIKE_SERIALNO));
               bike.setWeight(rs.getDouble(BIKE_WEIGHT));
               bike.setStatus(rs.getString(BIKE_STATUS));
           }
       }

       template.query("SELECT * FROM bikes WHERE bikes.serialNo = '" + serialNo + "'",
                       new BikeHandler());
       return bike;
   }

   public Bike getBike(int bikeId) {
          final Bike bike = new Bike();
          JdbcTemplate template = new JdbcTemplate();
          template.setDataSource(this.getDataSource());

          class BikeHandler implements RowCallbackHandler {
              public void processRow(ResultSet rs) throws SQLException {
                  bike.setBikeId(rs.getInt(BIKE_ID));
                  bike.setManufacturer(rs.getString(BIKE_MANUFACTURER));
                  bike.setModel(rs.getString(BIKE_MODEL));
                  bike.setFrame(rs.getInt(BIKE_FRAME));
                  bike.setSerialNo(rs.getString(BIKE_SERIALNO));
                  bike.setWeight(rs.getDouble(BIKE_WEIGHT));
                  bike.setStatus(rs.getString(BIKE_STATUS));
              }
          }

          template.query("SELECT * FROM bikes WHERE bikes.bikeId = " + bikeId,
                          new BikeHandler());
          return bike;
      }


   public Customer getCustomer(int custId) {
        final Customer customer = new Customer();
        JdbcTemplate template = new JdbcTemplate();

        class CustomerHandler implements RowCallbackHandler {
            public void processRow(ResultSet rs) throws SQLException {
                customer.setCustId(rs.getInt(CUST_ID));
                customer.setFirstName(rs.getString(CUST_FIRSTNAME));
                customer.setLastName(rs.getString(CUST_LASTNAME));
            }
        }

        template.query("SELECT * FROM customers WHERE customers.custId = " + custId,
                        new CustomerHandler());
        return customer;

    }

   public List getReservations(Customer customer) {
      return new FindReservationsByCustomer(this.getDataSource()).
            findReservations(customer.getCustId());
   }

   public List getReservations(Bike bike) {
      return new FindReservationsByBike(this.getDataSource()).
            findReservations(bike.getBikeId());   
   }

   public void setStoreName(String name) {
      this.storeName = name;
   }

   public void saveBike(Bike bike) {
      JdbcTemplate template = getJdbcTemplate();
      Bike orig = this.getBike(bike.getSerialNo());
      System.out.println(orig);
      if(orig.getBikeId() == 0) {
          template.execute("INSERT INTO bikes (manufacturer, model, frame, serialno, weight, status) VALUES ('" + bike.getManufacturer() + "', '" + bike.getModel() + "', " + bike.getFrame() + ", '" + bike.getSerialNo() + "', " + bike.getWeight() + ", '" + bike.getStatus() + "')");
      }  else {
          template.execute("UPDATE bikes SET manufacturer = '" + bike.getManufacturer() + "', model = '" + bike.getModel() + "', frame = " + bike.getFrame() + ", serialno = '" + bike.getSerialNo() + "', weight = " + bike.getWeight() + ", status = '" + bike.getStatus() + "' WHERE bikeId = " + bike.getBikeId());
      }
   }

   public void deleteBike(Bike bike) {
      JdbcTemplate template = getJdbcTemplate();
      template.execute("DELETE FROM bikes where bikes.id = " + bike.getBikeId() + "");
   }

   public String getStoreName() {
      return this.storeName;
   }

     abstract class FindReservations extends MappingSqlQuery {
        protected List reservations = new ArrayList();

        protected FindReservations(DataSource dataSource, String query) {
            super(dataSource, query);
        }

        protected Object mapRow(ResultSet rs, int rownum) throws SQLException {
            int resId = rs.getInt(1);
            int bikeId = rs.getInt(2);
            int custId = rs.getInt(3);
            Date resDate = rs.getDate(4);

            Bike bike = getBike(bikeId);
            Customer customer = getCustomer(custId);
            Reservation reservation = new Reservation(resId, bike, customer, resDate);
            reservations.add(reservation);
            return reservation;
        }

        abstract List findReservations(int param);
    }

    class FindReservationsByCustomer extends FindReservations {
        public FindReservationsByCustomer(DataSource dataSource) {
            super(dataSource, "SELECT * FROM reservations WHERE custId = ?");
            declareParameter(new SqlParameter(Types.INTEGER));
            compile();
        }

        public List findReservations(int param) {
            execute(param);
            return this.reservations;
        }
    }

    class FindReservationsByBike extends FindReservations {
        public FindReservationsByBike(DataSource dataSource) {
            super(dataSource, "SELECT * FROM reservations WHERE bikeId = ?");
            declareParameter(new SqlParameter(Types.INTEGER));
            compile();
        }

        public List findReservations(int param) {
            execute(param);
            return reservations;
        }
    }
}