from kfrm_tool.include.setting import name_list


def population_damage_calculation(
    floodTable,  # '(floodmap){name}'
    invenTable,  # '(population)2020_merge0_eval'
    invenGeom,  # 'geometry'
    floodGeom,  # 'geometry'
    geomType,  # 'MULTIPOLYGON'
    depthColumn,  # 'depth'
    resultTable,  # '(population){name}_result'
):
    sql = ""

    # 중복된 테이블이 있으면 삭제
    clear_table = f"""
        DROP TABLE IF EXISTS '{resultTable}';
    """
    sql += clear_table

    # resultTable 테이블 생성
    create_table = f"""
        CREATE TABLE '{resultTable}' as
            SELECT
                cast(A.ogc_fid as Integer) as ogc_fid,
                cast(B.'{depthColumn}' as Real) as depth,
                cast(area(A.geometry) as Real) as imm,
                cast(A.sig_cd as Text) as sig_cd,
                cast(A.emd_cd as Text) as emd_cd,
                cast(A.l_admin as Text) as l_admin,
                cast(A.tot_reg_cd as Text) as tot_reg_cd,
                cast(A.tot_pop_n as Real) as tot_pop_n,
                cast(A.tot_vul_n as Real) as tot_vul_n,
                cast(A.tot_gen_n as Real) as tot_gen_n,
                cast(0 as Real) as gen_par,
                cast(0 as Real) as vul_par,
                cast(0 as Real) as tot_par,
                cast(0 as Real) as gen_loss,
                cast(0 as Real) as vul_loss,
                cast(0 as Real) as vic_loss,
                cast(0 as Real) as life_cost,
                cast(0 as Real) as vic_cost,
                CastTo{geomType}(
                    intersection(
                        buffer(A.'{invenGeom}', 0),
                        buffer(B.'{floodGeom}', 0)
                    )
                ) as '{invenGeom}'
            FROM
                "{invenTable}" as A, "{floodTable}" as B
            WHERE 
                Intersects(
                    B.'{floodGeom}',
                    A.'{invenGeom}'
                ) 
                AND A.rowid in (
                    SELECT
                        rowid
                    FROM
                        SpatialIndex
                    WHERE
                        f_table_name='{invenTable}'
                        AND search_frame=B.'{floodGeom}'
                );
    """
    sql += create_table

    # UPDATE 테이블

    update_table = ""

    set_imm = f"""
            UPDATE
                '{resultTable}'
            SET
                imm=area({invenGeom})/imm;
            """
    update_table += set_imm

    set_par = f"""
            UPDATE
                '{resultTable}'
            SET
                gen_par=tot_gen_n*imm,
                vul_par=tot_vul_n*imm,
                tot_par=tot_pop_n*imm;
            """
    update_table += set_par

    # case 구문 생성
    case = [f"CASE WHEN A.depth={i} then B.'{i}' ELSE " for i in range(11)]
    case = "".join(case) + " B.'11' " + ("END " * 11)

    # 사상자 수 (vul, gen)_loss 계산 => 노출위험인구 * 인명피해 발생확률 (gen, vul)
    # 이재민 수 vic_loss 계산 => 노출위첨인구 * 이재민피해 발생확률 (total)
    set_loss = f"""
            UPDATE
                '{resultTable}' as A
            SET
                gen_loss = (
                    SELECT
                        ifNull(({case})*A.gen_par, 0)
                    FROM
                        '{name_list.P_LIFE}' as B
                    WHERE
                        hierarchy="General Population"
                    COLLATE NOCASE
                ),
                vul_loss = (
                    SELECT
                        ifNull(({case})*A.vul_par, 0)
                    FROM
                        '{name_list.P_LIFE}' as B
                    WHERE
                        hierarchy="Vulnerable Population"
                    COLLATE NOCASE
                ),
                vic_loss = (
                    SELECT
                        ifNull(({case})*A.tot_par, 0)
                    FROM
                        '{name_list.P_VICTIM}' as B
                    WHERE
                        hierarchy="All population"
                    COLLATE NOCASE
                );
            """
    update_table += set_loss

    # 인명 피해액 life_cost 계산 => 사상자 수 * 인명피해 원단위
    # 이재민 피해액 vic_cost 계산 => 이재민 수 * 이재민 피해 원단위
    set_cost = f"""
            UPDATE
                '{resultTable}' as A
            SET
                (life_cost, vic_cost) =
                    (
                        SELECT
                            ifNull((A.gen_loss+A.vul_loss)*B.life_loss, 0),
                            ifNull(A.vic_loss*B.victim_loss, 0)
                        FROM
                            '{name_list.P_COSTS}' as B
                    );
        """
    update_table += set_cost

    sql += update_table

    return sql


"""

create_table

CREATE TABLE '(population)sample_asc_result'                                                  AS
SELECT cast(a.ogc_fid AS        INTEGER)                                                      AS ogc_fid,
       cast(b.'depth' AS        REAL)                                                         AS depth,
       cast(area(a.geometry) AS REAL)                                                         AS imm,
       cast(a.sig_cd AS text)                                                                 AS sig_cd,
       cast(a.emd_cd AS text)                                                                 AS emd_cd,
       cast(a.l_admin AS text)                                                                AS l_admin,
       cast(a.tot_reg_cd AS text)                                                             AS tot_reg_cd,
       cast(a.tot_pop_n AS REAL)                                                              AS tot_pop_n,
       cast(a.tot_vul_n AS REAL)                                                              AS tot_vul_n,
       cast(a.tot_gen_n AS REAL)                                                              AS tot_gen_n,
       cast(0 AS           REAL)                                                              AS gen_par,
       cast(0 AS           REAL)                                                              AS vul_par,
       cast(0 AS           REAL)                                                              AS tot_par,
       cast(0 AS           REAL)                                                              AS gen_loss,
       cast(0 AS           REAL)                                                              AS vul_loss,
       cast(0 AS           REAL)                                                              AS vic_loss,
       cast(0 AS           REAL)                                                              AS life_cost,
       cast(0 AS           REAL)                                                              AS vic_cost,
       casttomultipolygon( intersection( buffer(a.'geometry', 0), buffer(b.'geometry', 0) ) ) AS 'geometry'
FROM   "(population)2020_merge0"                                                              AS a,
       "(floodmap)sample_asc"                                                                 AS b
WHERE  intersects( b.'geometry', a.'geometry' )
AND    a.rowid IN
       (
              SELECT rowid
              FROM   spatialindex
              WHERE  f_table_name='(population)2020_merge0'
              AND    search_frame=b.'geometry' );


update_table

UPDATE '(population)sample_asc_result'
SET    imm=area(geometry)/imm;UPDATE '(population)sample_asc_result'
SET    gen_par=tot_gen_n*imm,
       vul_par=tot_vul_n*imm,
       tot_par=tot_pop_n*imm;UPDATE '(population)sample_asc_result' AS a
SET    gen_loss =
       (
              SELECT ifnull((
                     CASE
                            WHEN a.depth=0 THEN b.'0'
                            ELSE
                                   CASE
                                          WHEN a.depth=1 THEN b.'1'
                                          ELSE
                                                 CASE
                                                        WHEN a.depth=2 THEN b.'2'
                                                        ELSE
                                                               CASE
                                                                      WHEN a.depth=3 THEN b.'3'
                                                                      ELSE
                                                                             CASE
                                                                                    WHEN a.depth=4 THEN b.'4'
                                                                                    ELSE
                                                                                           CASE
                                                                                                  WHEN a.depth=5 THEN b.'5'
                                                                                                  ELSE
                                                                                                         CASE
                                                                                                                WHEN a.depth=6 THEN b.'6'
                                                                                                                ELSE
                                                                                                                       CASE
                                                                                                                              WHEN a.depth=7 THEN b.'7'
                                                                                                                              ELSE
                                                                                                                                     CASE
                                                                                                                                            WHEN a.depth=8 THEN b.'8'
                                                                                                                                            ELSE
                                                                                                                                                   CASE
                                                                                                                                                          WHEN a.depth=9 THEN b.'9'
                                                                                                                                                          ELSE
                                                                                                                                                                 CASE
                                                                                                                                                                        WHEN a.depth=10 THEN b.'10'
                                                                                                                                                                        ELSE b.'11'
                                                                                                                                                                 end
                                                                                                                                                   end
                                                                                                                                     end
                                                                                                                       end
                                                                                                         end
                                                                                           end
                                                                             end
                                                               end
                                                 end
                                   end
                     end )*a.gen_par, 0)
              FROM   'population_life_loss' AS b
              WHERE  hierarchy="General Population" COLLATE nocase ),
       vul_loss =
       (
              SELECT ifnull((
                     CASE
                            WHEN a.depth=0 THEN b.'0'
                            ELSE
                                   CASE
                                          WHEN a.depth=1 THEN b.'1'
                                          ELSE
                                                 CASE
                                                        WHEN a.depth=2 THEN b.'2'
                                                        ELSE
                                                               CASE
                                                                      WHEN a.depth=3 THEN b.'3'
                                                                      ELSE
                                                                             CASE
                                                                                    WHEN a.depth=4 THEN b.'4'
                                                                                    ELSE
                                                                                           CASE
                                                                                                  WHEN a.depth=5 THEN b.'5'
                                                                                                  ELSE
                                                                                                         CASE
                                                                                                                WHEN a.depth=6 THEN b.'6'
                                                                                                                ELSE
                                                                                                                       CASE
                                                                                                                              WHEN a.depth=7 THEN b.'7'
                                                                                                                              ELSE
                                                                                                                                     CASE
                                                                                                                                            WHEN a.depth=8 THEN b.'8'
                                                                                                                                            ELSE
                                                                                                                                                   CASE
                                                                                                                                                          WHEN a.depth=9 THEN b.'9'
                                                                                                                                                          ELSE
                                                                                                                                                                 CASE
                                                                                                                                                                        WHEN a.depth=10 THEN b.'10'
                                                                                                                                                                        ELSE b.'11'
                                                                                                                                                                 end
                                                                                                                                                   end
                                                                                                                                     end
                                                                                                                       end
                                                                                                         end
                                                                                           end
                                                                             end
                                                               end
                                                 end
                                   end
                     end )*a.vul_par, 0)
              FROM   'population_life_loss' AS b
              WHERE  hierarchy="Vulnerable Population" COLLATE nocase ),
       vic_loss =
       (
              SELECT ifnull((
                     CASE
                            WHEN a.depth=0 THEN b.'0'
                            ELSE
                                   CASE
                                          WHEN a.depth=1 THEN b.'1'
                                          ELSE
                                                 CASE
                                                        WHEN a.depth=2 THEN b.'2'
                                                        ELSE
                                                               CASE
                                                                      WHEN a.depth=3 THEN b.'3'
                                                                      ELSE
                                                                             CASE
                                                                                    WHEN a.depth=4 THEN b.'4'
                                                                                    ELSE
                                                                                           CASE
                                                                                                  WHEN a.depth=5 THEN b.'5'
                                                                                                  ELSE
                                                                                                         CASE
                                                                                                                WHEN a.depth=6 THEN b.'6'
                                                                                                                ELSE
                                                                                                                       CASE
                                                                                                                              WHEN a.depth=7 THEN b.'7'
                                                                                                                              ELSE
                                                                                                                                     CASE
                                                                                                                                            WHEN a.depth=8 THEN b.'8'
                                                                                                                                            ELSE
                                                                                                                                                   CASE
                                                                                                                                                          WHEN a.depth=9 THEN b.'9'
                                                                                                                                                          ELSE
                                                                                                                                                                 CASE
                                                                                                                                                                        WHEN a.depth=10 THEN b.'10'
                                                                                                                                                                        ELSE b.'11'
                                                                                                                                                                 end
                                                                                                                                                   end
                                                                                                                                     end
                                                                                                                       end
                                                                                                         end
                                                                                           end
                                                                             end
                                                               end
                                                 end
                                   end
                     end )*a.tot_par, 0)
              FROM   'population_victim_loss' AS b
              WHERE  hierarchy="All population" COLLATE nocase );UPDATE '(population)sample_asc_result' AS a
SET
       (
              life_cost,
              vic_cost
       )
       =
       (
              SELECT ifnull((a.gen_loss+a.vul_loss)*b.life_loss, 0),
                     ifnull(a.vic_loss*b.victim_loss, 0)
              FROM   'population_unit_cost' AS b );


"""
