#!/usr/bin/env python3

from collections import namedtuple
import json
import sys

import pg8000

has_cannot_handle_type = False
has_count_not_match = False


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

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

    return cursor


def get_duplication_filters(cursor):

    cursor.execute(
        """
        SELECT id_user, id_unit, filter_type, COUNT(id_filter) as count
        FROM filter
        WHERE filter_type in ('flash', 'aperture', 'camera', 'exposure_time', 'focal_length', 'folder', 'iso', 'item_type', 'lens', 'takentime')
        GROUP BY id_user, id_unit, filter_type
        HAVING count(id_filter) > 1;
        """
    )

    filters = []
    for id_user, id_unit, filter_type, count in cursor:
        item = {
            "id_user": id_user,
            "id_unit": id_unit,
            "filter_type": filter_type,
            "count": count,
        }
        filters.append(item)

    return filters


def can_handle_type(filter_type):
    global has_cannot_handle_type

    if filter_type in [
        "flash",
        "aperture",
        "camera",
        "exposure_time",
        "focal_length",
        "iso",
        "lens",
    ]:
        return True

    # folder, item_type, takentime
    # This should not happen
    has_cannot_handle_type = True
    return False


def remove_dup_meta(cursor, item):
    global has_count_not_match

    cursor.execute(
        """
        SELECT {filter_type}
        FROM metadata
        WHERE id_unit = {id_unit}
        """.format(
            **item
        )
    )

    [value] = cursor.fetchone()

    if isinstance(value, str) and len(value) == 0:
        cursor.execute(
            """
            DELETE
            FROM filter
            WHERE id_user = {id_user} AND id_unit = {id_unit} AND filter_type = '{filter_type}'
            """.format(
                **item
            )
        )
        return

    if item["filter_type"] == "flash":
        id_filter = value
    else:
        cursor.execute(
            """
            SELECT id
            FROM {filter_type}
            WHERE name = %s
            """.format(
                **item
            ),
            (value,),
        )

        [id_filter] = cursor.fetchone()

    # print(id_filter)

    cursor.execute(
        """
        SELECT COUNT(*) as count
        FROM filter
        WHERE id_user = {id_user} AND id_unit = {id_unit} AND filter_type = '{filter_type}' AND id_filter != {id_filter}
        """.format(
            **item, id_filter=id_filter
        )
    )

    [count] = cursor.fetchone()

    if count != item["count"] - 1:
        print(">>> count not match")
        has_count_not_match = True
        return

    print(">>> delete count match, do delete")

    cursor.execute(
        """
        DELETE
        FROM filter
        WHERE id_user = {id_user} AND id_unit = {id_unit} AND filter_type = '{filter_type}' AND id_filter != {id_filter}
        """.format(
            **item, id_filter=id_filter
        )
    )


def handle(cursor, dup_filters):

    for item in dup_filters:
        print(item)
        if not can_handle_type(item["filter_type"]):
            continue

        remove_dup_meta(cursor, item)


def main():
    cursor = init_cursor()

    dup_filters = get_duplication_filters(cursor)

    if len(dup_filters) == 0:
        print("No need to do autofix")
        return

    success = handle(cursor, dup_filters)

    cursor.execute("COMMIT")

    if has_count_not_match:
        print("FATAL!! Has delete count not match. Cannot autofix.")
        sys.exit(1)

    if has_cannot_handle_type:
        print("FATAL!! Has unsupported filter_type. Cannot autofix.")
        sys.exit(1)

    print("Autofix successfully.")


if __name__ == "__main__":
    main()