-- Device registry: per-device toggles and app lock.
-- Creates both "devices" and "ftt_devices" so ftt_table('devices') works with any prefix.
-- Adds device_id to 001 tables: app_logs, crash_logs, reports.
-- For ftt_* tables (002), use migration 004.

CREATE TABLE IF NOT EXISTS devices (
  device_id VARCHAR(64) NOT NULL PRIMARY KEY,
  label VARCHAR(255) NULL,
  first_seen DATETIME NOT NULL,
  last_seen DATETIME NOT NULL,
  last_ip VARCHAR(45) NULL,
  app_enabled TINYINT(1) NOT NULL DEFAULT 1,
  email_enabled TINYINT(1) NOT NULL DEFAULT 1,
  webhook_enabled TINYINT(1) NOT NULL DEFAULT 1,
  logging_enabled TINYINT(1) NOT NULL DEFAULT 1,
  reports_enabled TINYINT(1) NOT NULL DEFAULT 1,
  unlock_code_hash VARCHAR(64) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_devices_last_seen (last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ftt_devices (
  device_id VARCHAR(64) NOT NULL PRIMARY KEY,
  label VARCHAR(255) NULL,
  first_seen DATETIME NOT NULL,
  last_seen DATETIME NOT NULL,
  last_ip VARCHAR(45) NULL,
  app_enabled TINYINT(1) NOT NULL DEFAULT 1,
  email_enabled TINYINT(1) NOT NULL DEFAULT 1,
  webhook_enabled TINYINT(1) NOT NULL DEFAULT 1,
  logging_enabled TINYINT(1) NOT NULL DEFAULT 1,
  reports_enabled TINYINT(1) NOT NULL DEFAULT 1,
  unlock_code_hash VARCHAR(64) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_devices_last_seen (last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add device_id to 001 tables (app_logs, crash_logs, reports)
ALTER TABLE app_logs ADD COLUMN device_id VARCHAR(64) NULL DEFAULT NULL AFTER install_uuid;
ALTER TABLE crash_logs ADD COLUMN device_id VARCHAR(64) NULL DEFAULT NULL AFTER install_uuid;
ALTER TABLE reports ADD COLUMN device_id VARCHAR(64) NULL DEFAULT NULL AFTER user_token_hash;

CREATE INDEX idx_app_logs_device ON app_logs (device_id);
CREATE INDEX idx_crash_logs_device ON crash_logs (device_id);
CREATE INDEX idx_reports_device ON reports (device_id);