import sqlite3

# Database: chatDB.sqlite
# Table: house_group_chatsimport requests
import telegramimport requests
import telegram
from telegram.ext import (Updater, MessageHandler, Filters, ConversationHandler, CommandHandler, RegexHandler)

from settings import token
from commands import *
from stationInfo import all_password_regex


# Initialisation
bot = telegram.Bot(token)
updater = Updater(token)
dispatcher = updater.dispatcher

# Register commands
registerCommand(dispatcher, "my_house_letters", my_house_letters)
registerCommand(dispatcher, "all_house_letters", all_house_letters)
registerCommand(dispatcher, "station_overview", station_overview)
registerCommand(dispatcher, "help", help)
registerCommand(dispatcher, "password", password)

# 'Start' conversation handler -- register house with bot
start_conv_handler = ConversationHandler(
        entry_points=[CommandHandler('start', start)],
        states={
            CONFIRMATION_REQUEST: [RegexHandler('^(Ilykoie|Neo|Laventus|Oneiroi|Alerion|Rabanna)$', registerHouse)],
            },
        fallbacks=[CommandHandler('remove', remove), CommandHandler('cancel', cancel)]
        )
dispatcher.add_handler(start_conv_handler)


registerCommand(dispatcher, "play", play)
play_handler = RegexHandler('^(1|2|3|4|5|6|7|8|9|10|11)$', get_instructions)
dispatcher.add_handler(play_handler)


# Message handler for station completion
completion_handler = RegexHandler(all_password_regex, station_complete)
dispatcher.add_handler(completion_handler)


# Polling for updates
updater.start_polling()

updater.idle()

from telegram.ext import (Updater, MessageHandler, Filters, ConversationHandler, CommandHandler, RegexHandler)

from settings import token
from commands import *
from stationInfo import all_password_regex


# Initialisation
bot = telegram.Bot(token)
updater = Updater(token)
dispatcher = updater.dispatcher

# Register commands
registerCommand(dispatcher, "my_house_letters", my_house_letters)
registerCommand(dispatcher, "all_house_letters", all_house_letters)
registerCommand(dispatcher, "station_overview", station_overview)
registerCommand(dispatcher, "help", help)
registerCommand(dispatcher, "password", password)

# 'Start' conversation handler -- register house with bot
start_conv_handler = ConversationHandler(
        entry_points=[CommandHandler('start', start)],
        states={
            CONFIRMATION_REQUEST: [RegexHandler('^(Ilykoie|Neo|Laventus|Oneiroi|Alerion|Rabanna)$', registerHouse)],
            },
        fallbacks=[CommandHandler('remove', remove), CommandHandler('cancel', cancel)]
        )
dispatcher.add_handler(start_conv_handler)


registerCommand(dispatcher, "play", play)
play_handler = RegexHandler('^(1|2|3|4|5|6|7|8|9|10|11)$', get_instructions)
dispatcher.add_handler(play_handler)


# Message handler for station completion
completion_handler = RegexHandler(all_password_regex, station_complete)
dispatcher.add_handler(completion_handler)


# Polling for updates
updater.start_polling()

updater.idle()

# Columns: chat_id, house

class ChatDBHelper:
    def __init__(self, dbname="chatDB.sqlite"):
        self.dbname = dbname
        self.conn = sqlite3.connect(dbname, check_same_thread = False)
        self.c = self.conn.cursor()

    def setup(self):
        self.conn.execute("DROP TABLE IF EXISTS house_group_chats ") #(for testing purposes)
        print("creating chat_id/house table (if it does not exist)")
        houses_table = "CREATE TABLE IF NOT EXISTS house_group_chats (chat_id integer, house text)"
        self.c.execute(houses_table)
        self.conn.commit()

    def is_house_registered(self, house_name):
        stmt = "SELECT house from house_group_chats"
        registered = [x[0] for x in self.conn.execute(stmt)]
        if house_name in registered:
            return True
        else:
            return False

    def is_chat_registered(self, chat):
        stmt = "SELECT chat_id from house_group_chats"
        registered = [x[0] for x in self.conn.execute(stmt)]
        for h in registered:
            if chat == h:
                return True
        return False

    # === Setter ===
    def add_house(self, chat, house_name):
        stmt = "INSERT INTO house_group_chats (chat_id, house) VALUES (?, ?)"
        args = (chat, house_name)
        self.c.execute(stmt, args)
        self.conn.commit()

    def remove_chat(self, chat):
        stmt = "DELETE FROM house_group_chats WHERE chat_id = (?)"
        args = (chat, )
        self.c.execute(stmt, args)
        self.conn.commit()

    # === Getters ===
    def get_house(self, chat):
        stmt = "SELECT house FROM house_group_chats WHERE chat_id = (?)"
        args = (chat,)
        # return [x for x in self.conn.execute(stmt, args)]
        row = self.c.execute(stmt, args).fetchone()
        return row[0]


    def print_table(self):
        # stmt = "SELECT * FROM house_group_chats"
        # table = self.c.execute(stmt)
        # print(table)
        # self.c.execute("SELECT * FROM house_group_chats")
        rows = self.c.fetchall()
        for row in rows:
            print(row)