/*
 * 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 sdmhw1;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author damian
 */
public class SDMHW1 {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // TODO code application logic here
        String url="jdbc:mysql://localhost:3306/jdbcex";
        Class.forName("com.mysql.jdbc.Driver");
        Connection con=DriverManager.getConnection(url, "jdbcuser","password");
        Statement persAddr=con.createStatement();
        Statement selectCards=con.createStatement();
        String name,birth,job,street,city,country;
        ArrayList<Person> everyPerson=new ArrayList<>();
        ArrayList<CreditCard> creditCards=new ArrayList();


        ResultSet rs=persAddr.executeQuery("SELECT `person`.`id`,`person`.`name`,`person`.`birthDate`,`person`.`job`,`address`.`street`,`address`.`city`,`address`.`country` FROM `jdbcex`.`person` INNER JOIN address on address.id=person.fk_address");

        while(rs.next()){
            name=rs.getString("name");
            birth=rs.getString("birthDate");
            job=rs.getString("job");
            street=rs.getString("street");
            city=rs.getString("city");
            country=rs.getString("country");
            int id=rs.getInt("id");
            try (ResultSet cards = selectCards.executeQuery("SELECT * FROM jdbcex.creditCard where fk_person="+id)) {
                while(cards.next()){
                    creditCards.add(new CreditCard(cards.getString("iban"),cards.getString("amount")));
                }
            }
            Person p=new Person(name,birth,job,new Address(street,city,country),creditCards);
            everyPerson.add(p);
            creditCards.clear();
        }

        creditCards.clear();
//        System.out.println(everyPerson);
        //insert a person
        Address a=new Address("Polona","Bucuresti","Romania");
        String sql = "insert into jdbcex.address (street,city,country) values (\'"+a.getStreet()+"\',\'"+a.getCity()+"\',\'"+a.getCountry()+"\')";
        System.out.println(sql);
        PreparedStatement ps = con.prepareStatement(sql,
                Statement.RETURN_GENERATED_KEYS);

        ps.execute();

        rs = ps.getGeneratedKeys();
        int generatedKey = 0; //id ul adresei introduse
        if (rs.next()) {
            generatedKey = rs.getInt(1);
        }
        CreditCard c=new CreditCard("NET", "BEANS");
        creditCards.add(c);
        Person e=new Person("Netbeans","2018-05-01","programator...",a,creditCards);
        String insertPerson="insert into jdbcex.person (name,birthDate,job,fk_address) values (\'"+e.getName()+"\',\'"+e.getBirthDate()+"\',\'"+e.getJob()+"\',\'"+generatedKey+"\')";
        ps = con.prepareStatement(insertPerson,
                Statement.RETURN_GENERATED_KEYS);

        ps.execute();

        rs = ps.getGeneratedKeys();
        generatedKey = 0; //id ul personaei introduse
        if (rs.next()) {
            generatedKey = rs.getInt(1);
        }

        con.createStatement().executeUpdate("insert into jdbcex.creditCard (iban,amount,fk_person) values (\'"+creditCards.get(0).getIban()+"\',\'"+creditCards.get(0).getAmount()+"\',\'"+generatedKey+"\')");
        rs.close();
        selectCards.close();
        con.close();
    }

}