/*****************************************************************************
*
* Filename         - $Workfile:   agents.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - 
*
* Contents         - Definition of the agents table.
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/

PROMPT Creating Table AGENTS

create table agents (
   agent_code                    char(5) not null,
   description                   varchar2(50),
   site_id                       number(10) not null,
   last_user                     number(10)              not null,
   archive_flag                  number(10)   default 0  not null,
   max_logins                    number(3),
   scanner_timeout               number(4),
   mws_timeout                   number(4),
   allow_enhanced_log            char(1) default 'N' not null,
   email_account_id              number(10,0),
   row_create_time               date,
   row_date_time                 date,
   cuser                         varchar2(30),
   muser                         varchar2(30),
   mterm                         varchar2(64),
   source                        varchar2(8),
   to_be_archived                varchar2(1),
   appn_row_id                   number(18) not null,
   system_id                     number(4) default 0 NOT NULL
);

alter table agents add ( constraint agents_pk primary key (appn_row_id) using index tablespace &1._indexes);

alter table agents add ( constraint agents_uk1 unique (agent_code) using index tablespace &1._indexes);

alter table agents add (constraint agents_fk_site_info foreign key (site_id) references site_info (site_id));

alter table agents add constraint agents_allow_enhanced_log check ( allow_enhanced_log in ('Y','N'));

alter table agents add constraint agents_fk_email_accounts foreign key (email_account_id) references email_accounts (email_account_id) on delete set null;
 
create index agents_sk1 on agents(email_account_id) tablespace &1._indexes;
create index agents_sk2 ON agents(site_id) tablespace &1._indexes;

COMMENT ON TABLE AGENTS IS 'This table contains information about the agents who have access to the system';

COMMENT ON COLUMN AGENTS.AGENT_CODE IS  'Agent code';
COMMENT ON COLUMN AGENTS.ALLOW_ENHANCED_LOG IS  'Enable enhanced event logging';
COMMENT ON COLUMN AGENTS.ARCHIVE_FLAG IS  'This field is no longer used';
COMMENT ON COLUMN AGENTS.DESCRIPTION IS  'Description of the agent';
COMMENT ON COLUMN AGENTS.EMAIL_ACCOUNT_ID IS  'Associated email account identifier';
COMMENT ON COLUMN AGENTS.LAST_USER IS  'Identity of the last user to access this agent';
COMMENT ON COLUMN AGENTS.MAX_LOGINS IS  'Maximum concurrent logins for this agent';
COMMENT ON COLUMN AGENTS.MWS_TIMEOUT IS  'MWS timeout for this agent';
COMMENT ON COLUMN AGENTS.SCANNER_TIMEOUT IS  'Scanner timeout for this agent';
COMMENT ON COLUMN AGENTS.SITE_ID IS  'The identity of the site';
COMMENT ON COLUMN AGENTS.APPN_ROW_ID IS 'Unique sequence number for this row';
COMMENT ON COLUMN AGENTS.ROW_CREATE_TIME IS 'The time the row was created';
COMMENT ON COLUMN AGENTS.ROW_DATE_TIME IS 'The time the row was last modified';
COMMENT ON COLUMN AGENTS.CUSER IS 'The user who created the row';
COMMENT ON COLUMN AGENTS.MUSER IS 'The user who last modified the row';
COMMENT ON COLUMN AGENTS.MTERM IS 'The terminal from which the last modifcation to this row was made';
COMMENT ON COLUMN AGENTS.SYSTEM_ID IS 'The system identifier';
COMMENT ON COLUMN AGENTS.SOURCE IS 'The process that last amended this row ';
COMMENT ON COLUMN AGENTS.TO_BE_ARCHIVED IS 'Indicates that this row is to be archived or has been archived';

