# Copyright (c) Daniel Sheffield 2022 - 2024
# All rights reserved.
from bottle import route, request, run, template, response, abort, static_file
import psycopg
from psycopg import Cursor
from psycopg.sql import SQL, Literal
import os
host = f"host={os.getenv('HOST')}"
db = f"dbname={os.getenv('DB', 'pgdb')}"
user = f"user={os.getenv('USER', 'pgdb')}"
password = f"password={os.getenv('PASSWORD','')}"
if not password.split('=',1)[1]:
    password = ''
conn: Cursor = psycopg.connect(f"{host} {db} {user} {password}")

@route('/pyapi/random')
def random():
    try:
        with conn.cursor() as cur:
            inner = SQL("""
SELECT category, translation, reference, txt
FROM pg_random_view_default_if_null
JOIN pg_categories ON category = name
ORDER BY tableid ASC
""")
            xml = cur.execute(SQL("""
SELECT query_to_xml_and_xmlschema({q}, false, false, ''::text);
""").format(q=inner.as_string(cur))).fetchone()[0]
    finally:
        conn.commit()
    response.content_type = 'application/xhtml+xml; charset=utf-8'
    return template("rest/query-to-xml", title="Random Prayer Generator", xml=xml)

@route('/pyapi/xslt')
def table():
    response.content_type = 'application/xhtml+xml; charset=utf-8'
    return static_file("query-to-xml-xslt.xml", root="rest")

@route('/pyapi/pg')
def specified():
    kingdom = { 'a_kingdom', 'z_kingdom' }
    categories = [ k for k in request.query.keys() ]
    translations = []
    references = []
    for c in categories:
        try:
            r, t = request.query[c].split(' ', 1)
        except ValueError:
            raise abort(
                400,
                f"Could not parse value for {c}."
                f" Given: {request.query[c]}"
            )

        if None in (r or None, t or None,):
            raise abort(
                400,
                f"Both reference and translation must be provided for {c}."
                f" Given: {request.query[c]}"
            )
        references.append(r)
        translations.append(t)

    if len(kingdom - set(categories)) not in (0, 2,):
        raise abort(
            400,
            f"Both of {kingdom} must be specified or neither specified."
            f" Missing: {kingdom - set(categories)}"
        )

    try:
        with conn.cursor() as cur:
            inner = SQL("""
SELECT category, translation, reference, txt
    FROM getprayer(
        {categories}::text[],
        {translations}::text[],
        {references}::text[]
    ) ORDER BY ordinal ASC
""").format(
    categories=Literal(categories),
    translations=Literal(translations),
    references=Literal(references)
)
            xml = cur.execute(SQL("""
SELECT query_to_xml_and_xmlschema({q}, false, false, ''::text);
""").format(q=Literal(inner.as_string(cur)))).fetchone()[0]
    finally:
        conn.commit()
    response.content_type = 'application/xhtml+xml; charset=utf-8'
    return template("rest/query-to-xml", title="Prayer Generator", xml=xml)

run(host='0.0.0.0', port=11888)