CREATE DATABASE radius;
GRANT ALL PRIVILEGES ON radius.* TO radius@localhost IDENTIFIED BY 'password';
CREATE OR REPLACE VIEW radius.radcheck AS
  SELECT
    CONV(SUBSTRING(CAST(SHA(CONCAT(u.usuario, '|Crypt-Password')) AS CHAR), 1, 16), 16, 10) AS id,
    u.usuario AS username,
    'Crypt-Password' AS attribute,
    ':=' AS op,
    u.contrasenia AS value
  FROM appdb.usuario AS u, appdb.grupo AS g, appdb.usuario_grupo AS ug
  WHERE
    u.id = ug.usuario
    AND ug.grupo = g.id
    AND g.grupo = 'radius'
    AND g.activo = true
    AND u.activo = true
;
CREATE OR REPLACE VIEW radius.radreply AS
  SELECT CONV(SUBSTRING(CAST(SHA(CONCAT(u.usuario, '|Reply-Message')) AS CHAR), 1, 16), 16, 10) AS id,
    u.usuario AS username,
    'Reply-Message' AS attribute,
    ':=' AS op,
    CONCAT('Hola ', u.nombre) AS value
  FROM appdb.usuario AS u, appdb.grupo AS g, appdb.usuario_grupo AS ug
  WHERE
    u.id = ug.usuario
    AND ug.grupo = g.id
    AND g.grupo = 'radius'
    AND g.activo = true
    AND u.activo = true
;