package ssc.jdbc.entities;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ssc.jdbc.Database;
import ssc.jdbc.Utility;

public class SchoolMember extends Entity {

    private String _nameFirst;
    private String _nameLast;
    private String _username;
    private int    _universityId;
    private String _roleName;
    private String _password;
    private String _saltString;

    private SchoolMember(boolean isNew, String firstName, String lastName, String userName, int universityId, String roleName, String password, String saltString) {
        isNew         = false;
        _nameFirst    = firstName;
        _nameLast     = lastName;
        _username     = userName;
        _universityId = universityId;
        _roleName     = roleName;
        _password     = password;
        _saltString   = saltString;
    }
    public SchoolMember(String firstName, String lastName, String userName, int universityId, String roleName, String password, String saltString) {
        isNew         = true;
        _nameFirst    = firstName;
        _nameLast     = lastName;
        _username     = userName;
        _universityId = universityId;
        _roleName     = roleName;
        _password     = password;
        _saltString   = saltString;
    }

    public SchoolMember() {
    }

    public String getFirstName() { return _nameFirst; }
    public void setFirstName(String value) { _nameFirst = value; isDirty = true; }

    public String getLastName() { return _nameLast; }
    public void setLastName(String value) { _nameLast = value; isDirty = true; }

    public String getUserName() { return _username; }
    public void setUserName(String value) { _username = value; isDirty = true; }

    public int getUniversityID() { return _universityId; }
    public void setUniversityID(int value) { _universityId = value; isDirty = true; }

    public String getRoleName() { return _roleName; }
    public void setRoleName(String value) { _roleName = value; isDirty = true; }

    public String getPassword() { return _password; }
    public void setPassword(String value) { _password = value; isDirty = true; }

    public String getSaltString() { return _saltString; }
    public void setSaltString(String value) { _saltString = value; isDirty = true; }

    ///////////////////////////

    public boolean authenticate(String password) {

        String concat = password + _saltString;

        String hash = Utility.SHA1( concat );

        return _password.equalsIgnoreCase( hash );
    }

    ///////////////////////////

    public String getFullName() {

        return _nameLast + ", " + _nameFirst;
    }

    public @Override String toString() {

        return getFullName() + " (" + _roleName + ": " + _universityId + ")";

    }

    public static SchoolMember isCached(int universityId) {

        for(Entity e : Database.getInstance().getCache()) {

            if( e instanceof SchoolMember ) {

                SchoolMember s = (SchoolMember)e;
                if( s.getUniversityID() == universityId ) return s;
            }
        }

        return null;
    }

    public static SchoolMember isCached(String username) {

        for(Entity e : Database.getInstance().getCache()) {

            if( e instanceof SchoolMember ) {

                SchoolMember s = (SchoolMember)e;
                if( s.getUserName().toLowerCase().equals(username.toLowerCase()) ) return s;
            }
        }

        return null;
    }

    public @Override boolean match(Object[] query) {

        String roleName  = (String)query[0];
        String nameQuery = (String)query[1];

        boolean match = true;

        if( roleName != null ) {

            match = strMatch( _roleName, roleName );
        }

        if( nameQuery != null ) {

            match = match && ( strMatch( _nameFirst, nameQuery ) || strMatch( _nameLast, nameQuery ) || strMatch( _username, nameQuery ) );

        }

        if( roleName == null && nameQuery == null ) match = false;

        return match;

    }

    ///////////////////////////

    public ModuleMark[] getMarks() throws SQLException {

        return ModuleMark.getModuleMarks(this);
    }

    public ModuleYear[] getTeachingModuleYears() throws SQLException {

        return ModuleYear.getModuleYears(this);

    }

    public @Override boolean save() throws SQLException {

        if(!isDirty) return false;

        Database d = Database.getInstance();

        if( isNew ) {

            PreparedStatement stmt = d.prepareStatement("INSERT INTO \"SchoolMember\" ( \"firstName\", \"lastName\", \"username\", \"universityId\", \"roleName\", \"password\", \"saltString\") VALUES ( ?, ?, ?, ?, ?, ?, ? )");
            stmt.setString( 1, _nameFirst );
            stmt.setString( 2, _nameLast );
            stmt.setString( 3, _username );
            stmt.setInt   ( 4, _universityId );
            stmt.setString( 5, _roleName );
            stmt.setString( 6, _password );
            stmt.setString( 7, _saltString );

            stmt.executeUpdate();
            stmt.close();

            isNew = false;

        } else {


            PreparedStatement stmt = d.prepareStatement("UPDATE \"SchoolMember\" SET \"firstName\" = ?, \"lastName\" = ?, \"username\" = ?, \"roleName\" = ?, \"saltString\" = ? WHERE \"universityId\" = ?");
            stmt.setString( 1, _nameFirst );
            stmt.setString( 2, _nameLast );
            stmt.setString( 3, _username );
            stmt.setString( 4, _roleName );
            stmt.setString( 5, _password );
            stmt.setString( 6, _saltString );

            stmt.setInt   ( 7, _universityId );

            stmt.executeUpdate();
            stmt.close();
        }

        isDirty = false;
        return true;
    }

    ///////////////////////////

    private static final String SELECT_FIELDS = "SELECT \"firstName\", \"lastName\", \"username\", \"universityID\", \"roleName\", \"password\", \"saltString\"";

    /** Returns null if the SchoolMember represented by this row is already cached */
    private static SchoolMember fromRow(ResultSet result) throws SQLException {

        String firstName = result.getString(1);
        String lastName  = result.getString(2);
        String username  = result.getString(3);
        int    uniId     = result.getInt(4);
        String rolename  = result.getString(5);
        String password  = result.getString(6);
        String salt      = result.getString(7);

        SchoolMember cached = isCached(uniId);
        if( cached != null ) return null;

        SchoolMember s = new SchoolMember(false, firstName, lastName, username, uniId, rolename, password, salt );
        return s;
    }

    public static SchoolMember[] getSchoolMembers() throws SQLException {

        return SchoolMember.getSchoolMembers( null, null );
    }

    public static SchoolMember[] getSchoolMembers(String roleName, String nameQuery) throws SQLException {

        ArrayList<SchoolMember>                   cacheResults;
        if(roleName == null && nameQuery == null) cacheResults = new ArrayList<SchoolMember>();
        else                                      cacheResults = searchCache( SchoolMember.class, roleName, nameQuery );

        PreparedStatement stmt;

        if( roleName == null && nameQuery == null ) {

            stmt = Database.getInstance().prepareStatement( SELECT_FIELDS + " FROM \"SchoolMember\"");

        } else {

            if( roleName != null && nameQuery == null ) {

                stmt = Database.getInstance().prepareStatement( SELECT_FIELDS + " FROM \"SchoolMember\" WHERE \"roleName\" = ?");
                stmt.setString(1, "%" + roleName  + "%"); // roleName

            } else if( roleName == null && nameQuery != null ) {

                stmt = Database.getInstance().prepareStatement( SELECT_FIELDS + " FROM \"SchoolMember\" WHERE (\"firstName\" ILIKE ?) OR (\"lastName\" ILIKE ?) OR (\"username\" ILIKE ?)");
                stmt.setString(1, "%" + nameQuery + "%"); // firstName
                stmt.setString(2, "%" + nameQuery + "%"); // lastName
                stmt.setString(3, "%" + nameQuery + "%"); // userName

            } else {

                stmt = Database.getInstance().prepareStatement( SELECT_FIELDS + " FROM \"SchoolMember\" WHERE \"roleName\" = ? AND ((\"firstName\" ILIKE ?) OR (\"lastName\" ILIKE ?) OR (\"username\" ILIKE ?))");
                stmt.setString(1, "%" + roleName  + "%"); // roleName
                stmt.setString(2, "%" + nameQuery + "%"); // firstName
                stmt.setString(3, "%" + nameQuery + "%"); // lastName
                stmt.setString(4, "%" + nameQuery + "%"); // userName

            }
        }

        ResultSet result = stmt.executeQuery();

        ArrayList<SchoolMember> fromDb = new ArrayList<SchoolMember>();

        while(result.next()) {

            SchoolMember s = fromRow( result );
            if( s != null ) fromDb.add( s );
        }
        result.close();

        fromDb.addAll( cacheResults );

        SchoolMember[] ret = new SchoolMember[ fromDb.size() ];
        fromDb.toArray( ret );
        return ret;
    }

    public static SchoolMember getSchoolMember(int universityID) throws SQLException {

        SchoolMember cached = isCached( universityID );
        if( cached != null ) return cached;

        PreparedStatement stmt = Database.getInstance().prepareStatement( SELECT_FIELDS + " FROM \"SchoolMember\" WHERE \"universityID\" = ?");
        stmt.setInt( 1, universityID );

        SchoolMember ret = null;

        ResultSet result = stmt.executeQuery();
        if( result.next() ) {

            ret = fromRow( result );
        }
        result.close();

        return ret;
    }

    public static SchoolMember getSchoolMember(String username) throws SQLException {

        SchoolMember cached = isCached(username);
        if( cached != null ) return cached;

        PreparedStatement stmt = Database.getInstance().prepareStatement( SELECT_FIELDS + " FROM \"SchoolMember\" WHERE \"username\" = ?");
        stmt.setString( 1, username );

        SchoolMember ret = null;

        ResultSet result = stmt.executeQuery();
        if( result.next() ) {

            ret = fromRow( result );
        }
        result.close();

        return ret;
    }

}