ALTER TABLE USER DROP INDEX INDEX_MLOGIN;
ALTER TABLE USER DROP INDEX INDEX_MPASS;
ALTER TABLE USER DROP MLOGIN;
ALTER TABLE USER DROP MPASS;
ALTER TABLE USER ADD ACMODE INTEGER NOT NULL AFTER TZKEY;
UPDATE USER SET ACMODE = 1;
DELETE FROM USER WHERE LOGIN IS NULL;
ALTER TABLE USER CHANGE LOGIN LOGIN VARCHAR(40) BINARY NOT NULL;
ALTER TABLE USER CHANGE PASS PASS VARCHAR(40) BINARY NOT NULL;

ALTER TABLE LITR DROP IP;
ALTER TABLE LITR DROP TYPE;
ALTER TABLE LITR DROP AGENT;
ALTER TABLE LITR ADD CIP VARCHAR(15) NOT NULL AFTER CHAL;

ALTER TABLE SGITR DROP CTYPE;
ALTER TABLE SGITR DROP CAGENT;

ALTER TABLE USERRLP DROP ISPC;
ALTER TABLE USERRLP DROP ISMB;

ALTER TABLE SESS DROP INDEX INDEX_IP;
ALTER TABLE SESS DROP INDEX INDEX_TYPE;
ALTER TABLE SESS DROP INDEX INDEX_AGENT;
ALTER TABLE SESS DROP IP;
ALTER TABLE SESS DROP TYPE;
ALTER TABLE SESS DROP AGENT;


-- YSG log
CREATE TABLE SGLOG (
	UKEY        INTEGER NOT NULL,				-- User key
	SGLKEY      INTEGER NOT NULL,				-- Log key
	CODE        INTEGER NOT NULL,				-- Log status code
	LI1         VARCHAR(255),					-- Information 1
	LI2         VARCHAR(255),					-- Information 2
	LI3         VARCHAR(255),					-- Information 3
	LI4         VARCHAR(255),					-- Information 4
	LI5         VARCHAR(255),					-- Information 5
	CTIME       DATETIME NOT NULL,				-- Creation time
	PRIMARY KEY ( UKEY, SGLKEY ),
	KEY INDEX_CODE ( CODE ),
	KEY INDEX_CTIME ( CTIME )
);


-- User access control network setting table
CREATE TABLE USERACN (
	UKEY        INTEGER NOT NULL,               -- User key
	UANKEY      INTEGER NOT NULL,               -- Network key
	NET         VARCHAR(18) NOT NULL,           -- Network
	PRIMARY KEY ( UKEY, UANKEY )
);


-- Table to store requests to reset access control setting
CREATE TABLE USERRAC (
	MAIL        VARCHAR(50) NOT NULL,           -- Email address
	WPASS       VARCHAR(40) BINARY NOT NULL,    -- Password for operation
	UKEY        INTEGER NOT NULL,               -- User key
	CTIME       DATETIME NOT NULL,              -- Creation time
	ETIME       DATETIME NOT NULL,              -- Expire time
	PRIMARY KEY ( MAIL, WPASS ),
	KEY INDEX_UKEY ( UKEY ),
	KEY INDEX_CTIME ( CTIME ),
	KEY INDEX_ETIME ( ETIME )
);
