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 DbObstap
{
   private static final Logger LOGGER = Logger.getLogger(DbObstap.class.getName());
   private DbConnArgs dbConnArgs;


   DbObstap(DbConnArgs dbConnArgs)
   {
      this.dbConnArgs = dbConnArgs;
   }


   static class Obstap
   {
      String dataproduct_type;
      Integer calib_level;
      String obs_collection;
      String obs_title;
      String obs_id;
      String obs_publisher_did;
      String bib_reference;
      String data_rights;

      String access_url;
      String access_format;
      Long access_estsize;

      String target_name;

      Double s_ra, s_dec, s_fov;
      String s_region;
      Long s_xel1, s_xel2;
      Double s_resolution;

      Double t_min, t_max, t_exptime, t_resolution;
      Long t_xel;

      Double em_min, em_max, em_res_power;
      Long em_xel;
      boolean em_valid;

      String o_ucd;

      String pol_states;
      Long   pol_xel;

      String facility_name;
      String instrument_name;

      // VLKB extesnion

      Integer overlapCodeSky;
      Integer overlapCodeVel;
      Integer overlapCode;

      String s_region_galactic;
      Double vel_min, vel_max;
   }




   public String[] queryOverlapingPubdid(QueryArgs qArgs)
      throws Exception
   {
      LOGGER.fine("trace");

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

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

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

         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(qArgs.collection != null)
      {
         String addColl = "";
         if(qArgs.collection.length() > 0)
            addColl += "(obs_collection LIKE '%" + qArgs.collection + "%')";
      
         if(addColl.length() > 0) theQuery += " AND (" + addColl + ")";
      }

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

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

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

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

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

      LOGGER.fine(theQuery);

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

      LOGGER.fine("Connecting to: " + dbConnArgs.uri() + " with user: " + dbConnArgs.userName() );

      try( 
            Connection conn = DriverManager.getConnection(dbConnArgs.uri(), dbConnArgs.userName(), dbConnArgs.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)
      {
         dbError(se);
         // se.printStackTrace();
         throw new Exception(se.toString());
      }

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

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

      return pubdidArr;
   }

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

         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 Obstap[] queryOutputData(String[] pubdidArr, Pos pos, Band band)
      throws Exception
   {
      LOGGER.fine("trace");

      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+"\'))";

      List<Obstap> obstapList = new ArrayList<>();

      LOGGER.fine(theQuery);

      LOGGER.fine("Connecting to: " + dbConnArgs.uri()
            + " with optional user/pwd: " + dbConnArgs.userName() +" / "+ dbConnArgs.password() );
      try(
            Connection conn = DriverManager.getConnection(dbConnArgs.uri(), dbConnArgs.userName(), dbConnArgs.password());
            Statement  st   = conn.createStatement();
            ResultSet  res  = st.executeQuery(theQuery);)
      {
         while (res.next())
         {
            Obstap obstap = new Obstap();

            obstap.dataproduct_type  = this.getString(res,"dataproduct_type");
            obstap.calib_level       = this.getInt(res,"calib_level");
            obstap.obs_collection    = this.getString(res,"obs_collection");
            obstap.obs_title         = this.getString(res,"obs_title");
            obstap.obs_id            = this.getString(res,"obs_id");
            obstap.obs_publisher_did = this.getString(res,"obs_publisher_did");

            obstap.bib_reference = this.getString(res,"bib_reference");
            obstap.data_rights   = this.getString(res,"data_rights");

            obstap.access_url     = this.getString(res,"access_url");
            obstap.access_format  = this.getString(res,"access_format");
            obstap.access_estsize = this.getLong(res,"access_estsize");

            obstap.target_name = this.getString(res,"target_name");

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

            obstap.t_min         = this.getDouble(res,"t_min");
            obstap.t_max         = this.getDouble(res,"t_max");
            obstap.t_exptime     = this.getDouble(res,"t_exptime");
            obstap.t_resolution  = this.getDouble(res,"t_resolution");
            obstap.t_xel         = this.getLong(res,"t_xel");

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

            obstap.o_ucd = this.getString(res,"o_ucd");

            obstap.pol_states = this.getString(res,"pol_states");
            obstap.pol_xel    = this.getLong(res ,"pol_xel");

            obstap.facility_name = this.getString(res,"facility_name");
            obstap.instrument_name = this.getString(res,"instrument_name");

            // VLKB extension

            obstap.s_region_galactic = this.getString(res,"s_region_galactic");
            obstap.vel_min           = this.getDouble(res,"vel_min"); //boolean em_min_valid = !res.wasNull();
            obstap.vel_max           = this.getDouble(res,"vel_max"); //boolean em_max_valid = !res.wasNull();

            boolean inputInsideDb = res.getBoolean("inputInsideDb");
            boolean dbInsideInput = res.getBoolean("dbInsideInput");
            obstap.overlapCodeSky = convertToOverlapCodeSky(inputInsideDb, dbInsideInput);

            obstap.overlapCodeVel = convertToOverlapCodeVel(band,obstap.em_valid,obstap.em_min,obstap.em_max);

            obstap.overlapCode    = convertToOverlapCode(obstap.overlapCodeSky, obstap.overlapCodeVel);


            obstapList.add(obstap);
         }

         LOGGER.fine("From DB collected # of Obstap : " + obstapList.size());
      }
      catch (SQLException se)
      {
         dbError(se);
         // se.printStackTrace();
         throw new Exception(se.toString());
      }

      Obstap[] cubes = obstapList.toArray(new Obstap[0]);

      return cubes;
   }

   /* convert overlap codes */

   private Integer convertToOverlapCodeSky(boolean inpInDb, boolean dbInInp)
   {   
      if(!inpInDb && !dbInInp) return 4; // parial overlap
      else if( inpInDb && !dbInInp) return 3; // input region completely inside fits-datacube
      else if(!inpInDb &&  dbInInp) return 2; // datacube completely inside input-region
      else return 5; // exact match: both inpInDb dbInInp are true
   }

   private Integer convertToOverlapCodeVel(Band band, boolean v_valid, double v_min, double v_max)
   {
      if((band != null) && v_valid)
      {
         if(band.system == Band.System.VELO_LSRK)
         {
            // FIXME assert qArgs: vel_min <= vel_max
            // FIXME assert cube:  v_min   <= v_max

            boolean dbInInp = (band.min <= v_min) && (v_min <= band.max)
               && (band.min <= v_max) && (v_max <= band.max);

            boolean inpInDb = (v_min <= band.min) && (band.min <= v_max)
               && (v_min <= band.max ) && (band.max <= v_max);

            return convertToOverlapCodeSky(inpInDb, dbInInp);

         }
         else return null;// FIXME other v_type NotImplemented yet
      }

      return null;
   }

   private Integer convertToOverlapCode(Integer ovcSky, Integer ovcVel)
   {
      if(ovcVel == null) return ovcSky; // 2D images
      else if(ovcSky == ovcVel) return ovcSky;
      else return 4;// partial overlap
   }


   /* conversions tolerate missing columns */

   private Integer getInt(ResultSet res, String colLabel)
   {
      try
      {
         Integer value = res.getInt(colLabel);
         return (res.wasNull() ? null : value);
      }
      catch(SQLException se)
      {
         dbError(se);
         return null;
      }
   }

   private Long getLong(ResultSet res, String colLabel)
   {
      try
      {
         Long value = res.getLong(colLabel);
         return (res.wasNull() ? null : value);
      }
      catch(SQLException se)
      {
         dbError(se);
         return null;
      }
   }

   private Double getDouble(ResultSet res, String colLabel)
   {
      try
      {
         Double value = res.getDouble(colLabel);
         return (res.wasNull() ? null : value);
      }
      catch(SQLException se)
      {
         dbError(se);
         return null;
      }
   }

   private String getString(ResultSet res, String colLabel)
   {
      try
      {
         String value = res.getString(colLabel);
         return (res.wasNull() ? null : value);
      }
      catch(SQLException se)
      {
         dbError(se);
         return null;
      }
   }



   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("Pos::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 dbError(SQLException se)
   {
      LOGGER.fine("SQLState : " + se.getSQLState());
      LOGGER.fine("ErrorCode: " + se.getErrorCode());
      LOGGER.warning("Message: " + se.getMessage());
      Throwable t = se.getCause();
      while(t != null) {
         LOGGER.fine("Cause: " + t);
         t = t.getCause();
      }
   }



}