#!/usr/bin/env python3
from datetime import datetime, timedelta
import json
import logging
import sys

import pg8000

EXIF_KEY_MAPPING = {
    # [smart album key]: [db column]
    "camera": "camera_make",
    "model": "camera_model",
    "exposure": "exposure",
    "aperture": "aperture",
    "iso": "iso",
    "focal": "focal_length_v2",
    "lens": "lens_v2",
    "flash": "flash_v2",
    "rating": "rating",
}
EXIF_KEYS = set(EXIF_KEY_MAPPING.keys())


class SmartAlbum:
    def __init__(self, config, cursor):
        self.cursor = cursor

        self.name = config.get("name")
        self.show_photo = config.get("show_photo", True)  # key may not exist
        self.show_video = config.get("show_video", True)
        self.rule_sets = config.get("rule_sets", [])

        self.item_path = []
        self.parse_rule_sets(self.rule_sets)

    def get_name(self):
        return self.name

    def get_item_path(self):
        return self.item_path

    def get_count(self):
        return len(self.item_path)

    def parse_rule_sets(self, rule_sets):
        all_photo_ids = (
            self.get_items_by_rule_sets(rule_sets, "photo")
            if self.show_photo
            else set()
        )
        all_video_paths = (
            self.get_items_by_rule_sets(rule_sets, "video")
            if self.show_video
            else set()
        )

        recently_condition = self.parse_recently_condition(rule_sets)

        if recently_condition is None:
            if all_photo_ids is None:
                all_photo_ids = []
                logging.warning("something is wrong")
            if all_video_paths is None:
                all_video_paths = []
                logging.warning("something is wrong")
            self.item_path = self.filter_with_disabled(all_photo_ids, all_video_paths)
        else:
            self.item_path = self.filter_with_recently_condition(
                recently_condition, all_photo_ids, all_video_paths
            )

    def get_items_by_rule_sets(self, rule_sets, item_type):
        all_items = None

        for rules in rule_sets:
            for rule in rules:
                field = rule.get("field")
                operator = rule.get("operator")
                value = rule.get("value")

                items = self.get_ids_by_rule(field, operator, value, item_type)
                if items is None:
                    # recently rule
                    continue

                if all_items is None:
                    all_items = set(items)
                else:
                    all_items.intersection_update(items)
                    if len(all_items) == 0:
                        return []

        return all_items

    def parse_recently_condition(self, rule_sets):
        condition = None

        for rules in rule_sets:
            for rule in rules:
                field = rule.get("field")
                operator = rule.get("operator")
                value = rule.get("value")

                if operator == "recently_add" and field == "date":
                    condition = {
                        "type": "upload",
                        "limit": int(value),
                    }
                if operator == "recently_comment" and field == "date":
                    condition = {
                        "type": "comment",
                        "limit": int(value),
                    }
        return condition

    def filter_with_disabled(self, photo_ids, video_paths):
        photos = self.get_photo_path_by_ids(photo_ids)
        videos = self.filter_video_path_with_disabled(video_paths)
        return photos + videos

    def get_photo_path_by_ids(self, photo_ids):
        count = len(photo_ids)
        if count == 0:
            return []

        sql = """
            SELECT path
            FROM photo_image
            WHERE disabled='f' AND id IN ({})
        """.format(
            self.generate_placeholder(count)
        )

        self.cursor.execute(sql, photo_ids)
        return [x for [x] in self.cursor]

    def filter_video_path_with_disabled(self, video_paths):
        count = len(video_paths)
        if count == 0:
            return []

        sql = """
            SELECT path
            FROM video
            WHERE disabled='f' AND path IN ({})
        """.format(
            self.generate_placeholder(count)
        )

        self.cursor.execute(sql, video_paths)
        return [x for [x] in self.cursor]

    def filter_with_recently_condition(
        self, recently_condition, all_photo_ids, all_video_paths
    ):
        recently_type = recently_condition["type"]
        recently_limit = recently_condition["limit"]

        if recently_type == "upload":
            return self.filter_with_recently_upload(
                all_photo_ids, all_video_paths, recently_limit
            )
        elif recently_type == "comment":
            return self.filter_with_recently_comment(
                all_photo_ids, all_video_paths, recently_limit
            )
        return []

    def filter_with_recently_upload(self, photo_ids, video_paths, recently_limit):
        photo_sql, photo_params = self.get_photo_sql_for_recently_upload(photo_ids)
        video_sql, video_params = self.get_video_sql_for_recently_upload(video_paths)

        return self.filter_with_recently_photo_and_video(
            photo_sql, photo_params, video_sql, video_params, recently_limit
        )

    def get_photo_sql_for_recently_upload(self, photo_ids):
        sql = """
            SELECT
                'photo' AS type,
                id,
                path,
                create_time AS recently_time
            FROM photo_image
            WHERE disabled='f'
        """
        return self.get_sql_for_recently(sql, "id", photo_ids)

    def get_video_sql_for_recently_upload(self, video_paths):
        sql = """
            SELECT
                'video' AS type,
                id,
                path,
                date AS recently_time
            FROM video
            WHERE disabled='f'
        """
        return self.get_sql_for_recently(sql, "path", video_paths)

    def filter_with_recently_photo_and_video(
        self, photo_sql, photo_params, video_sql, video_params, recently_limit
    ):

        if photo_sql and video_sql:
            base_sql = "{} UNION ALL {}".format(photo_sql, video_sql)
            params = photo_params + video_params
        elif photo_sql:
            base_sql = photo_sql
            params = photo_params
        elif video_sql:
            base_sql = video_sql
            params = video_params
        else:
            return []

        sql = """
            {}
            ORDER BY recently_time DESC
            LIMIT %s
        """.format(
            base_sql
        )
        params.append(recently_limit)

        self.cursor.execute(sql, params)
        return [path for [item_type, item_id, path, recently_time] in self.cursor]

    def filter_with_recently_comment(self, photo_ids, video_paths, recently_limit):
        photo_sql, photo_params = self.get_photo_sql_for_recently_comment(photo_ids)
        video_sql, video_params = self.get_video_sql_for_recently_comment(video_paths)

        return self.filter_with_recently_photo_and_video(
            photo_sql, photo_params, video_sql, video_params, recently_limit
        )

    def get_sql_for_recently(self, sql, value_key, values):
        if values is None:
            return sql, []

        count = len(values)
        if count == 0:
            return "", []

        sql += """
            AND {} IN ({})
        """.format(
            value_key, self.generate_placeholder(count)
        )
        return sql, list(values)

    def get_photo_sql_for_recently_comment(self, photo_ids):
        sql = """
            SELECT
                'photo' AS type,
                id,
                path,
                recently_time
            FROM photo_image
            LEFT JOIN (
                SELECT photo_id AS photo_id, MAX(date) AS recently_time
                FROM photo_comment
                GROUP BY photo_id
            ) comment
            ON photo_image.id = comment.photo_id
            WHERE disabled='f' AND recently_time IS NOT NULL
        """
        return self.get_sql_for_recently(sql, "id", photo_ids)

    def get_video_sql_for_recently_comment(self, video_paths):
        sql = """
            SELECT
                'video' AS type,
                id,
                path,
                recently_time
            FROM video
            LEFT JOIN (
                SELECT path AS video_path, MAX(date) AS recently_time
                FROM video_comment GROUP BY path
            ) comment
            ON video.path = comment.video_path
            WHERE disabled='f' AND recently_time IS NOT NULL
        """
        return self.get_sql_for_recently(sql, "path", video_paths)

    def get_ids_by_rule(self, field, operator, value, item_type):
        if field == "keyword":
            return self.get_ids_by_keyword(operator, value, item_type)
        if field == "date" and operator == "taken":
            return self.get_ids_by_taken_time(value, item_type)
        if field == "date" and operator == "upload":
            return self.get_ids_by_upload_time(value, item_type)
        if field == "albums":
            return self.get_ids_by_album(value, item_type)
        if field in ("geo", "people", "desc"):
            return self.get_ids_by_label(value, operator, item_type)
        if field in EXIF_KEYS:
            return self.get_ids_by_exif(value, operator, item_type, field)

        return None

    def get_ids_by_exif(self, value, operator, item_type, field):
        values = value.split(",")
        count = len(values)
        if count == 0:
            return []

        if operator != "any":
            return []

        # video only support rating
        if item_type == "video" and field != "rating":
            return []

        if field == "flash":
            # flash may have value like "No# compulsory", which originally is "No, compulsory"
            values = [x.replace("#", ",") for x in values]

        key, table = self.get_key_and_table(item_type)
        column = EXIF_KEY_MAPPING[field]
        sql = "SELECT DISTINCT({}) FROM {} WHERE {} IN ({})".format(
            key, table, column, self.generate_placeholder(count)
        )

        self.cursor.execute(sql, values)
        return [x for [x] in self.cursor]

    def generate_placeholder(self, length):
        return ",".join(["%s"] * length)

    def get_ids_by_label(self, value, operator, item_type):
        label_ids = value.split(",")
        count = len(label_ids)
        if count == 0:
            return []

        key = "image_id" if item_type == "photo" else "video_path"
        table = "photo_image_label" if item_type == "photo" else "photo_video_label"
        if operator == "all":
            sql = self.get_sql_ids_by_all_label(key, table, count)
        elif operator == "any":
            sql = self.get_sql_ids_by_any_label(key, table, count)
        else:
            return []

        self.cursor.execute(sql, label_ids)
        return [x for [x] in self.cursor]

    def get_sql_ids_by_all_label(self, key, table, count):
        return """
            SELECT {}
            FROM {}
            WHERE label_id IN ({})
            GROUP BY {} HAVING COUNT(label_id) = {}
        """.format(
            key, table, self.generate_placeholder(count), key, count
        )

    def get_sql_ids_by_any_label(self, key, table, count):
        return """
            SELECT DISTINCT({})
            FROM {}
            WHERE label_id IN ({})
        """.format(
            key, table, self.generate_placeholder(count)
        )

    def parse_date(self, value):
        try:
            return datetime.strptime(value, "%Y-%m-%d")
        except ValueError:
            return None

    def format_date(self, value):
        return datetime.strftime(value, "%Y-%m-%d")

    def get_key_and_table(self, item_type):
        key = "id" if item_type == "photo" else "path"
        table = "photo_image" if item_type == "photo" else "video"

        return key, table

    def get_ids_by_album(self, value, item_type):
        share_ids = value.split(",")
        count = len(share_ids)
        if count == 0:
            return []

        key, table = self.get_key_and_table(item_type)
        sql = """
            SELECT DISTINCT({})
            FROM {}
            WHERE shareid IN ({})
        """.format(
            key, table, self.generate_placeholder(count)
        )

        self.cursor.execute(sql, share_ids)
        return [x for [x] in self.cursor]

    def get_ids_by_upload_time(self, value, item_type):
        field = "create_time" if item_type == "photo" else "date"
        return self.get_ids_by_time_range(value, item_type, field)

    def get_ids_by_taken_time(self, value, item_type):
        field = "timetaken" if item_type == "photo" else "mdate"
        return self.get_ids_by_time_range(value, item_type, field)

    def get_ids_by_time_range(self, value, item_type, field):
        key, table = self.get_key_and_table(item_type)

        if value == "unknown":
            condition = "{} IS NULL OR date({})='1970-01-01'".format(field)
            params = []
        else:
            dates = value.strip().split(",")
            if len(dates) != 2:
                return []
            begin = self.parse_date(dates[0])
            end = self.parse_date(dates[1])

            conditions = []
            params = []
            if begin is not None:
                conditions.append("{} >= %s".format(field))
                params.append(self.format_date(begin))
            if end is not None:
                conditions.append("{} < %s".format(field))
                params.append(self.format_date(end + timedelta(days=1)))

            if len(conditions) == 0:
                return []
            condition = " AND ".join(conditions)

        sql = "SELECT {} FROM {} WHERE {}".format(key, table, condition)

        self.cursor.execute(sql, params)
        return [x for [x] in self.cursor]

    def get_ids_by_keyword(self, operator, value, item_type):
        if operator in ["all", "any"]:
            keywords = value.split(" ")
        elif operator == "exact":
            keywords = [value]
        else:
            return []

        if item_type == "photo":
            return self.get_photo_ids_by_keyword(operator, keywords)

        if item_type == "video":
            return self.get_video_paths_by_keyword(operator, keywords)

    def run_sql_and_params(self, operator, sql_and_params):
        id_set = None
        for sql, params in sql_and_params:
            self.cursor.execute(sql, params)
            ids = [x for [x] in self.cursor]

            if id_set is None:
                id_set = set(ids)
            else:
                if operator == "any":
                    id_set.update(ids)
                elif operator == "all":
                    id_set.intersection_update(ids)
                else:
                    raise Exception("operator exact shall not run more than once")
        return list(id_set)

    def get_photo_ids_by_keyword(self, operator, keywords):
        sql_and_params = self.get_sql_photo_ids_by_keyword(keywords)
        return self.run_sql_and_params(operator, sql_and_params)

    def get_video_paths_by_keyword(self, operator, keywords):
        sql_and_params = self.get_sql_video_paths_by_keyword(keywords)
        return self.run_sql_and_params(operator, sql_and_params)

    def escape_for_like(self, value):
        return value.replace("_", "\\_").replace("%", "\\%")

    def get_sql_photo_ids_by_keyword(self, keywords):
        sql_and_params = []
        for keyword in keywords:
            sql = """
                SELECT DISTINCT(photo_image.id)
                FROM photo_image
                LEFT JOIN (
                    SELECT photo_image_label.*, photo_label.name AS label_name
                    FROM photo_image_label
                    LEFT JOIN photo_label ON photo_label.id = photo_image_label.label_id
                ) photo_image_label ON photo_image.id = photo_image_label.image_id
                WHERE
                    lower(name) LIKE %s OR
                    lower(title) LIKE %s OR
                    lower(description) LIKE %s OR
                    lower(label_name) LIKE %s
            """
            params = ["%{}%".format(self.escape_for_like(keyword.lower()))] * 4
            sql_and_params.append((sql, params))

        return sql_and_params

    def get_sql_video_paths_by_keyword(self, keywords):
        sql_and_params = []
        for keyword in keywords:
            sql = """
                SELECT DISTINCT(video.path)
                FROM (
                    SELECT V.title as title, VD.title as desc_title, VD.description, V.path
                    FROM video as V
                    LEFT JOIN video_desc as VD ON V.path = VD.path
                ) video
                LEFT JOIN (
                    SELECT photo_video_label.*, photo_label.name AS label_name
                    FROM photo_video_label
                    LEFT JOIN photo_label ON photo_label.id = photo_video_label.label_id
                ) photo_video_label ON video.path = photo_video_label.video_path
                WHERE
                    lower(title) LIKE %s OR
                    lower(desc_title) LIKE %s OR
                    lower(description) LIKE %s OR
                    lower(label_name) LIKE %s
            """
            params = ["%{}%".format(self.escape_for_like(keyword.lower()))] * 4
            sql_and_params.append((sql, params))

        return sql_and_params


class SmartAlbums:
    def __init__(self):
        self.cursor = self.init_cursor("photo")
        self.albums = []

        configs = self.read_config()
        for name, config in configs.get("smart_albums").items():
            config["name"] = name
            self.albums.append(SmartAlbum(config, self.cursor))

    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 get(self):
        result = []
        for album in self.albums:
            result.append(
                {
                    "name": album.get_name(),
                    "item": album.get_item_path(),
                    "count": album.get_count(),
                }
            )

        return result

    def read_config(self):
        path = "/var/services/photo/@eaDir/SYNOPHOTO_SMART_ALBUM_CONFIG"
        return json.load(open(path, "r"))


def main():
    albums = SmartAlbums()

    result = albums.get()

    return result


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

    if len(sys.argv) >= 2:
        path = sys.argv[1]
        json.dump(result, open(path, "w"))
    else:
        print(json.dumps(result, indent=2))