from kfrm_tool.include.setting import name_list


def agriculture_damage_calculation(
    floodTable,  # '(floodmap){name}'
    invenTable,  # '(agriculture)2020_merge0_eval'
    invenGeom,  # 'geometry'
    floodGeom,  # 'geometry'
    geomType,  # 'MULTIPOLYGON'
    depthColumn,  # 'depth'
    tempResult,  # 'agriculture_result'
    invenOriHeader,  # ['ogc_fid', 'id', 'land_code', 'agr_area', 'sig_cd', 'emd_cd', 'l_admin', 'pad_a', 'fru_a', 'gre_a', 'gin_a', 'unf_a', 'upl_ses_a', 'upl_wam_a', ...]
    resultTable,  # '(agriculture){name}_result'
):
    sql = ""

    cdmgList, fdmgList, crpArList, prdLsList, prfLsList, updtList = [
        [] for _ in range(6)
    ]
    cReTblName, fReTblName = name_list.A_CDAMAGE + "_re", name_list.A_FDAMAGE + "_re"

    # 농작물 및 농경지 손상 함수 생성
    for i in range(12):
        s = f'SELECT farmland, crops, {i} as depth, "{i}" as value FROM '
        cdmgList.append(s + name_list.A_CDAMAGE)
        fdmgList.append(s.replace(", crops", "") + name_list.A_FDAMAGE)

    dicKeys = []
    for i, k in enumerate(name_list.CROP_KINDS):
        if k == "fru":
            continue
        dicKeys.append(k)
        farm, crop = name_list.CROP_KINDS[k].split(", ")
        areaColumn = (
            f"A.intersect_area"
            if 5 > i
            else f"((A.intersect_area/A.agr_area)*A.upl_{k}_a)"
        )
        crpArList.append(f"cast({areaColumn} as Real) as {k}_crp_ar")
        lstr = f"""cast(CASE WHEN A.land_code='{farm}' THEN
                        ifnull(A.intersect_area/A.agr_area*A.{k}_prdc/1000*(
                            SELECT value FROM {cReTblName} as B
                            WHERE A.depth=B.depth AND A.land_code=B.farmland AND B.crops='{crop}'
                        )/100, 0)
                    ELSE 0 END as Real) as {k}_prd_ls \n"""
        prdLsList.append(lstr)
        prfLsList.append(lstr.replace("prdc", "prft").replace("prd", "prf"))
        updtList.append(f"CASE WHEN {k}_prd_ls>0 THEN {k}_crp_ar ELSE 0 END")

    # 재계산을 위해 기존 테이블 삭제
    clear_table = f"""
        DROP TABLE IF EXISTS '{tempResult}';
        DROP TABLE IF EXISTS '{resultTable}';
        DROP TABLE IF EXISTS '{cReTblName}';
        DROP TABLE IF EXISTS '{fReTblName}';
    """
    sql += clear_table

    # 입시 테이블 생성 부분
    create_temp_table = f"""
        CREATE TEMP TABLE '{tempResult}' as
            SELECT
                A."{'", A."'.join(invenOriHeader[:-1])}",
                B."{depthColumn}" as depth,
                0 as intersect_area,
                area(A."{invenGeom}") as imm,
                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}'
                );
        CREATE TEMP TABLE
            '{cReTblName}' as {" UNION ALL ".join(cdmgList)};

        CREATE TEMP TABLE
            '{fReTblName}' as {" UNION ALL ".join(fdmgList)};
    """
    sql += create_temp_table

    update_temp_table = f"""
        UPDATE
            '{tempResult}'
        SET
            intersect_area=area(geometry),
            imm=area(geometry)/imm;
        INSERT OR IGNORE INTO
            '{name_list.A_FDAMAGE}'
        VALUES(
            '과수',
            {", ".join(["0"]*12)}
        );
    """
    sql += update_temp_table

    # 테이블 업데이트
    update_table = f"""
        DROP TABLE IF EXISTS '{resultTable}';
        CREATE TABLE '{resultTable}' as
            SELECT
                cast(A.ogc_fid as Integer) as ogc_fid, 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,
                {", ".join(crpArList)}, cast(0 as Real) as sum_crp_ar,
                cast(CASE WHEN A.land_code='논' AND C.value>0 THEN A.intersect_area ELSE 0 END as Real) as pad_lnd_ar,
                cast(CASE WHEN A.land_code='시설' AND C.value>0 THEN A.intersect_area ELSE 0 END as Real) as gre_lnd_ar,
                cast(CASE WHEN A.land_code='인삼' AND C.value>0 THEN A.intersect_area ELSE 0 END as Real) as gin_lnd_ar,
                cast(CASE WHEN A.land_code='비경지' AND C.value>0 THEN A.intersect_area ELSE 0 END as Real) as unf_lnd_ar,
                cast(CASE WHEN A.land_code='밭' AND C.value>0 THEN A.intersect_area ELSE 0 END as Real) as upl_lnd_ar,
                {", ".join(prdLsList)}, cast(0 as Real) as sum_prd_ls,
                {", ".join(prfLsList)}, cast(0 as Real) as sum_prf_ls,
                cast(CASE WHEN A.land_code='논' AND C.value>0 THEN A.intersect_area*(D.unit/1000)*C.value/100 ELSE 0 END as Real) as pad_cst_ls,
                cast(CASE WHEN A.land_code='시설' AND C.value>0 THEN A.intersect_area*(D.unit/1000)*C.value/100 ELSE 0 END as Real) as gre_cst_ls,
                cast(CASE WHEN A.land_code='인삼' AND C.value>0 THEN A.intersect_area*(D.unit/1000)*C.value/100 ELSE 0 END as Real) as gin_cst_ls,
                cast(CASE WHEN A.land_code='비경지' AND C.value>0 THEN A.intersect_area*(D.unit/1000)*C.value/100 ELSE 0 END as Real) as unf_cst_ls,
                cast(CASE WHEN A.land_code='밭' AND C.value>0 THEN A.intersect_area*(D.unit/1000)*C.value/100 ELSE 0 END as Real) as upl_cst_ls,
                A."{invenGeom}"
            FROM '{tempResult}' as A, '{fReTblName}' as C , '{name_list.A_DUNIT}' as D
            WHERE A.land_code=C.farmland AND A.depth=C.depth;

        UPDATE '{resultTable}' SET ({"_crp_ar, ".join(dicKeys)}_crp_ar)=({", ".join(updtList)});
        UPDATE '{resultTable}' SET (sum_crp_ar, sum_prd_ls, sum_prf_ls) = (
            ({"_crp_ar+".join(dicKeys)}_crp_ar),
            ({"_prd_ls+".join(dicKeys)}_prd_ls),
            ({"_prf_ls+".join(dicKeys)}_prf_ls)
        );
    """
    sql += update_table
    return sql


"""

create_temp_table

CREATE temp TABLE 'agriculture_result'
AS
  SELECT a."ogc_fid",
         a."id",
         a."land_code",
         a."agr_area",
         a."sig_cd",
         a."emd_cd",
         a."l_admin",
         a."pad_a",
         a."fru_a",
         a."gre_a",
         a."gin_a",
         a."unf_a",
         a."upl_ses_a",
         a."upl_wam_a",
         a."upl_rad_a",
         a."upl_cab_a",
         a."upl_cor_a",
         a."upl_soy_a",
         a."upl_swp_a",
         a."pad_prdc",
         a."gre_prdc",
         a."gin_prdc",
         a."unf_prdc",
         a."ses_prdc",
         a."wam_prdc",
         a."rad_prdc",
         a."cab_prdc",
         a."cor_prdc",
         a."soy_prdc",
         a."swp_prdc",
         a."sum_prdc",
         a."pad_prft",
         a."gre_prft",
         a."gin_prft",
         a."unf_prft",
         a."ses_prft",
         a."wam_prft",
         a."rad_prft",
         a."cab_prft",
         a."cor_prft",
         a."soy_prft",
         a."swp_prft",
         a."sum_prft",
         b."depth"                                                                                  AS depth,
         0                                                                                          AS intersect_area,
         area(a."geometry")                                                                         AS imm,
         casttomultipolygon( intersection( buffer( a.'geometry', 0 ), buffer( b.'geometry', 0 ) ) ) AS 'geometry'
  FROM   "(agriculture)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='(agriculture)2020_merge0_eval'
                AND    search_frame=b.'geometry' );CREATE temp TABLE 'agriculture_crop_dmg_func_re'
  AS
    SELECT farmland,
           crops,
           0   AS depth,
           "0" AS value
    FROM   agriculture_crop_dmg_func
    UNION ALL
    SELECT farmland,
           crops,
           1   AS depth,
           "1" AS value
    FROM   agriculture_crop_dmg_func
    UNION ALL
    SELECT farmland,
           crops,
           2   AS depth,
           "2" AS value
    FROM   agriculture_crop_dmg_func
    UNION ALL
    SELECT farmland,
           crops,
           3   AS depth,
           "3" AS value
    FROM   agriculture_crop_dmg_func
    UNION ALL
    SELECT farmland,
           crops,
           4   AS depth,
           "4" AS value
    FROM   agriculture_crop_dmg_func
    UNION ALL
    SELECT farmland,
           crops,
           5   AS depth,
           "5" AS value
    FROM   agriculture_crop_dmg_func
    UNION ALL
    SELECT farmland,
           crops,
           6   AS depth,
           "6" AS value
    FROM   agriculture_crop_dmg_func
    UNION ALL
    SELECT farmland,
           crops,
           7   AS depth,
           "7" AS value
    FROM   agriculture_crop_dmg_func
    UNION ALL
    SELECT farmland,
           crops,
           8   AS depth,
           "8" AS value
    FROM   agriculture_crop_dmg_func
    UNION ALL
    SELECT farmland,
           crops,
           9   AS depth,
           "9" AS value
    FROM   agriculture_crop_dmg_func
    UNION ALL
    SELECT farmland,
           crops,
           10   AS depth,
           "10" AS value
    FROM   agriculture_crop_dmg_func
    UNION ALL
    SELECT farmland,
           crops,
           11   AS depth,
           "11" AS value
    FROM   agriculture_crop_dmg_func;CREATE temp TABLE 'agriculture_frm_dmg_func_re'
    AS
      SELECT farmland,
             0   AS depth,
             "0" AS value
      FROM   agriculture_frm_dmg_func
      UNION ALL
      SELECT farmland,
             1   AS depth,
             "1" AS value
      FROM   agriculture_frm_dmg_func
      UNION ALL
      SELECT farmland,
             2   AS depth,
             "2" AS value
      FROM   agriculture_frm_dmg_func
      UNION ALL
      SELECT farmland,
             3   AS depth,
             "3" AS value
      FROM   agriculture_frm_dmg_func
      UNION ALL
      SELECT farmland,
             4   AS depth,
             "4" AS value
      FROM   agriculture_frm_dmg_func
      UNION ALL
      SELECT farmland,
             5   AS depth,
             "5" AS value
      FROM   agriculture_frm_dmg_func
      UNION ALL
      SELECT farmland,
             6   AS depth,
             "6" AS value
      FROM   agriculture_frm_dmg_func
      UNION ALL
      SELECT farmland,
             7   AS depth,
             "7" AS value
      FROM   agriculture_frm_dmg_func
      UNION ALL
      SELECT farmland,
             8   AS depth,
             "8" AS value
      FROM   agriculture_frm_dmg_func
      UNION ALL
      SELECT farmland,
             9   AS depth,
             "9" AS value
      FROM   agriculture_frm_dmg_func
      UNION ALL
      SELECT farmland,
             10   AS depth,
             "10" AS value
      FROM   agriculture_frm_dmg_func
      UNION ALL
      SELECT farmland,
             11   AS depth,
             "11" AS value
      FROM   agriculture_frm_dmg_func;


update_temp_table

UPDATE 'agriculture_result'
SET    intersect_area=area(geometry),
       imm=area(geometry)/imm;INSERT
or     IGNORE
into   'agriculture_frm_dmg_func' VALUES
       (
              '과수',
              0,
              0,
              0,
              0,
              0,
              0,
              0,
              0,
              0,
              0,
              0,
              0
       );

update_table

DROP TABLE IF EXISTS '(agriculture)sample_asc_result';CREATE TABLE '(agriculture)sample_asc_result' AS
SELECT cast(a.ogc_fid AS INTEGER)             AS ogc_fid,
       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.intersect_area AS                            REAL)         AS pad_crp_ar,
       cast(a.intersect_area AS                            REAL)         AS gre_crp_ar,
       cast(a.intersect_area AS                            REAL)         AS gin_crp_ar,
       cast(a.intersect_area AS                            REAL)         AS unf_crp_ar,
       cast(((a.intersect_area/a.agr_area)*a.upl_ses_a) AS REAL)         AS ses_crp_ar,
       cast(((a.intersect_area/a.agr_area)*a.upl_wam_a) AS REAL)         AS wam_crp_ar,
       cast(((a.intersect_area/a.agr_area)*a.upl_rad_a) AS REAL)         AS rad_crp_ar,
       cast(((a.intersect_area/a.agr_area)*a.upl_cab_a) AS REAL)         AS cab_crp_ar,
       cast(((a.intersect_area/a.agr_area)*a.upl_cor_a) AS REAL)         AS cor_crp_ar,
       cast(((a.intersect_area/a.agr_area)*a.upl_soy_a) AS REAL)         AS soy_crp_ar,
       cast(((a.intersect_area/a.agr_area)*a.upl_swp_a) AS REAL)         AS swp_crp_ar,
       cast(0 AS                                           REAL)         AS sum_crp_ar,
       cast(
       CASE
              WHEN a.land_code='논'
              AND    c.value>0 THEN a.intersect_area
              ELSE 0
       end AS REAL) AS pad_lnd_ar,
       cast(
       CASE
              WHEN a.land_code='시설'
              AND    c.value>0 THEN a.intersect_area
              ELSE 0
       end AS REAL) AS gre_lnd_ar,
       cast(
       CASE
              WHEN a.land_code='인삼'
              AND    c.value>0 THEN a.intersect_area
              ELSE 0
       end AS REAL) AS gin_lnd_ar,
       cast(
       CASE
              WHEN a.land_code='비경지'
              AND    c.value>0 THEN a.intersect_area
              ELSE 0
       end AS REAL) AS unf_lnd_ar,
       cast(
       CASE
              WHEN a.land_code='밭'
              AND    c.value>0 THEN a.intersect_area
              ELSE 0
       end AS REAL) AS upl_lnd_ar,
       cast(
       CASE
              WHEN a.land_code='논' THEN ifnull(a.intersect_area/a.agr_area*a.pad_prdc/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='논벼' )/100, 0)
              ELSE 0
       end AS REAL) AS pad_prd_ls ,
       cast(
       CASE
              WHEN a.land_code='시설' THEN ifnull(a.intersect_area/a.agr_area*a.gre_prdc/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='딸기' )/100, 0)
              ELSE 0
       end AS REAL) AS gre_prd_ls ,
       cast(
       CASE
              WHEN a.land_code='인삼' THEN ifnull(a.intersect_area/a.agr_area*a.gin_prdc/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='인삼' )/100, 0)
              ELSE 0
       end AS REAL) AS gin_prd_ls ,
       cast(
       CASE
              WHEN a.land_code='비경지' THEN ifnull(a.intersect_area/a.agr_area*a.unf_prdc/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='비경지' )/100, 0)
              ELSE 0
       end AS REAL) AS unf_prd_ls ,
       cast(
       CASE
              WHEN a.land_code='밭' THEN ifnull(a.intersect_area/a.agr_area*a.ses_prdc/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='참깨' )/100, 0)
              ELSE 0
       end AS REAL) AS ses_prd_ls ,
       cast(
       CASE
              WHEN a.land_code='밭' THEN ifnull(a.intersect_area/a.agr_area*a.wam_prdc/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='수박' )/100, 0)
              ELSE 0
       end AS REAL) AS wam_prd_ls ,
       cast(
       CASE
              WHEN a.land_code='밭' THEN ifnull(a.intersect_area/a.agr_area*a.rad_prdc/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='가을무' )/100, 0)
              ELSE 0
       end AS REAL) AS rad_prd_ls ,
       cast(
       CASE
              WHEN a.land_code='밭' THEN ifnull(a.intersect_area/a.agr_area*a.cab_prdc/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='가을배추' )/100, 0)
              ELSE 0
       end AS REAL) AS cab_prd_ls ,
       cast(
       CASE
              WHEN a.land_code='밭' THEN ifnull(a.intersect_area/a.agr_area*a.cor_prdc/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='옥수수' )/100, 0)
              ELSE 0
       end AS REAL) AS cor_prd_ls ,
       cast(
       CASE
              WHEN a.land_code='밭' THEN ifnull(a.intersect_area/a.agr_area*a.soy_prdc/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='콩' )/100, 0)
              ELSE 0
       end AS REAL) AS soy_prd_ls ,
       cast(
       CASE
              WHEN a.land_code='밭' THEN ifnull(a.intersect_area/a.agr_area*a.swp_prdc/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='고구마' )/100, 0)
              ELSE 0
       end AS    REAL) AS swp_prd_ls ,
       cast(0 AS REAL) AS sum_prd_ls,
       cast(
       CASE
              WHEN a.land_code='논' THEN ifnull(a.intersect_area/a.agr_area*a.pad_prft/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='논벼' )/100, 0)
              ELSE 0
       end AS REAL) AS pad_prf_ls ,
       cast(
       CASE
              WHEN a.land_code='시설' THEN ifnull(a.intersect_area/a.agr_area*a.gre_prft/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='딸기' )/100, 0)
              ELSE 0
       end AS REAL) AS gre_prf_ls ,
       cast(
       CASE
              WHEN a.land_code='인삼' THEN ifnull(a.intersect_area/a.agr_area*a.gin_prft/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='인삼' )/100, 0)
              ELSE 0
       end AS REAL) AS gin_prf_ls ,
       cast(
       CASE
              WHEN a.land_code='비경지' THEN ifnull(a.intersect_area/a.agr_area*a.unf_prft/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='비경지' )/100, 0)
              ELSE 0
       end AS REAL) AS unf_prf_ls ,
       cast(
       CASE
              WHEN a.land_code='밭' THEN ifnull(a.intersect_area/a.agr_area*a.ses_prft/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='참깨' )/100, 0)
              ELSE 0
       end AS REAL) AS ses_prf_ls ,
       cast(
       CASE
              WHEN a.land_code='밭' THEN ifnull(a.intersect_area/a.agr_area*a.wam_prft/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='수박' )/100, 0)
              ELSE 0
       end AS REAL) AS wam_prf_ls ,
       cast(
       CASE
              WHEN a.land_code='밭' THEN ifnull(a.intersect_area/a.agr_area*a.rad_prft/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='가을무' )/100, 0)
              ELSE 0
       end AS REAL) AS rad_prf_ls ,
       cast(
       CASE
              WHEN a.land_code='밭' THEN ifnull(a.intersect_area/a.agr_area*a.cab_prft/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='가을배추' )/100, 0)
              ELSE 0
       end AS REAL) AS cab_prf_ls ,
       cast(
       CASE
              WHEN a.land_code='밭' THEN ifnull(a.intersect_area/a.agr_area*a.cor_prft/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='옥수수' )/100, 0)
              ELSE 0
       end AS REAL) AS cor_prf_ls ,
       cast(
       CASE
              WHEN a.land_code='밭' THEN ifnull(a.intersect_area/a.agr_area*a.soy_prft/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='콩' )/100, 0)
              ELSE 0
       end AS REAL) AS soy_prf_ls ,
       cast(
       CASE
              WHEN a.land_code='밭' THEN ifnull(a.intersect_area/a.agr_area*a.swp_prft/1000*
                     (
                            SELECT value
                            FROM   agriculture_crop_dmg_func_re AS b
                            WHERE  a.depth=b.depth
                            AND    a.land_code=b.farmland
                            AND    b.crops='고구마' )/100, 0)
              ELSE 0
       end AS    REAL) AS swp_prf_ls ,
       cast(0 AS REAL) AS sum_prf_ls,
       cast(
       CASE
              WHEN a.land_code='논'
              AND    c.value>0 THEN a.intersect_area*(d.unit/1000)*c.value/100
              ELSE 0
       end AS REAL) AS pad_cst_ls,
       cast(
       CASE
              WHEN a.land_code='시설'
              AND    c.value>0 THEN a.intersect_area*(d.unit/1000)*c.value/100
              ELSE 0
       end AS REAL) AS gre_cst_ls,
       cast(
       CASE
              WHEN a.land_code='인삼'
              AND    c.value>0 THEN a.intersect_area*(d.unit/1000)*c.value/100
              ELSE 0
       end AS REAL) AS gin_cst_ls,
       cast(
       CASE
              WHEN a.land_code='비경지'
              AND    c.value>0 THEN a.intersect_area*(d.unit/1000)*c.value/100
              ELSE 0
       end AS REAL) AS unf_cst_ls,
       cast(
       CASE
              WHEN a.land_code='밭'
              AND    c.value>0 THEN a.intersect_area*(d.unit/1000)*c.value/100
              ELSE 0
       end AS REAL) AS upl_cst_ls,
       a."geometry"
FROM   'agriculture_result'          AS a,
       'agriculture_frm_dmg_func_re' AS c ,
       'agriculture_unit_cost'       AS d
WHERE  a.land_code=c.farmland
AND    a.depth=c.depth;UPDATE '(agriculture)sample_asc_result'
SET
       (
              pad_crp_ar,
              gre_crp_ar,
              gin_crp_ar,
              unf_crp_ar,
              ses_crp_ar,
              wam_crp_ar,
              rad_crp_ar,
              cab_crp_ar,
              cor_crp_ar,
              soy_crp_ar,
              swp_crp_ar
       )
       =(
       CASE
              WHEN pad_prd_ls>0 THEN pad_crp_ar
              ELSE 0
       end,
       CASE
              WHEN gre_prd_ls>0 THEN gre_crp_ar
              ELSE 0
       end,
       CASE
              WHEN gin_prd_ls>0 THEN gin_crp_ar
              ELSE 0
       end,
       CASE
              WHEN unf_prd_ls>0 THEN unf_crp_ar
              ELSE 0
       end,
       CASE
              WHEN ses_prd_ls>0 THEN ses_crp_ar
              ELSE 0
       end,
       CASE
              WHEN wam_prd_ls>0 THEN wam_crp_ar
              ELSE 0
       end,
       CASE
              WHEN rad_prd_ls>0 THEN rad_crp_ar
              ELSE 0
       end,
       CASE
              WHEN cab_prd_ls>0 THEN cab_crp_ar
              ELSE 0
       end,
       CASE
              WHEN cor_prd_ls>0 THEN cor_crp_ar
              ELSE 0
       end,
       CASE
              WHEN soy_prd_ls>0 THEN soy_crp_ar
              ELSE 0
       end,
       CASE
              WHEN swp_prd_ls>0 THEN swp_crp_ar
              ELSE 0
       end);UPDATE '(agriculture)sample_asc_result'
SET
       (
              sum_crp_ar,
              sum_prd_ls,
              sum_prf_ls
       )
       = ( (pad_crp_ar+gre_crp_ar+gin_crp_ar+unf_crp_ar+ses_crp_ar+wam_crp_ar+rad_crp_ar+cab_crp_ar+cor_crp_ar+soy_crp_ar+swp_crp_ar), (pad_prd_ls+gre_prd_ls+gin_prd_ls+unf_prd_ls+ses_prd_ls+wam_prd_ls+rad_prd_ls+cab_prd_ls+cor_prd_ls+soy_prd_ls+swp_prd_ls), (pad_prf_ls+gre_prf_ls+gin_prf_ls+unf_prf_ls+ses_prf_ls+wam_prf_ls+rad_prf_ls+cab_prf_ls+cor_prf_ls+soy_prf_ls+swp_prf_ls) );
"""
