-- Tables required by the radadmin API that are NOT part of the FreeRADIUS
-- schema. FreeRADIUS never reads or writes these; they hold billing status and
-- human-only metadata (name/phone/alias). Import this after the FreeRADIUS
-- schema.sql.

CREATE TABLE IF NOT EXISTS customers (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username    VARCHAR(64) NOT NULL,
  mac_address VARCHAR(17) NOT NULL,
  status      VARCHAR(10) NOT NULL DEFAULT 'new',
  created_at  DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

-- Soft delete: deleting a client hard-removes its radcheck/radusergroup/customers
-- rows but only SETS deleted_at here, so the human metadata survives and can be
-- restored/referenced. deleted_at IS NULL means "live". Uniqueness on mac_address
-- is enforced only among live rows via the generated active_mac column (NULL for
-- soft-deleted rows — MySQL/MariaDB allows many NULLs in a UNIQUE index), so a
-- re-added client coexists with any number of old soft-deleted copies.
-- groupname/status mirror the client's current group (radusergroup) and billing
-- status (customers): populated on add and kept in sync on edit. They stay on the
-- row when the client is soft-deleted, so a deleted client's group/status aren't
-- lost along with its hard-deleted RADIUS/billing rows.
CREATE TABLE IF NOT EXISTS radadmin_clients (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  mac_address VARCHAR(17) NOT NULL,
  name        VARCHAR(128) NULL,
  phone       VARCHAR(32) NULL,
  alias       VARCHAR(64) NULL,
  groupname   VARCHAR(64) NULL,
  status      VARCHAR(10) NULL,
  created_at  DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at  DATETIME NULL DEFAULT NULL,
  active_mac  VARCHAR(17) AS (IF(deleted_at IS NULL, mac_address, NULL)) STORED,
  PRIMARY KEY (id),
  UNIQUE KEY uq_radadmin_clients_active_mac (active_mac)
);

-- Soft-delete migration for radadmin_clients tables created before this change
-- (MariaDB syntax; idempotent, and a no-op on the fresh schema created above).
ALTER TABLE radadmin_clients
  ADD COLUMN IF NOT EXISTS groupname VARCHAR(64) NULL AFTER alias;
ALTER TABLE radadmin_clients
  ADD COLUMN IF NOT EXISTS status VARCHAR(10) NULL AFTER groupname;
ALTER TABLE radadmin_clients
  ADD COLUMN IF NOT EXISTS deleted_at DATETIME NULL DEFAULT NULL AFTER created_at;
ALTER TABLE radadmin_clients
  ADD COLUMN IF NOT EXISTS active_mac VARCHAR(17)
    AS (IF(deleted_at IS NULL, mac_address, NULL)) STORED;
ALTER TABLE radadmin_clients
  DROP INDEX IF EXISTS uq_radadmin_clients_mac;
ALTER TABLE radadmin_clients
  ADD UNIQUE KEY IF NOT EXISTS uq_radadmin_clients_active_mac (active_mac);

CREATE TABLE IF NOT EXISTS radadmin_devices (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  mac_address VARCHAR(17) NOT NULL,
  alias       VARCHAR(64) NULL,
  created_at  DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_radadmin_devices_mac (mac_address)
);

-- ---------------------------------------------------------------------------
-- Admin portal auth. Replaces the old single shared X-API-Key env secret with
-- real per-user logins, roles, revocable API keys, and an activity log.
--
-- The API seeds a default "admin" / "admin" account on first startup when
-- radadmin_admins is empty (must_change_password = 1 forces a reset on the
-- first login). No password hashes are stored in this file so the seed always
-- matches the app's hashing scheme.
-- ---------------------------------------------------------------------------

-- Admin portal users. password_hash is PBKDF2-HMAC-SHA256, formatted as
-- "pbkdf2_sha256$<iterations>$<salt_hex>$<hash_hex>". is_admin grants user
-- management, API-key management and activity-log access.
CREATE TABLE IF NOT EXISTS radadmin_admins (
  id                   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username             VARCHAR(64) NOT NULL,
  password_hash        VARCHAR(255) NOT NULL,
  is_admin             TINYINT(1) NOT NULL DEFAULT 0,
  must_change_password TINYINT(1) NOT NULL DEFAULT 0,
  created_at           DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_radadmin_admins_username (username)
);

-- Seed the default admin/admin login at import time so it exists regardless of
-- whether the API has started yet. INSERT IGNORE makes this idempotent: it is
-- skipped once an 'admin' row exists, so it never clobbers a changed password.
-- The hash below is PBKDF2-HMAC-SHA256 of "admin"; must_change_password = 1
-- forces a reset on first sign-in. (The API also seeds this on first startup.)
INSERT IGNORE INTO radadmin_admins (username, password_hash, is_admin, must_change_password)
VALUES (
  'admin',
  'pbkdf2_sha256$240000$b03a4d5fdc007ba832d5302dc787666d$f1c1a75c58af12402c2c9f2517ca2a89ab13eb5b8613929fb60f408559e48e79',
  1, 1
);

-- Opaque login sessions. token is a random secret handed to the browser and
-- sent back as "Authorization: Bearer <token>". Rows are deleted on logout and
-- ignored/cleaned once expires_at passes.
CREATE TABLE IF NOT EXISTS radadmin_sessions (
  token       CHAR(64) NOT NULL,
  admin_id    INT UNSIGNED NOT NULL,
  created_at  DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at  DATETIME NOT NULL,
  PRIMARY KEY (token),
  KEY idx_radadmin_sessions_admin (admin_id),
  CONSTRAINT fk_radadmin_sessions_admin
    FOREIGN KEY (admin_id) REFERENCES radadmin_admins (id) ON DELETE CASCADE
);

-- Programmatic API keys (created by admins only). The raw key is shown once at
-- creation; only its PBKDF2 hash is stored. key_prefix is the first few
-- characters, kept in clear so keys are identifiable in the UI. Clients send
-- the raw key in the X-API-Key header.
CREATE TABLE IF NOT EXISTS radadmin_api_keys (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name         VARCHAR(64) NOT NULL,
  key_prefix   VARCHAR(16) NOT NULL,
  key_hash     VARCHAR(255) NOT NULL,
  created_by   VARCHAR(64) NULL,
  created_at   DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  last_used_at DATETIME NULL,
  revoked      TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

-- Activity log: one row per state-changing action or auth event, with the
-- acting username, what they did, and where from. Admin-only in the UI.
CREATE TABLE IF NOT EXISTS radadmin_logs (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username   VARCHAR(64) NULL,
  action     VARCHAR(64) NOT NULL,
  detail     VARCHAR(512) NULL,
  ip_address VARCHAR(45) NULL,
  created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_radadmin_logs_username (username),
  KEY idx_radadmin_logs_created (created_at)
);
