namespace FrameworkDAL.Query.GOP
{
    // ============================================================
    // CanonicalModelQB — reusable, typed schema per business entity/document
    // (e.g. "PO", "LEAVE", "RESUME"), shared across Mapper definitions instead
    // of each Mapper inventing its own open-ended shape.
    //
    // Tables: MCANONICALMODEL, MCANONICALFIELD
    // Security: TENANTID = @TenantId mandatory on every query, EXCEPT read queries also
    // match TENANTID = -1 — the Framework-tier "library" sentinel (SOURCETYPE=1 rows,
    // seeded by 20260905_GOP_DocumentType_And_Library_Seed_SqlServer.sql) is meant to be
    // visible to every tenant, not just tenant -1 itself. Write queries (UPDATE/DEACTIVATE)
    // stay scoped to @TenantId only — CanonicalModelBLL.GuardSourceType is what actually
    // blocks a tenant session from mutating a library row it can merely see.
    //
    // Required indexes:
    //   IX_MCANONICALMODEL_TID_ENTITYCODE (TENANTID, ENTITYCODE) WHERE ISACTIVE=1
    //   IX_MCANONICALFIELD_TID_MODEL      (TENANTID, MCANONICALMODELID) WHERE STATUS=1
    // ============================================================
    public static class CanonicalModelQB
    {
        // ── Canonical Model ────────────────────────────────────────

        public const string GET_LIST = @"
            SELECT  m.TENANTID          AS TenantId,
                    m.MCANONICALMODELID AS CanonicalModelId,
                    m.ENTITYCODE        AS EntityCode,
                    m.MODELNAME         AS ModelName,
                    m.DESCRIPTION       AS Description,
                    m.VERSION           AS Version,
                    m.ISACTIVE          AS IsActive,
                    m.SORTORDER         AS SortOrder,
                    m.STATUS            AS Status,
                    m.SOURCETYPE        AS SourceType
            FROM    MCANONICALMODEL m
            WHERE   (m.TENANTID = @TenantId OR m.TENANTID = -1)
              AND   m.STATUS   = 1
            ORDER BY m.SOURCETYPE ASC, m.ENTITYCODE ASC;";

        public const string GET_BY_ID = @"
            SELECT  m.TENANTID          AS TenantId,
                    m.MCANONICALMODELID AS CanonicalModelId,
                    m.ENTITYCODE        AS EntityCode,
                    m.MODELNAME         AS ModelName,
                    m.DESCRIPTION       AS Description,
                    m.VERSION           AS Version,
                    m.ISACTIVE          AS IsActive,
                    m.SORTORDER         AS SortOrder,
                    m.STATUS            AS Status,
                    m.SOURCETYPE        AS SourceType
            FROM    MCANONICALMODEL m
            WHERE   (m.TENANTID = @TenantId OR m.TENANTID = -1)
              AND   m.MCANONICALMODELID = @CanonicalModelId;";

        // TOP 1 + explicit tenant-first ordering: a tenant's own row must win over a
        // same-EntityCode library row (TENANTID=-1) — EntityCode uniqueness is only
        // scoped to (TENANTID, ENTITYCODE), so both can legitimately exist at once.
        public const string GET_BY_ENTITY_CODE = @"
            SELECT  TOP 1
                    m.TENANTID          AS TenantId,
                    m.MCANONICALMODELID AS CanonicalModelId,
                    m.ENTITYCODE        AS EntityCode,
                    m.MODELNAME         AS ModelName,
                    m.DESCRIPTION       AS Description,
                    m.VERSION           AS Version,
                    m.ISACTIVE          AS IsActive,
                    m.SORTORDER         AS SortOrder,
                    m.STATUS            AS Status,
                    m.SOURCETYPE        AS SourceType
            FROM    MCANONICALMODEL m
            WHERE   (m.TENANTID = @TenantId OR m.TENANTID = -1)
              AND   m.ENTITYCODE = @EntityCode
              AND   m.ISACTIVE   = 1
            ORDER BY CASE WHEN m.TENANTID = @TenantId THEN 0 ELSE 1 END;";

        public const string INSERT = @"
            INSERT INTO MCANONICALMODEL
                (MCANONICALMODELID, TENANTID, ENTITYCODE, MODELNAME, DESCRIPTION,
                 VERSION, ISACTIVE, SORTORDER, STATUS, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@CanonicalModelId, @TenantId, @EntityCode, @ModelName, @Description,
                 @Version, @IsActive, @SortOrder, 1, @SourceType,
                 @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE = @"
            UPDATE MCANONICALMODEL
            SET    MODELNAME    = @ModelName,
                   DESCRIPTION  = @Description,
                   VERSION      = @Version,
                   ISACTIVE     = @IsActive,
                   SORTORDER    = @SortOrder,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  MCANONICALMODELID = @CanonicalModelId
            AND    TENANTID          = @TenantId";

        public const string DEACTIVATE = @"
            UPDATE MCANONICALMODEL
            SET    STATUS       = 2,
                   ISACTIVE     = 0,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  MCANONICALMODELID = @CanonicalModelId
            AND    TENANTID          = @TenantId";

        // ── Canonical Field ────────────────────────────────────────

        public const string GET_FIELDS_BY_MODEL = @"
            SELECT  f.TENANTID          AS TenantId,
                    f.MCANONICALFIELDID AS CanonicalFieldId,
                    f.MCANONICALMODELID AS CanonicalModelId,
                    f.FIELDPATH         AS FieldPath,
                    f.FIELDNAME         AS FieldName,
                    f.DATATYPE          AS DataType,
                    f.ISREQUIRED        AS IsRequired,
                    f.DEFAULTVALUE      AS DefaultValue,
                    f.DESCRIPTION       AS Description,
                    f.SORTORDER         AS SortOrder
            FROM    MCANONICALFIELD f
            WHERE   (f.TENANTID = @TenantId OR f.TENANTID = -1)
              AND   f.MCANONICALMODELID = @CanonicalModelId
              AND   f.STATUS            = 1
            ORDER BY f.SORTORDER ASC;";

        public const string INSERT_FIELD = @"
            INSERT INTO MCANONICALFIELD
                (MCANONICALFIELDID, TENANTID, MCANONICALMODELID, FIELDPATH, FIELDNAME,
                 DATATYPE, ISREQUIRED, DEFAULTVALUE, DESCRIPTION,
                 SORTORDER, STATUS,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@CanonicalFieldId, @TenantId, @CanonicalModelId, @FieldPath, @FieldName,
                 @DataType, @IsRequired, @DefaultValue, @Description,
                 @SortOrder, 1,
                 @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string GET_FIELD_MODEL_ID = @"
            SELECT  f.MCANONICALMODELID
            FROM    MCANONICALFIELD f
            WHERE   f.MCANONICALFIELDID = @CanonicalFieldId
              AND   (f.TENANTID = @TenantId OR f.TENANTID = -1);";

        public const string DELETE_FIELD = @"
            UPDATE MCANONICALFIELD
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  MCANONICALFIELDID = @CanonicalFieldId
            AND    TENANTID          = @TenantId";

        public const string DELETE_FIELDS_BY_MODEL = @"
            UPDATE MCANONICALFIELD
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  MCANONICALMODELID = @CanonicalModelId
            AND    TENANTID          = @TenantId";
    }
}