Two Welsh councils have teamed up to deliver what has been described as a "transformative" housing investment programme which will see thousands of new homes built, both for council and private use.