# create a table containing a crossmatch between a single usno-b field and ppmx

from optparse import OptionParser

import gavo
from gavo import config
from gavo import sqlsupport

fieldNames = {
	"B1": ("b1xi", "b1eta", "b1f", "b1s", "b1mag"),
	"B2": ("b2xi", "b2eta", "b2f", "b2s", "b2mag"),
	"R1": ("r1xi", "r1eta", "r1f", "r1s", "r1mag"),
	"R2": ("r2xi", "r2eta", "r2f", "r2s", "r2mag"),
	"I": ("ixi", "ieta", "if", "i_s", "imag"),
}

_script = r"""-DROP TABLE usnob.scratch
CREATE TABLE usnob.scratch AS (\
  SELECT q.raj2000, q.dej2000, \
		q.pos[0]-q.xi/cos(q.dej2000/180.*3.1415926536) AS alphaEp, \
		q.pos[1]+q.eta AS deltaEp, q.epoch, q.mag FROM (\
			SELECT a.raj2000, a.dej2000, \
				movePm(a.raj2000, a.dej2000, a.pmra, a.pmde, '2000-01-01T00:00:00', \
					b.epoch) AS pos,  \
				a.%(eta)s as eta, a.%(xi)s as xi, b.epoch, a.%(mag)s as mag\
			FROM usnob.data AS a, usnob.plates AS \
			b WHERE a.%(survey)s='%(targSurvey)s' AND a.%(field)s=%(targField)d \
				AND b.survey=a.%(survey)s AND b.field=a.%(field)s) AS q)
CREATE INDEX q3c_scratch ON usnob.scratch (q3c_ang2ipix(raj2000, dej2000))
CLUSTER q3c_scratch ON usnob.scratch
ANALYZE usnob.scratch
-DROP TABLE %(tableName)s
CREATE TABLE %(tableName)s AS (\
	SELECT raj2000, dej2000, alphaEp, deltaEp, epoch, mag, cmag, \
	pos[0] as alphaPPMX, pos[1] as deltaPPMX, localid, alpha2000P, delta2000P\
		FROM (\
	   SELECT raj2000, dej2000, alphaEp, deltaEp, epoch, mag, cmag, \
		 	movePm(alphaFloat, deltaFloat, pmra/3600., pmde/3600.,\
				'2000-01-01T00:00:00', epoch) AS pos, localid, \
				alphaFloat as alpha2000P, deltaFloat as delta2000P\
			FROM usnob.scratch as a, ppmx.data as b\
			WHERE q3c_join(a.raj2000, a.dej2000, b.alphaFloat, b.deltaFloat, 0.0001)\
		) AS q)
CREATE INDEX %(indexName)s ON %(tableName)s (q3c_ang2ipix(alphaEp, deltaEp))
CLUSTER %(indexName)s ON %(tableName)s
ANALYZE %(tableName)s
GRANT SELECT ON %(tableName)s TO gavo
DROP TABLE usnob.scratch"""


def makeCrossTable(tableName, band, targSurvey, targField):
	try:
		xi, eta, field, survey, mag = fieldNames[band]
	except KeyError:
		raise gavo.Error("Band %s is not known"%band)
	indexName = "q3c_"+tableName.split(".")[-1]
	runner = sqlsupport.ScriptRunner()
	runner.run(_script%locals(), True)


def parseCommandLine():
	parser = OptionParser(usage = "%prog [options]")
	parser.add_option("-f", "--field", help="process field FIELD", dest="field",
		action="store", type="int", default=250, metavar="FIELD")
	parser.add_option("-s", "--survey", help="take field from SURVEY (A-H)",
		dest="survey", action="store", type="str", default='A',
		metavar="SURVEY")
	parser.add_option("-b", "--band", help="use band BAND (B1, B2, R1, R2, I)",
		dest="band", action="store", type="str", default="B1", metavar="BAND")
	return parser.parse_args()

if __name__=="__main__":
	config.setDbProfile("feed")
	opts, args = parseCommandLine()
	makeCrossTable("usnob.curcross", opts.band, opts.survey, opts.field)