import json
import pam.sql
import pam.modules


class Database:
    def __init__(self, dbiface):
        self._interface = None
        self.interface = dbiface

    @property
    def interface(self):
        return self._interface

    @interface.setter
    def interface(self, value):
        self._interface = value

    async def get_guild_settings(self, guild_id):
        iface = self.interface
        results = await iface.execute_query("SELECT setting_name, setting_value FROM pam_guild_settings WHERE guild_id=$1;", int(guild_id))
        resultr = []
        for result in results:
            resultr.append(pam.GuildSetting(guild_id, result[0], result[1]))
        return resultr

    async def set_guild_setting(self, guild_id, setting):
        return await self.set_guild_settings(guild_id, setting)

    async def set_guild_settings(self, guild_id, *settings):
        iface = self.interface
        dat = []
        for setting in settings:
            dat.append((int(guild_id), setting.setting_name, setting.setting_value))
        return await iface.execute_query_transaction("INSERT INTO pam_guild_settings(guild_id, setting_name, setting_value) VALUES($1, $2, $3) ON CONFLICT(guild_id, setting_name) DO UPDATE SET"
                                                     " setting_value=EXCLUDED.setting_value;", *dat)

    async def get_moderator_action(self, guild_id, action_id):
        iface = self.interface
        results = await iface.execute_query("SELECT target_user, action_type, moderator, reason, action_timestamp, until, attached_message_id FROM pam_moderator_actions WHERE guild_id=$1 AND id=$2",
                                            int(guild_id), action_id)
        return pam.ModeratorAction(guild_id, results[0][0], results[0][1], results[0][2], results[0][3], results[0][4], results[0][5], results[0][6]) if len(results) > 0 else None

    async def get_moderator_actions(self, guild_id):
        iface = self.interface
        results = await iface.execute_query("SELECT target_user, action_type, moderator, reason, action_timestamp, until, attached_message_id FROM pam_moderator_actions WHERE guild_id=$1",
                                            int(guild_id))
        resultr = []
        for result in results:
            resultr.append(pam.ModeratorAction(guild_id, result[0], result[1], result[2], result[3], result[4], result[5], result[6]))
        return resultr

    async def get_bans_for(self, guild_id, target_user):
        iface = self.interface
        results = await iface.execute_query("SELECT target_user, action_type, moderator, reason, action_timestamp, until, attached_message_id FROM pam_moderator_actions WHERE guild_id=$1 AND"
                                            " target_user=$2 AND (action_type=3 OR action_type=4 OR action_type=5) ORDER BY action_timestamp DESC LIMIT 2;", int(guild_id), int(target_user))
        resultr = []
        for result in results:
            resultr.append(pam.ModeratorAction(guild_id, result[0], result[1], result[2], result[3], result[4], result[5], result[6]))
        return resultr

    async def get_timed_actions(self, guild_id):
        iface = self.interface
        results = await iface.execute_query("SELECT DISTINCT ON(target_user, action_type) target_user, action_type, moderator, reason, action_timestamp, until, attached_message_id FROM"
                                            " pam_moderator_actions WHERE guild_id=$1 AND (action_type=0 OR action_type=1 OR action_type=4 OR action_type=5) ORDER BY target_user DESC, action_type"
                                            " DESC, action_timestamp DESC;", int(guild_id))
        resultr = []
        for result in results:
            resultr.append(pam.ModeratorAction(guild_id, result[0], result[1], result[2], result[3], result[4], result[5], result[6]))
        return resultr

    async def set_moderator_action(self, guild_id, action):
        return await self.set_moderator_actions(guild_id, action)

    async def set_moderator_actions(self, guild_id, *actions):
        iface = self.interface
        dat = []
        for action in actions:
            dat.append((int(guild_id), int(action.target_user), action.action_type, int(action.moderator) if action.moderator is not None else None, action.reason, action.action_timestamp,
                        action.until, int(action.message) if action.message is not None else None))
        await iface.execute_query_transaction("INSERT INTO pam_moderator_actions(guild_id, target_user, action_type, moderator, reason, action_timestamp, until, attached_message_id) VALUES($1, $2,"
                                              " $3, $4, $5, $6, $7, $8) ON CONFLICT(guild_id, target_user, action_type, action_timestamp) DO UPDATE SET action_type=EXCLUDED.action_type,"
                                              " moderator=EXCLUDED.moderator, reason=EXCLUDED.reason;", *dat)

        tup = dat[len(dat) - 1]
        return await iface.execute_query("SELECT id FROM pam_moderator_actions WHERE guild_id=$1 AND target_user=$2 AND action_type=$3 AND (moderator=$4 OR moderator IS NULL) AND (reason=$5 OR reason"
                                         " IS NULL) AND action_timestamp=$6 AND (until=$7 OR until IS NULL) AND attached_message_id=$8;", *tup)

    async def get_tag(self, name, channel, guild):
        iface = self.interface
        results = await iface.execute_query("SELECT tag_name, tag_contents, channel_id, guild_id, owner_id, created FROM pam_tags WHERE tag_name=$1 AND (channel_id=$2 OR channel_id IS NULL) AND"
                                            " (guild_id=$3 OR guild_id IS NULL);", name, int(channel), int(guild))
        if not len(results):
            return None
        resultr = []
        for result in results:
            resultr.append(pam.modules.Tag(result[0], result[1], result[4], result[5], result[2], result[3]))
        best_match = resultr[0]
        for result in resultr:
            if result.guild is not None and best_match.guild is None:
                best_match = result
            if result.channel is not None and best_match.channel is None:
                best_match = result
        return best_match

    async def get_tag_guild(self, name, guild):
        iface = self.interface
        results = await iface.execute_query("SELECT tag_name, tag_contents, channel_id, guild_id, owner_id, created FROM pam_tags WHERE tag_name=$1 AND channel_id IS NULL AND (guild_id=$2 OR guild_id"
                                            " IS NULL);", name, int(guild))
        if not len(results):
            return None
        resultr = []
        for result in results:
            resultr.append(pam.modules.Tag(result[0], result[1], result[4], result[5], result[2], result[3]))
        best_match = resultr[0]
        for result in resultr:
            if result.guild is not None and best_match.guild is None:
                best_match = result
        return best_match

    async def get_tag_global(self, name):
        iface = self.interface
        results = await iface.execute_query("SELECT tag_name, tag_contents, channel_id, guild_id, owner_id, created FROM pam_tags WHERE tag_name=$1 AND channel_id IS NULL AND guild_id IS NULL;", name)
        if not len(results):
            return None
        resultr = []
        for result in results:
            resultr.append(pam.modules.Tag(result[0], result[1], result[4], result[5], result[2], result[3]))
        best_match = resultr[0]
        return best_match

    async def get_tags(self, channel, guild):
        iface = self.interface
        results = await iface.execute_query("SELECT tag_name, tag_contents, channel_id, guild_id, owner_id, created FROM pam_tags WHERE (channel_id=$1 OR channel_id IS NULL) AND (guild_id=$2 OR"
                                            " guild_id IS NULL);", int(channel), int(guild))
        if not len(results):
            return None
        resultr = []
        for result in results:
            resultr.append(pam.modules.Tag(result[0], result[1], result[4], result[5], result[2], result[3]))
        best_matches = {}
        for result in resultr:
            if result.name not in best_matches:
                best_matches[result.name] = result
            if result.guild is not None and best_matches[result.name].guild is None:
                best_matches[result.name] = result
            if result.channel is not None and best_matches[result.name].channel is None:
                best_matches[result.name] = result
        return best_matches

    async def get_tags_member(self, channel, guild, user):
        iface = self.interface
        results = await iface.execute_query("SELECT tag_name, tag_contents, channel_id, guild_id, owner_id, created FROM pam_tags WHERE owner_id=$1 AND (channel_id=$2 OR channel_id IS NULL) AND"
                                            " (guild_id=$3 OR guild_id IS NULL);", int(user), int(channel), int(guild))
        if not len(results):
            return None
        resultr = []
        for result in results:
            resultr.append(pam.modules.Tag(result[0], result[1], result[4], result[5], result[2], result[3]))
        best_matches = {}
        for result in resultr:
            if result.name not in best_matches:
                best_matches[result.name] = result
            if result.guild is not None and best_matches[result.name].guild is None:
                best_matches[result.name] = result
            if result.channel is not None and best_matches[result.name].channel is None:
                best_matches[result.name] = result
        return best_matches

    async def delete_tag(self, tag):
        iface = self.interface
        if tag.channel is not None and tag.guild is not None:
            dat = [(tag.name, int(tag.owner), int(tag.guild), int(tag.channel))]
            return await iface.execute_query_transaction("DELETE FROM pam_tags WHERE tag_name=$1 AND owner_id=$2 AND guild_id=$3 AND channel_id=$4;", *dat)
        elif tag.channel is None and tag.guild is not None:
            dat = [(tag.name, tag.owner, tag.guild)]
            return await iface.execute_query_transaction("DELETE FROM pam_tags WHERE tag_name=$1 AND owner_id=$2 AND guild_id=$3 AND channel_id IS NULL;", *dat)
        elif tag.channel is None and tag.guild is None:
            dat = [(tag.name, tag.owner)]
            return await iface.execute_query_transaction("DELETE FROM pam_tags WHERE tag_name=$1 AND owner_id=$2 AND guild_id IS NULL AND channel_id IS NULL;", *dat)

    async def set_tag(self, tag):
        iface = self.interface
        dat = [(tag.name, tag.contents, int(tag.channel) if tag.channel is not None else None, int(tag.guild) if tag.guild is not None else None, int(tag.owner), tag.timestamp)]
        return await iface.execute_query_transaction("INSERT INTO pam_tags(tag_name, tag_contents, channel_id, guild_id, owner_id, created) VALUES($1, $2, $3, $4, $5, $6);", *dat)

    async def update_tag(self, tag):
        iface = self.interface
        if tag.channel is not None and tag.guild is not None:
            dat = [(tag.contents, tag.name, int(tag.guild), int(tag.channel))]
            return await iface.execute_query_transaction("UPDATE pam_tags SET tag_contents=$1 WHERE tag_name=$2 AND guild_id=$3 AND channel_id=$4;", *dat)
        elif tag.channel is None and tag.guild is not None:
            dat = [(tag.contents, tag.name, tag.guild)]
            return await iface.execute_query_transaction("UPDATE pam_tags SET tag_contents=$1 WHERE tag_name=$2 AND guild_id=$3 AND channel_id IS NULL;", *dat)
        elif tag.channel is None and tag.guild is None:
            dat = [(tag.contents, tag.name)]
            return await iface.execute_query_transaction("UPDATE pam_tags SET tag_contents=$1 WHERE tag_name=%$2 AND guild_id IS NULL AND channel_id IS NULL;", *dat)

    async def get_feed_count(self, like):
        iface = self.interface
        results = []
        if like.tag is not None:
            results = await iface.execute_query("SELECT COUNT(id) FROM pam_rss_feeds WHERE guild_id=$1 AND channel_id=$2 AND feed_url=$3 AND item_tag=$4;", int(like.guild), int(like.channel),
                                                like.url, like.tag)
        else:
            results = await iface.execute_query("SELECT COUNT(id) FROM pam_rss_feeds WHERE guild_id=$1 AND channel_id=$2 AND feed_url=$3 AND item_tag IS NULL;", int(like.guild), int(like.channel),
                                                like.url)
        result = results[0][0]
        return result

    async def create_feed(self, feed):
        iface = self.interface
        return await iface.execute_query("INSERT INTO pam_rss_feeds(guild_id, channel_id, feed_url, item_tag, initialized, last_items) VALUES($1, $2, $3, $4, $5, $6);", int(feed.guild),
                                         int(feed.channel), feed.url, feed.tag, feed.initialized, json.dumps(feed.items))

    async def remove_feed(self, feed):
        iface = self.interface
        if feed.tag is not None and len(feed.tag) > 0:
            return await iface.execute_query("DELETE FROM pam_rss_feeds WHERE guild_id=$1 AND channel_id=$2 AND feed_url=$3 AND item_tag=$4;", int(feed.guild), int(feed.channel), feed.url, feed.tag)
        elif feed.tag is None or len(feed.tag) == 0:
            return await iface.execute_query("DELETE FROM pam_rss_feeds WHERE guild_id=$1 AND channel_id=$2 AND feed_url=$3 AND item_tag IS NULL;", int(feed.guild), int(feed.channel), feed.url)

    async def update_feed(self, feed):
        iface = self.interface
        if feed.tag is not None and len(feed.tag) > 0:
            return await iface.execute_query("UPDATE pam_rss_feeds SET initialized=$1, last_items=$2 WHERE guild_id=$3 AND channel_id=$4 AND feed_url=$5 AND item_tag=$6;", feed.initialized,
                                             json.dumps(feed.items), int(feed.guild), int(feed.channel), feed.url, feed.tag)
        elif feed.tag is None or len(feed.tag) == 0:
            return await iface.execute_query("UPDATE pam_rss_feeds SET initialized=$1, last_items=$2 WHERE guild_id=$3 AND channel_id=$4 AND feed_url=$5 AND item_tag IS NULL;", feed.initialized,
                                             json.dumps(feed.items), int(feed.guild), int(feed.channel), feed.url)

    async def get_feeds(self, guild):
        iface = self.interface
        results = await iface.execute_query("SELECT guild_id, channel_id, feed_url, item_tag, initialized, last_items FROM pam_rss_feeds WHERE guild_id=$1;", int(guild))
        resultr = []
        for result in results:
            feed = pam.modules.RssFeed(result[0], result[2], result[1], result[3])
            feed.initialized = result[4]
            feed.items = json.loads(result[5])
            resultr.append(feed)
        return resultr

    async def get_all_feeds(self):
        iface = self.interface
        results = await iface.execute_query("SELECT guild_id, channel_id, feed_url, item_tag, initialized, last_items FROM pam_rss_feeds;")
        resultr = []
        for result in results:
            feed = pam.modules.RssFeed(result[0], result[2], result[1], result[3])
            feed.initialized = result[4]
            feed.items = json.loads(result[5])
            resultr.append(feed)
        return resultr

    async def set_rolestate(self, guild, member, *roles):
        iface = self.interface
        return await iface.execute_query("INSERT INTO pam_rolestate(guild_id, member_id, role_ids) VALUES($1, $2, $3) ON CONFLICT(guild_id, member_id) DO UPDATE SET role_ids=EXCLUDED.role_ids;",
                                         int(guild), int(member), [int(x) for x in roles])

    async def get_rolestate(self, guild, member):
        iface = self.interface
        results = await iface.execute_query("SELECT role_ids FROM pam_rolestate WHERE guild_id=$1 AND member_id=$2 LIMIT 1;", int(guild), int(member))
        if not results:
            return []

        results = results[0][0]
        return [int(x) for x in results]
