package com.smth.cool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;

import com.smth.verycool.RPConfig;
import com.smth.verycool.SimpleSqlBuilder;
import com.smth.verycool.SqlStatement;

public class SecurityExchangeTool {

    private final Logger log = LoggerFactory.getLogger(getClass());

    private Map loadData(final SqlStatement coListSql) {
        DataSource ds = RPConfig.getConfig().getSqlDataSource();
        return (Map) new JdbcTemplate(ds).execute(new ConnectionCallback() {
            @Override
            public Object doInConnection(Connection con) throws SQLException,
                    DataAccessException {
                Map totals = new HashMap();
                {
                    SqlStatement sqt =
                            new SimpleSqlBuilder().
                                    line("select cs.pri_gmc, count(distinct cs.co_id) total").
                                    line("from cs").
                                    line("where cs.finish_date is null").
                                    line("group by cs.pri_gmc").
                                    line("having count(distinct cs.co_id) > 0").
                                    toStatement();
                    if (log.isDebugEnabled()) {
                        log.debug(sqt.toString());
                    }
                    PreparedStatement st = sqt.prepareStatement(con);
                    try {
                        ResultSet rs = st.executeQuery();
                        while (rs.next()) {
                            totals.put(rs.getString(1), Integer.valueOf(rs.getInt(2)));
                        }
                    } catch (SQLException e) {
                        log.warn(sqt.toString());
                        throw e;
                    } finally {
                        st.close();
                    }
                }

                SimpleSqlBuilder sqb =
                        new SimpleSqlBuilder().
                                append("select cs.pri_gmc, count(distinct cs.co_id) part").
                                line(" from").
                                subAlias("rel", coListSql).
                                line(", cs").
                                line("where cs.finish_date is null and rel.id = cs.co_id").
                                line("group by cs.pri_gmc").
                                line("union").
                                line("select -1 pri_gmc, count(*) part").
                                line(" from").
                                subAlias("rel", coListSql).
                                line(" where not exists (select 'x' from cs where cs.co_id = rel.id)");
                SqlStatement sq = sqb.toStatement();
                if (log.isDebugEnabled()) {
                    log.debug(sq.toString());
                }
                PreparedStatement st = sq.prepareStatement(con);
                try {
                    Map map = new HashMap();
                    ResultSet rs = st.executeQuery();
                    while (rs.next()) {
                        String exch = rs.getString(1);
                        Integer total = (Integer) totals.get(exch);
                        map.put(exch, new PartAndTotal(rs.getInt(2), (total != null ? total.intValue() : 0)));
                    }
                    return map;
                } catch (SQLException e) {
                    log.warn(sq.toString());
                    throw e;
                } finally {
                    st.close();
                }
            }
        });
    }

}