-- Galaxy Object Attributes/Tags for OPC UA Server
-- Returns all runtime-readable attributes for deployed automation objects.
-- Use full_tag_reference for read/write operations against the runtime.
-- Join with hierarchy.sql results on gobject_id to place attributes in the OPC UA browse tree.
--
-- Two sources of attributes:
--   1. attribute_definition (via primitive_instance) — system/primitive attributes
--      Joined via the instance's deployed_package_id to exclude undeployed changes.
--   2. dynamic_attribute — user-defined attributes (e.g., MachineID, MoveInFlag)
--      Walked via the deployed package derivation chain (package.derived_from_package_id)
--      to only include attributes that existed at deploy time.
--      When the same attribute appears at multiple levels (e.g., instance override and
--      base template), only the shallowest (most-derived) version is kept.
--
-- Historization detection: a dynamic attribute is historized when a primitive_instance
-- with a matching name exists in the deployed package chain and its primitive_definition
-- has primitive_name = 'HistoryExtension'.
--
-- Attribute category filter (mx_attribute_category):
--   2-11, 24 = runtime readable attributes
--
-- Attribute names starting with '_' are internal/hidden and excluded.
-- dynamic_attribute '.Description' suffixed entries are metadata, excluded.
--
-- Array dimensions are extracted from the mx_value hex string (bytes 5-6, little-endian
-- uint16 at hex positions 13-16). Works for both attribute_definition and dynamic_attribute.
--
-- Data types (mx_data_type):
--   1 = Boolean, 2 = Integer (Int32), 3 = Float (Single), 4 = Double,
--   5 = String, 6 = Time (DateTime), 7 = ElapsedTime (TimeSpan),
--   8 = (reference), 13 = (enumeration), 14 = (custom), 15 = InternationalizedString, 16 = (custom)

;WITH deployed_package_chain AS (
    -- Start from each deployed instance's deployed package
    SELECT
        g.gobject_id,
        p.package_id,
        p.derived_from_package_id,
        0 AS depth
    FROM gobject g
    INNER JOIN package p
        ON p.package_id = g.deployed_package_id
    WHERE g.is_template = 0
        AND g.deployed_package_id <> 0
    UNION ALL
    -- Walk up the package derivation chain
    SELECT
        dpc.gobject_id,
        p.package_id,
        p.derived_from_package_id,
        dpc.depth + 1
    FROM deployed_package_chain dpc
    INNER JOIN package p
        ON p.package_id = dpc.derived_from_package_id
    WHERE dpc.derived_from_package_id <> 0 AND dpc.depth < 10
),
-- Rank dynamic attributes: shallowest (most-derived) wins per object + attribute
ranked_dynamic AS (
    SELECT
        dpc.gobject_id,
        g.tag_name,
        da.attribute_name,
        g.tag_name + '.' + da.attribute_name
            + CASE WHEN da.is_array = 1 THEN '[]' ELSE '' END
            AS full_tag_reference,
        da.mx_data_type,
        dt.description AS data_type_name,
        da.is_array,
        CASE WHEN da.is_array = 1
             THEN CONVERT(int, CONVERT(varbinary(2),
                  SUBSTRING(da.mx_value, 15, 2) + SUBSTRING(da.mx_value, 13, 2), 2))
             ELSE NULL
        END AS array_dimension,
        da.mx_attribute_category,
        da.security_classification,
        CASE WHEN EXISTS (
            SELECT 1 FROM deployed_package_chain dpc2
            INNER JOIN primitive_instance pi
                ON pi.package_id = dpc2.package_id
                AND pi.primitive_name = da.attribute_name
            INNER JOIN primitive_definition pd
                ON pd.primitive_definition_id = pi.primitive_definition_id
                AND pd.primitive_name = 'HistoryExtension'
            WHERE dpc2.gobject_id = dpc.gobject_id
        ) THEN 1 ELSE 0 END AS is_historized,
        CASE WHEN EXISTS (
            SELECT 1 FROM deployed_package_chain dpc2
            INNER JOIN primitive_instance pi
                ON pi.package_id = dpc2.package_id
                AND pi.primitive_name = da.attribute_name
            INNER JOIN primitive_definition pd
                ON pd.primitive_definition_id = pi.primitive_definition_id
                AND pd.primitive_name = 'AlarmExtension'
            WHERE dpc2.gobject_id = dpc.gobject_id
        ) THEN 1 ELSE 0 END AS is_alarm,
        ROW_NUMBER() OVER (
            PARTITION BY dpc.gobject_id, da.attribute_name
            ORDER BY dpc.depth
        ) AS rn
    FROM deployed_package_chain dpc
    INNER JOIN dynamic_attribute da
        ON da.package_id = dpc.package_id
    INNER JOIN gobject g
        ON g.gobject_id = dpc.gobject_id
    INNER JOIN template_definition td
        ON td.template_definition_id = g.template_definition_id
    LEFT JOIN data_type dt
        ON dt.mx_data_type = da.mx_data_type
    WHERE td.category_id IN (1, 3, 4, 10, 11, 13, 17, 24, 26)
        AND da.attribute_name NOT LIKE '[_]%'
        AND da.attribute_name NOT LIKE '%.Description'
        AND da.mx_attribute_category IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 24)
)
SELECT
    gobject_id,
    tag_name,
    primitive_name,
    attribute_name,
    full_tag_reference,
    mx_data_type,
    data_type_name,
    is_array,
    array_dimension,
    mx_attribute_category,
    security_classification,
    is_historized,
    is_alarm,
    attribute_source
FROM (
    -- Part 1: System/primitive attributes (from attribute_definition via deployed package)
    SELECT
        g.gobject_id,
        g.tag_name,
        pi.primitive_name,
        ad.attribute_name,
        CASE WHEN pi.primitive_name = ''
             THEN g.tag_name + '.' + ad.attribute_name
             ELSE g.tag_name + '.' + pi.primitive_name + '.' + ad.attribute_name
        END + CASE WHEN ad.is_array = 1 THEN '[]' ELSE '' END
            AS full_tag_reference,
        ad.mx_data_type,
        dt.description AS data_type_name,
        ad.is_array,
        CASE WHEN ad.is_array = 1
             THEN CONVERT(int, CONVERT(varbinary(2),
                  SUBSTRING(ad.mx_value, 15, 2) + SUBSTRING(ad.mx_value, 13, 2), 2))
             ELSE NULL
        END AS array_dimension,
        ad.mx_attribute_category,
        ad.security_classification,
        CAST(0 AS int) AS is_historized,
        CAST(0 AS int) AS is_alarm,
        'primitive' AS attribute_source
    FROM gobject g
    INNER JOIN instance i
        ON i.gobject_id = g.gobject_id
    INNER JOIN template_definition td
        ON td.template_definition_id = g.template_definition_id
        AND td.runtime_clsid <> '{00000000-0000-0000-0000-000000000000}'
    INNER JOIN package p
        ON p.package_id = g.deployed_package_id
    INNER JOIN primitive_instance pi
        ON pi.package_id = p.package_id
        AND pi.property_bitmask & 0x10 <> 0x10
    INNER JOIN attribute_definition ad
        ON ad.primitive_definition_id = pi.primitive_definition_id
        AND ad.attribute_name NOT LIKE '[_]%'
        AND ad.mx_attribute_category IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 24)
    LEFT JOIN data_type dt
        ON dt.mx_data_type = ad.mx_data_type
    WHERE td.category_id IN (1, 3, 4, 10, 11, 13, 17, 24, 26)
        AND g.is_template = 0
        AND g.deployed_package_id <> 0

    UNION ALL

    -- Part 2: User-defined attributes (shallowest override from deployed package chain)
    SELECT
        gobject_id,
        tag_name,
        '' AS primitive_name,
        attribute_name,
        full_tag_reference,
        mx_data_type,
        data_type_name,
        is_array,
        array_dimension,
        mx_attribute_category,
        security_classification,
        is_historized,
        is_alarm,
        'dynamic' AS attribute_source
    FROM ranked_dynamic
    WHERE rn = 1
) all_attributes
ORDER BY tag_name, primitive_name, attribute_name;