# Path: tkp/db/model.py
# class Frequencyband(Base):
#     __tablename__ = 'frequencyband'
# 
#     id = Column(Integer, seq_frequencyband, primary_key=True,
#                 server_default=seq_frequencyband.next_value())
#     dataset_id = Column('dataset', Integer, ForeignKey('dataset.id'),
#                         nullable=False, index=True)
#     dataset = relationship('Dataset', backref=backref('frequencybands', cascade="all,delete"))
#     freq_central = Column(Double)
#     freq_low = Column(Double)
#     freq_high = Column(Double)
# 
# class Skyregion(Base):
#     __tablename__ = 'skyregion'
# 
#     id = Column(Integer, seq_skyregion, primary_key=True,
#                 server_default=seq_skyregion.next_value())
# 
#     dataset_id = Column('dataset', ForeignKey('dataset.id'), nullable=False, index=True)
#     dataset = relationship('Dataset',
#                            backref=backref('skyregions',cascade="all,delete"))
# 
#     centre_ra = Column(Double, nullable=False)
#     centre_decl = Column(Double, nullable=False)
#     xtr_radius = Column(Double, nullable=False)
#     x = Column(Double, nullable=False)
#     y = Column(Double, nullable=False)
#     z = Column(Double, nullable=False)
# 
# class Image(Base):
#     __tablename__ = 'image'
# 
#     id = Column(Integer, seq_image, primary_key=True,
#                 server_default=seq_image.next_value())
# 
#     dataset_id = Column('dataset', Integer, ForeignKey('dataset.id'), nullable=False, index=True)
#     dataset = relationship('Dataset', backref=backref('images', cascade="delete"))
# 
#     band_id = Column('band', ForeignKey('frequencyband.id'), nullable=False, index=True)
#     band = relationship('Frequencyband', cascade="delete")
# 
#     skyrgn_id = Column('skyrgn', Integer, ForeignKey('skyregion.id'), nullable=False, index=True)
#     skyrgn = relationship('Skyregion', backref=backref('images', cascade="delete"))
# 
#     tau = Column(Integer)
#     stokes = Column(SmallInteger, nullable=False, server_default=text("1"))
#     tau_time = Column(Double)
#     freq_eff = Column(Double, nullable=False)
#     freq_bw = Column(Double)
#     taustart_ts = Column(DateTime, nullable=False, index=True)
#     rb_smaj = Column(Double, nullable=False)
#     rb_smin = Column(Double, nullable=False)
#     rb_pa = Column(Double, nullable=False)
#     deltax = Column(Double, nullable=False)
#     deltay = Column(Double, nullable=False)
#     fwhm_arcsec = Column(Double)
#     fov_degrees = Column(Double)
#     rms_qc = Column(Double, nullable=False)
#     rms_min = Column(Double)
#     rms_max = Column(Double)
#     detection_thresh = Column(Double)
#     analysis_thresh = Column(Double)
#     url = Column(String(1024))
#     node = Column(SmallInteger, nullable=False, server_default=text("1"))
#     nodes = Column(SmallInteger, nullable=False, server_default=text("1"))
# 
#     data = relationship("ImageData", uselist=False, back_populates="image")
# 
# class Dataset(Base):
#     __tablename__ = 'dataset'
# 
#     id = Column(Integer, seq_dataset, server_default=seq_dataset.next_value(), primary_key=True)
#     rerun = Column(Integer, nullable=False, server_default=text("0"))
#     type = Column(SmallInteger, nullable=False, server_default=text("1"))
#     process_start_ts = Column(DateTime, nullable=False)
#     process_end_ts = Column(DateTime)
#     detection_threshold = Column(Double)
#     analysis_threshold = Column(Double)
#     assoc_radius = Column(Double)
#     backsize_x = Column(SmallInteger)
#     backsize_y = Column(SmallInteger)
#     margin_width = Column(Double)
#     description = Column(String(100), nullable=False)
#     node = Column(SmallInteger, nullable=False, server_default=text("1"))
#     nodes = Column(SmallInteger, nullable=False, server_default=text("1"))
# 
# Path: tkp/utility/coordinates.py
# def eq_to_cart(ra, dec):
#     """Find the cartesian co-ordinates on the unit sphere given the eq. co-ords.
# 
#         ra, dec should be in degrees.
#     """
#     return (math.cos(math.radians(dec)) * math.cos(math.radians(ra)),  # Cartesian x
#             math.cos(math.radians(dec)) * math.sin(math.radians(ra)),  # Cartesian y
#             math.sin(math.radians(dec)))  # Cartesian z


import math
from datetime import datetime
from tkp.db.model import Frequencyband, Skyregion, Image, Dataset
from tkp.utility.coordinates import eq_to_cart
from sqlalchemy import func, cast
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION as Double
                            AND sky.centre_decl + sky.xtr_radius
         AND (  (rc.x - sky.x) * (rc.x - sky.x)
                + (rc.y - sky.y) * (rc.y - sky.y)
                + (rc.z - sky.z) * (rc.z - sky.z)
             ) < %(inter_sq)s
      ;
    """ % {'inter_sq': inter_sq, 'skyregion_id': skyregion.id}
    session.execute(q)
    return inter


def get_skyregion(session, dataset, centre_ra, centre_decl, xtr_radius):
    """
     gets an id for a skyregion, given a pair of central co-ordinates and a radius.

     If no matching skyregion is found, a new one is inserted. In this case we also trigger execution of
     `updateSkyRgnMembers` for the new skyregion - this performs a simple assocation with current members of the
     runningcatalog to find sources that should be visible in the new skyregion, and updates the assocskyrgn table
     accordingly.

     args:
        session (sqlalchemy.orm.session.Session): a SQLAlchemy session object
        dataset_id (int): the dataset ID
        centre_ra (float): center RA coordinate
        centre_decl (float): center DECL coordinate
        xtr_radius (float): The extraction radius

     returns:
        tkp.db.models.Skyregion: a SQLalchemy skyregion
     """

# Path: tkp/db/model.py
# class Frequencyband(Base):
#     __tablename__ = 'frequencyband'
# 
#     id = Column(Integer, seq_frequencyband, primary_key=True,
#                 server_default=seq_frequencyband.next_value())
#     dataset_id = Column('dataset', Integer, ForeignKey('dataset.id'),
#                         nullable=False, index=True)
#     dataset = relationship('Dataset', backref=backref('frequencybands', cascade="all,delete"))
#     freq_central = Column(Double)
#     freq_low = Column(Double)
#     freq_high = Column(Double)
# 
# class Skyregion(Base):
#     __tablename__ = 'skyregion'
# 
#     id = Column(Integer, seq_skyregion, primary_key=True,
#                 server_default=seq_skyregion.next_value())
# 
#     dataset_id = Column('dataset', ForeignKey('dataset.id'), nullable=False, index=True)
#     dataset = relationship('Dataset',
#                            backref=backref('skyregions',cascade="all,delete"))
# 
#     centre_ra = Column(Double, nullable=False)
#     centre_decl = Column(Double, nullable=False)
#     xtr_radius = Column(Double, nullable=False)
#     x = Column(Double, nullable=False)
#     y = Column(Double, nullable=False)
#     z = Column(Double, nullable=False)
# 
# class Image(Base):
#     __tablename__ = 'image'
# 
#     id = Column(Integer, seq_image, primary_key=True,
#                 server_default=seq_image.next_value())
# 
#     dataset_id = Column('dataset', Integer, ForeignKey('dataset.id'), nullable=False, index=True)
#     dataset = relationship('Dataset', backref=backref('images', cascade="delete"))
# 
#     band_id = Column('band', ForeignKey('frequencyband.id'), nullable=False, index=True)
#     band = relationship('Frequencyband', cascade="delete")
# 
#     skyrgn_id = Column('skyrgn', Integer, ForeignKey('skyregion.id'), nullable=False, index=True)
#     skyrgn = relationship('Skyregion', backref=backref('images', cascade="delete"))
# 
#     tau = Column(Integer)
#     stokes = Column(SmallInteger, nullable=False, server_default=text("1"))
#     tau_time = Column(Double)
#     freq_eff = Column(Double, nullable=False)
#     freq_bw = Column(Double)
#     taustart_ts = Column(DateTime, nullable=False, index=True)
#     rb_smaj = Column(Double, nullable=False)
#     rb_smin = Column(Double, nullable=False)
#     rb_pa = Column(Double, nullable=False)
#     deltax = Column(Double, nullable=False)
#     deltay = Column(Double, nullable=False)
#     fwhm_arcsec = Column(Double)
#     fov_degrees = Column(Double)
#     rms_qc = Column(Double, nullable=False)
#     rms_min = Column(Double)
#     rms_max = Column(Double)
#     detection_thresh = Column(Double)
#     analysis_thresh = Column(Double)
#     url = Column(String(1024))
#     node = Column(SmallInteger, nullable=False, server_default=text("1"))
#     nodes = Column(SmallInteger, nullable=False, server_default=text("1"))
# 
#     data = relationship("ImageData", uselist=False, back_populates="image")
# 
# class Dataset(Base):
#     __tablename__ = 'dataset'
# 
#     id = Column(Integer, seq_dataset, server_default=seq_dataset.next_value(), primary_key=True)
#     rerun = Column(Integer, nullable=False, server_default=text("0"))
#     type = Column(SmallInteger, nullable=False, server_default=text("1"))
#     process_start_ts = Column(DateTime, nullable=False)
#     process_end_ts = Column(DateTime)
#     detection_threshold = Column(Double)
#     analysis_threshold = Column(Double)
#     assoc_radius = Column(Double)
#     backsize_x = Column(SmallInteger)
#     backsize_y = Column(SmallInteger)
#     margin_width = Column(Double)
#     description = Column(String(100), nullable=False)
#     node = Column(SmallInteger, nullable=False, server_default=text("1"))
#     nodes = Column(SmallInteger, nullable=False, server_default=text("1"))
# 
# Path: tkp/utility/coordinates.py
# def eq_to_cart(ra, dec):
#     """Find the cartesian co-ordinates on the unit sphere given the eq. co-ords.
# 
#         ra, dec should be in degrees.
#     """
#     return (math.cos(math.radians(dec)) * math.cos(math.radians(ra)),  # Cartesian x
#             math.cos(math.radians(dec)) * math.sin(math.radians(ra)),  # Cartesian y
#             math.sin(math.radians(dec)))  # Cartesian z


# Path: tkp/db/alchemy/image.py
import math
from datetime import datetime
from tkp.db.model import Frequencyband, Skyregion, Image, Dataset
from tkp.utility.coordinates import eq_to_cart
from sqlalchemy import func, cast
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION as Double

                            AND sky.centre_decl + sky.xtr_radius
         AND (  (rc.x - sky.x) * (rc.x - sky.x)
                + (rc.y - sky.y) * (rc.y - sky.y)
                + (rc.z - sky.z) * (rc.z - sky.z)
             ) < %(inter_sq)s
      ;
    """ % {'inter_sq': inter_sq, 'skyregion_id': skyregion.id}
    session.execute(q)
    return inter


def get_skyregion(session, dataset, centre_ra, centre_decl, xtr_radius):
    """
     gets an id for a skyregion, given a pair of central co-ordinates and a radius.

     If no matching skyregion is found, a new one is inserted. In this case we also trigger execution of
     `updateSkyRgnMembers` for the new skyregion - this performs a simple assocation with current members of the
     runningcatalog to find sources that should be visible in the new skyregion, and updates the assocskyrgn table
     accordingly.

     args:
        session (sqlalchemy.orm.session.Session): a SQLAlchemy session object
        dataset_id (int): the dataset ID
        centre_ra (float): center RA coordinate
        centre_decl (float): center DECL coordinate
        xtr_radius (float): The extraction radius

     returns:
        tkp.db.models.Skyregion: a SQLalchemy skyregion
     """

    skyregion = session.query(Skyregion).filter(Skyregion.dataset == dataset,