CREATE DATABASE IF NOT EXISTS radius_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE radius_db;

CREATE TABLE IF NOT EXISTS clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  mac VARCHAR(32) NOT NULL UNIQUE,
  ip_address VARCHAR(45) NOT NULL,
  rate_limit VARCHAR(100) NOT NULL DEFAULT '10M/10M',
  address_list VARCHAR(100) NOT NULL DEFAULT 'dhcp_clients',
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  session_timeout INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS radius_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  mac VARCHAR(32),
  router_ip VARCHAR(45),
  result VARCHAR(50),
  reply_ip VARCHAR(45),
  rate_limit VARCHAR(100),
  address_list VARCHAR(100),
  message TEXT,
  created_at DATETIME,
  INDEX idx_created_at (created_at),
  INDEX idx_mac (mac)
);
-- default: admin / admin123

INSERT INTO clients (mac, ip_address, rate_limit, address_list, status, session_timeout)
VALUES ('AA:BB:CC:DD:EE:FF', '10.10.10.50', '10M/10M', 'clients_10m', 'active', 0)
ON DUPLICATE KEY UPDATE ip_address=VALUES(ip_address), rate_limit=VALUES(rate_limit), address_list=VALUES(address_list), status=VALUES(status);


-- Optional migration for existing installations:
-- ALTER TABLE clients ADD COLUMN session_timeout INT NOT NULL DEFAULT 0;
-- ALTER TABLE clients DROP COLUMN customer_name, DROP COLUMN phone, DROP COLUMN comment;
