package dal;

import bll.User;
import java.io.IOException;
import java.sql.*;


/**
 *
 * @author AngelwingedDevil
 */
public class UserDB extends DBManager{
    private static Connection con;
    private static UserDB cinstance;

    private UserDB() throws IOException
    {
        super();
    }

    public static UserDB getDbInstance() throws IOException
    {
        if(cinstance==null)
        {
            cinstance=new UserDB();
        }
        return cinstance;
    }

    protected  PreparedStatement prepare(boolean b, User u) throws SQLException, IOException
    {
        con = getConnection();
        java.sql.PreparedStatement ps;
        if (b)
        {
            ps = con.prepareStatement("UPDATE `mediabooking`.`users` SET `username` = ?,`password`=?,`fname` = ?,`lname` = ?,`email` = ?,`phone1` = ?,`phone2` = ?,`address1` = ?,`address2` = ?,`zip` = ?,`type` = ?,`refid` = ? WHERE `users`.`id` =?");
        }
        else
        {
            ps = con.prepareStatement("INSERT INTO `mediabooking`.`users` (username,password,fname,lname,email,phone1,phone2,address1,address2,zip,type,refid) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
        }

        return setValues(u,ps,b);
    }

    protected  PreparedStatement setValues(User u,PreparedStatement ps,boolean b) throws SQLException
    {
        ps.setString(1, u.getUserName());
        ps.setString(2, u.getPassword());
        ps.setString(3, u.getFname());
        ps.setString(4, u.getLname());
        ps.setString(5, u.getEmail());
        ps.setInt(6, u.getPhone1());
        ps.setInt(7, u.getPhone2());
        ps.setString(8, u.getAddress1());
        ps.setString(9, u.getAddress2());
        ps.setInt(10, u.getZip());
        ps.setInt(11, u.getType());
        ps.setString(12, u.getRefId());
        if (b)
        {
            ps.setInt(13, u.getId());
        }

        return ps;
    }

    protected  User generate(ResultSet rs) throws SQLException
        {
                int id = rs.getInt("uid");
                String username = rs.getString("username");
                String pass = rs.getString("password");
                String fname = rs.getString("fname");
                String lname = rs.getString("lname");
                String email = rs.getString("email");
                int phone1 = rs.getInt("phone1");
                int phone2 = rs.getInt("phone2");
                String address1 = rs.getString("address1");
                String address2 = rs.getString("address2");
                int zip = rs.getInt("zip");
                int type = rs.getInt("type");
                String refid = rs.getString("refid");
                return new User(id, username, pass, email, fname, lname,
                        address1, address2, zip, type, phone1, phone2, refid) {};
        }

     public int create(User u) throws SQLException, IOException
     {
         try
         {
         PreparedStatement ps = prepare(false, u);
             System.out.println(ps);
         ps.executeQuery();
             System.out.println(ps);
         ResultSet rs = ps.getGeneratedKeys();

            rs.next();
            return rs.getInt(1);
        }
        finally
        {
            con.close();
        }
     }

     public User loginByUser(String userName,String password) throws SQLException, NullPointerException, IOException
     {
         try
         {
             con = getConnection();
             PreparedStatement ps = con.prepareStatement("SELECT * FROM `mediabooking`.`users` WHERE username=? AND password=?");
             ps.setString(1,userName);
             ps.setString(2,password);

             ResultSet rs = ps.executeQuery();

             if(rs.next())
               return generate(rs);
         }
         finally{
             con.close();
         }
         return null;
     }

     public User loginByEmail(String email,String password) throws SQLException, NullPointerException, IOException
     {
         try
         {
             con = getConnection();
             PreparedStatement ps = con.prepareStatement("SELECT * FROM `mediabooking`.`users` WHERE email=? AND password=?");
             ps.setString(1,email);
             ps.setString(2,password);

             ResultSet rs = ps.executeQuery();

             if(rs.next())
               return generate(rs);
         }
         finally{
             con.close();
         }
         return null;
     }

    public User getUser(int id) throws SQLException
    {
        con = getConnection();
        PreparedStatement ps = con.prepareStatement("SELECT * FROM mediabooking.users WHERE id=?");
        ps.setInt(1, id);
        ResultSet rs =ps.executeQuery();

        return generate(rs);
    }


}