package models.farmer;

import com.fasterxml.jackson.annotation.JsonProperty;
import helpers.DB;
import org.sql2o.Connection;

import java.util.List;

/**
 * Created by lenovo on 11/9/2015.
 */
public class FarmerField {
    @JsonProperty("field_id")
    public int fieldId;

    @JsonProperty("farmer_id")
    public int farmerId;

    @JsonProperty("desa_id")
    public int desaId;

    @JsonProperty("field_area")
    public double fieldArea;

    @JsonProperty("ownership_status")
    public String ownershipStatus;

    @JsonProperty("is_irrigated")
    public boolean isIrrigated;

    @JsonProperty("notes")
    public String notes;

    public static List<FarmerField> select (int farmerId) {
        try (Connection con = DB.sql2o.open()) {
            String sql =
                "SELECT field_id AS fieldId, farmer_id AS farmerId, location_desa_id AS desaId, area AS fieldArea, ownership_status AS ownershipStatus, " +
                "is_irrigated AS isIrrigated, notes " +
                "FROM farmer_field WHERE farmer_id = :farmerId";
            return con.createQuery(sql).addParameter("farmerId", farmerId).executeAndFetch(FarmerField.class);
        }
    }

    public static FarmerField selectOne (int fieldId) {
        try (Connection con = DB.sql2o.open()) {
            String sql =
                "SELECT field_id AS fieldId, farmer_id AS farmerId, location_desa_id AS desaId, area AS fieldArea, ownership_status AS ownershipStatus, " +
                "is_irrigated AS isIrrigated, notes " +
                "FROM farmer_field WHERE field_id = :fieldId";
            return con.createQuery(sql).addParameter("fieldId", fieldId).executeAndFetchFirst(FarmerField.class);
        }
    }

    public static int insert (FarmerField farmerField) {
        try (Connection con = DB.sql2o.open()) {
            String sql =
                    "INSERT INTO farmer_field (farmer_id, location_desa_id, area, ownership_status, is_irrigated, notes, created_at, updated_at) " +
                    "VALUES (:farmerId, :desaId, :area, :ownershipStatus, :isIrrigated, :notes, NOW(), NOW())";
            return con.createQuery(sql, true)
                    .addParameter("farmerId", farmerField.farmerId)
                    .addParameter("desaId", farmerField.desaId)
                    .addParameter("area", farmerField.fieldArea)
                    .addParameter("ownershipStatus", farmerField.ownershipStatus)
                    .addParameter("isIrrigated", farmerField.isIrrigated)
                    .addParameter("notes", farmerField.notes)
                    .executeUpdate()
                    .getKey(Integer.class);
        }
    }

    public static int update (FarmerField farmerField) {
        try (Connection con = DB.sql2o.open()) {
            String sql =
                "UPDATE farmer_field SET location_desa_id = :desaId, area = :area, ownership_status = :ownershipStatus, " +
                "is_irrigated = :isIrrigated, notes = :notes, " +
                "updated_at = CURRENT_TIMESTAMP WHERE field_id = :fieldId";
            return con.createQuery(sql)
                    .addParameter("fieldId", farmerField.fieldId)
                    .addParameter("desaId", farmerField.desaId)
                    .addParameter("area", farmerField.fieldArea)
                    .addParameter("ownershipStatus", farmerField.ownershipStatus)
                    .addParameter("isIrrigated", farmerField.isIrrigated)
                    .addParameter("notes", farmerField.notes)
                    .executeUpdate()
                    .getResult();
        }
    }

    public static int delete (int fieldId) {
        try (Connection con = DB.sql2o.open()) {
            String sql = "DELETE FROM farmer_field WHERE field_id = :fieldId";
            return con.createQuery(sql).addParameter("fieldId", fieldId).executeUpdate().getResult();
        }
    }
}

@@ -375,24 +375,271 @@
 nt desaId;%0A%0A
+    @JsonProperty(%22desa_name%22)%0A    public String desaName;%0A%0A    @JsonProperty(%22kecamatan_id%22)%0A    public int kecamatanId;%0A%0A    @JsonProperty(%22kabupaten_id%22)%0A    public int kabupatenId;%0A%0A    @JsonProperty(%22provinsi_id%22)%0A    public int provinsiId;%0A%0A
     @JsonPro
@@ -658,16 +658,16 @@
 _area%22)%0A
-
     publ
@@ -1032,32 +1032,35 @@
         %22SELECT 
+ff.
 field_id AS fiel
@@ -1056,32 +1056,35 @@
 _id AS fieldId, 
+ff.
 farmer_id AS far
@@ -1082,32 +1082,35 @@
 id AS farmerId, 
+ff.
 location_desa_id
@@ -1113,32 +1113,35 @@
 a_id AS desaId, 
+ff.
 area AS fieldAre
@@ -1135,32 +1135,60 @@
 a AS fieldArea, 
+%22 +%0A                    %22ff.
 ownership_status
@@ -1208,37 +1208,155 @@
 Status, 
-%22 +%0A                %22
+ds.name AS desaName, kc.id AS kecamatanId, %22 +%0A                    %22kb.id AS kabupatenId, pv.id AS provinsiId, %22 +%0A                    %22ff.
 is_irrig
@@ -1368,32 +1368,35 @@
 AS isIrrigated, 
+ff.
 notes %22 +%0A      
@@ -1428,34 +1428,354 @@
 eld 
-WHERE farmer_id = :farmerI
+ff, master_desa ds, master_kecamatan kc, master_kabupaten kb, master_provinsi pv %22 +%0A                %22WHERE %22 +%0A                    %22ff.farmer_id = :farmerId AND ff.location_desa_id = ds.id AND %22 +%0A                    %22ds.kecamatan_id = kc.id AND %22 +%0A                    %22kc.kabupaten_id = kb.id AND %22 +%0A                    %22kb.provinsi_id = pv.i
 d%22;%0A

add kecamatan, kabupaten, provinsi id