-- Chỉ dùng để chạy kiểm thử tự động (SQLite). Bản thật dùng schema.mysql.sql.
CREATE TABLE IF NOT EXISTS {p}users (
  id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT NOT NULL COLLATE NOCASE UNIQUE,
  password_hash TEXT NOT NULL, role TEXT NOT NULL, active INTEGER NOT NULL DEFAULT 1, created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS {p}login_attempts (
  id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL, ip TEXT NOT NULL, at TEXT NOT NULL, ok INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS {p}device_types (prefix TEXT NOT NULL PRIMARY KEY, name TEXT NOT NULL, counter INTEGER NOT NULL DEFAULT 0);
CREATE TABLE IF NOT EXISTS {p}locations (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL COLLATE NOCASE UNIQUE);
CREATE TABLE IF NOT EXISTS {p}departments (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL COLLATE NOCASE UNIQUE);
CREATE TABLE IF NOT EXISTS {p}vendors (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL COLLATE NOCASE UNIQUE);
CREATE TABLE IF NOT EXISTS {p}devices (
  code TEXT NOT NULL PRIMARY KEY, type TEXT NOT NULL, brand TEXT NOT NULL DEFAULT '', model TEXT NOT NULL DEFAULT '',
  serial TEXT NULL COLLATE NOCASE UNIQUE, hostname TEXT NULL COLLATE NOCASE UNIQUE, ip TEXT NULL UNIQUE, mac TEXT NULL COLLATE NOCASE UNIQUE,
  purchase_date TEXT NULL, vendor TEXT NOT NULL DEFAULT '', price INTEGER NULL, warranty_months INTEGER NULL,
  status TEXT NOT NULL, location TEXT NOT NULL, dept TEXT NOT NULL DEFAULT '', user_name TEXT NOT NULL DEFAULT '',
  uplink TEXT NULL, link TEXT NOT NULL DEFAULT '', note TEXT NOT NULL DEFAULT '', search_text TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS {p}transfers (
  id INTEGER PRIMARY KEY AUTOINCREMENT, moved_at TEXT NOT NULL, code TEXT NOT NULL, from_loc TEXT NOT NULL, to_loc TEXT NOT NULL,
  from_user TEXT NOT NULL DEFAULT '', to_user TEXT NOT NULL DEFAULT '', reason TEXT NOT NULL, done_by TEXT NOT NULL, created_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS {p}audit_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT, at TEXT NOT NULL, user_name TEXT NOT NULL, action TEXT NOT NULL, entity TEXT NOT NULL, detail TEXT NOT NULL);