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


CREATE TABLE IF NOT EXISTS radius_nas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NULL,
  nas_ip VARCHAR(45) NOT NULL UNIQUE,
  secret VARCHAR(100) NOT NULL,
  port INT NOT NULL DEFAULT 1812,
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  comment TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_nas_ip (nas_ip),
  INDEX idx_status (status)
);

INSERT INTO radius_nas (name, nas_ip, secret, port, status, comment)
VALUES ('MikroTik Main', '192.168.88.1', 'MySecret', 1812, 'active', 'Change NAS IP to your MikroTik source IP')
ON DUPLICATE KEY UPDATE secret=VALUES(secret), port=VALUES(port), status=VALUES(status);

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)
);

CREATE TABLE IF NOT EXISTS admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO admin_users (username, password_hash)
VALUES ('admin', '$2y$10$Qp3i2BI4wTgqvnb7xIQdm.1hI0Oq8z.O1e5EqZ6q5iaPP.Md7W14q')
ON DUPLICATE KEY UPDATE username=username;
-- 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;
