/*
 * 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 ch.kftg.ims.noezimmermann;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.awt.HeadlessException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.swing.JOptionPane;

/**
 *
 * @author 1033801
 */
public class CDatabase {

    ArrayList<String> Selected;
    ArrayList<String> Colour;
    ArrayList<Integer> X;
    ArrayList<Integer> Y;




    /**
     * @param args the command line arguments
     */
    public CDatabase() {
        Selected = new ArrayList<>();
        Colour = new ArrayList<>();
        X = new ArrayList<>();
        Y = new ArrayList<>();




//        CDatabase database = new CDatabase();
//        // insert three new rows
//        database.insert("Raw Materials", 3000);
//        database.insert("Semifinished Goods", 4000);
//        database.insert("Finished Goods", 5000);
    }

    private Connection connect() {
        // SQLite connection string
        String url = "jdbc:sqlite:src/ch/kftg/ims/noezimmermann/resources/database.sqlite";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }

    /**
     * Insert a new row into the warehouses table
     *
     * @param name
     * @param capacity
     */
    public void insert(String selected, String color, int x, int y) {
        String sql = "INSERT INTO TObjects(ObjId, ObjForm, ObjColor, ObjX, ObjY) VALUES(?,?,?,?,?)";
        //
        try (Connection conn = this.connect();
                PreparedStatement pstmt = conn.prepareStatement(sql)) {
//            pstmt.setInt(1, id);
            pstmt.setString(2, selected);
            pstmt.setString(3, color);
            pstmt.setInt(4, x);
            pstmt.setInt(5, y);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public void getData() {

        String sql = "SELECT ObjForm, ObjColor, ObjX, ObjY FROM TObjects";
        try (Connection conn = this.connect();
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql)) {

            // loop through the result set
            while (rs.next()) {
                Selected.add(rs.getString("ObjForm"));
                Colour.add(rs.getString("ObjColor"));
                X.add(rs.getInt("ObjX"));
                Y.add(rs.getInt("ObjY"));
                CObjPanel.Selected.add(rs.getString("ObjForm"));
                CObjPanel.Colour.add(rs.getString("ObjColor"));
                CObjPanel.X.add(rs.getInt("ObjX"));
                CObjPanel.Y.add(rs.getInt("ObjY"));




                System.out.println(rs.getString("ObjForm") + "\t"
                        + rs.getString("ObjColor") + "\t"
                        + rs.getInt("ObjX") + "\t"
                        + rs.getInt("ObjY"));

            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }


    }

    public void clearData() {
        String sql = "DELETE FROM TObjects";
        //
        try (Connection conn = this.connect();
                PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

    }


}

/*





























    private static Connection Conn = null;
    private static Statement Stmt;

    public static Connection connectDB(String cstr) {
        try {
            Class.forName("org.sqlite.JDBC");
            Conn = DriverManager.getConnection("jdbc:sqlite::resource:" + cstr);
//            JOptionPane.showMessageDialog(null, "Connect!");
            return Conn;
        } catch (ClassNotFoundException | SQLException | HeadlessException e) {
            JOptionPane.showMessageDialog(null, e);
        }
        return null;
    }

    public CDatabase() {

    }






















    public void addData(int id, String selected, String color, int x, int y) {
        ResultSet rs;

        String sqlGetData = "SELECT";
        String sqlWriteData = "INSERT INTO TObjects (ObjId,ObjForm,ObjColor,ObjX,ObjY)\n" +"VALUES ("+id+", '"+selected+"', '"+color+"', "+x+", "+y+");";
        try{
        Statement stmt = Conn.createStatement();
        rs = stmt.executeQuery(sqlWriteData);
            System.out.println(""+rs);

        System.out.println(id + " " + selected + " " + color + " " + x + " " + y);
        }catch(Exception e){
            e.printStackTrace();

        }

    }

}

 */