package sharin.sql.runner;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import sharin.sql.Sql;
import sharin.util.SqlUtils;

public class BasicSqlRunner implements SqlRunner {

    private final DataSource dataSource;

    private final ResultSetProcessor objectProcessor;

    private final ResultSetProcessor idProcessor;

    public BasicSqlRunner(DataSource dataSource) {
        this(dataSource, null);
    }

    public BasicSqlRunner(DataSource dataSource,
            ResultSetProcessor objectProcessor) {

        this(dataSource, objectProcessor, null);
    }

    public BasicSqlRunner(DataSource dataSource,
            ResultSetProcessor objectProcessor, ResultSetProcessor idProcessor) {

        this.dataSource = dataSource;
        this.objectProcessor = objectProcessor;
        this.idProcessor = idProcessor;
    }

    @SuppressWarnings("unchecked")
    public <R> R selectForObject(Sql sql) {
        return (R) selectForObject(sql, objectProcessor);
    }

    @SuppressWarnings("unchecked")
    public <R> R selectForObject(Sql sql, ResultSetProcessor processor) {
        R result = null;
        Connection conn = SqlUtils.getConnection(dataSource);

        try {
            PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
                    .getText());

            try {
                Object[] params = sql.getParams();

                if (params != null) {

                    for (int i = 0; i < params.length; i++) {
                        SqlUtils.setObject(stmt, i + 1, params[i]);
                    }
                }

                ResultSet rs = SqlUtils.executeQuery(stmt);

                try {
                    Object context = processor.prepare(rs);

                    if (SqlUtils.next(rs)) {
                        result = (R) processor.process(rs, context);
                    }

                } finally {
                    SqlUtils.close(rs);
                }

            } finally {
                SqlUtils.close(stmt);
            }

        } finally {
            SqlUtils.close(conn);
        }

        return result;
    }

    public <R> List<R> selectForList(Sql sql) {
        return selectForList(sql, objectProcessor);
    }

    public <R> List<R> selectForList(Sql sql, ResultSetProcessor processor) {
        return selectForList(sql, Integer.MAX_VALUE, 0, processor);
    }

    public <R> List<R> selectForList(Sql sql, int limit, int offset) {
        return selectForList(sql, limit, offset, objectProcessor);
    }

    @SuppressWarnings("unchecked")
    public <R> List<R> selectForList(Sql sql, int limit, int offset,
            ResultSetProcessor processor) {

        List<R> resultList = new ArrayList<R>();
        Connection conn = SqlUtils.getConnection(dataSource);

        try {
            PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
                    .getText());

            try {
                Object[] params = sql.getParams();

                if (params != null) {

                    for (int i = 0; i < params.length; i++) {
                        SqlUtils.setObject(stmt, i + 1, params[i]);
                    }
                }

                ResultSet rs = SqlUtils.executeQuery(stmt);

                try {
                    Object context = processor.prepare(rs);

                    for (int i = 0; i < offset; i++) {

                        if (!SqlUtils.next(rs)) {
                            return resultList;
                        }
                    }

                    for (int i = 0; i < limit; i++) {

                        if (!SqlUtils.next(rs)) {
                            break;
                        }

                        resultList.add((R) processor.process(rs, context));
                    }

                } finally {
                    SqlUtils.close(rs);
                }

            } finally {
                SqlUtils.close(stmt);
            }

        } finally {
            SqlUtils.close(conn);
        }

        return resultList;
    }

    public int insert(Sql sql) {
        int count = 0;
        Connection conn = SqlUtils.getConnection(dataSource);

        try {
            PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
                    .getText());

            try {
                Object[] params = sql.getParams();

                if (params != null) {

                    for (int i = 0; i < params.length; i++) {
                        SqlUtils.setObject(stmt, i + 1, params[i]);
                    }
                }

                count = SqlUtils.executeUpdate(stmt);

            } finally {
                SqlUtils.close(stmt);
            }

        } finally {
            SqlUtils.close(conn);
        }

        return count;
    }

    @SuppressWarnings("unchecked")
    public <I> I insertForId(Sql sql) {
        return (I) insertForId(sql, idProcessor);
    }

    @SuppressWarnings("unchecked")
    public <I> I insertForId(Sql sql, ResultSetProcessor processor) {
        I id = null;
        Connection conn = SqlUtils.getConnection(dataSource);

        try {
            PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
                    .getText());

            try {
                Object[] params = sql.getParams();

                if (params != null) {

                    for (int i = 0; i < params.length; i++) {
                        SqlUtils.setObject(stmt, i + 1, params[i]);
                    }
                }

                int count = SqlUtils.executeUpdate(stmt);

                if (count == 1) {
                    ResultSet rs = SqlUtils.getGeneratedKeys(stmt);

                    try {
                        Object context = processor.prepare(rs);

                        if (SqlUtils.next(rs)) {
                            id = (I) processor.process(rs, context);
                        }

                    } finally {
                        SqlUtils.close(rs);
                    }
                }

            } finally {
                SqlUtils.close(stmt);
            }

        } finally {
            SqlUtils.close(conn);
        }

        return id;
    }

    public int update(Sql sql) {
        assert sql.getText().substring(0, 6).equalsIgnoreCase("UPDATE");
        return execute(sql);
    }

    public int delete(Sql sql) {
        assert sql.getText().substring(0, 6).equalsIgnoreCase("DELETE");
        return execute(sql);
    }

    public int execute(Sql sql) {
        int count = 0;
        Connection conn = SqlUtils.getConnection(dataSource);

        try {
            PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
                    .getText());

            try {
                Object[] params = sql.getParams();

                if (params != null) {

                    for (int i = 0; i < params.length; i++) {
                        SqlUtils.setObject(stmt, i + 1, params[i]);
                    }
                }

                count = SqlUtils.executeUpdate(stmt);

            } finally {
                SqlUtils.close(stmt);
            }

        } finally {
            SqlUtils.close(conn);
        }

        return count;
    }
}
