import configparser
import logging
import os
from datetime import datetime
from decimal import *
from logging.handlers import TimedRotatingFileHandler

import modules.currency

import MySQLdb

# Set logging info
logger = logging.getLogger("db_log")
logger.setLevel(logging.INFO)
handler = TimedRotatingFileHandler('{}/logs/{:%Y-%m-%d}-db.log'.format(os.getcwd(), datetime.now()),
                                   when="d",
                                   interval=1,
                                   backupCount=5)
logger.addHandler(handler)

# Read config and parse constants
config = configparser.ConfigParser()
config.read('{}/webhookconfig.ini'.format(os.getcwd()))

# Check the currency of the bot
CURRENCY = config.get('main', 'currency')

# DB connection settings
DB_HOST = config.get('main', 'host')
DB_USER = config.get('main', 'user')
DB_PW = config.get('main', 'password')
DB_SCHEMA = config.get(CURRENCY, 'schema')


def db_init():
    if not check_db_exist():
        logger.info("db didn't exist: {}".format(DB_SCHEMA))
        create_db()
    logger.info("db did exist: {}".format(DB_SCHEMA))
    create_tables()
    create_triggers()


def check_db_exist():
    db = MySQLdb.connect(host=DB_HOST, port=3306, user=DB_USER, passwd=DB_PW, use_unicode=True,
                         charset="utf8mb4")
    logger.info("Checking if schema exists: {}".format(DB_SCHEMA))
    sql = "SHOW DATABASES LIKE '{}'".format(DB_SCHEMA)
    db_cursor = db.cursor()
    exists = db_cursor.execute(sql)
    db_cursor.close()
    db.close()

    return exists == 1


def create_db():
    db = MySQLdb.connect(host=DB_HOST, port=3306, user=DB_USER, passwd=DB_PW, use_unicode=True,
                         charset="utf8mb4")
    db_cursor = db.cursor()
    sql = 'CREATE DATABASE IF NOT EXISTS {}'.format(DB_SCHEMA)
    db_cursor.execute(sql)
    db.commit()
    db_cursor.close()
    db.close()
    logger.info('Created database')


def check_table_exists(table_name):
    db = MySQLdb.connect(host=DB_HOST, port=3306, user=DB_USER, passwd=DB_PW, db=DB_SCHEMA, use_unicode=True,
                         charset="utf8mb4")
    db_cursor = db.cursor()
    sql = "SHOW TABLES LIKE '{}'".format(table_name)
    db_cursor.execute(sql)
    result = db_cursor.fetchall()
    return result


def create_triggers():
    db = MySQLdb.connect(host=DB_HOST, port=3306, user=DB_USER, passwd=DB_PW, db=DB_SCHEMA, use_unicode=True,
                         charset="utf8mb4")
    db_cursor = db.cursor()
    db_cursor.execute("DROP TRIGGER IF EXISTS users_AFTER_INSERT;")
    db_cursor.execute("DROP TRIGGER IF EXISTS tip_list_AFTER_INSERT;")
    db_cursor.execute("DROP TRIGGER IF EXISTS dm_list_AFTER_INSERT;")
    user_trigger = """
                   CREATE TRIGGER `users_AFTER_INSERT` AFTER INSERT ON `users` FOR EACH ROW 
                   BEGIN 
                       INSERT INTO `languages` (`user_id`, `system`) 
                       VALUES (NEW.`user_id`, NEW.`system`);
                       INSERT INTO `return_address` (`user_id`, `system`, `last_action`) 
                       VALUES (NEW.`user_id`, NEW.`system`, now());
                       INSERT INTO `donation_info` (`user_id`, `system`)
                       VALUES (NEW.`user_id`, NEW.`system`);
                   END
                   """
    tip_list_trigger = """
                       CREATE DEFINER = CURRENT_USER TRIGGER `tip_list_AFTER_INSERT` AFTER INSERT ON `tip_list` FOR EACH ROW 
                       BEGIN 
                           UPDATE `return_address` SET `last_action` = now() 
                           WHERE `user_id` = new.`sender_id` 
                           AND `system` = new.`system`;
                       END
                       """
    dm_list_trigger = """
                      CREATE TRIGGER `dm_list_AFTER_INSERT` AFTER INSERT ON `dm_list` FOR EACH ROW 
                      BEGIN 
                          UPDATE `return_address` SET `last_action` = now() 
                          WHERE `user_id` = new.`sender_id` 
                          AND `system` = new.`system`;
                      END;
                      """

    db_cursor.execute(user_trigger)
    db_cursor.execute(tip_list_trigger)
    db_cursor.execute(dm_list_trigger)
    logger.info("Triggers set.")


def create_tables():
    db = MySQLdb.connect(host=DB_HOST, port=3306, user=DB_USER, passwd=DB_PW, db=DB_SCHEMA, use_unicode=True,
                         charset="utf8mb4")
    db_cursor = db.cursor()
    try:
        check_exists = check_table_exists('users')
        if not check_exists:
            # create users table
            sql = """
            CREATE TABLE IF NOT EXISTS `users` (
              `user_id` bigint(255) NOT NULL,
              `system` varchar(45) DEFAULT NULL,
              `user_name` varchar(100) DEFAULT NULL,
              `account` varchar(100) NOT NULL,
              `register` tinyint(1) NOT NULL DEFAULT '0',
              `created_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
              `mute` tinyint(1) NOT NULL DEFAULT '0',
              PRIMARY KEY (`user_id`),
              UNIQUE KEY `user_id_UNIQUE` (`user_id`),
              UNIQUE KEY `account_UNIQUE` (`account`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            """

            db_cursor.execute(sql)
            logger.info("Checking if users table was created: {}".format(
                check_table_exists('users')))

        check_exists = check_table_exists('telegram_chat_members')
        if not check_exists:
            # create telegram_chat_members table
            sql = """
            CREATE TABLE IF NOT EXISTS `telegram_chat_members` (
              `chat_id` bigint(100) NOT NULL,
              `chat_name` varchar(100) CHARACTER SET utf8mb4 NOT NULL,
              `member_id` bigint(100) NOT NULL,
              `member_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
              PRIMARY KEY (`chat_id`,`member_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            """

            db_cursor.execute(sql)
            logger.info("Checking if telegram_chat_members table was created: {}".format(
                check_table_exists('telegram_chat_members')))

        check_exists = check_table_exists('tip_list')
        if not check_exists:
            # create tip_list table
            sql = """
            CREATE TABLE IF NOT EXISTS `tip_list` (
              `dm_id` bigint(255) NOT NULL,
              `tx_id` varchar(255) DEFAULT NULL,
              `processed` tinyint(1) DEFAULT NULL,
              `sender_id` bigint(255) NOT NULL,
              `receiver_id` bigint(255) NOT NULL,
              `system` varchar(45) DEFAULT NULL,
              `dm_text` text DEFAULT NULL,
              `amount` decimal(10,5) DEFAULT NULL,
              `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
              PRIMARY KEY (`dm_id`,`sender_id`,`receiver_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            """

            db_cursor.execute(sql)
            logger.info("Checking if tip_list table was created: {}".format(
                check_table_exists('tip_list')))

        check_exists = check_table_exists('dm_list')
        if not check_exists:
            # create dm_list table
            sql = """
            CREATE TABLE IF NOT EXISTS `dm_list` (
             `dm_id` bigint(255) NOT NULL,
             `tx_id` varchar(100) GENERATED ALWAYS AS (concat('tip-',`dm_id`)) STORED,
             `processed` tinyint(1) NOT NULL,
             `sender_id` bigint(255) NOT NULL,
             `receiver_id` bigint(255) DEFAULT NULL,
             `dm_text` text DEFAULT NULL,
             `amount` decimal(10,5) DEFAULT NULL,
             `dm_response` text DEFAULT NULL,
             `first_attempt` tinyint(1) DEFAULT '0',
             `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
             PRIMARY KEY (`dm_id`),
             UNIQUE KEY `tx_id_UNIQUE` (`tx_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            """
            db_cursor.execute(sql)
            logger.info("Checking if tip_list table was created: {}".format(
                check_table_exists('tip_list')))

        check_exists = check_table_exists('languages')
        if not check_exists:
            # create languages table
            sql = """
                   CREATE TABLE IF NOT EXISTS `languages` (
                     `user_id` bigint(255) NOT NULL,
                     `language_code` varchar(2) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'en',
                     `system` varchar(45) CHARACTER SET utf8mb4 NOT NULL,
                     PRIMARY KEY (`user_id`),
                     UNIQUE KEY `user_id_UNIQUE` (`user_id`),
                     CONSTRAINT `user_key` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
                   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
                   """
            db_cursor.execute(sql)
            logger.info("Checking if languages table was created: {}".format(
                check_table_exists('languages')))

        check_exists = check_table_exists('return_address')
        if not check_exists:
            # create return_address table
            sql = """
                  CREATE TABLE IF NOT EXISTS `return_address` (
                    `user_id` bigint(255) NOT NULL,
                    `system` varchar(45) NOT NULL,
                    `account` varchar(100) DEFAULT NULL,
                    `last_action` datetime DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`user_id`,`system`)
                  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
                   """
            db_cursor.execute(sql)
            logger.info("Checking if return_address table was created: {}".format(
                check_table_exists('return_address')))

        check_exists = check_table_exists('spare_accounts')
        if not check_exists:
            # create spare_accounts table
            sql= """
            CREATE TABLE IF NOT EXISTS `spare_accounts` (
             `account` varchar(100) NOT NULL,
             PRIMARY KEY (`account`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            """
            db_cursor.execute(sql)
            logger.info("Checking if spare_accounts table was created: {}".format(
                check_table_exists('spare_accounts')))

        db.commit()
        db_cursor.close()
        db.close()
    except Exception as e:
        logger.info("Error creating tables for DB: {}".format(e))


def get_db_data(db_call):
    """
    Retrieve data from DB
    """
    logger.info("db host: {}".format(DB_HOST))
    logger.info("DB Schema: {}".format(DB_SCHEMA))
    db = MySQLdb.connect(host=DB_HOST, port=3306, user=DB_USER, passwd=DB_PW, db=DB_SCHEMA, use_unicode=True,
                         charset="utf8mb4")
    db_cursor = db.cursor()
    db_cursor.execute(db_call)
    db_data = db_cursor.fetchall()
    db_cursor.close()
    db.close()
    return db_data


def get_db_data_new(db_call, values):
    """
    Retrieve data from DB
    """
    db = MySQLdb.connect(host=DB_HOST, port=3306, user=DB_USER, passwd=DB_PW, db=DB_SCHEMA, use_unicode=True,
                         charset="utf8mb4")
    db_cursor = db.cursor()
    db_cursor.execute(db_call, values)
    db_data = db_cursor.fetchall()
    db_cursor.close()
    db.close()
    return db_data


def set_db_data(db_call, values):
    """
    Enter data into DB
    """
    db = MySQLdb.connect(host=DB_HOST, port=3306, user=DB_USER, passwd=DB_PW, db=DB_SCHEMA, use_unicode=True,
                         charset="utf8mb4")
    try:
        db_cursor = db.cursor()
        db_cursor.execute(db_call, values)
        db.commit()
        db_cursor.close()
        db.close()
        logger.info("{}: record inserted into DB".format(datetime.now()))
        return None
    except MySQLdb.ProgrammingError as e:
        logger.info("{}: Exception entering data into database".format(datetime.now()))
        logger.info("{}: {}".format(datetime.now(), e))
        return e


def set_db_data_tip(message, users_to_tip, t_index):
    """
    Special case to update DB information to include tip data
    """
    logger.info("{}: inserting tip into DB.".format(datetime.now()))
    db = MySQLdb.connect(host=DB_HOST, port=3306, user=DB_USER, passwd=DB_PW, db=DB_SCHEMA, use_unicode=True,
                         charset="utf8mb4")
    try:
        db_cursor = db.cursor()
        db_cursor.execute(
            "INSERT INTO tip_list (dm_id, tx_id, processed, sender_id, receiver_id, system, dm_text, amount)"
            " VALUES (%s, %s, 2, %s, %s, %s, %s, %s)",
            (message['id'], message['tip_id'], message['sender_id'],
             users_to_tip[t_index]['receiver_id'], message['system'], message['text'],
             Decimal(message['tip_amount'])))
        db.commit()
        db_cursor.close()
        db.close()
    except Exception as e:
        logger.info("{}: Exception in set_db_data_tip".format(datetime.now()))
        logger.info("{}: {}".format(datetime.now(), e))
        raise e


def set_spare_accounts(accounts):
    """
    Set DB with spare accounts.
    """
    insert_accounts_call = "INSERT INTO {}.spare_accounts (account) VALUES ".format(DB_SCHEMA)

    try:
        for index, account in enumerate(accounts['accounts']):
            if index == 0:
                insert_accounts_call += "(%s)"
            else:
                insert_accounts_call += ", (%s)"
        insert_accounts_call += ';'
        logger.info("insert accounts call: {}".format(insert_accounts_call))
        set_db_data(insert_accounts_call, accounts['accounts'])

    except Exception as e:
        logger.info("Error inserting spare accounts: {}".format(e))

    logger.info("New accounts set in DB.")


def get_spare_account():
    """
    Retrieve an account from the database.
    """
    check_accounts_call = "SELECT count(account) FROM {}.spare_accounts;".format(DB_SCHEMA)
    check_accounts_return = get_db_data(check_accounts_call)
    logger.info("check accounts return: {}".format(check_accounts_return[0][0]))
    if int(check_accounts_return[0][0]) <= 5:
        logger.info("Too few accounts, generating new ones: {}".format(check_accounts_return[0][0]))
        accounts = modules.currency.generate_accounts()
        logger.info("generated new accounts: {}".format(accounts))
        set_spare_accounts(accounts)

    get_account_call = "SELECT account FROM {}.spare_accounts LIMIT 1;".format(DB_SCHEMA)
    spare_account_return = get_db_data(get_account_call)

    remove_account_call = "DELETE FROM {}.spare_accounts WHERE account = %s".format(DB_SCHEMA)
    set_db_data(remove_account_call, spare_account_return[0])

    return spare_account_return[0][0]
