from kfrm_tool.include.setting import name_list


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

    # 테이블 초기화
    clear_table = f"""
      DROP TABLE IF EXISTS '{resultTable}';
    """
    sql += clear_table

    # 테이블 생성
    # 테이블 속성과 값을 설정하는 부분으로, 수정할 일이 거의 없을 걸로 예상
    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(0 as Real) as t1_n_2,
          cast(0 as Real) as t2_n_2,
          cast(0 as Real) as t3_n_2,
          cast(0 as Real) as t1_n_3,
          cast(0 as Real) as t2_n_3,
          cast(0 as Real) as t3_n_3,
          cast(A.l_admin as Text) as l_admin,
          cast(A.t1_n as Real) as t1_n,
          cast(A.t2_n as Real) as t2_n,
          cast(A.t3_n as Real) as t3_n,
          cast(A.t1_n as Real) as t1_n_exps,
          cast(A.t2_n as Real) as t2_n_exps,
          cast(A.t3_n as Real) as t3_n_exps,
          cast(A.t1_c as Real) as t1_c_exps,
          cast(A.t2_c as Real) as t2_c_exps,
          cast(A.t3_c as Real) as t3_c_exps,
          cast(0 as Real) as t1_n_dmg,
          cast(0 as Real) as t2_n_dmg,
          cast(0 as Real) as t3_n_dmg,
          cast(0 as Real) as t1_c_dmg,
          cast(0 as Real) as t2_c_dmg,
          cast(0 as Real) as t3_c_dmg,
          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

    # 임시 테이블 생성
    create_temp_table = f"""
      DROP TABLE IF EXISTS 'vehicle_tn_sum';
      CREATE TEMP TABLE 'vehicle_tn_sum' as
        SELECT * FROM '{resultTable}' GROUP BY sig_cd, emd_cd, ogc_fid;
    """
    sql += create_temp_table

    # 테이블 업데이트

    # CASE문 생성
    # A는 resultTable 테이블, B는 name_list.V_VULFUNC 테이블
    case = """
       CASE
          WHEN A.depth <= 0 THEN B.'0'
          WHEN A.depth = 1 THEN B.'1'
          WHEN A.depth = 2 THEN B.'2'
          WHEN A.depth = 3 THEN B.'3'
          WHEN A.depth = 4 THEN B.'4'
          WHEN A.depth = 5 THEN B.'5'
          WHEN A.depth = 6 THEN B.'6'
          WHEN A.depth = 7 THEN B.'7'
          WHEN A.depth = 8 THEN B.'8'
          WHEN A.depth = 9 THEN B.'9'
          WHEN A.depth = 10 THEN B.'10'
          ELSE B.'11'
       END
    """

    update_table = ""

    update_table += f"""
      UPDATE
        '{resultTable}' as A
      SET
        (t1_n_2, t2_n_2, t3_n_2) = (
          SELECT
            sum(B.t1_n),
            sum(B.t2_n),
            sum(B.t3_n)
          FROM
            'vehicle_tn_sum' as B
          WHERE
            A.sig_cd=B.sig_cd
          GROUP BY B.sig_cd
        );
      """

    update_table += f"""
      UPDATE
        '{resultTable}' as A
      SET
        (t1_n_3, t2_n_3, t3_n_3) = (
          SELECT
            sum(B.t1_n),
            sum(B.t2_n),
            sum(B.t3_n)
          FROM
            'vehicle_tn_sum' as B
          WHERE
            A.sig_cd=B.sig_cd
            AND A.emd_cd=B.emd_cd
          GROUP BY
            B.sig_cd,
            B.emd_cd
        );
    """

    # 노출차량 대수 계산 => 침수구역면적 / 집계구 면적 * 유형별 차량대수
    # 노출차량 가치 계산 => 침수구역면적 / 집계구 면적 * 유형별 차량가치
    update_table += f"""
      UPDATE '{resultTable}' SET imm=area({invenGeom})/imm;

      UPDATE
        '{resultTable}'
      SET
        (t1_n_exps, t2_n_exps, t3_n_exps, t1_c_exps, t2_c_exps, t3_c_exps) =
        (t1_n_exps*imm, t2_n_exps*imm, t3_n_exps*imm, t1_c_exps*imm, t2_c_exps*imm, t3_c_exps*imm);
    """

    # 차량 피해 계산 => 유형별 차량가치 * 손상률
    update_table += f"""
      UPDATE 
        '{resultTable}' as A 
      SET 
        t1_c_dmg = (SELECT ({case})*A.t1_c_exps*10/1000
          FROM '{name_list.V_VULFUNC}' as B WHERE type="type 1"),
        t2_c_dmg = (SELECT ({case})*A.t2_c_exps*10/1000
          FROM '{name_list.V_VULFUNC}' as B WHERE type="type 2"),
        t3_c_dmg = (SELECT ({case})*A.t3_c_exps*10/1000
          FROM '{name_list.V_VULFUNC}' as B WHERE type="type 3");
    """

    # 피해차량 대수 계산 => if 손상률 > 0 then 노출차량대수 else 0
    update_table += f"""
      UPDATE '{resultTable}' SET
        t1_n_dmg = (CASE WHEN t1_c_dmg>0 THEN t1_n_exps ELSE 0 END),
        t2_n_dmg = (CASE WHEN t2_c_dmg>0 THEN t2_n_exps ELSE 0 END),
        t3_n_dmg = (CASE WHEN t3_c_dmg>0 THEN t3_n_exps ELSE 0 END);
    """
    sql += update_table

    return sql


"""

create_table
CREATE TABLE '(vehicle)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(0 AS REAL)                                                                            AS t1_n_2,
       cast(0 AS REAL)                                                                            AS t2_n_2,
       cast(0 AS REAL)                                                                            AS t3_n_2,
       cast(0 AS REAL)                                                                            AS t1_n_3,
       cast(0 AS REAL)                                                                            AS t2_n_3,
       cast(0 AS REAL)                                                                            AS t3_n_3,
       cast(a.l_admin AS text)                                                                    AS l_admin,
       cast(a.t1_n AS REAL)                                                                       AS t1_n,
       cast(a.t2_n AS REAL)                                                                       AS t2_n,
       cast(a.t3_n AS REAL)                                                                       AS t3_n,
       cast(a.t1_n AS REAL)                                                                       AS t1_n_exps,
       cast(a.t2_n AS REAL)                                                                       AS t2_n_exps,
       cast(a.t3_n AS REAL)                                                                       AS t3_n_exps,
       cast(a.t1_c AS REAL)                                                                       AS t1_c_exps,
       cast(a.t2_c AS REAL)                                                                       AS t2_c_exps,
       cast(a.t3_c AS REAL)                                                                       AS t3_c_exps,
       cast(0 AS      REAL)                                                                       AS t1_n_dmg,
       cast(0 AS      REAL)                                                                       AS t2_n_dmg,
       cast(0 AS      REAL)                                                                       AS t3_n_dmg,
       cast(0 AS      REAL)                                                                       AS t1_c_dmg,
       cast(0 AS      REAL)                                                                       AS t2_c_dmg,
       cast(0 AS      REAL)                                                                       AS t3_c_dmg,
       casttomultipolygon( intersection( buffer( a.'geometry', 0 ), buffer( b.'geometry', 0 ) ) ) AS 'geometry'
FROM   "(vehicle)2020_merge0_eval"                                                                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='(vehicle)2020_merge0_eval'
              AND    search_frame=b.'geometry' );


update_table 
UPDATE '(vehicle)sample_asc_result' AS a
SET
       (
              t1_n_2,
              t2_n_2,
              t3_n_2
       )
       =
       (
                SELECT   sum(b.t1_n),
                         sum(b.t2_n),
                         sum(b.t3_n)
                FROM     'vehicle_tn_sum' AS b
                WHERE    a.sig_cd=b.sig_cd
                GROUP BY b.sig_cd );
UPDATE '(vehicle)sample_asc_result' AS a
SET
       (
              t1_n_3,
              t2_n_3,
              t3_n_3
       )
       =
       (
                SELECT   sum(b.t1_n),
                         sum(b.t2_n),
                         sum(b.t3_n)
                FROM     'vehicle_tn_sum' AS b
                WHERE    a.sig_cd=b.sig_cd
                AND      a.emd_cd=b.emd_cd
                GROUP BY b.sig_cd,
                         b.emd_cd );UPDATE '(vehicle)sample_asc_result'
SET    imm=area(geometry)/imm;UPDATE '(vehicle)sample_asc_result'
SET
       (
              t1_n_exps,
              t2_n_exps,
              t3_n_exps,
              t1_c_exps,
              t2_c_exps,
              t3_c_exps
       )
       = (t1_n_exps*imm, t2_n_exps*imm, t3_n_exps*imm, t1_c_exps*imm, t2_c_exps*imm, t3_c_exps*imm);UPDATE '(vehicle)sample_asc_result' AS a
SET    t1_c_dmg =
       (
              SELECT (
                     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.t1_c_exps*10/1000
              FROM   'vehicle_dmg_func'   AS b
              WHERE  type="type 1"),
       t2_c_dmg =
       (
              SELECT (
                     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.t2_c_exps*10/1000
              FROM   'vehicle_dmg_func'   AS b
              WHERE  type="type 2"),
       t3_c_dmg =
       (
              SELECT (
                     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.t3_c_exps*10/1000
              FROM   'vehicle_dmg_func'   AS b
              WHERE  type="type 3");
UPDATE '(vehicle)sample_asc_result'
SET    t1_n_dmg = (
       CASE
              WHEN t1_c_dmg>0 THEN t1_n_exps
              ELSE 0
       end),
       t2_n_dmg = (
       CASE
              WHEN t2_c_dmg>0 THEN t2_n_exps
              ELSE 0
       end),
       t3_n_dmg = (
       CASE
              WHEN t3_c_dmg>0 THEN t3_n_exps
              ELSE 0
       end);


"""
