package data;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Category;
import model.Product;

/*
 * 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.
 */
/**
 *
 * @author Jasper
 */
public class DBShop {

    private static final String URL = "jdbc:mysql://localhost:3333/shop";
    private static final String USER = "user";
    private static final String PWD = "";

    private static DBShop instance;
    private Connection connection;

    public static DBShop getInstance() {
        if (instance == null) {
            instance = new DBShop();
        }
        return instance;
    }

    private void initConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(URL, USER, PWD);

        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public void addProduct(String name, double price) {
        try {

            String sql = "insert into shop.product(name, price) values(?,?)";
            PreparedStatement prep = connection.prepareStatement(sql);
            prep.setString(1, name);
            prep.setDouble(2, price);
            prep.executeUpdate();
            prep.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public void addCategory(int id, String name) {
        try {

            String sql = "insert into shop.category(id, name) values(?,?)";
            PreparedStatement prep = connection.prepareStatement(sql);
            prep.setInt(1, id);
            prep.setString(2, name);
            prep.executeUpdate();
            prep.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public List<Product> getAllProducts() {
        List<Product> products = new ArrayList<>();

        try {
            String sql = "select * from shop.product";
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                products.add(new Product(rs.getString("name"), rs.getDouble("price"), rs.getString("id")));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return products;
    }

   public List<Category> getCategories(){
        List<Category> categories = new ArrayList<>();

        try {
            String sql = "select * from shop.category";
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                categories.add(new Category(rs.getInt("id"), rs.getString("name") ));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return categories;
    }

    private DBShop() {
        initConnection();
    }
}