#!/usr/bin/env python3
"""CGI script to read PostGIS for available times for one layer."""
import os
import sys
import cgi
import json
from datetime import datetime
import psycopg2
from psycopg2 import sql


def bad_request(msg):
    print("""Status: 400 Bad Request
Content-type: text/plain
Access-Control-Allow-Origin: *

{}""".format(msg))
    sys.exit(1)


def get_connect_str():
    pw_filename = "__POSTGRES_PASSWORD_FILE__"
    connect_str = "host=__POSTGRES_HOST__ " \
                  "port=__POSTGRES_PORT__ " \
                  "dbname=__POSTGRES_DBNAME__ " \
                  "user=__POSTGRES_USER__"
    if not os.path.isfile(pw_filename) or '__' in connect_str:
        print("""Status: 500 Backend Misconfigured\n
Content-type: text/plain
Access-Control-Allow-Origin: *

Backend has not been configured properly and doesn't know how to communicate with the database.
Please contact site administrator.
    """)
        sys.exit(1)

    with open(pw_filename, 'r') as pw_file:
        password = pw_file.read().strip()
        connect_str += " password={}".format(password)
    return connect_str


connect_str = get_connect_str()
form = cgi.FieldStorage()
table_name = form.getvalue('layer')
start_time = form.getvalue('start_time')
end_time = form.getvalue('end_time')
num_times = form.getvalue('num_times', 0)
order = form.getvalue('order', 'ASC')

if table_name is None:
    bad_request("Missing required parameter 'layer'.")

if start_time is not None:
    try:
        start_time = datetime.strptime(start_time, "%Y-%m-%dT%H:%M:%S")
    except ValueError:
        bad_request("""Bad 'start_time' format. Expected: 'YYYY-MM-DDTHH:MM:SS'""")

if end_time is not None:
    try:
        end_time = datetime.strptime(end_time, "%Y-%m-%dT%H:%M:%S")
    except ValueError:
        bad_request("""Bad 'end_time' format. Expected: 'YYYY-MM-DDTHH:MM:SS'""")

try:
    num_times = int(num_times)
    if num_times < 0:
        raise ValueError("'num_times' must greater or equal to 0.")
except ValueError:
    bad_request("""Invalid integer for 'num_times'.""")

order = order.upper()
if order not in ('ASC', 'DESC'):
    bad_request("""'order' must be either 'ASC' or 'DESC'.""")

query_str = """SELECT to_char(start_time, 'YYYY-MM-DD"T"HH24:MI:SS') FROM {}"""
if start_time is not None or end_time is not None:
    query_str += " WHERE "
if start_time is not None:
    query_str += "start_time >= timestamp '{}'".format(start_time.strftime('%Y-%m-%dT%H:%M:%S'))
if start_time is not None and end_time is not None:
    query_str += " AND "
if end_time is not None:
    query_str += "start_time <= timestamp '{}'".format(end_time.strftime('%Y-%m-%dT%H:%M:%S'))
query_str += " ORDER BY start_time {}".format(order)
if num_times != 0:
    query_str += " LIMIT {}".format(num_times)

try:
    conn = psycopg2.connect(connect_str)
    with conn:
        with conn.cursor() as cur:
            cur.execute(sql.SQL(query_str).format(sql.Identifier(table_name)))
            times = cur.fetchall()
except psycopg2.errors.DatabaseError:
    print("""Status: 500 Database Error
Content-type: text/plain
Access-Control-Allow-Origin: *

Error requesting time information from database.
""")
    sys.exit(1)

# postgres returned a list of tuples
times = [time_tuple[0] for time_tuple in times]

print("Content-Type: application/json")
print("Access-Control-Allow-Origin: *")
print()  # blank line, end of headers
print(json.dumps(times))