-- OTD140 LAN IP Manager schema
-- MySQL/MariaDB

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

CREATE TABLE IF NOT EXISTS admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Default admin login:
-- username: admin
-- password: ChangeMe!123
INSERT INTO admin_users (username, password_hash)
VALUES ('admin', '$2b$12$Jc/NVEQIRJPi9OluAu32wORFp9Y1x6rl2YjEKtpMrv3Lxv3EYYS8a')
ON DUPLICATE KEY UPDATE username=username;

CREATE TABLE IF NOT EXISTS devices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NULL,
  device_host VARCHAR(255) NOT NULL,
  auth_type ENUM('token','basic') NOT NULL DEFAULT 'token',
  api_user VARCHAR(64) NOT NULL,
  api_pass VARCHAR(128) NOT NULL,

  lan_if_id VARCHAR(64) NOT NULL DEFAULT 'lan',

  original_ip VARCHAR(64) NULL,
  original_netmask VARCHAR(64) NULL,

  temp_ip VARCHAR(64) NULL,
  temp_netmask VARCHAR(64) NULL,

  current_host VARCHAR(255) NULL,
  state ENUM('unknown','normal','changed') NOT NULL DEFAULT 'unknown',
  last_changed_at DATETIME NULL,

  notes TEXT NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS device_actions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id INT NOT NULL,
  action ENUM('fetch','change','revert') NOT NULL,
  ok TINYINT(1) NOT NULL DEFAULT 0,
  http_code INT NULL,
  message VARCHAR(255) NULL,
  request_json MEDIUMTEXT NULL,
  response_raw MEDIUMTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB;
