package admin.model;

import java.sql.Connection;
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 model.ConnectionPool;
public class SupportMod {
	
	
	public List<SupportInfo> getAllSupport() throws SQLException {
		PreparedStatement getAllsp = null;
     List<SupportInfo> result = new ArrayList<SupportInfo>();
     Connection con = new ConnectionPool().getConnection();
    
      getAllsp = con.prepareStatement("select * from support");
      ResultSet rs = getAllsp.executeQuery();
      
      while (rs.next()) {
      	
        String id = rs.getString(1);
      	String yahoo = rs.getString(2);
          String name = rs.getString(3);
          String phone = rs.getString(4);


          SupportInfo spi = new SupportInfo(id, yahoo, name, phone);
          result.add(spi);
      }

      con.close();
      return result;
  }
	
    public int recordsnumber() throws SQLException {
    	PreparedStatement rcgetAllsp = null;
    	int record = 0;
		Connection con = new ConnectionPool().getConnection();
		
			rcgetAllsp = con.prepareStatement("select count(id) from support");
	        ResultSet rs2 = rcgetAllsp.executeQuery();
	        while (rs2.next()) { 	        	
	        	record = rs2.getInt(1);
	        }
			con.close();
		return record;
	}
	
	public SupportInfo getOneSupport(String id) throws SQLException {
		PreparedStatement getone = null;
	     SupportInfo result = new SupportInfo("","","","");
	     Connection con = new ConnectionPool().getConnection();
	      getone = con.prepareStatement("select * from support where id = ?");
	      getone.setString(1, id);
	      ResultSet rs = getone.executeQuery();
	      
	      while (rs.next()) {
	      	
	        String idx = rs.getString(1);
	      	String yahoo = rs.getString(2);
	          String name = rs.getString(3);
	          String phone = rs.getString(4);


	          result = new SupportInfo(idx, yahoo, name, phone);
	      }

	      con.close();
	      return result;
	  }
	
	
	
	
	 public boolean CreateSupport(SupportInfo spi) {
		 PreparedStatement createsp = null;
			boolean result = false;

			Connection con = new ConnectionPool().getConnection();
			try {
			
				createsp = con.prepareStatement("insert into support(yahoo, name, phone) values(?, ?, ?)");
				createsp.setString(1, spi.getYahoo());
				createsp.setString(2, spi.getName());
				createsp.setString(3, spi.getPhone());
				int number = createsp.executeUpdate();
				if (number > 0) {
					result = true;
				}

				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			return result;
		}
	 
	 	public boolean UpdateSupport(String id, SupportInfo spi) {
	 		PreparedStatement update = null;
			boolean result = false;

			Connection con = new ConnectionPool().getConnection();
			try {
				
				update = con.prepareStatement("update support set yahoo = ?,name = ?,phone = ? where id = ?");
				update.setString(1, spi.getYahoo());
				update.setString(2, spi.getName());
				update.setString(3, spi.getPhone());
				update.setString(4, id);
				int number = update.executeUpdate();
				if (number > 0) {
					result = true;
				}

				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			return result;
		}
	 	
	 	public boolean DeleteSupport(String id) {
	 		PreparedStatement delsp = null;
			boolean result = false;

			Connection con = new ConnectionPool().getConnection();
			try {
				delsp = con.prepareStatement("DELETE FROM support WHERE id = ?");
				delsp.setString(1, id);
				int number = delsp.executeUpdate();
				if (number > 0) {
					result = true;
				}

				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			return result;
		}
	 	
	 	public boolean DeleteAllSupport() {
	 		PreparedStatement delallsp = null;
			boolean result = false;

			Connection con = new ConnectionPool().getConnection();
			try {
				delallsp = con.prepareStatement("DELETE FROM support");

				int number = delallsp.executeUpdate();
				if (number > 0) {
					result = true;
				}

				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			return result;
		}
	 
	 
	 public static void main(String[] args) {
//		 SupportInfo a = new SupportInfo("4","6","7","8");
		System.out.println(new SupportMod().DeleteAllSupport());
	}
}
