# Extra schema definitions compared to `cat` schema used by L1DB implementation.

# DiaObject needs a special column for time of last seen DiaSource,
# validityEnd should be allowed to have NULL (for +Infinity)
table: DiaObject
columns:
- name: lastNonForcedSource
  type: DATETIME
  nullable: false
  description: Last time when non-forced DIASource was seen for this object.
- name: validityEnd
  type: DATETIME
  nullable: true
  description: Time when validity of this diaObject ends.
  default: null
- name: nDiaSources
  type: INT
  nullable: false
  description: Total number of DiaSources associated with this DiaObject.
- name: uTOTFluxMean
  type: FLOAT
  nullable: true
  description: Weighted mean forced photometry flux for u filter.
  ucd: phot.count
  unit: nJy
- name: uTOTFluxMeanErr
  type: FLOAT
  nullable: true
  description: Standard error of uTOTFluxMean.
  ucd: stat.error
  unit: nJy
- name: uTOTFluxSigma
  type: FLOAT
  nullable: true
  description: Standard deviation of the distribution of uTOTFlux.
  ucd: stat.stdev
  unit: nJy
- name: gTOTFluxMean
  type: FLOAT
  nullable: true
  description: Weighted mean forced photometry flux for g filter.
  ucd: phot.count
  unit: nJy
- name: gTOTFluxMeanErr
  type: FLOAT
  nullable: true
  description: Standard error of gTOTFluxMean.
  ucd: stat.error
  unit: nJy
- name: gTOTFluxSigma
  type: FLOAT
  nullable: true
  description: Standard deviation of the distribution of gTOTFlux.
  ucd: stat.stdev
  unit: nJy
- name: rTOTFluxMean
  type: FLOAT
  nullable: true
  description: Weighted mean forced photometry flux for r filter.
  ucd: phot.count
  unit: nJy
- name: rTOTFluxMeanErr
  type: FLOAT
  nullable: true
  description: Standard error of rTOTFluxMean.
  ucd: stat.error
  unit: nJy
- name: rTOTFluxSigma
  type: FLOAT
  nullable: true
  description: Standard deviation of the distribution of rTOTFlux.
  ucd: stat.stdev
  unit: nJy
- name: iTOTFluxMean
  type: FLOAT
  nullable: true
  description: Weighted mean forced photometry flux for i filter.
  ucd: phot.count
  unit: nJy
- name: iTOTFluxMeanErr
  type: FLOAT
  nullable: true
  description: Standard error of iTOTFluxMean.
  ucd: stat.error
  unit: nJy
- name: iTOTFluxSigma
  type: FLOAT
  nullable: true
  description: Standard deviation of the distribution of iTOTFlux.
  ucd: stat.stdev
  unit: nJy
- name: zTOTFluxMean
  type: FLOAT
  nullable: true
  description: Weighted mean forced photometry flux for z filter.
  ucd: phot.count
  unit: nJy
- name: zTOTFluxMeanErr
  type: FLOAT
  nullable: true
  description: Standard error of zTOTFluxMean.
  ucd: stat.error
  unit: nJy
- name: zTOTFluxSigma
  type: FLOAT
  nullable: true
  description: Standard deviation of the distribution of zTOTFlux.
  ucd: stat.stdev
  unit: nJy
- name: yTOTFluxMean
  type: FLOAT
  nullable: true
  description: Weighted mean forced photometry flux for y filter.
  ucd: phot.count
  unit: nJy
- name: yTOTFluxMeanErr
  type: FLOAT
  nullable: true
  description: Standard error of yTOTFluxMean.
  ucd: stat.error
  unit: nJy
- name: yTOTFluxSigma
  type: FLOAT
  nullable: true
  description: Standard deviation of the distribution of yTOTFlux.
  ucd: stat.stdev
  unit: nJy
- name: uPSFluxMAD
  type: FLOAT
  nullable: true
  description: Median absolute deviation u band fluxes.
  unit: nJy
- name: uPSFluxSkew
  type: FLOAT
  nullable: true
  description: Skewness of the u band fluxes.
- name: uPSFluxPercentile05
  type: FLOAT
  nullable: true
  description: Value at the 5% percentile of the u band fluxes.
  unit: nJy
- name: uPSFluxPercentile25
  type: FLOAT
  nullable: true
  description: Value at the 25% percentile of the u band fluxes.
  unit: nJy
- name: uPSFluxPercentile50
  type: FLOAT
  nullable: true
  description: Value at the 50% percentile (median) of the u band fluxes.
  unit: nJy
- name: uPSFluxPercentile75
  type: FLOAT
  nullable: true
  description: Value at the 75% percentile of the u band fluxes.
  unit: nJy
- name: uPSFluxPercentile95
  type: FLOAT
  nullable: true
  description: Value at the 95% percentile of the u band fluxes.
  unit: nJy
- name: uPSFluxMin
  type: FLOAT
  nullable: true
  description: Minimum observed u band fluxes.
  unit: nJy
- name: uPSFluxMax
  type: FLOAT
  nullable: true
  description: Maximum observed u band fluxes.
  unit: nJy
- name: uPSFluxStetsonJ
  type: FLOAT
  nullable: true
  description: StetsonJ statistic for the u band fluxes.
- name: uPSFluxLinearSlope
  type: FLOAT
  nullable: true
  description: Linear best fit slope of the u band fluxes.
  unit: nJy/day
- name: uPSFluxLinearIntercept
  type: FLOAT
  nullable: true
  description: Linear best fit Intercept of the u band fluxes.
- name: uPSFluxMaxSlope
  type: FLOAT
  nullable: true
  description: Maximum slope between u band flux obsevations max(delta_flux/delta_time)
  unit: nJy/day
- name: uPSFluxErrMean
  type: FLOAT
  nullable: true
  description: Mean of the u band flux errors.
  unit: nJy

- name: gPSFluxMAD
  type: FLOAT
  nullable: true
  description: Median absolute deviation g band fluxes.
  unit: nJy
- name: gPSFluxSkew
  type: FLOAT
  nullable: true
  description: Skewness of the g band fluxes.
- name: gPSFluxPercentile05
  type: FLOAT
  nullable: true
  description: Value at the 5% percentile of the g band fluxes.
  unit: nJy
- name: gPSFluxPercentile25
  type: FLOAT
  nullable: true
  description: Value at the 25% percentile of the g band fluxes.
  unit: nJy
- name: gPSFluxPercentile50
  type: FLOAT
  nullable: true
  description: Value at the 50% percentile (median) of the g band fluxes.
  unit: nJy
- name: gPSFluxPercentile75
  type: FLOAT
  nullable: true
  description: Value at the 75% percentile of the g band fluxes.
  unit: nJy
- name: gPSFluxPercentile95
  type: FLOAT
  nullable: true
  description: Value at the 95% percentile of the g band fluxes.
  unit: nJy
- name: gPSFluxMin
  type: FLOAT
  nullable: true
  description: Minimum observed g band fluxes.
  unit: nJy
- name: gPSFluxMax
  type: FLOAT
  nullable: true
  description: Maximum observed g band fluxes.
  unit: nJy
- name: gPSFluxStetsonJ
  type: FLOAT
  nullable: true
  description: StetsonJ statistic for the g band fluxes.
- name: gPSFluxLinearSlope
  type: FLOAT
  nullable: true
  description: Linear best fit slope of the g band fluxes.
  unit: nJy/day
- name: gPSFluxLinearIntercept
  type: FLOAT
  nullable: true
  description: Linear best fit Intercept of the g band fluxes.
  unit: nJy
- name: gPSFluxMaxSlope
  type: FLOAT
  nullable: true
  description: Maximum slope between g band flux obsevations max(delta_flux/delta_time)
  unit: nJy/day
- name: gPSFluxErrMean
  type: FLOAT
  nullable: true
  description: Mean of the g band flux errors.
  unit: nJy

- name: rPSFluxMAD
  type: FLOAT
  nullable: true
  description: Median absolute deviation r band fluxes.
  unit: nJy
- name: rPSFluxSkew
  type: FLOAT
  nullable: true
  description: Skewness of the r band fluxes.
- name: rPSFluxPercentile05
  type: FLOAT
  nullable: true
  description: Value at the 5% percentile of the r band fluxes.
  unit: nJy
- name: rPSFluxPercentile25
  type: FLOAT
  nullable: true
  description: Value at the 25% percentile of the r band fluxes.
  unit: nJy
- name: rPSFluxPercentile50
  type: FLOAT
  nullable: true
  description: Value at the 50% percentile (median) of the r band fluxes.
  unit: nJy
- name: rPSFluxPercentile75
  type: FLOAT
  nullable: true
  description: Value at the 75% percentile of the r band fluxes.
  unit: nJy
- name: rPSFluxPercentile95
  type: FLOAT
  nullable: true
  description: Value at the 95% percentile of the r band fluxes.
  unit: nJy
- name: rPSFluxMin
  type: FLOAT
  nullable: true
  description: Minimum observed r band fluxes.
  unit: nJy
- name: rPSFluxMax
  type: FLOAT
  nullable: true
  description: Maximum observed r band fluxes.
  unit: nJy
- name: rPSFluxStetsonJ
  type: FLOAT
  nullable: true
  description: StetsonJ statistic for the r band fluxes.
- name: rPSFluxLinearSlope
  type: FLOAT
  nullable: true
  description: Linear best fit slope of the r band fluxes.
  unit: nJy/day
- name: rPSFluxLinearIntercept
  type: FLOAT
  nullable: true
  description: Linear best fit Intercept of the r band fluxes.
  unit: nJy
- name: rPSFluxMaxSlope
  type: FLOAT
  nullable: true
  description: Maximum slope between r band flux obsevations max(delta_flux/delta_time)
  unit: nJy/day
- name: rPSFluxErrMean
  type: FLOAT
  nullable: true
  description: Mean of the r band flux errors.
  unit: nJy

- name: iPSFluxMAD
  type: FLOAT
  nullable: true
  description: Median absolute deviation i band fluxes.
  unit: nJy
- name: iPSFluxSkew
  type: FLOAT
  nullable: true
  description: Skewness of the i band fluxes.
- name: iPSFluxPercentile05
  type: FLOAT
  nullable: true
  description: Value at the 5% percentile of the i band fluxes.
  unit: nJy
- name: iPSFluxPercentile25
  type: FLOAT
  nullable: true
  description: Value at the 25% percentile of the i band fluxes.
  unit: nJy
- name: iPSFluxPercentile50
  type: FLOAT
  nullable: true
  description: Value at the 50% percentile (median) of the i band fluxes.
  unit: nJy
- name: iPSFluxPercentile75
  type: FLOAT
  nullable: true
  description: Value at the 75% percentile of the i band fluxes.
  unit: nJy
- name: iPSFluxPercentile95
  type: FLOAT
  nullable: true
  description: Value at the 95% percentile of the i band fluxes.
  unit: nJy
- name: iPSFluxMin
  type: FLOAT
  nullable: true
  description: Minimum observed i band fluxes.
  unit: nJy
- name: iPSFluxMax
  type: FLOAT
  nullable: true
  description: Maximum observed i band fluxes.
  unit: nJy
- name: iPSFluxStetsonJ
  type: FLOAT
  nullable: true
  description: StetsonJ statistic for the i band fluxes.
- name: iPSFluxLinearSlope
  type: FLOAT
  nullable: true
  description: Linear best fit slope of the i band fluxes.
  unit: nJy/day
- name: iPSFluxLinearIntercept
  type: FLOAT
  nullable: true
  description: Linear best fit Intercept of the i band fluxes.
  unit: nJy
- name: iPSFluxMaxSlope
  type: FLOAT
  nullable: true
  description: Maximum slope between i band flux obsevations max(delta_flux/delta_time)
  unit: nJy/day
- name: iPSFluxErrMean
  type: FLOAT
  nullable: true
  description: Mean of the i band flux errors.
  unit: nJy

- name: zPSFluxMAD
  type: FLOAT
  nullable: true
  description: Median absolute deviation z band fluxes.
  unit: nJy
- name: zPSFluxSkew
  type: FLOAT
  nullable: true
  description: Skewness of the z band fluxes.
- name: zPSFluxPercentile05
  type: FLOAT
  nullable: true
  description: Value at the 5% percentile of the z band fluxes.
  unit: nJy
- name: zPSFluxPercentile25
  type: FLOAT
  nullable: true
  description: Value at the 25% percentile of the z band fluxes.
  unit: nJy
- name: zPSFluxPercentile50
  type: FLOAT
  nullable: true
  description: Value at the 50% percentile (median) of the z band fluxes.
  unit: nJy
- name: zPSFluxPercentile75
  type: FLOAT
  nullable: true
  description: Value at the 75% percentile of the z band fluxes.
  unit: nJy
- name: zPSFluxPercentile95
  type: FLOAT
  nullable: true
  description: Value at the 95% percentile of the z band fluxes.
  unit: nJy
- name: zPSFluxMin
  type: FLOAT
  nullable: true
  description: Minimum observed z band fluxes.
  unit: nJy
- name: zPSFluxMax
  type: FLOAT
  nullable: true
  description: Maximum observed z band fluxes.
  unit: nJy
- name: zPSFluxStetsonJ
  type: FLOAT
  nullable: true
  description: StetsonJ statistic for the z band fluxes.
- name: zPSFluxLinearSlope
  type: FLOAT
  nullable: true
  description: Linear best fit slope of the z band fluxes.
  unit: nJy/day
- name: zPSFluxLinearIntercept
  type: FLOAT
  nullable: true
  description: Linear best fit Intercept of the z band fluxes.
  unit: nJy
- name: zPSFluxMaxSlope
  type: FLOAT
  nullable: true
  description: Maximum slope between z band flux obsevations max(delta_flux/delta_time)
  unit: nJy/day
- name: zPSFluxErrMean
  type: FLOAT
  nullable: true
  description: Mean of the z band flux errors.
  unit: nJy

- name: yPSFluxMAD
  type: FLOAT
  nullable: true
  description: Median absolute deviation y band fluxes.
  unit: nJy
- name: yPSFluxSkew
  type: FLOAT
  nullable: true
  description: Skewness of the y band fluxes.
- name: yPSFluxPercentile05
  type: FLOAT
  nullable: true
  description: Value at the 5% percentile of the y band fluxes.
  unit: nJy
- name: yPSFluxPercentile25
  type: FLOAT
  nullable: true
  description: Value at the 25% percentile of the y band fluxes.
  unit: nJy
- name: yPSFluxPercentile50
  type: FLOAT
  nullable: true
  description: Value at the 50% percentile (median) of the y band fluxes.
  unit: nJy
- name: yPSFluxPercentile75
  type: FLOAT
  nullable: true
  description: Value at the 75% percentile of the y band fluxes.
  unit: nJy
- name: yPSFluxPercentile95
  type: FLOAT
  nullable: true
  description: Value at the 95% percentile of the y band fluxes.
  unit: nJy
- name: yPSFluxMin
  type: FLOAT
  nullable: true
  description: Minimum observed y band fluxes.
  unit: nJy
- name: yPSFluxMax
  type: FLOAT
  nullable: true
  description: Maximum observed y band fluxes.
  unit: nJy
- name: yPSFluxStetsonJ
  type: FLOAT
  nullable: true
  description: StetsonJ statistic for the y band fluxes.
- name: yPSFluxLinearSlope
  type: FLOAT
  nullable: true
  description: Linear best fit slope of the y band fluxes.
  unit: nJy/day
- name: yPSFluxLinearIntercept
  type: FLOAT
  nullable: true
  description: Linear best fit Intercept of the y band fluxes.
  unit: nJy
- name: yPSFluxMaxSlope
  type: FLOAT
  nullable: true
  description: Maximum slope between y band flux obsevations max(delta_flux/delta_time)
  unit: nJy/day
- name: yPSFluxErrMean
  type: FLOAT
  nullable: true
  description: Mean of the y band flux errors.
  unit: nJy

---
# DiaObject needs a special column for time of last seen DiaSource,
# validityEnd should be allowed to have NULL (for +Infinity)
table: DiaForcedSource
columns:
- name: diaForcedSourceId
  type: BIGINT
  nullable: false
  description: Unique id for forced soure.
- name: totFlux
  type: FLOAT
  nullable: true
  description: Point Source model flux measured on the direct image.
  ucd: phot.count
  unit: nJy
- name: totFluxErr
  type: FLOAT
  nullable: true
  description: Uncertainty of totFlux.
  ucd: stat.error;phot.count
  unit: nJy
- name: midPointTai
  type: DOUBLE
  nullable: false
  description: Effective mid-exposure time for this diaForcedSource
  ucd: time.epoch
  unit: d
- name: filterName
  type: CHAR
  nullable: false
  description: Name of stored filter.

---
# DiaObjectLast uses the same columns as DiaObject but has different index
table: DiaObjectLast
indices:
- name: PK_DiaObjectLast
  columns:
  - pixelId
  - diaObjectId
  type: PRIMARY
- name: IDX_DiaObjectLast_diaObjectId
  columns:
  - diaObjectId
  type: INDEX
- name: nDiaSources
  type: INT
  nullable: false
  description: Total number of DiaSources associated with this DiaObject.

---
# Special PK index for DiaObject table with spacial column being first
# (should provide better locality)
table: DiaObjectIndexHtmFirst
indices:
- name: PK_DiaObject
  columns:
  - pixelId
  - diaObjectId
  - validityStart
  type: PRIMARY
- name: IDX_DiaObject_diaObjectId
  columns:
  - diaObjectId
  type: INDEX

---
# Added columns for DIASource table for use in ap_association.
table: DiaSource
columns:
- name: filterName
  type: CHAR
  nullable: false
  description: Name of stored filter.
- name: isDipole
  type: BOOL
  nullable: true
  description: Object determined to be a dipole.

