#!/usr/bin/env python

import MySQLdb
import _mysql_exceptions
import logging
import sys

class FilesMonitoring:
    LETTERS = '0123456789abcdef'
    SQL_CREATE_TABLE_TEMPLATE_FILENAME = 'sql/CreateTableTemplate.sql'

    def __init__(self, hostname, logger = logging.getLogger()):
        self.logger = logger
        if hostname != 'neverland':
            self.old_db = MySQLdb.connect('ippc11', 'ippuser', 'ipp', 'FilesMonitoring')
            self.new_db = MySQLdb.connect('ippc11', 'ippuser', 'ipp', 'DisksMonitoring')
        else:
            self.old_db = MySQLdb.connect('localhost', 'root', 'root', 'FilesMonitoring')
            self.new_db = MySQLdb.connect('localhost', 'root', 'root', 'DisksMonitoring')
        self.create_table_template = ''
        for line in open(FilesMonitoring.SQL_CREATE_TABLE_TEMPLATE_FILENAME):
            self.create_table_template += line

    def close(self):
        self.old_db.close()
        self.new_db.close()

    def convert_node(self, node):
        self.logger.info('Converting data for storage node [%s]' % node)
        for letter1 in FilesMonitoring.LETTERS:
            for letter2 in FilesMonitoring.LETTERS:
                oldtable = 'Files_%s_%s%s' % (node, letter1, letter2)
                newtable = '%s_%s%s' % (node, letter1, letter2)
                old_count = self.convert_table(oldtable, newtable)
                cursor = self.new_db.cursor()
                cursor.execute('SELECT COUNT(*) FROM %s' % newtable)
                new_count = cursor.fetchone()[0]
                if old_count != new_count:
                    self.logger.error('Problem for tables [%s/%s]' % (oldtable, newtable))
                    self.logger.info(' Old/New count: %d' % (old_count, new_count))
                else:
                    self.logger.info('Conversion from [%s] to [%s] complete' % (oldtable, newtable))

    def convert_table(self, oldtable, newtable):
        query_select = """SELECT 
filename, neb_ins_id, subdirname, nebkey, size, md5sum, role, stage, lifetime, creation_date, last_check 
FROM %s""" % oldtable
        old_cursor = self.old_db.cursor()
        old_cursor.execute(query_select)
        rows = old_cursor.fetchall()
        self.logger.info('Table: [%s] (%d elements))' % (oldtable, len(rows)))
        query_insert_template_defined = """INSERT INTO %s
(filename, neb_ins_id, subdirname, nebkey, size, md5sum, 
 role, stage, lifetime, creation_date, last_check, status)
VALUES 
('%s', %d, '%s', '%s', %d, '%s', 
 '%s', '%s', '%s', '%s', '%s', '%s')
"""
        query_insert_template_undefined = """INSERT INTO %s
(filename, neb_ins_id, subdirname, nebkey, size, md5sum, 
 role, stage, lifetime, creation_date, last_check, status)
VALUES 
('%s', %d, '%s', '%s', 0, NULL, 
 'undefined', 'undefined', 'undefined', '%s', '1010-01-01', 'undefined')
"""
        new_cursor = self.new_db.cursor()
        count = 1
        for (filename, neb_ins_id, subdirname, nebkey, size, md5sum, role, stage, lifetime, 
             creation_date, last_check) in rows:
            if count % 10000 == 0:
                logger.info('%d / %d' % (count, len(rows)))
                new_cursor.close()
                self.new_db.commit()
                new_cursor = self.new_db.cursor()
            if role == 'undefined' or md5sum is None or md5sum == 'None':
                logger.debug('Executing for undefined')
                logger.debug(query_insert_template_undefined % (newtable,
                                                                filename,
                                                                neb_ins_id, 
                                                                subdirname, 
                                                                nebkey,
                                                                creation_date))
                new_cursor.execute(query_insert_template_undefined % (newtable,
                                                                      filename,
                                                                      neb_ins_id, 
                                                                      subdirname, 
                                                                      nebkey,
                                                                      creation_date))
            elif role == 'deleted':
                logger.debug('Executing for deleted')
                logger.debug(query_insert_template_defined % (newtable,
                                                              filename, 
                                                              neb_ins_id, 
                                                              subdirname, 
                                                              nebkey, 
                                                              size, 
                                                              md5sum, 
                                                              'undefined', 
                                                              stage, 
                                                              lifetime, 
                                                              creation_date, 
                                                              last_check,
                                                              'deleted'))
                new_cursor.execute(query_insert_template_defined % (newtable,
                                                                    filename, 
                                                                    neb_ins_id, 
                                                                    subdirname, 
                                                                    nebkey, 
                                                                    size, 
                                                                    md5sum, 
                                                                    'undefined', 
                                                                    stage, 
                                                                    lifetime, 
                                                                    creation_date, 
                                                                    last_check,
                                                                    'deleted'))
            else:
                logger.debug('Executing for others')
                logger.debug(query_insert_template_defined % (newtable,
                                                              filename, 
                                                              neb_ins_id, 
                                                              subdirname, 
                                                              nebkey, 
                                                              size, 
                                                              md5sum, 
                                                              role, 
                                                              stage, 
                                                              lifetime, 
                                                              creation_date, 
                                                              last_check,
                                                              'ok'))
                new_cursor.execute(query_insert_template_defined % (newtable,
                                                                    filename, 
                                                                    neb_ins_id, 
                                                                    subdirname, 
                                                                    nebkey, 
                                                                    size, 
                                                                    md5sum, 
                                                                    role, 
                                                                    stage, 
                                                                    lifetime, 
                                                                    creation_date, 
                                                                    last_check,
                                                                    'ok'))
            count += 1
        new_cursor.close()
        self.new_db.commit()
        old_cursor.close()
        return len(rows)

if __name__ == '__main__':
    logger = logging.getLogger()
    formatter = logging.Formatter('%(asctime)s | %(levelname)7s | %(message)s', '%Y-%m-%d %H:%M:%S')
    logger.setLevel(logging.INFO)
    stderr = logging.StreamHandler(sys.__stdout__)
    stderr.setFormatter(formatter)
    logger.addHandler(stderr)
    logger.info('Logging to stderr')

    import socket
    hostname = socket.gethostbyaddr(socket.gethostname())[0].split('.')[0]
    fm = FilesMonitoring(hostname)
    for node in sys.argv[1:]:
        fm.convert_node(node)
    fm.close()
