package com.mkyong.sp;

import com.mkyong.repository.BookRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.math.BigDecimal;

@Component
public class StoredFunction {

    private static final Logger log = LoggerFactory.getLogger(StoredFunction.class);

    @Autowired
    @Qualifier("jdbcBookRepository")
    private BookRepository bookRepository;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private SimpleJdbcCall simpleJdbcCallFunction1;
    private SimpleJdbcCall simpleJdbcCallFunction2;

    // init SimpleJdbcCall
    @PostConstruct
    public void init() {

        jdbcTemplate.setResultsMapCaseInsensitive(true);

        simpleJdbcCallFunction1 = new SimpleJdbcCall(jdbcTemplate).withFunctionName("get_price_by_id");
        simpleJdbcCallFunction2 = new SimpleJdbcCall(jdbcTemplate).withFunctionName("get_database_time");
    }

    private static final String SQL_STORED_FUNCTION_1 = ""
            + " CREATE OR REPLACE FUNCTION get_price_by_id(p_id IN BOOKS.ID%TYPE) "
            + " RETURN NUMBER"
            + " IS o_price BOOKS.PRICE%TYPE;"
            + " BEGIN"
            + "  SELECT PRICE INTO o_price from BOOKS WHERE ID = p_id;"
            + "  RETURN(o_price);"
            + " END;";

    private static final String SQL_STORED_FUNCTION_2 = ""
            + " CREATE OR REPLACE FUNCTION get_database_time "
            + " RETURN VARCHAR2"
            + " IS o_date VARCHAR2(20);"
            + " BEGIN"
            + "  SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO o_date FROM dual;"
            + "  RETURN(o_date);"
            + " END;";

    public void start() {

        log.info("Creating Store Procedures and Function...");
        jdbcTemplate.execute(SQL_STORED_FUNCTION_1);
        jdbcTemplate.execute(SQL_STORED_FUNCTION_2);

        /* Test Stored Function 1 */
        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("p_id", 3L);
        BigDecimal price = simpleJdbcCallFunction1.executeFunction(BigDecimal.class, in);
        System.out.println(price);  // 37.3

        /* Test Stored Function 2 */
        String database_time = simpleJdbcCallFunction2.executeFunction(String.class);
        System.out.println(database_time); // e.g current date, 23-JUL-2019 05:08:44

    }

}