import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;

public class Main {

    private final String url ="jdbc:postgresql://www.studenti.famnit.upr.si/banana_melone";
    private final String user = "goran_tubic";
    private final String password = "12qw34er";

    public Connection connect() throws SQLException{
        return DriverManager.getConnection(this.url,this.user,this.password);
    }

    public void izpisiMesta(ResultSet rs){
        try {
            while(rs.next()){
                System.out.println(rs.getInt("id") + ". " +rs.getString("name") + " s populacijo: " + rs.getString("population"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public void slovenskaMesta(){
        String query = "SELECT id, name, population FROM CITY WHERE countryCode =?;";
        try {
            Connection connection = connect();
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setString(1, "SVN"); //zaradi injections
            ResultSet rs = statement.executeQuery();
//          rs.next(); //gres na prvo vrstico z rezultati
            izpisiMesta(rs);
            connection.close();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }

    public void dodajMesta() throws IOException, FileNotFoundException{
        //create sequence city_id_seq start 4079
        //alter table city alter id set default nextval('city_id_seq'); //nastavi field na auto increment oz na 'city_id_seq'
        try {
            Connection connection = connect();
            BufferedReader br = new BufferedReader(new FileReader("mesta.txt"));
            String sql = "INSERT INTO city(name,population,district, countryCode) Values(?,?,?,'SVN')";
            PreparedStatement statement = connection.prepareStatement(sql);
            while(true){
                String vrstica = br.readLine();
                if(vrstica == null) break;
                String [] tok = vrstica.trim().split(",");
                statement.setString(1, tok[0]);
                statement.setInt(2, Integer.parseInt(tok[1]));
                statement.setString(3, tok[2]);
                statement.addBatch();
            }
            statement.execute();
            br.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }


    public int numberOfCities(){
        String query = "SELECT COUNT(*) FROM CITY;";
        int count = 0;
        try {
            Connection connection = connect();
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            rs.next(); //gres na prvo vrstico z rezultati
            count = rs.getInt(1);
            connection.close();
            return count;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }

        return -1;
    }


    public static void main(String[] args) {
        Main main = new Main();
        System.out.println(main.numberOfCities());
        main.slovenskaMesta();

    }

}