
import java.util.logging.Logger;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
/* import javax.sql.*; needed if using DataSource instead of DriverManager for DB-connections */

import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLClassLoader;

import java.util.Enumeration;
import java.util.List;
import java.util.ArrayList;

import java.lang.ClassNotFoundException;


public class DbPSearch
{
   private static final Logger LOGGER = Logger.getLogger(DbPSearch.class.getName());
   private DBConn dbConn;

   //private static final String DB_DRIVER = "org.postgresql.Driver";

   DbPSearch(DBConn dbConn)
   {
      this.dbConn = dbConn;
   }

   public String[] queryOverlapingPubdid(Coord coord, SubsurveyId subsurveyId)
   {
	   LOGGER.info("trace");

	   double lon = coord.lon;
	   double lat = coord.lat;
	   double radius = coord.radius;
	   double dlon = coord.dlon;
	   double dlat = coord.dlat;

	   boolean  vel_valid   = coord.vel_valid;
	   String   vel_type    = coord.vel_type;
	   double   vel_low     = coord.vel_low;
	   double   vel_up      = coord.vel_up;

	   String inputRegion = null;

	   if(coord.shape.equals("CIRCLE"))
	   {
		   inputRegion = "scircle '<(" + Double.toString(lon) + "d," + Double.toString(lat) + "d),"
			   + Double.toString(radius) + "d>'";
	   }
	   else if( coord.shape.equals("RECT") )
	   {
		   /*Vert vert[] = toVertices(lon, lat, dlon, dlat);

		     inputRegion = "spoly '{" 
		     + "(" + Double.toString(vert[0].lon) + "d," + Double.toString(vert[0].lat) + "d)," 
		     + "(" + Double.toString(vert[1].lon) + "d," + Double.toString(vert[1].lat) + "d)," 
		     + "(" + Double.toString(vert[2].lon) + "d," + Double.toString(vert[2].lat) + "d)," 
		     + "(" + Double.toString(vert[3].lon) + "d," + Double.toString(vert[3].lat) + "d)" 
		     + "}'";
		     */

		   /* South-West and North-East corners of a box */
		   String sw_lon = Double.toString(lon - dlon/2.0);
		   String sw_lat = Double.toString(lat - dlat/2.0);
		   String ne_lon = Double.toString(lon + dlon/2.0);
		   String ne_lat = Double.toString(lat + dlat/2.0);

		   inputRegion = "sbox '( ("+ sw_lon + "d, " + sw_lat + "d), (" + ne_lon +"d, " + ne_lat + "d) )'";
	   }
	   else
	   {
		   throw new IllegalArgumentException("Coord::shape was: " + coord.shape + " but valid is CIRCLE or RECT");
	   }


      String theQuery;
      if(coord.skySystem.equals("GALACTIC"))
      {
         theQuery ="SELECT obs_publisher_did FROM obscore WHERE (" + inputRegion + "  && polygon_region_galactic)";
      }
      else
      {
         theQuery ="SELECT obs_publisher_did FROM obscore WHERE (" + inputRegion + "  && polygon_region)";
      }

      if(vel_valid)
      {
         String vel_no_overlap = "((em_min > " + Double.toString(vel_up) + ") OR (em_max < " + Double.toString(vel_low) + "))";

         theQuery += " AND ( (NOT " + vel_no_overlap + ") OR (em_min is null) OR (em_max is null))";
         /* NOTE '... OR (em_min is null)' statement causes to include 2D-continuum datasets if they overlap in sky
          * It is the legacy-search behaviour - however is that useful ?
          */
      }

      if(subsurveyId != null)
      {
         /* FIXME replace this implementation with exact string match once survey_id is defined / added to obs_core */

         String addSS = "";
         if((subsurveyId.surveyName != null) && (subsurveyId.surveyName.length() > 0))
            addSS += "(obs_collection LIKE '" + subsurveyId.surveyName + "%')";

         if((subsurveyId.species != null)  && (subsurveyId.species.length() > 0) )
         {
            if(addSS.length() > 0) addSS += " OR ";
            addSS += "(obs_collection LIKE '%" + subsurveyId.species + "%')";
         }
         if((subsurveyId.transition != null) && (subsurveyId.transition.length() > 0)  )
         {
            if(addSS.length() > 0) addSS += " OR ";
            addSS += "(obs_collection LIKE '%" + subsurveyId.transition + "')";
         };

         if(addSS.length() > 0) theQuery += " AND (" + addSS + ")";
      }

      //theQuery += " ORDER BY obs_collection";

      LOGGER.info(theQuery);

      List<String> pubdidList = new ArrayList<>();

      LOGGER.info("Connecting to: " + dbConn.uri() + " with optional user/pwd: " + dbConn.userName() +" / "+ dbConn.password() );

      try( 
            Connection conn = DriverManager.getConnection(dbConn.uri(), dbConn.userName(), dbConn.password());
            Statement  st   = conn.createStatement();
            ResultSet  res  = st.executeQuery(theQuery);)
      {

         while (res.next())
         {
            String pubdid_str = res.getString("obs_publisher_did");
            pubdidList.add(pubdid_str);
         }

      }
      catch (SQLException se)
      {
         logSqlExInfo(se);
         se.printStackTrace();
      }

      String[] pubdidArr = pubdidList.toArray(new String[0]);

      LOGGER.info("pubdidArr[] length: " + pubdidArr.length);

      return pubdidArr;
   }




   public FormatResponseFilter.ObsCore[] queryOutputData(String[] pubdidArr, Coord coord/*, SubsurveyId subsurveyId*/)
   {
      LOGGER.info("");
      double lon = coord.lon;
      double lat = coord.lat;
      double radius = coord.radius;
      double dlon = coord.dlon;
      double dlat = coord.dlat;

      boolean  vel_valid   = coord.vel_valid;
      String   vel_type    = coord.vel_type;
      double   vel_low     = coord.vel_low;
      double   vel_up      = coord.vel_up;

      String inputRegion = null;

      if(coord.shape.equals("CIRCLE"))
      {
         inputRegion = "scircle '<(" + Double.toString(lon) + "d," + Double.toString(lat) + "d),"
            + Double.toString(radius) + "d>'";
      }
      else if( coord.shape.equals("RECT") )
      {
         /*Vert vert[] = toVertices(lon, lat, dlon, dlat);

           inputRegion = "spoly '{" 
           + "(" + Double.toString(vert[0].lon) + "d," + Double.toString(vert[0].lat) + "d)," 
           + "(" + Double.toString(vert[1].lon) + "d," + Double.toString(vert[1].lat) + "d)," 
           + "(" + Double.toString(vert[2].lon) + "d," + Double.toString(vert[2].lat) + "d)," 
           + "(" + Double.toString(vert[3].lon) + "d," + Double.toString(vert[3].lat) + "d)" 
           + "}'";
           */

         /* South-West and North-East corners of a box */
         String sw_lon = Double.toString(lon - dlon/2.0);
         String sw_lat = Double.toString(lat - dlat/2.0);
         String ne_lon = Double.toString(lon + dlon/2.0);
         String ne_lat = Double.toString(lat + dlat/2.0);

         inputRegion = "sbox '( ("+ sw_lon + "d, " + sw_lat + "d), (" + ne_lon +"d, " + ne_lat + "d) )'";
      }
      else
      {
         throw new IllegalArgumentException("Coord::shape was: " + coord.shape + " but valid is CIRCLE or RECT");
      }

      String commaSepPubdids  = String.join("\',\'", pubdidArr);


      //String theQuery ="SELECT dataproduct_type,obs_publisher_did,obs_collection,polygon_region_galactic,access_url,em_min,em_max," 
      String theQuery;
      if(coord.skySystem.equals("GALACTIC"))
      {
         theQuery ="SELECT *," 
            + inputRegion + " <@ polygon_region_galactic AS inputInsideDb, " 
            + inputRegion + " @> polygon_region_galactic AS dbInsideInput FROM obscore WHERE (obs_publisher_did IN (\'"+commaSepPubdids+"\'))";
      }
      else
      {
         theQuery ="SELECT *," 
            + inputRegion + " <@ polygon_region AS inputInsideDb, " 
            + inputRegion + " @> polygon_region AS dbInsideInput FROM obscore WHERE (obs_publisher_did IN (\'"+commaSepPubdids+"\'))";
      }

      theQuery += " ORDER BY obs_collection";

      //LOGGER.info(theQuery);

      List<FormatResponseFilter.ObsCore> obsCoreList = new ArrayList<>();

      LOGGER.info("Connecting to: " + dbConn.uri() + " with optional user/pwd: " + dbConn.userName() +" / "+ dbConn.password() );
      try( 
            Connection conn = DriverManager.getConnection(dbConn.uri(), dbConn.userName(), dbConn.password());
            Statement  st   = conn.createStatement();
            ResultSet  res  = st.executeQuery(theQuery);)
      {
         //ResultSet res = doQuery(theQuery);

         while (res.next())
         {
            FormatResponseFilter.ObsCore obsCore = new FormatResponseFilter.ObsCore();

            obsCore.dataproduct_type  = res.getString("dataproduct_type");
            obsCore.calib_level       = res.getInt("calib_level");
            obsCore.obs_collection    = res.getString("obs_collection");
            obsCore.obs_id            = res.getString("obs_id");
            obsCore.obs_publisher_did = res.getString("obs_publisher_did");

            obsCore.access_url     = res.getString("access_url");
            obsCore.access_format  = res.getString("access_format");
            obsCore.access_estsize = res.getLong("access_estsize");

            obsCore.target_name = res.getString("target_name");

            obsCore.s_ra         = res.getDouble("s_ra");
            obsCore.s_dec        = res.getDouble("s_dec");
            obsCore.s_fov        = res.getDouble("s_fov");
            obsCore.s_region     = res.getString("s_region");
            obsCore.s_xel1       = res.getLong("s_xel1");
            obsCore.s_xel2       = res.getLong("s_xel2");
            obsCore.s_resolution = res.getDouble("s_resolution");

            obsCore.t_min         = res.getDouble("t_min");
            obsCore.t_max         = res.getDouble("t_max");
            obsCore.t_exptime     = res.getDouble("t_exptime");
            obsCore.t_resolution  = res.getDouble("t_resolution");
            obsCore.t_xel         = res.getLong("t_xel");

            obsCore.em_min       = res.getDouble("em_min"); boolean em_min_valid = !res.wasNull();
            obsCore.em_max       = res.getDouble("em_max"); boolean em_max_valid = !res.wasNull();
            obsCore.em_valid     = em_min_valid && em_max_valid;;
            obsCore.em_res_power = res.getDouble("em_res_power");
            obsCore.em_xel       = res.getLong("em_xel");

            obsCore.o_ucd = res.getString("o_ucd");

            obsCore.pol_states = res.getString("pol_states");
            obsCore.pol_xel    = res.getLong("pol_xel");

            obsCore.facility_name = res.getString("facility_name");
            obsCore.instrument_name = res.getString("instrument_name");

            obsCore.vertices_str  = res.getString("polygon_region_galactic");
            obsCore.inputInsideDb = res.getBoolean("inputInsideDb");
            obsCore.dbInsideInput = res.getBoolean("dbInsideInput");

            obsCoreList.add(obsCore);
         }

         LOGGER.info("From DB collected # of ObsCore : " + obsCoreList.size());
      }
      catch (SQLException se)
      {
         logSqlExInfo(se);
         se.printStackTrace();
      }

      FormatResponseFilter.ObsCore[] cubes = obsCoreList.toArray(new FormatResponseFilter.ObsCore[0]);

      return cubes;
   }
   /*
      public static void loadDriver()// throws ClassNotFoundException
      {
   /* https://docs.oracle.com/javase/tutorial/jdbc/basics/connecting.html :
    * Any JDBC 4.0 drivers that are found in your class path are automatically loaded.
    * (However, you must manually load any drivers prior to JDBC 4.0 with the method
    * Class.forName.)
    * /
   /* OR
    * DriverManager.registerDriver(new org.postgresql.Driver());
    * LOGGER.info(getClasspathString());
    * LOGGER.info(getRegisteredDriverList());
    *i /
    try
    {
   //   Class.forName(DB_DRIVER);
    }
    catch (ClassNotFoundException e)
    {
    LOGGER.info("DB driver "+ DB_DRIVER +" not found: " + e.getMessage());
    e.printStackTrace();
    }
      }
      */
   /*
      private ResultSet doQuery(String theQuery) throws SQLException, ClassNotFoundException
      {
   /* https://docs.oracle.com/javase/tutorial/jdbc/basics/connecting.html :
    * Any JDBC 4.0 drivers that are found in your class path are automatically loaded.
    * (However, you must manually load any drivers prior to JDBC 4.0 with the method
    * Class.forName.)
    * /
   //    Class.forName(DB_DRIVER);
   /* OR
    * DriverManager.registerDriver(new org.postgresql.Driver());
    * LOGGER.info(getClasspathString());
    * LOGGER.info(getRegisteredDriverList());
    * /

    LOGGER.info("Connecting to: " + dbConn.uri() + " with optional user/pwd: " + dbConn.userName() +" / "+ dbConn.password() );

    Connection conn = DriverManager.getConnection(dbConn.uri(), dbConn.userName(), dbConn.password());

    Statement  st   = conn.createStatement();

    ResultSet  res  = st.executeQuery(theQuery);

    return res;
      }
      */
   /*   public Subsurvey[] getSurveyTable()
        {
        List<Subsurvey> survList = new ArrayList<Subsurvey>();

        String theQuery = "SELECT name,species,transition,rest_frequency,restf_fits_unit,velocity_fits_unit,description FROM surveys";


        LOGGER.info("Connecting to: " + dbConn.uri() + " with optional user/pwd: " + dbConn.userName() +" / "+ dbConn.password() );
        try( 
        Connection conn = DriverManager.getConnection(dbConn.uri(), dbConn.userName(), dbConn.password());
        Statement  st   = conn.createStatement();
        ResultSet  res  = st.executeQuery(theQuery);)
        {
   //ResultSet res = doQuery(theQuery);

   if(res == null)
   {
   LOGGER.info("Query yielded no resuls: " + theQuery);
   }
   else
   {
   while (res.next())
   {
   Subsurvey surv = new Subsurvey();
   surv.rf          = res.getDouble("rest_frequency");
   surv.surveyname  = res.getString("name");
   surv.species     = res.getString("species");
   surv.transition  = res.getString("transition");
   surv.rf_unit     = res.getString("restf_fits_unit");
   surv.vel_unit    = res.getString("velocity_fits_unit");
   surv.description = res.getString("description");

   survList.add(surv);
   }
   }
        }
        catch (SQLException se)
        {
        logSqlExInfo(se);
        se.printStackTrace();
        }
        return survList.toArray(new Subsurvey[0]);
        }
        */




   private void logSqlExInfo(SQLException se)
   {
      LOGGER.info("SQLState : " + se.getSQLState());
      LOGGER.info("ErrorCode: " + se.getErrorCode());
      LOGGER.info("Message  : " + se.getMessage());
      Throwable t = se.getCause();
      while(t != null) {
         LOGGER.info("Cause: " + t);
         t = t.getCause();
      }
   }



   private String getClasspathString()
   {
      StringBuffer classpath = new StringBuffer("getClasspathString:\r\n");
      ClassLoader applicationClassLoader = this.getClass().getClassLoader();
      if (applicationClassLoader == null) {
         applicationClassLoader = ClassLoader.getSystemClassLoader();
      }
      URL[] urls = ((URLClassLoader)applicationClassLoader).getURLs();
      for(int i=0; i < urls.length; i++) {
         classpath.append(urls[i].getFile()).append("\r\n");
      }

      return classpath.toString();
   }


   /* Returns the list of JDBC Drivers loaded by the caller's class loader */
   private String getRegisteredDriverList()
   {
      StringBuffer drvList = new StringBuffer("getRegisteredDriverList:\r\n");
      for (Enumeration e = DriverManager.getDrivers();
            e.hasMoreElements(); )
      {
         Driver d = (Driver) e.nextElement();
         String driverClass = d.getClass().getName();
         drvList.append(driverClass).append("\r\n");	
      }
      return drvList.toString();
   }



}
