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



import traceback
from app.models import Table,Database,DES,Columns,db,TableVacuate

from osgeo.ogr import DataSource,FeatureDefn,FieldDefn,Layer

import datetime
import uuid

from sqlalchemy.orm import Session

from app.util.component.PGUtil import PGUtil
from app.util.component.ModelVisitor import ModelVisitor
from app.util.component.StructuredPrint import StructurePrint
from app.util.component.ApiTemplate import ApiTemplate
from app.util.component.GeometryAdapter import GeometryAdapter

class Api(ApiTemplate):
    api_name = "数据刷新"
    def process(self):

        res = {}
        pg_ds =None
        data_session=None
        this_time = datetime.datetime.now()
        try:
            
            database_guid = self.para.get("database_guid")
            database = Database.query.filter_by(guid=database_guid).one_or_none()
    
            if not database:
                raise Exception("数据库不存在!")
            spatial_tables = Table.query.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]
            db_tables_names = []
    

            pg_ds: DataSource = PGUtil.open_pg_data_source(1, DES.decode(database.sqlalchemy_uri))
            # 更新空间表
            # 增加表
            self.add_spatail_table(database, pg_ds, spatial_tables_names, db_tables_names, this_time)
    
            # 删除/修改表
            self.edit_spatial_table(pg_ds, spatial_tables, db_tables_names, this_time)

            # 处理抽稀表
            self.deal_vacuate_table(pg_ds,database.guid)


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

    
            # 注册普通表
            # 实体库连接
            data_session: Session = PGUtil.get_db_session(DES.decode(database.sqlalchemy_uri))
    
            # 空间表
            spatial_tables = Table.query.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 = Table.query.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, database_guid, real_common_tables_name, origin_common_tables_name,
                            this_time)

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

            db.session.commit()
            res["msg"] = "刷新数据成功！"
            res["result"] = True

        except Exception as e:
            raise e
    
        finally:
            if pg_ds:
                pg_ds.Destroy()
            if data_session:
                data_session.close()
    
        return res
    

    def add_spatail_table(self,database,pg_ds,spatial_tables_names,db_tables_names,this_time):
        # 更新空间表
        # 增加表

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

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

                if layer.GetName().__contains__("_vacuate_"):
                    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("空间表增加！")

                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(layer.GetGeomType()),
                              extent=extent,
                              feature_count=feature_count
                              )
                db.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().lower()
                    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)
                    db.session.add(column)

    def deal_vacuate_table(self,pg_ds,database_guid):


        for i in range(pg_ds.GetLayerCount()):
            layer: Layer = pg_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]
                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 =Table.query.filter_by(name=base_layer_name,database_guid=database_guid).one_or_none()
                if base_table:
                    if not TableVacuate.query.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))
                        db.session.add(table_vacuate)
                        Table.query.filter_by(guid=base_table.guid).update({"is_vacuate": 1})
                else:
                    kk=1




    def edit_spatial_table(self,pg_ds,spatial_tables,db_tables_names,this_time):

        for table in spatial_tables:

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

                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().lower()
                    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)
                        db.session.add(column)

                # 删除列
                for column in columns:
                    if column.name not in db_columns_names:
                        StructurePrint.print("{}空间表属性减少！".format(table.name))
                        db.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))
                    Table.query.filter_by(guid=table.guid).update({"feature_count": feature_count,
                                                                   "extent": extent})


    def add_common_table(self,data_session,database_guid,real_common_tables_name,origin_common_tables_name,this_time):
        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__()
                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
                              )

                db.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)
                    db.session.add(column)

                # 删除不存在的表
                for n in origin_common_tables_name:
                    if n not in real_common_tables_name:
                        table = Table.query.filter_by(name=n).filter_by(database_guid=database_guid).one_or_none()
                        if table:
                            db.session.delete(table)

    def edit_common_table(self,data_session,database_guid,real_common_tables_name,origin_common_tables_name,this_time):
        for table_name in origin_common_tables_name:
            table = Table.query.filter_by(name=table_name).filter_by(database_guid=database_guid).one_or_none()
            if table:
                if table_name not in real_common_tables_name:
                    StructurePrint.print("{}非空间表减少！".format(table_name))
                    db.session.delete(table)
                # 修改表
                else:
                    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)
                            db.session.add(column)

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

                    # 修改要素量
                    sql = 'select count(*) from "{}"'.format(table_name)

                    count = data_session.execute(sql).fetchone()[0]
                    if not table.feature_count.__eq__(count):
                        StructurePrint.print("{}表要素变化！".format(table_name))
                        Table.query.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": "user",
         "in": "formData",
         "type": "string",
         "description": "用户"}

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