-- MPN Device: schema MySQL / MariaDB. {p} sẽ được thay bằng tiền tố bảng (mặc định mpn_).
-- Chạy được nhiều lần (IF NOT EXISTS). Mã hóa utf8mb4, không phân biệt hoa thường và dấu theo collation.

CREATE TABLE IF NOT EXISTS {p}users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(10) NOT NULL,
  active TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_{p}users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS {p}login_attempts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL,
  ip VARCHAR(45) NOT NULL,
  at DATETIME NOT NULL,
  ok TINYINT NOT NULL,
  KEY ix_{p}la (email, at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS {p}device_types (
  prefix VARCHAR(3) NOT NULL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  counter INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS {p}locations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  UNIQUE KEY uq_{p}locations (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS {p}departments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  UNIQUE KEY uq_{p}departments (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS {p}vendors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  UNIQUE KEY uq_{p}vendors (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cột serial, hostname, ip, mac để NULL khi trống: MySQL cho phép nhiều NULL trong cột UNIQUE.
CREATE TABLE IF NOT EXISTS {p}devices (
  code VARCHAR(20) NOT NULL PRIMARY KEY,
  type VARCHAR(3) NOT NULL,
  brand VARCHAR(100) NOT NULL DEFAULT '',
  model VARCHAR(150) NOT NULL DEFAULT '',
  serial VARCHAR(100) NULL,
  hostname VARCHAR(100) NULL,
  ip VARCHAR(45) NULL,
  mac VARCHAR(17) NULL,
  purchase_date DATE NULL,
  vendor VARCHAR(150) NOT NULL DEFAULT '',
  price BIGINT NULL,
  warranty_months INT NULL,
  status VARCHAR(20) NOT NULL,
  location VARCHAR(150) NOT NULL,
  dept VARCHAR(150) NOT NULL DEFAULT '',
  user_name VARCHAR(150) NOT NULL DEFAULT '',
  uplink VARCHAR(20) NULL,
  link VARCHAR(10) NOT NULL DEFAULT '',
  note TEXT NOT NULL,
  search_text TEXT NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uq_{p}dev_serial (serial),
  UNIQUE KEY uq_{p}dev_host (hostname),
  UNIQUE KEY uq_{p}dev_ip (ip),
  UNIQUE KEY uq_{p}dev_mac (mac),
  KEY ix_{p}dev_status (status),
  KEY ix_{p}dev_loc (location)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Lịch sử điều chuyển: chỉ thêm, không sửa hay xóa; không khóa ngoại để giữ lại khi thiết bị bị xóa.
CREATE TABLE IF NOT EXISTS {p}transfers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  moved_at DATE NOT NULL,
  code VARCHAR(20) NOT NULL,
  from_loc VARCHAR(150) NOT NULL,
  to_loc VARCHAR(150) NOT NULL,
  from_user VARCHAR(150) NOT NULL DEFAULT '',
  to_user VARCHAR(150) NOT NULL DEFAULT '',
  reason VARCHAR(500) NOT NULL,
  done_by VARCHAR(100) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY ix_{p}tr_code (code),
  KEY ix_{p}tr_date (moved_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS {p}audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  at DATETIME NOT NULL,
  user_name VARCHAR(100) NOT NULL,
  action VARCHAR(40) NOT NULL,
  entity VARCHAR(100) NOT NULL,
  detail TEXT NOT NULL,
  KEY ix_{p}audit_at (at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;