package project.batch.generic.csv;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;

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.BooleanUtil;
import core.util.bean.Deal;
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;

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

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

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

	/**
	 * タイトル取得
	 *
	 * @return タイトル
	 */
	public String[] getTitle() {
		return (this.title == null) ? null : this.title.clone();
	}

	/**
	 * クエリ取得
	 *
	 * @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() {
		return Deal.of(setHeaderInfo()).then(() -> {
			setQueryBuilder();
			setCondition();
		}).result();
	}

	/**
	 * ヘッダ情報設定
	 *
	 * @return 設定した場合 true を返す。
	 */
	private boolean setHeaderInfo() {
		try (
			var conn = JdbcSource.getConnection();
			var psmt = QueryUtil.createStatement(
				QueryUtil.getSqlFromFile("project.svc.generic.csv.extract.SelectHeader"),
				this.param, Jdbc.wrap(conn)::readonlyStatement);
			var rs = psmt.executeQuery()
		) {
			final var ret = rs.next();
			if (ret) {
				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 ret;
		} catch (final SQLException ex) {
			throw new PhysicalException(ex);
		}
	}

	/**
	 * クエリビルダ設定
	 */
	private void setQueryBuilder() {

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

		try (
			var conn = JdbcSource.getConnection();
			var psmt = QueryUtil.createStatement(
				QueryUtil.getSqlFromFile("project.svc.generic.csv.extract.SelectItem"),
				this.param, Jdbc.wrap(conn)::readonlyStatement);
			var 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(rs.getInt("BREAK_INDEX"));
				row.add(rs.getInt("ORDER_SORT"));
				order.add(rs.getString("ORDER_KBN"));
			}
		} catch (final SQLException ex) {
			throw new PhysicalException(ex);
		}

		this.pagebreak = toPageBreak(page);

		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 page BREAK_INDEXリスト
	 * @return 配列
	 */
	private static int[] toPageBreak(final List<Integer> page) {
		return ArrayUtils.toPrimitive(page.stream().
				filter(Objects::nonNull).toArray(Integer[]::new));
	}

	/**
	 * 条件設定
	 */
	private void setCondition() {
		final var ope = new ArrayList<String>();
		final var cond = new ArrayList<String>();
		final var item = new ArrayList<String>();

		try (
			var conn = JdbcSource.getConnection();
			var psmt = QueryUtil.createStatement(
				QueryUtil.getSqlFromFile("project.svc.generic.csv.extract.SelectCondition"),
				this.param, Jdbc.wrap(conn)::readonlyStatement);
			var 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()]));
	}
}
