package dao;

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 javax.swing.JOptionPane;

public class Metodos {

	public static Connection connect() throws ClassNotFoundException {
		// SQLite connection string
		Class.forName("org.sqlite.JDBC");
		String url = "jdbc:sqlite:C://Users//i410132//Desktop//bd//tests.db";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		return conn;
	}

	public static void createNewTable() throws ClassNotFoundException {
		// SQLite connection string
		String url = "jdbc:sqlite:C://Users//i410132//Desktop//bd//tests.db";

		// SQL statement for creating a new table
		String sql = "CREATE TABLE IF NOT EXISTS warehouses ( id integer PRIMARY KEY, name text NOT NULL, capacity real);";
		Class.forName("org.sqlite.JDBC");

		try (Connection conn = DriverManager.getConnection(url);
				Statement stmt = conn.createStatement()) {
			// create a new table
			stmt.execute(sql);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}

	public static void consultarTodos() throws SQLException,
			ClassNotFoundException {
		try {
			Connection conn = Metodos.connect();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt
					.executeQuery("SELECT id, name, capacity FROM warehouses");

			while (rs.next()) {
				System.out.println(rs.getInt("id") + "\t"
						+ rs.getString("name") + "\t"
						+ rs.getDouble("capacity"));
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}

	public static void insert(String name, double capacity) throws ClassNotFoundException {
		String sql = "INSERT INTO warehouses(name,capacity) VALUES(?,?)";

		try {
			Connection conn = Metodos.connect();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setDouble(2, capacity);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}
	
	public static void update(Integer id, String name, double capacity) throws ClassNotFoundException {
		String sql = "UPDATE warehouses SET name = ?, capacity = ? where id= ? ;";
		
		try {
			Connection conn = Metodos.connect();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, name);
            pstmt.setDouble(2, capacity);
            pstmt.setInt(3, id);
            
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}

	public static void delete(Integer id) throws ClassNotFoundException{
		String sql = "DELETE FROM warehouses WHERE id = ?;";
		
		try {
			Connection conn = Metodos.connect();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
            pstmt.setInt(1, id);
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}
	
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Metodos md = new Metodos();
		// createNewTable();
//		String nome = JOptionPane.showInputDialog("Digite o Nome");
//		Integer capacidade = Integer.parseInt(JOptionPane.showInputDialog("Digite a Capacidade"));
//		
//		if((nome != null)&&(capacidade != null)){
//			Metodos.insert(nome, capacidade);			
//		}
		
		md.update(2, "Bragaa", 2800);
		md.delete(4);
		
		consultarTodos();
	}
}