#!/usr/bin/env python
import csv
import json
import numpy as np                      # for np.nan
import os
import re
import sqlite3 as sqlite
import sys

pixelScale = 0.168                      # arcsec/pixel
#
# Possible output fields, in order (not all need be present)
#
fieldList = [
    "visit",
    # "frameid",
    "object",
    "dateObs",
    "utc",
    "ra2000",
    "dec2000",
    "filter",
    "exptime",
    "altitude",
    "instRot",
    "fwhm",
    "memo",
]
#
# Formats for those fields
#
fmts = {
    "visit": "%-6d",
    "frameid": "%-9s",
    "object": "%-20s",
    "filter": "%7s",
    "dateObs": "%-10s",
    "utc": "%-8s",
    "exptime": "%7.1f",
    "altitude": "%8.4f",
    "instRot": "%7.2f",
    "instPa": "%7.2f",
    "ra2000": "%12s",
    "dec2000": "%13s",
    "memo": "%-30s",
    # other available fields
    "fwhm": "%5.2f",
    "nCcd": "%3d",
    "rerun": "20s",
}


def printHeader(fmt, sep):
    """Print a header given an string of formats, separated by sep"""
    hlen = 0

    fmt = fmt.split(sep)
    for i, f in enumerate(fmt):
        mat = re.search(r"^%(\([^)]+\))(-)?(\d+)", f)
        if mat:
            name, minus, width = mat.groups()
            hlen += int(width)
            fmt[i] = "%%%s%s%ss" % (name, minus if minus else "", width)
    hlen += (len(fmt) - 1)*len(sep)

    print(sep.join(fmt) % dict(zip(fieldList, fieldList)))
    print(hlen*"-")


def readLog(sqlFile, separator, extraFields=[], where=[], reverse=False, verbose=False):
    """Read an HSC sqlite file generated by Michitaro Koike"""

    types = {}
    for f in extraFields:
        types[f] = dict(d=int, f=float, g=float, s=str)[fmts[f][-1]]

    fmt = []
    for f in fieldList:
        fmt.append("%%(%s)%s" % (f, fmts[f][1:]))

    printedHeader = False               # wait until we know that all the fields are valid
    fmt = separator.join(fmt)

    #-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    where, vals = where

    query = """
SELECT
 visit, rerun, pointing, dateObs, field, filter, header, header_json, 
updated_at, fwhm, arrived_ccds, memo

FROM
 frames
 %s
ORDER BY visit %s
""" % ("WHERE " + " AND ".join(where) if where else "",
       "DESC" if reverse else "")

    if verbose:
        print >> sys.stderr, query

    n = {}
    expTimes = {}
    visits = {}

    conn = sqlite.connect(sqlFile)
    cursor = conn.cursor()

    ret = cursor.execute(query, vals)
    del cursor
    del conn

    fd = sys.stdout
    csvWriter = csv.writer(fd) if separator == "," else None

    for line in ret:
        visit, rerun, pointing, dateObs, object, filter, header_str, header, \
            updated_at, fwhm, nCcd, memo = line
        if fwhm:
            fwhm *= pixelScale          # convert to arcsec

        header = json.loads(header)
        if not header:
            for card in header_str.split("\n"):
                card = card.rstrip()
                if not card:
                    continue
                if re.search(r"^(COMMENT\s+|END$)", card):
                    continue

                mat = re.search(r"^([^\s]+)\s*=\s*([^\s]+)", card)
                if mat:
                    k, v = mat.groups()
                    if v in ("T", "F"):
                        v = (v == "T")
                    else:
                        try:
                            v = int(v)
                        except:
                            try:
                                v = float(v)
                            except:
                                v = v[1:-1].rstrip()

                            try:
                                float(v) # is this a float/int masquarading as a string?
                                #print card
                            except:
                                pass
                    header[k] = v
                else:
                    raise ValueError("Could not parse card: {}".format(card))
        try:
            fitsFilename = os.path.split(header["FRAMEID.comment"])[1]
            frameid = re.sub(r"_\d_\d\d\.fits$", "", fitsFilename)
        except:
            fitsFilename = None

        try:
            fields = dict(
                visit=visit,
                rerun=rerun,
                frameid=frameid,
                object=object.rstrip(),
                filter=filter,
                dateObs=dateObs,
                utc=re.sub(r"\.[0-9]+$", "", header["UT"]),
                exptime=float(header["EXPTIME"]),
                altitude=float(header["ALTITUDE"]),
                instRot=float(header["INR-STR"]),
                instPa=float(header["INST-PA"]),
                ra2000=header["RA2000"],
                dec2000=header["DEC2000"],
                memo=memo if memo else "",
                updated_at=updated_at,
                fwhm=fwhm if fwhm else np.nan,
                nCcd=nCcd if nCcd else 0,
            )
            for f in extraFields:
                if not f in fields:
                    try:
                        fields[f] = types[f](header[f])
                    except KeyError, e:
                        print >> sys.stderr, "Unknown field %s; ignoring" % f
                        extraFields.remove(f)
                        nfmt = []
                        for field in fmt.split(separator):
                            if not re.search(r"%%\(%s\)" % f, field):
                                nfmt.append(field)
                        fmt = separator.join(nfmt)
            #
            # OK, we're ready to actually print.  Print the header first,
            # now that we know which fields are valid
            #
            if not printedHeader:
                if csvWriter:
                    fieldNames = []
                    for field in fmt.split(separator):
                        fieldNames.append(re.search(r"%\(([^)]+)\)", field).group(1))

                    csvWriter.writerow(fieldNames)
                else:
                    printHeader(fmt, separator)
                printedHeader = True

            if csvWriter:
                csvData = []
                for f in fieldNames:
                    csvData.append(fields[f])

                csvWriter.writerow(csvData)
            else:
                print >> fd, fmt % fields
        except IOError as e:
            return
        except Exception as e:
            print >> sys.stderr, "Failed to process line: %s" % e, type(e)
            import pdb
            pdb.set_trace()

        if False and visit == 903846:
            import pdb
            pdb.set_trace()
            pass

if __name__ == "__main__":
    import argparse
    parser = argparse.ArgumentParser(description="""\
Process the .sqlite3 file generated by Michitao Koike's HSC webpage
http://hsca-web01.subaru.nao.ac.jp/michitaro/web-preview

There is a predefined set and order of fields which you may modify with -a and -x or replace using --field.
In all cases the "memo" field is printed last, but otherwise the order of keywords is obeyed.  You may use
any keyword in the default set or FITS header (n.b.: matching is case sensitive)

A separator of "," is handled specially;  no line is printed between the header and the data,
and a valid csv file is produced.
""")

    parser.add_argument('file', type=str, help="file to parse")
    parser.add_argument("--separator", help="Column separator", default=" | ")
    parser.add_argument('--addField', "-a", metavar="FIELD", action="append",
                        help="include this field (maybe be comma-separated list and/or repeated)",
                        default=[])
    parser.add_argument('--excludeField', "-x", metavar="FIELD", action="append",
                        help="exclude this field (maybe be comma-separated list and/or repeated)",
                        default=[])
    parser.add_argument('--fields', dest="fieldList", action="append",
                        help="include this fields, clearing defaults." +
                        "(maybe be comma-separated list and/or repeated; see also -a, -x)",
                        default=[])

    parser.add_argument('--dateObs', "-d", action="append",
                        help="desired dates (may be repeated; supports * and ?)", default=[])
    parser.add_argument('--filter', action="append",
                        help="desired filter (may be repeated)", default=[])
    parser.add_argument('--object', "-o", action="append",
                        help="desired object[s] (may be repeated; supports * and ?)", default=[])
    parser.add_argument('--rerun', action="append",
                        help="desired rerun[s] (may be repeated; supports * and ?)", default=[])
    parser.add_argument('--visit', action="append",
                        help="desired visit[s] (may be repeated; supports * and ?)", default=[])
    parser.add_argument('--reverse', action="store_true",
                        help="reverse sort order (newest at top)", default=False)

    parser.add_argument('--verbose', '-v', action="store_true", help="how chatty should I be?", default=False)

    args = parser.parse_args()

    if args.fieldList:
        fieldList = []
        for f in args.fieldList:
            for f in re.split(r",\s*", f):
                fieldList.append(f)
    #
    # Remove fields from fieldList
    #
    bad = []
    for x in args.excludeField:
        for x in re.split(r",\s*", x):
            try:
                fieldList.remove(x)
            except ValueError:
                bad.append(x)

    if bad:
        print >> sys.stderr, "Failed to find field%s %s" % ("s" if len(bad) > 1 else "", ", ".join(bad))
    #
    # Add extra fields
    #
    addField = []
    for f in args.addField:
        for f in re.split(r",\s*", f):
            addField.append(f)

    for f in addField:
        fieldList.append(f)
        if not f in fmts:
            fmts[f] = "%10s"
    #
    # If present, move "memo" to the end of the list
    #
    try:
        i = fieldList.index("memo")
        del fieldList[i:i+1]
        fieldList.append("memo")
    except:
        pass

    vals = []
    where = []
    for k, glob in [("filter", False), ("dateObs", True), ("object", True), ("rerun", True),
                    ("visit", True)]:
        v = getattr(args, k)
        if k == "object":
            k = "field"

        if v:
            w = []
            exclude = None
            for v in v:
                exclude_old = exclude
                if v[0] == "^":
                    exclude = True
                    v = v[1:]
                else:
                    exclude = False

                if exclude_old is not None and exclude_old != exclude:
                    print >> sys.stderr, "You must either exclude or accept all of your \"%s\"s: %s" % \
                        (k, ", ".join(getattr(args, k)))
                    sys.exit(1)

                vals.append(v)
                if glob:
                    w.append("%s%s glob ?" % ("NOT " if exclude else "", k))
                else:
                    w.append("%s%s    = ?" % ("NOT " if exclude else "", k))

            logical = " %s " % ("AND" if exclude else "OR")
            where.append("(%s)" % logical.join(w))

    if args.verbose:
        print "where:", where
        print "vals:", vals

    readLog(args.file, args.separator, addField, (where, vals), args.reverse, verbose=args.verbose)
