import logging
import sys
import sqlite3


class GuildDB:

    def __init__(self, dbfile):
        self._dbfile = dbfile
        self.logger = logging.getLogger('ManaChat.Guild')
        self.db, self.cur = self._open_sqlite_db(dbfile)

        # self.cur.execute('PRAGMA foreign_keys = ON')
        self.cur.execute('create table if not exists GUILDS(\
                              ID       integer primary key,\
                              NAME     text[25] not null unique,\
                              CREATED  datetime default current_timestamp,\
                              MOTD     text[100])')
        self.cur.execute('create table if not exists PLAYERS(\
                              ID       integer primary key,\
                              NAME     text[25] not null unique,\
                              LASTSEEN date,\
                              GUILD_ID integer,\
                              ACCESS   integer not null default -10,\
                              SHOWINFO boolean not null default 0,\
                              foreign key(GUILD_ID) references GUILDS(ID))')
        self.db.commit()

    def __del__(self):
        try:
            self.db.close()
        except Exception:
            pass

    def _open_sqlite_db(self, dbfile):
        """
        Open sqlite db, and return tuple (connection, cursor)
        """
        try:
            db = sqlite3.connect(dbfile)
            cur = db.cursor()
        except sqlite3.Error, e:
            self.logger.error("sqlite3 error: %s", e.message)
            sys.exit(1)
        return db, cur

    def guild_create(self, name):
        self.cur.execute('insert into GUILDS(NAME) values(?)', (name,))
        self.db.commit()
        if self.cur.rowcount:
            self.logger.info('Created guild "%s"', name)
            return True
        else:
            self.logger.info('Error creating guild "%s"', name)
            return False

    def guild_delete(self, name):
        self.cur.execute('select ID from GUILDS where name = ?', (name,))
        row = self.cur.fetchone()
        if row:
            guild_id = row[0]
            self.cur.execute('delete from GUILDS where name=?', (name,))
            self.cur.execute('update PLAYERS set GUILD_ID = NULL, \
                              ACCESS = -10, where GUILD_ID = ?', (guild_id,))
            self.db.commit()
            self.logger.info('Deleted guild "%s"', name)
            return True
        else:
            self.logger.error('Guild "%s" not found', name)
            return False

    def guild_set_motd(self, name, motd):
        self.cur.execute('update GUILD set MOTD = ? where NAME = ?',
                         (motd, name))
        self.db.commit()
        if self.cur.rowcount:
            self.logger.info('Guild "%s" MOTD: %s', name, motd)
            return True
        else:
            self.logger.error('Error setting MOTD for guild: %s', name)
            return False

    def player_info(self, name):
        query = '''select GUILDS.ID,GUILDS.NAME,ACCESS
                   from PLAYERS join GUILDS
                   on PLAYERS.GUILD_ID = GUILDS.ID
                   where PLAYERS.NAME = ?'''
        self.cur.execute(query, (name,))
        return self.cur.fetchone()

    def player_get_access(self, name, guild_name=''):
        query = 'select ACCESS from PLAYERS where NAME = ?'
        self.cur.execute(query, (name, guild_name))
        row = self.cur.fetchone()
        if row:
            return row[0]
        else:
            # self.logger.warning('player %s not found', name)
            return -10

    def player_set_access(self, name, access_level):
        query = '''update table PLAYERS set ACCESS = ?
                   where name = ?'''
        self.cur.execute(query, (name, access_level))
        self.db.commit()

    def player_join_guild(self, player, guild, access=0):
        self.cur.execute('select ID from GUILDS where NAME = ?', (guild,))
        guild_info = self.cur.fetchone()
        if guild_info:
            guild_id = guild_info[0]
        else:
            self.logger.error('Guild "%s" not found', guild)
            return False

        query = '''update or ignore PLAYERS
                   set GUILD_ID = ?, ACCESS = ?
                   where NAME = ?'''
        self.cur.execute(query, (guild_id, access))

        query = '''insert or ignore into
                   PLAYERS(NAME, GUILD_ID, ACCESS)
                   values(?, ?, ?)'''
        self.cur.execute(query, (player, guild_id, access))

        self.db.commit()

        self.logger.info('Added player "%s" to guild "%s"',
                         player, guild)
        return True

    def player_set_showinfo(self, player, si=True):
        query = '''update table PLAYERS set SHOWINFO = ?
                   where name = ?'''
        self.cur.execute(query, (player, si))
        self.db.commit()

    def guild_remove_player(self, player_name):
        query = '''update PLAYERS set GUILD_ID = NULL, ACCESS = -10
                   where NAME = ?'''
        self.cur.execute(query, (player_name,))
        self.db.commit()

    def all_players_same_guild(self, player_name):
        query = '''select NAME from PLAYERS
                   where GUILD_ID = (select GUILD_ID from PLAYERS
                                     where NAME = ?)'''
        return self.cur.fetchall(query, (player_name,))

    def all_players_any_guild(self):
        query = '''select NAME from PLAYERS
                   where ACCESS >= 0'''
        return self.cur.fetchall(query)