import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.PrintWriter;


import java.sql.*;
import java.util.*;




public class zone {
     List <model> listOfBlogs;

  public zone()
  {

    Connection conn = null;
    listOfBlogs = new ArrayList<model>();


    // connect to the database

        conn = connectToDatabaseOrDie();


    // get the data
    populateListOfTopics(conn, listOfBlogs);

   //print the data
   printTopics(listOfBlogs);



  }


  public List<model> printTopics(List<model> listOfBlogs)
  {
    Iterator<model> it = listOfBlogs.iterator();
    while (it.hasNext())
    {
        model blog = (model)it.next();
      System.out.println( blog.day + "  " + blog.hour +" " + blog.zone_id);
    }
    return listOfBlogs;
  }


private void populateListOfTopics(Connection conn, List<model> listOfBlogs)
  {
    try
    {
        for(int j=9;j<10;j++){
       for(int i=10;i<12;i++){

        String sql= "SELECT day,hour,zone_id FROM public.zone_block_table where day=? and hour=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1,j);
        pstmt.setInt(2,i);


        ResultSet rs = pstmt.executeQuery();



      while ( rs.next() )
      {
         model blog = new model();

        blog.day = rs.getInt ("day");
        blog.hour=rs.getInt ("hour");
        blog.zone_id  = rs.getInt ("zone_id");



        listOfBlogs.add(blog);

      }
      convertToCsv(rs,i,j);

      rs.close();
      pstmt.close();

      }
    }
    }
    catch (SQLException se) {
      System.err.println("Threw a SQLException creating the list of state.");
      System.err.println(se.getMessage());
    } catch (Exception e) {
        System.out.println("Err");
        e.printStackTrace();
    }
  }




  private Connection connectToDatabaseOrDie()
  {
    Connection conn = null;
    try
    {
      Class.forName("org.postgresql.Driver");
      String url = "jdbc:postgresql://localhost:5432/mvc_data_base";
      conn = DriverManager.getConnection(url,"postgres", "122333");
    }
    catch (ClassNotFoundException e)
    {
      e.printStackTrace();



    }
    catch (SQLException e)
    {
      e.printStackTrace();

    }
    return conn;
  }

  public  void convertToCsv(ResultSet rs,int k,int l) throws SQLException, FileNotFoundException {
        PrintWriter csvWriter = new PrintWriter(new File("state_"+k+"_"+l+".csv")) ;
        ResultSetMetaData meta = rs.getMetaData() ;
        int numberOfColumns = meta.getColumnCount() ;
        String dataHeaders = """ + meta.getColumnName(1) + """ ;
        for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) {
                dataHeaders += ","" + meta.getColumnName(i) + """ ;
        }
        csvWriter.println(dataHeaders) ;
        while (rs.next()) {
            String row = """ + rs.getString(1) + """  ;
            for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) {
                row += ","" + rs.getString(i) + """ ;
            }
        csvWriter.println(row) ;
        }
        csvWriter.close();
    }





}