import subprocess
from pyiem.util import get_dbconn

conn = get_dbconn("hads")
for yr in range(2023, 2024):
    for mo in range(1, 5):
        print(yr, mo)
        cursor = conn.cursor('streamer')
        cursor.execute(f"select station, valid at time zone 'UTC' as utc_valid, key, value, depth, unit_convention, qualifier, dv_interval, st_x(geom) as lon, st_y(geom) as lat, elevation from raw{yr}_{mo:02.0f} r JOIN stations t on (r.station = t.id)")
        with open(f'hads_{yr}{mo:02.0f}.csv', 'w', encoding='ascii') as fh:
            fh.write("station,utc_valid,key,value,depth,unit_convention,qualifier,dv_interval,lon,lat,elevation_meters\n")
            for row in cursor:
                try:
                    fh.write((",".join([str(a) for a in row])).replace("None", "") + "\n")
                except:
                    print(row)
        cursor.close()
        subprocess.call(f"gzip hads_{yr}{mo:02.0f}.csv", shell=True)