/***********************************************************
 * Script: Create_Table_DIRECTORY.txt
 * Created Date: 02/06/2009
 * Purpose: Create DIRECTORY TABLE, SEQ, CONSTRAINTS and 
 *          INDEXING for the Online Directory Request Form
 ***********************************************************/


WHENEVER SQLERROR EXIT COMMIT

/*=========================================================*/
/* Table: directory  */
/*=========================================================*/
CREATE TABLE directory
( 	directory_id 	number(32,0) 	not null,
  	member_number 	varchar2(11) 	not null,
  	last_name	varchar2(100),
  	first_name	varchar2(100),
  	email		varchar2(100),
  	addr_line_1	varchar2(100),
  	addr_line_2	varchar2(100),
  	city		varchar2(100),
  	state		varchar2(50),
  	zip_code	varchar2(50),
  	country		varchar2(100),
  	agent_id 	number(32,0), 	
  	is_terms_conditions char(1), 	
  	is_online char(1),
  	creation_date timestamp,
  	CONSTRAINT directory_directory_id_pk 
  	  PRIMARY KEY (dirctory_id),
  	CONSTRAINT directory_agent_id_fk
  	  FOREIGN KEY (agent_id)
  	  REFERENCES agent(agent_id)
)
/


/*=========================================================*/
/*  Sequence: directory_id_seq  */
/*=========================================================*/

CREATE SEQUENCE directory_id_seq
increment by 1
start with 1
maxvalue 999999999
minvalue 1
nocycle
nocache
noorder
/

/*=========================================================*/
/* Index: idx_directory_member_number  */
/*=========================================================*/
create index idx_directory_member_number on directory (
   member_number ASC
)
/

/*=========================================================*/
/* Index: idx_directory_creation_date  */
/*=========================================================*/
create index idx_directory_creation_date on directory (
   creation_date ASC
)
/

/*=========================================================*/
/* Index: idx_directory_agent_id  */
/*=========================================================*/
create index idx_directory_agent_id on directory (
   agent_id ASC
)
/

/*=========================================================*/
/* Index: idx_directory_last_name  */
/*=========================================================*/
create index idx_directory_last_name on directory (
   last_name ASC
)
/

/*=========================================================*/
/* Index: idx_directory_first_name  */
/*=========================================================*/
create index idx_directory_first_name on directory (
   first_name ASC
)
/

/*=========================================================*/
/* Index: idx_directory_full_name  */
/*=========================================================*/
create index idx_directory_full_name on directory (
      first_name ASC,
      last_name ASC
)
/



-----------------------------------------------------------------------------------



/***********************************************************
 * Script: INSERT_DIRECTORY_ADMIN.txt
 * Created Date: 02/26/2009
 * Purpose: Insert NEW ADMIN USERS to run Directory reporting 
 ***********************************************************/

/* Insert NEW role Type */

INSERT INTO role_type
(role_id, role_type)
VALUES
(283, 'ROLE_FRI_DIRECTORY_ADMIN');
commit;

/* Insert NEW admin users, its user_role and user_property */

DECLARE
  p_user_id NUMBER;
  TYPE membernumberList IS TABLE OF VARCHAR2(11);
  membernumbers membernumberList := membernumberList('prodsupport','ownerwebteam');
  TYPE passwordList IS TABLE OF VARCHAR2(11);
  passwords passwordList := passwordList('margate01','orlando01');
BEGIN


FOR i IN membernumbers.FIRST .. membernumbers.LAST
LOOP
        DBMS_OUTPUT.PUT_LINE('looping');

  SELECT HIBERNATE_SEQUENCE.NEXTVAL
    INTO p_user_id
    FROM DUAL;
    
  INSERT INTO USERS
    (USER_ID,
     USER_NAME,
     PASSWORD,
     EMAIL,
     SECURITY_QUESTION,
     SECURITY_ANSWER,
     DISABLED_FLAG,
     DELETED_FLAG,
     LOCKOUT_FLAG,
     CREATED_TIME)
    VALUES
    (p_user_id,
     membernumbers(i),
     passwords(i),
     'abc@xyz.com',
     'Please do not use the forgot password function on this account',
     passwords(i),
     0,
     0,
     0,
     SYSDATE);
     
  INSERT INTO USER_ROLE
    (USER_ROLE_ID,
     ROLE_ID,
     USER_ID)
    VALUES
    (HIBERNATE_SEQUENCE.NEXTVAL,
     283,
     p_user_id);

  INSERT INTO user_properties 
    (user_properties_id, 
     property_id,
     user_id,
     VALUE)
   VALUES 
    (Hibernate_sequence.NEXTVAL,
     215, 
     p_user_id, 
     membernumbers(i));

END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('inserting admin failed');
END;

commit;