CREATE TABLE nexus_users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(190) DEFAULT '',
  plan VARCHAR(64) NOT NULL DEFAULT 'standard',
  status ENUM('active', 'disabled') NOT NULL DEFAULT 'active',
  device_hash CHAR(64) DEFAULT NULL,
  device_label VARCHAR(190) DEFAULT '',
  expires_at DATETIME DEFAULT NULL,
  activated_at DATETIME DEFAULT NULL,
  last_seen_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_nexus_users_email (email),
  KEY idx_nexus_users_status (status),
  KEY idx_nexus_users_device_hash (device_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE nexus_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,
  device_hash CHAR(64) NOT NULL,
  device_label VARCHAR(190) DEFAULT '',
  app_version VARCHAR(50) DEFAULT '',
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_nexus_sessions_token_hash (token_hash),
  KEY idx_nexus_sessions_user_id (user_id),
  KEY idx_nexus_sessions_device_hash (device_hash),
  CONSTRAINT fk_nexus_sessions_user FOREIGN KEY (user_id) REFERENCES nexus_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE nexus_license_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED DEFAULT NULL,
  email VARCHAR(190) DEFAULT '',
  event VARCHAR(64) NOT NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  message VARCHAR(255) DEFAULT '',
  device_hash CHAR(64) DEFAULT NULL,
  ip_address VARCHAR(64) DEFAULT '',
  user_agent VARCHAR(255) DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_nexus_logs_user_id (user_id),
  KEY idx_nexus_logs_event (event),
  KEY idx_nexus_logs_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;