package DataSource;

/*
 * This File is one of the examples from Java Object Oriented Programming
 * Do not reproduce this code for others or use it in a commercial setting without prior permission from the author.
 */

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import products.*;
import salesApplication.PriceList;

/**
 *
 * @author Matthew Gregory
 * @website www.matthewgregory-author.com.au
 *
 */
public class DatabaseDataSource implements DataSource {

    public PriceList retrieveList(String name) {
        PriceList retrievedList=new PriceList();
        try {
//            Connection con = DriverManager.getConnection(
//                    "jdbc:derby://localhost:1527/Price List",
//                    "test",
//                    "test");
            Connection con = DriverManager.getConnection(
                   "jdbc:sqlserver://localhost;" +  
   "databaseName=PriceList",
                    "test",
                    "test");
            //replace...
            //Statement stmt = con.createStatement();
            //with a prepared statement...
            PreparedStatement stmt = 
                    con.prepareStatement(
                            "select pl.PRICE_LIST, p.* from products p " +
                            "join price_list_products pl on (pl.PRODUCT=p.PRODUCT_ID) " +
                            "where pl.PRICE_LIST=?");
            //bind the  first '?' to the parameter 'name'
            stmt.setString(1, name);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                retrievedList.addProduct(fromResultSet(rs));
                while (rs.next()){
                    retrievedList.addProduct(fromResultSet(rs));
                }
            } else {
                System.out.println("Price list: "+name+" not found!");
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseDataSource.class.getName()).log(Level.SEVERE, null, ex);
        } 
        return retrievedList;
    }
    
    private Product fromResultSet(ResultSet rs) throws SQLException{
        //cast the product as the correct type and fetch specific values
        switch (rs.getString("product_type").toUpperCase()){
            case "PEN":
                //Create the instance
                Pen pen = new Pen(rs.getString("product_name"));
                //Populate the generic fields
                pen = (Pen) setFields(pen,rs);
                //Populate the specific fields
                pen.setType(""+rs.getString("pen_type"));
                //Note: ""+ avoids a null pointer exception if pen_type is null
                //Return the product
                return pen;
            case "PENCIL":
                //Create the instance
                Pencil pencil = new Pencil(rs.getString("product_name"));
                //Populate the generic fields
                pencil = (Pencil) setFields(pencil,rs);
                //Populate the specific fields
                pencil.shade=rs.getString("pencil_shade");
                return pencil; 
            case "RULER":
                //Create the instance
                Ruler ruler = new Ruler(rs.getString("product_name")
                ,rs.getString("description")
                ,rs.getDouble("price")
                ,rs.getString("ruler_length"));
                //Populate the generic fields
                ruler = (Ruler) setFields(ruler,rs);
                return ruler;
            case "ERASER":
                //Create the instance
                Eraser eraser = new Eraser(rs.getString("product_name"));
                //Populate the generic fields
                eraser = (Eraser) setFields(eraser,rs);
                return eraser;
            case "NOTE PAD":
                //Create the instance
                Notepad notepad = new Notepad(rs.getString("product_name"));
                //Populate the generic fields
                notepad = (Notepad) setFields(notepad,rs);
                return notepad;
            case "PENCIL CASE":                
                //Create the instance
                PencilCase pencilCase = new PencilCase(rs.getString("product_name"));
                //Populate the generic fields
                pencilCase = (PencilCase) setFields(pencilCase,rs);
                return pencilCase;
            case "WRITING PACK":
                //Create the instance
                WritingPack writingPack = new WritingPack(rs.getString("product_name"));
                //Populate the generic fields
                writingPack = (WritingPack) setFields(writingPack,rs);
                //We would also have to populate all the information from
                //the writing pack table into this object, but for sake of 
                //time we won't worry.
                return writingPack;
            default:
                Product product = new Product(rs.getString("product_name"));
                //Populate the generic fields
                product = setFields(product,rs);
                return product;
        }        
    }
    
    private Product setFields(Product product,ResultSet rs) throws SQLException{
        product.setPrice(rs.getDouble("price"));
        product.setBrand(rs.getString("product_brand"));
//        product.setColour(rs.getString("colour"));
        product.enterDescription(rs.getString("description")
                ,rs.getString("country")
                ,rs.getInt("weight")
                ,rs.getInt("height"));
        return product;
    }

    public void saveList(String name, PriceList priceList) {
        try {
//            Connection con = DriverManager.getConnection(
//                    "jdbc:derby://localhost:1527/Price List",
//                    "test",
//                    "test");
            Connection con = DriverManager.getConnection(
                   "jdbc:sqlserver://localhost;databaseName=PriceList",
                    "test",
                    "test");
            //Find out if the price list already exist..
            PreparedStatement stmt = con.prepareStatement(
                "select count(*) count from price_lists where list_name =?");
            stmt.setString(1, name);
            ResultSet rs = stmt.executeQuery();
            rs.next();//need this to fetch the first set of rows
            if (rs.getInt("count")!=0) {//List already exists...
                System.out.println("List already exists");
            } else {
                System.out.println("List doesn't exist");
                //create list
                PreparedStatement insertList = con.prepareStatement(
                        "insert into price_lists values (?,?)");
                insertList.setString(1,name);
                insertList.setDate(2,Date.valueOf(priceList.DATE_CREATED));
                //Note JDBC driver doesn't recognise LocalDate object yet!
                insertList.executeUpdate();
                //associate the products with this list
                PreparedStatement insertListProducts = con.prepareStatement(
                        "insert into price_list_products values(?,?)");
                insertListProducts.setString(1,name);
                for (Item i:priceList.getList()){
                    insertListProducts.setInt(2,getProductId(i.getName()));
                    insertListProducts.executeUpdate();
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseDataSource.class.getName()).log(Level.SEVERE, null, ex);
        } 
    }
    
    private int getProductId(String name) throws SQLException{
//            Connection con = DriverManager.getConnection(
//                    "jdbc:derby://localhost:1527/Price List",
//                    "test",
//                    "test");
            Connection con = DriverManager.getConnection(
                   "jdbc:sqlserver://localhost;databaseName=PriceList",
                    "test",
                    "test");
            PreparedStatement getProductId = con.prepareStatement(
                        "select product_id from products where product_name=?");
                getProductId.setString(1,name);
            ResultSet rs = getProductId.executeQuery();
            if (rs.next()){
            return rs.getInt("product_id");
            } else {
                throw new SQLException("Product: "+name+" not found!");
            }
    }

    public void getListNames() {
        System.out.println("Current Price List Available: ");
        try {
//            Connection con = DriverManager.getConnection(
//                    "jdbc:derby://localhost:1527/Price List",
//                    "test",
//                    "test");
            Connection con = DriverManager.getConnection(
                   "jdbc:sqlserver://localhost;databaseName=PriceList",
                    "test",
                    "test");
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("select list_name from price_lists");
            while (rs.next()) {
                System.out.println(rs.getString("list_name"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseDataSource.class.getName()).log(Level.SEVERE, null, ex);
        } 
    }
}
