SET FOREIGN_KEY_CHECKS=0;



/* Users */
CREATE TABLE users (
	ID			SERIAL,

	Username		VARCHAR(255) NOT NULL,

	Disabled		SMALLINT NOT NULL DEFAULT '0',

	UNIQUE (Username)
) TYPE=InnoDB CHARACTER SET latin1 COLLATE latin1_bin;
CREATE INDEX users_idx1 ON users (Username);



/* User attributes */
CREATE TABLE user_attributes (
	ID			SERIAL,

	UserID			BIGINT UNSIGNED NOT NULL,

	Name			VARCHAR(255) NOT NULL,
	Operator		VARCHAR(4) NOT NULL,
	Value			VARCHAR(255),

	Disabled		SMALLINT NOT NULL DEFAULT '0',

	FOREIGN KEY (UserID) REFERENCES users(ID)
) TYPE=InnoDB CHARACTER SET latin1 COLLATE latin1_bin;



/* Groups */
CREATE TABLE groups (
	ID			SERIAL,

	Name			VARCHAR(255) NOT NULL,

	Priority		SMALLINT NOT NULL,

	Disabled		SMALLINT NOT NULL DEFAULT '0',

	Comment			VARCHAR(1024)

)  TYPE=InnoDB CHARACTER SET latin1 COLLATE latin1_bin;
CREATE INDEX groups_idx1 ON groups (Name);



/* Group attributes */
CREATE TABLE group_attributes (
	ID			SERIAL,

	GroupID			BIGINT UNSIGNED NOT NULL,

	Name			VARCHAR(255) NOT NULL,
	Operator		VARCHAR(4) NOT NULL,
	Value			VARCHAR(255),

	Disabled		SMALLINT NOT NULL DEFAULT '0',

	FOREIGN KEY (GroupID) REFERENCES groups(ID)
) TYPE=InnoDB CHARACTER SET latin1 COLLATE latin1_bin;



/* User to group mapping */
CREATE TABLE users_to_groups (
	ID			SERIAL,

	UserID			BIGINT UNSIGNED NOT NULL,
	GroupID			BIGINT UNSIGNED NOT NULL,

	Disabled		SMALLINT NOT NULL DEFAULT '0',
	Comment			VARCHAR(1024),

	UNIQUE (UserID,GroupID),
	FOREIGN KEY (UserID) REFERENCES users(ID),
	FOREIGN KEY (GroupID) REFERENCES groups(ID)
)  TYPE=InnoDB CHARACTER SET latin1 COLLATE latin1_bin;
CREATE INDEX users_to_groups_idx1 ON users_to_groups (UserID,GroupID);



/* Realms */
CREATE TABLE realms (
	ID			SERIAL,

	Name			VARCHAR(255) NOT NULL,

	Disabled		SMALLINT NOT NULL DEFAULT '0'

)  TYPE=InnoDB CHARACTER SET latin1 COLLATE latin1_bin;
CREATE INDEX realms_idx1 ON realms (Name);



/* Realm attributes */
CREATE TABLE realm_attributes (
	ID			SERIAL,

	RealmID			BIGINT UNSIGNED NOT NULL,

	Name			VARCHAR(255) NOT NULL,
	Operator		VARCHAR(4) NOT NULL,
	Value			VARCHAR(255),

	Disabled		SMALLINT NOT NULL DEFAULT '0',

	FOREIGN KEY (RealmID) REFERENCES realms(ID)
) TYPE=InnoDB CHARACTER SET latin1 COLLATE latin1_bin;



/* Accounting */
CREATE TABLE accounting (
	ID			SERIAL,

	Username		VARCHAR(255) NOT NULL,

	ServiceType		INT UNSIGNED NOT NULL,

	FramedProtocol		INT UNSIGNED NOT NULL, 

	NASPort			VARCHAR(255) NOT NULL,
		
	NASPortType		INT UNSIGNED NOT NULL,

	CallingStationID	VARCHAR(255) NOT NULL, 

	CalledStationID		VARCHAR(255) NOT NULL,

	NASPortID		VARCHAR(255) NOT NULL,

	AcctSessionID		VARCHAR(255) NOT NULL,
	
	FramedIPAddress		VARCHAR(16) NOT NULL,

	AcctAuthentic		INT UNSIGNED NOT NULL,

	EventTimestamp		DATETIME NOT NULL,

	NASIdentifier		VARCHAR(255) NOT NULL,	

	NASIPAddress		VARCHAR(16) NOT NULL,

	AcctDelayTime		INT UNSIGNED NOT NULL,

	AcctSessionTime		INT UNSIGNED NOT NULL,

	AcctInputOctets		INT UNSIGNED NOT NULL,

	AcctInputGigawords	INT UNSIGNED NOT NULL,

	AcctInputPackets	INT UNSIGNED NOT NULL,	

	AcctOutputOctets	INT UNSIGNED NOT NULL,

	AcctOutputGigawords	INT UNSIGNED NOT NULL,

	AcctOutputPackets	INT UNSIGNED NOT NULL,

	AcctStatusType		INT UNSIGNED NOT NULL,

	AcctTerminateCause	INT UNSIGNED NOT NULL
) TYPE=InnoDB CHARACTER SET latin1 COLLATE latin1_bin;
CREATE INDEX accounting_idx1 ON accounting (Username);