package com.springbook;

import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

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

public class JDBCRentABike extends JdbcDaoSupport implements RentABike {
   private String storeName;
   private static final int MANUFACTURER = 2;
   private static final int MODEL = 3;
   private static final int FRAME = 4;
   private static final int SERIALNO = 5;
   private static final int WEIGHT = 6;
   private static final int STATUS = 7;


   public List getBikes() {
      final ArrayList results = new ArrayList();
      JdbcTemplate template = getJdbcTemplate();
      template.query("SELECT * FROM bikes",
            new RowCallbackHandler() {
               public void processRow(ResultSet rs) throws SQLException {
                  Bike bike = new Bike(rs.getString(MANUFACTURER),
                        rs.getString(MODEL), rs.getInt(FRAME),
                        rs.getString(SERIALNO), rs.getDouble(WEIGHT),
                        rs.getString(STATUS));
                  results.add(bike);
               }
            });
      return results;
   }

   public Bike getBike(String serialNo) {
      final Bike bike = new Bike();
      JdbcTemplate template = getJdbcTemplate();
      template.query("SELECT * FROM bikes where bikes.serialno = '" + serialNo + "'",
            new RowCallbackHandler() {
               public void processRow(ResultSet rs) throws SQLException {
                  bike.setManufacturer(rs.getString(MANUFACTURER));
                  bike.setModel(rs.getString(MODEL));
                  bike.setFrame(rs.getInt(FRAME));
                  bike.setSerialNo(rs.getString(SERIALNO));
                  bike.setWeight(rs.getDouble(WEIGHT));
                  bike.setStatus(rs.getString(STATUS));
               }
            });
      return bike;
   }

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

   public void saveBike(Bike bike) {
      JdbcTemplate template = getJdbcTemplate();
      template.execute("DELETE FROM bikes where bikes.serialno = '" + bike.getSerialNo() + "'");
      template.execute("INSERT INTO bikes (manufacturer, model, frame, serialno, weight, status) VALUES ('" + bike.getManufacturer() + "', '" + bike.getModel() + "', " + bike.getFrame() + ", '" + bike.getSerialNo() + "', " + bike.getWeight() + ", '" + bike.getStatus() + "')");      
   }

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

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