#!/usr/bin/env python
# -*- coding:utf-8 -*-

"""
这个脚本的作用在于：
    1. 重新获取数据库中的数据
    2. 并重新生成索引文件
注意：
    1. 当迁移服务的时候需要重新执行此文件， 或者迁移索引文件
    2. 此脚本的索引文件路径需要与配置文件的路径一致
    3. 此脚本的mysql用户名和密码需要与配置文件一致

"""

import pymysql
import os
from whoosh.index import create_in
from jieba.analyse import ChineseAnalyzer
from decimal import Decimal
from whoosh.fields import Schema, TEXT, ID
import configparser

analyzer = ChineseAnalyzer()


class Config(object):
    """
    # Config().get_content("user_information")
    """

    def __init__(self, config_filename="report.cnf"):
        file_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), config_filename)
        self.cf = configparser.ConfigParser()
        self.cf.read(file_path)

    def get_sections(self):
        return self.cf.sections()

    def get_options(self, section):
        return self.cf.options(section)

    def get_content(self, section):
        result = {}
        for option in self.get_options(section):
            value = self.cf.get(section, option)
            result[option] = int(value) if value.isdigit() else value
        return result


class base_pymysql(object):
    def __init__(self, host, port, user, password, db_name):
        self.db_host = host
        self.db_port = int(port)
        self.user = user
        self.password = str(password)
        self.db = db_name
        self.conn = None
        self.cursor = None

    def connect(self):
        self.conn = pymysql.connect(host=self.db_host, port=self.db_port, user=self.user,
                                    passwd=self.password, db=self.db, charset="utf8")
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)


class MyPymysql(base_pymysql):
    """
    Basic Usage:
        ret = My_Pymysql('test1')
        res = ret.selectone_sql("select * from aaa")
        print(res)
        ret.close()
    Precautions:
        Config.__init__(self, config_filename="report.cnf")
    """

    def __init__(self, conf_name):
        # self.conf = Config().get_content(conf_name)
        self.conf = {"host": "47.92.6.187", "port": 3306, "db_name": "db_metadata", "user": "root",
                     "password": "python123"}
        super(MyPymysql, self).__init__(**self.conf)
        self.connect()

    def idu_sql(self, sql):
        # adu: insert, delete, update的简写
        # 考虑到多语句循环, try就不写在这里了
        self.cursor.execute(sql)
        self.conn.commit()

    def insert_sql(self, sql, value=None):
        # adu: insert, delete, update的简写
        self.cursor.execute(sql, value)
        self.conn.commit()

    def selectone_sql(self, sql):
        self.cursor.execute(sql)
        self.conn.commit()
        return self.cursor.fetchone()

    def selectall_sql(self, sql):
        self.cursor.execute(sql)
        self.conn.commit()
        return self.cursor.fetchall()

    def close(self):
        self.conn.close()
        self.conn = None
        self.cursor = None


def parse_db(tables, conn, indexname, schema, indexdir, keys):
    table = tables
    sql = "select * from %s;" % table
    rows = conn.selectall_sql(sql)
    conn.close()
    # keys = rows[0].keys()
    s = "Schema("
    for key in range(len(keys)):
        if key == 0:
            if keys[0] == "ProjectID":
                s += keys[key].replace('\n', '').replace('/r', '').replace('\t', '').replace(' ',
                                                                                             '') + '=ID(stored=True, unique=True), '
            elif keys[0] == "QuesID":
                s += keys[key].replace('\n', '').replace('/r', '').replace('\t', '').replace(' ',
                                                                                             '') + '=ID(stored=True, unique=True), '
            elif keys[0] == "VariableID":
                s += keys[key].replace('\n', '').replace('/r', '').replace('\t', '').replace(' ',
                                                                                             '') + '=ID(stored=True, unique=True), '
        else:
            s += keys[key].replace('\n', '').replace('/r', '').replace('\t', '').replace(' ',
                                                                                         '') + '=TEXT(stored=True, analyzer=analyzer), '
    s = s.rstrip(", ")
    s += ")"
    # print(s)
    schema = eval(s)
    if not os.path.exists(indexdir):
        os.makedirs(indexdir)
    ix = create_in(indexdir, schema=schema, indexname=indexname)
    writer = ix.writer()
    for row in rows:
        docline = """writer.add_document("""
        for key in keys:
            val = row[key]
            if not val:
                val = ""
            elif isinstance(val, (Decimal,)):
                val = str(val)

            else:
                val = pymysql.escape_string(str(val))
            docline += key + '="' + val + '", '
        docline = docline.rstrip(", ")
        docline += """)"""
        # print(docline)
        exec(docline)
    writer.commit()


def escape(s, obj="’"):
    ret = ''
    for x in s:
        if x == obj:
            ret += '\\'
        ret += x
    return ret


def app():
    tables = ["meta_project", "meta_questionnaire", "meta_variable"]
    # filepath = Config().get_content("indexFilePath")["filepath"]
    filepath = "/data/zk_css/index"  # 指定路径,首先需要创建
    indexdir = "indexdir"
    filename = filepath + "/" + indexdir
    projKey = []
    quesKey = []
    varKey = []
    dataBase = "db_metadata"
    ret = MyPymysql('notdbMysql')
    for i in range(len(tables)):
        if tables[i] == "meta_project":
            ProjectSql = "select COLUMN_NAME as columnName from information_schema.COLUMNS where table_name = '{}' and table_schema = '{}';".format(
                tables[i],
                dataBase)

            ProjectColumnName = ret.selectall_sql(ProjectSql)
            for j in ProjectColumnName:
                projKey.append(j["columnName"])
        elif tables[i] == "meta_variable":
            VariableSql = "select COLUMN_NAME as columnName from information_schema.COLUMNS where table_name = '{}' and table_schema = '{}';".format(
                tables[i],
                dataBase)

            VariableColumnName = ret.selectall_sql(VariableSql)
            for j in VariableColumnName:
                varKey.append(j["columnName"])
        elif tables[i] == "meta_questionnaire":
            QuesSql = "select COLUMN_NAME as columnName from information_schema.COLUMNS where table_name = '{}' and table_schema = '{}';".format(
                tables[i],
                dataBase)

            QuesColumnName = ret.selectall_sql(QuesSql)
            for j in QuesColumnName:
                quesKey.append(j["columnName"])
    ret.close()

    for table in tables:
        if table == "meta_project":
            keys = projKey
        elif table == "meta_questionnaire":
            keys = quesKey
        elif table == "meta_variable":
            keys = varKey
        db_connect = MyPymysql("metadata")
        name = table.split("_")
        dbname = name[0] + name[1]
        parse_db(table, db_connect, indexname=dbname, schema="", indexdir=filename, keys=keys)


if __name__ == '__main__':
    # 1. 修改mypymysql的用户名密码
    # 2. 修改filepath的路径,前提是先创建
    app()
