#!/usr/bin/env python3
# -*- coding:utf-8 -*-

import json
import logging
import os
import subprocess
from pathlib import Path
from random import shuffle
from shlex import split

import ipfshttpclient
import psycopg2
from flexx import flx

import ipfsdocs
from ipfsdocs.common import URL

ipfshttpclient.VERSION_MAXIMUM = "0.10.0"

LOGGER = logging.getLogger(__name__)


def conn():
    return psycopg2.connect(f"dbname=docs user=postgres host={os.environ['SERVICEMESH_IP']}")


def execute(*commands):
    LOGGER.debug("begin transaction")
    with conn() as c:
        with c.cursor() as cursor:
            for command in commands:
                LOGGER.debug(" " + command)
                cursor.execute(command)
    LOGGER.debug("commit transaction")


def query(command):
    LOGGER.debug(" " + command)
    with conn() as c:
        with c.cursor() as cursor:
            cursor.execute(command)
            return cursor.fetchall()


class BackendJs(flx.JsComponent):
    def expose_links(self, values):
        self.root.state.set_query_results(values)
        self.root.state.set_number_of_elements(len(values))

    def init(self):
        global fetch, window, RegExp
        hash = window.location.href.split("/").pop()
        fetch(f"{URL}/ipfs/" + hash).then(lambda e: e.json()).then(self.expose_links)


class Backend(flx.PyComponent):
    def init(self):
        self.ipfs = IPFS()
        self.postgres = Postgres()


class IPFS(flx.PyComponent):
    @property
    def share_app_hash(self):
        with ipfshttpclient.connect(f"/ip4/{os.environ['SERVICEMESH_IP']}/tcp/5002") as c:
            return c.add(str(ipfsdocs.html), recursive=True, pin=False)[-1]["Hash"]

    @flx.reaction("root.jssays.share_one")
    def on_share(self, *events):
        for event in events:
            filename = event.filename
            cid = event.cid
            webcid = event.webcid
            with ipfshttpclient.connect(f"/ip4/{os.environ['SERVICEMESH_IP']}/tcp/5002") as c:
                files = c.files.ls("/")
                if (files["Entries"] is not None and "share" in [e["Name"] for e in files["Entries"]]):
                    c.files.rm("/share", recursive=True)
                c.files.mkdir("/share")
                c.files.mkdir("/share/web")
                c.files.cp(cid, f"/share/{filename}")
                c.files.cp(webcid, f"/share/web/{filename}")
                dirhash = c.files.stat("/share")["Hash"]
                self.root.pysays.shared_one(
                    f"/ipfs/{dirhash}/{filename}",
                    f"/ipfs/{dirhash}/web/{filename}",
                )

    @flx.reaction("root.jssays.share_selection")
    def on_share_selection(self, *events):
        self.share_some(
            [result for result in self.root.state.query_results if result["cid"] in self.root.state.selection])

    def share_some(self, share):
        with ipfshttpclient.connect(f"/ip4/{os.environ['SERVICEMESH_IP']}/tcp/5002") as c:
            hash = c.add_json(share, opts={"pin": "false"})
            # /ipns/QmUB7Xp973dkmZT75dbBPKWtqhpkyqiicT9SqmLWuEYbbT
            self.root.pysays.shared_selection(f"/ipfs/{self.share_app_hash}/ipfsdocsjs.html?/{hash}")

    @flx.reaction("root.jssays.share_all")
    def on_share_all(self, *events):
        self.share_some(self.root.state.query_results)


class Postgres(flx.PyComponent):
    def init(self):
        self.init_events()
        self.init_tags()

    @flx.reaction("root.state.owner")
    def init_tags(self, *events):
        self.root.state.set_tags({
            key: values.split("|")
            for key, values in query("select value->>'key' as key, string_agg(value->>'value', '|') as value"
                                     " from tag"
                                     f" where tag_owner = '{self.root.state.owner.lower()}'"
                                     " group by key")
        })

    @flx.reaction("root.jssays.delete_event")
    def delete_event(self, *events):
        event = events[-1]
        event_id = event.event_id
        execute(f"delete from event_old where event_id = '{event_id}'")
        self.root.pysays.ok()
        self.init_events()

    @flx.reaction(
        "root.state.owner",
        "root.state.from_date",
        "root.state.to_date",
    )
    def init_events(self, *events):
        def is_day_event(date):
            return date.hour == date.minute == date.second == 0

        def format_date(date):
            format_string = "%y/%m/%d"
            if not is_day_event(date):
                format_string += " %H:%M"
            return date.strftime(format_string)

        where = [f"owner = '{self.root.state.owner.lower()}'"]
        if self.root.state.from_date:
            where.append(f"date >= '{self.root.state.from_date}'")
        if self.root.state.to_date:
            where.append(f"date <= '{self.root.state.to_date}'")
        where_string = " and ".join(where)
        self.root.state.set_events([{
            "description": description,
            "date": format_date(date),
            "id": id,
        } for description, date, id in query("select description, date, event_id from event_old"
                                             f" where {where_string}"
                                             " order by date")])

    @flx.reaction("root.jssays.add_tag_to_selection")
    def add_tag_to_selection(self, *events):
        event = events[-1]
        cids = [f"('{cid}')" for cid in self.root.state.selection.keys()]
        execute(f"""
with id_of_tag as (
     select tag.tag_id
     from tag
     where tag.value = $TAGVALUE${{ "key": "{event.key}", "value": "{event.value}" }}$TAGVALUE$
)
,    cids as (
     select cid from (
          values {", ".join(cids)}
     ) as t(cid)
)
insert into tagmap (cid, tag_id)
select cids.cid, id_of_tag.tag_id
from cids, id_of_tag
ON CONFLICT DO NOTHING;
""")
        self.root.pysays.ok()
        self.query_updated()

    @flx.reaction("root.jssays.remove_tag_from_selection")
    def remove_tag_from_selection(self, *events):
        event = events[-1]
        cids = [f"('{cid}')" for cid in self.root.state.selection.keys()]
        execute(f"""
with id_of_tag as (
     select tag.tag_id
     from tag
     where tag.value = $TAGVALUE${{ "key": "{event.key}", "value": "{event.value}" }}$TAGVALUE$
)
,    cids as (
     select cid from (
          values {", ".join(cids)}
     ) as t(cid)
)
delete from tagmap
where cid in (select cid from cids)
and tag_id in (select tag_id from id_of_tag)
""")
        self.root.pysays.ok()
        self.query_updated()

    @flx.reaction("root.jssays.create_event")
    def create_event(self, *events):
        event = events[-1]
        execute("insert into event (description, date, owner) values ("
                f" $description${event.text}$description$,"
                f" $date${event.date}$date$,"
                f" $owner${self.root.state.owner.lower()}$owner$"
                " )")
        self.init_events()
        self.root.pysays.ok()

    @flx.reaction("root.jssays.add_new_tag")
    def create_tag(self, *events):
        event = events[-1]
        execute("insert into tag (value, tag_owner) values ("
                f""" $value${{ "key": "{event.key}", "value": "{event.value}" }}$value$,"""
                f" $owner${self.root.state.owner.lower()}$owner$"
                ")"
                " ON CONFLICT DO NOTHING;")
        self.init_tags()
        self.root.pysays.ok()

    @flx.reaction("root.jssays.remove_tag")
    def remove_tag(self, *events):
        event = events[-1]
        execute(
            f"""
            delete from tagmap where
            tag_id in (select tag_id from tag
            where
            value = $value${{ "key": "{event.key}", "value": "{event.value}" }}$value$
            and tag_owner = $owner${self.root.state.owner.lower()}$owner$
            )
            """, f"""
            delete from tag
            where
            value = $value${{ "key": "{event.key}", "value": "{event.value}" }}$value$
            and tag_owner = $owner${self.root.state.owner.lower()}$owner$
            """)
        self.init_tags()
        self.root.pysays.ok()

    @flx.reaction("root.jssays.count_tags")
    def count_tags(self, *events):
        event = events[-1]
        count = query(f"""select count(tag_id)
from tagmap
natural left join tag
where value = $value${{ "key": "{event.key}", "value": "{event.value}" }}$value$
and tag_owner = $owner${self.root.state.owner.lower()}$owner$
""")[0]
        self.root.pysays.counted_tags(event.key, event.value, count)

    @flx.reaction("root.jssays.delete_one")
    def delete(self, *events):
        event = events[-1]
        execute(f"update photovideo set state='delete' where cid='{event.cid}'")
        self.root.pysays.ok()
        self.query_updated()
        if event.cid in self.root.state.selection:
            self.root.state.remove_from_selection(event.cid)

    @flx.reaction("root.jssays.move_selection_to_state")
    def move_selection_to_state(self, *events):
        state = events[-1]["state"]
        cids = [f"'{cid}'" for cid in self.root.state.selection.keys()]
        execute(f"update file set state='{state}' where cid in ({', '.join(cids)})")
        self.root.pysays.ok()
        self.query_updated()

    @flx.reaction("root.jssays.move_selection_to_owner")
    def move_selection_to_owner(self, *events):
        owner = events[-1]["owner"]
        cids = [f"'{cid}'" for cid in self.root.state.selection.keys()]
        execute(f"update file set owner='{owner}', state='todo' where cid in ({', '.join(cids)})")
        self.root.pysays.ok()
        self.query_updated()

    @flx.reaction("root.jssays.move_selection_to_event")
    def move_selection_to_event(self, *events):
        event_id = events[-1]["event_id"]
        if event_id == -1:
            event_id = "null"
        cids = [f"'{cid}'" for cid in self.root.state.selection.keys()]
        execute(f"update photovideo set event_id={event_id} where cid in ({', '.join(cids)})")
        self.root.pysays.ok()
        self.query_updated()

    @flx.reaction("root.jssays.move_selection_to_date")
    def move_selection_to_date(self, *events):
        date = events[-1]["date"]
        cids = [f"'{cid}'" for cid in self.root.state.selection.keys()]
        execute(f"update file set date='{date}'where cid in ({', '.join(cids)})")
        self.root.pysays.ok()
        self.query_updated()

    @flx.reaction(
        "root.state.query",
        "root.state.current_event_id",
        "root.state.state",
        "root.state.owner",
        "root.state.active_tags",
        "root.state.query_limit",
        "root.state.query_order",
        "root.state.from_date",
        "root.state.to_date",
        "root.state.random",
        "root.pysays.compute",
    )
    def query_updated(self, *events):
        # select cid, string_agg("name", ',') from photovideo natural join
        # tagmap natural join tag group by cid
        # http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/
        # (using the toxi schema)
        in_tag = []
        not_in_tag = []
        for key, value in self.root.state.active_tags:
            if key.startswith("-"):
                key = key[len("-"):]
                not_in_tag.append(f"""{{"key":"{key}", "value": "{value}"}}""")
            else:
                in_tag.append(f"""{{"key":"{key}", "value": "{value}"}}""")
        in_tag_ids_query = f"""
select tag_id from tag
where json_build_array(value)::jsonb <@ $value$[{", ".join(in_tag)}]$value$::jsonb
and tag.tag_owner = '{self.root.state.owner.lower()}'
"""
        not_in_tag_ids_query = f"""
select tag_id from tag
where json_build_array(value)::jsonb <@ $value$[{", ".join(not_in_tag)}]$value$::jsonb
"""
        not_in_tag_query = f"""
select cid from file
natural left join tagmap
natural left join tag
where tag.tag_id in ({not_in_tag_ids_query})
"""
        query_ = ("select")
        attributes = [
            ("state", "res_state"),
            ("concat(to_char(f.date, 'YYMMDD_HHMMSS'), '-', right(f.cid, 3))", "res_filename"),
            ("f.mimetype", "res_mimetype"),
            ("f.tableoid::regclass", "res_class"),
            ("f.cid", "cid"),
            ("f.web_cid", "res_web_cid"),
            ("f.thumbnail_cid", "res_thumbnail_cid"),
            ("f.date", "res_date"),
            ("e.description", "res_description"),
        ]
        query_ += (" " + ", ".join(f"{selection} as {name}" for selection, name in attributes))
        query_ += (" from photovideo f"
                   " \nleft join event_old e using (event_id)"
                   " \nnatural left join tagmap"
                   " \nnatural left join tag t")
        query_ += (" \nwhere ")
        where = [f"state = '{self.root.state.state.lower()}'", f"f.owner = '{self.root.state.owner.lower()}'"]
        if in_tag:
            where += [f"t.tag_id in ({in_tag_ids_query})"]
        if not_in_tag:
            where += [f"cid not in ({not_in_tag_query})"]
        if self.root.state.current_event_id != -1:
            where.append(f"e.event_id = {self.root.state.current_event_id}")
        if self.root.state.from_date:
            where.append(f"f.date >= '{self.root.state.from_date}'")
        if self.root.state.to_date:
            where.append(f"f.date <= '{self.root.state.to_date}'")
        query_ += "\nand ".join(where)
        query_ += f"""\ngroup by {", ".join(f"{name}" for _, name in attributes)}"""
        if in_tag:
            query_ += f" \nhaving count (f.cid) = {len(in_tag)}"
        query_ = f"""
select {", ".join(f"{name}" for _, name in attributes)}, json_agg(tag.value) from
(
-- select all the files matching the criteria
{query_}
) as files
natural left join
(
-- select all the tags of that user to add the data in the result
  select * from tagmap natural left join tag
  where tag.tag_owner = '{self.root.state.owner.lower()}' or tag.tag_owner is null
) as tag
group by {", ".join(f"{name}" for _, name in attributes)}
order by res_date {self.root.state.query_order}
"""
        query_ += (f" \nlimit {self.root.state.query_limit}")
        results = [{
            "class": cls,
            "web_cid": web_cid,
            "thumbnail_cid": thumbnail_cid,
            "date": str(date.isoformat()),
            "description": description,
            "cid": cid,
            "filename": f"{filename_no_ext}.{mimetype.split('/')[-1]}",
            "mimetype": mimetype,
            "state": state,
            "tags": ([] if tags == [None] else tags)
        } for state, filename_no_ext, mimetype, cls, cid, web_cid, thumbnail_cid, date, description, tags in query(
            query_)]
        if self.root.state.random:
            shuffle(results)
        self.root.state.set_query_results(results)
        self.root.state.set_number_of_elements(len(results))