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;

import vo.parameter.*;

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


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




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

      String inputRegion = toPgSphereSqlTypeString(coord.pos);
      String dbRegion    = toRegionColumnName(coord.pos.system);

      String theQuery = "SELECT obs_publisher_did FROM obscore WHERE ("+inputRegion+"  && " + dbRegion + ")";

      boolean  vel_valid = (coord.band != null) && (coord.band.system != Band.System.NONE);
      if(vel_valid)
      {
         String prefix = toSpecColumnNamePrefix(coord.band.system);
         String vel_no_overlap
            = "((" + prefix + "_min > " + Double.toString(coord.band.getMax())
            + ") OR (" + prefix + "_max < " + Double.toString(coord.band.getMin()) + "))";

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

      if(coord.collection != null)
      {
         String addColl = "";
         if(coord.collection.length() > 0)
            addColl += "(obs_collection LIKE '%" + coord.collection + "%')";
      
         if(addColl.length() > 0) theQuery += " AND (" + addColl + ")";
      }

      theQuery += appendIntervalConstraint(coord.fov,     "s_fov");
      theQuery += appendIntervalConstraint(coord.spatres, "s_resolution");
      theQuery += appendIntervalConstraint(coord.specrp,  "em_res_power");
      theQuery += appendIntervalConstraint(coord.exptime, "t_exptime");
      theQuery += appendIntervalConstraint(coord.timeres, "t_resolution");

      theQuery += appendStringMatchConstraint(coord.id,         "obs_publisher_did");
      theQuery += appendStringMatchConstraint(coord.facility,   "facility_name");
      theQuery += appendStringMatchConstraint(coord.instrument, "instrument_name");
      theQuery += appendStringMatchConstraint(coord.dptype,     "dataproduct_type");

      theQuery += appendStringMatchConstraint(coord.target, "target_name");
      theQuery += appendStringMatchConstraint(coord.format, "access_format");

      if(coord.calib != null)
         theQuery += " AND (" + coord.calib + " = calib_level)";

      if(coord.maxrec != null)
         theQuery += " LIMIT " + coord.maxrec;


      //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;
   }

   private String appendIntervalConstraint(Interval interval, String colName)
   {
      if(interval != null)
      {
         String no_overlap
            = "((" + colName + " > " + Double.toString(interval.getMax())
            + ") OR (" + colName + " < " + Double.toString(interval.getMin()) + "))";

         return " AND ( (" + colName + " is null) OR (NOT " + no_overlap + "))";
      }
      else
      {
         return "";
      }
   }

   private String appendStringMatchConstraint(String str, String colName)
   {
      if(str != null)
      {
         return " AND ( (" + colName + " is null) OR ('" + str.trim() + "' = " + colName + "))";
      }
      else
      {
         return "";
      }
   }



   public FormatResponseFilter.ObsCore[] queryOutputData(String[] pubdidArr, Pos pos)
   {
      LOGGER.info("");

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

      String inputRegion = toPgSphereSqlTypeString(pos);
      String dbRegion    = toRegionColumnName(pos.system);

      String theQuery ="SELECT *," 
         + inputRegion + " <@ " + dbRegion + " AS inputInsideDb, " 
         + inputRegion + " @> " + dbRegion + " 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);)
      {
         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_region_galactic = res.getString("s_region_galactic");
            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.vel_min      = res.getDouble("vel_min"); //boolean em_min_valid = !res.wasNull();
            obsCore.vel_max      = res.getDouble("vel_max"); //boolean em_max_valid = !res.wasNull();

            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;
   }


   private String toPgSphereSqlTypeString(Pos pos)
   {
      double lon;
      double lat;
      double radius;
      double dlon;
      double dlat;

      String inputRegion = null;

      switch(pos.shape)
      {
         case CIRCLE:
            lon = pos.circle.lon;
            lat = pos.circle.lat;
            radius = pos.circle.radius;
            inputRegion = "scircle '<(" + Double.toString(lon) + "d," + Double.toString(lat) + "d),"
               + Double.toString(radius) + "d>'";
            break;

         case RANGE:
            lon =  (pos.range.lon1 + pos.range.lon2)/2.0;
            lat =  (pos.range.lat1 + pos.range.lat2)/2.0;
            dlon = (pos.range.lon2 - pos.range.lon1)/2.0;
            dlat = (pos.range.lat2 - pos.range.lat1)/2.0;

            /* 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) )'";
            break;

         case POLYGON:
            // FIXME redefine Polygon as point-array:
            assert(pos.polygon.lon.length == pos.polygon.lat.length);

            // Polygon has at least 3 points
            inputRegion = "spoly '( (" + pos.polygon.lon[0] + "d, " + pos.polygon.lat[0] + "d),";
            for(int ii=1; ii < pos.polygon.lon.length; ii++)
            {
               inputRegion += ", (" + pos.polygon.lon[ii] + "d, " + pos.polygon.lat[ii] + "d)";
            }
            inputRegion += " )'";
            break;

         default:
            throw new IllegalArgumentException("Coord::shape was: " + pos.shape.toString()
                  + " but valid is CIRCLE or RANGE or POLYGON");
      }

      return inputRegion;
   }

   private String toRegionColumnName(Pos.System system)
   {
      String dbRegion;
      switch(system)
      {
         case GALACTIC:
            dbRegion = "polygon_region_galactic";
            break;
         default:
            dbRegion = "polygon_region";
      }
      return dbRegion;
   }

   private String toSpecColumnNamePrefix(Band.System system)
   {
      // vlkb-volib/Band.System: WAVE_Barycentric, VELO_LSRK, GRID, NONE
      String prefix;
      switch(system)
      {
         case VELO_LSRK: prefix = "vel"; break;
         default: prefix = "em";
      }
      return prefix;
   }


   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();
      }
   }



}