Understanding how supermassive black holes (SMBHs) grow and regulate the growth of their host galaxies requires tracing the cold molecular interstellar medium (ISM) throughout the full AGN feeding-feedback cycle. Molecular gas constitutes both the primary fuel for star formation and black-hole accretion and one of the ISM phases most directly affected by AGN-driven radiation, winds, and radio jets. Over the past decade, ALMA has resolved circumnuclear molecular discs, rings, spirals, tori, and jet-driven outflows on scales of tens of parsecs in nearby AGN. These studies have demonstrated that combining spatially resolved gas kinematics with multi-line diagnostics is essential for disentangling the physical processes underlying the observed gas conditions. However, our current picture remains largely based on a limited number of exceptionally nearby systems. Most local AGN have instead been observed with ALMA in a single transition at moderate or low angular resolution, preventing a systematic characterisation of both the dynamics and physical state of the gas. Moreover, existing studies have largely overlooked kinetic-mode AGN hosted by massive early-type galaxies, despite their central importance for understanding maintenance-mode feedback. In this White Paper, we argue that ALMA2040 should transform these detailed case studies into a statistical framework. A ∼10× improvement in line sensitivity relative to the current ALMA, together with fast mosaicking, ultra-wide instantaneous bandwidth, and longer baselines (resolving up to ∼9 mas at 230 GHz), will enable homogeneous, multi-line mapping of the molecular ISM on giant molecular clouds scales across statistically significant samples of both radiative- and kinetic-mode AGN out to z≲0.1.