#!/usr/bin/env python3
# coding: UTF-8
try:
    import sys
    import importlib
    importlib.reload(sys)
    import socket
    from gspylib.inspection.common import SharedFuncs
    from gspylib.inspection.common.CheckItem import BaseItem
    from gspylib.inspection.common.CheckResult import ResultStatus
    from gspylib.common.Common import DefaultValue, ClusterCommand
except ImportError as ie:
    raise Exception("[GAUSS-52200] : Unable to import module: %s." % str(ie))

g_result = {}


class CheckUnAnalyzeTable(BaseItem):
    def __init__(self):
        super(CheckUnAnalyzeTable, self).__init__(self.__class__.__name__)

    def check_dws_table(self, sql_db, sql1, sql2):
        global g_result
        dbList = []
        (status, result, error) = ClusterCommand.excuteSqlOnLocalhost(self.port,
                                                                      sql_db)
        if status != 2:
            raise Exception("Execute sql:%s failed. Error:%s" % (sql_db, error))
        recordsCount = len(result)
        for i in range(0, recordsCount):
            dbList.append(result[i][0])
        dbList.remove("template0")
        final_result = ""
        for db in dbList:
            table_list = []
            ClusterCommand.excuteSqlOnLocalhost(self.port, sql1, db)
            ClusterCommand.excuteSqlOnLocalhost(self.port, "set client_min_messages='error';"
                                                           "create table to_be_selected_check(test int);", db)
            sql2 = "set client_min_messages='error';%s" % sql2
            (status, result, error) = ClusterCommand.excuteSqlOnLocalhost(
                self.port, sql2, db)
            if status != 2:
                raise Exception("Execute sql:%s failed. Error:%s" % (sql2, error))
            if result and result[0][0]:
                for tmptable in result[0][0].splitlines():
                    if db == "postgres" and tmptable.upper().startswith("PMK."):
                        pass
                    else:
                        table_list.append(tmptable)
                if table_list:
                    final_result += "%s:\n%s\n" % (db, "\n".join(table_list))
            g_result[db] = table_list
        return final_result

    def check_table(self, sql_db, sql1, sql2):
        global g_result
        # Get the database in the node, remove template0
        output = SharedFuncs.runSqlCmd(sql_db, self.user, "", self.port,
                                       self.tmpPath, "postgres", self.mpprcFile)
        dbList = output.split("\n")
        dbList.remove("template0")
        final_result = ""
        for db in dbList:
            table_list = []
            SharedFuncs.runSqlCmd(sql1, self.user, "", self.port, self.tmpPath,
                                  db, self.mpprcFile)
            output = SharedFuncs.runSqlCmd(sql2, self.user, "", self.port,
                                           self.tmpPath, db, self.mpprcFile)
            for tmp_table in output.splitlines():
                if (db == "postgres" and tmp_table.upper().startswith("PMK.")):
                    pass
                else:
                    table_list.append(tmp_table)
            if (table_list):
                final_result += "%s:\n%s\n" % (db, "\n".join(table_list))
            g_result[db] = table_list
        return final_result

    def doCheck(self):
        flag = SharedFuncs.getFirstCNInstance(self.user, self.mpprcFile, self.tmpPath)
        if flag or self.cluster.isSingleInstCluster():
            sql1 = """create or replace FUNCTION get_antiempty_tables(
OUT result_tables text
) returns text as $$
declare
    test_sql text;
    type cursor_type is ref cursor;
    cur_sql_stmts cursor_type;
    cur_test_sql_result cursor_type;
    test_sql_result int;
    result_tables text := '';
begin
    drop table if exists to_be_selected_check;
    create temp table to_be_selected_check distribute by REPLICATION as
     select 'select 1 from ' || nspname || '.' || relname || ' limit 1;' as stmt
      from pg_catalog.pg_class c, pg_catalog.pg_namespace n where c.relnamespace=n.oid and c.reltuples=0
        AND n.nspname <> 'pg_catalog'
        AND n.nspname <> 'cstore'
        AND n.nspname <> 'information_schema'
        AND n.nspname <> 'schema_cur_table_col'
        AND n.nspname <> 'schema_cur_table'
        AND c.relname not in ('gs_wlm_session_info', 'gs_wlm_operator_info', 'gs_wlm_ec_operator_info', 
        'gs_blocklist_query', 'sql_plan', 'sql_plan_baseline')
        AND c.relkind='r'
        AND c.relpersistence='p'
        AND n.nspname !~ '^pg_toast';

    open cur_sql_stmts for 'select stmt from to_be_selected_check';
    loop
        fetch cur_sql_stmts into test_sql;
        exit when cur_sql_stmts%notfound;
        open cur_test_sql_result for test_sql;
        fetch cur_test_sql_result into test_sql_result;
        if not cur_test_sql_result%notfound and 0 = position('to_be_selected_check' in test_sql) then
            result_tables = result_tables ||
             replace(replace(replace(test_sql, 'select 1 from ', ''), ' limit 1', ''), ';', CHR(10));
        end if ;
        close cur_test_sql_result;
    end loop;
    close cur_sql_stmts;
    drop table if exists to_be_selected_check;
    return result_tables;
end; $$
LANGUAGE 'plpgsql';"""

            sql2 = "select get_antiempty_tables();"
            sqldb = "select datname from pg_catalog.pg_database;"
            dataDir = self.cluster.getDbNodeByName(socket.gethostname()).cmagents[0].datadir
            security_mode_value = DefaultValue.getSecurityMode(dataDir)
            if security_mode_value == "on":
                final_result = self.check_dws_table(sqldb, sql1, sql2)
                if final_result:
                    self.result.val = "The result is not ok:\n%s" % final_result
                    self.result.rst = ResultStatus.NG
                else:
                    self.result.val = "All table analyzed"
                    self.result.rst = ResultStatus.OK
            else:
                final_result = self.check_table(sqldb, sql1, sql2)
                if final_result:
                    self.result.val = "Tables unanalyzed:\n%s" % final_result
                    self.result.rst = ResultStatus.NG
                else:
                    self.result.val = "All table analyzed"
                    self.result.rst = ResultStatus.OK
        else:
            self.result.rst = ResultStatus.NA
            self.result.val = "First cn is not in this host"

    def doSet(self):
        result_str = ""
        for db in g_result.keys():
            for table in g_result[db]:
                sql = "analyze %s;" % table
                output = SharedFuncs.runSqlCmd(sql, self.user, "", self.port, self.tmpPath, db, self.mpprcFile)
                result_str = "%s%s:%s Result: %s.\n" % (result_str, db, table, output)
        self.result.val = "Analyze %s successfully." % result_str
