package project.batch.generic.csv;

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

import org.apache.commons.lang3.ArrayUtils;

import common.db.JdbcSource;
import common.db.jdbc.Jdbc;
import common.sql.QueryUtil;
import core.exception.PhysicalException;
import core.util.ArrayUtil;
import core.util.BooleanUtil;
import project.svc.generic.csv.extract.QueryBuilder;

/**
 * クエリビルダ
 * @author Tadashi Nakayama
 */
public final class ExtractBatchBuilder {

	/** スキーマ名 */
	private String schema;
	/** FROM句 */
	private String from;
	/** 条件句 */
	private String where;
	/** パターン名 */
	private String pattern;
	/** タイトル */
	private String[] title;
	/** 改頁項目位置 */
	private int[] pagebreak = {};

	/** パラメタ */
	private Map<String, Object> param;

	/** 条件数 */
	private int size;
	/** ヘッダフラグ */
	private boolean header;
	/** クエリビルダ */
	private QueryBuilder query;

	/** 分割フラグ */
	private boolean bunkatsu = true;

	/**
	 * スキーマ名取得
	 * @return スキーマ名
	 */
	public String getSchema() {
		return this.schema;
	}

	/**
	 * 分割確認
	 * @return 分割の場合 true を返す。
	 */
	public boolean isBunkatsu() {
		return this.bunkatsu;
	}

	/**
	 * パターン名取得
	 * @return パターン名
	 */
	public String getNmUserPattern() {
		return this.pattern;
	}

	/**
	 * タイトル取得
	 * @return タイトル
	 */
	public String[] getTitle() {
		return ArrayUtil.copyOf(this.title);
	}

	/**
	 * クエリ取得
	 * @return クエリ
	 */
	public String getQuery() {
		return this.query.build();
	}

	/**
	 * 改頁項目位置取得
	 * @return 改頁項目位置
	 */
	public int[] getPageBreakIndex() {
		return this.pagebreak.clone();
	}

	/**
	 * パラメタ設定
	 * @param val パラメタマップ
	 */
	public void setParam(final Map<String, Object> val) {
		this.param = val;
	}

	/**
	 * ビルド
	 * @return ビルドした場合 true を返す。
	 */
	public boolean build() {
		if (setHeaderInfo("project.svc.generic.csv.extract.SelectHeader")) {
			setQueryBuilder("project.svc.generic.csv.extract.SelectItem");
			setCondition("project.svc.generic.csv.extract.SelectCondition");
			return true;
		}
		return false;
	}

	/**
	 * ヘッダ情報設定
	 * @param file クエリ
	 * @return 設定した場合 true を返す。
	 */
	private boolean setHeaderInfo(final String file) {
		try (Connection conn = JdbcSource.getConnection()) {
			try (PreparedStatement psmt = QueryUtil.createStatement(
					QueryUtil.getSqlFromFile(file), this.param,
					Jdbc.wrap(conn)::readonlyStatement)) {
				try (ResultSet rs = psmt.executeQuery()) {
					if (rs.next()) {
						this.schema = rs.getString("SCHEMA_NAME");
						this.from = rs.getString("REC_SQL");
						this.where = rs.getString("REC_WHERE");
						this.pattern = rs.getString("USER_PATTERN_NAME");
						this.header = BooleanUtil.toBool(rs.getString("HEADER"));
						this.size = rs.getInt("CONDITION_COLUMN");
						return true;
					}
					return false;
				}
			}
		} catch (final SQLException ex) {
			throw new PhysicalException(ex);
		}
	}

	/**
	 * クエリビルダ設定
	 * @param file クエリ
	 */
	private void setQueryBuilder(final String file) {

		List<String> label = new ArrayList<>();
		List<String> value = new ArrayList<>();
		List<String> column = new ArrayList<>();
		List<String> group = new ArrayList<>();
		List<Integer> row = new ArrayList<>();
		List<String> order = new ArrayList<>();
		List<String> aggre = new ArrayList<>();
		List<Integer> page = new ArrayList<>();

		try (Connection conn = JdbcSource.getConnection()) {
			try (PreparedStatement psmt = QueryUtil.createStatement(
					QueryUtil.getSqlFromFile(file), this.param,
					Jdbc.wrap(conn)::readonlyStatement)) {
				try (ResultSet rs = psmt.executeQuery()) {
					while (rs.next()) {
						label.add(rs.getString("ITEM_LABEL"));
						value.add(rs.getString("ITEM_VALUE"));
						column.add(rs.getString("OUTPUT_FLG"));
						aggre.add(rs.getString("AGGREGATION_KBN"));
						group.add(rs.getString("GROUP_FLG"));
						page.add(Integer.valueOf(rs.getInt("BREAK_INDEX")));
						row.add(Integer.valueOf(rs.getInt("ORDER_SORT")));
						order.add(rs.getString("ORDER_KBN"));
					}
				}
			}
		} catch (final SQLException ex) {
			throw new PhysicalException(ex);
		}

		this.pagebreak = ArrayUtils.toPrimitive(
				ArrayUtil.shrink(page.toArray(new Integer[page.size()])));

		this.query = new QueryBuilder(this.from, this.where,
				value.toArray(new String[value.size()]), label.toArray(new String[label.size()]));
		this.query.setColumnOutput(column.toArray(new String[column.size()]));
		this.query.setGroup(group.toArray(new String[group.size()]));
		this.query.setAggregation(aggre.toArray(new String[aggre.size()]));
		this.query.setOrderSort(row.toArray(new Integer[row.size()]));
		this.query.setOrderKbn(order.toArray(new String[order.size()]));

		this.title = new String[0];
		if (this.header) {
			this.title = QueryBuilder.getTitle(column.toArray(new String[column.size()]),
					label.toArray(new String[label.size()]));
		}
	}

	/**
	 * 条件設定
	 * @param file クエリ
	 */
	private void setCondition(final String file) {
		List<String> ope = new ArrayList<>();
		List<String> cond = new ArrayList<>();
		List<String> item = new ArrayList<>();

		try (Connection conn = JdbcSource.getConnection()) {
			try (PreparedStatement psmt = QueryUtil.createStatement(
					QueryUtil.getSqlFromFile(file), this.param,
					Jdbc.wrap(conn)::readonlyStatement)) {
				try (ResultSet rs = psmt.executeQuery()) {
					while (rs.next()) {
						ope.add(rs.getString("OPERATOR"));
						cond.add(rs.getString("CONDITION"));
						item.add(rs.getString("ITEM_NAME"));
					}
				}
			}
		} catch (final SQLException ex) {
			throw new PhysicalException(ex);
		}

		this.query.setCondition(this.size, ope.toArray(new String[ope.size()]),
						cond.toArray(new String[cond.size()]),
						item.toArray(new String[item.size()]));
	}
}
