CREATE TABLE principal
(
	principal_id int IDENTITY (1,1) ,
	username varchar(60)  NOT NULL ,
	password varchar(128)  NOT NULL ,
	locked_flag bit  NOT NULL 
	CONSTRAINT df_principal_locked_flag
		 DEFAULT  0,
	enabled_flag bit  NOT NULL 
	CONSTRAINT df_principal_enabled_flag
		 DEFAULT  0,
	login_attempt_count tinyint  NOT NULL ,
	acct_expiration_date datetime  NULL ,
	password_expiration_date datetime  NULL ,
	created_date datetime  NOT NULL 
	CONSTRAINT df_principal_created_date
		 DEFAULT  GETDATE(),
	updated_date datetime  NOT NULL 
	CONSTRAINT df_principal_updated_date
		 DEFAULT  GETDATE(),
	CONSTRAINT PK_principal PRIMARY KEY (principal_id ASC)
)
go

ALTER TABLE principal
	ADD CONSTRAINT  UIX_principal_username UNIQUE (username  ASC)
go

CREATE TABLE user_group
(
	user_group_id int IDENTITY (1,1) ,
	group_code varchar(30)  NOT NULL ,
	display_name varchar(30)  NOT NULL ,
	description varchar(80)  NULL ,
	created_date datetime  NOT NULL 
	CONSTRAINT df_user_group_created_date
		 DEFAULT  GETDATE(),
	CONSTRAINT PK_user_group PRIMARY KEY (user_group_id ASC)
)
go

CREATE TABLE user_role
(
	user_role_id int IDENTITY (1,1) ,
	role_code varchar(30)  NOT NULL ,
	display_name varchar(30)  NOT NULL ,
	description varchar(80)  NULL ,
	created_date datetime  NOT NULL 
	CONSTRAINT df_user_role_created_date
		 DEFAULT  GETDATE(),
	CONSTRAINT PK_user_role PRIMARY KEY (user_role_id ASC)
)
go

CREATE TABLE user_group_role_xref
(
	user_group_id int  NOT NULL ,
	user_role_id int  NOT NULL ,
	CONSTRAINT PK_user_group_role_xref PRIMARY KEY (user_group_id ASC,user_role_id ASC),
	CONSTRAINT  FK_user_group_role_xref_group FOREIGN KEY (user_group_id) REFERENCES user_group(user_group_id),
CONSTRAINT  FK_user_group_role_xref_role FOREIGN KEY (user_role_id) REFERENCES user_role(user_role_id)
)
go

CREATE TABLE principal_user_group_xref
(
	principal_id int  NOT NULL ,
	user_group_id int  NOT NULL ,
	CONSTRAINT PK_principal_user_group_xref PRIMARY KEY (principal_id ASC,user_group_id ASC),
	CONSTRAINT  FK_principal_user_group_xref_principal FOREIGN KEY (principal_id) REFERENCES principal(principal_id),
CONSTRAINT  FK_principal_user_group_xref_group FOREIGN KEY (user_group_id) REFERENCES user_group(user_group_id)
)
go