package org.oskari.statistics.user;

import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * MyBatis Mapper for the SQL table oskari_user_indicator for thematic map user indicators.
 * The database currently supports multiple years/regionsets per indicator, but these queries do not so the API
 * will change a bit in the future.
 */
public interface UserIndicatorMapper {

    // TODO: support multiple year/regionsets
    @Select("SELECT" +
            "    i.id," +
            "    i.user_id," +
            "    i.title," +
            "    i.source," +
            "    i.description," +
            "    i.published," +
            "    d.year," +
            "    d.regionset_id " +
            "FROM" +
            "    oskari_user_indicator i" +
            "    LEFT JOIN oskari_user_indicator_data d ON i.id = d.indicator_id " +
            "WHERE" +
            "    i.user_id =  #{userId} " +
            "ORDER BY i.id, d.regionset_id, d.year")
    @Results({
            @Result(property = "userId", column = "user_id"),
            @Result(property = "regionsetId", column = "regionset_id")
    })
    @ResultType(UserIndicatorDataRow.class)
    List<UserIndicatorDataRow> findByUser(long userId);

    // TODO: support multiple year/regionsets
    @Select("SELECT" +
            "    i.id," +
            "    i.user_id," +
            "    i.title," +
            "    i.source," +
            "    i.description," +
            "    i.published," +
            "    d.year," +
            "    d.regionset_id " +
            "FROM" +
            "    oskari_user_indicator i" +
            "    LEFT JOIN oskari_user_indicator_data d ON i.id = d.indicator_id " +
            "WHERE" +
            "    i.id = #{id} " +
            "ORDER BY i.id, d.regionset_id, d.year")
    @Results({
            @Result(property = "userId", column = "user_id"),
            @Result(property = "regionsetId", column = "regionset_id")
    })
    @ResultType(UserIndicatorDataRow.class)
    List<UserIndicatorDataRow> findById(long id);


    @Select("SELECT data FROM oskari_user_indicator_data" +
            " WHERE indicator_id = #{indicator} AND regionset_id = #{regionset} AND year = #{year}")
    String getData(@Param("indicator") long indicator, @Param("regionset") long regionset, @Param("year") int year);


    @Delete("delete from oskari_user_indicator where id = #{id} AND user_id = #{userId}")
    int delete(@Param("id") long id, @Param("userId") long userId);

    @Delete("delete from oskari_user_indicator where user_id = #{userId}")
    void deleteByUser(long userId);

/*
    <statement id="insert" parameterMap="UserIndicatorInsertParameter" resultClass="int">
    insert into oskari_user_indicator (
            user_id,
            title,
            source,
            description,
            published)
    values (?, ?, ?, ?, ?)
    <!-- TODO: data should be inserted into oskari_user_indicator_data table
    Should be upgraded to MyBatis!
            -->
    </statement>


    <update id="update" parameterMap="UserIndicatorUpdateParameter">
    update oskari_user_indicator set
    user_id = ?,
    title = ?,
    source = ?,
    description = ?,
    published = ?
    where id = ?
    <!-- TODO: data should be inserted into oskari_user_indicator_data table
    Should be upgraded to MyBatis!
            -->
            */
}

@@ -2607,116 +2607,359 @@
 );%0A%0A
-/*%0A
     
-%3Cstatement id=%22insert%22 parameterMap=%22UserIndicatorInsertParameter%22 resultClass=%22int%22%3E%0A    insert into
+@Insert(%22INSERT INTO oskari_user_indicator_data%22%0A            + %22 (indicator_id, regionset_id, year, data)%22%0A            + %22 VALUES (#%7Bindicator%7D, #%7Bregionset%7D, #%7Byear%7D, #%7Bdata%7D)%22)%0A    void addData(@Param(%22indicator%22) long indicator, @Param(%22regionset%22) long regionset, @Param(%22year%22) int year, @Param(%22data%22) String data);%0A%0A    @Delete(%22DELETE FROM
  osk
@@ -2976,18 +2976,24 @@
 ndicator
- (
+_data%22 +
 %0A       
@@ -3001,629 +3001,800 @@
     
-user_id,%0A            title,%0A            source,%0A            description,%0A            published)%0A    values (?, ?, ?, ?, ?)%0A    %3C!-- TODO: data should be inserted into oskari_user_indicator_data table%0A    Should be upgraded to MyBatis!%0A            --%3E%0A    %3C/statement%3E%0A%0A%0A    %3Cupdate id=%22update%22 parameterMap=%22UserIndicatorUpdateParameter%22%3E%0A    update oskari_user_indicator set%0A    user_id = ?,%0A    title = ?,%0A    source = ?,%0A    description = ?,%0A    published = ?%0A    where id = ?%0A    %3C!-- TODO: data should be inserted into oskari_user_indicator_data table%0A    Should be upgraded to MyBatis!%0A            --%3E%0A            */
+%22 WHERE indicator_id = #%7Bindicator%7D AND regionset_id = #%7Bregionset%7D AND year = #%7Byear%7D%22)%0A    void deleteData(@Param(%22indicator%22) long indicator, @Param(%22regionset%22) long regionset, @Param(%22year%22) int year);%0A%0A    @Insert(%22INSERT INTO oskari_user_indicator%22%0A            + %22 (user_id, title, source, description, published)%22%0A            + %22 VALUES (#%7BuserId%7D, #%7Btitle%7D, #%7Bsource%7D, #%7Bdescription%7D, #%7Bpublished%7D)%22)%0A    void addIndicator(UserIndicatorDataRow row);%0A%0A    @Update(%22update oskari_user_indicator set%22 +%0A            %22    title = #%7Btitle%7D,%22 +%0A            %22    source = #%7Bsource%7D,%22 +%0A            %22    description = #%7Bdescription%7D,%22 +%0A            %22    published = #%7Bpublished%7D%22 +%0A            %22    where id = #%7Bid%7D AND user_id = #%7BuserId%7D%22)%0A    void updateIndicator(UserIndicatorDataRow row);
 %0A%7D%0A

Add insert/update/delete sqls to UserIndicator MyBatis mapper