-- Migration 9: Dispositivos, apps e configuracao remota
-- Source: backend/db/dump/19052026_nextbar.sql

-- -------------------------------------------------------- 9. Dispositivos, apps e configuracao remota
--
-- Estrutura para tabela `dispositivos`
--

CREATE TABLE `dispositivos` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  device_identifier VARCHAR(200) NOT NULL,
  device_type VARCHAR(200),
  gateway VARCHAR(200),
  model VARCHAR(200),
  serial_number VARCHAR(200),
  app_version VARCHAR(200),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status TINYINT(1) NOT NULL DEFAULT 1,
  trash TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
--
-- Estrutura para tabela `historico_status_dispositivos`
--

CREATE TABLE `historico_status_dispositivos` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  device_id INT NOT NULL,
  previous_status TINYINT(1),
  new_status TINYINT(1),
  motivation VARCHAR(200),
  changed_by INT(11),
  modified_on TIMESTAMP NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status TINYINT(1) NOT NULL DEFAULT 1,
  trash TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (device_id) REFERENCES dispositivos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
--
-- Estrutura para tabela `app_version`
--

CREATE TABLE `app_version` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  app_name VARCHAR(200),
  platform VARCHAR(200),
  version VARCHAR(200) NOT NULL,
  build_number INT(11), 
  mandatory_minimum INT(11),
  release_note TEXT, 
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status TINYINT(1) NOT NULL DEFAULT 1,
  trash TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
--
-- Estrutura para tabela `feature_flags`
--

CREATE TABLE `feature_flags` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  feature_key VARCHAR(100) NOT NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  value INT(11),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status TINYINT(1) NOT NULL DEFAULT 1,
  trash TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
--
-- Estrutura para tabela `feature_flags_tenant`
--

CREATE TABLE `feature_flags_tenant` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  feature_flag_id INT NOT NULL,
  tenant_id INT NOT NULL,
  event_id INT NOT NULL,
  feature_key VARCHAR(100) NOT NULL,
  value INT(11),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status TINYINT(1) NOT NULL DEFAULT 1,
  trash TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id),
  FOREIGN KEY (feature_flag_id) REFERENCES feature_flags(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
--
-- Estrutura para tabela `configuraçoes_remotas`
--

CREATE TABLE `configuracoes_remotas` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  event_id INT NOT NULL,
  config_key VARCHAR(200) NOT NULL,
  config_value VARCHAR(200) NOT NULL,
  config_version VARCHAR(200),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status TINYINT(1) NOT NULL DEFAULT 1,
  trash TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
--
-- Estrutura para tabela `versoes_config_dispositivo`
--

CREATE TABLE `versoes_config_dispositivo` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  device_id INT NOT NULL,
  config_version VARCHAR(200) NOT NULL,
  applied_at TIMESTAMP NOT NULL,
  applied_by INT(11),
  error TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status TINYINT(1) NOT NULL DEFAULT 1,
  trash TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (device_id) REFERENCES dispositivos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Estrutura para tabela `tokens_dispositivo`
--

CREATE TABLE `tokens_dispositivo` (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  device_id INT NOT NULL,
  token_hash VARCHAR(200) NOT NULL,
  expires_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status TINYINT(1) NOT NULL DEFAULT 1,
  trash TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (device_id) REFERENCES dispositivos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;