# from ..db_connection import SQLiteConnection

from kfrm_tool.include.db.dao.damage_calculation_by_kict.agriculture import (
    agriculture_damage_calculation,
)
from kfrm_tool.include.db.dao.damage_calculation_by_kict.building import (
    building_damage_calculation,
)
from kfrm_tool.include.db.dao.damage_calculation_by_kict.population import (
    population_damage_calculation,
)
from kfrm_tool.include.db.dao.damage_calculation_by_kict.vehicle import (
    vehicle_damage_calculation,
)
from kfrm_tool.include.setting.logger import KFRMLogger
from kfrm_tool.include.setting.name_list import *
from kfrm_tool.include.setting.path_list import DefaultPath


class SqliteQuery:
    def __init__(self, conn):
        self.sqlite = conn
        self.path = DefaultPath()

    # create
    def createInventoryTables(self):
        sqlPath = self.path.getSpatialInvenSqlPath()
        with open(sqlPath) as f:
            contents = f.read()
            self.sqlite.excScript(contents)

    def returnTempAreaListSQL(self, tableName):
        areaTbl = "tmp_area_" + tableName
        sql = f"""
                DROP TABLE IF EXISTS '{areaTbl}';
                CREATE TEMP TABLE '{areaTbl}' as
                WITH RECURSIVE cte(org, part, rest, pos, ct, sig_cd, emd_cd) as (
                    SELECT l_admin, '', l_admin||' ', 0, 0, sig_cd, emd_cd 
                    FROM '{tableName}' GROUP BY sig_cd, emd_cd
                UNION ALL
                    SELECT org, substr(org,1, pos + instr(rest, ' ')),
                        substr(rest, instr(rest, ' ')+1),
                        pos + INSTR(rest, ' '),
                        ct+1, sig_cd, emd_cd
                    FROM 'cte' WHERE instr(rest, ' ')>0       
                ) SELECT part, ct, sig_cd, emd_cd
                FROM 'cte' WHERE pos <> 0
                GROUP BY part ORDER BY pos;
                
                UPDATE '{areaTbl}' SET ct=ct-1 WHERE sig_cd IN (
                    SELECT sig_cd FROM '{areaTbl}' WHERE ct=3 AND substr(part, -2)="구 ");
            """
        self.sqlite.excScript(sql)
        return areaTbl

    def tableStandardization(self, tableName, newTblName, depth, flag):
        _, geom, types, coord, srid, _, _, _, _ = self.getGeometryInfo(tableName)
        if flag:  # flag가 True 일 때, code 값을 숫자값으로 변경
            sql = f"""UPDATE '{tableName}' as A SET '{depth}' = 
                (SELECT 
                    (CASE WHEN min!=-9999 THEN min ELSE 0 END + CASE WHEN max!=-9999 THEN max ELSE min END)/2 
                FROM {RECLASS_TABLE} as B WHERE A.'{depth}'=B.code);"""
            self.sqlite.excNone(sql)

        sql = f"CREATE TABLE '{newTblName}' as SELECT CASE WHEN 0<A.'{depth}' AND A.'{depth}'<0.3 THEN 1 ELSE \n"
        for i in range(1, 10):
            sql += f"CASE WHEN {0.3*i}<=A.'{depth}' AND A.'{depth}'<{0.3*(i+1)} THEN {i+1} ELSE \n"
        sql += f"""CASE WHEN 3.0<=A.'{depth}' THEN 11 ELSE 0 {'END '*11} as '{depth}', {geom} FROM '{tableName}' as A 
            WHERE A.'{depth}'>0;
        """

        self.sqlite.excNone(sql)
        self.recoverGeometry(newTblName, geom, srid, types, coord)

    # insert
    def insertTableData(self, tableName, contents):  # contents = [[]]
        valueCount = (", ?" * len(contents[0]))[1:]

        self.sqlite.excNone(f"DELETE FROM {tableName};")
        self.sqlite.excMany(
            f"INSERT INTO {tableName} VALUES ({valueCount});", tuple(contents)
        )

    # update
    def updateTableColumns(self, tableName, targetColumn, changeContent, whereContent):
        sql = f"UPDATE '{tableName}' SET {targetColumn}='{changeContent}' WHERE {whereContent};"
        KFRMLogger.debug(sql)
        self.sqlite.excNone(sql)

    def updateTableMultiColumns(
        self, tableName, targetColumn, changeContent, whereContent
    ):
        update_columns = [
            f"{targetColumn[i]}='{changeContent[i]}'" for i in range(len(targetColumn))
        ]
        sql = (
            f"UPDATE '{tableName}' SET {','.join(update_columns)} WHERE {whereContent};"
        )
        KFRMLogger.debug(sql)
        self.sqlite.excNone(sql)

    # select
    def isReclassTableContent(self):
        sql = f"SELECT count(*) FROM {RECLASS_TABLE};"
        flag = self.sqlite.excValue(sql)[0][0] > 0
        return flag

    def getReaultTableList(self, invenType, grid=""):
        sql = f"SELECT tbl_name FROM sqlite_master WHERE type='table' AND tbl_name like '({invenType})%_result{grid}' COLLATE NOCASE;"
        rst = self.sqlite.excValue(sql)
        tableList = [table[0] for table in rst]
        return tableList

    def getAreaList(self, flag=True, tableName=""):
        if flag:
            sql = f"SELECT area, area_count FROM {LAYER_FLOOD} GROUP BY area_count;"
        else:
            sql = f"SELECT sig_cd, l_admin FROM '{tableName}' WHERE sig_cd<>'' GROUP BY sig_cd;"
        return self.sqlite.excValue(sql)

    def getGeometryInfo(self, tableName):
        sql = f"SELECT * FROM geometry_columns WHERE f_table_name='{tableName}';"
        KFRMLogger.debug("getGeometryInfo : " + sql)
        sql_result = self.sqlite.excValue(sql)
        KFRMLogger.debug(sql_result)
        geomInfo = sql_result[0]
        sql = f"SELECT auth_name, srtext, proj4text FROM spatial_ref_sys WHERE srid='{geomInfo[4]}';"
        KFRMLogger.debug("getGeometryInfo : " + sql)
        sql_result = self.sqlite.excValue(sql)
        KFRMLogger.debug(sql_result)
        geomInfo += sql_result[0]

        # return: tableName, geomName, geomType, coord, srid, index enable, auth_name, srtext, proj
        return geomInfo

    def getUnionGeometry(self, tableName):
        sqliteGeomInfo = self.getGeometryInfo(tableName)
        if sqliteGeomInfo:
            geometryName = sqliteGeomInfo[1]
            srid = sqliteGeomInfo[4]
            auth = sqliteGeomInfo[6]
            proj = sqliteGeomInfo[8]
            srtext = sqliteGeomInfo[7]
            sql = f"""SELECT AsText(ST_Union(ST_MakeValid({geometryName}))) FROM 
                        (SELECT ST_IsValid({geometryName}) AS Valid, 
                            ST_Union({geometryName}) AS geometry
                        FROM '{tableName}' GROUP BY Valid);
            """
            return self.sqlite.excValue(sql), srid, auth, proj, srtext
        else:
            raise Warning("noting values")

    def getIntersectGeometry(self, tableName, wkt):
        geom = self.getGeometryInfo(tableName)[1]
        sql = f"SELECT asWkt(st_union({geom})) FROM '{tableName}' WHERE intersects({geom}, geomFromText('{wkt}'));"
        return self.sqlite.excValue(sql)

    def getOneGeometry(self, tableName):
        sqliteGeomInfo = self.getGeometryInfo(tableName)

        if sqliteGeomInfo:
            geometryName = sqliteGeomInfo[1]
            srid = sqliteGeomInfo[4]
            sql = f"SELECT AsText({geometryName}) FROM '{tableName}' limit 1;"
            return self.sqlite.excValue(sql), srid
        else:
            raise Warning("noting values")

    def getTableHeader(self, tableName, returnAll=False):
        sql = f"PRAGMA table_info('{tableName}');"
        ret = self.sqlite.excValue(sql)
        if returnAll:
            header = ret
        else:
            header = [h[1].lower() for h in ret]

        return header

    def getTableContents(self, tableName, columns="*", condition=""):
        sql = f"SELECT {columns} FROM '{tableName}' {condition};"
        KFRMLogger.debug(sql)
        value = self.sqlite.excValue(sql)
        KFRMLogger.debug(f"getTableContents : {value}")
        return value

    def getFloodColumnName(self, floodTable):
        condition = f"WHERE table_name='{floodTable}'"
        return self.getTableContents(LAYER_FLOOD, "flood_column", condition)[0][0]

    def recoverGeometry(self, tableName, geom, srid, types, coord):
        types = self.convertGeomType(types)
        sql = f"""SELECT recoverGeometryColumn('{tableName}', '{geom}', {srid}, '{types}', '{coord}');
                  SELECT CreateSpatialIndex('{tableName}', '{geom}');"""
        KFRMLogger.debug(sql)
        self.sqlite.excScript(sql)

    def convertGeomType(self, types):
        geomList = [
            "GEOMETRY",
            "POINT",
            "LINESTRING",
            "POLYGON",
            "MULTIPOINT",
            "MULTILINESTRING",
            "MULTIPOLYGON",
            "GEOMETRYCOLLECTION",
        ]
        try:
            types = types if type(types) == str else geomList[types]
            return types
        except IndexError:
            raise IndexError(
                "Ensure that the hazard data input (shp) is in the form of a polygon or multipolygon. The input data includes an unsupported type, such as a polygonZ"
            )

    def isTabled(self, tableName):  # 테이블이 존재하는지 확인
        sql = f"SELECT * FROM sqlite_master WHERE tbl_name='{tableName}' collate nocase AND type='table';"
        ret = self.sqlite.excValue(sql)
        flag = True if ret else False
        return flag

    def isParamSetting(self, types):
        if types == B_PARAM:
            sql = f"""
                SELECT A.occupancy FROM 
                    {B_COSTS} as A, {B_CSVR} as B,
                    {B_DEPRECIATION} as C, {B_ENTRACE} as D
                WHERE 
                    A.occupancy = B.occupancy AND B.occupancy = C.occupancy AND C.occupancy = D.occupancy
                LIMIT 1;
            """
        elif types == B_VUL:
            sql = f"""
                SELECT A.occupancy FROM {B_SDAMAGE} as A, {B_IDAMAGE} as B
                WHERE A.occupancy = B.occupancy LIMIT 1;
            """
        elif types == V_PARAM:
            sql = f"""
                SELECT A.type FROM {V_COSTS} as A, {V_SALVAGE} as B 
                WHERE A.type = B.type LIMIT 1;"""
        elif types == V_VUL:
            sql = f"SELECT type FROM {V_VULFUNC} LIMIT 1;"
        elif types == P_PARAM:
            sql = f"SELECT year FROM {P_COSTS} LIMIT 1;"
        elif types == P_VUL:
            sql = f"SELECT A.hierarchy FROM {P_LIFE} as A, {P_VICTIM} as B LIMIT 1;"
        elif types == A_PARAM:
            sql = f"""
                SELECT A.farmland FROM 
                    {A_PRIFLOW} as A, {A_DUNIT} as B;
            """
        elif types == A_VUL:
            sql = f"""
                SELECT A.farmland FROM {A_CDAMAGE} as A, {A_FDAMAGE} as B
                WHERE A.farmland = B.farmland LIMIT 1;
            """

        if self.sqlite.excValue(sql):
            return True
        return False

    # --- Building
    def createBuildingValueTable(self, tableName, mapYear):
        valueTable = tableName + "_eval"
        columns = self.getTableHeader(tableName)
        insSt = '(CASE WHEN A.bdtyp="단독주택" THEN A.bdstr=B.description ELSE (B.description is NULL or B.description="") END)'
        sql = f"""DROP INDEX IF EXISTS 'idx_{tableName}';
                CREATE INDEX 'idx_{tableName}' ON '{tableName}'(bdtyp, bdstr);
                
                DROP TABLE IF EXISTS '{valueTable}';
                CREATE TABLE '{valueTable}' as
                SELECT {",".join(columns[:-1])}, 
                    cast(A.bd_f_area*B.costs*
                    ((100.0-CASE WHEN (100/A.persisting_period)*A.years>=80 
                        THEN 80.0 ELSE (100/A.persisting_period)*A.years END)/100) as real) as str_value
                    , cast(0 as real) as cont_value, {columns[-1]} as geometry
                FROM (
                    SELECT {",".join(columns)}, C.persisting_period,
                        CASE 
                        WHEN {mapYear}-A.use_year > C.persisting_period THEN  C.persisting_period
                        WHEN {mapYear}-A.use_year <= 0 
                            THEN substr(round(C.persisting_period/2, 0), 0, instr(round(C.persisting_period/2, 0), '.')) 
                        ELSE {mapYear}-A.use_year 
                        END as years
                    FROM '{tableName}' as A, {B_DEPRECIATION} as C
                    WHERE A.bdtyp=C.occupancy AND {insSt.replace("B.", "C.")}) as A,
                    {B_COSTS} as B
                WHERE A.bdtyp=B.occupancy AND {insSt};
                
                UPDATE '{valueTable}' as A SET 
                    cont_value=(SELECT A.str_value*csvr/100
                        FROM '{B_CSVR}' as B WHERE A.bdtyp=B.occupancy);"""
        self.sqlite.excScript(sql)

    def createBuildingSumValue(self, tableNames):
        joinValue, groupValue, sql = [""] * 3
        for table in tableNames:
            areaTbl = self.returnTempAreaListSQL(table)

            for typeCount in [2, 3]:
                if typeCount == 3:
                    joinValue = " AND A.emd_cd=C.emd_cd"
                    groupValue = ", C.emd_cd"

                sql += f"""
                    SELECT 
                        {typeCount} as type, C.part as l_admin, B.occupancy as bdtyp, count(A.bdtyp) as building_count, 
                        sum(A.str_value) as str_value, sum(A.cont_value) as cont_value, 
                        sum(A.str_value)+sum(A.cont_value) as "sum"
                    FROM '{areaTbl}' as C 
                        LEFT JOIN '{B_CSVR}' as B LEFT JOIN '{table}' as A
                    ON trim(A.bdtyp)=trim(B.occupancy) AND A.sig_cd=C.sig_cd{joinValue}
                    WHERE C.ct={typeCount}
                    GROUP BY B.occupancy, C.sig_cd{groupValue}
                    UNION"""

        sql = f"""
            DROP TABLE IF EXISTS building_summarize; 
            CREATE TABLE building_summarize as {sql[:-5]};
        """

        self.sqlite.excScript(sql)

    def getBuildingSumValue(self, tableName, typeCount, flag):
        joinValue, groupValue = "", ""
        if typeCount == 3:
            joinValue = " AND A.emd_cd=C.emd_cd"
            groupValue = ", C.emd_cd"

        if flag:  # summarize 집계
            sql = (
                "SELECT l_admin, bdtyp, building_count, str_value, cont_value, sum "
                + f"FROM building_summarize WHERE type={typeCount} ORDER BY l_admin;"
            )

        else:  # loss 집계 sum
            sql = f"""
                SELECT
                    A.l_admin, sum(A.dmg_str) as dmg_str, sum(A.dmg_cont) as dmg_cont, 
                    count(*) as all_count, sum(dmg_flag) as dmg_count
                FROM "{tableName}" as A GROUP BY A.sig_cd{groupValue.replace("C.", "A.")} ORDER BY l_admin ASC;                
            """

        return self.sqlite.excValue(sql, False)

    def buildingLossResult(self, floodTable, invenTable):
        invenGeom, types, coord, invenSrid = self.getGeometryInfo(invenTable)[1:5]
        floodGeom = self.getGeometryInfo(floodTable)[1]
        invenHeader = self.getTableHeader(invenTable)  # eval 테이블
        depthColumn = self.getTableContents(
            LAYER_FLOOD, "flood_column", f'where table_name="{floodTable[:-2]}"'
        )[0][0]
        resultTable = f"(building){floodTable[10:-2]}_result"

        sql = building_damage_calculation(
            invenHeader,
            depthColumn,
            invenGeom,
            invenTable,
            floodTable,
            floodGeom,
            resultTable,
        )
        self.sqlite.excScript(sql)
        self.recoverGeometry(resultTable, invenGeom, invenSrid, types, coord)
        return resultTable

    # --- Population
    def getPopulationSumValue(self, tableNames, typeCount, flag):
        sql = ""
        joinValue, groupValue = "", ""
        if typeCount == 3:
            joinValue = " AND A.emd_cd=B.emd_cd"
            groupValue = ", B.emd_cd"

        if flag:
            for name in tableNames:
                areaTbl = self.returnTempAreaListSQL(name)
                sql += f"""
                    SELECT 
                        B.part as l_admin, sum(A.tot_vul_n) as tot_vul_n, sum(A.tot_gen_n) as tot_gen_n, sum(A.tot_pop_n) as tot_pop_n
                    FROM '{name}' as A LEFT JOIN '{areaTbl}' as B 
                    ON A.sig_cd=B.sig_cd{joinValue} WHERE l_admin<>"" AND B.ct={typeCount} GROUP BY B.sig_cd{groupValue} UNION"""
            sql = sql[:-5]

        else:
            sql = f"""
                DROP TABLE IF EXISTS popu_sum;
                CREATE TEMP TABLE popu_sum as 
                    SELECT sig_cd, emd_cd, sum(tot_pop_n) as tot_pop_n, 
                        sum(tot_vul_n) as tot_vul_n, sum(tot_gen_n) as tot_gen_n 
                    FROM (SELECT distinct sig_cd, emd_cd, tot_pop_n, tot_gen_n, tot_vul_n FROM "{tableNames}") as B
                    GROUP BY B.sig_cd{groupValue}; 
                      
                DROP TABLE IF EXISTS popu_sum_split;
                CREATE TEMP TABLE popu_sum_split as 
                    SELECT sig_cd, emd_cd, 0 as gen_par1, 0 as gen_par2, 
                        0 as vul_par1, 0 as vul_par2, 0 as tot_par1, 0 as tot_par2, 0 as gen_loss1, 
                        0 as gen_loss2, 0 as vul_loss1, 0 as vul_loss2, 0 as vic_loss1, 0 as vic_loss2, 
                        0 as life_cost1, 0 as life_cost2, 0 as vic_cost1, 0 as vic_cost2
                    FROM "{tableNames}" GROUP BY sig_cd, emd_cd;
                      
                UPDATE popu_sum_split as A SET (gen_par1, vul_par1, tot_par1, gen_loss1, vul_loss1, vic_loss1, 
                    life_cost1, vic_cost1) = (SELECT 
                        sum(gen_par), sum(vul_par), sum(tot_par), sum(gen_loss), 
                        sum(vul_loss), sum(vic_loss), sum(life_cost), sum(vic_cost)
                    FROM '{tableNames}' as B WHERE A.sig_cd=B.sig_cd{joinValue} AND
                        depth<=3 AND l_admin<>"" GROUP BY sig_cd{groupValue.replace("B.", "")});
                      
                UPDATE popu_sum_split as A SET (gen_par2, vul_par2, tot_par2, gen_loss2, vul_loss2, vic_loss2, 
                    life_cost2, vic_cost2) = (SELECT
                        sum(gen_par), sum(vul_par), sum(tot_par), sum(gen_loss), 
                        sum(vul_loss), sum(vic_loss), sum(life_cost), sum(vic_cost)
                    FROM '{tableNames}' as B WHERE A.sig_cd=B.sig_cd{joinValue} AND
                        depth>3 AND l_admin<>"" GROUP BY sig_cd{groupValue.replace("B.", "")});
            """
            self.sqlite.excScript(sql)
            areaTbl = self.returnTempAreaListSQL(tableNames)
            sql = f"""
                SELECT 
                    B.part as l_admin, C.tot_pop_n, C.tot_vul_n, C.tot_gen_n,
                    A.gen_par1 as "gen_par (0-0.9)", A.gen_par2 as "gen_par (0.9-)", 
                    A.vul_par1 as "vul_par (0-0.9)", A.vul_par2 as "vul_par (0.9-)", 
                    ifnull(A.tot_par1, 0)+ifnull(A.tot_par2, 0) as tot_par,
                    A.gen_loss1 as "gen_loss (0-0.9)", A.gen_loss2 as "gen_loss (0.9-)",
                    A.vul_loss1 as "vul_loss (0-0.9)", A.vul_loss2 as "vul_loss (0.9-)", 
                    A.vic_loss1 as "vic_loss (0-0.9)", A.vic_loss2 as "vic_loss (0.9-)",
                    A.life_cost1 as "life_cost (0-0.9)", A.life_cost2 as "life_cost (0.9-)",
                    A.vic_cost1 as "vic_cost (0-0.9)", A.vic_cost2 as "vic_cost (0.9-)", 
                    ifnull(A.vic_cost1, 0)+ifnull(A.vic_cost2, 0)+ifnull(A.life_cost1, 0)+ifnull(A.life_cost2, 0) as tot_cost
                FROM popu_sum_split as A, '{areaTbl}' as B, popu_sum as C
                WHERE B.ct={typeCount} AND A.sig_cd=B.sig_cd{joinValue}
                    AND A.sig_cd=C.sig_cd{joinValue.replace("B.", "C.")} 
                GROUP BY A.sig_cd{groupValue.replace("B.", "A.")};
            """
        return self.sqlite.excValue(sql, False)

    def populationLossResult(self, floodTable, invenTable):
        invenGeom, types, coord, invenSrid = self.getGeometryInfo(invenTable)[1:5]
        floodGeom = self.getGeometryInfo(floodTable)[1]
        geomType = self.convertGeomType(types)
        depthColumn = self.getFloodColumnName(floodTable)
        resultTable = f"(population){floodTable[10:]}_result"

        sql = population_damage_calculation(
            floodTable,
            invenTable,
            invenGeom,
            floodGeom,
            geomType,
            depthColumn,
            resultTable,
        )

        self.sqlite.excScript(sql)
        self.recoverGeometry(resultTable, invenGeom, invenSrid, types, coord)
        return resultTable

    # --- Agriculture
    def createAgricultureValueTable(self, tableName, mapYear):
        cnt = 0
        prdcList, prftList, sumPrdc, sumPrft = [[] for _ in range(4)]
        for k, v in CROP_KINDS.items():
            if k == "fru":
                continue

            v = v.split(", ")[1]
            area = f"{k}_a" if cnt < 4 else f"upl_{k}_a"
            s = f"{area}*ifnull((SELECT input_prd_cst FROM '{A_PRIFLOW}' WHERE crops='{v}'), 0)/1000 as {k}_prdc"
            prdcList.append(s)
            prftList.append(
                s.replace("input_prd_cst", "expct_netpr").replace("prdc", "prft")
            )
            sumPrdc.append(f"{k}_prdc")
            sumPrft.append(f"{k}_prft")
            cnt += 1

        headerList = self.getTableHeader(tableName)
        resultTable = tableName + "_eval"
        sql = f"""
            DROP TABLE IF EXISTS '{resultTable}';
            CREATE TABLE '{resultTable}' as 
                SELECT {", ".join(headerList[:-1])}, {", ".join(prdcList)}, 0 as sum_prdc, 
                    {", ".join(prftList)}, 0 as sum_prft, {headerList[-1]} 
                FROM '{tableName}' as A;
                
            UPDATE '{resultTable}' as A SET (sum_prdc, sum_prft) = ({"+".join(sumPrdc)}, {"+".join(sumPrft)});
        """
        self.sqlite.excScript(sql)

    def createAgricultureSumValue(self, tableNames):
        area, prdc, prft = [""] * 3
        for i, k in enumerate(CROP_KINDS):  # 단위 변환: 10000㎡ = 1ha
            area += (
                f"sum(upl_{k}_a)/10000 as upl_{k}_a,"
                if 4 < i
                else f"sum({k}_a)/10000 as {k}_a,"
            )
            if k == "fru":
                continue
            prdc += f"sum({k}_prdc) as {k}_prdc,"
        prft = prdc.replace("prdc", "prft")[:-1]

        joinValue, groupValue, sql, indexValue = [""] * 4
        for table in tableNames:
            areaTbl = self.returnTempAreaListSQL(table)

            for typeCount in [2, 3]:
                if typeCount == 3:
                    joinValue = " AND A.emd_cd=C.emd_cd"
                    groupValue = ", C.emd_cd"

                indexValue += f"""
                    DROP INDEX IF EXISTS 'idx_{table}';
                    CREATE INDEX 'idx_{table}' ON '{table}'(sig_cd, emd_cd);
                """
                sql += f"""
                    SELECT 
                        {typeCount} as type, C.part as l_admin, {area} {prdc} {prft}
                    FROM '{areaTbl}' as C LEFT JOIN '{table}' as A
                        ON A.sig_cd=C.sig_cd{joinValue}
                    WHERE C.ct={typeCount}
                    GROUP BY C.sig_cd{groupValue}
                    UNION"""

        sql = f"""
            DROP TABLE IF EXISTS agriculture_summarize; 
            CREATE TABLE agriculture_summarize as {sql[:-5]};
        """
        self.sqlite.excScript(indexValue + sql)

    def getAgricultureSumValue(self, tableName, typeCount, flag, agriCount=0):
        if flag:  # summarize 집계
            headers = self.getTableHeader("agriculture_summarize")[1:]
            sql = f"SELECT {','.join(headers)} FROM agriculture_summarize WHERE type={typeCount} ORDER BY l_admin;"

        else:  # loss 집계 sum
            groupValue = ", A.emd_cd" if typeCount == 2 else ""
            if agriCount == 1:
                s = ""
                for k in CROP_KINDS:
                    if k != "fru":
                        s += f"sum({k}_crp_ar) as {k}_crp_ar,"

                sql = f"""
                    SELECT l_admin, {s} sum(sum_crp_ar) as sum_crp_ar, 
                        {s.replace("crp_ar", "prd_ls")} sum(sum_prd_ls) as sum_prd_ls, 
                        {s.replace("crp_ar","prf_ls")} sum(sum_prf_ls) as sum_prf_ls
                    FROM '{tableName}' as A GROUP BY A.sig_cd{groupValue} ORDER BY l_admin ASC;
                """
            else:
                s = ""
                kinds = list(CROP_KINDS.keys())[:5] + ["upl"]
                kinds.remove("fru")
                for k in kinds:
                    s += f"sum({k}_lnd_ar) as {k}_lnd_ar,"

                sql = f"""
                    SELECT l_admin, {s} {s.replace("lnd_ar", "cst_ls")[:-1]}
                    FROM '{tableName}' as A GROUP BY A.sig_cd{groupValue} ORDER BY l_admin ASC;
                """

        return self.sqlite.excValue(sql, False)

    def agricultureLossResult(self, floodTable, invenTable):
        invenGeom, types, coord, invenSrid = self.getGeometryInfo(invenTable)[1:5]
        floodGeom = self.getGeometryInfo(floodTable)[1]
        geomType = self.convertGeomType(types)
        depthColumn = self.getFloodColumnName(floodTable)
        tempResult = "agriculture_result"
        resultTable = f"(agriculture){floodTable[10:]}_result"

        invenOriHeader = self.getTableHeader(invenTable)

        sql = agriculture_damage_calculation(
            floodTable,
            invenTable,
            invenGeom,
            floodGeom,
            geomType,
            depthColumn,
            tempResult,
            invenOriHeader,
            resultTable,
        )

        self.sqlite.excScript(sql)
        self.recoverGeometry(resultTable, invenGeom, invenSrid, types, coord)
        return resultTable

    # --- Vehicle
    def createVehicleValueTable(self, tableName, mapYear):
        valueTable = tableName + "_eval"
        columns = self.getTableHeader(tableName)
        types = [
            ["PAS1", "PAS2", "PAS3", "PAS4", "PAS8", "PAS9"],
            ["PAS5", "PAS6", "PAS7", "VAN1", "TRU1", "TRU2", "TRU5", "TRU8", "TRU11"],
            ["VAN2", "VAN3", "TRU3", "TRU4", "TRU6", "TRU7", "TRU9", "TRU10"],
        ]
        calc1, calc2 = [""] * 2
        for i, type in enumerate(types):
            for t in type:
                calc1 += f"sum(CASE WHEN type='{t.upper()}' THEN car_data ELSE 0 END) as {t}_C, \n"
                calc2 += f"A.{t}_N*B.{t}_C+"
            calc2 = calc2[:-1] + f" as t{i+1}_c, \n"

        # 차량 인벤토리의 차량 대수는 김길호 박사님의 요청으로 반올림 정수로 계산 했었음. 하지만 여기에서는 소수점 값 까지 필요하기 때문에 여기서 재 계산해서 사용하는것임.
        sql = f"""
            DROP TABLE IF EXISTS temp_vehicle_calc_values;
            CREATE TEMP TABLE temp_vehicle_calc_values as
                SELECT {calc1[:-3]} FROM (
                    SELECT A.type, A.costs*B.rate/100 as car_data FROM {V_COSTS} as A, {V_SALVAGE} as B
                    WHERE A.type=B.type
                );
        
            DROP TABLE IF EXISTS '{valueTable}';
            CREATE TABLE '{valueTable}' as
                SELECT 
                    A.ogc_fid, A.sig_cd, A.emd_cd, A.l_admin, A.tot_reg_cd,
                    A.{"_N+".join(types[0])}_N as t1_n,
                    A.{"_N+".join(types[1])}_N as t2_n,
                    A.{"_N+".join(types[2])}_N as t3_n,
                    {calc2[:-3]}, A.'{columns[-1]}' as geometry
                FROM '{tableName}' as A, temp_vehicle_calc_values as B;
        """
        self.sqlite.excScript(sql)

    def createVehicleSumValue(self, tableNames):
        joinValue, groupValue, sql = [""] * 3
        for table in tableNames:
            areaTbl = self.returnTempAreaListSQL(table)

            for typeCount in [2, 3]:
                if typeCount == 3:
                    joinValue = " AND A.emd_cd=C.emd_cd"
                    groupValue = ", C.emd_cd"

                sql += f"""
                    SELECT 
                        {typeCount} as type, A.sig_cd, A.emd_cd, C.part as l_admin,
                        sum(A.t1_n) as t1_n, sum(A.t2_n) as t2_n, sum(A.t3_n) as t3_n,
                        sum(A.t1_c) as t1_c, sum(A.t2_c) as t2_c, sum(A.t3_c) as t3_c
                    FROM '{areaTbl}' as C LEFT JOIN '{table}' as A
                    ON A.sig_cd=C.sig_cd{joinValue}
                    WHERE C.ct={typeCount}
                    GROUP BY C.sig_cd{groupValue}
                    UNION"""

        sql = f"""
            DROP TABLE IF EXISTS vehicle_summarize; 
            CREATE TABLE vehicle_summarize as {sql[:-5]};
        """
        self.sqlite.excScript(sql)

    def getVehicleSumValue(self, tableNames, typeCount, flag):
        sql = ""
        joinValue, groupValue = "", ""
        if typeCount == 3:
            joinValue = " AND A.emd_cd=B.emd_cd"
            groupValue = ", B.emd_cd"

        if flag:  # Summarize
            headers = self.getTableHeader("vehicle_summarize")[3:]
            sql = f"SELECT {','.join(headers)} FROM vehicle_summarize WHERE type={typeCount} ORDER BY l_admin;"

        else:  # Loss
            areaTbl = self.returnTempAreaListSQL(tableNames)
            columns = "sum(t1_n) as t1_n, sum(t2_n) as t2_n, sum(t3_n) as t3_n"
            sql = f"""
                SELECT 
                    B.part as l_admin,
                    t1_n_{typeCount} as t1_n, t2_n_{typeCount} as t2_n, t3_n_{typeCount} t3_n,
                    {columns.replace("_n", "_n_exps")}, {columns.replace("_n","_c_exps")},
                    {columns.replace("_n", "_n_dmg")}, {columns.replace("_n", "_c_dmg")}
                FROM 
                    '{tableNames}' as A, '{areaTbl}' as B
                WHERE A.sig_cd=B.sig_cd {joinValue} AND B.ct={typeCount}
                GROUP BY A.sig_cd{groupValue.replace("B.", "A.")};
            """

        return self.sqlite.excValue(sql, False)

    def vehicleLossResult(self, floodTable, invenTable):
        invenGeom, types, coord, invenSrid = self.getGeometryInfo(invenTable)[1:5]
        floodGeom = self.getGeometryInfo(floodTable)[1]
        geomType = self.convertGeomType(types)
        depthColumn = self.getFloodColumnName(floodTable)
        resultTable = f"(vehicle){floodTable[10:]}_result"

        sql = vehicle_damage_calculation(
            floodTable,
            invenTable,
            invenGeom,
            floodGeom,
            geomType,
            depthColumn,
            resultTable,
        )

        self.sqlite.excScript(sql)
        self.recoverGeometry(resultTable, invenGeom, invenSrid, types, coord)
        return resultTable

    # --- All Loss
    # ---   Private Loss
    def insertInvenInfraSumResult(self):
        contents = self.getTableContents(LAYER_FLOOD, "table_name")
        tables = [t[0] for t in contents]

        sql = f"DELETE FROM {F_RESULT};"
        for t in tables:
            name = t[10:]
            sql += f"""
                INSERT INTO {F_RESULT} VALUES("{t}", "Damage amount", {("0,"*7)} Null);
                INSERT INTO {F_RESULT} VALUES("{t}", "Loss", {("0,"*8)[:-1]});
            
                UPDATE {F_RESULT} SET building_res = (
                    SELECT sum( ifNull(dmg_flag, 0) ) FROM "(building){name}_result"
                        WHERE bdtyp in ('단독주택', '아파트', '다세대/연립') ) 
                    WHERE type="Damage amount" and table_name="{t}";
                UPDATE {F_RESULT} SET building_non_res = (
                    SELECT sum( ifNull(dmg_flag, 0) ) FROM "(building){name}_result"
                        WHERE bdtyp not in ('단독주택', '아파트', '다세대/연립') ) 
                    WHERE type="Damage amount" and table_name="{t}";
                UPDATE {F_RESULT} SET building_res = (
                    SELECT sum( ifNull(dmg_sum, 0) ) FROM "(building){name}_result"
                        WHERE bdtyp in ('단독주택', '아파트', '다세대/연립') ) 
                    WHERE type="Loss" and table_name="{t}";
                UPDATE {F_RESULT} SET building_non_res = (
                    SELECT sum( ifNull(dmg_sum, 0) ) FROM "(building){name}_result"
                        WHERE bdtyp not in ('단독주택', '아파트', '다세대/연립') ) 
                    WHERE type="Loss" and table_name="{t}";
                
                UPDATE {F_RESULT} SET (life, victim) = (
                    SELECT sum( ifNull(gen_loss, 0) )+sum( ifNull(vul_loss, 0) ), 
                        sum( ifNull(vic_loss, 0) ) FROM "(population){name}_result") 
                    WHERE type="Damage amount" and table_name="{t}";
                UPDATE {F_RESULT} SET (life, victim) = (
                    SELECT sum( ifNull(life_cost, 0) ), sum( ifNull(vic_cost, 0) ) FROM "(population){name}_result") 
                    WHERE type="Loss" and table_name="{t}";
                
                UPDATE {F_RESULT} SET vehicle = (
                    SELECT sum( ifNull(t1_n_dmg, 0) )+sum( ifNull(t2_n_dmg, 0) )+sum( ifNull(t3_n_dmg, 0) ) 
                        FROM "(vehicle){name}_result") 
                    WHERE type="Damage amount" and table_name="{t}";
                UPDATE {F_RESULT} SET vehicle = (
                    SELECT sum( ifNull(t1_c_dmg, 0) )+sum( ifNull(t2_c_dmg, 0) )+sum( ifNull(t3_c_dmg, 0) ) 
                        FROM "(vehicle){name}_result") 
                    WHERE type="Loss" and table_name="{t}";
                
                UPDATE {F_RESULT} SET (crop, farmland) = (
                    SELECT 
                        sum( ifNull(sum_crp_ar, 0) ), sum( ifNull(pad_lnd_ar, 0) )+sum( ifNull(gre_lnd_ar, 0) )
                        +sum( ifNull(gin_lnd_ar, 0) )+sum( ifNull(upl_lnd_ar, 0) ) 
                    FROM "(agriculture){name}_result") WHERE type="Damage amount" and table_name="{t}";
                UPDATE {F_RESULT} SET (crop, farmland) = (
                    SELECT sum( ifNull(sum_prd_ls, 0) )+sum( ifNull(sum_prf_ls, 0) ), 
                        sum( ifNull(pad_cst_ls, 0) )+sum( ifNull(gre_cst_ls, 0) )+sum( ifNull(gin_cst_ls, 0) )+sum( ifNull(upl_cst_ls, 0) ) 
                    FROM "(agriculture){name}_result") WHERE type="Loss" and table_name="{t}";
                
                UPDATE {F_RESULT} SET sum = 
                    ifNull(building_res, 0) + ifNull(building_non_res, 0) + ifNull(life, 0) + ifNUll(victim, 0) + 
                    ifNull(vehicle, 0) + ifNull(crop, 0) + ifNull(farmland, 0) 
                WHERE type="Loss" and table_name="{t}";
            """
        self.sqlite.excScript(sql)

    def invenInfraSumResult(self, tableName):
        columns = self.getTableHeader(F_RESULT)[2:]
        contents = self.getTableContents(
            F_RESULT, ",".join(columns[1:]), f'WHERE table_name="{tableName}"'
        )
        return contents

    def createExtentGrid(self, content, inven, table):
        resultTbl = table + "_grid"
        sql = """
            DROP TABLE IF EXISTS extent_grid;
            CREATE TABLE extent_grid(
                gid text, 
                value integer,
                wkt text
            );
        """
        self.sqlite.excScript(sql)
        self.insertTableData("extent_grid", content)

        columns = self.getTableHeader(table, True)[1:-1]
        calCol = []
        for c in columns:
            if c[2] in ["REAL", "INT"] and c[1] != "dmg_flag":
                calCol.append(
                    ("avg" if c[1] == "depth" else "sum") + f"({c[1]}*rate) as {c[1]}"
                )
            else:
                calCol.append(c[1])

        sql = f"""
            SELECT addgeometrycolumn('extent_grid', 'geometry', 5179, 'MULTIPOLYGON', 'XY');
            UPDATE extent_grid 
            SET geometry = geomfromtext(wkt, 5179);
            
            DROP TABLE IF EXISTS 'temp_intersection';
            CREATE TEMP TABLE temp_intersection as
                SELECT  
                    A.gid, B.ogc_fid,
                    area(intersection(A.geometry, B.geometry))/area(B.geometry) as rate,
                    B.{", B.".join([c[1] for c in columns])}, A.geometry
                FROM extent_grid as a, "{table}" as b
                WHERE 
                    Intersects(B.'geometry', A.'geometry') AND
                    B.rowid in (SELECT rowid FROM SpatialIndex WHERE f_table_name='{table}' AND 
                        search_frame=A.'geometry');
            
            UPDATE temp_intersection SET rate = 1 WHERE rate is NULL;
            
            DROP TABLE IF EXISTS "{resultTbl}";
            CREATE TABLE "{resultTbl}" as 
                SELECT 
                    ogc_fid, gid, {", ".join(calCol)}, geometry
                from temp_intersection
                group by gid;        
                    
            UPDATE "{resultTbl}" as a SET (ogc_fid, l_admin, sig_cd, emd_cd) = (
                SELECT ogc_fid, l_admin, sig_cd, emd_cd 
                FROM temp_intersection as b 
                WHERE a.gid=b.gid 
                GROUP BY gid HAVING rate=max(rate)
            );
            
            -- 격자는 EPGS:5179 고정
            SELECT recovergeometrycolumn('{resultTbl}', 'geometry', 5179, 'MULTIPOLYGON', 'XY');
        """

        if inven.lower() == "population":
            sql += f"""
                UPDATE "{resultTbl}" as a SET tot_reg_cd = (
                    SELECT tot_reg_cd 
                    FROM temp_intersection as b 
                    WHERE a.gid=b.gid 
                    GROUP BY gid HAVING rate=max(rate)
                );
            
                UPDATE "{resultTbl}" as D SET (tot_pop_n, tot_vul_n, tot_gen_n) = (
                    SELECT 
                        (sum(A.tot_par)/B.tot_par) * C.tot_pop_n,
                        (sum(A.vul_par)/B.vul_par) * C.tot_vul_n,
                        (sum(A.gen_par)/B.gen_par) * C.tot_gen_n
                    FROM "{resultTbl}" as A, 
                        (SELECT 
                            sum(tot_par) as tot_par, sum(vul_par) as vul_par, sum(gen_par) as gen_par 
                            FROM "{resultTbl}"
                        ) as B, 
                        (SELECT sum(tot_pop_n) as tot_pop_n, sum(tot_vul_n) as tot_vul_n, sum(tot_gen_n) as tot_gen_n 
                            FROM (
                                SELECT sig_cd, emd_cd, tot_reg_cd, tot_pop_n, tot_gen_n, tot_vul_n
                                FROM "{table}" GROUP BY sig_cd, emd_cd, tot_reg_cd
                            )
                        ) as C
                    WHERE A.sig_cd=D.sig_cd and A.emd_cd=D.emd_cd and A.tot_reg_cd=D.tot_reg_cd
                    GROUP BY sig_cd, emd_cd, tot_reg_cd
                );
            """

        elif inven.lower() == "vehicle":
            sqlDic = {"": ", emd_cd, ogc_fid", "_2": "", "_3": ", emd_cd"}

            for k in sqlDic:
                group = list(filter(None, sqlDic[k].split(", ")))
                joinSql = "".join([f" and A.{g}=D.{g}" for g in group])
                sql += f"""
                    UPDATE "{resultTbl}" as D SET (t1_n{k}, t2_n{k}, t3_n{k}) = (
                        SELECT 
                            (sum(A.t1_n_exps)/B.t1_n_exps) * C.t1,
                            (sum(A.t2_n_exps)/B.t2_n_exps) * C.t2,
                            (sum(A.t3_n_exps)/B.t3_n_exps) * C.t3
                        FROM "{resultTbl}" as A,
                        (SELECT 
                            sum(t1_n_exps) as t1_n_exps, sum(t2_n_exps) as t2_n_exps, sum(t3_n_exps) as t3_n_exps 
                            FROM "{resultTbl}"
                        ) as B, 
                        (SELECT sum(t1_n{k}) as t1, sum(t2_n{k}) as t2, sum(t3_n{k}) as t3
                            FROM ( SELECT t1_n{k}, t2_n{k}, t3_n{k} FROM "{table}" GROUP BY sig_cd{sqlDic[k]} )
                        ) as C
                        WHERE A.sig_cd=D.sig_cd{joinSql} 
                        GROUP BY sig_cd{sqlDic[k]}
                    );
                """

        self.sqlite.excScript(sql)
        return resultTbl

    # ---   Public Loss
    def calcPublicLoss(self):
        sql = f"""
            DELETE FROM {ANNU_LOSS};
            INSERT INTO {ANNU_LOSS}(flood, frequency, private_sector) 
                SELECT F.table_name, F.frequency, R.sum 
                FROM 
                    (SELECT table_name, frequency FROM {LAYER_FLOOD}) as F,
                    (SELECT table_name, sum FROM {F_RESULT} WHERE type = 'Loss') as R 
                WHERE F.table_name = R.table_name;
                
            UPDATE {ANNU_LOSS} SET public_sector = 
                private_sector * (SELECT value FROM {PUB_LOSS});
            
            UPDATE {ANNU_LOSS} SET total = private_sector+public_sector;
        """
        self.sqlite.excScript(sql)

    # ---   Annualized loss
    def calcAnuualizedApply(self, newData):
        sl = []
        for flood, frequency in newData:
            sl.append(
                f"UPDATE {ANNU_LOSS} SET frequency = {frequency} WHERE flood = '{flood}';"
            )

        self.sqlite.excScript(" ".join(sl))

    def calcAnnualizedCalc(self):
        sql = f"""
            UPDATE {ANNU_LOSS} as A SET rank = (
                SELECT r FROM (
                    SELECT row_number() over (ORDER BY frequency) r, flood FROM {ANNU_LOSS}
                ) as B 
                WHERE A.flood = B.flood
            );
            
            UPDATE {ANNU_LOSS} as A SET (probability, average) = (
                SELECT probability, average 
                FROM (
                    SELECT 
                        flood,
                        1.0/frequency - (
                            CASE WHEN lead(frequency) over(ORDER BY rank) is NULL 
                                THEN 0 ELSE 1.0/lead(frequency) over(ORDER BY rank) END
                        ) as Probability,
                        CASE WHEN lead(total) over(ORDER BY rank) is NULL 
                        THEN total ELSE ( total+(lead(total) over(ORDER BY rank)) )/2 END as Average
                    FROM {ANNU_LOSS}
                ) as B
                WHERE A.flood = B.flood 
            );
            
            UPDATE {ANNU_LOSS} SET annual = probability * average;
            
            UPDATE {ANNU_LOSS} as A SET cumulative = (
                SELECT s FROM (
                    SELECT 
                        flood,
                        sum(annual) over(ORDER BY rank) as s
                    FROM {ANNU_LOSS}
                ) as B
                WHERE A.flood = B.flood
            );
        """
        self.sqlite.excScript(sql)
