import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class Main {

    static final String DB_URL = "jdbc:mysql://localhost/lab1?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";

    static Statement stmt = null;

    static final String USER = "root";
    static final String PASS = "root";

    public static void main(String[] args) throws ClassNotFoundException,
            SQLException, InstantiationException, IllegalAccessException {

        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);


        stmt = conn.createStatement();

        String sql = "SELECT * FROM Products";
        ResultSet rs = stmt.executeQuery(sql);

        while (rs.next()) {
            String name = rs.getString("prod_name");
            //System.out.println(name);
        }

        rs.close();
        stmt.close();
        conn.close();
        System.out.println(getVendorsNames());
        System.out.println(getProductsNames("Bear Emporium"));

    }

    static ArrayList<String> getVendorsNames() throws ClassNotFoundException,
            SQLException, InstantiationException, IllegalAccessException  {

        Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);


        stmt = conn.createStatement();

        String sql = "SELECT * FROM Vendors";
        ResultSet rs = stmt.executeQuery(sql);

        ArrayList<String> res = new ArrayList<String>();

        while (rs.next()) {
            String name = rs.getString("vend_name");
            String id = rs.getString("vend_id");
            res.add(name);
            System.out.println(name+" "+id);
        }

        rs.close();
        stmt.close();
        conn.close();
        return res;
    }
    static ArrayList<String> getProductsNames(String customerName) throws ClassNotFoundException,
            SQLException, InstantiationException, IllegalAccessException  {

        Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);


        stmt = conn.createStatement();

        String sql = "SELECT * FROM Vendors WHERE vend_name='"+customerName+"'";
        ResultSet rs = stmt.executeQuery(sql);

        ArrayList<String> res = new ArrayList<String>();
        String vendorId="";

        while (rs.next()) {
            vendorId = rs.getString("vend_id");
        }

        rs.close();

        sql = "SELECT * FROM Products";
        rs = stmt.executeQuery(sql);
        System.out.println(rs);

        while (rs.next()) {
            String productName = rs.getString("prod_name");
            String dd = rs.getString("vend_id");
            System.out.println(productName+" "+dd);
        }

        rs.close();

        stmt.close();
        conn.close();
        return res;
    }


}