-- =============================================
-- Returns the recipient company's postal address as a structured JSON object
-- ({name, street, postalCode, city, state, country}) for the invoice editor's
-- structured address dialog (EN 16931 / eRechnung). Mirrors the location
-- resolution of [fds__getCompanyNameAddress] but keeps the fields separate
-- instead of composing them into one free-text string. See ADR 0012.
-- =============================================
CREATE FUNCTION [dbo].[fds__getCompanyAddressJson]
(
	@companyid bigint
)
RETURNS nvarchar(max)
AS
BEGIN
	DECLARE @locationid bigint, @name nvarchar(255);
	DECLARE @street nvarchar(255), @postal nvarchar(255), @city nvarchar(255), @state nvarchar(255), @country varchar(15);

	SELECT TOP(1) @locationid = cy.[Location#ID], @name = cy.[name]
		FROM [dbo].[mfr__companies] as cy WHERE cy.[id] = @companyid;

	IF @locationid IS NULL
		SELECT TOP(1) @locationid = l.[ID]
			FROM [dbo].[mfr__#locations] as l
			JOIN [dbo].[mfr__companies] as cy ON l.[Property] = 'Company:Location' AND l.[EntityId] = cy.[Id]
			WHERE cy.[id] = @companyid;

	SELECT TOP(1) @street = loc.[AddressString], @postal = loc.[Postal], @city = loc.[City],
				  @state = loc.[State], @country = loc.[Country]
		FROM [dbo].[mfr__#locations] as loc WHERE loc.[id] = @locationid;

	RETURN (
		SELECT
			  ISNULL(@name, '')    AS [name]
			, ISNULL(@street, '')  AS [street]
			, ISNULL(@postal, '')  AS [postalCode]
			, ISNULL(@city, '')    AS [city]
			, ISNULL(@state, '')   AS [state]
			, ISNULL(@country, '') AS [country]
		FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
	);
END