#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__()

            refresh_process = multiprocessing.Process(target=self.table_refresh,args=(database,task_guid))
            refresh_process.start()

            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()
            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):

        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)

            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, sys_session,spatial_tables_names, this_time,db_tuple)# 实体库中空间表名

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

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


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

            # 注册普通表
            # 实体库连接
            data_session: Session = PGUtil.get_db_session(DES.decode(database.sqlalchemy_uri))

            # 处理后空间表
            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)

            # 删除、修改普通表
            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,sys_session,spatial_tables_names,this_time,db_tuple):
        '''
        注册新增空间表
        :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


                    # 范围统计和数量统计以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")
                    # 要素少于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()
                    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("空间表增加！")

                    geom_type = GeometryAdapter.get_geometry_type(layer)
                except:
                    StructurePrint().print("表{}注册失败！".format(l_name), "warn")
                    continue

                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,
                              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,sys_session,spatial_tables,db_tables_names,this_time,db_tuple):



        for table in spatial_tables:

            # 删除表
            if table.name not in db_tables_names:
                StructurePrint().print("空间表减少！")
                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

                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")
                # 要素少于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()
                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})


    def add_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 real_common_tables_name:
            if table_name not in origin_common_tables_name:
                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]

                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
                              )

                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)

    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:
            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})

    
    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":{
                }
            }
            }
        }
}