private final GcsService gcsService = GcsServiceFactory.createGcsService(RetryParams.getDefaultInstance());

  private static final transient Logger log = LoggerFactory.getLogger(RosieCSVBlobServlet.class);

  private static final long serialVersionUID = 1L;

  List <Agency> agencyList;

  List <Household> householdList;

  List<Person> personList;

  List <Source> sourceList;

  BlobKey hudXMLURL;

  JAXBElement<?> sourcesJAXBElement;

  XMLInputFactory xmlif;

  Unmarshaller unmarshaller;

 

  static String[] csv1Record = new String[27];

  static String[] csv2Record = new String[27];

  static String[] csv3Record = new String[27];

  static String[] csv4Record = new String[27];

  static String[] csv5Record = new String[27];

  static String[] csv6Record = new String[27];

  static String[] csv7Record = new String[27];

  static String[][] csvRecords = {csv1Record, csv2Record, csv3Record, csv4Record, csv5Record, csv6Record, csv7Record };

//    "SSNFamilyHead", "RecordType_1","StreetAddress","Apartment","City","State",

//    "Zip5","StreetAddress","Apartment","City","State","Zip5","WorkTelephone",

//    "HomeTelephone","ReasonForCrisis_Code","FamilySize","HousingSituation_Code",

//    "LengthInHousingSituation_Code","NumberNeedingShelter","InsuranceType1_Code",

//    "InsuranceType2_Code","ChronicHomeless_Y_N","Prior_Loc_Y_N"};

//  static String[] csv2Record = {"AgencyCode","TransactionDate_mmddyy","TransactionTime_hhmmss",

//    "SSNFamilyHead","RecordType_2","FamilyMemberNumber","FamilyMemberSSN",

//    "Relationship_Code","FirstName","MiddleName","LastName","DateOfBirth_mmddyy",

//    "Gender_M_F","Race1_Code","Race2_Code","Hispanic_Y_N","Pregnant_Y_N",

//    "MonthsPregnant","NeedShelter_Y_N","Veteran_Code","StreetAddress","Apartment",

//    "City","State","Zip5","HousingSituation_Code","LengthInHousingSituation_Code"};

//  static String[] csv3Record = {"AgencyCode","TransactionDate_mmddyy","TransactionTime_hhmmss",

//    "SSNFamilyHead","RecordType_3","FamilyMemberNumber","Income_ExpenseType_Code",

//    "MonthlyAmount"};

//  static String[] csv4Record = {"AgencyCode","TransactionDate_mmddyy","TransactionTime_hhmmss",

//    "SSNFamilyHead","RecordType_4","FamilyMemberNumber","HealthProblem_Code",

//    "HealthProblemDescription"};

//  static String[] csv5Record = {"AgencyCode","TransactionDate_mmddyy","TransactionTime_hhmmss",

//    "SSNFamilyHead","RecordType_5","FamilyMemberNumber","Status_Code",

//    "Status_Description","FinancialAssistance_Amount","Destination_Code"};

//  static String[] csv6Record = {"AgencyCode","TransactionDate_mmddyy","TransactionTime_hhmmss",

//    "SSNFamilyHead","RecordType_6","FamilyMemberNumber","AssistanceProvided_Code",

//    "AssistanceProvided_Description","AssistanceProvided_Amount"};

//  static String[] csv7Record = {"AgencyCode","TransactionDate_mmddyy","TransactionTime_hhmmss",

//    "SSNFamilyHead","RecordType_7","DateIfMoveIn_mmddyy","DateIfMoveOut_mmddyy",

//    "Bed_UnitNumber","ReasonForLeaving_Code","Destination_Code"};

  int householdMemberCounterCSV2;

  public static final String BUCKETNAME = "rosie";

  public static final String FILENAME1 = "fileRosieCSV1FamilyInformation";

  public static final String FILENAME2 = "fileRosieCSV2FamilyMembers";

  public static final String FILENAME3 = "fileRosieCSV3FamilyIncomeAndExpense";

  public static final String FILENAME4 = "fileRosieCSV4MemberHealthProblems";

  public static final String FILENAME5 = "fileRosieCSV5ProgramsProvided";

  public static final String FILENAME6 = "fileRosieCSV6FamilyAssistanceProvided";

  public static final String FILENAME7 = "fileRosieCSV7MoveInMoveOutTransaction";

 

  public void init() {

    try {

      xmlif = XMLInputFactory.newInstance();

    } catch (Exception e){}

  }

  @Override

  public void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {   

    log.info("doGet in RosieCSVBlobServlet entered");

    //this needs to change to the GCS key in the future?

    hudXMLURL = new BlobKey(req.getParameter("hud_xml_url"));

    String[] blobStoreURL = new String[5];

    // check the completion state of each of the files by reading from the datastore

        // look at the last/most recent record (either in file or in datastore/cloud sql)

   

    // if file1 already started and not finished, resume file 1

      // get last marker processed from file or db?  what is the key?

      // continue, writing out each time

    HUDXML3BlobService hxbs = new HUDXML3BlobService();

    log.info("trying to get HUDXML3Blob object from Key String: " + hudXMLURL.getKeyString());

    HUDXML3Blob blob = hxbs.getHUDXML3BlobFromXMLKey(hudXMLURL.getKeyString());

    if (blob != null) {

      String id = blob.getId().toString();

      if (!recordExists(id)) {

        log.info("for HUD XML blob record: " + id + " nothing has been done, not even file 1 indexing, so start up first time.");

        startUpFirstTime(id);

      }

      else {

        log.info("HUD XML blob record: " + id + " already exists, so continuing from past session.");

        //everything keys of the household index

        if (!finishedConverting(id)) {

          convertHUDXMLToRosieCSV2(id);

          // set as finished in database

          blob = hxbs.getHUDXML3BlobFromXMLKey(hudXMLURL.getKeyString());

          blob.setCsvUrl2Completed(1);

          hxbs.updateHUDXML3Blob(blob);

        }

      }

      log.info("finished storing in db, now writing to a GCS file");

      writeToGoogleCloudStorage();

    }

    else {

      log.info("We have a problem, since the database didn't create an entry when uploading the HUDXML file");

    }

    //if no record for such a url, do all the things we always do to start things up as before

    //make response (this isn't really being used right now by the client, because of the long/unknown processing time and shutdowns

    resp.setHeader("Content-Type", "application/json");

    //create JSON response of blobStoreURL contents

    ObjectMapper mapper = new ObjectMapper();

    ArrayNode blobStoreURLs = mapper.createArrayNode();

    for (int i=0; i<blobStoreURL.length; i++) {

      blobStoreURLs.add(blobStoreURL[i]); 

    }

    log.info("Responding with blobStoreURLs.toString()" + blobStoreURLs.toString());

    resp.getOutputStream().println(blobStoreURLs.toString()); 

  }

 

  void startUpFirstTime(String id) {

    log.info("inside startUpFirstTime");

    //clear database of RosieIndexes

    log.info("calling delete all Rosie indexes");

    RosieIndexesService ris = new RosieIndexesService();

    ris.deleteAllRosieIndexes();

    startRecordsCreation();

    indexHouseholds(id);

    // set as finished in database

    HUDXML3BlobService hxbs = new HUDXML3BlobService();

    HUDXML3Blob blob = hxbs.getHUDXML3BlobFromXMLKey(hudXMLURL.getKeyString());

    //blob.setCsvUrl1Completed(1);

    //hxbs.updateHUDXML3Blob(blob);

    //blob = null;

    convertHUDXMLToRosieCSV2(id);

    blob = hxbs.getHUDXML3BlobFromXMLKey(hudXMLURL.getKeyString());

    blob.setCsvUrl2Completed(1);

    hxbs.updateHUDXML3Blob(blob);

    blob = null;

    log.info("finished processing in one pass!");

  }

 

  void startRecordsCreation() {

    // returns the blobstore urls for all of the to-be converted Rosie csv files, with headers 

    // the input string is the url of the uploaded input HUD XML file it needs to convert

    //clear records

    for (int i = 0; i < csvRecords.length; i++)

      for (int j=0; j < csvRecords[i].length; j++)

        csvRecords[i][j] = "";   

  }

  boolean recordExists(String id) {

    boolean result = false;

    HUDXML3BlobService hxbs = new HUDXML3BlobService();

    HUDXML3Blob blob = hxbs.getHUDXML3Blob(id);

    if (blob != null) {

      if (blob.getCsvUrl2Indexed() == 1) {

        result = true;

      }

    }

    return result;

  }

  String [] getNextIndexToProcess(String id) {

    log.info("inside getNextIndexToProcess");

    String[] nextIndexRecord = new String[2];

    //get next index for file from persistence

    EntityManager em;

    em = EMF.get().createEntityManager();

    String file_index_column = "file2index";

    //This needs to be improved, but it works fine.  It just doesn't go in true ascending order

    Query q = em.createQuery ("SELECT rosie FROM RosieIndexes rosie WHERE rosie.xmlBlobId = :xmlBlobId AND rosie." + file_index_column + " = (SELECT MIN(rosie." + file_index_column + ") FROM RosieIndexes rosie WHERE rosie.used = 0)");

   

    if (id != null) {

      q.setParameter ("xmlBlobId",(new BigInteger(id)));

      RosieIndexes r = null;

      try {

        @SuppressWarnings("unchecked")

        List<RosieIndexes> resultList = q.getResultList();

        for (RosieIndexes i : resultList) {

          log.info("RosieIndexes record ID returned in getNextIndexToProcess is: " + i.getId());

        }

        r = (RosieIndexes) q.getSingleResult();

      }

      catch (NoResultException nr) {

        log.info("no results from query.  wait, then try again.");

        try {

            Thread.sleep(70);

            r = (RosieIndexes) q.getSingleResult();

        } 

        catch (NoResultException nr1) {

          log.info("no results from query.  wait, then try again.");

          try {

            Thread.sleep(200);

            r = (RosieIndexes) q.getSingleResult();

          }

          catch (NoResultException nr2) {

            log.info("no results from query, even after waiting twice.");

            nextIndexRecord[0] = "-1";

            nextIndexRecord[1] = "-1";

            return nextIndexRecord;

           

          }

          catch (InterruptedException ie) {

            log.info("Timer issue: ");

            ie.printStackTrace();

          }

        }

        catch (InterruptedException ie) {

          log.info("Timer issue: ");

          ie.printStackTrace();

        }

      }

     

      String idResult = r.getId().toString();

      String indexResult = null;

      indexResult = r.getFile2index();

      nextIndexRecord[0] = idResult;

      nextIndexRecord[1] = indexResult;

      log.info("getNextIndexToProcess got next id: " + idResult + " and next index: " + indexResult);

      if (nextIndexRecord[0] == null || nextIndexRecord[0].isEmpty()) {

        nextIndexRecord[0] = "-1";

        nextIndexRecord[1] = "-1";

      }

    }

    return nextIndexRecord;

  }

 

  InputStream openXMLStream(BlobKey blobKeyHUDXML) {

    InputStream inputStream = null;

    try { 

      log.info("Using BlobstoreInputStream");

      inputStream = new BlobstoreInputStream(blobKeyHUDXML);

    } catch (Exception e) {

      log.info("Error reading from BlobstoreInputStream:" + e.toString());

    }     

    //unzip the input stream

    InputStream zis = null;

    try {

      zis = new BufferedInputStream(new GZIPInputStream(inputStream));

    } catch (IOException e) {

      log.info("Problem creating BufferedInputStream from GZIP" + e.toString());

    }

    return zis;

  }

 

  @SuppressWarnings("unchecked")

  boolean finishedIndexing(String id) {

    boolean status = false;

    //get status for file from persistence

    List<Integer> results = null;

    EntityManager em;

    em =EMF.get().createEntityManager();

    //String file_column = "csv_url2_indexed";

    String file_column = "csvUrl2Indexed";

    log.info("executing query: SELECT hblob." + file_column + " FROM HUDXML3Blob AS hblob WHERE hblob.id = :" + id);

    Query q = em.createQuery ("SELECT hblob." + file_column + " FROM HUDXML3Blob AS hblob WHERE hblob.id = :id");

    if (id != null) {

      Long idLong = Long.valueOf(id);

      q.setParameter ("id", idLong);

      try {

        Thread.sleep(500);

        results = q.getResultList();

      } catch (Exception e) {

        e.printStackTrace();

      }

    }

    if (results.get(0) == 1) {

      status = true;

    } else {log.warn("reindexing households, because the index wasn't detected as a '1'");}

    return status;

  }

  @SuppressWarnings("unchecked")

  boolean finishedConverting(String id) {

    boolean status = false;

    //get status for file from persistence

    List<Integer> results = null;

    EntityManager em;

    em =EMF.get().createEntityManager();   

    String file_column = "csvUrl2Completed";

    Query q = em.createQuery ("SELECT hblob." + file_column + " FROM HUDXML3Blob AS hblob WHERE hblob.id = :id");

    if (id != null) {

      q.setParameter ("id", Long.valueOf(id));

      //@SuppressWarnings("unchecked")

      results = q.getResultList();

    }

    if (results.get(0) == 1) {

      status = true;

    }

    return status;

  }

 

  void convertHUDXMLToRosieCSV2(String id) {

    //check if finished indexing

    if (finishedIndexing(id)) {

      log.info("it was determined that households have finished indexing");

      // pick an unprocessed index

      boolean keepRunning = true;

      while (keepRunning) {

        String[] nextIndexRecord = getNextIndexToProcess(id);

        String nextIndex = nextIndexRecord[1];

        if (nextIndex == null || nextIndex == "-1") {

          keepRunning = false;

        } else {

          processNextCSV2(nextIndex);

          //mark nextIndex as processed

          RosieIndexesService ris = new RosieIndexesService();

          RosieIndexes ri = ris.getRosieIndexes(nextIndexRecord[0]);

          ri.setUsed(1);

          ris.updateRosieIndexes(ri);

        }

      }

    }

    //else index it

    else {

      indexHouseholds(id);

      //recurse this method to process remaining indexes, and remember to save/write as you go

      convertHUDXMLToRosieCSV2(id);

    }

  }

  void indexHouseholds(String id) {

    log.info("indexing file 2");

    InputStream zis = openXMLStream(hudXMLURL);

   

    //JAXBContext jc = null;

    //Use StAX to get all the Households one for each File 2  Record

    XMLStreamReader xmlr0 = null;

    try {

      // set up a StAX reader

      xmlr0 = xmlif.createXMLStreamReader(zis);

      //Using StAX to get all the household ids for File 2 Household Membership Generation

      try {

        while(xmlr0.hasNext()) {

          // Get and check each SiteService for the right SiteServiceID

          xmlr0.next();

          if (xmlr0.isStartElement()) {

            // System.out.println("local name is: " + xmlr.getLocalName());

            if (xmlr0.getLocalName().equals("Household") && xmlr0.isStartElement()) {

              xmlr0.require(XMLStreamConstants.START_ELEMENT, null, "Household");

              // get the HouseholdID element following soon after this element

              while(xmlr0.hasNext()) {

                xmlr0.next();

                if (xmlr0.isStartElement()) {

                  if (xmlr0.getLocalName().equals("IDStr")) {

                    xmlr0.require(XMLStreamConstants.START_ELEMENT, null, "IDStr");

                    //save this index in db for later sequential processing

                    String index = xmlr0.getElementText();

                    RosieIndexes r = new RosieIndexes();

                    r.setXmlBlobId(new BigInteger(id));

                    r.setFile2index(index);

                    RosieIndexesService ris = new RosieIndexesService();

                    //log.info("file 2 being indexed");

                    ris.createRosieIndexes(r);

                    break;

                  }

                  else if (xmlr0.getLocalName().equals("IDNum")) {

                    xmlr0.require(XMLStreamConstants.START_ELEMENT, null, "IDNum");

                    //save this index in db for later sequential processing

                    String index = xmlr0.getElementText();

                    RosieIndexes r = new RosieIndexes();

                    r.setXmlBlobId(new BigInteger(id));

                    r.setFile2index(index);

                    RosieIndexesService ris = new RosieIndexesService();

                    //log.info("file 2 being indexed");

                    ris.createRosieIndexes(r);

                    break;

                  }

                }

              }

            }  

          }

        }

        //mark this file as indexed

        HUDXML3BlobService hxbs = new HUDXML3BlobService();

        log.info("hudXMLURL.getKeyString() for blob being searched for" + hudXMLURL.getKeyString());

        HUDXML3Blob blob = hxbs.getHUDXML3BlobFromXMLKey(hudXMLURL.getKeyString());

        blob.setCsvUrl2Indexed(1);

        hxbs.updateHUDXML3Blob(blob); 

      } catch (Exception e) {

        log.info("XML Stream exception caught: " + e.toString());

      }

      xmlr0.close();

      zis.close();

    } catch (Exception e) {

      log.info("exception caught creating StAX stream " + e.toString());

    }

  }

 

  void convertHouseholdToRosieCSV2(Household household) {

    // populate ROSIE CSV file 2 "Household Information" with each person that is a household member

    // each household member represents one record in Rosie CSV #2

    log.info("inside convertHouseholdtoRosieCSV()");

    //Use JAXB to get the Head of Household's ID

    BigInteger headOfHouseholdID = household.getHeadOfHouseholdID().getIDNum();

    //headOfHouseholdIDList = (NodeList) xpath.evaluate("/Sources/Source/Export/Household[HouseholdID/IDStr/text() = \"" + hid+"\"]/HeadOfHouseholdID/IDNum", doc, XPathConstants.NODESET);

    //log.info("Exception while getting the head of household's id for household " + hid + ": " + e.toString());

    String sSNFamilyHeadCSV2 = null;

    householdMemberCounterCSV2 = 1;

    //look up the head of household's SSN

    sSNFamilyHeadCSV2 = getHeadOfHouseholdSSNFromHousehold(household);

    log.info("Placing \'" + sSNFamilyHeadCSV2 + "\' into csv2Record[3]");

    if (!sSNFamilyHeadCSV2.isEmpty() && sSNFamilyHeadCSV2 != null) {

      csv2Record[3] = sSNFamilyHeadCSV2;

    } else {log.info("Problem getting head of household SSN");}

   

    //No mapping yet for RecordType_2

    csv2Record[4] = "2";

   

    String hh_id;

    if (household.getHouseholdID().getIDStr() != null) {

      hh_id = household.getHouseholdID().getIDStr();

    } else if (household.getHouseholdID().getIDNum() != null) {

      hh_id = household.getHouseholdID().getIDNum().toString();

    } else {log.info("couldn't get household id in convertHouseholdToRosieCSV2"); hh_id = "";}

    log.info("calling processHouseholdMembersToRosie for household: " + hh_id);

   

    // Because, in this particular XML, the Head Of Household is not listed as a household member (which it should be, also process that person separately.

    processMembersAndHeadsOfHouseholds(household, headOfHouseholdID);

    // then process all the members

    processHouseholdMembersToRosie(household);

   

    // reset the common household values back to empty strings, so they don't enter into

    //    the next household record

    csv2Record[3] = "";

  }

 

  void processNextCSV2(String nextIndex) {

    log.info("inside processNextCSV2.  nextIndex is: " + nextIndex);

   

    //Use StAX to get Households

    JAXBContext jc = null;

    try {

      jc = JAXBContext.newInstance(Household.class);

    } catch (JAXBException e) {

      log.info("JAXB Household.class context problem: " + e.toString());

      e.printStackTrace();

    }

    try {

      unmarshaller = jc.createUnmarshaller();

    } catch (JAXBException e) {

      log.info("Problem unmarshalling Household context: " + e.toString());

      e.printStackTrace();

    }

   

    try {

       InputStream zis = openXMLStream(hudXMLURL);

      XMLStreamReader xmlr0 = null;

      // set up a StAX reader to find the nextIndex within the XML

      xmlr0 = xmlif.createXMLStreamReader(zis);     

      while(xmlr0.hasNext()) {

        //Get each household

        xmlr0.next();

        if (xmlr0.isStartElement()) {

          //log.info("name is:" + xmlr1.getLocalName());

          if (xmlr0.getLocalName().equals("Household")) {

            xmlr0.require(XMLStreamConstants.START_ELEMENT, null, "Household");

            JAXBElement<Household> householdJAXB = unmarshaller.unmarshal(xmlr0,Household.class);

            Household householdElement = (Household)householdJAXB.getValue();

            if (householdElement.getHouseholdID().getIDStr() != null) {

              if ((householdElement.getHouseholdID().getIDStr()).equals(nextIndex)) {

                log.info("Now converting household: " + householdElement.getHouseholdID().getIDStr());

                try {

                  convertHouseholdToRosieCSV2(householdElement);

                } catch (Exception e) {log.info("Problem converting household: " + householdElement.getHouseholdID().getIDStr() + " " + e.toString()); e.printStackTrace();}

                break;

              }

            }

            else if (householdElement.getHouseholdID().getIDNum() != null) {

              if ((householdElement.getHouseholdID().getIDNum().toString()).equals(nextIndex)) {

                log.info("Now converting household: " + householdElement.getHouseholdID().getIDNum());

                try {

                  convertHouseholdToRosieCSV2(householdElement);

                } catch (Exception e) {log.info("Problem converting household: " + householdElement.getHouseholdID().getIDNum() + " " + e.toString()); e.printStackTrace();}

                break;

              }

            }

            else {log.info("Couldn't get household ID in processNextCSV2");}

          }

        }

      }

    } catch (Exception e) {

      log.info("XML Stream exception caught: " + e.toString());

      e.printStackTrace();

    }

  }

 

  void processFundingSourceServiceEvent(SiteServiceParticipation ssp, Household household) {

    //log.info("inside processFundingSourceServiceEvent()");

    String transactionDateCSV6 = null;

    String transactionTimeCSV6 = null;

    String AssistanceProvidedAmountCSV6 = null;

    for (int i=0; i<csv6Record.length; i++) {

      csv6Record[i]="";

    }

    //Get all of this household's fundingSources

    //"/Sources/Source/Export/Person/SiteServiceParticipation[HouseholdID/IDNum/text() = \"" + hid + "\"]/ServiceEvent/FundingSources/FundingSource", doc, XPathConstants.NODESET));

    if (!(ssp.getServiceEvent().isEmpty())) {   

      for (ServiceEvent serviceEvent : ssp.getServiceEvent()) {

        for (FundingSourceServiceEvent fs : serviceEvent.getFundingSources().getFundingSource()) {

          //For each funding source, write a record.

          //Each record starts with an agency

          //Look up the service key, since we use that instead of agency

          csv6Record[0] = csv1Record[0];