
drop table UserInfo cascade constraints
/


drop table Room cascade constraints
/


drop table LoginHistory cascade constraints
/


/*==============================================================*/
/* Table: LoginHistory                                          */
/*==============================================================*/


create table LoginHistory  (
   LoginID              NUMBER(6),
   UserName             VARCHAR2(30)                     not null,
   Alias                VARCHAR2(30)                     not null,
   IP                   VARCHAR2(20)                   default 'a'  not null,
   LoginTime            DATE                             not null,
   LogoutTime           DATE                             not null,
   RoomID               INTEGER                          not null
)
/


/*==============================================================*/
/* Table: Room                                                  */
/*==============================================================*/


create table Room  (
   RoomID               INTEGER                          not null,
   RoomName             VARCHAR2(20)                     not null,
   MaxUser              INTEGER                        default 100  not null,
   NearMaxUser          INTEGER                        default 80  not null,
   IniNo                INTEGER                        default 1  not null,
   TemplateNo           INTEGER                        default 1  not null,
   LogRoom              INTEGER                        default 0  not null,
   LogCommandInfo       INTEGER                        default 1  not null,
   LogUserLoginInfo     INTEGER                        default 1  not null,
   LogSecret            INTEGER                        default 1  not null,
   LogIP                INTEGER                        default 0  not null,
   LastMessage          INTEGER                        default 6  not null,
   RoomArea             INTEGER                        default 0  not null,
   RoomType             INTEGER                        default 0  not null,
   AllowGrade           INTEGER                        default 0  not null,
   AllowMember          INTEGER                        default 0  not null,
   AllowUserName          INTEGER                        default 0  not null,
   AllowGuest           INTEGER                        default 1  not null,
   CompType             INTEGER                        default 1  not null,
   CryptType            INTEGER                        default 1  not null,
   AllowPublic          INTEGER                        default 1  not null,
   Background           VARCHAR2(100)                  default '/images/bbb.jpg'  not null,
   LogoutUrl            VARCHAR2(50)                   default '/'  not null,
   ExternValue          VARCHAR2(255)                  default ''  not null,
   Administrators       VARCHAR2(255)                  default 'admin'  not null,
   RoomTopic            VARCHAR2(200)                  default ''  not null,
   RoomPassword         VARCHAR2(20)                   default ''  not null,
   PrivateRoom          INTEGER                        default 0  not null,
   SupportAudio         INTEGER                        default 1  not null,
   SupportVideo         INTEGER                        default 1  not null,
   RoomDisplay         INTEGER                         default 1  not null,
   Other         INTEGER                               default 0  not null,
   UserType           INTEGER                          default 0  not null ,
   constraint PK_ROOM primary key (RoomID)
)
/


/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/

create table UserInfo  (
   UserName             VARCHAR2(30)                     not null,
   Alias                VARCHAR2(30)                     not null,
   Password             VARCHAR2(40)                   default 'a'  not null,
   Age                  INTEGER                        default 1  not null,
   Sex                  INTEGER                        default 2  not null,
   Member		INTEGER 		       default 0  not null,
   Grade                INTEGER                        default 0  not null,
   Credit               INTEGER                        default 0  not null,
   Email                VARCHAR2(50)                   default 'a'  not null,
   Oicq                 VARCHAR2(20)                   default ''  not null,
   Icon                 VARCHAR2(20)                   default ''  not null,
   Photo                VARCHAR2(100)                   default 'a'  not null,
   Resume               VARCHAR2(200)                   default 'a'  not null,
   Guest                INTEGER                        default 3  not null,
   RegistTime           DATE                             not null,
   LoginTime            DATE                             not null,
   LogoutTime           DATE                             not null,
   TotalTime            INTEGER                        default 0  not null,
   Question             varchar2(50)                   default '' not null,
   Answer               varchar2(50)                   default '' not null,
   Status               INTEGER                        default 1  not null,
   IP                   VARCHAR2(20)                   default 'a'  not null,
   RoomID               INTEGER                        default 0  not null,
   UserType             INTEGER                        default 0  not null ,
   constraint PK_USERINFO primary key (UserName)
)
/

create table  TransferCredit (
TransferCreditID      int                 not null default 0,
InitUserName          varchar(30)         not null,
InitIP                varchar(20)         not null,
InitCredit            int                 not null,
PassUserName          varchar(30)         not null,
PassIP                varchar(20)         not null,
PassCredit            int                 not null,
TransferTime           DATE               not null 
);

create table if ChangeCredit (
ChangeID              int                 not null default 0,
AdminUserName        varchar(30)          not null,
AdminIP              varchar(20)          not null,
UserName        varchar(30)          not null,
ChangeTime	 DATE          not null,
OrgCredit        int               not null,
NowCredit	int		not null,
AddCredit	int		not null
 constraint PK_ChangeCredit primary key (ChangeID)
);

