package data;

import java.rmi.*;
import java.sql.*;
import java.util.*;
import javax.ejb.*;
import javax.naming.*;
import javax.sql.*;

public class ApplicantBean implements EntityBean
{
    private DataSource dataSource;
    private SkillLocalHome skillHome;
    private LocationLocalHome locationHome;

    private String login;
    private String name;
    private String email;
    private String summary;
    private LocationLocal location;

    // vector attribute; List of SkillLocal ref's.
    private List skills;

    public String getLogin () {
        return login;
    }

    public String getName () {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail () {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getSummary () {
        return summary;
    }

    public void setSummary(String summary) {
        this.summary = summary;
    }

    public LocationLocal getLocation () {
        return location;
    }

    public void setLocation(LocationLocal location) {
        this.location = location;
    }

    /** returns (copy of) the list of skills. */
    public Collection getSkills () {
        return new ArrayList(this.skills);
    }

    public void setSkills(Collection skills) {

        // just validate that the collection holds references to SkillLocal's
        for(Iterator iter = getSkills().iterator(); iter.hasNext(); ) {
            SkillLocal skill = (SkillLocal)iter.next();
        }

        // replace the list of skills with that defined.
        this.skills = new ArrayList(skills);
    }

    // EJB methods start here

    public void ejbPostCreate (String login, String name, String email) {}

    public String ejbCreate (String login, String name, String email) throws CreateException {

        try {
            ejbFindByPrimaryKey(login);
            throw new CreateException("Duplicate applicant name: "+login);
        }
        catch (FinderException ex) {}
        
        Connection con = null;
        PreparedStatement stmt = null;
        try {
            con = dataSource.getConnection();
            stmt = con.prepareStatement(
            "INSERT INTO Applicant (login,name,email) VALUES (?,?,?)");

            stmt.setString(1, login);
            stmt.setString(2, name);
            stmt.setString(3, email);
            stmt.executeUpdate();
        }
        catch (SQLException e) {
            error("Error creating applicant "+login,e);
        }
        finally {
            closeConnection(con, stmt, null, null);
        }
        this.login = login;
        this.name = name;
        this.email = email;
        this.summary = null;
        this.location = null;

        this.skills = new ArrayList();

        return login;
    }

    public String ejbFindByPrimaryKey(String login) throws FinderException {
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = dataSource.getConnection();
            stmt = con.prepareStatement(
            "SELECT login FROM Applicant WHERE login = ?");

            stmt.setString(1, login);
            rs = stmt.executeQuery();

            if (!rs.next()) {
                throw new FinderException("Unknown applicant: "+login);
            }
            return login;
        }
        catch (SQLException e) {
            error("Error in findByPrimaryKey for "+login,e);
        }
        finally {
            closeConnection(con, stmt, null, rs);
        }
        return null;
    }

    public Collection ejbFindAll() throws FinderException {
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = dataSource.getConnection();
            stmt = con.prepareStatement(
            "SELECT login FROM Applicant ORDER By login");

            rs = stmt.executeQuery();

            Collection col = new ArrayList();
            while (rs.next()) {
                col.add(rs.getString(1));
            }
            return col;
        }
        catch (SQLException e) {
            error("Error in findAll",e);
        }
        finally {
            closeConnection(con, stmt, null, rs);
        }
        return null;
    }

    public Collection ejbFindByLocation(String location) throws FinderException {
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = dataSource.getConnection();
            stmt = con.prepareStatement(
            "SELECT login FROM Applicant WHERE location = ? ORDER BY login");

            stmt.setString(1, location);
            rs = stmt.executeQuery();

            Collection col = new ArrayList();
            while (rs.next()) {
                col.add(rs.getString(1));
            }
            return col;
        }
        catch (SQLException e) {
            error("Error in findByLocation: "+location,e);
        }
        finally {
            closeConnection(con, stmt, null, rs);
        }
        return null;
    }

    public void ejbLoad(){
        String login = (String)ctx.getPrimaryKey();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = dataSource.getConnection();
            stmt = con.prepareStatement(
            "SELECT name,email,summary,location FROM Applicant WHERE login = ?");

            stmt.setString(1, login);
            rs = stmt.executeQuery();

            if (!rs.next()) {
                error("No data found in ejbLoad for "+login,null);
            }
            this.login = login;
            this.name = rs.getString(1);
            this.email = rs.getString(2);
            this.summary = rs.getString(3);
            String locationName = rs.getString(4);
            this.location = (locationName!=null)?locationHome.findByPrimaryKey(locationName):null;

            // load skills
            stmt = con.prepareStatement(
            "SELECT applicant, skill FROM ApplicantSkill WHERE applicant = ? ORDER BY skill");

            stmt.setString(1, login);
            rs = stmt.executeQuery();

            List skillNameList = new ArrayList();
            while (rs.next()) {
                skillNameList.add(rs.getString(2));
            }
            this.skills = skillHome.lookup(skillNameList);

        }
        catch (SQLException e) {
            error("Error in ejbLoad for "+login,e);
        }
        catch (FinderException e) {
            error("Error in ejbLoad (invalid location) for "+login,e);
        }
        finally {
            closeConnection(con, stmt, null, rs);
        }
    }

    public void ejbStore(){
        Connection con = null;
        PreparedStatement stmt = null;
        try {
            con = dataSource.getConnection();
            stmt = con.prepareStatement(
            "UPDATE Applicant SET name = ?, email = ?, summary = ?, location = ? WHERE login = ?");

            stmt.setString(1, name);
            stmt.setString(2, email);
            stmt.setString(3, summary);
            if (location != null) {
                stmt.setString(4, location.getName());
            } else {
                stmt.setNull(4, java.sql.Types.VARCHAR);
            }
            stmt.setString(5, login);
            stmt.executeUpdate();

            // delete all skills
            stmt = con.prepareStatement(
            "DELETE FROM ApplicantSkill WHERE applicant = ?");

            stmt.setString(1, login);
            stmt.executeUpdate();

            // now insert skills back in again
            for (Iterator iter = getSkills().iterator(); iter.hasNext(); ) {
                SkillLocal skill = (SkillLocal)iter.next();
    
                stmt = con.prepareStatement(
                "INSERT INTO ApplicantSkill (applicant,skill) VALUES (?,?)");
    
                stmt.setString(1, login);
                stmt.setString(2, skill.getName());
                stmt.executeUpdate();
            }
        }
        catch (SQLException e) {
            error("Error in ejbStore for "+login,e);
        }
        finally {
            closeConnection(con, stmt, null, null);
        }

    }

    public void ejbPassivate(){
        login = null;
        name = null;
        email = null;
        summary = null;
        location = null;
    }

    public void ejbActivate(){
    }

    public void ejbRemove(){
        String login = (String)ctx.getPrimaryKey();
    
        Connection con = null;
        PreparedStatement stmt1 = null;
        PreparedStatement stmt2 = null;
        try {
            con = dataSource.getConnection();

            stmt1 = con.prepareStatement(
            "DELETE FROM ApplicantSkill WHERE applicant = ?");
            stmt2 = con.prepareStatement(
            "DELETE FROM Applicant WHERE login = ?");

            stmt1.setString(1, login);
            stmt2.setString(1, login);

            stmt1.executeUpdate();
            stmt2.executeUpdate();
        }
        catch (SQLException e) {
            error("Error removing applicant "+login,e);
        }
        finally {
            closeConnection(con, stmt1, stmt2, null);
        }
        login = null;
        name = null;
        email = null;
        summary = null;
        location = null;
    }

    private EntityContext ctx;

    public void setEntityContext(EntityContext ctx) {
        this.ctx = ctx;
        InitialContext ic = null;
        try {
            ic = new InitialContext();
            dataSource = (DataSource)ic.lookup("java:comp/env/jdbc/Agency");
            skillHome = (SkillLocalHome)ic.lookup("java:comp/env/ejb/SkillLocal");
            locationHome = (LocationLocalHome)ic.lookup("java:comp/env/ejb/LocationLocal");
        }
        catch (NamingException ex) {
            error("Error looking up depended EJB or resource",ex);
            return;
        }
    }

    public void unsetEntityContext() {
        this.ctx = null;
        dataSource = null;
        skillHome = null;
        locationHome = null;
    }

    private void closeConnection (Connection con, PreparedStatement stmt1, PreparedStatement stmt2, ResultSet rslt) {
        if (rslt != null) {
            try {
                rslt.close();
            }
            catch (SQLException e) {}
        }
        if (stmt1 != null) {
            try {
                stmt1.close();
            }
            catch (SQLException e) {}
        }
        if (stmt2 != null) {
            try {
                stmt2.close();
            }
            catch (SQLException e) {}
        }
        if (con != null) {
            try {
                con.close();
            }
            catch (SQLException e) {}
        }
    }

    private void error (String msg, Exception ex) {
        String s = "ApplicantBean: "+msg + "\n" + ex;
        System.out.println(s);
        throw new EJBException(s,ex);
    }
    
}