# A quick script that goes through a site's obscore table and
# warns against NULLs in obscore columns where that might be
# due to incomplete mapping.
#
# TODO: only warn against all-NULL columns.  That probably won't
# work through ADQL, but SQL can do that somewhat like this:
#
# select obs_collection, 'access_estsize' as colname, bool_and(target_name is null) as allnull
# from ivoa.obscore
# group by obs_collection

import os

import pyvo

SVC_URL = os.environ.get("SVC_URL", "http://dc.g-vo.org/tap")

COLS = ["access_estsize", "access_format", "access_url",
	"calib_level",
	"dataproduct_type",
	"em_max", "em_min", "em_res_power", "em_ucd", "em_xel",
	"facility_name", "instrument_name",
	"o_ucd",
	"obs_id", "obs_publisher_did", "obs_title",
	"preview",
	"s_dec", "s_fov", "s_pixel_scale", "s_ra", "s_region", "s_resolution",
	"s_xel1", "s_xel2",
	"t_exptime", "t_max", "t_min", "t_resolution", "t_xel",
	"target_class", "target_name",]


def build_query(cols):
	with_clauses, table_names = [], []
	for c in cols:
		table_name = "nulls_in_"+c
		with_clauses.append(
			"{} as (select distinct obs_collection, '{}' as colname"
			"  from ivoa.obscore where {} is null)".format(table_name, c, c))
		table_names.append(table_name)
	
	return ("with {} {}".format(
			", ".join(with_clauses),
			" union all ".join(
				"select * from {}".format(tn) for tn in table_names)))


svc = pyvo.dal.TAPService(SVC_URL)
res = svc.run_async(build_query(COLS))
with open("withnulls.vot", "wb") as f:
	res.to_table().write(output=f, format="votable")