package json_package;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import main_package.DataBaseConnection;
import main_package.DataBaseMethodsDuplicate;

public class DatabaseMethods1 
{
	SimpleDateFormat sdfForDb=new SimpleDateFormat("yyyy-MM-dd");
	public String selectQueWithBranch(String query,int branchId)
	{
		
		if(branchId==-1)
		{
			
		}
		else
		{
			if(query.contains("where"))
			{
				query=query+" and branch_id='"+branchId+"'";
			}
			else
			{
				query=query+" where branch_id='"+branchId+"'";
			}
		}
		
		return query;
	}
	
	public String loginUser(String userid, String pwd) 
	{
		System.out.println("========== loginUser() START ==========");
		System.out.println("userid received: " + userid);
		System.out.println("password received: " + (pwd != null ? "[PROVIDED]" : "null"));

		if(userid.contains("'") || pwd.contains("'"))
		{
			System.out.println("DEBUG: userid or password contains single quote (')");
			System.out.println("DEBUG: Returning empty string");
			return "";
		}
		else if(userid!=null && !userid.trim().equalsIgnoreCase("") 
				&& pwd!=null && !pwd.trim().equalsIgnoreCase(""))
		{
			System.out.println("DEBUG: userid and password validation passed");

			try(Connection con=DataBaseConnection.JAVAConnection();)
			{
				System.out.println("DEBUG: Database connection established");

				Statement st=con.createStatement();
				System.out.println("DEBUG: Statement created");

				String query="select * from login where userid='"+userid+"'and password='"+pwd+"' and status='active' ";
				System.out.println(query);
				System.out.println("DEBUG: Login query created");
				System.out.println("DEBUG: userid = " + userid);
				System.out.println("DEBUG: Executing login query...");

				ResultSet ss=st.executeQuery(query);

				System.out.println("DEBUG: Login query executed successfully");

				if(ss.next())
				{
					System.out.println("DEBUG: Login record found");

					String type = ss.getString("type");
					System.out.println("DEBUG: User type = " + type);

					if(ss.getString("type").equals("employee"))
					{
						System.out.println("DEBUG: Employee login detected");

						//boolean check=new DataBaseMethodsDuplicate().checkUserNameDuplicacy(userid);
						boolean check=true;

						System.out.println("DEBUG: Employee check result = " + check);

						if(check==true)
						{
							System.out.println("DEBUG: Employee login successful");
							System.out.println("DEBUG: Returning = " + ss.getString("type"));

							return ss.getString("type");
						}
						else
						{
							System.out.println("DEBUG: Employee check failed");
							System.out.println("DEBUG: Returning empty string");

							return "";
						}
					}
					else if(ss.getString("type").equals("user"))
					{
						System.out.println("DEBUG: User login detected");

						System.out.println("DEBUG: Calling checkShareHolderActivation()");
						boolean check=new DataBaseMethodsDuplicate().checkShareHolderActivation(userid);

						System.out.println("DEBUG: Shareholder activation check result = " + check);

						if(check==true)
						{
							System.out.println("DEBUG: User activation successful");
							System.out.println("DEBUG: Returning = " + ss.getString("type"));

							return ss.getString("type");
						}
						else
						{
							System.out.println("DEBUG: User activation check failed");
							System.out.println("DEBUG: Returning empty string");

							return "";
						}
					}
					else if(ss.getString("type").equals("admin") || ss.getString("type").equals("masterAdmin"))
					{
						System.out.println("DEBUG: Admin/MasterAdmin login detected");
						System.out.println("DEBUG: Returning = " + ss.getString("type"));

						return ss.getString("type");
					}
					else
					{
						System.out.println("DEBUG: Unknown user type = " + ss.getString("type"));
					}
				}
				else
				{
					System.out.println("DEBUG: No active login record found for userid = " + userid);
				}
			}
			catch (Exception e)
			{
				System.out.println("========== EXCEPTION IN loginUser() ==========");
				System.out.println("DEBUG: Exception message = " + e.getMessage());
				e.printStackTrace();
				System.out.println("==============================================");
			}
		}
		else
		{
			System.out.println("DEBUG: userid or password is null/empty");
			System.out.println("DEBUG: userid = " + userid);
			System.out.println("DEBUG: password provided = " + (pwd != null && !pwd.trim().equalsIgnoreCase("")));
		}

		System.out.println("DEBUG: Reached final return");
		System.out.println("========== loginUser() END ==========");
		return "";
	}
	public int loanRequest(int branchId,String shareHolderNo, Date startDate, String loanType, String amount, String frequency,
			String terms) 
	{
		int j = 0;
		Timestamp mDate = new Timestamp(startDate.getTime());
		mDate.setHours(0);mDate.setMinutes(0);mDate.setSeconds(0);mDate.setNanos(0);
		try (Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			String query ="insert into loan_request_from_app(share_holder_no,loan_type,request_date,amount,frequency,terms,branch_id)"
			+ "values(?,?,?,?,?,?)";
			PreparedStatement st1 = conn.prepareStatement(query);
			st1.setString(1, shareHolderNo);
			st1.setString(2, loanType);
			st1.setTimestamp(3, mDate);
			st1.setString(4, amount);
			st1.setString(5, frequency);
			st1.setString(6, terms);
			st1.setInt(7, branchId);
			
			int i = st1.executeUpdate();
			if (i >= 1)
				j++;
		} 
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return j;
	}
	
	public int paymentRequest(int branchId,String employee, Date startDate, String loanType, String loanNo, String amount,
			String payMode, String savingType, String transactionId, String response, String attendance) 
	{
		if(savingType==null || savingType.equals(""))
		{
			savingType="N/A";
		}
		int j = 0;
		Timestamp mDate = new Timestamp(startDate.getTime());
		mDate.setHours(0);mDate.setMinutes(0);mDate.setSeconds(0);mDate.setNanos(0);
		try (Connection conn = DataBaseConnection.JAVAConnection();) 
		{
			String query ="insert into loan_payment_from_app(employee,loan_type,loan_no,pay_date,amount,pay_mode,saving_type,transaction_id, response, attendance,branch_id)"
			+ "values(?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement st1 = conn.prepareStatement(query);
			st1.setString(1, employee);
			st1.setString(2, loanType);
			st1.setString(3, loanNo);
			st1.setTimestamp(4, mDate);
			st1.setString(5, amount);
			st1.setString(6, payMode);
			st1.setString(7, savingType);
			st1.setString(8, transactionId);
			st1.setString(9, response);
			st1.setString(10, attendance);
			st1.setInt(11, branchId);
			
			int i = st1.executeUpdate();
			if (i >= 1)
				j++;
		} 
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return j;
	}

	public String loanCollection(int branchId,Date startDate, Date endDate) {
		String dt=sdfForDb.format(startDate);
		String et=sdfForDb.format(endDate);
		
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="select sum(amount) from loanpayment where pay_date>='"+dt+"' and pay_date<='"+et+"' and receipt_status='approved' and chequeStatus='cleared'  ";
			//System.out.println(qq);
			ResultSet rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getString("sum(amount)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return "";
	}

	public String rdfdCollection(int branchId,Date startDate, Date endDate, String string) {
		String dt=sdfForDb.format(startDate);
		String et=sdfForDb.format(endDate); 
		
		String qq="";
		
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			
			qq="select sum(amount) from payment_receipt where user_no like '"+string+"%' and date>='"+dt+"' and date<='"+et+"' and receipt_status='approved' and chequeStatus='cleared'  ";
			
			//System.out.println(qq);
			ResultSet rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				qq= rr.getString("sum(amount)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return qq;
	}

	public String savingCollection(int branchId,Date startDate, Date endDate) {
		String dt=sdfForDb.format(startDate);
		String et=sdfForDb.format(endDate);
		
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
		 	String	qq="select sum(amount) from customer_deposit where date>='"+dt+"' and date<='"+et+"' and status='approved' and chequeStatus='cleared'  ";
			ResultSet rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getString("sum(amount)");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return "";
	}

	public String penaltyCollection(int branchId,Date startDate, Date endDate) 
	{
		String dt=sdfForDb.format(startDate);
		String et=sdfForDb.format(endDate);
		
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="select sum(amount) from income_table where income_date>='"+dt+"' and  income_date<='"+et+"' and loan_no!='' ";
			//System.out.println(qq);
			ResultSet rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return rr.getString("sum(amount)");
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return "";
	}

	public int insertGpsTrackingData(int branchId,String userid, String latitude, String longitude) 
	{
		Timestamp tt=new Timestamp(new Date().getTime());
		tt.setNanos(0);
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="insert into gps_tracking(user_id, latitude, longitude, tracking_date,branch_id) values('"+userid+"','"+latitude+"','"+longitude+"','"+tt+"','"+branchId+"') ";
			int i=st.executeUpdate(qq);
			return i;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}

	public double dailyIncomeCollection(int branchId, Date startDate, Date endDate,String item) 
	{
		String dt=sdfForDb.format(startDate);
		String et=sdfForDb.format(endDate);
		
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="select sum(amount) from income_table where income_date>='"+dt+"' and  income_date<='"+et+"' and item='"+item+"' and status='active'"
					+ " and chequeStatus='cleared'  ";
			//System.out.println(qq);
			ResultSet rr=st.executeQuery(selectQueWithBranch(qq,branchId));
			if(rr.next())
			{
				return Double.parseDouble(new DecimalFormat("##.##").format(rr.getDouble("sum(amount)")));
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}

	public int brideDataInsert(String brideFhname, String briderMname,
			String brideAddress, String brideBusiness,
			String brideBusinessPlace, String brideMobileNo,
			String brideRemark, String id) 
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="update reg_table set bride_fhname='"+brideFhname+"' , bride_mname='"+briderMname+"', bride_address='"+brideAddress+"', bride_business='"+brideBusiness+"'"
					+ ", bride_business_place='"+brideBusinessPlace+"', bride_mobile_no='"+brideMobileNo+"', bride_remark='"+brideRemark+"' where id='"+id+"' ";
			System.out.println(qq);
			int i=st.executeUpdate(qq);
			return i;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}
	
	public int appLoanDataInsert(Date loanDateDt, String loanAmount,
			String terms, String emi,
			String frequency,Date firstEmiDateDt, String loanRemark,String id) 
	{
		String loanDt=sdfForDb.format(loanDateDt);
		String firstEmiDt=sdfForDb.format(firstEmiDateDt);
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="update reg_table set loan_date='"+loanDt+"' , loan_amount='"+loanAmount+"', terms='"+terms+"', emi='"+emi+"'"
					+ ", frequency='"+frequency+"', first_emi_date='"+firstEmiDt+"', loan_remark='"+loanRemark+"' where id='"+id+"' ";
			//System.out.println(qq);
			int i=st.executeUpdate(qq);
			return i;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}

	public boolean checkGpsLastEntry(String userid, String latitude, String longitude) 
	{
		try(Connection conn=DataBaseConnection.JAVAConnection())
		{
			Statement st=conn.createStatement();
			String qq="select * from gps_tracking where user_id='"+userid+"' and latitude='"+latitude+"' and longitude='"+longitude+"' order by id desc limit 1 ";
			ResultSet rr=st.executeQuery(qq);
			if(rr.next())
			{
				return true;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return false;
	}
	
	
	public int paymentRequest(int branchId,String employee, Date startDate, String loanType, String loanNo, String amount,
			String payMode, String savingType, String transactionId, String response, String attendance,String paymentProof) {
		
		 if(savingType == null || savingType.equals(""))
		    {
		        savingType = "N/A";
		    }

		    int j = 0;

		    Timestamp mDate = new Timestamp(startDate.getTime());
		    mDate.setHours(0);
		    mDate.setMinutes(0);
		    mDate.setSeconds(0);
		    mDate.setNanos(0);

		try(Connection conn = DataBaseConnection.JAVAConnection()) {
			
			String query ="insert into loan_payment_from_app("
	                + "employee,loan_type,loan_no,pay_date,amount,pay_mode,"
	                + "saving_type,transaction_id,response,attendance,branch_id,payment_proof)"
	                + " values(?,?,?,?,?,?,?,?,?,?,?,?)";
			
			PreparedStatement st1 = conn.prepareStatement(query);
			
			st1.setString(1, employee);
	        st1.setString(2, loanType);
	        st1.setString(3, loanNo);
	        st1.setTimestamp(4, mDate);
	        st1.setString(5, amount);
	        st1.setString(6, payMode);
	        st1.setString(7, savingType);
	        st1.setString(8, transactionId);
	        st1.setString(9, response);
	        st1.setString(10, attendance);
	        st1.setInt(11, branchId);
	        st1.setString(12, paymentProof);
	        
	        int i = st1.executeUpdate();
	        
	        
	        if(i >= 1)
	        {
	            j++;
	        }
			
		} catch (Exception e) {
           e.printStackTrace();
		}
		
		return j;
		
	}
	
	
	
}
