/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package LABB5_DAOTextFile;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.xml.sax.SAXException;
/**
 *
 * @author h14zguba
 */
public class DAOSpaMySQL implements DAOSpa {

   private Connection con;
   Statement statement;
   PreparedStatement preparedStatement;


    @Override
    public void addSpa(DTOSpa dtoSpa) {


       try {
           con =getConnection();

           // ? fråga tecken platshållar för data som ska in i tabellen
           String insertSpaSQL =    "INSERT INTO h14zguba_spaProduct"+
                   "(ID,namn,kategori,imageURL,iLager)VALUES"+
                   "(?,?,?,?,?)";

           preparedStatement = con.prepareStatement(insertSpaSQL);
           preparedStatement.setInt   (1, dtoSpa.ID);
           preparedStatement.setString(2, dtoSpa.namn);
           preparedStatement.setString(3, dtoSpa.kategori);
           preparedStatement.setString(4, dtoSpa.imageURL);
           preparedStatement.setString(5, dtoSpa.iLager);

           preparedStatement.executeUpdate();
           preparedStatement.close();
           con.close();
       } catch (SQLException ex) {
           Logger.getLogger(DAOSpaMySQL.class.getName()).log(Level.SEVERE, null, ex);
       }





    }

    @Override
    public void deleteSpa(int ID) {

       try {
           con = getConnection();

           // ? Frågatecken platshållar för data som ska in i tabellen
           String deleteSpaSQL = "DELETE FROM h14zguba_spaProduct WHERE ID=?";

           preparedStatement = con.prepareStatement(deleteSpaSQL);
           preparedStatement.setString(1, String.valueOf(ID));
           preparedStatement.executeUpdate();
           preparedStatement.close();
           con.close();

       }catch (SQLException ex){

           System.out.println(DAOSpaMySQL.class.getName()+ex.getMessage());
       }
    }

    @Override
    public void uppdateSpa(DTOSpa dtoSpa) {

        try{

            con = getConnection();

            String updateSpaSQL = "UPDATE h14zguba_spaProduct SET namn=?, kategori=?, imageURL=?, iLager=? WHERE id=?";

            preparedStatement = con.prepareStatement(updateSpaSQL);
            preparedStatement.setString(1, dtoSpa.namn);
            preparedStatement.setString(2, dtoSpa.kategori);
            preparedStatement.setString(3, dtoSpa.imageURL);
            preparedStatement.setString(4, dtoSpa.iLager);
            preparedStatement.setInt(5, dtoSpa.ID);

            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();

        }catch (SQLException ex){
          System.out.println(DAOSpaMySQL.class.getName()+ex.getMessage());

        }
    }

   @Override
    public List<DTOSpa> getSpa(){
       List<DTOSpa> spaProdukArray = new ArrayList();

       try {

           con  = getConnection();
           statement = con.createStatement();

           String sql = "SELECT ID,namn,kategori,imageURL,iLager from h14zguba_spaProduct";

           ResultSet result = statement.executeQuery(sql);

           while (result.next()) {
               //Hämtar ut data baserat på kolumnamn i databasen


               int ID = result.getInt("ID");
               String Namn = result.getString("namn");
               String Kategori = result.getString("kategori");
               String ImageURL = result.getString("imageURL");
               String iLager = result.getString("iLager");


        //Skapar upp ett nytt DTOSpa objekt och lägger den i listan
              spaProdukArray.add(new DTOSpa(ID,Namn,Kategori,ImageURL,iLager));

           }//end While
        result.close();
        statement.close();
        con.close();
        return spaProdukArray;

       }catch (SQLException ex) {
           System.out.println(DAOSpaMySQL.class.getName()+ ex.getMessage());
       }
       return null;
   }

    //Privat metod för att returnera connection objekt
private Connection getConnection(){

    try {

        con = DriverManager.getConnection(

//JDBC: java Database connecivity
//Just nu jobbar mot MySQL Server, Databas server(utb-mysql.du.se), port:3306/AnvNamn:db30

                "jdbc:mysql://utb-mysql.du.se:3306/db30",//Vilket databas
                 "db30",// användarnamn
                 "FJJAcyMU");// lösenordet

                 return con;

}catch(SQLException ex){

    System.out.println(DAOSpaMySQL.class.getName() + ex.getMessage() );

}
return null;
}


}