-- LabbitBox 標準認証 アカウント
CREATE TABLE LB_AUTH_ACC
(
  ACC_NO       Bigint                          NOT NULL,
  ACC_TYPE     Smallint                        DEFAULT  0,
  ACC_ID       Varchar(80) CHARACTER SET ASCII NOT NULL,
  SCREEN_NAME  Varchar(80) CHARACTER SET UTF8 NOT NULL,
  SALT         Varchar(2)  CHARACTER SET ASCII DEFAULT 'Aa',
  PWD          Varchar(64) CHARACTER SET ASCII DEFAULT '',
  VALID_ID     Varchar(1)  CHARACTER SET ASCII DEFAULT 'T',
  ACC_LOCK     Varchar(1)  CHARACTER SET ASCII DEFAULT 'T',
  CERTIFICATED Timestamp                       DEFAULT NULL,
  CREATED      Timestamp                       DEFAULT CURRENT_TIMESTAMP,
  UPDATED      Timestamp                       DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(ACC_NO)
);

UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'アカウント番号' where RDB$FIELD_NAME = 'ACC_NO'       and RDB$RELATION_NAME = 'LB_AUTH_ACC';
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'アカウント区分' where RDB$FIELD_NAME = 'ACC_TYPE'     and RDB$RELATION_NAME = 'LB_AUTH_ACC';
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'アカウントID'   where RDB$FIELD_NAME = 'ACC_ID'       and RDB$RELATION_NAME = 'LB_AUTH_ACC';
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = '表示名'         where RDB$FIELD_NAME = 'SCREEN_NAME'  and RDB$RELATION_NAME = 'LB_AUTH_ACC';
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'SALT'           where RDB$FIELD_NAME = 'SALT'         and RDB$RELATION_NAME = 'LB_AUTH_ACC';
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'パスワード'     where RDB$FIELD_NAME = 'PWD'          and RDB$RELATION_NAME = 'LB_AUTH_ACC';
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = '有効区分(T/F)'  where RDB$FIELD_NAME = 'VALID_ID'     and RDB$RELATION_NAME = 'LB_AUTH_ACC';
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'ロック(T/F)'    where RDB$FIELD_NAME = 'ACC_LOCK'     and RDB$RELATION_NAME = 'LB_AUTH_ACC';
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = '認証日時'       where RDB$FIELD_NAME = 'CERTIFICATED' and RDB$RELATION_NAME = 'LB_AUTH_ACC';
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = '作成日時'       where RDB$FIELD_NAME = 'CREATED'      and RDB$RELATION_NAME = 'LB_AUTH_ACC';
UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = '更新日時'       where RDB$FIELD_NAME = 'UPDATED'      and RDB$RELATION_NAME = 'LB_AUTH_ACC';

CREATE UNIQUE INDEX LB_AUTH_ACC_2NDKEY ON LB_AUTH_ACC(ACC_ID     );
CREATE UNIQUE INDEX LB_AUTH_ACC_3NDKEY ON LB_AUTH_ACC(SCREEN_NAME);

UPDATE RDB$RELATIONS SET RDB$DESCRIPTION = 'LabbitBox 標準認証 アカウント' WHERE RDB$RELATION_NAME = 'LB_AUTH_ACC';

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON LB_AUTH_ACC TO SYSDBA WITH GRANT OPTION;

CREATE GENERATOR SEQ_LB_AUTH_ACC_ACC_NO;



-- LabbitBox 標準認証 ログ
CREATE TABLE LB_AUTH_LOG
(
  LOG_NO      Bigint                          NOT NULL,
  ACC_NO      Bigint                          NOT NULL,
  LOGIN_TIME  Timestamp                       DEFAULT CURRENT_TIMESTAMP,
  AUTH_TYPE   Varchar(7)  CHARACTER SET ASCII,
  AUTH_STATUS Varchar(1)  CHARACTER SET ASCII DEFAULT 'F',
  IP_ADDRESS  Varchar(15) CHARACTER SET ASCII DEFAULT '',
  VALID_ID    Varchar(1)  CHARACTER SET ASCII DEFAULT 'T',
  CREATE_USER Bigint                          DEFAULT -1,
  CREATE_TIME Timestamp                       DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (LOG_NO)
);

UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = '主キー'                      where RDB$FIELD_NAME = 'LOG_NO'      and RDB$RELATION_NAME = 'LB_AUTH_LOG';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'アカウント'                  where RDB$FIELD_NAME = 'ACC_NO'      and RDB$RELATION_NAME = 'LB_AUTH_LOG';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'ログイン日時'                where RDB$FIELD_NAME = 'LOGIN_TIME'  and RDB$RELATION_NAME = 'LB_AUTH_LOG';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = '認証種別(POST,SESSIONなど)'  where RDB$FIELD_NAME = 'AUTH_TYPE'   and RDB$RELATION_NAME = 'LB_AUTH_LOG';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = '認証結果(T:成功/F:失敗)'     where RDB$FIELD_NAME = 'AUTH_STATUS' and RDB$RELATION_NAME = 'LB_AUTH_LOG';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'IPアドレス'                  where RDB$FIELD_NAME = 'IP_ADDRESS'  and RDB$RELATION_NAME = 'LB_AUTH_LOG';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = '有効区分(T:有効/F:無効)'     where RDB$FIELD_NAME = 'VALID_ID'    and RDB$RELATION_NAME = 'LB_AUTH_LOG';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = '新規作成者'                  where RDB$FIELD_NAME = 'CREATE_USER' and RDB$RELATION_NAME = 'LB_AUTH_LOG';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = '新規作成日'                  where RDB$FIELD_NAME = 'CREATE_TIME' and RDB$RELATION_NAME = 'LB_AUTH_LOG';

CREATE INDEX LB_AUTH_LOG_2NDKEY ON LB_AUTH_LOG (ACC_NO,LOGIN_TIME);

UPDATE RDB$RELATIONS set RDB$DESCRIPTION = 'LabbitBox 標準認証 ログ' where RDB$RELATION_NAME = 'LB_AUTH_LOG';

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON LB_AUTH_LOG TO  SYSDBA WITH GRANT OPTION; 

CREATE GENERATOR SEQ_LB_AUTH_LOG_LOG_NO;



-- LabbitBox 標準認証 OTP
CREATE TABLE LB_AUTH_OTP
(
  OTP_NO      Bigint                          NOT NULL,
  ACC_NO      Bigint                          NOT NULL,
  PRM_ID      Varchar(1)  CHARACTER SET ASCII DEFAULT 'C' NOT NULL,
  PRM_KEYWORD Varchar(32) CHARACTER SET ASCII NOT NULL,
  VALID_TIME  Timestamp                       DEFAULT NULL,
  VALID_ID    Varchar(1)  CHARACTER SET ASCII DEFAULT 'T',
  CREATE_USER Bigint                          DEFAULT -1,
  CREATE_TIME Timestamp                       DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (OTP_NO)
);

UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = '主キー'                  where RDB$FIELD_NAME = 'OTP_NO'      and RDB$RELATION_NAME = 'LB_AUTH_OTP';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'アカウント(対象外は-1)'  where RDB$FIELD_NAME = 'ACC_NO'      and RDB$RELATION_NAME = 'LB_AUTH_OTP';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = '区分(C:COOKIE/P:OTP)'    where RDB$FIELD_NAME = 'PRM_ID'      and RDB$RELATION_NAME = 'LB_AUTH_OTP';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = 'キー'                    where RDB$FIELD_NAME = 'PRM_KEYWORD' and RDB$RELATION_NAME = 'LB_AUTH_OTP';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = '有効期限'                where RDB$FIELD_NAME = 'VALID_TIME'  and RDB$RELATION_NAME = 'LB_AUTH_OTP';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = '有効区分(T:有効/F:無効)' where RDB$FIELD_NAME = 'VALID_ID'    and RDB$RELATION_NAME = 'LB_AUTH_OTP';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = '新規作成者'              where RDB$FIELD_NAME = 'CREATE_USER' and RDB$RELATION_NAME = 'LB_AUTH_OTP';
UPDATE RDB$RELATION_FIELDS set RDB$DESCRIPTION = '新規作成日'              where RDB$FIELD_NAME = 'CREATE_TIME' and RDB$RELATION_NAME = 'LB_AUTH_OTP';

UPDATE RDB$RELATIONS set RDB$DESCRIPTION = 'ログインパラメータファイル' where RDB$RELATION_NAME = 'LB_AUTH_OTP';

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON LB_AUTH_OTP TO  SYSDBA WITH GRANT OPTION;

CREATE GENERATOR SEQ_LB_AUTH_OTP_OTP_NO;


