from kfrm_tool.include.setting import name_list


def building_damage_calculation(
    invenHeader,  # ['ogc_fid', 'l_admin', 's_bdtyp', 'bdtyp', 's_bdstr', 'bdstr', 'bd_f_area', 'gro_flo_co', 'und_flo_cd', 'ra_use_year', 'use_year', 'sig_cd', 'emd_cd', 'li_cd', ...]
    depthColumn,  # 'depth'
    invenGeom,  # 'geometry'
    invenTable,  # '(building)2020_merge0_eval'
    floodTable,  # '(floodmap){name}_c'
    floodGeom,  # 'geometry'
    resultTable,  # '(building){name}_result'
) -> str:
    # depth에 따른 취약성 계수
    outVul = "CASE WHEN 0<A.inner_depth AND A.inner_depth<0.3 THEN B.'1' "
    for i in range(2, 11):
        a = round((i - 1) * 0.3, 1)
        b = round(i * 0.3, 1)
        outVul += f"WHEN {a}<=A.inner_depth AND A.inner_depth<{b} THEN B.'{i}' "
    outVul += "WHEN 3.0 <= A.inner_depth THEN B.'11' ELSE B.'0' END END"
    innerValue = outVul.replace("B.", "C.")

    sql = ""

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

    # building_result 테이블 생성
    create_table = f"""
      CREATE TEMP TABLE building_result as
        SELECT
          A.{", A.".join(invenHeader[:-1])},
          B."{depthColumn}" as depth,
          CASE WHEN B."{depthColumn}"-C.height<=0 THEN 0 ELSE B."{depthColumn}"-C.height END as inner_depth,
          CASE WHEN B."{depthColumn}"-C.height<=0 THEN 0 ELSE 1 END as dmg_flag,
          0 as f_dmg_str,
          0 as f_dmg_cont,
          0 as dmg_str,
          0 as dmg_cont,
          0 as dmg_sum,
          A.{invenGeom}
        FROM
          "{invenTable}" as A,
          "{floodTable}" as B,
          "{name_list.B_ENTRACE}" as C
        WHERE
          A.bdtyp=C.occupancy
          AND 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

    # 구조물 피해 f_dmg_str 계산 => 구조물 가치 * 손상률 / 100
    # 내용물 피해 f_dmg_cont 계산 => 내용물 가치 * 손상률 / 100
    update_f_dmg_str_cont = f"""
      UPDATE building_result as A
      SET (f_dmg_str, f_dmg_cont)=(
        SELECT
          CASE WHEN A.dmg_flag=0 THEN 0 ElSE {outVul},
          CASE WHEN A.dmg_flag=0 THEN 0 ElSE {innerValue}
        FROM
          {name_list.B_SDAMAGE} as B,
          {name_list.B_IDAMAGE} as C
        WHERE
          A.bdtyp=B.occupancy
          AND A.bdtyp=C.occupancy
          AND (
            CASE
              WHEN A.bdtyp="단독주택" THEN A.bdstr=B.description
              ELSE (B.description is NULL or B.description="")
            END
          )
        );
    """
    sql += update_f_dmg_str_cont

    # dmg_str, dmg_cont, dmg_sum 계산
    update_dmg_str_cont_sum = """
      UPDATE building_result as A
      SET
        dmg_str = ifNull(str_value*(f_dmg_str/100), 0),
        dmg_cont = ifNull(cont_value*(f_dmg_cont/100), 0),
        dmg_sum = ifNull(str_value*(f_dmg_str/100), 0) + ifNull( cont_value*(f_dmg_cont/100), 0);
    """
    sql += update_dmg_str_cont_sum

    # 최종 결과 테이블 생성
    create_result_table = f"""
      CREATE TABLE "{resultTable}" as
        SELECT
          cast(ogc_fid as Integer) as ogc_fid,
          cast(sig_cd as Text) as sig_cd,
          cast(emd_cd as Text) as emd_cd,
          cast(l_admin as Text) as l_admin,
          cast(bdtyp as Text) as bdtyp,
          cast(str_value as Real) as str_value,
          cast(cont_value as Real) as cont_value,
          cast(dmg_flag as Integer) as dmg_flag,
          cast(f_dmg_str as Real) as f_dmg_str,
          cast(f_dmg_cont as Real) as f_dmg_cont,
          cast(dmg_str as Real) as dmg_str,
          cast(dmg_cont as Real) as dmg_cont,
          cast(dmg_sum as Real) as dmg_sum,
          {invenGeom}
        FROM building_result;
    """
    sql += create_result_table

    return sql


"""
User Data를 sample_asc, 인벤토리 년도를 2020년으로 하였을 경우

create_result_table
CREATE TABLE "(building)sample_asc_result" AS
  SELECT Cast(ogc_fid AS INTEGER)  AS ogc_fid,
         Cast(sig_cd AS TEXT)      AS sig_cd,
         Cast(emd_cd AS TEXT)      AS emd_cd,
         Cast(l_admin AS TEXT)     AS l_admin,
         Cast(bdtyp AS TEXT)       AS bdtyp,
         Cast(str_value AS REAL)   AS str_value,
         Cast(cont_value AS REAL)  AS cont_value,
         Cast(dmg_flag AS INTEGER) AS dmg_flag,
         Cast(f_dmg_str AS REAL)   AS f_dmg_str,
         Cast(f_dmg_cont AS REAL)  AS f_dmg_cont,
         Cast(dmg_str AS REAL)     AS dmg_str,
         Cast(dmg_cont AS REAL)    AS dmg_cont,
         Cast(dmg_sum AS REAL)     AS dmg_sum,
         geometry
  FROM   building_result; 


create_table
CREATE temp TABLE building_result
AS
  SELECT a.ogc_fid,
         a.l_admin,
         a.s_bdtyp,
         a.bdtyp,
         a.s_bdstr,
         a.bdstr,
         a.bd_f_area,
         a.gro_flo_co,
         a.und_flo_cd,
         a.ra_use_year,
         a.use_year,
         a.sig_cd,
         a.emd_cd,
         a.li_cd,
         a.mntn_yn,
         a.lnbr_mnnm,
         a.lnbr_slno,
         a.buld_nm_dc,
         a.mat_res,
         a.str_value,
         a.cont_value,
         b."depth" AS depth,
         CASE
                WHEN b."depth"-c.height<=0 THEN 0
                ELSE b."depth"-c.height
         end AS inner_depth,
         CASE
                WHEN b."depth"-c.height<=0 THEN 0
                ELSE 1
         end AS dmg_flag,
         0   AS f_dmg_str,
         0   AS f_dmg_cont,
         0   AS dmg_str,
         0   AS dmg_cont,
         0   AS dmg_sum,
         a.geometry
  FROM   "(building)2020_merge0_eval" AS a,
         "(floodmap)sample_asc_c"     AS b,
         "building_entrance"          AS c
  WHERE  a.bdtyp=c.occupancy
  AND    intersects(b.'geometry', a.'geometry')
  AND    a.rowid IN
         (
                SELECT rowid
                FROM   spatialindex
                WHERE  f_table_name='(building)2020_merge0_eval'
                AND    search_frame=b.'geometry' );

update_dmg_str_const_sum
UPDATE building_result AS A
SET    dmg_str = Ifnull(str_value * ( f_dmg_str / 100 ), 0),
       dmg_cont = Ifnull(cont_value * ( f_dmg_cont / 100 ), 0),
       dmg_sum = Ifnull(str_value*(f_dmg_str/100), 0)
                 + Ifnull( cont_value*(f_dmg_cont/100), 0); 

update_f_dmg_str_cont
UPDATE building_result AS a
SET
       (
              f_dmg_str,
              f_dmg_cont
       )
       =
       (
              SELECT
                     CASE
                            WHEN a.dmg_flag=0 THEN 0
                            ELSE
                                   CASE
                                          WHEN 0.0<=a.inner_depth
                                          AND    a.inner_depth<0.3 THEN b.'1'
                                          WHEN 0.3<=a.inner_depth
                                          AND    a.inner_depth<0.6 THEN b.'2'
                                          WHEN 0.6<=a.inner_depth
                                          AND    a.inner_depth<0.9 THEN b.'3'
                                          WHEN 0.9<=a.inner_depth
                                          AND    a.inner_depth<1.2 THEN b.'4'
                                          WHEN 1.2<=a.inner_depth
                                          AND    a.inner_depth<1.5 THEN b.'5'
                                          WHEN 1.5<=a.inner_depth
                                          AND    a.inner_depth<1.8 THEN b.'6'
                                          WHEN 1.8<=a.inner_depth
                                          AND    a.inner_depth<2.1 THEN b.'7'
                                          WHEN 2.1<=a.inner_depth
                                          AND    a.inner_depth<2.4 THEN b.'8'
                                          WHEN 2.4<=a.inner_depth
                                          AND    a.inner_depth<2.7 THEN b.'9'
                                          WHEN 2.7<=a.inner_depth
                                          AND    a.inner_depth<3.0 THEN b.'10'
                                          WHEN 3.0<=a.inner_depth
                                          AND    a.inner_depth<3.3 THEN b.'11'
                                          ELSE b.'0'
                                   end
                     end,
                     CASE
                            WHEN a.dmg_flag=0 THEN 0
                            ELSE
                                   CASE
                                          WHEN 0.0<=a.inner_depth
                                          AND    a.inner_depth<0.3 THEN c.'1'
                                          WHEN 0.3<=a.inner_depth
                                          AND    a.inner_depth<0.6 THEN c.'2'
                                          WHEN 0.6<=a.inner_depth
                                          AND    a.inner_depth<0.9 THEN c.'3'
                                          WHEN 0.9<=a.inner_depth
                                          AND    a.inner_depth<1.2 THEN c.'4'
                                          WHEN 1.2<=a.inner_depth
                                          AND    a.inner_depth<1.5 THEN c.'5'
                                          WHEN 1.5<=a.inner_depth
                                          AND    a.inner_depth<1.8 THEN c.'6'
                                          WHEN 1.8<=a.inner_depth
                                          AND    a.inner_depth<2.1 THEN c.'7'
                                          WHEN 2.1<=a.inner_depth
                                          AND    a.inner_depth<2.4 THEN c.'8'
                                          WHEN 2.4<=a.inner_depth
                                          AND    a.inner_depth<2.7 THEN c.'9'
                                          WHEN 2.7<=a.inner_depth
                                          AND    a.inner_depth<3.0 THEN c.'10'
                                          WHEN 3.0<=a.inner_depth
                                          AND    a.inner_depth<3.3 THEN c.'11'
                                          ELSE c.'0'
                                   end
                                                    end
              FROM   building_structure_damage_rate AS b,
                     building_inner_damage_rate     AS c
              WHERE  a.bdtyp=b.occupancy
              AND    a.bdtyp=c.occupancy
              AND    (
                            CASE
                                   WHEN a.bdtyp="단독주택" THEN a.bdstr=b.description
                                   ELSE (b.description IS NULL
                                   OR     b.description="")
                            end ) );

"""
