package com.ex.DAO;

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

import com.ex.Util.ConnFactory;
import com.ex.pojos.Loan;
import com.ex.pojos.MBS;

public class MBSDAO {
	
	//private static ConnFactory connFactory = ConnFactory.getConnFactory();
	
	// here is where the queries will go.
	
	private static final String MBS_SUM_AMOUNT = "update MBS Set sum_amount =(Select sum(amount) from Loan where mbs_id=?)where mbs_id=?";
	private static final String MBS_AVG_RATE ="update MBS Set avg_rate =(Select avg(rate) from Loan where mbs_id=?)where mbs_id=?";
	private static final String MBS_NUM_LOANS = "update MBS Set num_loans =(Select count(*) from Loan where mbs_id=?)where mbs_id=?";
	private static final String MBS_APPROVED = "update MBS set status='approved', approved_date=?, denial_reason = null where mbs_id=?";
	private static final String MBS_DENIED = "update MBS set status='denied', denial_reason=? where mbs_id=?";
	
	private static final String MBS_SUBMIT = "update MBS set status='pending' where mbs_id=?";
	
	private static final String MBS_NEW = "insert into MBS values(?,?,?,?,?,?,null,'pending',null)";
	private static final String MBS_UPDATE = "update MBS set SMO=?, bank_id=?";
	private static final String ALL_MBS = "select mbs_id, bank_name, sum_amount, avg_rate, num_of_loans, SMO, approved_date, status, denial_reason, b.bank_id from MBS m left join bank b on m.bank_id = b.bank_id order by mbs_id";
	private static final String ALL_MBS_WITH_STATUS = "select mbs_id, bank_name, sum_amount, avg_rate, num_of_loans, SMO, approved_date, status, denial_reason, b.bank_id from MBS m left join bank b on m.bank_id = b.bank_id  where m.status=? order by mbs_id";
	private static final String THIS_MBS = "select mbs_id, bank_name, sum_amount, avg_rate, num_of_loans, SMO, approved_date, status, denial_reason, b.bank_id from MBS m left join bank b on m.bank_id = b.bank_id  where m.mbs_id=? order by mbs_id";
	
	private static final String DENIED_MBS_SAVE = "update MBS set num_of_loans=?, sum_amount=?, avg_rate=?, SMO=? where mbs_id=?";
	private static final String DENIED_MBS_SUBMIT = "update MBS set num_of_loans=?, sum_amount=?, avg_rate=?, SMO=?, denial_reason=null, status='pending' where mbs_id=?";
	
	public static void save_denied_mbs(int num_loans, double sum, double avg, String smo, int mbs_id, String[] loans){
		try
		{
			Connection conn = ConnFactory.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(DENIED_MBS_SAVE);
			pstmt.setInt(1, num_loans);
			pstmt.setDouble(2, sum);
			pstmt.setDouble(3, avg);
			pstmt.setString(4, smo);
			pstmt.setInt(5, mbs_id);
			pstmt.executeUpdate();
			
			String sql = "update loans set mbs_id=null where mbs_id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, mbs_id);
			pstmt.executeUpdate();
			
			sql = "update loans set mbs_id=? where loan_id in (" + loans[0];
			for(int i = 1; i < loans.length; i++){
				sql += ", " + loans[i];
			}
			sql += ")";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, mbs_id);
			pstmt.executeUpdate();
			
			
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void submit_denied_mbs(int num_loans, double sum, double avg, String smo, int mbs_id, String[] loans){
		try
		{
			Connection conn = ConnFactory.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(DENIED_MBS_SUBMIT);
			pstmt.setInt(1, num_loans);
			pstmt.setDouble(2, sum);
			pstmt.setDouble(3, avg);
			pstmt.setString(4, smo);
			pstmt.setInt(5, mbs_id);
			pstmt.executeUpdate();
			
			String sql = "update loans set mbs_id=null where mbs_id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, mbs_id);
			pstmt.executeUpdate();
			
			sql = "update loans set mbs_id=? where loan_id in (" + loans[0];
			for(int i = 1; i < loans.length; i++){
				sql += ", " + loans[i];
			}
			sql += ")";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, mbs_id);
			pstmt.executeUpdate();
			
			
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static List<MBS> AllMBS()
	{
		List<MBS> mbs = new ArrayList<MBS>();
		try(Connection conn = ConnFactory.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(ALL_MBS);)
		{
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				MBS m = new MBS();
				m.setMbs_id(rs.getInt(1));
				m.setBank_name(rs.getString(2));
				m.setSum_amount(rs.getDouble(3));
				m.setAvg_rate(rs.getDouble(4));
				m.setNum_loans(rs.getInt(5));
				m.setSMO(rs.getString(6));
				m.setApproved_date(rs.getDate(7));
				m.setStatus(rs.getString(8));
				m.setDenial_reason(rs.getString(9));
				m.setBank_id(rs.getInt(10));
				mbs.add(m);
				
			}
			
			pstmt.close();
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return mbs;			
	}
	
	
	public static List<MBS> StatusMBS(String status)
	{
		List<MBS> mbs = new ArrayList<MBS>();
		try(Connection conn = ConnFactory.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(ALL_MBS_WITH_STATUS);)
		{
			pstmt.setString(1, status);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				MBS m = new MBS();
				m.setMbs_id(rs.getInt(1));
				m.setBank_name(rs.getString(2));
				m.setSum_amount(rs.getInt(3));
				m.setAvg_rate(rs.getInt(4));
				m.setNum_loans(rs.getInt(5));
				m.setSMO(rs.getString(6));
				m.setApproved_date(rs.getDate(7));
				m.setStatus(rs.getString(8));
				m.setDenial_reason(rs.getString(9));
				m.setBank_id(rs.getInt(10));
				mbs.add(m);
			}
			
			pstmt.close();
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return mbs;			
	}
	
	public static List<MBS> BankStatusMBS(int bank_id, String status)
	{
		String sql = "select mbs_id, bank_name, sum_amount, avg_rate, num_of_loans, SMO, approved_date,"
				+ " status, denial_reason, b.bank_id from MBS m left join bank b on m.bank_id = b.bank_id"
				+ "  where m.status=? AND b.bank_id=? order by mbs_id";
		List<MBS> mbs = new ArrayList<MBS>();
		
		try(Connection conn = ConnFactory.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(sql);)
		{
			pstmt.setString(1, status);
			pstmt.setInt(2, bank_id);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				MBS m = new MBS();
				m.setMbs_id(rs.getInt(1));
				m.setBank_name(rs.getString(2));
				m.setSum_amount(rs.getDouble(3));
				m.setAvg_rate(rs.getDouble(4));
				m.setNum_loans(rs.getInt(5));
				m.setSMO(rs.getString(6));
				m.setApproved_date(rs.getDate(7));
				m.setStatus(rs.getString(8));
				m.setDenial_reason(rs.getString(9));
				m.setBank_id(rs.getInt(10));
				mbs.add(m);
			}
			pstmt.close();
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return mbs;			
	}

	
	public static MBS ThisMBS(int mbs_id)
	{
		MBS m = new MBS();
		try(Connection conn = ConnFactory.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(THIS_MBS);)
		{
			pstmt.setInt(1, mbs_id);
			ResultSet rs = pstmt.executeQuery();
			
			rs.next();
				
				m.setMbs_id(rs.getInt(1));
				m.setBank_name(rs.getString(2));
				m.setSum_amount(rs.getDouble(3));
				m.setAvg_rate(rs.getDouble(4));
				m.setNum_loans(rs.getInt(5));
				m.setSMO(rs.getString(6));
				m.setApproved_date(rs.getDate(7));
				m.setStatus(rs.getString(8));
				m.setDenial_reason(rs.getString(9));
				m.setBank_id(rs.getInt(10));
			
				pstmt.close();
				conn.close();
				
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return m;			
	}

	public static void Sum(int mbs_id)
	{
		
		try(Connection conn = ConnFactory.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(MBS_SUM_AMOUNT);)
		{
			pstmt.setInt(1, mbs_id);
			pstmt.setInt(2, mbs_id);
			pstmt.executeUpdate();
			
			pstmt.close();
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
					
	}
	
	public static void Rate(int mbs_id)
	{
		
		try(Connection conn = ConnFactory.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(MBS_AVG_RATE);)
		{
			pstmt.setInt(1, mbs_id);
			pstmt.setInt(2, mbs_id);
			pstmt.executeUpdate();
			
			pstmt.close();
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
					
	}
	
	public static void NumLoans(int mbs_id)
	{
		
		try(Connection conn = ConnFactory.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(MBS_NUM_LOANS);)
		{
			pstmt.setInt(1, mbs_id);
			pstmt.setInt(2, mbs_id);
			pstmt.executeUpdate();
			
			pstmt.close();
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
					
	}
	
	public static void Approved(int mbs_id)
	{
		
		try(Connection conn = ConnFactory.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(MBS_APPROVED);)
		{
			pstmt.setDate(1, new Date(System.currentTimeMillis()));
			pstmt.setInt(2, mbs_id);
			pstmt.executeUpdate();
			
			pstmt.close();
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
					
	}
	
	public static void Denied(String denial_reason, int mbs_id)
	{
		
		try(Connection conn = ConnFactory.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(MBS_DENIED);)
		{
			pstmt.setString(1,denial_reason);
			pstmt.setInt(2, mbs_id);
			pstmt.executeUpdate();
			
			pstmt.close();
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
					
	}
	
	public static void Submit(int mbs_id)
	{
		
		try(Connection conn = ConnFactory.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(MBS_SUBMIT);)
		{
			
			pstmt.setInt(1, mbs_id);
			pstmt.executeUpdate();
			
			pstmt.close();
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
					
	}
	
	
	public static int NewMBS(int bank_id, double sum, double avg_rate, int num_loans, String smo)
	{
		int mbs_id=0;
		try
		{
			Connection conn = ConnFactory.getConnection();
			PreparedStatement pstmt = null;
			pstmt = conn.prepareStatement("select max(mbs_id) from MBS");
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			mbs_id = rs.getInt(1) + 1;
			
			pstmt = conn.prepareStatement(MBS_NEW);
			
			pstmt.setInt(1, mbs_id);
			pstmt.setInt(2, bank_id);
			pstmt.setDouble(3, sum);
			pstmt.setDouble(4, avg_rate);
			pstmt.setInt(5, num_loans);
			pstmt.setString(6, smo);
			pstmt.executeUpdate();
			
			pstmt = conn.prepareStatement("update loans set mbs_id = ? where bank_id = ? and mbs_id is null");
			pstmt.setInt(1, mbs_id);
			pstmt.setInt(2, bank_id);
			pstmt.executeUpdate();
			
			pstmt.close();
			conn.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return mbs_id;
					
	}
	public static int NewManualMBS(int bank_id, double sum, double avg_rate, int num_loans, String smo, String[] loan_id_list)
	{
		int mbs_id=0;
		String sql = "update loans set mbs_id=? where loan_id in ("+ loan_id_list[0];
		for(String str: loan_id_list){
			sql += ", " + str;
		}
		sql += ")";
		try
		{
			Connection conn = ConnFactory.getConnection();
			PreparedStatement pstmt = null;
			pstmt = conn.prepareStatement("select max(mbs_id) from MBS");
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			mbs_id = rs.getInt(1) + 1;
			
			pstmt = conn.prepareStatement(MBS_NEW);
			
			pstmt.setInt(1, mbs_id);
			pstmt.setInt(2, bank_id);
			pstmt.setDouble(3, sum);
			pstmt.setDouble(4, avg_rate);
			pstmt.setInt(5, num_loans);
			pstmt.setString(6, smo);
			pstmt.executeUpdate();
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, mbs_id);
			
			pstmt.executeUpdate();
			
			pstmt.close();
			conn.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return mbs_id;
					
	}
	
	public static void UpdateMBS( String SMO, int bank_id)
	{
		
		try(Connection conn = ConnFactory.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(MBS_UPDATE);)
		{
			pstmt.setString(1, SMO);
			pstmt.setInt(2, bank_id);
			
			pstmt.executeUpdate();
			
			pstmt.close();
			conn.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
					
	}
	
	
	
}