#!/usr/bin/env python3

import json
import logging
import sys
import os
import pg8000

from argparse import ArgumentParser


def init_cursor():
    user = "postgres"
    unix_sock = "/var/run/postgresql/.s.PGSQL.5432"
    database = "synophoto"
    conn = pg8000.connect(user, unix_sock=unix_sock, database=database)

    cursor = conn.cursor()
    cursor.execute("SET CLIENT_ENCODING TO 'UTF8'")

    return cursor


def GetAllUserInfo(cursor):
    cursor.execute(
        """
        SELECT id, uid, name, config, enable FROM user_info
    """
    )
    result = toJSON(cursor)
    return result


def GetUserInfoById(cursor, id):
    cursor.execute(
        """
        SELECT id, uid, name, config, enable FROM user_info
        WHERE id = {}
    """.format(
            id
        )
    )
    result = toJSON(cursor)
    return result


def GetNamedAndHiddenPerson(cursor, id_user_info, offset, limit):
    cursor.execute(
        """
        SELECT * FROM user_{}.person
        WHERE name <> '' OR hidden = 't'
        ORDER BY id
        OFFSET {} LIMIT {}
    """.format(
            id_user_info, offset, limit
        )
    )
    result = toJSON(cursor)
    return result


def GetFace(cursor, id_user_info, id_person, offset, limit):
    cursor.execute(
        """
        SELECT id, bounding_box, id_unit, ref_id_unit
        FROM user_{}.face
        WHERE id_person = {}
        ORDER BY id
        OFFSET {} LIMIT {}
    """.format(
            id_user_info, id_person, offset, limit
        )
    )

    result = []
    for id, bounding_box, id_unit, ref_id_unit in cursor.fetchall():
        result.append(
            {
                "id": id,
                "bounding_box": bounding_box,
                "path": GetPath(cursor, id_user_info, ref_id_unit),
                "id_unit": id_unit,
                "ref_id_unit": ref_id_unit,
            }
        )
    return result


def GetPath(cursor, id_user_info, ref_id_unit):
    cursor.execute(
        """
        SELECT filename, id_folder FROM user_{}.unit
        WHERE id = {}
    """.format(
            id_user_info, ref_id_unit
        )
    )
    filename, id_folder = cursor.fetchone()

    cursor.execute(
        """
        SELECT name FROM user_{}.folder
        WHERE id = {}
    """.format(
            id_user_info, id_folder
        )
    )
    [folder_name] = cursor.fetchone()

    return os.path.join(folder_name, filename)


def CountFaceNeedToMigrate(cursor, id_user_info):
    cursor.execute(
        """
        SELECT count(*) FROM user_{}.face
        WHERE id_person IN (
            SELECT id FROM user_{}.person
            WHERE name <> '' OR hidden = 't'
        )
    """.format(
            id_user_info, id_user_info
        )
    )
    count = toJSON(cursor)

    return count


def GetTeamLibraryPath(cursor):
    cursor.execute(
        """
        SELECT path FROM team_library
        ORDER BY name ASC
    """
    )
    path = ""

    if row := cursor.fetchone():
        [path] = row

    return {"path": path}


def DumpAllFace(unit_cursor, id_user_info):
    unit_cursor.execute(
        """
        SELECT id, filename, id_folder FROM user_{}.unit
        ORDER BY id_folder, id
    """.format(
            id_user_info
        )
    )

    data_cursor = init_cursor()
    result = {}

    while True:
        row = unit_cursor.fetchone()
        if row == None:
            break
        id_unit, filename, id_folder = row

        data_cursor.execute(
            """
            SELECT name FROM user_{}.folder
            WHERE id = {}
        """.format(
                id_user_info, id_folder
            )
        )
        [folder_name] = data_cursor.fetchone()

        path = os.path.join(folder_name, filename)

        faces = []

        data_cursor.execute(
            """
            SELECT bounding_box FROM user_{}.face
            WHERE ref_id_unit = {}
        """.format(
                id_user_info, id_unit
            )
        )

        for [bounding_box] in data_cursor.fetchall():
            faces.append(bounding_box)

        result[path] = faces

    return result


def toJSON(cursor):
    cols = [desc[0].decode("utf-8") for desc in cursor.description]
    result = [dict(zip(cols, row)) for row in cursor]
    return result


def args_parser(argv):
    argparser = ArgumentParser()
    argparser.add_argument("output_path", type=str, help="specify output filepath")
    subparsers = argparser.add_subparsers()

    GetAllUserInfo_parser = subparsers.add_parser("GetAllUserInfo")
    GetAllUserInfo_parser.set_defaults(function=GetAllUserInfo, which="GetAllUserInfo")

    GetUserInfoById_parser = subparsers.add_parser("GetUserInfoById")
    GetUserInfoById_parser.add_argument(
        "--id-user", type=int, required=True, help="user id"
    )
    GetUserInfoById_parser.set_defaults(
        function=GetUserInfoById, which="GetUserInfoById"
    )

    GetNamedAndHiddenPerson_parser = subparsers.add_parser("GetNamedAndHiddenPerson")
    GetNamedAndHiddenPerson_parser.add_argument(
        "--id-user", type=int, required=True, help="user id"
    )
    GetNamedAndHiddenPerson_parser.add_argument(
        "-o", "--offset", type=int, required=True, help="offset"
    )
    GetNamedAndHiddenPerson_parser.add_argument(
        "-l", "--limit", type=int, required=True, help="limit"
    )
    GetNamedAndHiddenPerson_parser.set_defaults(
        function=GetNamedAndHiddenPerson, which="GetNamedAndHiddenPerson"
    )

    GetFace_parser = subparsers.add_parser("GetFace")
    GetFace_parser.add_argument("--id-user", type=int, required=True, help="user id")
    GetFace_parser.add_argument(
        "--id-person", type=int, required=True, help="person id"
    )
    GetFace_parser.add_argument(
        "-o", "--offset", type=int, required=True, help="offset"
    )
    GetFace_parser.add_argument("-l", "--limit", type=int, required=True, help="limit")
    GetFace_parser.set_defaults(function=GetFace, which="GetFace")

    CountFaceNeedToMigrate_parser = subparsers.add_parser("CountFaceNeedToMigrate")
    CountFaceNeedToMigrate_parser.add_argument(
        "--id-user", type=int, required=True, help="user id"
    )
    CountFaceNeedToMigrate_parser.set_defaults(
        function=CountFaceNeedToMigrate, which="CountFaceNeedToMigrate"
    )

    GetTeamLibraryPath_parser = subparsers.add_parser("GetTeamLibraryPath")
    GetTeamLibraryPath_parser.set_defaults(
        function=GetTeamLibraryPath, which="GetTeamLibraryPath"
    )

    DumpAllFace_parser = subparsers.add_parser("DumpAllFace")
    DumpAllFace_parser.add_argument(
        "--id-user", type=int, required=True, help="user id"
    )
    DumpAllFace_parser.set_defaults(function=DumpAllFace, which="DumpAllFace")

    args = argparser.parse_args()
    return args


def main(argv):
    cursor = init_cursor()

    args = args_parser(argv)
    if args.which == "GetAllUserInfo":
        result = args.function(cursor)
    elif args.which == "GetUserInfoById":
        result = args.function(cursor, args.id_user)
    elif args.which == "GetFace":
        result = args.function(
            cursor, args.id_user, args.id_person, args.offset, args.limit
        )
    elif args.which == "GetNamedAndHiddenPerson":
        result = args.function(cursor, args.id_user, args.offset, args.limit)
    elif args.which == "CountFaceNeedToMigrate":
        result = args.function(cursor, args.id_user)
    elif args.which == "GetTeamLibraryPath":
        result = args.function(cursor)
    elif args.which == "DumpAllFace":
        result = args.function(cursor, args.id_user)

    if args.output_path:
        path = args.output_path
        json.dump(result, open(path, "w"))
    else:
        print(json.dumps(result))

    return result


if __name__ == "__main__":
    ret = 0
    try:
        result = main(sys.argv[1:])
    except Exception as e:
        ret = 1
        logging.error(" ".join(sys.argv) + " failed!")
        print(e)
    sys.exit(ret)