CREATE TABLE IF NOT EXISTS ti_mobile_config (
    id TINYINT UNSIGNED NOT NULL PRIMARY KEY DEFAULT 1,
    enrollment_hash CHAR(64) NULL,
    enrollment_expires_at DATETIME NULL,
    enrollment_uses_remaining SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    sync_interval_min SMALLINT UNSIGNED NOT NULL DEFAULT 30,
    recent_hours SMALLINT UNSIGNED NOT NULL DEFAULT 2,
    silent_hours SMALLINT UNSIGNED NOT NULL DEFAULT 24,
    offline_alert_hours SMALLINT UNSIGNED NOT NULL DEFAULT 168,
    storage_critical_percent TINYINT UNSIGNED NOT NULL DEFAULT 10,
    security_patch_max_age_days SMALLINT UNSIGNED NOT NULL DEFAULT 365,
    min_android_sdk SMALLINT UNSIGNED NOT NULL DEFAULT 29,
    current_app_version VARCHAR(40) NOT NULL DEFAULT '1.0.0',
    current_app_version_code INT UNSIGNED NOT NULL DEFAULT 1,
    collect_apps TINYINT(1) NOT NULL DEFAULT 1,
    api_enabled TINYINT(1) NOT NULL DEFAULT 1,
    updated_by VARCHAR(120) NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO ti_mobile_config (id) VALUES (1);

CREATE TABLE IF NOT EXISTS ti_mobile_dispositivos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agent_id VARCHAR(40) NULL,
    token_hash CHAR(64) NOT NULL,
    token_emitido_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    fingerprint_hash CHAR(64) NOT NULL,
    android_id_hash CHAR(64) NULL,
    tipo_dispositivo ENUM('TABLET','SMARTPHONE','OUTRO') NOT NULL DEFAULT 'TABLET',
    patrimonio VARCHAR(60) NULL,
    numero_serie_manual VARCHAR(120) NULL,
    imei_manual VARCHAR(40) NULL,
    responsavel_nome VARCHAR(160) NULL,
    setor VARCHAR(180) NULL,
    observacoes VARCHAR(1000) NULL,
    fabricante VARCHAR(120) NULL,
    marca VARCHAR(120) NULL,
    modelo VARCHAR(180) NULL,
    device_name VARCHAR(180) NULL,
    android_version VARCHAR(40) NULL,
    sdk_int SMALLINT UNSIGNED NULL,
    security_patch VARCHAR(30) NULL,
    build_id VARCHAR(120) NULL,
    build_fingerprint VARCHAR(500) NULL,
    arquitetura VARCHAR(120) NULL,
    cpu_cores SMALLINT UNSIGNED NULL,
    ram_total_mb BIGINT UNSIGNED NULL,
    ram_disponivel_mb BIGINT UNSIGNED NULL,
    armazenamento_total_mb BIGINT UNSIGNED NULL,
    armazenamento_usado_mb BIGINT UNSIGNED NULL,
    armazenamento_livre_mb BIGINT UNSIGNED NULL,
    bateria_percentual TINYINT UNSIGNED NULL,
    bateria_carregando TINYINT(1) NULL,
    bateria_temperatura DECIMAL(5,1) NULL,
    bateria_status VARCHAR(60) NULL,
    bateria_fonte VARCHAR(60) NULL,
    rede_tipo VARCHAR(60) NULL,
    ip_local VARCHAR(80) NULL,
    resolucao_tela VARCHAR(80) NULL,
    densidade_tela VARCHAR(80) NULL,
    app_version VARCHAR(40) NULL,
    app_version_code INT UNSIGNED NULL,
    apps_hash CHAR(64) NULL,
    vinculo_smartphone_id INT UNSIGNED NULL,
    vinculo_ativo_id BIGINT UNSIGNED NULL,
    status ENUM('ATIVO','PENDENTE','REVOGADO') NOT NULL DEFAULT 'ATIVO',
    revogado_em DATETIME NULL,
    revogado_por VARCHAR(120) NULL,
    primeira_comunicacao DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ultima_comunicacao DATETIME NULL,
    ultima_sincronizacao_completa DATETIME NULL,
    ultimo_ip_remoto VARCHAR(45) NULL,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_mobile_agent_id (agent_id),
    UNIQUE KEY uk_mobile_fingerprint (fingerprint_hash),
    INDEX idx_mobile_patrimonio (patrimonio),
    INDEX idx_mobile_tipo (tipo_dispositivo),
    INDEX idx_mobile_status_seen (status, ultima_comunicacao),
    INDEX idx_mobile_smartphone (vinculo_smartphone_id),
    INDEX idx_mobile_ativo (vinculo_ativo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ti_mobile_sincronizacoes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dispositivo_id BIGINT UNSIGNED NOT NULL,
    coletado_em DATETIME NOT NULL,
    recebido_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resumo_hash CHAR(64) NULL,
    payload_json LONGTEXT NULL,
    ip_remoto VARCHAR(45) NULL,
    app_version VARCHAR(40) NULL,
    app_version_code INT UNSIGNED NULL,
    INDEX idx_mobile_sync_device (dispositivo_id, recebido_em),
    CONSTRAINT fk_mobile_sync_device FOREIGN KEY (dispositivo_id)
        REFERENCES ti_mobile_dispositivos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ti_mobile_eventos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dispositivo_id BIGINT UNSIGNED NOT NULL,
    tipo VARCHAR(80) NOT NULL,
    severidade ENUM('INFO','ATENCAO','CRITICO') NOT NULL DEFAULT 'INFO',
    titulo VARCHAR(220) NOT NULL,
    valor_anterior TEXT NULL,
    valor_novo TEXT NULL,
    detalhes TEXT NULL,
    origem VARCHAR(40) NOT NULL DEFAULT 'AGENTE',
    status_revisao ENUM('ABERTO','RECONHECIDO','RESOLVIDO') NOT NULL DEFAULT 'ABERTO',
    reconhecido_por VARCHAR(120) NULL,
    reconhecido_em DATETIME NULL,
    observacao_revisao TEXT NULL,
    resolvido TINYINT(1) NOT NULL DEFAULT 0,
    resolvido_por VARCHAR(120) NULL,
    resolvido_em DATETIME NULL,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_mobile_event_device (dispositivo_id, criado_em),
    INDEX idx_mobile_event_open (resolvido, severidade, criado_em),
    INDEX idx_mobile_event_review (status_revisao, criado_em),
    CONSTRAINT fk_mobile_event_device FOREIGN KEY (dispositivo_id)
        REFERENCES ti_mobile_dispositivos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ti_mobile_apps (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dispositivo_id BIGINT UNSIGNED NOT NULL,
    package_name VARCHAR(220) NOT NULL,
    app_label VARCHAR(220) NULL,
    version_name VARCHAR(120) NULL,
    version_code BIGINT NULL,
    instalado TINYINT(1) NOT NULL DEFAULT 1,
    obrigatorio TINYINT(1) NOT NULL DEFAULT 0,
    system_app TINYINT(1) NOT NULL DEFAULT 0,
    primeira_deteccao DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ultima_deteccao DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    UNIQUE KEY uk_mobile_app_device_package (dispositivo_id, package_name),
    INDEX idx_mobile_app_package (package_name, ativo),
    CONSTRAINT fk_mobile_app_device FOREIGN KEY (dispositivo_id)
        REFERENCES ti_mobile_dispositivos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ti_mobile_apps_catalogo (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    package_name VARCHAR(220) NOT NULL,
    app_label VARCHAR(220) NOT NULL,
    obrigatorio TINYINT(1) NOT NULL DEFAULT 0,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_mobile_catalog_package (package_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO ti_mobile_apps_catalogo(package_name,app_label,obrigatorio) VALUES
('com.android.chrome','Google Chrome',1),
('com.microsoft.teams','Microsoft Teams',0),
('br.gov.meugovbr','Gov.br',0),
('com.anydesk.anydeskandroid','AnyDesk',0),
('com.adobe.reader','Adobe Acrobat',0);

CREATE TABLE IF NOT EXISTS ti_mobile_nonces (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dispositivo_id BIGINT UNSIGNED NOT NULL,
    nonce CHAR(64) NOT NULL,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_mobile_nonce (dispositivo_id, nonce),
    INDEX idx_mobile_nonce_date (criado_em),
    CONSTRAINT fk_mobile_nonce_device FOREIGN KEY (dispositivo_id)
        REFERENCES ti_mobile_dispositivos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ti_mobile_rate_limits (
    chave_hash CHAR(64) NOT NULL,
    janela_inicio DATETIME NOT NULL,
    tentativas SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    PRIMARY KEY (chave_hash),
    INDEX idx_mobile_rate_window (janela_inicio)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ti_mobile_auditoria (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dispositivo_id BIGINT UNSIGNED NULL,
    acao VARCHAR(80) NOT NULL,
    ator VARCHAR(160) NULL,
    ip_remoto VARCHAR(45) NULL,
    detalhes TEXT NULL,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_mobile_audit_device (dispositivo_id, criado_em),
    INDEX idx_mobile_audit_action (acao, criado_em)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
