package dao;

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

import dto.AbstractUsedCar;
import dto.SmallCar;
import dto.Under1000Car;
import dto.Under1500Car;

public class UsedCarDAO {

	private static final String SELECT_ALL_SQL =
		"select id, 名前, 価格, 排気量 FROM 中古車マスタ";

	// 全件検索メソッド
	public static List<AbstractUsedCar> findAll() throws SQLException
	{
		List<AbstractUsedCar> list = new ArrayList<>();

		try
		(
			Connection con =
				DriverManager.getConnection("jdbc:oracle:thin:@xx.xx.xx.xx:1521:xxx","xxxx", "xxxx");
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(SELECT_ALL_SQL);
		)
		{
			while(rs.next())
			{
				list.add(createInstance(rs));
			}
		}

		return list;
	}

	// 指定排気量以下の中古車検索
	public static List<AbstractUsedCar> findByDisplacement(int displacement) throws SQLException
	{
		List<AbstractUsedCar> list = new ArrayList<>();

		try
		(
			Connection con =
				DriverManager.getConnection("jdbc:oracle:thin:@xx.xx.xx.xx:1521:xxx","xxxx", "xxxx");
			ResultSet rs = createStatement4Displacement(con, displacement).executeQuery();
		)
		{
			while(rs.next())
			{
				list.add(createInstance(rs));
			}
		}

		return list;
	}

	// 指定価格以下の中古車検索
	public static List<AbstractUsedCar> findByPrice(int price) throws SQLException
	{
		List<AbstractUsedCar> list = new ArrayList<>();

		try
		(
			Connection con =
				DriverManager.getConnection("jdbc:oracle:thin:@xx.xx.xx.xx:1521:xxx","xxxx", "xxxx");
			ResultSet rs = createStatement4Price(con, price).executeQuery();
		)
		{
			while(rs.next())
			{
				list.add(createInstance(rs));
			}
		}

		return list;
	}

	// 排気量検索用PreparedStatement生成
	private static PreparedStatement createStatement4Displacement(Connection con, int displacement) throws SQLException
	{
		// PreparedStatement生成
		PreparedStatement pstmt =
			con.prepareStatement("SELECT ID, 名前, 価格, 排気量 FROM 中古車マスタ WHERE 排気量 <= ?");
		// Parameterセット
		pstmt.setInt(1, displacement);

		return pstmt;
	}

	// 価格検索用PreparedStatement生成
	private static PreparedStatement createStatement4Price(Connection con, int price) throws SQLException
	{
		// PreparedStatement生成
		PreparedStatement pstmt =
			con.prepareStatement("SELECT ID, 名前, 価格, 排気量 FROM 中古車マスタ WHERE 価格 <= ?");
		// Parameterセット
		pstmt.setInt(1, price);

		return pstmt;
	}

	// ResultSetから中古車クラスのインスタンスを生成する
	private static AbstractUsedCar createInstance(ResultSet rs) throws SQLException
	{
		// ID, 名前、価格、排気量取得
		int id = rs.getInt("ID");
		String name = rs.getString("名前");
		int price = rs.getInt("価格");
		int displacement = rs.getInt("排気量");

		AbstractUsedCar car = null;

		// 排気量に応じて、生成するインスタンスを変える
		if(displacement <= 660)
		{
			car = new SmallCar(id, name, price, displacement);
		}
		else if(displacement <= 1000)
		{
			car = new Under1000Car(id, name, price, displacement);
		}
		else if(displacement <= 1500)
		{
			car = new Under1500Car(id, name, price, displacement);
		}

		return car;
	}


}