-- FreeRADIUS SQL tables (minimal MySQL/MariaDB-compatible AAA + accounting schema)
CREATE TABLE IF NOT EXISTS radcheck (id BIGINT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(128) NOT NULL,attribute VARCHAR(64) NOT NULL,op CHAR(2) NOT NULL DEFAULT ':=',value VARCHAR(253) NOT NULL, INDEX username_idx(username(64)));
CREATE TABLE IF NOT EXISTS radreply (id BIGINT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(128) NOT NULL,attribute VARCHAR(64) NOT NULL,op CHAR(2) NOT NULL DEFAULT '=',value VARCHAR(253) NOT NULL, INDEX username_idx(username(64)));
CREATE TABLE IF NOT EXISTS radgroupcheck (id BIGINT AUTO_INCREMENT PRIMARY KEY,groupname VARCHAR(64) NOT NULL,attribute VARCHAR(64) NOT NULL,op CHAR(2) NOT NULL DEFAULT ':=',value VARCHAR(253) NOT NULL);
CREATE TABLE IF NOT EXISTS radgroupreply (id BIGINT AUTO_INCREMENT PRIMARY KEY,groupname VARCHAR(64) NOT NULL,attribute VARCHAR(64) NOT NULL,op CHAR(2) NOT NULL DEFAULT '=',value VARCHAR(253) NOT NULL);
CREATE TABLE IF NOT EXISTS radusergroup (id BIGINT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(128) NOT NULL,groupname VARCHAR(64) NOT NULL,priority INT NOT NULL DEFAULT 1,INDEX username_idx(username(64)));
CREATE TABLE IF NOT EXISTS nas (id INT AUTO_INCREMENT PRIMARY KEY,nasname VARCHAR(128) NOT NULL UNIQUE,shortname VARCHAR(64),type VARCHAR(30) DEFAULT 'other',ports INT,secret VARCHAR(253) NOT NULL,server VARCHAR(64),community VARCHAR(64),description VARCHAR(200));
CREATE TABLE IF NOT EXISTS radacct (radacctid BIGINT AUTO_INCREMENT PRIMARY KEY,acctsessionid VARCHAR(128) NOT NULL,acctuniqueid VARCHAR(64) NOT NULL UNIQUE,username VARCHAR(128),realm VARCHAR(64),nasipaddress VARCHAR(64) NOT NULL,nasportid VARCHAR(32),nasporttype VARCHAR(32),acctstarttime DATETIME NULL,acctupdatetime DATETIME NULL,acctstoptime DATETIME NULL,acctinterval INT NULL,acctsessiontime BIGINT NULL,acctauthentic VARCHAR(32),connectinfo_start VARCHAR(128),connectinfo_stop VARCHAR(128),acctinputoctets BIGINT NOT NULL DEFAULT 0,acctoutputoctets BIGINT NOT NULL DEFAULT 0,calledstationid VARCHAR(64),callingstationid VARCHAR(64),acctterminatecause VARCHAR(64),servicetype VARCHAR(32),framedprotocol VARCHAR(32),framedipaddress VARCHAR(64),framedipv6address VARCHAR(64),framedipv6prefix VARCHAR(64),framedinterfaceid VARCHAR(64),delegatedipv6prefix VARCHAR(64),class VARCHAR(128), INDEX user_idx(username),INDEX nas_idx(nasipaddress),INDEX session_active_idx(acctstoptime));
CREATE TABLE IF NOT EXISTS radpostauth (id BIGINT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(128) NOT NULL,pass VARCHAR(253),reply VARCHAR(32),authdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, class VARCHAR(128), INDEX username_idx(username),INDEX authdate_idx(authdate));
