namespace DMSDAL.Query.Migration
{
    public static class AlfrescoMigrationQB
    {
        // Requires index: IX_MENTITY_ENTITYID (already PKed), IX_DBOBJECTFIELDS_OBJECTID_ISPK
        public const string GET_ENTITY_TABLE_INFO = @"
            SELECT
                E.ENTITYID    AS EntityId,
                E.ENTITYCODE  AS EntityCode,
                E.ENTITYNAME  AS EntityName,
                DO.DBOBJECTNAME  AS SourceTable,
                DF.DBFIELDNAME   AS PKColumn
            FROM   DBO.MENTITY        E
            JOIN   DBO.DBOBJECT       DO ON DO.DBOBJECTID = E.DBOBJECTID
            JOIN   DBO.DBOBJECTFIELDS DF ON DF.DBOBJECTID = DO.DBOBJECTID
                                         AND DF.ISPRIMARYKEY = 1
            WHERE  E.ENTITYID = @ObjectTypeId";

        // Dynamic query — table and column names come from EntityTableInfoDTO, not user input.
        // Caller builds this at runtime using EntityTableInfoDTO.SourceTable / PKColumn.
        // Pattern: SELECT {pk} FROM {table} WHERE CLIENTID=@ClientId ORDER BY {pk}
        //          OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY
        public const string GET_ENTITY_IDS_PAGED_TEMPLATE =
            "SELECT [{0}] FROM DBO.[{1}] WHERE CLIENTID=@ClientId " +
            "ORDER BY [{0}] OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

        // Check whether this entity already has a non-Alfresco attachment in TATTACHMENT.
        public const string CHECK_ALREADY_MIGRATED = @"
            SELECT COUNT(1)
            FROM   DBO.TATTACHMENT
            WHERE  OBJECTTYPEID    = @ObjectTypeId
              AND  OBJECTID        = @ObjectId
              AND  ATTACHMENTOPTION IN (2, 3)
              AND  DATABASENAME    = @DatabaseName";

        public const string GET_ALFRESCO_SETTINGS = @"
            SELECT COMMONVALUE
            FROM   DBO.MCOMMONCONFIG
            WHERE  COMMONKEY = @Key
              AND  CLIENTID  = @ClientId";

        public const string INSERT_MIGRATION_LOG = @"
            EXEC DBO.SP_MIGRATION_LOG_INSERT
                @ObjectTypeId, @ObjectId, @AlfrescoNodeId,
                @DisplayFileName, @ClientId";

        public const string UPDATE_MIGRATION_LOG = @"
            EXEC DBO.SP_MIGRATION_LOG_UPDATE
                @MigrationLogId, @Status, @AttachmentId, @ErrorMessage";

        public const string GET_MIGRATION_STATUS = @"
            EXEC DBO.SP_MIGRATION_STATUS @ObjectTypeId, @ClientId";
    }
}