import subprocess import sys import os # Instalar dependências via pip (funciona sem uv) print("[v0] Instalando pymysql e bcrypt via pip...") subprocess.check_call([sys.executable, "-m", "pip", "install", "pymysql", "bcrypt", "--quiet"]) import pymysql import bcrypt # --- Configuração via variáveis de ambiente --- DB_CONFIG = { "host": os.environ.get("MYSQL_HOST", "localhost"), "port": int(os.environ.get("MYSQL_PORT", 3306)), "user": os.environ.get("MYSQL_USER", "root"), "password": os.environ.get("MYSQL_PASSWORD", ""), "database": os.environ.get("MYSQL_DATABASE", "admanager"), "charset": "utf8mb4", "connect_timeout": 15, } # --- Tabelas em ordem de dependência --- TABLES = [ ("tenants", """ CREATE TABLE IF NOT EXISTS tenants ( id VARCHAR(36) NOT NULL DEFAULT (UUID()), name VARCHAR(255) NOT NULL, domain VARCHAR(255) NOT NULL, status ENUM('active','inactive','pending','blocked') NOT NULL DEFAULT 'pending', agent_status ENUM('online','offline','warning','unknown') NOT NULL DEFAULT 'unknown', last_heartbeat DATETIME NULL, agent_version VARCHAR(32) NULL, agent_hostname VARCHAR(255) NULL, agent_token VARCHAR(255) NULL, users_count INT NOT NULL DEFAULT 0, groups_count INT NOT NULL DEFAULT 0, base_dn VARCHAR(500) NOT NULL DEFAULT '', default_user_ou VARCHAR(500) NOT NULL DEFAULT '', default_group_ou VARCHAR(500) NOT NULL DEFAULT '', preferred_dc VARCHAR(255) NOT NULL DEFAULT '', ldap_port SMALLINT NOT NULL DEFAULT 389, use_ssl TINYINT(1) NOT NULL DEFAULT 0, upn_suffix VARCHAR(255) NOT NULL DEFAULT '', ad_service_user VARCHAR(255) NOT NULL DEFAULT '', 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_domain (domain), INDEX idx_status (status), INDEX idx_agent_status (agent_status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """), ("portal_users", """ CREATE TABLE IF NOT EXISTS portal_users ( id VARCHAR(36) NOT NULL DEFAULT (UUID()), tenant_id VARCHAR(36) NULL, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, password_hash VARCHAR(255) NOT NULL, role ENUM('admin_global','tenant_admin','tenant_operator','tenant_viewer') NOT NULL DEFAULT 'tenant_viewer', status ENUM('active','inactive','pending','blocked') NOT NULL DEFAULT 'active', mfa_enabled TINYINT(1) NOT NULL DEFAULT 0, mfa_secret VARCHAR(255) NULL, last_login DATETIME 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_email (email), INDEX idx_tenant_id (tenant_id), INDEX idx_role (role), INDEX idx_status (status), CONSTRAINT fk_portal_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """), ("portal_sessions", """ CREATE TABLE IF NOT EXISTS portal_sessions ( id VARCHAR(36) NOT NULL DEFAULT (UUID()), user_id VARCHAR(36) NOT NULL, token_hash VARCHAR(255) NOT NULL, ip_address VARCHAR(45) NULL, user_agent TEXT NULL, expires_at DATETIME NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_token (token_hash), INDEX idx_user_id (user_id), CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES portal_users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """), ("portal_user_permissions", """ CREATE TABLE IF NOT EXISTS portal_user_permissions ( id INT NOT NULL AUTO_INCREMENT, user_id VARCHAR(36) NOT NULL, module VARCHAR(100) NOT NULL, actions JSON NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_user_module (user_id, module), INDEX idx_user_id (user_id), CONSTRAINT fk_permissions_user FOREIGN KEY (user_id) REFERENCES portal_users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """), ("ad_users", """ CREATE TABLE IF NOT EXISTS ad_users ( id VARCHAR(36) NOT NULL DEFAULT (UUID()), tenant_id VARCHAR(36) NOT NULL, sam_account_name VARCHAR(255) NOT NULL, upn VARCHAR(500) NOT NULL DEFAULT '', display_name VARCHAR(255) NOT NULL, first_name VARCHAR(255) NOT NULL DEFAULT '', last_name VARCHAR(255) NOT NULL DEFAULT '', email VARCHAR(500) NOT NULL DEFAULT '', department VARCHAR(255) NULL, title VARCHAR(255) NULL, phone VARCHAR(100) NULL, ou VARCHAR(1000) NOT NULL DEFAULT '', enabled TINYINT(1) NOT NULL DEFAULT 1, password_never_expires TINYINT(1) NOT NULL DEFAULT 0, must_change_password TINYINT(1) NOT NULL DEFAULT 0, cannot_change_password TINYINT(1) NOT NULL DEFAULT 0, object_guid VARCHAR(255) NULL, distinguished_name VARCHAR(1000) NULL, last_sync DATETIME 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_guid (object_guid), UNIQUE KEY uq_tenant_sam (tenant_id, sam_account_name), INDEX idx_tenant_id (tenant_id), INDEX idx_enabled (enabled), CONSTRAINT fk_ad_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """), ("ad_groups", """ CREATE TABLE IF NOT EXISTS ad_groups ( id VARCHAR(36) NOT NULL DEFAULT (UUID()), tenant_id VARCHAR(36) NOT NULL, sam_account_name VARCHAR(255) NOT NULL, display_name VARCHAR(255) NOT NULL, description TEXT NULL, dn VARCHAR(1000) NOT NULL DEFAULT '', members_count INT NOT NULL DEFAULT 0, protected TINYINT(1) NOT NULL DEFAULT 0, object_guid VARCHAR(255) NULL, last_sync DATETIME 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_guid (object_guid), UNIQUE KEY uq_tenant_sam (tenant_id, sam_account_name), INDEX idx_tenant_id (tenant_id), CONSTRAINT fk_ad_groups_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """), ("ad_group_members", """ CREATE TABLE IF NOT EXISTS ad_group_members ( group_id VARCHAR(36) NOT NULL, user_id VARCHAR(36) NOT NULL, added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (group_id, user_id), CONSTRAINT fk_gm_group FOREIGN KEY (group_id) REFERENCES ad_groups(id) ON DELETE CASCADE, CONSTRAINT fk_gm_user FOREIGN KEY (user_id) REFERENCES ad_users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """), ("agent_tasks", """ CREATE TABLE IF NOT EXISTS agent_tasks ( id VARCHAR(36) NOT NULL DEFAULT (UUID()), tenant_id VARCHAR(36) NOT NULL, type VARCHAR(100) NOT NULL, status ENUM('pending','running','success','error','cancelled') NOT NULL DEFAULT 'pending', priority TINYINT NOT NULL DEFAULT 5, payload JSON NOT NULL, result JSON NULL, error_message TEXT NULL, retry_count TINYINT NOT NULL DEFAULT 0, max_retries TINYINT NOT NULL DEFAULT 3, created_by VARCHAR(36) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, started_at DATETIME NULL, completed_at DATETIME NULL, PRIMARY KEY (id), INDEX idx_tenant_id (tenant_id), INDEX idx_status (status), INDEX idx_created_at (created_at), CONSTRAINT fk_tasks_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """), ("audit_logs", """ CREATE TABLE IF NOT EXISTS audit_logs ( id VARCHAR(36) NOT NULL DEFAULT (UUID()), tenant_id VARCHAR(36) NULL, tenant_name VARCHAR(255) NULL, user_id VARCHAR(36) NULL, user_name VARCHAR(255) NOT NULL DEFAULT 'system', action VARCHAR(100) NOT NULL, object_type VARCHAR(100) NOT NULL, object_id VARCHAR(255) NOT NULL DEFAULT '', object_name VARCHAR(255) NOT NULL DEFAULT '', details TEXT NULL, ip_address VARCHAR(45) NULL, status ENUM('success','error') NOT NULL DEFAULT 'success', task_id VARCHAR(36) NULL, agent_response TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_tenant_id (tenant_id), INDEX idx_user_id (user_id), INDEX idx_action (action), INDEX idx_created_at (created_at), INDEX idx_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """), ("sync_history", """ CREATE TABLE IF NOT EXISTS sync_history ( id INT NOT NULL AUTO_INCREMENT, tenant_id VARCHAR(36) NOT NULL, sync_type ENUM('full','partial','delta') NOT NULL DEFAULT 'full', status ENUM('running','success','error') NOT NULL, users_synced INT NOT NULL DEFAULT 0, groups_synced INT NOT NULL DEFAULT 0, error_message TEXT NULL, started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, completed_at DATETIME NULL, PRIMARY KEY (id), INDEX idx_tenant_id (tenant_id), INDEX idx_started_at (started_at), CONSTRAINT fk_sync_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """), ("tenant_policies", """ CREATE TABLE IF NOT EXISTS tenant_policies ( tenant_id VARCHAR(36) NOT NULL, must_change_password_on_first TINYINT(1) NOT NULL DEFAULT 1, cannot_change_password TINYINT(1) NOT NULL DEFAULT 0, password_never_expires TINYINT(1) NOT NULL DEFAULT 0, account_starts_enabled TINYINT(1) NOT NULL DEFAULT 1, operator_can_choose_must_change TINYINT(1) NOT NULL DEFAULT 1, operator_can_choose_cannot_change TINYINT(1) NOT NULL DEFAULT 0, operator_can_choose_never_expires TINYINT(1) NOT NULL DEFAULT 1, operator_can_choose_enabled TINYINT(1) NOT NULL DEFAULT 1, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (tenant_id), CONSTRAINT fk_policy_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """), ("protected_objects", """ CREATE TABLE IF NOT EXISTS protected_objects ( id INT NOT NULL AUTO_INCREMENT, tenant_id VARCHAR(36) NOT NULL, object_type ENUM('group','ou','user') NOT NULL, identifier VARCHAR(500) NOT NULL, description VARCHAR(255) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_tenant_id (tenant_id), CONSTRAINT fk_protected_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci """), ] def main(): print(f"[v0] Conectando ao MySQL: {DB_CONFIG['user']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}") try: conn = pymysql.connect(**DB_CONFIG) except Exception as e: print(f"[v0] ERRO ao conectar: {e}") sys.exit(1) print("[v0] Conexao estabelecida com sucesso!") try: with conn.cursor() as cur: cur.execute("SET FOREIGN_KEY_CHECKS=0") conn.commit() print(f"\n[v0] Criando {len(TABLES)} tabelas...\n") for table_name, ddl in TABLES: try: cur.execute(ddl.strip()) conn.commit() print(f"[v0] OK => {table_name}") except Exception as e: print(f"[v0] ERRO => {table_name}: {e}") conn.rollback() sys.exit(1) cur.execute("SET FOREIGN_KEY_CHECKS=1") conn.commit() # Seed: admin global print("\n[v0] Inserindo admin global padrao...") password_hash = bcrypt.hashpw(b"Admin@123", bcrypt.gensalt(12)).decode() cur.execute(""" INSERT INTO portal_users (id, tenant_id, name, email, password_hash, role, status, mfa_enabled) VALUES ('usr-admin-global-001', NULL, 'Administrador Global', 'admin@admanager.local', %s, 'admin_global', 'active', 0) ON DUPLICATE KEY UPDATE name = VALUES(name), password_hash = VALUES(password_hash), role = VALUES(role), status = VALUES(status) """, (password_hash,)) conn.commit() print("[v0] OK => portal_users (admin global inserido/atualizado)") print("\n[v0] ============================================") print("[v0] Migracao concluida com sucesso!") print(f"[v0] Banco: {DB_CONFIG['database']} em {DB_CONFIG['host']}") print("[v0]") print("[v0] Credenciais do admin padrao:") print("[v0] Email : admin@admanager.local") print("[v0] Senha : Admin@123") print("[v0] Role : admin_global") print("[v0] ============================================") finally: conn.close() if __name__ == "__main__": main()