<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping
    default-lazy="false"
    auto-import="true"
    default-access="property"
    default-cascade="all"
    package="gov.va.med.esr.common.model.security">

    <class name="ESRUserPrincipalImpl"
        table="USERS"
        dynamic-insert="true"
        dynamic-update="true">
        
        <id
            name="identifier"
            type="big_decimal"
            column="USERID">
            <generator class="gov.va.med.fw.persistent.hibernate.BigSequenceGenerator">
                <param name="sequence">transaction_s</param>
            </generator>
        </id>

        <version name="version" type="integer" column="RECORD_MODIFIED_COUNT"/>    

        <property name="name" type="string" not-null="true"
            update="false" column="USERNAME"
            length="30" unique="true"/>

        <property name="fullName" type="string" not-null="true" column="FULL_NAME" length="100" />

        <property name="password" type="string" not-null="true"
            column="PASSWORD" length="128" unique="false"/>

        <property name="givenName" type="string" column="FIRST_NAME" length="30"/>
        <property name="middleName" type="string" column="MIDDLE_NAME" length="40"/>
        <property name="familyName" type="string" column="LAST_NAME" length="40"/>

        <property name="passwordChangeDate" type="timestamp" not-null="true" column="PASSWORD_CHANGE_DATE"/>

        <property name="lockDate" type="timestamp" insert="false" column="ACCOUNT_LOCK_DATE"/>

        <property name="inActiveDate" type="date" insert="false" column="INACTIVE_DATE"/>

        <property name="initialLoginDate" type="date" insert="false" column="INITIAL_LOGIN_DATE"/>
        
        <property name="failAttemptCount" type="integer" column="NUMBER_OF_LOGIN_ATTEMPTS"/>
        
        <many-to-one name="facility" class="gov.va.med.esr.common.model.lookup.VAFacility" outer-join="false" 
            cascade="none" column="STD_INSTITUTION_ID" />
        
        <property name="jobTitle" type="string" column="JOB_TITLE" length="80"/>

        <property name="agreementSignatureCode" type="string" column="AGREEMENT_SIGNATURE_CODE" length="6"/>
        
        <property name="signatureVerified" type="boolean" column="SIGNATURE_VERIFIED_IND"/>
        
        <property name="passwordCreateDate" type="date" column="PASSWORD_CREATE_DATE"/>

        <property name="passwordSuspendDate" type="date" column="ACCOUNT_EXPIRE_DATE"/>
        
        <property name="passwordExpireDate" type="date" column="PASSWORD_EXPIRE_DATE"/>
                
        <many-to-one name="functionalGroup" 
            class="gov.va.med.esr.common.model.lookup.FunctionalGroup" outer-join="false" 
            cascade="none" column="USER_GROUP_TYPE_ID" />
                                
        <set name="internalUserRoles" lazy="false" inverse="true" cascade="all-delete-orphan">
            <key column="USERID" not-null="true" />
            <one-to-many class="UserRole" />
        </set>

       <set name="internalCapabilitySets" lazy="false" inverse="true" cascade="all-delete-orphan">
            <key column="USERID"/>
            <one-to-many class="UserCapabilitySet" />
        </set>    
                        
       <set name="internalCapabilities" lazy="false" inverse="true" cascade="all-delete-orphan">
            <key column="USERID"/>
            <one-to-many class="UserCapability" />
        </set>        
        
        <property name="createdOn" column="RECORD_CREATED_DATE" type="timestamp" not-null="true" update="false"/>
        <property name="modifiedOn" column="RECORD_MODIFIED_DATE" type="timestamp" not-null="true"/>
        <property name="createdBy" column="RECORD_CREATED_BY" update="false" type="gov.va.med.fw.persistent.hibernate.AuditUserType"/> 
        <property name="modifiedBy" column="RECORD_MODIFIED_BY" type="gov.va.med.fw.persistent.hibernate.AuditUserType"/>
    </class>
    <query name="userPrincipal_FindByUserName">
        FROM   ESRUserPrincipalImpl p
        WHERE  upper(p.name) = upper(:name)
    </query>
    <query name="userPrincipal_FindByUserRoleNameAndFunctionalGroup">
        SELECT p.name, p.fullName, p.givenName, p.familyName, p.middleName
        FROM   ESRUserPrincipalImpl p 
        WHERE  (p.inActiveDate is null or p.inActiveDate &gt; sysdate) 
        and p.functionalGroup.code = :functinalGroupName         
        and p.identifier in (select ur.userPrincipal.identifier from
        UserRole ur where ur.rolePrincipal.identifier in (select r.identifier from
        ESRRolePrincipalImpl r where r.name = :roleName))
    </query>    
    <query name="userPrincipal_FindByUserRoleName">
        SELECT p.name, p.fullName, p.givenName, p.familyName, p.middleName
        FROM   ESRUserPrincipalImpl p 
        WHERE  (p.inActiveDate is null or p.inActiveDate &gt; sysdate) and 
        p.identifier in (select ur.userPrincipal.identifier from
        UserRole ur where ur.rolePrincipal.identifier in (select r.identifier from
        ESRRolePrincipalImpl r where r.name = :name))
    </query>
    <query name="userPrincipal_FindByUserFunctionalGroup">
        SELECT p.name, p.fullName, p.givenName, p.familyName, p.middleName
        FROM   ESRUserPrincipalImpl p 
        WHERE  (p.inActiveDate is null or p.inActiveDate &gt; sysdate)
        and p.functionalGroup.code = :name
    </query>    
    <sql-query name="userPrincipal_FindByUserCapabilityName">
        <return-scalar column="username" type="string"/>
        <return-scalar column="fullname" type="string"/>
        <return-scalar column="firstname" type="string"/>
        <return-scalar column="lastname" type="string"/>
        <return-scalar column="middlename" type="string"/>
        <![CDATA[                    
        select u.USERNAME username, u.FULL_NAME fullname,
        u.FIRST_NAME firstname, u.LAST_NAME lastname,  u.MIDDLE_NAME middlename from
        users u where (u.inactive_date is null or u.inactive_date > sysdate)
        and u.userid in (    
        select up.userid from 
            user_permissions up, permission_type p
        where up.permission_type_id = p.id and
            (up.active_date is null or trunc(sysdate) >= up.active_date) and
            (up.inactive_date is null or trunc(sysdate) <= up.inactive_date) and
            p.code = :name
        union
        select ups.userid from 
            user_permission_sets ups, permission_set ps, 
            permission_set_permissions psp, permission_type p
        where 
            ups.permission_set_id = psp.permission_set_id
            and ups.permission_set_id = ps.permission_set_id
            and psp.permission_type_id = p.id and
            (ps.active_date is null or trunc(sysdate) >= ps.active_date) and
            (ps.inactive_date is null or trunc(sysdate) <= ps.inactive_date) and
            p.code = :name
        union 
        select ur.userid from 
            user_roles ur, roles r, role_permissions rp, permission_type p
        where  
            ur.role_id = rp.role_id
            and ur.role_id = r.role_id
            and rp.permission_type_id = p.id and
            (r.active_date is null or trunc(sysdate) >= r.active_date) and
            (r.inactive_date is null or trunc(sysdate) <= r.inactive_date) and
            p.code = :name
        union
        select ur.userid from 
            user_roles ur, roles r, role_permission_sets rps, permission_set ps, 
            permission_set_permissions psp, permission_type p
        where 
            ur.role_id = r.role_id
            and ur.role_id = rps.role_id 
            and rps.permission_set_id = psp.permission_set_id
            and rps.permission_set_id = ps.permission_set_id
            and psp.permission_type_id = p.id and
            (r.active_date is null or trunc(sysdate) >= r.active_date) and
            (r.inactive_date is null or trunc(sysdate) <= r.inactive_date) and
            (ps.active_date is null or trunc(sysdate) >= ps.active_date) and
            (ps.inactive_date is null or trunc(sysdate) <= ps.inactive_date) and
            p.code = :name    
        )
        ]]>    
    </sql-query>
        
    <query name="userPrincipal_FindAll">
        SELECT p.name, p.fullName, p.givenName, p.familyName, p.middleName
        FROM   ESRUserPrincipalImpl p 
        WHERE  p.inActiveDate is null or p.inActiveDate &gt; sysdate
        order by p.familyName,p.givenName
    </query>
    <query name="userPrincipal_lookupUser">
        SELECT p.name, p.fullName, p.givenName, p.familyName, p.middleName
        FROM   ESRUserPrincipalImpl p 
        WHERE  upper(p.name) = upper(:name)
    </query>        
    <query name="userPrincipal_lookupUserByFullname">
        SELECT p.name, p.fullName, p.givenName, p.familyName, p.middleName
        FROM   ESRUserPrincipalImpl p 
        WHERE  upper(p.fullName) like upper(:name)
    </query>
    <sql-query name="userPrincipal_passwordChangeHistory">
	    <![CDATA[
		select distinct  PASSWORD_CHANGE_DATE , PASSWORD, RECORD_MODIFIED_BY from (
        SELECT PASSWORD_CHANGE_DATE , PASSWORD, RECORD_MODIFIED_BY, record_modified_date,
        rank()  over (partition by PASSWORD_CHANGE_DATE order by record_modified_date asc) pwd_rank
		FROM   USERS_H
        WHERE  USERID = :userid 
		)  where pwd_rank = 1 order by PASSWORD_CHANGE_DATE desc									
		]]>
		<return-scalar column="PASSWORD_CHANGE_DATE" type="timestamp"/>
		<return-scalar column="PASSWORD" type="string"/>
		<return-scalar column="RECORD_MODIFIED_BY" type="string"/>		
    </sql-query> 	    
        
</hibernate-mapping>