#!/bin/bash

# MAKE HSC PDR1 WIDE LARGE QUERY

if [ "$i" = vvds ];
then
echo "-- START 1/3 ..." ${i} >> ${rootdir}/hsc-pdr1-wide-large.sql
echo "SELECT
  object_id, ra, dec,
  -- Aperture Photometry
  gmag_aperture10, rmag_aperture10, imag_aperture10, zmag_aperture10, ymag_aperture10,
  gmag_aperture10_err, rmag_aperture10_err, imag_aperture10_err, zmag_aperture10_err, ymag_aperture10_err,
  gmag_aperture15, rmag_aperture15, imag_aperture15, zmag_aperture15, ymag_aperture15,
  gmag_aperture15_err, rmag_aperture15_err, imag_aperture15_err, zmag_aperture15_err, ymag_aperture15_err,
  gmag_aperture20, rmag_aperture20, imag_aperture20, zmag_aperture20, ymag_aperture20,
  gmag_aperture20_err, rmag_aperture20_err, imag_aperture20_err, zmag_aperture20_err, ymag_aperture20_err,
  gmag_aperture30, rmag_aperture30, imag_aperture30, zmag_aperture30, ymag_aperture30,
  gmag_aperture30_err, rmag_aperture30_err, imag_aperture30_err, zmag_aperture30_err, ymag_aperture30_err,
  gmag_aperture40, rmag_aperture40, imag_aperture40, zmag_aperture40, ymag_aperture40,
  gmag_aperture40_err, rmag_aperture40_err, imag_aperture40_err, zmag_aperture40_err,
  gmag_aperture57, rmag_aperture57, imag_aperture57, zmag_aperture57, ymag_aperture57,
  gmag_aperture57_err, rmag_aperture57_err, imag_aperture57_err, zmag_aperture57_err, ymag_aperture57_err,
  gmag_aperture84, rmag_aperture84, imag_aperture84, zmag_aperture84, ymag_aperture84,
  gmag_aperture84_err, rmag_aperture84_err, imag_aperture84_err, zmag_aperture84_err, ymag_aperture84_err,
  gmag_aperture118, rmag_aperture118, imag_aperture118, zmag_aperture118, ymag_aperture118,
  gmag_aperture118_err, rmag_aperture118_err, imag_aperture118_err, zmag_aperture118_err, ymag_aperture118_err,
  gmag_aperture168, rmag_aperture168, imag_aperture168, zmag_aperture168, ymag_aperture168,
  gmag_aperture168_err, rmag_aperture168_err, imag_aperture168_err, zmag_aperture168_err, ymag_aperture168_err,
  gmag_aperture235, rmag_aperture235, imag_aperture235, zmag_aperture235, ymag_aperture235,
  gmag_aperture235_err, rmag_aperture235_err, imag_aperture235_err, zmag_aperture235_err, ymag_aperture235_err
FROM
  -- WIDE SURVEY (in PDR1 available in AEGIS, XMM-LSS, GAMA09H, WIDE12H, GAMA15H, HECTOMAP, VVDS)
  pdr1_wide.forced
  LEFT JOIN pdr1_wide.afterburner USING(object_id)
  WHERE
  pdr1_wide.search_${i}(object_id)
  -- WE keep only primary detection because recommended
  AND detect_is_primary" >> ${rootdir}/hsc-pdr1-wide-large.sql
echo " " >> ${rootdir}/hsc-pdr1-wide-large.sql
echo "-- START 2/3 ..." ${i} >> ${rootdir}/hsc-pdr1-wide-large.sql
echo "SELECT
  object_id, ra, dec,
  -- Total Photometry
  gmag_kron, rmag_kron, imag_kron, zmag_kron, ymag_kron,
  gmag_kron_err, rmag_kron_err, imag_kron_err, zmag_kron_err, ymag_kron_err,
  gmag_psf, rmag_psf, imag_psf, zmag_psf, ymag_psf,
  gmag_psf_err, rmag_psf_err, imag_psf_err, zmag_psf_err, ymag_psf_err,
  gcmodel_mag, rcmodel_mag, icmodel_mag, zcmodel_mag, ycmodel_mag,
  gcmodel_mag_err, rcmodel_mag_err, icmodel_mag_err, zcmodel_mag_err, ycmodel_mag_err,
  -- Extension Measurements
  gflux_kron_radius, rflux_kron_radius, iflux_kron_radius, zflux_kron_radius, yflux_kron_radius,
  -- Extendedness flags or star/galaxy separation flag
  gclassification_extendedness, rclassification_extendedness, iclassification_extendedness, zclassification_extendedness, yclassification_extendedness
FROM
  -- WIDE SURVEY (in PDR1 available in AEGIS, XMM-LSS, GAMA09H, WIDE12H, GAMA15H, HECTOMAP, VVDS)
  pdr1_wide.forced
  LEFT JOIN pdr1_wide.afterburner USING(object_id)
  WHERE
  pdr1_wide.search_${i}(object_id)
  -- WE keep only primary detection because recommended
  AND detect_is_primary" >> ${rootdir}/hsc-pdr1-wide-large.sql
echo " " >> ${rootdir}/hsc-pdr1-wide-large.sql
echo "-- START 3/3 ..." ${i} >> ${rootdir}/hsc-pdr1-wide-large.sql
echo "SELECT
  object_id, ra, dec,
  -- Photometric Flags
  gflags_pixel_offimage, rflags_pixel_offimage, iflags_pixel_offimage, zflags_pixel_offimage, yflags_pixel_offimage,
  gflags_pixel_bright_object_center, rflags_pixel_bright_object_center, iflags_pixel_bright_object_center, zflags_pixel_bright_object_center, yflags_pixel_bright_object_center,
  gflags_pixel_bright_object_any, rflags_pixel_bright_object_any, iflags_pixel_bright_object_any, zflags_pixel_bright_object_any, yflags_pixel_bright_object_any
  -- flags recommended for filtering spurious sources; Table 4 PDR1 survey paper (Aihara et al. 2017)
  gflags_pixel_interpolated_center, rflags_pixel_interpolated_center, iflags_pixel_interpolated_center, zflags_pixel_interpolated_center, yflags_pixel_interpolated_center,
  gflags_pixel_interpolated_any, rflags_pixel_interpolated_any, iflags_pixel_interpolated_any, zflags_pixel_interpolated_any, yflags_pixel_interpolated_any,
  gflags_pixel_saturated_center, rflags_pixel_saturated_center, iflags_pixel_saturated_center, zflags_pixel_saturated_center, yflags_pixel_saturated_center,
  gflags_pixel_saturated_any, rflags_pixel_saturated_any, iflags_pixel_saturated_any, zflags_pixel_saturated_any, yflags_pixel_saturated_any,
  gflux_kron_flags, rflux_kron_flags, iflux_kron_flags, zflux_kron_flags, yflux_kron_flags,
  gflux_psf_flags, rflux_psf_flags, iflux_psf_flags, zflux_psf_flags, yflux_psf_flags,
  gcmodel_flux_flags, rcmodel_flux_flags, icmodel_flux_flags, zcmodel_flux_flags, ycmodel_flux_flags
  -- flags included in the clean photometry set query (of these only *centroid_sdss_flags, *pixel_edge, *pixel_cr_any, *pixel_bad flags are used in our filtering; parent_id is not used)
  gcentroid_sdss_flags, rcentroid_sdss_flags, icentroid_sdss_flags, zcentroid_sdss_flags, ycentroid_sdss_flags,
  gflags_pixel_edge, rflags_pixel_edge, iflags_pixel_edge, zflags_pixel_edge, yflags_pixel_edge,
  gflags_pixel_cr_any, rflags_pixel_cr_any, iflags_pixel_cr_any, zflags_pixel_cr_any, yflags_pixel_cr_any,
  gflags_pixel_bad, rflags_pixel_bad, iflags_pixel_bad, zflags_pixel_bad, yflags_pixel_bad,
  parent_id,
  -- the following flags are not downloaded because they are included in the selection here applied for primary objects
  -- detect_is_patch_inner, detect_is_tract_inner, deblend_nchild, detect_is_primary,
  -- Afterburner Quantities (left joined to forced photometry) to eliminate junk; the junk detection algorithm was turned off in the forced photometry production
  object_id, gchild_flux_convolved_1_0, rchild_flux_convolved_1_0, ichild_flux_convolved_1_0, zchild_flux_convolved_1_0, ychild_flux_convolved_1_0,
gchild_flux_convolved_1_0_err, rchild_flux_convolved_1_0_err, ichild_flux_convolved_1_0_err, zchild_flux_convolved_1_0_err, ychild_flux_convolved_1_0_err, 
gchild_mag_convolved_1_0, rchild_mag_convolved_1_0, ichild_mag_convolved_1_0, zchild_mag_convolved_1_0, ychild_mag_convolved_1_0, 
gchild_mag_convolved_1_0_err, rchild_mag_convolved_1_0_err, ichild_mag_convolved_1_0_err, zchild_mag_convolved_1_0_err, ychild_mag_convolved_1_0_err,
gchild_flux_convolved_1_0_apcorr, rchild_flux_convolved_1_0_apcorr, ichild_flux_convolved_1_0_apcorr, zchild_flux_convolved_1_0_apcorr, ychild_flux_convolved_1_0_apcorr, 
gchild_flux_convolved_1_0_flags, rchild_flux_convolved_1_0_flags, ichild_flux_convolved_1_0_flags, zchild_flux_convolved_1_0_flags, ychild_flux_convolved_1_0_flags,
gdetected_notjunk, gchild_flux_convolved_flag, rdetected_notjunk, rchild_flux_convolved_flag, idetected_notjunk, ichild_flux_convolved_flag, zdetected_notjunk, zchild_flux_convolved_flag, ydetected_notjunk, ychild_flux_convolved_flag
FROM
  -- WIDE SURVEY (in PDR1 available in AEGIS, XMM-LSS, GAMA09H, WIDE12H, GAMA15H, HECTOMAP, VVDS)
  pdr1_wide.forced
  LEFT JOIN pdr1_wide.afterburner USING(object_id)
  WHERE
  pdr1_wide.search_${i}(object_id)
  -- WE keep only primary detection because recommended
  AND detect_is_primary" >> ${rootdir}/hsc-pdr1-wide-large.sql
echo " " >> ${rootdir}/hsc-pdr1-wide-large.sql

fi
