namespace IceImportDAL.Query.EntityFieldLookup;

// Resolves the physical table/column names backing a MENTITY row, so ValueType 4/5 (Code/Name ->
// Id) lookups can hit the target table directly instead of round-tripping through the entity's own
// GetSelectList endpoint. Chain: MENTITY.ENTITYID -> MENTITYMEMBERS (per-field metadata) ->
// DBOBJECTFIELDS (DBFIELDID is the FK) -> DBOBJECT (physical table name), plus MENTITY.DBOBJECTID
// -> DBOBJECT directly for the table name/primary-key column.
public static class EntityFieldLookupQB
{
    // One row per (EntityId): the physical table name and its primary-key column name.
    public const string GET_TABLE_AND_ID_FIELD = @"
        SELECT d.DBOBJECTNAME AS TableName,
               c.DBFIELDNAME  AS IdFieldName
        FROM   MENTITY a
        JOIN   MENTITYMEMBERS b ON b.ENTITYID = a.ENTITYID 
        JOIN   DBOBJECTFIELDS c ON c.DBOBJECTFIELDID = b.DBFIELDID AND C.ISPRIMARYKEY = 0
        JOIN   DBOBJECT d       ON d.DBOBJECTID = a.DBOBJECTID
        WHERE  a.ENTITYID = @EntityId";

    // Resolves the physical column backing a specific entity member (MEMBERNAME = 'CODE' or
    // 'NAME'). Returns zero or more rows — caller takes the first.
    public const string GET_FIELD_BY_MEMBERNAME = @"
        SELECT c.DBFIELDNAME AS FieldName
        FROM   MENTITY a
        JOIN   MENTITYMEMBERS b ON b.ENTITYID = a.ENTITYID
        JOIN   DBOBJECTFIELDS c ON c.DBOBJECTFIELDID = b.DBFIELDID
        WHERE  a.ENTITYID = @EntityId
        AND    b.MEMBERNAME = @MemberName";

    // Fallback for entities with no member literally named 'NAME' (e.g. MGCMTYPE, whose
    // display-name column is GCMTYPEDESCRIPTION) — match on the common CODE/DESCRIPTION
    // master-data naming convention instead.
    public const string GET_FIELD_BY_MEMBERNAME_LIKE = @"
        SELECT c.DBFIELDNAME AS FieldName
        FROM   MENTITY a
        JOIN   MENTITYMEMBERS b ON b.ENTITYID = a.ENTITYID
        JOIN   DBOBJECTFIELDS c ON c.DBOBJECTFIELDID = b.DBFIELDID
        WHERE  a.ENTITYID = @EntityId
        AND    b.MEMBERNAME LIKE @MemberNamePattern
        ORDER BY b.SLNO";
}