#!/usr/bin/env python3
import json
import logging
import os.path
import sys

import pg8000

MIGRATE_FILE_NAME = "SYNOPHOTO_MIGRATE_METADATA"
LABEL_CATEGORY_MAP = {
    "person": 0,
    "place": 1,
    "general": 2,
}


class BaseMetadata:
    def __init__(self):
        self.cursor = self.init_cursor("postgres")

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

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

        return cursor

    def dump(self):
        pass

    def run_sql(self, sql, key, root_folder=None, skip_jpeg=False, overwrite=False):
        self.cursor.execute(sql)

        count = 0
        for row in self.cursor:
            path = self.get_full_path(row, root_folder)
            if skip_jpeg and self.is_jpeg_path(path):
                continue

            value = self.sanitize_value(row, key)
            if not value:
                continue

            self.write_migrate_file(path, key, value, overwrite=overwrite)
            count += 1

        logging.info("dump {}, count: {}".format(key, count))

    def get_full_path(self, row, root_folder):
        row_length = len(row)
        if row_length == 2:
            return row[0]
        elif row_length == 3:
            [filename, folder_name, value] = row
            return os.path.join(root_folder, folder_name[1:], filename)

        raise ValueError("row length is not 2 or 3")

    def get_value_from_row(self, row):
        row_length = len(row)
        if row_length == 2:
            value = row[1]
        elif row_length == 3:
            value = row[2]
        else:
            raise ValueError("row length is not 2 or 3")
        return value

    def sanitize_value(self, row, key):
        value = self.get_value_from_row(row)

        # desc, rating, general, place, person
        if key == "rating":
            if not isinstance(value, int) or value > 5 or value < 0:
                return None
        elif key in ("general", "place", "person"):
            value = self.sanitize_str_value(value)
            if not value:
                return None
            return [value]
        elif key == "desc":
            value = self.sanitize_str_value(value)
            return value
        else:
            return None

    def sanitize_str_value(self, value):
        if not isinstance(value, str):
            return None
        return value.strip()

    def load_content(self, path):
        if not os.path.exists(path):
            return {}

        try:
            return json.load(open(path, "r"))
        except Exception as err:
            logging.warning("Failed to load json file {}. error: {}".format(path, err))

    def fill_content(self, content, key, value, overwrite=False):
        if key in content:
            if overwrite:
                content[key] = value
            elif isinstance(content[key], list) and isinstance(value, list):
                content[key] = list(set(content[key] + value))
            else:
                # overwrite is False and not list type, skip it
                pass
        else:
            content[key] = value

    def get_ea_dir_path(self, image_path):
        dirname, filename = os.path.split(image_path)
        return os.path.join(dirname, "@eaDir", filename)

    def is_jpeg_path(self, path):
        _, ext = os.path.splitext(path.lower())
        return ext in (".jpeg", ".jpg", ".jpe")

    def write_migrate_file(self, path, key, value, overwrite=False):
        if not os.path.exists(path):
            logging.warning(
                "path {} not exists, skip writing migrate file".format(path)
            )
            return

        ea_dir_path = self.get_ea_dir_path(path)
        if not os.path.exists(ea_dir_path):
            logging.warning("ea dir {} not exists, create it".format(ea_dir_path))
            os.makedirs(ea_dir_path)

        migrate_file_path = os.path.join(ea_dir_path, MIGRATE_FILE_NAME)
        content = self.load_content(migrate_file_path)
        self.fill_content(content, key, value, overwrite)

        try:
            with open(migrate_file_path, "w") as f:
                f.write(json.dumps(content, ensure_ascii=False))
        except Exception as err:
            logging.warning(
                "Failed to write json file {}. error: {}".format(migrate_file_path, err)
            )

        logging.info("dump to file {}".format(migrate_file_path))


class PhotoStationMetadata(BaseMetadata):
    def __init__(self):
        self.cursor = self.init_cursor("photo")

    def dump(self):
        self.run_sql(self.get_image_labels_sql("general"), "general", skip_jpeg=True)
        self.run_sql(self.get_image_labels_sql("place"), "place")
        # migrate person label or not, to be discussed
        # self.run_sql(self.get_image_labels('person'), 'person')
        self.run_sql(self.get_image_desc_sql(), "desc", skip_jpeg=True)
        # self.run_sql(self.get_image_rating_sql(), 'rating', skip_jpeg=True)

        self.run_sql(self.get_video_labels_sql("general"), "general")
        self.run_sql(self.get_video_labels_sql("place"), "place")
        self.run_sql(self.get_video_desc_sql(), "desc")
        # self.run_sql(self.get_video_rating_sql(), 'rating')

    def get_image_labels_sql(self, tag_type):
        category = LABEL_CATEGORY_MAP[tag_type]
        # unconfirmed person label has empty name
        return """
            SELECT Image.path as path, Label.name as value
            FROM photo_image as Image, photo_image_label as Relation, photo_label as Label
            WHERE
                Image.id = Relation.image_id AND
                Label.id = Relation.label_id AND
                Label.name != '' AND
                Label.category = {}
            ORDER BY path
        """.format(
            category
        )

    def get_video_labels_sql(self, tag_type):
        category = LABEL_CATEGORY_MAP[tag_type]
        return """
            SELECT Video.path as path, Label.name as value
            FROM video as Video, photo_video_label as Relation, photo_label as Label
            WHERE
                Video.path = Relation.video_path AND
                Label.id = Relation.label_id AND
                Label.name != '' AND
                Label.category = {}
            ORDER BY path
        """.format(
            category
        )

    def get_image_desc_sql(self):
        return self.get_desc_sql("photo_image")

    def get_video_desc_sql(self):
        return self.get_desc_sql("video_desc")

    def get_desc_sql(self, table):
        return """
            SELECT path, description as value
            FROM {}
            WHERE
                description != ''
            ORDER BY path
        """.format(
            table
        )

    def get_image_rating_sql(self):
        return self.get_rating_sql("photo_image")

    def get_video_rating_sql(self):
        return self.get_rating_sql("video")

    def get_rating_sql(self, table):
        return """
            SELECT path, rating as value
            FROM {}
            WHERE
                rating != 0
            ORDER BY path
        """.format(
            table
        )


class MomentsMetadata(BaseMetadata):
    def __init__(self):
        self.cursor = self.init_cursor("synophoto")

    def dump(self):
        users = self.get_users()

        for [id_user, root_folder] in users:
            self.switch_to_user(id_user)
            self.run_sql(
                self.get_labels_sql(),
                "general",
                root_folder=root_folder,
                skip_jpeg=True,
            )
            self.run_sql(
                self.get_desc_sql(), "desc", root_folder=root_folder, skip_jpeg=True
            )

    def get_users(self):
        sql = """
            SELECT id, uid, name
            FROM user_info
        """
        self.cursor.execute(sql)

        users = []
        for [id_user, uid, name] in self.cursor:
            if not name:
                continue

            if uid:
                user_home = os.path.expanduser("~{}".format(name))
                users.append((id_user, os.path.join(user_home, "Drive")))
            else:
                users.append((id_user, name))

        return users

    def switch_to_user(self, id_user):
        sql = "SET search_path TO user_{}".format(id_user)
        self.cursor.execute(sql)

    def get_labels_sql(self):
        return """
            SELECT U.filename as filename, F.name as folder_name, T.name as value
            FROM unit as U, folder as F, general_tag as T, many_unit_has_many_general_tag as M
            WHERE
                U.id = M.id_unit AND
                U.id_folder = F.id AND
                M.id_general_tag = T.id
            ORDER BY F.name, U.filename
        """

    def get_desc_sql(self):
        return """
            SELECT U.filename as filename, F.name as folder_name, M.description as value
            FROM unit as U, folder as F, metadata as M
            WHERE
                U.id = M.id_unit AND
                U.id_folder = F.id AND
                M.description != ''
            ORDER BY F.name, U.filename
        """


def photo_station_main():
    meta = PhotoStationMetadata()
    meta.dump()


def moments_main():
    meta = MomentsMetadata()
    meta.dump()


def usage():
    print("Usage: {} [photo_station | moments]".format(sys.argv[0]))
    sys.exit(-1)


if __name__ == "__main__":
    logging.basicConfig(level=logging.INFO)

    if len(sys.argv) == 2:
        app = sys.argv[1]
        if app == "photo_station":
            photo_station_main()
        elif app == "moments":
            moments_main()
        else:
            usage()
    else:
        usage()