#author:        4N
#createtime:    2021/1/27
#email:         nheweijun@sina.com

import traceback
from ..models import Table,Database,Columns,db,TableVacuate,Task,DES
from osgeo.ogr import DataSource,FeatureDefn,FieldDefn,Layer
import datetime
import uuid
from sqlalchemy.orm import Session
from app.util.component.SQLUtil import SQLUtil
from app.util.component.PGUtil import PGUtil
from app.util.component.StructurePrint import StructurePrint
from app.util.component.ApiTemplate import ApiTemplate
from app.util.component.GeometryAdapter import GeometryAdapter
from app.util.component.TaskController import TaskController
from app.util.component.TaskWriter import TaskWriter
import multiprocessing
import configure
from app.util.component.UserCheck import UserCheck
class Api(ApiTemplate):
    api_name = "数据刷新"
    def process(self):
        res = {}
        try:

            database_guid = self.para.get("database_guid")
            database = Database.query.filter_by(guid=database_guid).one_or_none()

            #验证权限
            UserCheck.verify(database.creator)

            if not database:
                raise Exception("数据库不存在!")
                # 初始化task
            task_guid = uuid.uuid1().__str__()

            task = Task(guid=task_guid,
                        name="更新 | {}".format(database.alias),
                        create_time=datetime.datetime.now(),
                        state=0,
                        task_type=3,
                        creator=self.para.get("creator"),
                        file_name=None,
                        database_guid=database.guid,
                        process="数据库更新中",
                        # task_pid=refresh_process.pid
                        )

            db.session.add(task)
            db.session.commit()

            refresh_process = multiprocessing.Process(target=self.table_refresh,args=(database,task_guid,self.para.get("creator")))
            refresh_process.start()

            Task.query.filter_by(guid=task_guid).update({"task_pid":refresh_process.pid})
            db.session.commit()

            res["msg"] = "数据库更新已提交！"
            res["data"] = task_guid
            res["result"] = True

        except Exception as e:
            print(traceback.format_exc())
            raise e
        return res


    def table_refresh(self,database,task_guid,creator):

        pg_ds =None
        sys_ds =None
        data_session=None
        result = {}
        sys_session = None
        task_writer = None


        db_tuple = PGUtil.get_info_from_sqlachemy_uri(DES.decode(database.sqlalchemy_uri))

        try:

            #任务控制,等待执行
            TaskController.wait(task_guid)
            task_writer = TaskWriter(task_guid)


            sys_session = PGUtil.get_db_session(configure.SQLALCHEMY_DATABASE_URI)
            sys_ds = PGUtil.open_pg_data_source(0,configure.SQLALCHEMY_DATABASE_URI)
            # 实体库连接
            data_session: Session = PGUtil.get_db_session(DES.decode(database.sqlalchemy_uri))

            this_time = datetime.datetime.now()
            database_guid = database.guid

            # 已注册空间表
            spatial_tables = sys_session.query(Table).order_by(Table.create_time.desc()).filter_by(database_guid=database_guid).filter(
                Table.table_type != 0).all()

            # 已注册空间表名
            spatial_tables_names = [table.name for table in spatial_tables]

            # 实体库datasource
            pg_ds: DataSource = PGUtil.open_pg_data_source(1, DES.decode(database.sqlalchemy_uri))

            # 更新空间表
            # 增加表
            db_tables_names = self.add_spatail_table(database, pg_ds, data_session,sys_session,spatial_tables_names, this_time,db_tuple,creator)# 实体库中空间表名

            # 删除/修改表
            self.edit_spatial_table(pg_ds, data_session, sys_session,spatial_tables, db_tables_names, this_time,db_tuple)

            # 空间表处理完毕
            sys_session.commit()


            # 注册普通表


            # 处理后空间表
            spatial_tables = sys_session.query(Table).order_by(Table.create_time.desc()).filter_by(database_guid=database_guid).filter(
                Table.table_type != 0).all()
            # 处理后空间表名
            spatial_tables_names = [table.name for table in spatial_tables]

            # 原有普通表
            common_tables = sys_session.query(Table).order_by(Table.create_time.desc()).filter_by(database_guid=database_guid).filter(
                Table.table_type == 0).all()
            # 原有普通表名
            origin_common_tables_name = [table.name for table in common_tables]

            # 现有普通表
            real_common_tables_name = []

            # 只注册public中的表
            common_result = data_session.execute(
                "select relname as tabname from pg_class c where  relkind = 'r' and relnamespace=2200 and  relname not like 'pg_%' and relname not like 'sql_%' order by relname").fetchall()
            for re in common_result:
                table_name = re[0]
                if table_name not in spatial_tables_names and (not table_name.__contains__("_vacuate_")):
                    real_common_tables_name.append(table_name)

            # 增加新普通表

            self.add_common_table(data_session, sys_session, database_guid, real_common_tables_name, origin_common_tables_name,
                                  this_time,db_tuple,creator)
            sys_session.commit()

            # 删除、修改普通表
            self.edit_common_table(data_session,sys_session, database_guid, real_common_tables_name, origin_common_tables_name,
                                   this_time,db_tuple)

            sys_session.commit()
            result["data"] = "刷新数据成功！"
            result["state"] = 1

            task_writer.update_task({"state": 1, "update_time": datetime.datetime.now(),"process":"更新成功"})


        except Exception as e:
            try:
                StructurePrint().print(traceback.format_exc())
                task_writer.update_task({"state": -1, "update_time": datetime.datetime.now(),"process":"更新失败"})
                task_writer.update_process(e.__str__())
                task_writer.update_process("任务中止！")
            except Exception as ee:
                StructurePrint().print(traceback.format_exc())
        finally:
            task_writer.close()
            if pg_ds:
                pg_ds.Destroy()
            if data_session:
                data_session.close()
            if sys_session:
                sys_session.close()
            if sys_ds:
                sys_ds.Destroy()
        return result

    def add_spatail_table(self,database,pg_ds,data_session,sys_session,spatial_tables_names,this_time,db_tuple,creator):
        '''
        注册新增空间表
        :param database:
        :param pg_ds:
        :param spatial_tables_names: 已注册空间表名
        :param this_time:
        :return: 实体库中空间表名
        '''

        db_tables_names=[]

        for i in range(pg_ds.GetLayerCount()):
            layer: Layer = pg_ds.GetLayer(i)
            geom_column = layer.GetGeometryColumn()
            db_tables_names.append(layer.GetName())
            if not geom_column:
                continue
            if layer.GetName() not in spatial_tables_names:
                l_name = layer.GetName()

                try:
                    # 只注册public的空间表,其他表空间的表名会有.
                    if layer.GetName().__contains__("."):
                        continue
                    # 略过抽稀表
                    if layer.GetName().__contains__("_vacuate_"):
                        continue

                    # # 没有权限的表跳过
                    # if not PGUtil.check_table_privilege(l_name, "SELECT", db_tuple[0], pg_ds):
                    #     StructurePrint().print("用户{}对表{}没有select权限！".format(db_tuple[0], l_name), "warn")
                    #     continue

                    # 没有权限的表跳过
                    if not SQLUtil.check_table_privilege(l_name, "SELECT", db_tuple[0], data_session):
                        StructurePrint().print("用户{}对表{}没有select权限！".format(db_tuple[0], l_name), "warn")
                        continue

                    # 范围统计和数量统计以100w为界限
                    query_count_layer = data_session.execute('''SELECT reltuples::bigint AS ec FROM pg_class WHERE  oid = 'public."{}"'::regclass'''.format(l_name)).fetchone()
                    feature_count = query_count_layer[0]

                    # query_count_layer: Layer = pg_ds.ExecuteSQL(
                    #     '''SELECT reltuples::bigint AS ec FROM pg_class WHERE  oid = 'public."{}"'::regclass'''.format(
                    #         l_name))

                    # feature_count = query_count_layer.GetFeature(0).GetField("ec")




                    # 要素少于100w可以精确统计
                    if feature_count < 1000000:
                        feature_count = layer.GetFeatureCount()
                        ext = layer.GetExtent()
                    else:
                        # query_ext_layer: Layer = pg_ds.ExecuteSQL(
                        #     "select geometry(ST_EstimatedExtent('public', '{}','{}'))".format(l_name,
                        #                                                                       layer.GetGeometryColumn()))
                        #
                        # ext = query_ext_layer.GetExtent()
                        try:

                            ext = data_session.execute("select st_xmin(box3d),st_xmax(box3d),st_ymin(box3d),st_ymax(box3d) from (select box3d(geometry(ST_EstimatedExtent('public', '{}','{}')))) as b".format(l_name,
                                                                                              layer.GetGeometryColumn())).fetchone()
                        except:
                            data_session.rollback()
                            ext = layer.GetExtent()


                    if ext[0] < 360:
                        ext = [round(e, 6) for e in ext]
                    else:
                        ext = [round(e, 2) for e in ext]
                    extent = "{},{},{},{}".format(ext[0], ext[1], ext[2], ext[3])

                    StructurePrint().print("空间表{}增加！".format(l_name))

                    geom_type = GeometryAdapter.get_geometry_type(layer)
                except Exception as e:
                    StructurePrint().print("{}".format(e.__str__()+":"+ traceback.format_exc()), "warn")
                    StructurePrint().print("空间表{}新增失败！".format(l_name), "warn")
                    data_session.rollback()
                    continue

                olds = sys_session.query(Table).filter_by(name=l_name,database_guid=database.guid).all()
                for old in olds:
                    sys_session.delete(old)


                table_guid = uuid.uuid1().__str__()
                table = Table(guid=table_guid,
                              database_guid=database.guid,
                              # alias=layer.GetName(),
                              name=layer.GetName(), create_time=this_time, update_time=this_time,
                              table_type=GeometryAdapter.get_table_type(geom_type),
                              extent=extent,
                              creator=creator,
                              feature_count=feature_count
                              )
                sys_session.add(table)
                feature_defn: FeatureDefn = layer.GetLayerDefn()

                for i in range(feature_defn.GetFieldCount()):
                    field_defn: FieldDefn = feature_defn.GetFieldDefn(i)
                    field_name = field_defn.GetName()
                    field_alias = field_name if field_defn.GetAlternativeName() is None or field_defn.GetAlternativeName().__eq__(
                        "") else field_defn.GetAlternativeName()
                    column = Columns(guid=uuid.uuid1().__str__(), table_guid=table_guid,
                                     name=field_name, alias=field_alias, create_time=this_time, update_time=this_time)
                    sys_session.add(column)
        return db_tables_names

    def deal_vacuate_table(self,sys_ds,sys_session,database_guid):


        for i in range(sys_ds.GetLayerCount()):
            layer: Layer = sys_ds.GetLayer(i)
            geom_column = layer.GetGeometryColumn()

            if not geom_column:
                continue



            if layer.GetName().__contains__("_vacuate_"):
                l_name = layer.GetName()

                base_layer_name = l_name.split("_vacuate_")[0].split("_")[1]

                level = l_name.split("_")[-2]

                pixel_distance_str: str ="0"
                try:
                    pixel_distance_str: str = l_name.split("_")[-1]
                    if pixel_distance_str.startswith("0"):
                        pixel_distance_str = "0.{}".format(pixel_distance_str)
                except:
                    pass

                base_table =sys_session.query(Table).filter_by(name=base_layer_name,database_guid=database_guid).one_or_none()
                if base_table:
                    if not sys_session.query(TableVacuate).filter_by(table_guid=base_table.guid,name=l_name).one_or_none():
                        table_vacuate = TableVacuate(guid=uuid.uuid1().__str__(),
                                                     table_guid=base_table.guid,
                                                     level=level,
                                                     name=l_name,
                                                     pixel_distance=float(pixel_distance_str))
                        sys_session.add(table_vacuate)

                        sys_session.query(Table).filter_by(guid=base_table.guid).update({"is_vacuate": 1})
                else:
                    kk=1



    def edit_spatial_table(self,pg_ds, data_session,sys_session,spatial_tables,db_tables_names,this_time,db_tuple):

        for table in spatial_tables:
            try:
                # 删除表
                if table.name not in db_tables_names:
                    StructurePrint().print("{}空间表减少！".format(table.name))
                    sys_session.delete(table)
                # 修改表
                else:
                    layer: Layer = pg_ds.GetLayerByName(table.name)
                    l_name = layer.GetName()

                    # 只注册public的空间表,其他表空间的表名会有.
                    if layer.GetName().__contains__("."):
                        continue

                    if layer.GetName().__contains__("_vacuate_"):
                        continue

                    # # 没有权限的表跳过
                    # if not PGUtil.check_table_privilege(l_name, "SELECT", db_tuple[0], pg_ds):
                    #     StructurePrint().print("用户{}对表{}没有select权限！".format(db_tuple[0], l_name), "warn")
                    #     sys_session.delete(table)
                    #     continue

                    # 没有权限的表跳过
                    if not SQLUtil.check_table_privilege(l_name, "SELECT", db_tuple[0], data_session):
                        StructurePrint().print("用户{}对表{}没有select权限！".format(db_tuple[0], l_name), "warn")
                        sys_session.delete(table)
                        continue

                    columns = table.relate_columns
                    columns_names = [column.name for column in columns]
                    feature_defn: FeatureDefn = layer.GetLayerDefn()
                    db_columns_names = []

                    # 增加列
                    for i in range(feature_defn.GetFieldCount()):
                        field_defn: FieldDefn = feature_defn.GetFieldDefn(i)
                        field_name = field_defn.GetName()
                        db_columns_names.append(field_name)

                        if field_name not in columns_names:
                            StructurePrint().print("{}空间表属性增加！".format(table.name))
                            field_alias = field_name if field_defn.GetAlternativeName() is None or field_defn.GetAlternativeName().__eq__(
                                "") else field_defn.GetAlternativeName()
                            column = Columns(guid=uuid.uuid1().__str__(), table_guid=table.guid,
                                             name=field_name, alias=field_alias, create_time=this_time,
                                             update_time=this_time)
                            sys_session.add(column)

                    # 删除列
                    for column in columns:
                        if column.name not in db_columns_names:
                            StructurePrint().print("{}空间表属性减少！".format(table.name))
                            sys_session.delete(column)

                    # 范围统计和数量统计以100w为界限
                    # query_count_layer: Layer = pg_ds.ExecuteSQL(
                    #     '''SELECT reltuples::bigint AS ec FROM pg_class WHERE  oid = 'public."{}"'::regclass'''.format(
                    #         l_name))
                    # feature_count = query_count_layer.GetFeature(0).GetField("ec")

                    query_count_layer = data_session.execute('''SELECT reltuples::bigint AS ec FROM pg_class WHERE  oid = 'public."{}"'::regclass'''.format(l_name)).fetchone()
                    feature_count = query_count_layer[0]

                    # 要素少于100w可以精确统计
                    if feature_count < 1000000:
                        feature_count = layer.GetFeatureCount()
                        ext = layer.GetExtent()

                    else:
                        # query_ext_layer: Layer = pg_ds.ExecuteSQL(
                        #     "select geometry(ST_EstimatedExtent('public', '{}','{}'))".format(l_name,
                        #                                                                       layer.GetGeometryColumn()))
                        # ext = query_ext_layer.GetExtent()
                        try:
                            ext = data_session.execute("select st_xmin(box3d),st_xmax(box3d),st_ymin(box3d),st_ymax(box3d) from (select box3d(geometry(ST_EstimatedExtent('public', '{}','{}')))) as b".format(l_name,
                                                                                              layer.GetGeometryColumn())).fetchone()
                        except:
                            data_session.rollback()
                            ext = layer.GetExtent()

                    if ext[0] < 360:
                        ext = [round(e, 6) for e in ext]
                    else:
                        ext = [round(e, 2) for e in ext]
                    extent = "{},{},{},{}".format(ext[0], ext[1], ext[2], ext[3])

                    # 修改要素量
                    if not table.feature_count.__eq__(feature_count):
                        StructurePrint().print("{}空间表要素！".format(table.name))
                        sys_session.query(Table).filter_by(guid=table.guid).update({"feature_count": feature_count,
                                                                       "extent": extent})
            except Exception as e:
                StructurePrint().print("{}".format(e.__str__() + ":" + traceback.format_exc()), "warn")
                StructurePrint().print("空间表{}修改失败！".format(table.name), "warn")
                data_session.rollback()
                continue

    def add_common_table(self,data_session,sys_session,database_guid,real_common_tables_name,origin_common_tables_name,this_time,db_tuple,creator):
        for table_name in real_common_tables_name:
            if table_name not in origin_common_tables_name:
                try:

                    StructurePrint().print("{}非空间表增加！".format(table_name))
                    table_guid = uuid.uuid1().__str__()


                    # 没有权限的表跳过
                    if not SQLUtil.check_table_privilege(table_name, "SELECT", db_tuple[0], data_session):
                        StructurePrint().print("用户{}对表{}没有select权限！".format(db_tuple[0], table_name), "warn")
                        continue

                    count = data_session.execute('select count(*) from "{}"'.format(table_name)).fetchone()[0]

                    olds = sys_session.query(Table).filter_by(name=table_name, database_guid=database_guid).all()
                    for old in olds:
                        sys_session.delete(old)

                    table = Table(guid=table_guid,
                                  database_guid=database_guid,
                                  name=table_name, create_time=this_time, update_time=this_time,
                                  table_type=0,
                                  feature_count=count,
                                  creator = creator
                                  )

                    sys_session.add(table)

                    sql = '''
                       SELECT
                           a.attnum,
                           a.attname AS field
                       FROM
                           pg_class c,
                           pg_attribute a,
                           pg_type t
                       WHERE
                           c.relname = '{}'
                           and a.attnum > 0
                           and a.attrelid = c.oid
                           and a.atttypid = t.oid
                       ORDER BY a.attnum 
                       '''.format(table_name)

                    cols = data_session.execute(sql).fetchall()
                    for col in cols:
                        column = Columns(guid=uuid.uuid1().__str__(), table_guid=table_guid,
                                         name=col[1], create_time=this_time, update_time=this_time)
                        sys_session.add(column)

                    # 删除不存在的表
                    for n in origin_common_tables_name:
                        if n not in real_common_tables_name:
                            tables = Table.query.filter_by(name=n).filter_by(database_guid=database_guid).all()
                            for table in tables:
                                sys_session.delete(table)
                except Exception as e:
                    StructurePrint().print("{}".format(e.__str__() + ":" + traceback.format_exc()), "warn")
                    StructurePrint().print("非空间表{}增加失败！".format(table_name), "warn")
                    data_session.rollback()
                    continue

    def edit_common_table(self,data_session,sys_session,database_guid,real_common_tables_name,origin_common_tables_name,this_time,db_tuple):
        for table_name in origin_common_tables_name:
            try:
                tables = sys_session.query(Table).filter_by(name=table_name).filter_by(database_guid=database_guid).all()
                for table in tables:
                    if table_name not in real_common_tables_name:
                        StructurePrint().print("{}非空间表减少！".format(table_name))
                        sys_session.delete(table)
                    # 修改表
                    else:

                        # 没有权限的表删除
                        if not SQLUtil.check_table_privilege(table_name, "SELECT", db_tuple[0], data_session):
                            StructurePrint().print("用户{}对表{}没有select权限！".format(db_tuple[0], table_name), "warn")
                            sys_session.delete(table)
                            continue

                        columns = table.relate_columns
                        columns_names = [column.name for column in columns]

                        sql = '''
                           SELECT
                               a.attnum,
                               a.attname AS field
                           FROM
                               pg_class c,
                               pg_attribute a,
                               pg_type t
                           WHERE
                               c.relname = '{}'
                               and a.attnum > 0
                               and a.attrelid = c.oid
                               and a.atttypid = t.oid
                           ORDER BY a.attnum 
                           '''.format(table_name)

                        cols = data_session.execute(sql).fetchall()
                        real_cols_name = [col[1] for col in cols]

                        # 属性增加
                        for col in real_cols_name:
                            if col not in columns_names:
                                StructurePrint().print("{}表要素属性增加！".format(table_name))
                                column = Columns(guid=uuid.uuid1().__str__(), table_guid=table.guid,
                                                 name=col, create_time=this_time, update_time=this_time)
                                sys_session.add(column)

                        # 属性减少
                        for column in columns:
                            if column.name not in real_cols_name:
                                StructurePrint().print("{}表要素属性减少！".format(table_name))
                                sys_session.delete(column)

                        count = SQLUtil.get_table_count(table_name,data_session)

                        if not table.feature_count.__eq__(count):
                            StructurePrint().print("{}表要素变化！".format(table_name))
                            sys_session.query(Table).filter_by(guid=table.guid).update({"feature_count": count})
            except Exception as e:
                StructurePrint().print("{}".format(e.__str__() + ":" + traceback.format_exc()), "warn")
                StructurePrint().print("非空间表{}修改失败！".format(table_name), "warn")
                data_session.rollback()
                continue
    
    api_doc={
    "tags":["管理接口"],
    "parameters":[
        {"name": "database_guid",
         "in": "formData",
         "type": "string",
         "description": "数据库guid",
         "required":"true"},
        {"name": "creator",
         "in": "formData",
         "type": "string",
         "description": "创建者"}

    ],
    "responses":{
        200:{
            "schema":{
                "properties":{
                }
            }
            }
        }
}